Error executing MySQL procedure

Ignition
1

Hey
I have problem with MySQL procedure. I created one looking like this:

CREATE DEFINER=`root`@`%` PROCEDURE `test`()
BEGIN
SELECT '1';
END

As You see it’s simple. And when I do this its fine:

system.db.runQuery('call test', 'WPPiM')

But when I want to call it this way it crashes:

proc = system.db.createSProcCall('test', 'WPPiM')
system.db.execSProcCall(proc)

I get following error:

Traceback (most recent call last):

  File "<event:actionPerformed>", line 2, in <module>



java.lang.Exception: java.lang.Exception: Error executing system.db.execSProcCall()


	caused by Exception: Error executing system.db.execSProcCall()
	caused by GatewayException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.
	caused by SQLException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.

Ignition v7.8.5 (b2016120813)
Java: Oracle Corporation 1.8.0_181

Does anyone know what is wrong? I need to use second way because I need IN and OUT parameters.

2

OK I found some solution. Looks like this:

tx = system.db.beginTransaction('WPPiM')
system.db.runUpdateQuery('SET @insId = 0', 'WPPiM', tx)
print system.db.runQuery("call WPPiM.getStanMBK(NULL, @insId)", 'WPPiM', tx)
print system.db.runScalarQuery('SELECT @insId', 'WPPiM', tx)
system.db.commitTransaction(tx)
system.db.closeTransaction(tx)

and the outcome is:

<PyDataset rows:966 cols:50>
54

But I’m still wondering why the previous example didn’t work.
P.s. in this post I used target procedure, that’s why its different

3

I realize this post is 3 years old, but just ran into the same problem and took longer than it should to find the solution since i checked here first. Googling the general java SQLException led to the answer outside of ignition context.

You need to append
?noAccessToProcedureBodies=true
to the Connect URL of the database connection from the gateway web page configuration.

This only applies to MySQL.