Running Stored Procedures Using PreparedSQLStatement

Databases
1

Dear Friends
Xojo

Thank you for your comments that you can send me to solve the following:
I am trying to execute a stored procedure called nombreins using PreparedSQLStatement with the following code:

'pDb is a property of type MySQLCommunityServer

Dim sql As string="bdcontabilidad.nombresins"
Dim stmt As PreparedSQLStatement=App.pDb.Prepare(sql)  
stmt.BindType(0,MySQLPreparedStatement.MYSQL_TYPE_STRING)
stmt.Bind(0,"Juan")
stmt.SQLExecute
MsgBox("Error  : "+ App.pDb.ErrorMessage)

When I run it shows me the following error:

Error:You have an error in your SQL syntax;check the manual that corresponds to your MySQL server version for the right syntax to use near ‘bdcontabilidad.nombresins’ at line 1

In Mysql the stored procedure code is as follows:

DELIMITER $$

DROP PROCEDURE IF EXISTS `bdcontabilidad`.`nombresins` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `nombresins`(IN pnombre VARCHAR(100))
BEGIN

   INSERT nombres(nombre)
   VALUES(pnombre);

END $$
DELIMITER ;

Much appreciated will be their scope, to solve the error and to be able to insert a record in Mysql with PreparedSQLStatement

Cordially,
Raul

2

you never set it up so you could pass any parameters to it (this is similar to your previous question https://forum.xojo.com/40818-run-stored-procedures-in-mysql)

the sql you’re using is NOT the same as the sql constructed in that question - and it should be nearly identical except for replacing the parameter

more like below (note I’ve not tested this so it may not be 100% correct but it will be close)
you MAY need to use a variable for the bind value (this line stmt.Bind(0,“Juan”) )

'pDb is a property of type MySQLCommunityServer

dim s as string = "Juan"
Dim sql As string="CALL bdcontabilidad.nombresins(?)" // see the similarity to your previous questions about this ?
Dim stmt As PreparedSQLStatement=App.pDb.Prepare(sql)  
stmt.BindType(0,MySQLPreparedStatement.MYSQL_TYPE_STRING)
stmt.Bind(0,s)
stmt.SQLExecute
MsgBox("Error  : "+ App.pDb.ErrorMessage)
3

Thank you so much
Norman Palardy

With its great help I can already execute stored procedures using preparedsqlstatment

Cordially,

Raul Juarez Pulache