Snap for JDBC that runs stored procedure and returns status and results...like SQL execute snap

[jcornelius]

I need a snap like the SQL execute snap that returns both the results and the status but is for JDBC stored procedure…does one exist

[Anendu]

Hello Mbowen,

I am currently running Stored Procedure against SQL database using JDBC execute SNAP and its returning me on only the execution status .

DB Version :- SQL server 2017

Can you please help, How to run stored procedure in JDBC SNAP?

Thanks,
Anendu

[mbowen]

Hi @Anendu

Our Generic JDBC Execute doesn’t wrap a CallableStatement which is the typical object used to invoke a stored procedures. It allows registering out parameters, etc. However, we may be able to invoke a stored procedure if it has no output parameters and returns no result set – pretty restrictive!

Your questions suggest that you do want to get results back. Please clarify ? As mentioned above, you can invoke a stored function in a SELECT. Nick shows how to invoke a stored procedure in an anonymous PL/SQL block to load a JSON blob, but that’s Oracle – nevertheless, all success stories helpful.

So, tell me a bit more about your stored procedure. BTW, we do have SQL Server snap pack which has a Stored Procedure snap which has very good support for stored procedures.

[jcornelius]

Pervasive supports in and out and in/out…the real issue is in the JDBC Execute snap has limit of max 1 output (unlike SQL Execute which allows for max 2…1 for status and 1 for returned data), the JDBC Execute throws exception when the returns is defined for a stored procedure, if I use and out it just does not return the data only the status (ie execution result)

[jcornelius]

Pervasive supports in and out and in/out…the real issue is in the JDBC Execute snap has limit of max 1 output (unlike SQL Execute which allows for max 2…1 for status and 1 for returned data), the JDBC Execute throws exception when the returns is defined for a stored procedure, if I use and out it just does not return the data only the status (ie execution result)

[mbowen]

Pervasive is not a database we directly support, and as you’ve found, there are limitations using the generic JDBC snap pack. A next step may be to create a support ticket with an example of what you’re trying to do. I don’t have experience with this database, but maybe some other folks on the team do.