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.