• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Cannot use an OUTPUT option when passing a constant to a stored procedure

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

Resolved Cannot use an OUTPUT option when passing a constant to a stored procedure

  1. How-to
0
Votes
Undo
  1. Aleš Vojáček
    Aleš Vojáček
  2. PowerBuilder
  3. Wednesday, 1 March 2023 12:50 PM UTC

Hi all,

We are usig PB2022 with 22.0.0.1892 runtime.

I have problem with this code :

STRING FAZE
decimal{0} id

DECLARE GET_ID PROCEDURE FOR GET_ID @ID = :ID OUT;
faze="EXECUTE"
EXECUTE GET_ID;
if SQLCA.sqlcode=-1 then GOTO ERR
faze="FETCH"
FETCH GET_ID INTO :ID;
if SQLCA.sqlcode=-1 then GOTO ERR
CLOSE GET_ID;

return ID

err: .............

 

On production server SQL Server 2017 database in 2008 compatablity mode

this command EXECUTE GET_ID;

ends with error : Cannot use an OUTPUT option when passing a constant to a stored procedure.

It works ok on our dev SQL server 2016 database in 2008 compatablity mode

Connection string has set those options CommitOnDisconnect='No',DateTimeAllowed='Yes',BindSPInput=0,DisableBind=1,StaticBind=0, CursorLock='Opt',CursorScroll='Forward',OptSelectBlob=1

DBMS = "MSOLEDBSQL SQL Server"

in query profiler  on our dev server I can see those statements which are related to it:

exec [Essox_E34].[sys].sp_procedure_params_100_rowset N'GET_ID',1,N'dbo',NULL

declare @p1 nvarchar(80)  set @p1=N'2133861414'  exec GET_ID @p1 output  select @p1

 

Datawindow Zoom causing RowFocuschanging
PB 2022 Native SCC issue
Who is viewing this page
Fernando Manuel Murgueytio Urbizagastegui
Accepted Answer
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 2 March 2023 22:30 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi,

Luckily I had an AdventureWorks database on one of my pc's and also sqlserver already installed.

This is the stored procedure that I created:

create PROCEDURE [dbo].[GET_ID] 
	@ID decimal(10,0) output
AS
BEGIN

SELECT @ID=1000
END;

grant execute on dbo.get_id to public;

And see attached a small sample application that demonstrates the use of it. (PB 2022).

[EDIT] I have replaced my servername and user and password with "<fill this in>", just do a search on "<fill this in>" so you can adapt it to connect with your info. I'm also using AdventureWorks as the database, so you probably want to change that also.

For me it works. I'm passing in a value of 5.0 and I'm getting back the 1000 value.

The stored procedure is declared as an external function in the n_transaction object. It's a SubRoutine and not a Function, since it doesn't return any value.

Then in the application object, you have to specify n_transaction to replace sqlca:

Hope you get it working.

Regards.

 

Attachments (1)
sqlserverSP.zip
Comment
Load more comments
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 6 March 2023 10:18 AM UTC
Yw, I hope it works,

regards.
  1. Helpful
Aleš Vojáček
  1. Aleš Vojáček
  2. Tuesday, 14 March 2023 19:30 PM UTC
Hi,

so I have positive output from testing on client's side so, RPC calling works.

Stil did not find why the old way works on our setup and not on client's setup.

I will rewrite calling about 100 sp calls and who knows how many nested call in our application :-(

Still little bit confusing that the problem was not foud, but we have solution thanks to you.

I'm not super positive with transaction object with 100+ RPC functions on it, but it seems that this is the way how to slove our problems :-(

Thank you Miguel a lot with solving problem
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Tuesday, 14 March 2023 20:14 PM UTC
lol, I'm glad I could help! It's a bit of work, but the RPC calls are way more stable. I also don't know exactly why in the older versions things were more solid.

regards!
  1. Helpful
There are no comments made yet.
Responses (5)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 2 March 2023 01:55 AM UTC
  2. PowerBuilder
  3. # 1

Please show us the first part of the stored procedure itself (not the PB declaration, but the CREATE .... with the input output parameters etc.). Also, does your stored procedure do a RETURN of any value?

Have you tried to follow https://docs.appeon.com/pb2019/application_techniques/ch12s03.html ? 

regards.

Comment
Aleš Vojáček
  1. Aleš Vojáček
  2. Thursday, 2 March 2023 10:54 AM UTC
CREATE PROCEDURE [dbo].[GET_ID] @ID T_ID OUTPUT

/*WITH ENCRYPTION*/ AS

BEGIN

INSERT INTO id_source (nonid) values (0)

SELECT @ID=@@IDENTITY + 1000

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

some other code, but this version has same issue as the real procedure.

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

END

  1. Helpful
Aleš Vojáček
  1. Aleš Vojáček
  2. Thursday, 2 March 2023 10:57 AM UTC
And sorry T_ID is decimal (10,0)

A.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 1 March 2023 21:39 PM UTC
  2. PowerBuilder
  3. # 2

Just another question:

Have you tried to see what happens if you do

"SQLCA.DBParm="BindSPInput=1" instead of SQLCA.DBParm="BindSPInput=0" ?

See: https://docs.appeon.com/pb2022/connection_reference/BindSPInput.html 

 

Comment
Aleš Vojáček
  1. Aleš Vojáček
  2. Thursday, 2 March 2023 09:42 AM UTC
Hi,

I did, try any combination of those parameters.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 1 March 2023 17:51 PM UTC
  2. PowerBuilder
  3. # 3

there should be a solution on https://www.experts-exchange.com/questions/24779851/powerbuilder-11-5-calling-stored-procedures-with-output-parameters.html but I really hate them for stealing everyone's code and then trying to charge you for it, so I'm not going to sign-up with them.

Comment
Load more comments
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 1 March 2023 19:06 PM UTC
Maybe this is a good place to start. https://docs.appeon.com/pb2019/application_techniques/ch12s03.html

I'm still not sure if it'll work for SQL Server though. Later this evening, I might have some time to try, I'll try to verify that, but I don't have sqlserver installed. The usual way of doing things is to declare an object of type 'transaction' and specify that object instead of SQLCA in your application object (there's a button that says 'variable' or 'other values' (can't connect to PB now). You then declare the rpcfunc stored procedure/functions as if they were 'external functions' and then from anywhere in powerbuilder you can just call them like SQLCA.MyStoredProcedure(....).

Don't forget that for any OUT parameters you will have to initialize the local string variable that you pass in (if the paremater is a string), with enough spaces to be able to receive the length of the value back from the call.
  1. Helpful
John Fauss
  1. John Fauss
  2. Wednesday, 1 March 2023 19:35 PM UTC
I can verify that defining Remote Procedure Call function declarations in a Transaction object and calling them works for SQL Server.
  1. Helpful 2
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 1 March 2023 19:39 PM UTC
Great. thanks John!
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 1 March 2023 15:26 PM UTC
  2. PowerBuilder
  3. # 4

Hi,

I think you have to name your parameters for this.

Something like suggested here: https://stackoverflow.com/questions/39593402/how-to-declare-and-execute-storedprocedure-in-powerbuilder

Not sure if in the first answer "@" should precede the parameter names and if you should use "=>".

Another thing I seem to remember, is that you might be able to use "IN OUT" instead of "OUT".

Long time since I've worked with SQL Server, so sorry if not the best answer here.

regards.

 

Comment
Load more comments
Aleš Vojáček
  1. Aleš Vojáček
  2. Thursday, 2 March 2023 09:45 AM UTC
Hi,

yes I did, but may be I'm missing something :-) Will look at it again.

A.
  1. Helpful
Aleš Vojáček
  1. Aleš Vojáček
  2. Thursday, 2 March 2023 09:47 AM UTC
I still does not see what is different in stackoverflow solution.

A.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 2 March 2023 09:57 AM UTC
In one of the answers on stackoverflow, they don't use "@" and in the second one they use "=>", might be worth trying ?

Can you please show use your stored procedure / function (NOT the pb declaration but the SQL to create it). I only want to see the initial part and any RETURN that you do of values. No need for the whole procedure. So at least I can try to figure out the external function declaration that you would have to make. Also you're not answering all of the questions. Have you tried to follow the steps as described in https://docs.appeon.com/pb2019/application_techniques/ch12s03.html ?

This is in case I really have an ocean of spare time, install sql server and try to reproduce it myself, but for now that's highly unlikely, quite busy.

regards.

  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.