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

Resolved Stored Procedures with Output Parameters

  1. How-to
0
Votes
Undo
  1. Roland Smith
    Roland Smith
  2. PowerBuilder
  3. Friday, 10 May 2019 14:55 PM UTC

PB 2017-R3 and SQL Server 2017 Express using ODBC connection.

I am able to run a stored procedure with output parameter using RPCFUNC but for some reason my client doesn't want to do it that way. When I attempt to code a DECLARE, I get a compile error when I add the OUTPUT clause and if I tell it to ignore the error, the program just disappears when I click the button.

Here is the error message:

SQLSTATE = 37000[Microsoft][SQL Server Native Client 11.0] Syntax error, permission violation, or other nonspecific error.

Here is the RPCFUNC declaration:

Function long sproc_assignuid ( Ref string cretvalue, long bfromfront ) RPCFunc Alias For "dbo.vsp_am_assignuid"

Here is my declare code:

Long ll_bFromFront
String ls_cRetValue

DECLARE sproc_assignuid PROCEDURE FOR dbo.vsp_am_assignuid
    @cretvalue = :ls_cRetValue OUTPUT,
    @bfromfront = :ll_bFromFront;

ls_cRetValue = Space(15)

EXECUTE sproc_assignuid;

If sqlca.SQLCode < 0 Then
    MessageBox( "Error Calling vsp_am_assignuid", &
    sqlca.SQLErrText, StopSign!)
    Return
End If

FETCH sproc_assignuid INTO :ls_cRetValue;

CLOSE sproc_assignuid;

MessageBox("ls_cRetValue", ls_cRetValue)

 

Was this NativePDF problem fixed?
Error while running PB2017 R3 .exe file on any mac...
Accepted Answer
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Tuesday, 14 May 2019 15:13 PM UTC
  2. PowerBuilder
  3. # Permalink

I'm using SQL Server.

I've since found that this is caused by a bug in the ODBC driver. The SNC driver works as expected.

Comment
There are no comments made yet.
Responses (2)
DIPANJAN DUTTA
DIPANJAN DUTTA Accepted Answer Pending Moderation
  1. Tuesday, 14 May 2019 06:55 AM UTC
  2. PowerBuilder
  3. # 1

Hi Roland,

 

I have used Oracle 11g in Database, where you no need to pass you output parameter in PB cursor declare statement. it is working fine.

Here is my Oracle Procedure

Create or Replace Procedure sp_get_fstbook
(as_FstBookName OUT varchar2, as_search_type IN varchar2, as_search_text IN varchar2)
As


Begin

Select  <>
Into     as_FstBookName    --OutParameter 
From   <>
Where  <>;


End;

Here is my My Power Builder Code

String ls_FstBookName, ls_InputType, ls_SearchText
ls_InputType = 'A'
ls_SearchText = 'PK'


DECLARE sproc_BookDetails PROCEDURE FOR sp_get_fstbook (
as_search_type => :ls_InputType,            //--------->Second parameter of Procedure
as_search_text => :ls_SearchText            //--------->Third parameter of Procedure
USING SQLCA;


EXECUTE sproc_BookDetails;

If sqlca.SQLCode < 0 Then
MessageBox( "Error Calling proc - sp_get_fstbook", sqlca.SQLErrText, StopSign!)
Return
End If

 

FETCH sproc_BookDetails INTO :ls_FstBookName;
If sqlca.SQLCode < 0 Then
MessageBox( "Error Fetching proc - sp_get_fstbook", sqlca.SQLErrText, StopSign!)
Return
End If

CLOSE sproc_BookDetails;

MessageBox("Book Name", ls_FstBookName)

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.