Karachi   ->   Sweden   ->   Karachi, again   ->   Dubai   ->   Bahrain   ->   Karachi, once more   ->   London and Leeds

Tuesday, March 18, 2008

Calling Sybase Stored Procedures through ADO

A few days ago I downloaded evaluation version of Sybase Adaptive Server Enterprise. The database is popularly known as Sybase. Here is some brief history of the database, and here is another very interesting thread on how Microsoft adapted Sybase as SQL Server.

Coming back to the topic intended for this post, below is a very small stored procedure which does almost nothing interesting (this, however, requires you to create a table named Test_Account with at least two integer columns, namely AccountId and Balance):

create procedure dbo.sp_getBalance
@pAccountId integer,
@oBalance integer output
as
begin
select @oBalance = Balance
from Test_Account where AccountId = @pAccountId
end


Calling this from Visual Basic is very simple. Below code has been tested with Sybase ASE OLEDB Provider:

Set Connect = New ADODB.Connection
Connect.CursorLocation = adUseServer
Connect.Provider = "ASEOLEDB"
Connect.ConnectionString = "Data Source=jaywalker:5000;Database=SybaseSPs;UID=sa;PWD=;"
Connect.Open

sqlString = "sp_getBalance"
Dim cmd As New ADODB.Command
cmd.CommandText = sqlString
cmd.CommandType = adCmdStoredProc
Set cmd.ActiveConnection = Connect

Call cmd.Parameters.Append(cmd.CreateParameter("@pAccountId", adInteger, adParamInput, 4, 3))
Call cmd.Parameters.Append(cmd.CreateParameter("@oBalance", adInteger, adParamOutput))
cmd.Execute

MsgBox cmd.Parameters("@oBalance").Value


The same can be coded (without exception handling) in VC++ as follows:

CoInitialize(NULL);
_CommandPtr sybCmd ("ADODB.Command");
_ConnectionPtr sybConn ("ADODB.Connection");

sybConn->CursorLocation = adUseServer;
sybConn->Provider = "ASEOLEDB";
_bstr_t connString = "Data Source=jaywalker:5000;Database=SybaseSPs;UID=sa;PWD=;";
sybConn->Open (connString, "", "", adConnectUnspecified);

bstr_t sql = "sp_getBalance";
sybCmd->ActiveConnection = sybConn;
sybCmd->CommandType = adCmdStoredProc;
sybCmd->CommandText = sql;

sybCmd->Parameters->Append (sybCmd->CreateParameter ("@pAccountId", adInteger, adParamInput, sizeof(int), 3));
sybCmd->Parameters->Append (sybCmd->CreateParameter ("@oBalance", adInteger, adParamOutput, sizeof(int)));

sybCmd->Execute(NULL, NULL, adExecuteNoRecords);

long val = sybCmd->Parameters->Item["@oBalance"]->Value;

CoUninitialize();




Years ago, I wrote a similar article on calling Oracle Stored Procedures through ADO from both Visual Basic 6 and VC++ 6. That page became quite popular with the passage of time.