How to connect database with C and CPP ?

I got this tutorial from one site its very helpful hence shared here..... :)

This tutorial show coding how to connect with a database in C++….

This are actual guides

Note:


Quote
Before I start just to introduce some SQL commands and stuff. For starter you had some back grounds on it….

SQL (Structured Query Language) is a fourth-generation language (4GL) that is used to define, manipulate, and control an RDBMS (relational database management system).

DML sublanguage is concerned with commands that can perform operations on data in tables.It provides four commands for data manipulation:

SELECT command retrieves data for queries.

INSERT command adds new rows into a database table.

UPDATE command changes the values of data items in a database table.

DELETE command removes rows from a database table.


And let’s start….

ODBC (Open Database Connectivity)

• A standard interface for connecting from C++ to relational databases
• It allows individual providers to implement and extend the standard with their own ODBC drivers

Here are procedures or rather steps used in the industry in C++ coding for connecting to a database

Steps of the ODBC

• Include Header Files
• Open a Connection to a Database
• Choose an ODBC Driver
• Query the Database
• Creating an ODBC Statement Object
• Executing a Query and Returning an ODBCResultSet Object
• Extracting Data from an ODBCResultSet
• Closing the ODBCResultSet and ODBCStatement
• Importance of closing the connection



Okey, this are actual steps how I connect to a database…definitely I used Oracle database, but I don’t want to further say what version it is. LOL


1. Include the Header Files
# include statements at the beginning of your programs:
#include
#include
#include

2. Open a Connection to a Database
Set the environment handle:
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hdlEnv);

Set ODBC Driver version:
SQLSetEnvAttr(hdlEnv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC30);

Set the connection handle:
SQLAllocHandle(SQL_HANDLE_DBC, hdlEnv, &hdlConn);

Connect to the database:
SQLConnect(hdlConn, (SQLCHAR*)dsnName,SQL_NTS,(SQLCHAR*)userID,SQL_NTS, (SQLCHAR*)passwd, SQL_NTS);

3. Choose Driver
•DSN – Data Source Name
•Open the GUI ODBC Administrator (ODBCConfig)
•Choose the appropriate ODBC driver
•Provide a meaningful name to the DSN
•Specify the server and the host string (host string is required if the server is running on a different machine)

4. Query the Database
Querying the database involves the following steps:

–Creating a Statement
SQLAllocHandle(SQL_HANDLE_STMT, hdlDbc, &hdlStmt);
It allocates the memory for the statement handle. The database handle obtained during connection phase is passed as the second argument.

– Executing a Query
SQLExecDirect(hdlStmt, stmt, SQL_NTS);
It executes the query, which is passed in as SQLCHAR* in the second argument.

5. Extract the Data out of the Executed Query

SQLGetData(hStmt,colNum,type,retVal,buffLength,&cbData);
It extracts data from table as void* data and places it in retVal

colNum refers to the column number provided in the SELECT statement in SQLExecDirect()

Type is one of the standard ODBC data types
example: DT_STRING à for a string data type
DT_DOUBLE à for a double data type

buffLength is the estimated size of the expected data

cbData is the actual size of the data

6. Traverse Through the Results
SQLFetch(hStmt);
Fetches the next record

hStmt is the statement handle obtained using SQLAllocHandle
If a record is available, It returns SQL_SUCCEEDED

7. Close the Statement and the Connection
SQLFreeHandle(SQL_HANDLE_STMT, hdlStmt);
It closes and de-allocates the memory reserved for the statement handle

SQLFreeHandle(SQL_HANDLE_DBC, hdlConn);
It disconnects and de-allocates the memory reserved for the connection handle

SQLFreeHandle(SQL_HANDLE_ENV, hdlEnv);
It de-allocates the memory occupied by the environment handle


For further escalate the whole steps here a sample program

//Header files:

#include
#include
#include
...
....

//Declaration:

SQLHandle hdlEnv, hdlConn, hdlStmt, hdlDbc
char* stmt = "SELECT * from NutHead"; //SQL statement NutHead is the Table name

//for example
char *dsnName = “COLLECTOR”  name of your program or what ever…..
char* userID = "eXceed";
char* passwd = "hole";
char* retVal[256];
unsigned int cbData;

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hdlEnv);
SQLSetEnvAttr(hdlEnv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC30);
SQLAllocHandle(SQL_HANDLE_DBC, hdlEnv, &hdlConn);
SQLConnect(hdlConn, (SQLCHAR*)dsnName,SQL_NTS,(SQLCHAR*)userID,SQL_NTS, (SQLCHAR*)passwd, SQL_NTS);
SQLAllocHandle(SQL_HANDLE_STMT, hdlDbc, &hdlStmt);
SQLExecDirect(hdlStmt, (SQLCHAR*)stmt, SQL_NTS);


//Initialize the database connection

while(SQLFetch(hdlStmt) == SQL_SUCCEEDED)
{
SQLGetData(hdlStmt,0,DT_STRING,retVal,256,&cbData);
std::cout << retVal << std::endl;
}
SQLFreeHandle(SQL_HANDLE_STMT, hdlStmt);
SQLFreeHandle(SQL_HANDLE_DBC, hdlConn);
SQLFreeHandle(SQL_HANDLE_ENV, hdlEnv); //End the connecti

Continue