MySql stored procedure call

Ignition
1

I’m used to work with MS SQL Server and this is my first time with MySql.
I have defined a very simple Stored Procedure in my database:

CREATE DEFINER=`root`@`%` PROCEDURE `logs_test`(par1 int, out par2 int)
BEGIN
    set par2=234;
END

When i try to call it like that:

call=system.db.createSProcCall("logs_test")
call.registerInParam(1, system.db.INTEGER,10)
call.registerOutParam(2, system.db.INTEGER)
system.db.execSProcCall(call)

I get the following error:

If I try to use named paramters, it doesn’t work either and the error switches to:

Any suggestion?

Thanks in advance

2

Your stored procedure worked without problems for me. I can only replicate the first error if I deliberately removed “out” from par2’s definition and updated the stored procedure.

3

Thanks mgross, I found I had a problem of stored procedure naming: I was used to naming my objects as database.schema.objectname in MS SQL Server and I was trying to replicate that in MySql. I found that, even if MySql accepts it (e.g. you get the data you want if you work in Workbench), the JDBC driver doesn’t like it, I suppose: you get errors like I reported, which are very difficult to debug.
Moreover my first attempt to solve that naming problem was just to add a procedure with name logs_test, while keeping also the logs.test routine in the object tree, and that was a big mistake: I think, the JDBC driver was confused by the two similar names. After deleting the logs.test object, it started running ok.

1 Like