 | Documentation |
|---|---|
27.6. Accessing EnterpriseDB using ADOThe common way to access a database from inside an ASP page is to:
27.6.1. ASP Example using VB Script27.6.1.1. Create an ADO Connection to the EnterpriseDB DatabaseBefore a database can be accessed from a web page, a database connection has to be established. You can connect to the EnterpriseDB database with the following ASP code written using VBScript:
Dim Conn
Set Conn = Server.CreateObject( "ADODB.Connection" )
27.6.1.2. Open the Connection to the Database ServerAfter the ADO connection object is created then using that connection object, we need to load the specific driver for access to our underlying database. The following lines of code are written in ASP for opening a connection to EnterpriseDB using ADO:
Conn.ConnectionString ="DRIVER={EnterpriseDB 8.2};
SERVER=127.0.0.1;
port=5444;
DATABASE=edb;
UID=enterprisedb;
PWD=anything"
Conn.Open
If the connection is opened successfully then the following line of code would execute:
Response.Write( "Connection Successfully Opened.....<br>" )
Response.Write( "=================================================<br>" )
Response.Write( "=================================================<br>" )
Response.Write( "Test Case #1 <br>" )
Response.Write( "Calling procedure proc_refcursor_1 <br>" )
Response.Write( "=================================================<br>" )
Here we are calling the procedure named "proc_refcursor_1" which is stored in the EnterpriseDB database. 27.6.1.3. Create Command ObjectThen, the next step is to create the command object for sending a command to the database for record retrieval or for performing queries over the data in the database. The following lines of code are written in ASP for creating a command object and then the most recently opened connection is set as the active connection to the database:
Dim Cmd
Set Cmd = Server.CreateObject( "ADODB.Command" )
Cmd.ActiveConnection = Conn
27.6.2. For a Stored Procedure27.6.2.1. Set Command Text and Bind Parameters with It
Cmd.CommandText = "proc_refcursor_1"
The following is the signature of the procedure "proc_refcursor_1":
CREATE OR REPLACE PROCEDURE proc_refcursor_1( a OUT SYS_REFCURSOR,
b IN OUT INTEGER,
c OUT SYS_REFCURSOR,
d OUT NUMERIC,
e OUT SYS_REFCURSOR,
f IN OUT VARCHAR2 )
The following is the 1st Out REFCURSOR:
Dim OutRefCursor1
Cmd.Parameters.Append Cmd.CreateParameter( "outrefcursor1",
adLongVarChar,
adParamOutput,
20,
OutRefCursor1 )
The following is the 2nd Out INTEGER: Dim InOutInteger2
InOutInteger2 = 22
Cmd.Parameters.Append Cmd.CreateParameter( "inoutinteger2",
adInteger,
adParamInputOutput,
4,
InOutInteger2 )
The following is the 3rd Out REFCURSOR: Dim OutRefCursor3
Cmd.Parameters.Append Cmd.CreateParameter( "outrefcursor3",
adLongVarChar,
adParamOutput,
20,
OutRefCursor3 )
The following is the 4th Out NUMERIC: Dim OutNumeric4
Cmd.Parameters.Append Cmd.CreateParameter( "outnumeric4",
adNumeric,
adParamOutput,
5,
OutNumeric4 )
The following is the 5th Out REFCURSOR: Dim OutRefCursor5
Cmd.Parameters.Append Cmd.CreateParameter( "outrefcursor5",
adLongVarChar,
adParamOutput,
20,
OutRefCursor5 )
The following is the 6th Out VARCHAR: Dim OutVarchar6
InOutVarchar6 = "Hello"
Cmd.Parameters.Append Cmd.CreateParameter( "inoutvarchar6",
adVarChar,
adParamInputOutput,
41,
InOutVarchar6 )
27.6.2.2. Set Active Connection and Prepare the StatementThe next step is to set the active connection for which the following line of code is used: Cmd.ActiveConnection = Conn After this, the statement to be executed is prepared and then the transaction is started using the following lines of code:
Cmd.Prepared = true
Conn.BeginTrans()
27.6.2.3. Set Command Type and Execute the ProcedureThen, set the command type to a stored procedure and after that execute that stored procedure from the EnterpriseDB database using the following lines of code:
Cmd.CommandType = 4
Dim rs
Set rs = Cmd.Execute()
27.6.2.4. Fetch Each Field of Recordset ObjectAfter that each field of the Recordset object is fetched using a loop which iterates till the end of the Recordset is reached. The fields of the Recordset object are fetched and then they are displayed on the screen along with their respective values.
Dim f1
Do Until rs Is Nothing
Response.Write( "<table border=" )%>"3" bordercolor="black">
<%
Do Until rs.EOF
Response.Write( "<tr>" )
For Each f1 in rs.Fields
Response.Write( "<td>" & f1.Value & "</td>" )
Next
Response.Write( "</tr>" )
rs.MoveNext()
Loop
Response.Write( "</table>" )
Response.Write( "<br>" )
Set rs = rs.NextRecordset
After the end of the current Recordset is reached, then the Recordset object is closed. If rs.State = 0 Then
Response.Write( "<br>Recordset object is closed<br>" )
Exit Do
End If
Response.Write( "<br><b>Next Recordset</b><br><br><br>" )
Loop
The fetching process is repeated for every Recordset in a REFCURSOR. After all the Recordsets have been processed that were in a REFCURSOR then the last Recordset object is also closed using the following lines of code: If Not rs.State = 0 Then
rs.Close()
End If
Set rs = nothing
27.6.2.5. Close the ADO Command ObjectThe following line of code is used for closing the ADO command object: Set Cmd = nothing
27.6.2.6. Commit the TransactionThe following line of code is used for committing the transaction: Conn.CommitTrans()
27.6.3. For a FunctionFor calling a function, steps 1, 2 and 3 are the same. In step 4, the following is done: Cmd.CommandText = "func_refcursor_1"
The following is the signature of the function "func_refcursor_1" that is being called: CREATE OR REPLACE FUNCTION func_refcursor_1( a OUT SYS_REFCURSOR,
b IN OUT INTEGER,
c OUT SYS_REFCURSOR,
d OUT NUMERIC,
e OUT SYS_REFCURSOR,
f IN OUT VARCHAR2 )
RETURN TIMESTAMP
Here in case of calling a function, there is a return type also so we also need to do the following: Dim RetTimeStamp
Cmd.Parameters.Append Cmd.CreateParameter( "retTimestamp", adDBTimeStamp, adParamReturnValue, 8, RetTimeStamp )
For binding the parameters to the command, the following is done based on the signature of our function being called: The following is the 1st Out REFCURSOR: Cmd.Parameters.Append Cmd.CreateParameter( "outrefcursor1", adLongVarChar, adParamOutput, 20, OutRefCursor1 )
The following is the 2nd Out INTEGER: InOutInteger2 = 125
Cmd.Parameters.Append Cmd.CreateParameter( "inoutinteger2",
adInteger,
adParamInputOutput,
4,
InOutInteger2 )
The following is the 3rd Out REFCURSOR: Cmd.Parameters.Append Cmd.CreateParameter( "outrefcursor3", adLongVarChar, adParamOutput, 20, OutRefCursor3 )
The following is the 4th Out NUMERIC: Cmd.Parameters.Append Cmd.CreateParameter( "outnumeric4", adNumeric, adParamOutput, 5, OutNumeric4 )
The following is the 5th Out REFCURSOR: Cmd.Parameters.Append Cmd.CreateParameter( "outrefcursor5", adLongVarChar, adParamOutput, 20, OutRefCursor5 )
The following is the 6th Out Varchar: InOutVarchar6 = "Myyyyy"
Cmd.Parameters.Append Cmd.CreateParameter( "inoutvarchar6",
adVarChar,
adParamInputOutput,
41,
InOutVarchar6 )
Steps 5 and 6 are the same. 27.6.3.1. Fetch Each Field of Recordset ObjectFor fetching each field of the Recordset Object, the following lines of code are written:
Do Until rs Is Nothing
Response.Write( "<table border=" )%>"3" bordercolor="black">
<%
Do Until rs.EOF
Response.Write( "<tr>" )
For Each f1 in rs.Fields
Response.Write( "<td>" & f1.Value & "</td>" )
Next
Response.Write( "</tr>" )
rs.MoveNext()
Loop
Response.Write( "</table>" )
Response.Write( "<br>" )
Set rs = rs.NextRecordset
The Recordset object is closed: If rs.State = 0 Then
Response.Write( "<br>Recordset object is closed<br>" )
Exit Do
End If
Response.Write( "<br><b>Next Recordset</b><br><br><br>" )
Loop
Here the Recordset object is being closed: If Not rs.State = 0 Then
rs.Close()
End If
Set rs = nothing
Steps 8 and 9 are also the same. 27.6.4. Another Example of a Function CallThe following is the signature of function "func_refcursor_2" CREATE OR REPLACE FUNCTION func_refcursor_2( a OUT SYS_REFCURSOR,
b IN OUT INTEGER,
c OUT SYS_REFCURSOR,
d OUT NUMERIC,
f IN OUT VARCHAR2 )
RETURN SYS_REFCURSOR
Similarly, steps 3 to 9 are repeated for this function as well. 27.6.5. Visual C++ Example27.6.5.1. Including Header FilesInclude the following header files: #include <Windows.h>
#include <stdio.h>
#include <tchar.h>
#include <time.h>
#include <msado15.h>
27.6.5.2. Macros
#ifdef SAFE_RELEASE
#undef SAFE_RELEASE
#endif // SAFE_RELEASE
#define SAFE_RELEASE( Obj )
if( Obj != NULL )
Obj->Release();
Obj = NULL
27.6.5.3. Global Variables
// {00000514-0000-0010-8000-00AA006D2EA4}
static const GUID ___Connection = { 0x00000514,
0x0000,
0x0010,
{ 0x80,
0x00,
0x00,
0xAA,
0x00,
0x6D,
0x2E,
0xA4 } };
// {00000507-0000-0010-8000-00AA006D2EA4}
static const GUID ___Command ={ 0x00000507,
0x0000,
0x0010,
{ 0x80,
0x00,
0x00,
0xAA,
0x00,
0x6D,
0x2E,
0xA4 } };
27.6.5.4. Create ADO Connection Object and Connect to the Database Server
HRESULT ConnectDB( OUT ADOConnection **ppConn )
{
HRESULT hr;
if( ppConn == NULL )
return E_POINTER;
Here the connection object is created: hr = CoCreateInstance( ___Connection, NULL, CLSCTX_INPROC_SERVER, __uuidof(ADOConnection), (void **)ppConn );
if( FAILED( hr ) )
return hr;
Now, connection to the EnterpriseDB database is established using the following piece of code: BSTR wszConn = ::SysAllocString( L"DRIVER={EnterpriseDB 2006};SERVER=127.0.0.1;port=5444;DATABASE=edb;" );
BSTR wszUser = ::SysAllocString( L"edb" );
BSTR wszPasswd = ::SysAllocString( L"" );
hr = (*ppConn)->Open( wszConn, wszUser, wszPasswd, adConnectUnspecified );
::SysFreeString( wszConn );
::SysFreeString( wszUser );
::SysFreeString( wszPasswd );
if( FAILED( hr ) )
{
SAFE_RELEASE( (*ppConn) );
return hr;
}
return hr;
}
27.6.5.5. Create Command Object
HRESULT CreateCommandObject(IN ADOConnection *pConn,OUT ADOCommand **ppCmd)
{
HRESULT hr;
if( ppCmd == NULL || pConn == NULL )
return E_POINTER;
Here the command object is being created: hr = CoCreateInstance( ___Command, NULL, CLSCTX_INPROC_SERVER, __uuidof(ADOCommand), (void **)ppCmd );
if( FAILED( hr ) )
return hr;
Here the active connection object is being set:
VARIANT varConn;
::VariantInit( &varConn );
varConn.vt = VT_DISPATCH;
hr = pConn->QueryInterface( IID_IDispatch, (void **)&(varConn.pdispVal) );
hr = (*ppCmd)->put_ActiveConnection( varConn );
::VariantClear( &varConn );
if( FAILED( hr ) )
{
SAFE_RELEASE( (*ppCmd) );
return hr;
}
return hr;
}
27.6.5.6. Bind Parameters with the Command and Execute Procedure
HRESULT
ExecStoredProc(IN ADOCommand *pCmd,OUT ADORecordset **ppRecordset)
{
HRESULT hr;
if( pCmd == NULL || ppRecordset == NULL )
return E_POINTER;
*ppRecordset = NULL;
Here we are putting the command text: BSTR wszCmd = ::SysAllocString( L"proc_refcursor_1" );
pCmd->put_CommandText( wszCmd );
::SysFreeString( wszCmd );
Here we are putting the command type: hr = pCmd->put_CommandType( adCmdStoredProc );
Here we are getting ADOParameters object for appending:
ADOParameters *pParams = NULL;
hr = pCmd->get_Parameters( &pParams );
if( FAILED( hr ) )
return hr;
BSTR wszParamName;
This is the 1st OUT REFCURSOR: wszParamName = ::SysAllocString( L"OutRefcursor1" );
This is an OUT parameter. We need to allocate a buffer as to tell ADO that we have memory allocated for OUT parameter:
VARIANT varOutRefcursor1;
::VariantInit( &varOutRefcursor1 );
varOutRefcursor1.vt = VT_BYREF|VT_I1;
varOutRefcursor1.pcVal = (CHAR *)::CoTaskMemAlloc( sizeof(CHAR)*31 );
ADOParameter *pOutRefcursor1 = NULL;
hr = pCmd->CreateParameter( wszParamName,
dLongVarChar,
adParamOutput,
sizeof(CHAR)*31,
varOutRefcursor1,
&pOutRefcursor1 );
SysFreeString( wszParamName );
if( FAILED( hr ) )
{
SAFE_RELEASE( pParams );
return hr;
}
This is Query IDispatch interface for appending:
IDispatch *pDispOutRefcursor1 = NULL;
pOutRefcursor1->QueryInterface( IID_IDispatch, (void **)&pDispOutRefcursor1 );
SAFE_RELEASE( pOutRefcursor1 );
/* Append the parameter */
hr = pParams->Append( pDispOutRefcursor1 );
SAFE_RELEASE( pDispOutRefcursor1 );
if( FAILED( hr ) )
{
SAFE_RELEASE( pParams );
return hr;
}
This is the 2nd IN/OUT INTEGER: wszParamName = ::SysAllocString( L"InOutInteger2" );
VARIANT varInOutInteger2;
::VariantInit( &varInOutInteger2 );
varInOutInteger2.vt = VT_I4;
varInOutInteger2.lVal = 125;
ADOParameter *pInOutInteger2 = NULL;
hr = pCmd->CreateParameter( wszParamName,
adInteger,
adParamInputOutput,
sizeof(LONG),
varInOutInteger2,
&pInOutInteger2 );
SysFreeString( wszParamName );
if( FAILED( hr ) )
{
SAFE_RELEASE( pParams );
return hr;
}
This is Query IDispatch interface for appending: IDispatch *pDispInOutInteger2 = NULL;
pInOutInteger2->QueryInterface( IID_IDispatch, (void **)&pDispInOutInteger2 );
SAFE_RELEASE( pInOutInteger2 );
Append the parameter: hr = pParams->Append( pDispInOutInteger2 );
SAFE_RELEASE( pDispInOutInteger2 );
if( FAILED( hr ) )
{
SAFE_RELEASE( pParams );
return hr;
}
This is 3rd OUT REFCURSOR(): wszParamName = ::SysAllocString( L"OutRefcursor3" );
This is an OUT parameter. We need to allocate a buffer so as to tell ADO that we have memory allocated for OUT parameter: VARIANT varOutRefcursor3;
::VariantInit( &varOutRefcursor3 );
varOutRefcursor3.vt = VT_BYREF|VT_I1;
varOutRefcursor3.pcVal = (CHAR *)::CoTaskMemAlloc( sizeof(CHAR)*31 );
ADOParameter *pOutRefcursor3 = NULL;
hr = pCmd->CreateParameter( wszParamName,
adLongVarChar,
adParamOutput,
sizeof(CHAR)*31,
varOutRefcursor3,
&pOutRefcursor3 );
SysFreeString( wszParamName );
if( FAILED( hr ) )
{
SAFE_RELEASE( pParams );
return hr;
}
This is Query IDispatch interface for appending: IDispatch *pDispOutRefcursor3 = NULL;
pOutRefcursor3->QueryInterface( IID_IDispatch, (void **)&pDispOutRefcursor3 );
SAFE_RELEASE( pOutRefcursor3 );
Append the parameter: hr = pParams->Append( pDispOutRefcursor3 );
SAFE_RELEASE( pDispOutRefcursor3 );
if( FAILED( hr ) )
{
SAFE_RELEASE( pParams );
return hr;
}
This is the 4th OUT NUMERIC: wszParamName = ::SysAllocString( L"OutNumeric4" );
This is an OUT parameter. We need to allocate a buffer so as to tell ADO that we have memory allocated for OUT parameter: VARIANT varOutNumeric4;
::VariantInit( &varOutNumeric4 );
varOutNumeric4.vt = VT_BYREF|VT_DECIMAL;
varOutNumeric4.pdecVal = (DECIMAL *)::CoTaskMemAlloc( sizeof(DECIMAL) );
ADOParameter *pOutNumeric4 = NULL;
hr = pCmd->CreateParameter( wszParamName,
adNumeric,
adParamOutput,
sizeof(DECIMAL),
varOutNumeric4,
&pOutNumeric4 );
SysFreeString( wszParamName );
if( FAILED( hr ) )
{
SAFE_RELEASE( pParams );
return hr;
}
This is the Query IDispatch interface for appending: IDispatch *pDispOutNumeric4 = NULL;
pOutNumeric4->QueryInterface( IID_IDispatch, (void **)&pDispOutNumeric4 );
SAFE_RELEASE( pOutNumeric4 );
Append the parameter: hr = pParams->Append( pDispOutNumeric4 );
SAFE_RELEASE( pDispOutNumeric4 );
if( FAILED( hr ) )
{
SAFE_RELEASE( pParams );
return hr;
}
This is the 5th OUT REFCURSOR: wszParamName = ::SysAllocString( L"OutRefcursor5" );
This is an OUT parameter. We need to allocate a buffer so as to tell ADO that we have memory allocated for OUT parameter: VARIANT varOutRefcursor5;
::VariantInit( &varOutRefcursor5 );
varOutRefcursor5.vt = VT_BYREF|VT_I1;
varOutRefcursor5.pcVal = (CHAR *)::CoTaskMemAlloc( sizeof(CHAR)*31 );
ADOParameter *pOutRefcursor5 = NULL;
hr = pCmd->CreateParameter( wszParamName,
adLongVarChar,
adParamOutput,
sizeof(CHAR)*31,
varOutRefcursor5,
&pOutRefcursor5 );
SysFreeString( wszParamName );
if( FAILED( hr ) )
{
SAFE_RELEASE( pParams );
return hr;
}
This is the Query IDispatch interface for appending: IDispatch *pDispOutRefcursor5 = NULL;
pOutRefcursor5->QueryInterface( IID_IDispatch, (void **)&pDispOutRefcursor5 );
SAFE_RELEASE( pOutRefcursor5 );
Append the parameter here: hr = pParams->Append( pDispOutRefcursor5 );
SAFE_RELEASE( pDispOutRefcursor5 );
if( FAILED( hr ) )
{
SAFE_RELEASE( pParams );
return hr;
}
This is for the 6th IN/OUT VARCHAR: wszParamName = ::SysAllocString( L"InOutVarchar6" );
OLECHAR szVal[255];
::memset( szVal, 0, sizeof(OLECHAR)*255 );
::lstrcpyW( szVal, L"Hello" );
VARIANT varInOutVarchar6;
::VariantInit( &varInOutVarchar6 );
varInOutVarchar6.vt = VT_BSTR;
varInOutVarchar6.bstrVal = ::SysAllocStringLen( szVal, 255 );
ADOParameter *pInOutVarchar6 = NULL;
hr = pCmd->CreateParameter( wszParamName,adVarChar,adParamInputOutput,255,varInOutVarchar6,&pInOutVarchar6 );
SysFreeString( wszParamName );
if( FAILED( hr ) )
{
SAFE_RELEASE( pParams );
return hr;
}
This is the Query IDispatch interface for appending: IDispatch *pDispInOutVarchar6 = NULL;
pInOutVarchar6->QueryInterface( IID_IDispatch, (void **)&pDispInOutVarchar6 );
SAFE_RELEASE( pInOutVarchar6 );
Append the parameter here: hr = pParams->Append( pDispInOutVarchar6 );
SAFE_RELEASE( pDispInOutVarchar6 );
if( FAILED( hr ) )
{
SAFE_RELEASE( pParams );
return hr;
}
|
