I would like to add a database to my program. How can I do this.

Posted on 2004-11-10
Last Modified: 2008-02-01
Hi experts,
I have no experience with databases concerning c++ (some MySQL and PHP).
I am working on a cross-platform application (Windows, UNIX).
Now that it is working I would like to add database support to my app in both,
UNIX and Win. There is an Oracle database on the UNIX server.
How can I connect to a database using MFC, ANSI C++, ... ??
Is it necessary to locally set-up the database, too?
I am missing vocabluary in the context, so please give me as much ideas,
as possible. Maybe also a tutorial or two.
Question by:allmer
    LVL 86

    Assisted Solution

    I'd suggest checking out Oracle's site at ("Oracle Developer Suite") where you can download their 'SDK' and will find downloads, documentation, samples etc. for a variety of platforms. Also, ("Tutorials") seems to be a good starting poijnt.
    LVL 55

    Assisted Solution

    by:Jaime Olivares
    LVL 5

    Author Comment

    I don't seem to understand what I need to get me started.
    I guess I would need to install the database application itself.
    Would there be something that tells me step by step what to
    do in order to set everything up to use Visual Studio .net for
    the programming with a recent oracle database?
    LVL 30

    Assisted Solution

    Check out the following link for free downloads of Oracle 10g:

    Here is a link for documentation on all 10g software including the installation guides.

    LVL 39

    Expert Comment

    You could use ODBC interface for both platforms:

      - Install Oracle ODBC client Windows and UNIX (comes with Oracle installation)
      - Create a DataSource (Windows using odbcad32.exe, UNIX check ODBC client)
      - Use include file sqlext.h (Windows/Unix?), sql.h (Unix?)
      - libraries in Windows are odbc32.lib and odbccp32.lib

    Tell me, if you need more information.

    Regards, Alex
    LVL 5

    Author Comment

    I am downloading two "CDs" from oracle at the moment.
    They are supposed to contain the developer's suite.
    I guess I'll need some more help when done with the installation.
    Thanks so far,

    Assisted Solution

    i would use ODBC on Windows - just install the Oracle driver and then create a DSN (Data Source Name).

    IN the DSN you define your schema password and the service name.


    LVL 5

    Author Comment

    Good morning everyone,
    So I installed oracle 10g for developers.
    If I now start the Oracle Discoverer Administrator it asks a password and login
    which I don't have.
    Where do I turn to setup these?
    LVL 5

    Author Comment

    I have a database, Username and a login now.
    Of course I also have a space where the stuff is located.
    Where do I go from here?
    I guess I need some classes there,
    where do I find them?
    LVL 5

    Author Comment

    Can I have some more information on ODBC?
    Can I connect to an access database using that?
    LVL 5

    Author Comment

    If I use these two headers:
    #include <sql.h>
    #include <sqlext.h>
    it sure compiles under windows. But won't compile in UNIX.
    Which headers do I have to use there?
    Do they contain the same library?
    LVL 39

    Expert Comment

    >> Which headers do I have to use there?

    That differs between unix platforms. AFAIK, you need sql.h, sqlext.h, sqlunix.h and sqlopt.h. At AIX it is sql2.h (because of DB2 database).

    >> Do they contain the same library?

    Same library as in Windows? No, of course not. Check your library path. The name of the lib should be either be prefixed by 'odbc' or 'sql'.

    I found the following link with google:

    that seems to provide the information you are looking for.

    Regards, Alex

    LVL 5

    Author Comment

    Very nice tutorial Alex.
    One thing they all fail to explain is how to convert data types.
    Maybe I don't know how the SQL statement will look like at programming time.
    So I end up with something like a list<string> where each string is an SQL command.
    Then I try something like:

    bool CPeptideDatabase::ExecuteSQL(list<string> lst) {
          string q;
          char text[1024];
                    //char *text = new char[1024];
          for(list<string>::iterator i=lst.begin(); i!=lst.end(); i++) {
                q = *i;
                SQLTCHAR query[] = _T(text);
                retcode = SQLPrepare(hstmt,query,sizeof(query));
                if(retcode == SQL_ERROR)

    And of course it fails to compile.
    How is the conversion done?
    Must be very tough since otherwise it should be explained somewhere.
    LVL 39

    Accepted Solution

    First, i would say, forget the _T and SQLTCHAR stuff. AFAIK, neither ORACLE nor MYSQL do anything with wide strings (it could have changed in recent times, but 2000 only SQLSERVER could stor UNICODE strings).

    Next, if you want to execute SQL statements, i would use SQLExecDirect rather than SQLPrepare. SQLPrepare is used tp *prepare* an SQL statement that has placeholders like in

           "UPDATE Parts SET Price = ? WHERE PartID = ?"

    Then, you could replace the '?' terms at runtime using SQLBindParameter(..). However, you have to hold the statement handle passed to SQLPrepare and use SQLExecute to finally excute the prepared statement. All that you can avoid by uing SQLExecDirect instead (it is a little bit slower because it has to parse the SQL statement, but that shouldn't count on modern hardware).

    MSDN says:

    ------------------------------------ MSDN -----------------------------------------------
    Prepared Execution
    Prepared execution is an efficient way to execute a statement more than once. The statement is first compiled, or prepared, into an access plan. The access plan is then executed one or more times at a later time. For more information about access plans, see &#8220;Processing an SQL Statement&#8221; in Chapter 2, &#8220;An Introduction to SQL and ODBC.&#8221;

    Prepared execution is commonly used by vertical and custom applications to repeatedly execute the same, parameterized SQL statement. For example, the following code prepares a statement to update the prices of different parts. It then executes the statement multiple times with different parameter values each time.

    SQLREAL   Price;
    SQLINTEGER  PartIDInd = 0, PriceInd = 0;

    // Prepare a statement to update salaries in the Employees table.
    SQLPrepare(hstmt, "UPDATE Parts SET Price = ? WHERE PartID = ?", SQL_NTS);

    // Bind Price to the parameter for the Price column and PartID to
    // the parameter for the PartID column.
    SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_REAL, 7, 0,
           &Price, 0, &PriceInd);
    SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 10, 0,
           &PartID, 0, &PartIDInd);

    // Repeatedly execute the statement.
    while (GetPrice(&PartID, &Price)) {
    -------------------------------- MSDN --------------------------------------------------------------

    >> And of course it fails to compile.

    Can you post the compiler errors? Maybe its only a SQL_NTS that means *null terminated string* instead of sizeof(query) that has to be changed?

    >>     if(retcode == SQL_ERROR)
    >>            return(false);

    You need to implement a better error handling, like that:

    Bool OdbcEnvironment::checkSqlReturn(RETCODE ret, String* pInfo, Bool bGetInfo, Bool bUpdate)
       if (ret == SQL_SUCCESS || (ret == SQL_SUCCESS_WITH_INFO && !bGetInfo))
          return True;

       if (pInfo == NULL)
          return False;

       SQLHSTMT& hStmt = bUpdate? m_hStmtUpd : m_hStmt;
       UChar szSqlState[10];
       UChar szErrorMsg[SQL_MAX_MESSAGE_LENGTH];
       Long  nativeError;
       Short nLenErrorMsg;

       String&  info = *pInfo;
       String   newLine;

       if (ret == SQL_SUCCESS_WITH_INFO)
          info = "SQL_SUCCESS_WITH_INFO " + info;
          info = "SQL_ERROR " + info;

       for (;;)
           if (SQLError(m_hEnv, m_hDbc, hStmt, szSqlState, &nativeError,
              szErrorMsg, SQL_MAX_MESSAGE_LENGTH-1, &nLenErrorMsg) != SQL_SUCCESS)

           info << newLine << " | " << String((CharPtr)szSqlState) <<
                            " : " << String(nativeError) << " | " << String((CharPtr)szErrorMsg);
           if (newLine.isEmpty())
              newLine = "\n\t";
       return False;

    Note, that is only a sample code that won't compile on your system. I used some special typedefs like UInt, Bool, CharPtr, ...  and a string class that was capable to act like a string stream. But, i think it is not difficult to adopt it to STL.

    Regards, Alex

    P.S.  Could you post some SQL statements as well?

    LVL 5

    Author Comment

    Sorry, that I come back to this question so late.
    After some research I did, I found that there are easier ways for me to get my data to a database.
    I am going to write XML formated text now. Which can easily be uploaded to the database I am using.
    Furthermore there are ways to use xml files as a database.
    Thanks for your help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    IntroductionThis article is the second in a three part article series on the Visual Studio 2008 Debugger.  It provides tips in setting and using breakpoints. If not familiar with this debugger, you can find a basic introduction in the EE article loc…
    This article shows you how to optimize memory allocations in C++ using placement new. Applicable especially to usecases dealing with creation of large number of objects. A brief on problem: Lets take example problem for simplicity: - I have a G…
    The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
    The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now