[转帖]如何在PowerBuilder中调用MS SQL SERVER的存储过程

ehxz 发表于 2007-6-26 12:10:41

<table cellspacing="0" cellpadding="0" width="681" border="0"><tbody><tr><td valign="top" width="651"><table height="1760" cellspacing="0" cellpadding="0" width="100%"><tbody><tr><td height="1762"><center><table width="97%" border="0"><tbody><tr><td>Overview
The examples in this document are intended to extend the information provided with our Online Documentation and manuals. How do I get the output parameters back from the server if my stored procedure is also returning a resultset?
There are a couple of different ways to get the RESULTSET and OUTPUT parameter from a stored procedure.Code Sample 1: DECLARE, EXECUTE and FETCH ResultSet and Output parameters: The first is to declare, execute and fetch the resultset and then when the sqlcode = 100 get out of loop and fetch the procedure a second time.  The code would look like this: //declare local variable
 LONG l_parm1
 LONG l_out_parm
STRING s_message
CONNECT USING SQLCA;
//Initialize the input parameter - this could be hard coded.
 l_parm1 = 35
DECLARE testproc PROCEDURE FOR dbo.testproc @Parm1 = :l_parm1, @OutParm = :l_out_parm OUTPUT USING SQLCA;
 EXECUTE testproc;
 //First, fetch the RESULTSET
 do while sqlca.sqlcode = 0
 FETCH testproc INTO :s_message;
 if sqlca.sqlcode = 0 then
 MessageBox( "s_message", s_message)
 end if
loop
 //Now fetch the OUTPUT PARM
 FETCH testproc INTO :l_out_parm;
 MessageBox( "l_out_parm", String(l_out_parm))
 CLOSE testproc;
 DISCONNECT USING SQLCA;
The second way this can be accomplished is by using the RPC method.  This is discussed in the "Application Techniques" manual. Code sample 2:
  Dynamic SQL Format 4 declaring a stored procedure The sample in our help file illustrates ways to use format 4 using a declared cursor.  This script uses Format 4 embedded SQL statements and a declared stored procedure. This example assumes you know that there will be only one output descriptor and that it will be an integer. You can expand this example to support any number of output descriptors and any data type by wrapping the CHOOSE CASE statement in a loop and expanding the CASE statements. string ls_procname, ls_sql, ls_Temp
 int li_job_id, li_Ctr, li_Temp
 int li_rtn
li_job_id = dw_emp.getitemNumber(1, "job_id")
 setNull(li_job_id)
ls_procname = 'pr_405237'
 ls_sql = 'execute ' + ls_procname + ' @job_id=' + '?'
PREPARE SQLSA FROM :ls_sql using sqlca;
 DESCRIBE SQLSA INTO SQLDA ;
DECLARE my_procudure DYNAMIC PROCEDURE FOR SQLSA ;
 li_rtn = SQLDA.SetDynamicParm(1, li_job_id)
 sle_1.Text = String(li_rtn)
 EXECUTE DYNAMIC my_procudure USING DESCRIPTOR SQLDA ;
 FETCH my_procudure USING DESCRIPTOR SQLDA ;
 If Sqlca.Sqlcode <> 0 then
     Messagebox("Error ", String(Sqlca.Sqlcode) + sqlca.sqlerrtext)
  else
        for li_Ctr = 1 to sqlda.NumOutputs
           CHOOSE CASE SQLDA.OutParmType
             CASE TypeString!
               ls_Temp = GetDynamicString(SQLDA, li_Ctr)
             CASE TypeInteger!
               li_Temp = GetDynamicNumber(SQLDA, li_Ctr)
           END CHOOSE
        next
   end if
CLOSE my_procedure ;
</td></tr></tbody></table></center></td></tr></tbody></table></td></tr><tr><td width="651"></td></tr></tbody></table>

页: [1]

PowerBuilder(PB)BBS社区's Archiver

[转帖]如何在PowerBuilder中调用MS SQL SERVER的存储过程