Forum Discussion

jcornelius's avatar
jcornelius
New Contributor III
5 years ago

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

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

pipeline

16 Replies

  • nickshore's avatar
    nickshore
    New Contributor II
    4 years ago

    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

  • mbowen's avatar
    mbowen
    Employee
    5 years ago

    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.

    • jcornelius's avatar
      jcornelius
      New Contributor III
      5 years ago

      we are using JDBC to connect to Pervasive db
      the SQL Execute will not connect

    • nickshore's avatar
      nickshore
      New Contributor II
      5 years ago

      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

      • mbowen's avatar
        mbowen
        Employee
        5 years ago

        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 ?

  • jcornelius's avatar
    jcornelius
    New Contributor III
    5 years ago

    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's avatar
    jcornelius
    New Contributor III
    5 years ago

    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's avatar
      mbowen
      Employee
      5 years ago

      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.

    • mbowen's avatar
      mbowen
      Employee
      4 years ago

      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.