Skip to Content
author's profile photo Former Member
Former Member

Calling a Stored procedure through query in Powerbuilder

Hi All,

I am trying to call a Stored procedure using below functions.

Declare XXX Procedure for SP_XXX(:input1, :input2, :Output)

Execute XXX.

Can anyone please let me know if this is Valid syntax or if i am missing anything.

Because the output variable always has value empty.

Thanks in Advance,

Lavanya.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • Posted on Aug 09, 2016 at 02:08 PM

    FYI: How it works depends from you DBMS!

    What's yours?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 09, 2016 at 03:25 PM

    This I what do..

    1) Created Transaction User Object and use as SQLCA.

    2) Declare external function for Stored Procedure ( define variable with Ref for output )

    3) Call SQLCA.usp_xxxx ( variables list )

    Regards,

    Jahir.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 09, 2016 at 01:50 PM

    Look for OUTPUT keyword or declare it as an external function.

    See here for examples:

    Stored Procedures « Schultz’s PowerBuilder Notes

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 09, 2016 at 01:55 PM

    Hi Lavanya;

    FWIW: You might like to consider using the Stored Procedure DataWindow instead of hard coding the SP commands inline with your PowerScript code. Especially, if the SP is returning an ANSI result set - as you then need to go into a FETCH loop in your PowerScript code to get the entire result set values back into your application. Also requiring requiring an array of variables / structures to hold the RS as well.

    Food for thought. 😉

    Regards ... Chris

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Lavanaya;

      Here is a real world example of working PowerScript code that calls a SP that in turn returns something ...

      ------------------------------------------------------------

      DECLARE AddName PROCEDURE FOR ST_INS_NAMES

      @names_id = :names_id,

      @names_dec_id = :ldec_names_dec_id,

      @first_names = :first_names,

      @last_name = :last_name

      USING itr;

      EXECUTE AddName;

      IF itr.SQLcode = -1 then

      MessageBox("Ooops","NAMES Insert failed!~n DB Error: " + itr.SQLErrText, StopSign!)

      else

      //fetch the ID returned by the procedure!

      FETCH AddDecName INTO :dec_names_id;

      CLOSE AddDecName;

      end if

      ---------------------------------------------------------

      HTH

      Regards ... Chris

  • Posted on Aug 09, 2016 at 02:12 PM

    You are missing the reserved word OUTPUT or OUT after the return variable:

    Declare XXX Procedure for SP_XXX(:input1, :input2, :Output OUTPUT);

    Execute XXX;

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 09, 2016 at 10:26 PM

    You generally have to initialise the output variable when using it in embedded SQL. Since you say it is "empty" I assume you're expecting a string. Before the execute try:

    Output = Space(24) or Fill( "x", 24)

    if you are expecting a maximum of 24 characters back.

    Apart from that what other folk said - declare it as a function on your transaction object or as a datawindow. If you're expecting just a single value, a database function rather than SP would be preferable.

    HTH, Paul

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.