• RSS
  • Facebook
  • Twitter

Knowledge is Power.

  • Who you are ?

    Working on machines without understanding them ? Then you should be here..

  • Where you are ?

    Geographical location should not become a barrier to Share our knowledge.

  • What do you do ?

    Puzzles and Interview question are intended to be discussed here.

    Saturday, May 8, 2010

    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

    0 comments:

    Post a Comment