Get a value from a stored procedure MySQL

Dear friends
Xojo

Grateful for the interest and its scope to solve the following question:

Is it possible to get a value from a MySQL stored procedure in Xojo?

Basically I try to pass a variable numoper with value zero and that after executing the stored procedure, that variable returns the ID of the last inserted record

The code in xojo is the following:

  Dim s as string = "Juan"
  Dim numoper As Integer=0
  Dim sql As string="CALL bdcontabilidad.nombresins(?)" 
  Dim stmt As PreparedSQLStatement=App.pDb.Prepare(sql)  
  stmt.BindType(0,MySQLPreparedStatement.MYSQL_TYPE_STRING)
  stmt.BindType(1,MySQLPreparedStatement.MYSQL_TYPE_LONG)
  stmt.SQLExecute(s,numoper)
  MsgBox("Error  : "+ App.pDb.ErrorMessage)

The stored procedure code is the following:

DELIMITER $$

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

   INSERT nombres(nombre)
   VALUES(pnombre);

   SET pnumoper = LAST_INSERT_ID();
   
END $$

DELIMITER ;

Greetings.

Raul Juarez Pulache

Try sqlselect instead of sqlexecute

sqlselect is for sql code that returns rows
sqlexecute for sql that does not

Thank you so much
Norman Palardy

I have tried as you indicated, but I can NOT solve it
The returned recordset is null

The code in xojo is the following:

  Dim s as string = "Juan"
  Dim numoper As Integer=0
  Dim sql As string="CALL bdcontabilidad.nombresins(?)" 
  Dim stmt As PreparedSQLStatement=App.pDb.Prepare(sql)  
  stmt.BindType(0,MySQLPreparedStatement.MYSQL_TYPE_STRING)
  stmt.BindType(1,MySQLPreparedStatement.MYSQL_TYPE_LONG)
  Dim rs As recordset =stmt.SQLSelect(s,numoper)
  MsgBox("Error  : "+ App.pDb.ErrorMessage)
  If rs <> Nil Then
    MsgBox "With records"
  Else
    MsgBox "Nothing"
  End If

Cordially,

Raul Juarez Pulache

ah … with an out param you might have to look at whats in the variable numoper since your procedure doesn’t return a set of rows just one value

And I’ll admit that its just a guess at this point as I mostly dont use mysql :stuck_out_tongue:
I prefer PostgreSQL and so my experience is more oriented towards that

I don’t think that you will be able to retrieve the value from an OUT param in MySQL from Xojo (but I could very well be wrong).

You can, however, change your procedure from using an OUT param, ex:

CREATE DEFINER=`admin`@`%` PROCEDURE `addone`(input INT, OUT output INT) BEGIN SET output = input + 1; END

To output the value directly using a SELECT statement, ex:

CREATE DEFINER=`admin`@`%` PROCEDURE `addone`(input INT) BEGIN SELECT input + 1; END

Or you can define the procedure like this if you are already setting the output value and just want to use it directly in the SELECT statement:

CREATE DEFINER=`admin`@`%` PROCEDURE `addone`(input INT) BEGIN SET @output = input + 1; SELECT @output; END

Dear Jared Feder

I had to modify the code in the Mysql, as you indicated.

Thanks to your help, I can get a value from Mysql
The code that works for me is the following one and can serve somebody for reference.

Xojo

  Dim nombre as string = "Juan"
  Dim edad As Integer=35
  Dim sql As string="CALL bdcontabilidad.nombresins(?,?)" 
  Dim stmt As PreparedSQLStatement=App.pDb.Prepare(sql)  
  stmt.BindType(0,MySQLPreparedStatement.MYSQL_TYPE_STRING)
  stmt.BindType(1,MySQLPreparedStatement.MYSQL_TYPE_LONG)
  Dim rs As recordset =stmt.SQLSelect(nombre,edad)
  If Not App.pDb.Error Then
    MsgBox "El  ID es "+str(rs.IdxField(1).IntegerValue)
  Else
    MsgBox("Error  : "+ App.pDb.ErrorMessage)
  End If
  rs.Close
  Dim sql2 As string="mysql_use_result()"
  App.pDb.SQLExecute(sql2)

MySQL

DELIMITER $$

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

   INSERT nombres(nombre,edad)
   VALUES(pnombre,pedad);
   SET @Numid =LAST_INSERT_ID();
   SELECT @Numid;
 
END $$

DELIMITER ;

greetings and blessings
Raul Juarez Pulache