Publish date: 2003-01-16
Stored procedure. Use of property std_exec_sp (VB)
'SAMPLE OF PURPOSE OF 'STD_EXEC_CALL' property Sub sample_8_3() Dim cn As New ADODB.Connection cn.Provider = "LCPI.IBProvider" cn.Open "data source=main:e:\database\employee.gdb", "gamer", "vermut" cn.BeginTrans 'table with customers Dim customers As New ADODB.Recordset 'opening with support of bi-directional access (adOpenKeyset) customers.Open "customer", cn, adOpenKeyset, adLockReadOnly 'command for execute STORED PROCEDURE MAIL_LABEL Dim cmd As New ADODB.Command cmd.ActiveConnection = cn 'std_exec_sp=true ---------------------------------------------------- 'translate SQL 'exec SP(...)' to 'execute procedure SP(...)' 'reception of result through OUT-parameters Debug.Print "STD_EXEC_SP=TRUE" cmd.Properties("std_exec_sp") = True cmd.CommandText = "exec MAIL_LABEL(:cust_no)" 'goto to first row customers.MoveFirst While (Not customers.EOF) cmd("cust_no") = customers("cust_no") cmd.Execute 'print result Debug.Print "cust_no:" & customers("cust_no") & " - " & customers("customer") Debug.Print "-----------------------" 'OUT-parameters Debug.Print "line 1:" & cmd("line1") Debug.Print "line 2:" & cmd("line2") Debug.Print "line 3:" & cmd("line3") Debug.Print "line 4:" & cmd("line4") Debug.Print "line 5:" & cmd("line5") Debug.Print "line 6:" & cmd("line6") Debug.Print "--------------------------------------" customers.MoveNext Wend 'NOT customers.EOF 'std_exec_sp=false ----------------------------------------------------- 'translate Sql 'exec SP(...)' to 'select * from SP(...)' 'reception of result through recordset Debug.Print "STD_EXEC_SP=FALSE" cmd.CommandText = "" 'reset ADODB.Command cmd.Properties("std_exec_sp") = False cmd.CommandText = "exec MAIL_LABEL(:cust_no)" cmd.Parameters.Refresh 'goto to first row customers.MoveFirst 'recordset for processing rows from SP Dim mail_label_rs As ADODB.Recordset While (Not customers.EOF) cmd("cust_no") = customers("cust_no") Set mail_label_rs = cmd.Execute 'print result Debug.Print "cust_no:" & customers("cust_no") & " - " & customers("customer") While (Not mail_label_rs.EOF) Debug.Print "-----------------------" 'COLUMNS Debug.Print "line 1:" & mail_label_rs("line1") Debug.Print "line 2:" & mail_label_rs("line2") Debug.Print "line 3:" & mail_label_rs("line3") Debug.Print "line 4:" & mail_label_rs("line4") Debug.Print "line 5:" & mail_label_rs("line5") Debug.Print "line 6:" & mail_label_rs("line6") mail_label_rs.MoveNext Wend Debug.Print "--------------------------------------" mail_label_rs.Close customers.MoveNext Wend 'NOT customers.EOF cn.CommitTrans End Sub 'sample8_3