When I surf several site about this topic I almost fail. There is no book out there for this. When I almost ended I found this topic in Dr. Dobbs Portal . The topic is originally written by Alex Ragen(Alex Ragen is a consultant based in Jerusalem, Israel. You can reach him at Alex@CheckPoint.com.). I totally copying that topic for you as I am very small person to edit that. So, here is the topic.
I make a living by writing applications that acquire and present data in useful forms, so I'm always looking for tools that help me organize and present data. These days, a number of powerful database packages are available — such as Microsoft Access or Borland Paradox — that provide not only credible database engines, but all kinds of nearly spectacular tools for designing forms and reports as well. Using these packages would greatly shorten development time for me, but I have avoided them because they all suffer one crippling limitations: they are set up to receive input data only from a user typing at a keyboard.
These applications do have provisions for importing data from ASCII files, and even other applications, but these imports are expected to occur on a one-time basis, usually when setting the system up for the first time. For me, this limitation is a serious one, because in the systems I design, most of the data originates elsewhere: an instrument directly attached to the computer or a device communicating over a telephone line. It's difficult and sometimes impossible to do this kind of data acquisition with these database packages.
These packages typically provide limited programming support by offering a native variant of Basic, or some similar language. For a dyed-in-the-wool C programmer like me, this "support" amounts to a restriction. Still, I have watched with envy as other developers whipped out very sophisticated systems in a matter of weeks using database packages, and I've tried to find ways to use them myself.
Recently, I've succeeded in building two applications that combine the best of both worlds — a convenient and easy-to-use database with good old C — without sacrificing any flexibility. The secret: an old-new standard for maintaining databases called ODBC (Open Data Base Connectivity). If this sounds too good to be true, I should add that I paid a price for this success, but in my case it was well worth it. After reading this article, you can judge for yourself whether this approach is useful to you, and whether you're willing to pay the price.
The database I used was Microsoft Access 2.0. I could just as well have used Paradox, or any other database for which ODBC drivers are available. I chose Access for rather mundane reasons; I had read some good reviews in the press and also, as a user of other components of Microsoft Office, I was able to get a good price on Access. In almost everything that follows, you can substitute Paradox or any other ODBC compliant database of your choice for Access.
If you've ever used something called "embedded SQL," then you already know what ODBC is like. (See the sidebar for more information about SQL.) When you want to perform some database activity in your C program — for example, if you want to query the database or update it — then you simply call a function and supply the appropriate SQL statement. From this short description of ODBC, you can infer two of its essential characteristics: it's designed for use with relational databases and you have to know SQL in order to use it. Whether you consider these characteristics strengths or shortcomings depends on your attitude toward relational databases in general.
Matters of philosophy aside, here's how I would go about constructing a mixed CODBC-Access application: First, build the database with Access. Forget about ODBC and C for the time being. One word of warning — you must plan your database very carefully. Resist the temptation to rush ahead and start designing forms and reports simply because they're so easy to churn out. Concentrate first and foremost on the design of the tables and the relationships between them, and run your design past your users. Once the database has been finalized, fill the tables with sample data, including the data that will, in the real system, come from non-keyboard sources. Generate some sample reports. Make sure the database is functioning as it is supposed to. Later on, when you add the ODBC components to your application, you'll want to be sure that the database component is functioning properly so that you'll be better able to isolate problems.
Using ODBC
The relevant components of ODBC are as follows:
- ODBC.INI
- ODBC.DLL
- individual ODBC drivers for each database
[ODBC Data Sources]
MS Access Databases=Access Data (*.mdb)
MS Access 2.0 Databases=Access 2.0 for MS Office (*.mdb)
SuperApp=Access Data (*.mdb)
The above example indicates that three data sources are considered Microsoft Access databases: MS Access Databases, MS Access 2.0 Databases, and SuperApp. Each of the data sources listed in the ODBC Data Sources section is described in greater detail in a section of its own, later on in the file:
[SuperApp]
Driver=C:\WINDOWS\SYSTEM\ODBCJT16.DLL
DefaultDir=C:\SUPERAPP
JetIniPath=MSACC20.INI
DBQ=SUPERAPP.MDB
UID=Admin
Description= SuperApp Database
DataDirectory=c:\SUPERAPP\SUPERAPP.mdb
SingleUser=False
UseSystemDB=True
FileType=RedISAM
SystemDB=c:\SUPERAPP\system.mda
This example describes the SuperApp database as a Microsoft Access database whose ODBC driver is C:\WINDOWS\SYSTEM\ODBCJT16.DLL, and whose name is SUPERAPP.MDB in the directory c:\SUPERAPP. To use a database with ODBC in a C program, you must do the following:
Initialize the ODBC environment
The SQLAllocEnv function allocates memory for an environment handle and initializes ODBC for an application:return (SQLAllocEnv(&hEnv) == SQL_SUCCESS);
Connect to the database
The SQLAllocConnect function allocates a connection handle and the SQLConnect function actually makes the connection. ODBC looks up the database in ODBC.INI and then connects to it using the specified driver. If SQLConnect fails, it's usually a sign that something is wrong in ODBC.INI or that the required driver is missing.if ((nResult = SQLAllocConnect(hEnv, (HDBC FAR *)&hDBC))
!= SQL_SUCCESS) {
... error message ...
}
nResult = SQLConnect(hDBC,(LPSTR)szDBName,SQL_NTS,
(LPSTR)szUserName,SQL_NTS,
(LPSTR)szPassword,SQL_NTS);
if (nResult != SQL_SUCCESS && nResult !=
SQL_SUCCESS_WITH_INFO) {
... error message ...
}
Query/Update the Database
You query or update the database using SQL statements. For example, to update a database, you first allocate an SQL statement handle:
if ((nResult = SQLAllocStmt(hDBC,&hStatement))
!= SQL_SUCCESS) {
... error message ...
}
Then you build an SQL statement to do whatever it is you want to do. The following statement updates the MACHINE table, changing the values of the fields (columns in SQL jargon) HardwareVersion, SoftwareVersion in the record (row in SQL) whose MachineID field equals whatever is in szMachineID:
// ODBC SQL statement handleThen you execute the SQL statement
HSTMT hStatement = SQL_NULL_HSTMT;
sprintf(pUpdate, "UPDATE DISTINCTROW MACHINE SET "
" MACHINE.HardwareVersion = %d,"
" MACHINE.SoftwareVersion = %d"
" WHERE (\"MachineID\"=%s);",
HardwareVersion,SoftwareVersion, szMachineID);
nResult = SQLExecDirect(hStatement, pUpdate, QL_NTS);
if (nResult != SQL_SUCCESS)
... error message ...The following code fragment retrieves an unspecified number of records satisfying the given criteria from the EVENT table. Because EventID is the only field in the SELECT list, only EventID is available for later processing:
// ODBC SQL statement handle
HSTMT hStatement = SQL_NULL_HSTMT;
RETCODE nResult;
long lEventID;
sprintf(pSelect,"SELECT DISTINCTROW EventID "
" FROM EVENT WHERE ((MachineID = %s) AND "
" (Status = 0) AND (EventCode = %d));",
pQTask->szMachineID, iNoPeriodicalReport);
nResult = SQLExecDirect(hStatement,pSelect,
SQL_NTS);
if (nResult != SQL_SUCCESS) {
DisplayError(nResult,hStatement);
SQLFreeStmt(hStatement,SQL_DROP);
return;
}
The next fragment binds the EventID field (the 0th field in the SELECT list) with the long variable lEventID:
nResult = SQLBindCol(hStatement,1,SQL_C_LONG,The following while statement (in C) loops through all the retrieved rows. Each time SQLFetch is successfully called, a new value appears in lEventID.
&(lEventID), 0,NULL);
if (nResult != SQL_SUCCESS) {
DisplayError(nResult,hStatement);
SQLFreeStmt(hStatement,SQL_DROP);
return;
}
while (TRUE) {
nResult = SQLFetch(hStatement);
if (nResult != SQL_SUCCESS) { // no more rows
break;
}
... do something with lEventID ...
}
Free the SQL Statement Handle
When you're done querying or updating the database, you need to free the SQL statement handle:SQLFreeStmt(hStatement,SQL_DROP);
Disconnect From the Database
The following C code disconnects from a database by breaking the connection established by SQLConnect:SQLDisconnect(hDBC);freeing the handle allocated by SQLAllocConnect:
SQLFreeConnect(hDBC);and freeing the environment allocated by SQLAllocEnv
SQLFreeEnv(hEnv);
Debugging
It's best, in my experience, to isolate all the functions that call ODBC functions into a single source file. This file is then the only one that needs to include sql.h and sqlext.h. One of the sample applications in the ODBC SDK is qurydemo, which is a sort of generic query program. If I was having a problem with an Access query, I would have Access translate the offending query to SQL. Then I would copy the SQL text to the clipboard, paste it into qurydemo's window and run the query. This process enabled me to isolate the problem more effectively than if I had tried to debug using Access's very limited debugging tools.
Getting it all Working
The techniques described here are the same for all databases. Presumably, if you decide to switch databases in mid-project, all you have to do is get hold of the appropriate ODBC driver and update ODBC.INI. You shouldn't even have to recompile your C code. That's the theory, anyway. In practice you may have to modify any of the SQL statements whose syntax is unique to the SQL dialect favored by your former database.
What I read about ODBC seemed pretty straightforward, and I didn't expect any problems when I started my projects, but of course I ran into a surprise or two along the way. I obtained the ODBC SDK from the Microsoft Developer's Network. The SDK includes drivers, example code, and lots of documentation on CD-ROM.
I also bought a print copy of Microsoft's ODBC 2.0 Programmers' Reference and SDK Guide and got to work. I discovered that the documentation was incomplete and oriented more to ODBC driver writers than to ODBC users. For example, the documentation for the ODBC.INI parameters is very sketchy. Until I came across (on CompuServe) an (apparent) Microsoft inter-office memo, I didn't know about the DBQ parameter and my application couldn't direct ODBC all the way to the database file, but only as far as the directory.
In the course of debugging, I got stuck on a problem and turned to CompuServe for help. My question "Can you advise me where I can get an up-to-date list of Jet [MS Access] database engine error messages?" produced the following reply from Microsoft: "If you are using the Access 2.0 ODBC driver then I would expect you to get all sorts of internal database errors from your program. The Access 2.0 ODBC driver that ships with Access 2.0 currently works only with MS Office products Excel and Word. We are currently working on the 2.0 ODBC driver to work with other products like VC++ but no release date has been given." By all rights the rest of this story should have been nothing but wail and woe, but it turned out to be just the opposite. I figured out the solution to my problem without Microsoft's help, and I have never had any of the internal database errors they promised me.
Is It Worth It?
On the upside, using Access and ODBC got me off to a quick start, and the user interface is one of the best I have ever done. If I had done it all in straight C, it would have taken me six months more, at the least, and the user interface wouldn't have been nearly as good. I gained a reliable, first-rate database and a quick-and-beautiful GUI. Best of all, I didn't have to give up my favorite language (after French). On the downside, ODBC and Access are both very slow. Relational databases have a well-deserved bad name when it comes to speed, and Access is definitely not an exception to the rule. My users haven't complained about speed yet — they're not programmers and don't know much about nanoseconds or clock cycles — and as time goes by they'll be running my systems on Pentiums or better, so speed won't be so important. However, if I were building a system where lightning speed was critical, I would steer clear of ODBC.
It is possible to speed things up by "preparing" a frequently executed SQL statement and then using SQLExec instead of SQLExecDirect. When using SQLEXEC, the statement is parsed and an access plan prepared only once. However, if the underlying database is slow, or located somewhere over the network beyond a gateway, the speedup isn't likely to make much difference. In summary, I would definitely recommend considering the mixed C-ODBC-Access, but there are still some cases where the old ways are best.
Resources and References
[1] Microsoft Developers Network — This is a subscription service of Microsoft that provides technical articles, documentation, code, and utilities to Windows developers. Regular updates are issued on CD-ROM. Contact Microsoft Corp., One Microsoft Way, Redmond, VA 98053.
[2] ODBC 2.0 Programmer's Reference and SDK Guide, ISBN 155-615-6588 (Microsoft Press, 1995).
[3] James R. Groff and Paul N. Weinberg. The LAN Times Guide to SQL (Osborne McGraw-Hill, 1994).
--------------------------------------------------------
Human do error, please email:- webmaster@piyadas-world.com if you find any. Please visit http://www.piyadas-world.com for more resource.
0 comments
Post a Comment