Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved DECLARE AND EXECUTE Stored Procedures don't work with SQL Server

  1. Issue
0
Votes
Undo
  1. Richard Donagrandi
    Richard Donagrandi
  2. PowerBuilder
  3. Wednesday, 10 April 2024 20:49 PM UTC

I have a stored procedure in SQL Server:

CREATE PROCEDURE [GROUP1].[test_return]( @OUTVALUE integer OUT ) AS
begin
     SET @OUTVALUE = 999999;
     RETURN
end

In Powerbuilder, I've set up a DECLARE and EXECUTE statement:

long ll_result

DECLARE test_result PROCEDURE FOR group1.test_return @OUTVALUE = :ll_result OUTPUT;
EXECUTE test_result;


MESSAGEBOX('RESULT','Test OUTPUT: ' + string(ll_result) + CHAR(13) + CHAR(10) + sqlca.sqlerrtext, StopSign!)

--------

...And my result is always 0 with sqlerrtext reporting: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Error converting data type varchar to int

I am using Powerbuilder 2022 R2 build 2819 and SQL Server 2022

The workaround is to put a local external function call in the n_trans object like so:

         function long test_return(ref long OUTVALUE) RPCFUNC ALIAS FOR "group1.test_return"

But I shouldn't have to do this. This is a bug.

 

  1. SQL Server
  2. Stored Procedure
How to disable the theme for datawindow when the t...
PB(2022) IDE, how can a custom menu icon for DWSyn...
Responses (4)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 11 April 2024 14:37 PM UTC
  2. PowerBuilder
  3. # 1

Hi Richard;
  FWIW: I would have coded the SP as follows ...

DROP  Procedure test_return;
CREATE PROCEDURE test_return  
AS
    BEGIN
      Select 99999 as OutValue
    END
;

Execute test_return  ;


When  I run the above SP in the DB Painter connected to SS2022, I get the following results...

HTH

Regards .. Chris

 

Comment
Richard Donagrandi
  1. Richard Donagrandi
  2. Thursday, 11 April 2024 15:35 PM UTC
This doesn't work, because we need that value to populate a reference variable. I'm also following the example in the documentation for SQL Server DECLARE and EXECUTE. The documentation I have doesn't include the PBNewSPInvocation dbparm setting, and the online documentation is incomplete (there's no description of this DBPARM outside its casual mention in the ODBC DECLARE documentation)...
  1. Helpful
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 11 April 2024 02:43 AM UTC
  2. PowerBuilder
  3. # 2

Hi, Richard - 

Perhaps the information in the following link may help:

    https://docs.appeon.com/pb2022r2/connecting_to_your_database/XREF_31495_ODBC_DECLARE_and.html

Best regards, John

Comment
Richard Donagrandi
  1. Richard Donagrandi
  2. Thursday, 11 April 2024 15:43 PM UTC
PBNewSPInvocation isn't in my documentation, for whatever reason.
  1. Helpful
John Fauss
  1. John Fauss
  2. Thursday, 11 April 2024 16:17 PM UTC
The link I provided is from the documentation for PB 2022 R2, which is what you stated you are using. PBNewSPInvocation is a setting that can be specified in the DBParm property of the transaction object (along with other properties). Here is a link to the explanation of this DBParm property in the Connection Reference publication for PB 2022 R2:

https://docs.appeon.com/pb2022r2/connection_reference/ch01s01s104.html
  1. Helpful
There are no comments made yet.
Richard Donagrandi
Richard Donagrandi Accepted Answer Pending Moderation
  1. Wednesday, 10 April 2024 22:16 PM UTC
  2. PowerBuilder
  3. # 3

What do you mean by "RMB the iSQL Pane"?  --No, I cannot get it to execute.

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 10 April 2024 23:14 PM UTC
RMB = Right Mouse Button
  1. Helpful
Richard Donagrandi
  1. Richard Donagrandi
  2. Wednesday, 10 April 2024 23:37 PM UTC
LOL... But of course! :> ... no, execute doesn't work that way either.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 10 April 2024 21:04 PM UTC
  2. PowerBuilder
  3. # 4

Hi Richard;

  Does it work this way ...

  • Connect the IDE to SS2022
  • Open the DB Painter
  • Activate the ISQL Pane
  • Type in " Execute test_result; " into the ISQL pane
  • RMB the iSQL Pane & then select "Execute".

Regards .. Chris

Comment
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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.