1. Vipin Dwivedi
  2. PowerBuilder
  3. Wednesday, 27 November 2019 19:34 PM UTC

Hi PB Appoen Folks,

Greeting! and Happy ThanksGiving!

 

What is the difference if I call the stored procedure using Select statement Vs Declaring and executing the procedure. See below sample example.

select 1 From sp_test(:a);

Vs

DECLARE Test PROCEDURE FOR
sp_test
@as_val= :a
USING SQLCA:

EXECUTE Test;
Close Test ;

Does PB ODBC process it differently? The reason why I am asking is because

Our application is getting crash at runtime at all user machine? We are suspecting the Close Procedure statement is causing this problem so trying to convert them to embedded SQL statement.

 

Please suggest.

Accepted Answer
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 27 November 2019 19:40 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi Vipin;

  If you are using a newer version of PB (ie: PB 12.6 or higher), Sybase changed the way PB processes SP calls when you are using the ODBC driver. Try adding this parameter to your DBParm field ...

    SQLCA.DBParm="PBNewSPInvocation='Yes'"

HTH

Regards ... Chris

Comment
  1. Vipin Dwivedi
  2. Wednesday, 27 November 2019 19:44 PM UTC
If I don't change DBParm and try making embedded call, will there still be chance for failure?
  1. Helpful
  1. Chris Pollach @Appeon
  2. Wednesday, 27 November 2019 19:51 PM UTC
If you are not using this new parameter, then the likelihood of failure is higher. Hard to predict though as there are many variations to utilizing SP's.
  1. Helpful
There are no comments made yet.
Vipin Dwivedi Accepted Answer Pending Moderation
  1. Thursday, 5 December 2019 21:30 PM UTC
  2. PowerBuilder
  3. # 1

Hi Chris,

I implemented the DBParm option to Transaction object to run the procedure and it went fine but now I am see two behaviour.

If I don't use SQLCA.DBParm="PBNewSPInvocation='Yes'" and if procedure does not update anything to database it returns SQLCODE as 0.

If we are using this option then the SQLCODE is returning 100. I also found that if I use this option then signature of procedure call is not taking the parenthesis "(" for the argument like

declare Test PROCEDURE FOR Administrator.sp_test(@a, @b) using SQLCA;

 

Is this a normal behaviour? Does this option change the SQLCODE value? What changes PB make when we use this option. I want to make sure to use this option before adding into my application.

 

Please suggest.

Comment
There are no comments made yet.
Vipin Dwivedi Accepted Answer Pending Moderation
  1. Wednesday, 27 November 2019 19:37 PM UTC
  2. PowerBuilder
  3. # 2

One thing I forgot to mention as well is - My procedure does not return any result set to what is the syntax to make an embedded SQL call for procedure execution?

Comment
  1. Michael Kramer
  2. Wednesday, 27 November 2019 20:49 PM UTC
I would consider using RPCFUNC on transaction object. Far less embedded SQL to write. You define the procedure as local external on transaction class. Then call it like any other function the transaction class. DBError fires if error occurs. - AND - you can still also check SQLCode etc.
  1. Helpful
  1. Vipin Dwivedi
  2. Wednesday, 27 November 2019 21:21 PM UTC
Can you give me an example for achieving this? Do you mean to say that declare the procedure as local external function in n_tr? If that would be the case you will have to change the signature each time when you change the procedure signature (Input parameter). Correct?
  1. Helpful
  1. Michael Kramer
  2. Thursday, 28 November 2019 12:00 PM UTC
That is very true! You may consider inserting a layer of inheritance between Transaction and your "n_tr_CurrentlyUsed". In that intermediate layer you could define all the local external functions as PRIVATE + add PUBLIC PowerScript functions. Each such "wrapper" function in PoewrScript pre/post-processes input/output data for that specific function. EX: A procedure having an OUTPUT string => Such poarameter requires allocating memory in PBVM up front.

This intermediate layer has one single purpose - provide easy access to each relevant user-defined functions and stored procedure. For each wrapper function you can design how you want to call it: External function; embedded SQL; DataWindow in DataStore; or some mixture.

Documentation > F1-Help > Search for "RPCFUNC"> Select topic titled "Using Transaction objects to call stored procedures".

That page introduces you to RPCFUNC and contains a 5-step how-to guide.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.