Run Stored Procedures in Mysql

Databases
1

Dear Friends
Xojo

Good day to all of you and grateful in advance for the interest and for the scopes you can get to resolve how to execute a stored procedure:
In Xojo the code to execute the stored procedure, is the following one:

Dim pnombre As string          ' parameter nombre
Dim sql as String="CALL bdcontabilidad.nombresins(pnombre)"
App.pDb.SQLExecute(sql)
MsgBox("Error  "+ App.pDb.ErrorMessage)

Where pDb is a property of type MySQLCommunityServer, I can successfully connect to Mysql
I can insert, update and delete Mysql records without any problem even using PreparedSQLStatement.

The thing is that I can not execute stored procedures
For the code above it shows me the following message:

Error Unknown column ‘pnombre’ in ‘field list’

I can not understand the error because it says that it does not recognize pnombre, however pnombre has an explicit value.

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 ;

The database is called bdcontabilidad
The table is called nombres
And said table has a field called nombre Type VARCHAR (100)

Your answers will be greatly appreciated

Cordially,

Raul Juarez Pulache

2

Put a breakpoint on the line

App.pDb.SQLExecute(sql)

And try your code again
What you should see is that in your code you might have a variable called pnombre but what you need to use is the VALUE it holds as part of your sql

If you try to run (using a terminal or other tool connected to mysql) and try to run the string you created as your statement

CALL bdcontabilidad.nombresins(pnombre)

You will get the same error - because pnombre is unknown to mysql

Try something like the following that puts the VALUE of pnombre into your sql

Dim pnombre As string          ' parameter nombre
Dim sql as String="CALL bdcontabilidad.nombresins(" + pnombre + ")"

Again put a break point on

App.pDb.SQLExecute(sql)

And run it again

FWIW pnombre wont have any contents if it really is declared right above the creation of the sql like your sample shows

3

Thanks Norman P

Including what you recommend:

  Dim pnombre As string ="Juan"
  Dim sql as String="CALL bdcontabilidad.nombresins(" + pnombre + ")"
  App.pDb.SQLExecute(sql)
  MsgBox("Error  "+ App.pDb.ErrorMessage)

It also generates the following error message:

Error Unknown column ‘Juan’

Some additional scope to solve the problem

Cordially,

Raul Juarez Pulache

4

Try wrapping the value of the variable “pnombre” in single quotes within your parenthesis:

Dim sql as String="CALL bdcontabilidad.nombresins('" + pnombre + "')"

That indicates to mySql that it is a value instead of a variable.

5

in reality its probably better to use a prepared statement and avoid the quoting issue altogether

6

Thanks Norman Palardy
Thanks Jared Feder

The simple quotes are the ones that were missing, and finally I could insert the registry in the table of Mysql

Dim sql as String="CALL bdcontabilidad.nombresins('" + pnombre + "')"

I am learning xojo and my objective is to be able to use prepared statement to execute the stored procedures in Mysql and is what I am going to try.

Cordially,

Raul Juarez Pulache