2.ADO Wrapper API

ADO Wrapper API included in ProudDB is a group of API that allows easy-to-use of Microsoft ActiveX Data Object (ADO) in C++. Through ADO Wrapper API, you can access DBMS more easily with the use of ADO. For instance, it can be applied when you need to access other database than ProudDB database such as web bulletin and etc.

Warning: ADO Wrapper API doesn't use the custom DB cache function of ProudDB. It only uses caching and pooling methods provided in MS ADO.

ADO Wrapper API is consisting of classes as followed.

2.1Accessing database with ADO

In order to access database, first generate Proud.CAdoConnection object and use Open method. Only then query can be passed to database by Proud.CAdoConnection.Execute.

void Foo()
{
    Proud::CAdoConnection conn;
    conn.Open(L"Data Source=localhost;Database=ProudDB-Test;Trusted_Connection=yes");
    
    conn.BeginTrans();  // This method begins the transaction. 
    int Val = 3;
    conn.Execute(L"delete from Table1 where FieldA=%d",Val);
    conn.CommitTrans();  // This method commits the transaction.
}

2.2Accessing recordset with ADO

To gain recordset from the result of executed query, generate Proud.CAdoRecordset object and call for Proud.CAdoRecordset.Open. To manage fields, use Proud.CAdoRecordset.FieldValues and Proud.CAdoRecordset.FieldNames.

void Foo()
{
    Proud::CAdoConnection conn;
    ...
    Proud::CAdoRecordset rs;
    rs.Open(conn,OpenForReadWrite,L"select * from Table1 where FieldA=%d",Val);
    rs.FieldValues[L"FieldA"] = Val+1;
    rs.Update();
    rs.Close();
    
    rs.Open(conn,OpenForReadWrite,L"select * from Table2",Val);
    while(rs.IsEOF() == false)
    {
        int a = rs.FieldValues[L"FieldX"];
        rs.MoveNext();
    }
}

2.3Running stored procedure with ADO

To run Stored procedure, generate Proud.CAdoCommand object, fill in parameters in field of Proud.CAdoCommand.Parameters and call for Proud.CAdoCommand.Execute.

void Foo()
{
    Proud::CAdoConnection conn;
    ...
    Proud::CAdoCommand cmd;
    cmd.Prepare(conn, L"sp_GetTable1");
    cmd.Parameters[1] = Val;   // Refers to the first parameter of stored procedure
    Proud::CAdoRecordset rs = cmd.Execute();
    long retVal = cmd.Parameters[0]; // Stored procedure gains the retuned value.
    if(retVal < 0)
    {
        ...
    }
    else
    {
        if(!rs.IsOpened())
            rs.Open(); // It becomes accessible only when the returned recordset object is open first. 
        int x = rs.FieldValues[L"FieldA"];
    }
}

When any error occurs as using ADO Wrapper API, it throws Proud.DbException type(derived class of std.exception) of exception. Following is an example of handing an exception.

void Foo()
{
    try
    {
        Proud::CAdoConnection conn;
        ...
    }
    catch(Proud::AdoException &e)
    {
        ShowException(Proud::StringA2W(e.what())); // Converts to Unicode since e.what() returns ASCII type of string.
    }
}

2.4Data type that can be used in ADO

ADO Wrapper API is compatible with most types of data

Here is an example.

Proud::CAdoCommand cmd;
...

cmd.Parameters[1] = 3.f; // Input floating point
cmd.Parameters[2] = L"abc"; // Input string
cmd.Parameters[3] = CTime(2009,1,1,3,4,5); // Input date & time, 2009, 1/1, 3:04:05
...
int ret = cmd.Parameters[0]; // Output integer
Proud::String txt = cmd.Parameters[2]; // Output string
CTime when = cmd.Parameters[3]; // Output data & time

But some data types require the use of separated module as shown in below.

Date & Time data type

The class provided for date & time data type in ADO Wrapper API is Proud.CPnTime. Here shows how you can use it.

Proud::CPnTime t(2009,1,2,3,4,5); // 2009, January/2, 3:04:05
Proud::CAdoCommand cmd;
cmd.Parameters[1] = t; //Inserts date & time by parameter of stored procedure
...
Proud::CPnTime t2;
t2 = cmd.Parameters[2]; // This output parameter of stored procedure that receives data & time.

t2.Format(L"%x");  // This method converts date & time to string format.
int year = t2.GetYear(); // This method gets year information.

Image data type

Image data type can be managed as using ADO Wrapper API.

Proud::CAdoCommand cmd;
Proud::ByteArray t;

... //This method fills in data on t

cmd.Parameters[1].FromByteArray(t); // This method inserts t as a parameter of stored procedure
...

ByteArray t2;
t2 = cmd.Parameters[2].ToByteArray(t2); // This method is output parameter of stored procedure that gains Image type value.

2.5Accessing MySQL with ADO

ADO Wrapper API can also access MySQL.

In order to access MySQL, first MySQL ODBC Driver or MySQL Provider must be installed.

The example below shows how you can access MySQL when its driver is properly installed.

void Foo()
{
    Proud::CAdoConnection conn;
    conn.Open(L"Driver={MySQL ODBC 5.1 Driver};
                server=localhost;port=3306;Database=tablename;
                User ID=xxx;Password=yyy;"); //ODBC Driver 5.1
//conn.Open(L"Provider=MySQLProv;server=localhost;port=3306;Database=tablename;User ID=xxx;Password=yyy;"); When Provider is installed.
   //conn.Open(L"Driver={MySQL ODBC 3.51 Driver};server=localhost;port=3306;Database=tablename;User ID=xxx;Password=yyy;");When ODBC Driver 3.51 is installed.
   //conn.Open(L"Driver={MySQL ODBC 5.1 Driver};server=localhost;port=3306;Database=tablename;User ID=xxx;Password=yyy;",MySql);Skips string check by passing enum value.
conn.BeginTrans();  // The method begins the transaction. Warning!!! Provider doesn't support transaction.
int Val = 3;
conn.Execute(L"delete from Table1 where FieldA=%d",Val);
conn.CommitTrans();  // This methods commits the transaction.
}

There are few things you need to be careful about as using MySQL unlike MSSQL.

Things you need to be careful about as using ADO Wrapper API in MySQL

There are few things you need to be cautious about as using ADO Wrapper API in MySQL.

In case of using Provider (When Provider=MySQLProv; is included in ConnectionString)

Provider doesn't support transaction by default because MySQL doesn't support transaction by itself. But, if you are using ODBC driver, you can use transaction since it is supported by the driver itself.

In case of using Proud.CAdoCommand

If you are using Proud.CAdoCommand then you need to add parameters explicitly.

Also, Proud.CAdoCommand supports neither return value nor out Parameter.

The example below shows how Proud.CAdoCommand can be used in MySQL.

void Foo()
{
    Proud::CAdoConnection conn;
    Proud::CAdoRecordset rs;
    ...

    Proud::CAdoCommand cmd;
    cmd.Prepare(conn, L"sp_GetTable1");
    // cmd.Parameters[1] = Val;   // This refers to the first parameter of stored procedure. Do not use this.
    cmd.AppendParameter(L"ParamName",ADODB::adVarWChar,ADODB::adParamInput,Val); //Insert parameter explicitly.

    cmd.Execute(rs);
    //long retVal = cmd.Parameters[0]; // This method gains a value returned by stored procedure. But MySQL doesn't support returned value.

    if(!rs.IsOpened())
        rs.Open(); // It becomes accessible only when the returned recordset is open first.
    int x = rs.FieldValues[L"FieldA"];
}

2.6Tracking ADO operation

ADO Wrapper API offers a function that can track every steps of accessing DBMS.

DBMS such as MS SQL Server and etc. comes with query tracker (or query profiler) but sometimes it is prevented for use under security reason by game publishers. Tracking ADO operation can be an alternative solution when query tracker is prohibited.

The example below shows how event can be received after a certain delay access time is passed as querying or commanding.

class CAdoWrapEvent:public Proud::IDbmsAccessEvent //This method inherits event interface.
{
    void OnQueryDelayed(LPCWSTR lpszcommand,Proud::CPnTime curtime,uint32_t querytick)
    {
        wprintf(L"QueryDelayed - comment:%s curtime:%s delayedtime:%u",
        lpszcommand,curtime.Format("%x %X").GetString(),querytick);
    }
};

CAdoWrapEvent g_EventSink;//This method inherits event interface.
{void main()
{
    Proud::CDbmsAccessTracker::DelayedAccessThresholdMilisec = 500; //This method sets the query delay detection time to 500 milliseconds. 
    Proud::CDbmsAccessTracker::SetAdoWrapEvent(&g_EventSink);

    //Use ADO wrapper API from here on...
}

This function can also receive events of ADO Wrapper API used in 1. ProudNet Database Cache System version 2

2.7About ADO connection pooling

Microsoft ActiveX Data Object (ADO) provides connection pooling. If there are ADO connection object that connected with specific database, it will share current connection when there are existing ADO connection object rather than making new connection. Therefore it still show good performance even you use ADO connection object only once as local variable and just leave one ADO connection object that connected database during running a program.

Here is an example.

Proud::CAdoConnection g_justConnected; // entire object that only keep database connection

void main()
{
    CoInitialize(0);

    g_justConnected.Open(MyDatabaseConnectionString); // Keep connecting with database. It just maintain connection until closing program.

    ...
}

void SomeDatabaseAccess()
{
    /* It still keep good performance just create database connection object. If g_justConnected existed, current connection will re-use immediately.*/
    Proud::CAdoConnection conn;
    conn.Open(MyDatabaseConnectionString);
    ...
}

For more detail please refer to Pooling in the Microsoft Data Access Components http://msdn.microsoft.com/en-us/library/ms810829.aspx

2.8Notes on ADO use

BSTR of ADO does reference counting and when reference counting becomes 0, BSTR will be automatically released, however, it is not released immediately but does string pooling internally. As a result of this, a memory lack error might occur due to memory use increase. The use of memory will increase continuously when the speed of new string allocation is faster than the speed of releasing string that is not used.

For example, in case of receiving RecordSet by massive data query frequently, BSTR is continuously allocated in proportion to data size, so the use of memory will increase.

The possible alternative to the above issue is not to use BSTR String Pooling by registering environment variables as below.

OANOCACHE 1