OLEDB

Now lets take a look at OLEDB. The reason why we keep saying Microsoft has won the battle for the Internet, in each and every article, is because of products like ODBC ( Open DataBase Connectivity). What most people don't realize is that PowerBuilder, other products like PowerBuilder and the entire Client-Server Architecture wouldn't have been possible if it weren't for ODBC. What ODBC says is , you write a program in one way, which lets you retrieve data but you don't specify where you are retrieving the data from. If you are using Microsoft Access, then you can, at runtime, specify Microsoft Access and your program will work. Now if you realize tomorrow that Oracle is better, all that you have to do is transfer your tables to Oracle without a change being made to the program and it works as before. This is because your program isn't fussy about who it is talking to. ODBC is therefore a great product and it has become too popular and to! o im portant for Microsoft to junk it.

The major problem however is that Microsoft's ODBC is very C oriented i.e. it uses the C-API ( C-functions ). Because of this, it is rather restrictive. To top it all off, it works well only with SQL based databases.

Today I receive thousands of e-mails from people who read my tutorials and send us their response. But if I ever want to find out whether a certain John Doe has sent me e-mail this month, then there is no way I can do it. I use the Eudora for all my mailing needs and Eudora has to understand certain protocols well. So it knows about SMTP and POP3, it has a good user interface, but unfortunately, it doesn't let me handle databases. So now for databasing, Microsoft has created something called OLEDB, which is a blend of OLE2.0 and database handling i.e. OLE + ODBC = OLEDB. This time, there are no restrictions as far as being SQL based is concerned

On to the code now...

Create a new project to build an exe file.

a.cpp

#define INITGUID
#define DBINITCONSTANTS
#define INC_OLE2			
#include <windows.h>		
#include <ole2ver.h>		
#include <cguid.h>			
#include <oledb.h>
#include <oledberr.h>
#include <stdio.h>
DEFINE_GUID(CLSID_SampProv, 0xE8CCCB79L,0x7C36,0x101B,0xAC,0x3A,0x00,0xAA,0x00,0x44,0x77,0x3D);

void abc( BYTE p)
{
FILE *fp=fopen("z.txt","a+");
fprintf(fp,"%c",p);
fclose(fp);
}
void abc1( char *p)
{
FILE *fp=fopen("z.txt","a+");
fprintf(fp,"%s",p);
fclose(fp);
}
IDBInitialize* pIDBInitialize;IDBCreateSession *pIDBCreateSession;
DBID dbcolid;IOpenRowset* pIOpenRowset;IAccessor *pIAccessor;HACCESSOR hAccessor;
DBBINDING rgBind;BYTE*pRowData;IRowset*pIRowset;
ULONG cRowsObtained,iRow;HROW rghRows[100],*pRows=&rghRows[0];char aa[200];
void main()
{
OleInitialize( NULL );
CoCreateInstance(CLSID_SampProv,0,CLSCTX_INPROC_SERVER,IID_IDBInitialize,
           (void **)&pIDBInitialize);
pIDBInitialize->Initialize();
pIDBInitialize->QueryInterface(IID_IDBCreateSession,(void**)&pIDBCreateSession);
pIDBCreateSession->CreateSession(0,IID_IOpenRowset,(IUnknown**)&pIOpenRowset);    
dbcolid.eKind=DBKIND_NAME;
dbcolid.uName.pwszName=L"z.csv";
pIOpenRowset->OpenRowset(0,&dbcolid,0,IID_IRowset,0,0,(IUnknown**)&pIRowset);
rgBind.dwPart=DBPART_VALUE|DBPART_LENGTH|DBPART_STATUS;
rgBind.eParamIO= DBPARAMIO_NOTPARAM;                              
rgBind.iOrdinal=1;
rgBind.wType=DBTYPE_STR;
rgBind.obValue=8;
rgBind.obLength=0;
rgBind.obStatus=4;
rgBind.cbMaxLen=7;
pIRowset->QueryInterface( IID_IAccessor, (void**)&pIAccessor );
pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA,1,&rgBind,0,&hAccessor,0);
pRowData = (BYTE *) malloc( 1000);
pIRowset->GetNextRows(NULL,0,100,&cRowsObtained,&pRows);         
for ( iRow=0; iRow < cRowsObtained; iRow++ )
{
pIRowset->GetData(rghRows[iRow],hAccessor,pRowData );
for ( int j =0 ;j<=4;j++)
abc(pRowData [j+8]);
abc('\n');
}
}

Now if you'll look closely at this, our smallest OLEDB program, ( we are going to write many complicated ones a little later ) you will see two header files, oledb.h and oledberr.h. So whether you are running nmake or using VC++ 4.2 (we have tried it out only on VC++ 4.2), make sure that your include and your lib variables point to OleDbsdk include and the lib subdirectories.

Lets start with the first line of the program - OleInitialize (NULL). This line is absolutely necessary. We then start the actual program with CoCreateInstance. The first parameter, CLSID_SampProv is a structure which is 16 bytes large. The DEFINE_GUID macro will create this structure and initialize it. For the ones who came late to the party, every OLE Server is given a number. You can call it CLSID or GUID or whatever you like. So Microsoft ships you the OLEDBServer and the number given above is the CLSID number of the Server.

The third parameter in CoCreateInstance says that it is going to be a dll, ( the casing is in a dll and not an exe file ), the fourth one is the number of an interface ( interfaces are also known by numbers with the same 16 byte structure ), so we give IID_IDBInitialize and the last parameter which is pIDBInitialize is a pointer to an IDBInitialize interface. This pointer will get filled up with some value or the other. Which means that with CoCreateInstance, the server will create an object that looks like an IDBInitialize, and it will return a pointer to IDBInitialize which will be stored in pIDBInitialize.

Now from this pointer ( i.e. pIDBInitialize ) I can call the Initialize function. Why I need to call Initialize we don't know, but obviously it could mean checking versions, it could mean allocating more memory, it could mean the initializing itself, or else it could mean nothing at all.

Now after the OleDBServer is initialized, a session has to be created , so I use QueryInterface and get a pointer to an IDBCreateSession. Now once I have an IDBCreateSession pointer, I will then call a function called CreateSession. CreateSession will return an IOpenRowset pointer because the second parameter given to the function is IID_IOpenRowset. Now the best way to understand Session is, that each time you want to talk to somebody different you will ask for a different session pointer. The IOpenRowset pointer contains information about the data. So now using the IOpenRowset pointer, you call the OpenRowset function and give it a structure called dbcolid. Now we have initialized only 2 members of this structure. The member uName is again a structure having a variable called pwszName. This variable is more important because it has to be given a filename. I have got a comma delimited file called z.csv in a certain subdirectory to which this variable has been initi! alized ( the structure of z.csv we will come to it in a short while). 'L' means that it is not a char but it is an int. These are the pesky little problems caused by unicode . So we are now returned an IRowset pointer by the OpenRowset function which talks about the actual data.

The large number of objects are necessary if OLEDB is not to become obsolete in a couple of years. All that we would like you to do is create the file z.csv i.e. the comma delimited file. In our case it is only 4 lines. On the first line you can give the name of the field, it can be whatever you like, the second is for the type, it can be a char or an int or any other data type, if it is a char then in round brackets you give the size of the field (i.e. no of chars) and then finally your data. If you have too many fields, i.e. if you have more than one field then you separate it with commas.

z.csv

"CustomerID"
Char(5)
aaa
bbb

Now we will have to access this data. So using the IRowset pointer , we ask for a pointer to the IAccessor interface. The Rowset spoke about data, but now to access the data you have to create the Accessor. In the CreateAccessor function, the second parameter holds the no. of columns in the file. We have given 1 because in the file we have only one column. The third parameter rgBind is a structure which looks like DBBINDING. In the DBBINDING structure, the important member is iOrdinal which we have initialized to 1 because we are talking about the first column in the file. The type of this column in the file is string so wType is initialized to DBTYPE_STR; , the maximum length is 5 + 2 = 7 ( 2 is for 0D,0A) . And then we have 3 more member which are obLength, obStatus, obValue. Thelenght starts at the 0 offset, then 4 bytes later is the status ( whether it was a success, a failure, whether the data was truncated, what was the problem ) and from the 8th bytes onwards is! th e actual data for that column. Now if you would have multiple columns then either you will need an array of DBBINDING structures or you specify the details of the columns one by one. Then using the IRowset pointer you call the function GetNextRow in which the third parameter is 100 which means read 100 rows from the file. The cRowsObtained is the address of a variable which will tell you how many rows you have got in the file. In our case the file has only 2 rows so cRowsObtained will give you 2 .

The last parameter pRows ( a pointer to an HROW ) is initialised to an array of 100 .

Now using the GetData function , you can read the rows from the array rghRows, so we could either use a FOR loop or say GetData for every new row. In the GetData function, the second parameter is the Accessor and the last parameter is pRowData which will now contain the actual data. The size of the array rghRows is 100 because we wanted 100 rows at a time. pRowData is a pointer to a char, so all that you have is an array, in which the first 8 bytes is the status information, immediately after which is the data (only 5 bytes) which you can put in a loop.

What we have done here is nothing but an extremely simplified version of the samples that came along with OLEDB. Now the reason we have done this is because when we looked at his original programs there were too many things to understand at one go. Too many objects were pre-written. So we grossly simplified it and as we give you more and more examples, we will come closer and closer to the original program.

Move back to the Vijay Mukhi's Technology Cornucopia Page to learn more about the other new Internet Technologies.