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
Hi John:
For which database? You can do quite a bit in the SQL Execute snap – SELECT, CREATE, even invoke a stored function (well, at least for Oracle). Oracle has the notion of IN (parameters passed in) and OUT parameters (values returned from the procedure), and you can even define a stored function which returns a value and also a result in an OUT parameter – don’t know that this is a common pattern though.
function f1(x_out OUT varchar2) return number AS
BEGIN
x_out := 'OK';
RETURN 1;
END f1;
We do support stored procedures for most of our databases, but to what extent depends on the db.
we are using JDBC to connect to Pervasive db
the SQL Execute will not connect
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)
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)
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.
Hi,
Just jumping on this thread as we have a requirement to execute an Oracle stored procedure that has a IN/OUT parameter set which I know isn’t support via the Stored Procedure snap. Are you saying this should be achievable through the Oracle Execute snap?
Thanks
Nick
Hi Nick:
Jumping in on thread is perfectly fine. The Oracle Execute snap can’t invoke stored procedures. Can you tell me more about your requirement and how the Stored Procedure snap can’t handle it ?
Hi,
Sorry for the delayed response. I did actually to get it to execute a stored procedure via the Oracle Execute Snap with the following syntax.
Thanks
Nick
1 Like
Hi All,
Any info how to call Sybase store procedure.
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
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.
Hi @sanjaynayak
Please see other comments about restrictions invoking stored procedures using the Generic JDBC Execute snap. What kinds of stored procedures do you want to invoke? We don’t have a Sybase snap pack, but think SAP bought them 2010-ish. We do have an SAP HANA snap pack, but am really less familiar with it, but could find out more information.
It’s sybase store procedure
I know you had mentioned Sybase, and the reference to SAP HANA clearly indicates that I have less experience with this DB, and not helpful to you. I’ve actually tagged another developer who has done some recent work with stored procedures across all of our snaps. So, you may hear some more from him.
Using a Sybase JDBC driver, we should be able to invoke a stored procedure, but again, there are some constraints using this snap. I can do a little more research, and maybe other dev will be able to say a bit more too.
Yes @smudassir , we are not expecting any out put, once store procedure get call, it will do what it supposed to be do in db level.