• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • PB2017 Error in executing SP with output parameter

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

Resolved PB2017 Error in executing SP with output parameter

  1. Advice
0
Votes
Undo
  1. Vincent Ma
    Vincent Ma
  2. PowerBuilder
  3. Monday, 14 October 2019 07:34 AM UTC

I have tried a sample test for calling stored procedure in PB 2017.

Tested SP as below

create procedure sp_vm_test
 @in_parm varchar(10),
 @out_parm varchar(20) output
as begin
 set @out_parm = @in_parm + ' return from SP'
end

Coding in PB as below

string ls_in_parm, ls_out_parm

ls_in_parm = "Hello"
ls_out_parm = ""
declare proc_vm procedure for sp_vm_test
 @in_parm = :ls_in_parm,
 @out_parm = :ls_out_parm OUTPUT
using SQLCA;
execute proc_vm;
if (SQLCA.SQLCODE <> 0 and SQLCA.SQLCODE <> 100) then
 Messagebox("Debug", "SQLCODE = " + String(SQLCA.SQLCODE) + " SQLErrTest = '" + SQLCA.SQLErrText + "'")
else
 fetch proc_vm into :ls_out_parm;
 Messagebox("Debug", ls_out_parm)
end if
close proc_vm;

If I run it by deploying to Appeon server. Prompted message is

Hello return from SP

However, if same source runs in source locally. below message is prompted.

SQLCODE = -1 SQLErrTest = 'SQLSTATE = 42000 Microsoft SQL Server Native Client 11.0 Cannot use the OUTPUT option when passing a constant to a stored procedure.'

Please advise.

 

 

 

 

SMTP SendTlsMail - 0365 connect - send problem
Parsing JSON numbers and decimal point character
Responses (6)
Vincent Ma
Vincent Ma Accepted Answer Pending Moderation
  1. Wednesday, 16 October 2019 02:37 AM UTC
  2. PowerBuilder
  3. # 1

Hi Chris

Thanks for your help again.

Your suggestion works find that I can get output from SP in PB now.

I discover this problem when I work on migrating a system from PB11 / MS SQL 2005 to PB2017 / MS SQL 2013. This sample program is for easy illustration only. I shall still trying to find a solution without code modification. Otherwise, I need to modify/test dozens of programs containing SP calling script with output parameter :(

Thanks and Regards

Vincent

Comment
There are no comments made yet.
Vincent Ma
Vincent Ma Accepted Answer Pending Moderation
  1. Wednesday, 16 October 2019 02:29 AM UTC
  2. PowerBuilder
  3. # 2

Hi Michael,

 

Thanks for your help.

- Variable declared in PB shouldn't be readonly. Referring to my testing script ls_out_parm is declared as string without any other parameter setting.

- DB connect parameter as below: -

Autocommit = True

DBMS = SNC SQL Native Client(OLE DB)

DBParm = PROVIDER='SQLNCLI11',DATASOURCE='*****',PROVIDERSTRING='database=isc_invoice_demo',PBTrimCharColumns='Yes', AppName='ISCPlus-vicma'

Notes: ***** as masked IP address of our DB server

- I don't think that it relates to DB user role, as the program works fine if I deploy it to PowerServer and run via Appeon. Same DB user login is used in both environment. 

 

I discover this problem when I work on migrating a system from PB11 / MS SQL 2005 to PB2017 / MS SQL 2013. This sample program is for easy illustration only. Hopefully, root cause can be identified without coding modification. Otherwise, I need to modify all those programs containing SP calling script with output parameter :(

 

Regards

Vincent

Comment
Michael Kramer
  1. Michael Kramer
  2. Wednesday, 16 October 2019 08:27 AM UTC
I have similar style code working perfectly in PB 2017 R3, so I'm trying to figure out where the delta is between the surroundings of your DECLARE; EXECUTE; FETCH; CLOSE; and mine same-sequence; same-INPUT/OUTPUT mix of parms.
  1. Helpful
There are no comments made yet.
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Tuesday, 15 October 2019 16:15 PM UTC
  2. PowerBuilder
  3. # 3

Hi Vincent,

I see you are using MS SQL Server.

Is your INPUT variable actually a CONSTANT or a pass-by-readonly parameter in the function calling the stored procedure?

 I will try to replicate EXACTLY your code. How is SQLCA configured?
(DBMS, DBParm, Lock, AutoCommit - except for actual login-ID and password)

What role does your login user have in the database? Are you logged in as db_owner/dbo?

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 15 October 2019 13:44 PM UTC
  2. PowerBuilder
  3. # 4

Hi Vincint;

Can you try this revised SP code, as follows ...

Create Procedure sp_vm_test
   @in_parm varchar ( 10 )
as
BEGIN
   DECLARE @out_parm  varchar ( 20 )
   SET  @out_parm = @in_parm + ' return from SP'
   SELECT @out_parm
END
;

Regards ... Chris

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Tuesday, 15 October 2019 19:07 PM UTC
Hi Vincent;

After the SP Code change above. My in-line code (like yours) and SP based DW work with that SP now ...



(04B80008): execute sp_vm_test 'Chris' (DBI_COMPILE) (0.001 MS / 9.401 MS)

(04B80008): EXECUTE:(DBI_EXECUTE) (0.725 MS / 10.126 MS)

(04B80008): GET NUMBER OF COLUMNS SELECTED:(DBI_GET_SELECT_ITEMS) (0.004 MS / 10.130 MS)

(04B80008): ^ 1 Columns

(04B80008): DESCRIBE:(DBI_DESCRIBE) (0.008 MS / 10.138 MS)

(04B80008): ,dsntype=varchar,len=21,type=VCHAR,pbt=2,dbt=1,ct=0,prec=0,scale=0

(04B80008): (DBI_SUPPORT_INPUT_PARM_BIND) (0.002 MS / 10.140 MS)

(04B80008): BIND SELECT OUTPUT BUFFER (DataWindow):(DBI_SELBIND) (0.017 MS / 10.157 MS)

(04B80008): ,dsntype=varchar,len=21,type=CHAR,pbt=1,dbt=1,ct=0,prec=0,scale=0

(04B80008): FETCH NEXT:(DBI_FETCHNEXT) (0.031 MS / 10.188 MS)

(04B80008): =Chris return from SP

(04B80008): FETCH NEXT:(DBI_FETCHNEXT) (0.001 MS / 10.189 MS)

(04B80008): *** DBI_FETCHEND *** (rc 100)

(04B80008): NEXT RESULT SET:(DBI_GET_NEXT_RESULT_SET) (0.048 MS / 10.237 MS)

(04B80008): *** DBI_RESULTEND *** (rc 100)

(04B80008): CANCEL:(DBI_CANCEL) (0.035 MS / 10.272 MS)

(04B80008): DISCONNECT:(DBI_DISCONNECT) (0.060 MS / 10.332 MS)

(04B80008): SHUTDOWN DATABASE INTERFACE:(DBI_SHUTDOWN_INTERFACE) (0.000 MS / 10.332 MS)

HTH - Regards ... Chris
  1. Helpful
There are no comments made yet.
Vincent Ma
Vincent Ma Accepted Answer Pending Moderation
  1. Tuesday, 15 October 2019 01:37 AM UTC
  2. PowerBuilder
  3. # 5

Hi Chris,

 

Thanks for your reply. I've tried your suggestion. SP can be executed without error message. However, returned SQLCODE is 100 and there is nothing returned from FETCH statement.

 

Regards

Vincent

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 14 October 2019 15:15 PM UTC
  2. PowerBuilder
  3. # 6

Hi Vincent;

  Try dropping the Output definition from the PowerScript's DECLARE statement. The Fetch should handle that aspect. The declare is mainly for mapping the input parameters.

HTH

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.