How to use a Stored Procedure to return a recordset in a Visual Basic ADO program

Resolving The Problem

INTRODUCTION

The following steps describe how to use a stored procedure to return a recordset in a Microsoft® Visual Basic (VB) ADO program, via an ODBC connection.


STEPS

1. Create the Stored Procedure on your database server. The following Stored Procedure Language (SPL) creates a function on an IBM® Informix® Dynamic Server database,

CREATE FUNCTION customer_name( ) RETURNING VARCHAR(30), VARCHAR(30);

DEFINE firstname VARCHAR(30);
DEFINE surname  VARCHAR(30);

FOREACH cursor1 FOR
SELECT fname, lname INTO firstname, surname FROM customer
RETURN firstname, surname WITH RESUME;
END FOREACH

END FUNCTION;

Dim strConnection As String
Dim conADO As ADODB.Connection
Dim resADO As ADODB.Recordset
Dim strUID As String
Dim strPWD As String

strUID = "<user name>"
strPWD = "<password>"

'ODBC Connection String
strConnection = "driver={IBM INFORMIX 3.82 32 BIT}" & _
";uid=" & strUID & _
";pwd=" & strPWD & _
";database=<db_name>" & _
";host=<host>" & _
";server=<server>"  & _
";service=<service>"  & _
";protocol=onsoctcp"
 
Set conADO = New ADODB.Connection
Set resADO = New ADODB.Recordset
conADO.Open strConnection
resADO.Open "Execute function customer_name()", conADO, , , adCmdTxt
While Not resADO.EOF
List1.AddItem (resADO(0) & " " & resADO(1))
resADO.MoveNext
Wend