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 ?
