StarQuest Technical Documents

Sample ADO Code to Call a Stored Procedure and Return Output Data

Last Update: 26 June 2008
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ044

Abstract

This document provides sample Active Server Page (ASP) code that uses ADO with StarSQL to call a stored procedure and return output parameter data. This sample assumes there is already a stored procedure registered on the host that calls an existing program.

Solution

For StarSQL users connected to DB2/400, result sets cannot be returned from AS/400 stored procedures. However, results from the stored procedure can be returned using output (or input/output) parameters. The following code illustrates how to call a stored procedure and display the result data.

DB2 for OS/390 or z/OS v5 or later users can return a result set from a stored procedure and/or output data using the same method described in this document.

The sample code does the following:

  1. Connects to a StarSQL data source called "MYDSN".
  2. Creates and assigns values to one input parameter and two input/output parameters.
  3. Calls the stored procedure "CLPROG1" that is located in library "PETER".
  4. Displays the results of the input/output parameters to the screen.

Note: Since the application receives a recordset back from the Execute call, in order to receive the results of the output parameters, the recordset needs to be set to "Nothing".

'Establish the connection
strConn = "DSN=MYDSN;UID=MYUSERID;PASSWORD=MYPASS"
Set Conn=Server.CreateObject("ADODB.Connection")
Conn.Open strConn

'Define the command object
Set Cmd=Server.CreateObject("ADODB.Command")
Cmd.ActiveConnection = Conn
Cmd.CommandText = "PETER.CLPROG1"
Cmd.CommandType = 4
Cmd.Parameters.Refresh
Cmd.Properties.Refresh

'Create and append the parameters
Cmd.Parameters.Append Cmd.CreateParameter("P1",129,1,10)
Cmd.Parameters.Append Cmd.CreateParameter("P2",3,3,13)
Cmd.Parameters.Append Cmd.CreateParameter("P3",129,3,10)

'Set the parameter values
Cmd("P1")="APPLES"
Cmd("P2")=10
Cmd("P3")="ORANGES"

'Execute the "CALL" statement for the procedure. ADO 'constructs the CALL statement
set rs1=Cmd.Execute

'Free the recordset
set rs1=nothing

'Display the results
response.write "<HTML>"
response.write cmd(1)
response.write "<BR>"
response.write cmd(2)
response.write "</HTML>"

'Close the connection
Conn.Close
set Conn=nothing

Additional References

Fronckowiak, John and David Helda. Visual Basic 6: Database Programming. California: IDG Books Worldwide, 1999.

DISCLAIMER

The information in technical documents comes without any warranty. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The information in technical documents may be gathered from various sources, including IBM, Microsoft, and other organizations.