RDO Stored Procedure的呼叫

来源:cww

这Stored Procedure的呼叫是另一件令人头大的事，因为SQL Server与Informix的Stored
Procedure语法不同，使用方式不太相同，以致於透过RDO 来呼叫时，也有一些的不同

Informix的Stored Procedure呼叫
假设有两个Stored Procedure如下：
CREATE PROCEDURE GETQPPFA (PCASENO CHAR(8),PSEQ INTEGER)
   RETURNING CHAR(1), CHAR(9), CHAR(12), DECIMAL(9,2);
DEFINE psys_kind CHAR(1);
DEFINE pnckm_code CHAR(9);
DEFINE phel_code CHAR(12);
DEFINE PUNIT_PRICE DECIMAL(9,2);
--CASE_NO + SEQ 是UNIQUE的KEY值
SELECT sys_kind, nckm_code, hel_code, unit_price
       into psys_kind, pnckm_code, phel_code, punit_price
       from qppfa
       Where case_no = PCASENO and seq = PSEQ;
RETURN psys_kind, pnckm_code, phel_code, punit_price;
END PROCEDURE;


CREATE PROCEDURE GETQPPFA2 (PCASENO CHAR(8))
   RETURNING CHAR(1), CHAR(9), CHAR(12), DECIMAL(9,2);
DEFINE psys_kind CHAR(1);
DEFINE pnckm_code CHAR(9);
DEFINE phel_code CHAR(12);
DEFINE PUNIT_PRICE DECIMAL(9,2);

FOREACH 
  SELECT sys_kind, nckm_code, hel_code, unit_price
       into psys_kind, pnckm_code, phel_code, punit_price
       from qppfa
       Where case_no = PCASENO 
  RETURN psys_kind, pnckm_code, phel_code, punit_price WITH RESUME;
END FOREACH;
END PROCEDURE;

如果使用像SQL SERVER一般的方式

sql = "{ Call GETQPPFA ( ?, ?) }"
Set qry = cn.CreateQuery("MyQuery",sql)
qry.rdoParameters(0) = "E8701761"
qry.rdoParameters(1) = 1
qry.Execute
那麽，不会有结果集传回来，这是自然的，因为sql字串中只有两个问号，代表传入的两
个叁数，而没有传出叁数(如果把OutPut的叁数也加进来，即多4个问号，会有错)。
这时候该如以下的方式来做：
Private WithEvents cn   As rdoConnection
Private en As rdoEnvironment
Private rs As rdoResultset
Private qry As rdoQuery

Dim connstr As String
Set en = rdoEnvironments(0)
Set cn = New rdoConnection

cn.CursorDriver = rdUseServer
'或者 设定为rdUseNone也可以
connstr = "UID=cww;PWD=jjh5612;Database=cwwpf@eis;" _
        + "Driver={OpenLink Generic 32 Bit Driver};" _
        + "Host=192.168.0.61;" _
        + ";FetchBufferSize=30" _
        + ";NoLoginBox=Yes" _
        + ";Options=" _
        + ";Protocol=TCP/IP" _
        + ";ReadOnly=No" _
        + ";ServerOptions=" _
        + ";ServerType=Informix 7.2"
cn.Connect = connstr

cn.EstablishConnection rdDriverNoPrompt, False
Dim sql As String
sql = "Execute procedure getqppfa ('E8701761', 1)"
Set qry = cn.CreateQuery("MyQuery", sql)
Set rs = qry.OpenResultset(rdOpenForwardOnly, _
       rdConcurReadOnly, rdExecDirect)


如此，当作一个Row的Resultset传回，这里要强调的，只能用rdOpenForwardOnly与
rdConcurReadonly的方式来开启，否则不会传回任何东西。另外，有没有设定rdExecDirect
在这里不会影响，都会有传回值。

如果想要以叁数查询的方式来做，就得如下：
Private WithEvents cn   As rdoConnection
Private en As rdoEnvironment
Private rs As rdoResultset
Private qry As rdoQuery

Dim connstr As String
Set en = rdoEnvironments(0)
Set cn = New rdoConnection

cn.CursorDriver = rdUseServer
'或者 设定为rdUseNone也可以
connstr = "UID=cww;PWD=jjh5612;Database=cwwpf@eis;" _
        + "Driver={OpenLink Generic 32 Bit Driver};" _
        + "Host=192.168.0.61;" _
        + ";FetchBufferSize=30" _
        + ";NoLoginBox=Yes" _
        + ";Options=" _
        + ";Protocol=TCP/IP" _
        + ";ReadOnly=No" _
        + ";ServerOptions=" _
        + ";ServerType=Informix 7.2"
cn.Connect = connstr

cn.EstablishConnection rdDriverNoPrompt, False
Dim sql As String
sql = "Execute procedure getqppfa2 ( ? )"  '设定是叁数查询的方式
Set qry = cn.CreateQuery("MyQuery", sql)
qry.rdoParameters(0) = "E8701761"          '给定叁数
Set rs = qry.OpenResultset(rdOpenForwardOnly, _
       rdConcurReadOnly)

'do something

qry.rdoParameters(0) = "E8703069"          '重设叁数
rs.Requery                                 '重新查询 

在这个例子中，Resultset的Row不只一笔，使用的方式没有什麽不同，但这是使用叁数
查询的方式，而叁数查询的OpenResultset方法，不可以使用rdExecDirect叁数，否则没
有办法做叁数型态的转换工作，如果真的使用了，不会有错只是没有结果集传回便是了。
而如果想改用其他的数查询，那在改变叁数值後使用rs.Requery便可以了，不要再使用
OpenResultset重新开启，否则RDO会建立一个新的暂时性Stored Procedure并执行该查
询(若使用了rdExecDirect则不会建立暂时性Stored Procedure)。

SQL SERVER 6.5的Stored Procedure呼叫

假设有个Stored Procedure如下
CREATE PROCEDURE getqppfa
    @pcaseno CHAR(8),  @pseq    INT,
    @OUTVAL INT OUTPUT     
 AS
    SELECT @OUTVAL = (Select Count(*) from qppfa where case_no = @pcaseno)
    IF @OUTVAL > 0
       RETURN 1
    ELSE
       RETURN 0
GO


CREATE PROCEDURE getqppfa2
    @pcaseno CHAR(8)
 AS
    SELECT sys_kind, nckm_code, hel_code unit_price
         FROM QPPFA
         WHERE case_no = @pcaseno 
GO

相对应的STORED PROCEDURE呼叫如下：(传回值的呼叫方式)

传回值的呼叫方式

Private WithEvents cn   As rdoConnection
Private en As rdoEnvironment
Private rs As rdoResultset
Private qry As rdoQuery

Dim connstr As String
Set en = rdoEnvironments(0)
Set cn = New rdoConnection

cn.CursorDriver = rdUseServer
connstr = "DSN=SQLSRV;UID=cww;PWD=jjh5612;"
cn.Connect = connstr

cn.EstablishConnection rdDriverNoPrompt, False
Dim sql As String
sql = "{ ? = call GETQPPFA (?, ?, ?) }"
'一共有四个叁数，第一个接收Stored Procedure的Return之值
'第2-4个叁数别对应@pcaseno , @pseq , @OUTVAL 
Set qry = cn.CreateQuery("MyQuery", sql)
qry.rdoParameters(1) = "E8701761"  
qry.rdoParameters(2) = 1
qry.rdoParameters(3).Direction = rdParamOutput '设定第三个叁数是OUTPUT
qry.Execute
Debug.Print qry.rdoParameters(0), qry.rdoParameters(3)
'如果有资料，则qry.rdoParameters(0)传回1，qry.rdoParameters(3)传回笔数


传回Resultset

Private WithEvents cn   As rdoConnection
Private en As rdoEnvironment
Private rs As rdoResultset
Private qry As rdoQuery

Dim connstr As String
Set en = rdoEnvironments(0)
Set cn = New rdoConnection

cn.CursorDriver = rdUseServer
connstr = "DSN=SQLSRV;UID=cww;PWD=jjh5612;"
cn.Connect = connstr
cn.EstablishConnection rdDriverNoPrompt, False

Dim sql As String
sql = "{ call  GETQPPFA2 (?) }"
Set qry = cn.CreateQuery("MyQuery", sql)
qry.rdoParameters(0) = "E8701761"

Set rs = qry.OpenResultset(rdOpenKeyset, _
       rdConcurReadOnly)

'do something      

qry.rdoParameters(0) = "E8703069"
rs.Requery

除了sql字串的设定不同之外，和Informix是差不多的，唯使用SQL Server当後端者，其
OpenResultset可以是rdOpenForwardOnly/rdOpenStatic/rdOpenkeyset等都没有关系，
但是，如果Stored Procedure由Select 指令取出的Resultset，是没有办法Update/Insert
的，这应很直觉，即然使用Select的查询指令，那当然不能新增修改(除非Stored Procedure
用的是Insert /Update/Delete 等SQL1语法)。