ID: Q185125
The information in this article applies to:
In order to open a stored procedure within ActiveX Data Objects (ADO), you must first open a Connection Object, then a Command Object, fill the Parameters Collection with one parameter in the collection for each parameter in the query, and then use the Command.Execute() method to open the ADO Recordset.
You may optionally use the Parameters.Refresh method to populate the Parameters Collection for the stored procedure. In addition, if the stored procedure is returning output or return parameters, you need to close the recordset before checking the value of the output parameters.
This is demonstrated in the code snippets below that deletes (if it already exists) and then creates a stored procedure, sp_adoTest, on a SQL Server that has input, output, and return parameters, as well as returning a recordset.
This article demonstrates how to perform this operation using VBA/VBScript, C++, and Java.
All of the code snippets below assume an ODBC Data Source named "CharlotteTown" that points to a SQL Server of the same name. These code snippets are abbreviated from the ADO samples listed in the REFERENCES section. Please use the full ADO samples if you want to build and run this code.
Dim Conn1 As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim Rs1 As ADODB.Recordset
Dim strTmp As String
Dim Connect As String
Dim Drop As String
Dim Create As String
Dim sp as string
Dim i As Integer
Dim l As Long
sConnect= "driver={sql server};" & _
"server=CharlotteTown;" & _
"Database=pubs;UID=sa;PWD=;"
sCreate = "create proc sp_AdoTest( @InParam int, " & _
"@OutParam int OUTPUT ) " & _
"as " & _
"select @OutParam = @InParam + 10 " & _
"SELECT * FROM Authors WHERE " & _
"State <> 'CA' " & _
"return @OutParam +10"
sDrop = "if exists " & _
"(select * from sysobjects where " & _
"id = object_id('dbo.sp_AdoTest') and " & _
"sysstat & 0xf = 4)" & _
"drop procedure dbo.sp_AdoTest"
sSP = "sp_Adotest"
' Establish connection.
Set Conn1 = New ADODB.Connection
Conn1.ConnectionString = sConnect
Conn1.Open
' Drop procedure, if it exists & recreate it.
Set Rs1 = Conn1.Execute(sDrop, l, adCmdText)
Set Rs1 = Nothing
Set Rs1 = Conn1.Execute(sCreate, l, adCmdText)
Set Rs1 = Nothing
' Open recordset.
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "sp_AdoTest"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Refresh
Cmd1.Parameters(1).Value = 10
Set Rs1 = Cmd1.Execute()
' Process results from recordset, then close it.
RS1.Close
Set Rs1 = Nothing
' Get parameters (assumes you have a list box named List1).
Debug.print vbTab & "RetVal Param = " & Cmd1.Parameters(0).Value
Debug.print vbTab & "Input Param = " & Cmd1.Parameters(1).Value
Debug.print vbTab & "Output Param = " & Cmd1.Parameters(2).Value
Set conn1 = CreateObject( "ADODB.Connection.1.5" )
#import "C:\Program Files\Common Files\System\ado\msado15.dll" \
rename( "EOF", "adoEOF" )
...
ADODB::_ConnectionPtr Conn1;
ADODB::_CommandPtr Cmd1;
ADODB::_RecordsetPtr Rs1;
_bstr_t bstrConnect( L"driver={sql server};"
L"server=CharlotteTown;"
L"Database=pubs;UID=sa;PWD=;" );
_bstr_t bstrCreate ( L"create proc sp_AdoTest( @InParam int, "
L"@OutParam int OUTPUT ) "
L"as "
L"select @OutParam = @InParam + 10 "
L"SELECT * FROM Authors WHERE "
L"State <> 'CA' "
L"return @OutParam +10" );
_bstr_t bstrDrop ( L"if exists "
L"(select * from sysobjects where "
L"id = object_id('dbo.sp_AdoTest') and "
L"sysstat & 0xf = 4)"
L"drop procedure dbo.sp_AdoTest" );
_bstr_t bstrSP ( L"sp_Adotest" );
try
{
// Establish connection.
Conn1.CreateInstance( __uuidof( ADODB::Connection ) );
Conn1->ConnectionString = bstrConnect;
Conn1->Open( bstrEmpty, bstrEmpty, bstrEmpty, -1 );
// Drop procedure, if it exists & recreate it.
Conn1->Execute( bstrDrop, &vtEmpty, ADODB::adCmdText);
Conn1->Execute( bstrCreate, &vtEmpty, ADODB::adCmdText);
// Open recordset.
Cmd1.CreateInstance( __uuidof( ADODB::Command ) );
Cmd1->ActiveConnection = Conn1;
Cmd1->CommandText = bstrSP;
Cmd1->CommandType = ADODB::adCmdStoredProc;
Cmd1->Parameters->Refresh();
Cmd1->Parameters->Item[ _variant_t( (long) 1 ) ]->Value =
_variant_t( (long) 10 );
Rs1 = Cmd1->Execute( &vtEmpty, &vtEmpty2, ADODB::adCmdUnknown );
// Process results from recordset, then close it.
Rs1->Close();
// Get parameters.
TRACE( "\tRetVal Param = %s",
CrackStrVariant( (tagVARIANT)
Cmd1->Parameters->Item[ _variant_t( 0L ) ]->Value ) );
TRACE( "\tInput Param = %s",
CrackStrVariant( (tagVARIANT)
Cmd1->Parameters->Item[ _variant_t( 1L ) ]->Value ) );
TRACE( "\tOutput Param = %s",
CrackStrVariant( (tagVARIANT)
Cmd1->Parameters->Item[ _variant_t( 2L ) ]->Value ) );
}
catch( CException *e ) { e->Delete(); }
catch(...) { }
CrackStrVariant is a function that stuffs the contents of a Variant into a CString (if possible) and is demonstrated in the Adovc.exe sample referenced below.
msado15._Connection Conn1 = new msado15.Connection();
msado15._Command Cmd1 = null;
msado15._Recordset Rs1 = new msado15.Recordset();
Variant v1 = new Variant();
Variant v2 = new Variant();
String bstrConnect = new String(
"driver={sql server};" +
"server=CharlotteTown;Database=pubs;UID=sa;PWD=;");
String bstrCreate = new String(
"create proc sp_AdoTest( @InParam int, " +
"@OutParam int OUTPUT ) " +
"as " +
"select @OutParam = @InParam + 10 " +
"SELECT * FROM Authors WHERE " +
"State <> 'CA' " +
"return @OutParam +10" );
String bstrDrop = new String(
"if exists " +
"(select * from sysobjects where " +
"id = object_id('dbo.sp_AdoTest') and " +
"sysstat & 0xf = 4)" +
"drop procedure dbo.sp_AdoTest" );
String bstrSP = new String( "sp_Adotest" );
// Trap any error/exception.
try
{
// Establish connection.
Conn1.putConnectionString( bstrConnect );
Conn1.Open( bstrEmpty, bstrEmpty, bstrEmpty, -1 );
// Drop procedure, if it exists & recreate it.
vtEmpty.noParam();
Conn1.Execute( bstrDrop, vtEmpty,
msado15.CommandTypeEnum.adCmdText);
vtEmpty.noParam();
Conn1.Execute( bstrCreate, vtEmpty,
msado15.CommandTypeEnum.adCmdText);
// Open recordset.
Cmd1= new msado15.Command();
Cmd1.putActiveConnection( Conn1 );
Cmd1.putCommandText ( bstrSP );
Cmd1.putCommandType (msado15.CommandTypeEnum.adCmdStoredProc);
Cmd1.getParameters().Refresh();
v1.putInt( 1 );
v2.putInt( 10 );
Cmd1.getParameters().getItem( v1 ).putValue( v2 );
vtEmpty.noParam();
vtEmpty2.noParam();
Rs1 = Cmd1.Execute( vtEmpty, vtEmpty2,
msado15.CommandTypeEnum.adCmdUnknown );
// Process results from recordset, then close it.
Rs1.Close();
// Get parameters (assumes you have a listbox named List1)
v1.putInt( 0 );
List1.addItem( " RetVal Param = " +
Cmd1.getParameters().getItem( v1 ).getValue() );
v1.putInt( 1 );
List1.addItem( " Input Param = " +
Cmd1.getParameters().getItem( v1 ).getValue() );
v1.putInt( 2 );
List1.addItem( " Output Param = " +
Cmd1.getParameters().getItem( v1 ).getValue() );
}
// Catch Blocks
catch (com.ms.com.ComFailException e) { }
catch(Exception e) { }
For additional information, please see the following articles in the Microsoft Knowledge Base:
ARTICLE-ID: Q172403
TITLE : SAMPLE: Adovb.exe Demonstrates How to Use ADO with Visual
Basic
ARTICLE-ID: Q174565
TITLE : FILE: Adovc.exe Demonstrates How To Use ADO with Visual C++
ARTICLE-ID: Q182782
TITLE: FILE: Adovj.exe Demonstrates How to Use ADO with Visual J++
Keywords : kbADO kbGenInfo kbVBp kbVC kbVJ
Version : WINDOWS:1.0,1.5
Platform : WINDOWS
Issue type : kbhowtoLast Reviewed: February 4, 1999