Runing stored procedures from VB
This is a multi-part message in MIME format. ------=_NextPart_000_0040_01BFD525.A0C53C10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Dear Collegues ! I am new in Informix, so if someone can help me accessing stored = procedures and stored functions from VB and getting results. I will be = very glad to receive some code examples. I tried trught ADODB.Comman but = did failed. Thank you in adwance. ------=_NextPart_000_0040_01BFD525.A0C53C10 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content=3D"text/html; charset=3Diso-8859-1" = http-equiv=3DContent-Type> <META content=3D"MSHTML 5.00.2920.0" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT face=3DArial size=3D2> <DIV><FONT face=3DArial size=3D2>Dear Collegues !</FONT></DIV> <DIV> </DIV> <DIV><FONT face=3DArial size=3D2>I am new in Informix, so if someone can = help me=20 accessing stored procedures and stored functions from VB and getting = results. I=20 will be very glad to receive some code examples. I tried trught = ADODB.Comman but=20 did failed.</FONT></DIV> <DIV> </DIV> <DIV><FONT face=3DArial size=3D2>Thank you in=20 adwance.</FONT></DIV></FONT></DIV></BODY></HTML> ------=_NextPart_000_0040_01BFD525.A0C53C10--
You have to use the "call" syntax to run the SP. Return values are accessed
by executing the command into a recordset. The return values are available
in the Fields collection. Here's an example for you:
A stored procedure to update the "customer" table and return the Informix
error code:
----------------------------------------------------------------------------
----------------------
create procedure U_customer(Vcust_num char(10), Vlast_name char(20),
Vfirst_name char(20),
Wcust_num char(10)) returning smallint;define return_code smallint;
define sql_err int;
define isam_err int;
define sql_text char(20);
on exception set sql_err, isam_err, sql_text
let return_code=sql_err;
return return_code;
end exception;
update customer
set (cust_num,last_name,first_name)
= (Vcust_num,Vlast_name,Vfirst_name)
where cust_num=Wcust_num;
let return_code=0;
return return_code;
end procedure;
----------------------------------------------------------------------------
----------------------
Heres the VB code to use an ADO Command object to execute the stored
procedure:
----------------------------------------------------------------------------
----------------------
' Setup SP call for customer update.
Set qryUCustomer = New ADODB.Command
qryUCustomer.CommandType = adCmdText
qryUCustomer.CommandText = "{ call U_customer(?,?,?,?) }"
Set prm = qryUCustomer.CreateParameter("Vcust_num", adChar,
adParamInput, 10)
qryUCustomer.Parameters.Append prm
Set prm = qryUCustomer.CreateParameter("Vlast_name", adChar,
adParamInput, 20)
qryUCustomer.Parameters.Append prm
Set prm = qryUCustomer.CreateParameter("Vfirst_name", adChar,
adParamInput, 20)
qryUCustomer.Parameters.Append prm
Set prm = qryUCustomer.CreateParameter("Wcust_num", adChar,
adParamInput, 10)
qryUCustomer.Parameters.Append prm
' Set the parameters for the UPDATE command object.
qryUCustomer.Parameters(0).Value = Trim(mstrCustNum)
qryUCustomer.Parameters(1).Value = Trim(mstrLastName)
qryUCustomer.Parameters(2).Value = Trim(mstrFirstName)
qryUCustomer.Parameters(3).Value = Trim(mstrKeyCustNum)
' Set the ActiveConnection of the Command object to the Connection
parameter.
qryUCustomer.ActiveConnection = CNN
' Execute the Command object into a RecordSet, and retrieve the return
code.
Set RSExecuteSP = qryUCustomer.Execute
' Set the return value to the SP return code and close the RecordSet.
intRetVal = RSExecuteSP.Fields(0)
RSExecuteSP.Close
----------------------------------------------------------------------------
----------------------
"George Mamaladze" <gerogi@marcom.de> wrote in message
news:8i4u5r$fej$1@news.xmission.com...
> Dear Collegues !
>
> I am new in Informix, so if someone can help me accessing stored =
> procedures and stored functions from VB and getting results. I will be =
> very glad to receive some code examples. I tried trught ADODB.Comman but =
> did failed.
>
> Thank you in adwance.