Database Connectivity with C++ - An Introduction

 

Open Database Connectivity (ODBC)

ODBC is a standard or open application programming interface (API) for accessing to database management systems (DBMS). This API is independent of any one programming language, database system or operating system. ODBC is based on the Call Level Interface (CLI) specifications from SQL, X/Open (now part of The Open Group), and the ISO/IEC. ODBC was created by the SQL Access Group and first released in September, 1992.

By using ODBC statements in a C++ program, you can access files in a number of different databases, such as, MS Access, MS SQL, Oracle, DB2 and My SQL. In addition to the ODBC software, a separate module or driver is needed for each database to be accessed.

Setting up ODBC data source

In order to make a connection from 3rd party products using ODBC, an ODBC data source is needed. A data source stores connection information such as user name, password, and location of database.

Steps for setting up a data source:

Step 1:

ODBC Administrator can be started by going to Control Panel and double clicking on 32 bit ODBC or Starting the utility called "32bit ODBC Administrator” if you have the ODBC SDK installed.

Step 2:

Stay at the User Data Source tab and click on Add. This will bring up another window titled "Create New Data Source".

Step 3:

Pick the ODBC driver that to be used. Pick the InterBase driver which is called

"InterBase 5.x driver by Visigenic (*.gdb)" and then click on Finish. This will bring up a new window with the title "InterBase ODBC Configuration".

Step 4:

Fill in the blank fields in this window

Data Source Name: Make up a name for your data source.

Description: This is the description of the data source. It's not required.

Network Protocol: Choose the protocol from the drop down list.

Database: Fill in the physical full path to the database including the database name to server.

Server: Fill in the server name. If you choose the protocol "local", this will default to the local server.

Username: Fill in the database user name.

Password: Fill in the password corresponding to the above user name. Go to the Advanced tab and fill in the CharacterSet and Roles.

Step 5: Clicking the OK button will bring back to the main form. You should see the newly added user data source there.

Note: A user data source is a data source visible to the user whereas a system data source is visible to the system.

 Connecting from Delphi or C++ Builder using the ODBC data source

ODBC connection from Delphi is very similar to connecting using Borland Development Environment from Delphi. Here is an example of connecting using the Tquery component. This example will also display the results of a sql statement.

Step 1:

Drop a Tquery, a Tdatasource, and a Tdbgrid component on a Delphi form.

Step 2:

Set the following properties for the Tquery component: DatabaseName: Pick from the list the data source name you just created in ODBC Administrator.

SQL: Input the SQL statement to be executed. For example: "select * from table1".

Active: Set to True to connect. And supply user name and password on connection.

Step 3:

Set the following properties for the Tdatasource component:

Data Set: Set to the name of the Tquery component, or "query1" in this case.

Step 4:

Set the following properties for the TDBGrid component:

Data Source: Set to the name of the Tdatasource component, or "data source1" in this Case

Step 5:

Now you can see the returned results from select statement in the dbgrid area.

3.4 Connecting MS Visual C++ 5.0 to Databases

Now we have to create an ODBC data-source, which points to your database.

Step 1: Start up ODBC Administrator.

Double click the "32bit ODBC" icon in Control Panel to start the ODBC administrator.

Step 2: Click the "Add" button to create a new data-source, select the driver you wish to use (if your database is say, MS SQL select the "Microsoft SQL Driver" and so on) and click the "finish" button.

Step 3:

Enter a name for the data source in the "Data Source Name" field. "Description" field is optional.

Step 4: Click on the "Select" button from the "Database" field. A browser box appears where you select the location of your MS SQL database file. When done click the "OK" button to return back to the previous screen and "OK" again to complete the creation of the data source. Now click the "OK" button to quit the ODBC administrator.

Step 5: Now, we can start writing a C++ program that will open a connection to the database and perform an SQL query. The SQL query can be any SQL statement to search, insert, update or delete data in the database. This program will not do any validation of whether or not a record with the same value already exists in the database table and  the SQL string is a legal SQL statement. A set of C/C++ functions are supplied with ODBC and are found in the two header files sql.h and sqlext.h. We would need to include both the header files in the program using the "#include" operation. 

Code to Connect C/C++ to the ODBC data source

The code for connecting C/C++ to OBDC data source (“ABC”) and performing SQL query is given below . The code does not include error handling considered desirable when modifying the table or establishing and closing the connection to the database. You will need to change the name of the data source if you wish to use this code. The database may also require a user ID and password.

// ODBC data source is called "ABC". It then executes a SQL statement “SELECT

Model

// FROM Makes WHERE Make = ' Suzuki';" should pull out all models made by Suzuki

stored in the database

#include <sqlext.h>

#include <stdio.h>

#include <iostream.h>

void main()

{

HENV hEnv = NULL; // Handle from SQLAllocEnv()

HDBC hDBC = NULL; // Handle for Connection

HSTMT hStmt = NULL; // Statement handle

UCHAR szDSN[SQL_MAX_DSN_LENGTH] = "ABC"; // Data Source Name

UCHAR* szUID = NULL; // User ID buffer

UCHAR* szPasswd = NULL; // Password buffer

UCHAR szModel[128]; // Model buffer

SDWORD cbModel; // Model buffer bytes received

UCHAR szSqlStr[] = "Select Model From Makes Where Make=''Suzuki";

// SQL string

RETCODE retcode; // Return code

// allocate memory for ODBC Environment handle

SQLAllocEnv (&hEnv); // Allocate memory for the connection handle

SQLAllocConnect (hEnv, &hDBC);

// connect to the data source "ABC" using userid and password.

retcode = SQLConnect (hDBC, szDSN, SQL_NTS, szUID, SQL_NTS, szPasswd,

SQL_NTS);

if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)

{

// allocate memory for the statement handle

retcode = SQLAllocStmt (hDBC, &hStmt);

// prepare the SQL statement by assigning it to the statement handle

retcode = SQLPrepare (hStmt, szSqlStr, sizeof (szSqlStr));

// execute SQL statement handle

retcode = SQLExecute (hStmt);

// Project only column 1 which is the models

SQLBindCol (hStmt, 1, SQL_C_CHAR, szModel, sizeof(szModel), &cbModel);

// Get row of data from the result set defined above in the statement

retcode = SQLFetch (hStmt);

while (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)

{

cout ("\t%s\n", szModel); // Print row (model)

retcode = SQLFetch (hStmt); // Fetch next row from result set

}

// Free the allocated statement handle

SQLFreeStmt (hStmt, SQL_DROP);

// Disconnect from datasource

SQLDisconnect (hDBC);

// Free the allocated connection handle

SQLFreeConnect (hDBC);

// Free the allocated ODBC environment handle

SQLFreeEnv (hEnv);

}