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 ;
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
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 ;