?
Solved

Simple database connection C++ ODBS DSN datasource

Posted on 2007-10-02
6
Medium Priority
?
764 Views
Last Modified: 2013-12-14
Hi, I would like to connect to a System ODBC data source (DSN) from my C++ application. I am not using MFC. I believe this should be easy but cannot find anything simple anywhere!

I want to run the query 'INSERT INTO TestTable (Field1) VALUES ('Hello World')"

In pseudocode I believe it would be like this

create new database object
open database object from the database 'mydatabase'
run insert query
close database connection
destroy database object

Can anyone help?
0
Comment
Question by:butterhook
  • 3
  • 2
6 Comments
 
LVL 39

Accepted Solution

by:
itsmeandnobodyelse earned 1500 total points
ID: 19999356
Maybe something like that:

     SQLHENV hEnv = SQL_NULL_HENV;
     SQLHDBC hDbc = SQL_NULL_HDBC;
     SQLHSTMT hStmt = SQL_NULL_HSTMT;

     RETCODE retSqlAllocEnv  = SQLAllocEnv(&hEnv);
     if (retSqlAllocEnv != SQL_SUCCESS)
            return 1;  /* error */
     RETCODE retSqlAllocConnect = SQLAllocConnect(hEnv, &hDbc);
     if (retSqlAllocConnect != SQL_SUCCESS)
            return 2;  /* error */
     /* connect to a data source created with odbcad32.exe */
     RETCODE retSqlConnect = SQLConnect(hDbc, "datasource", SQL_NTS,
                                            user, SQL_NTS, pwd, SQL_NTS);
     if (retSqlConnect != SQL_SUCCESS)
            return 3;  /* error */
     RETCODE retAllocStmt = SQLAllocStmt(hDbc, &hStmt);
     if (retAllocStmt != SQL_SUCCESS)
            return 4;  /* error */
     RETCODE retExecute = SQLExecDirect(hStmt,
         "INSERT INTO MYTABLE (ID,DESC,NUM) VALUES (12345,'text',67.89)",
         SQL_NTS);
     if (retAllocStmt != SQL_SUCCESS)
            return 5;  /* error */

You can free the allocated resources by calling the SQLFree* functions.

You need one SQLHENV, for each connection one SQLHDBC (note if accessing from different threads use one connection for each thread), and a SQLHSTMT for each statement to execute.

Fetching data using select statements is some more difficult, cause you need to provide a buffer for the resultset, bind columns to variables and call SQLFetch in a loop. Tell me if you want that additional information.

In case of an error you may get the full error information by calling SQLError (ODBC 3.0 SQLDiagRec) in a loop while the return code == SQL_SUCCESS.

Note, unfortunately the native ODBC isn't very simply. With ODBC based class libraries like that in MFC (CRecordset or CDaoRecordset) things are a little bit easier, though it is not much difficult to make a small class wrapper around the statements above.



Regards, Alex

0
 
LVL 39

Assisted Solution

by:itsmeandnobodyelse
itsmeandnobodyelse earned 1500 total points
ID: 19999591
>>>>  though it is not much difficult to make a small class wrapper
>>>> around the statements above.


class Database
{
     SQLHENV hEnv;
     SQLHDBC hDbc;
public:
      Database(SQLHENV he = SQL_NULL_HENV)
             : hEnv(he), hDbc(SQL_NULL_HDBC) {}  
      SQLHENV getEnv() { return hEnv; }  // for using the env for another connection
      SQLHDBC getDB()  { return hDbc; }  // for using the env for another connection
      bool connect(const string& datasource, const string& user, const string& pwd)
      {
           if (hEnv == SQL_NULL_HENV)
           {
                  RETCODE retSqlAllocEnv  = SQLAllocEnv(&hEnv);
                   if (retSqlAllocEnv != SQL_SUCCESS)
                        return false;  /* error */
           }
           RETCODE retSqlAllocConnect = SQLAllocConnect(hEnv, &hDbc);
           if (retSqlAllocConnect != SQL_SUCCESS)
                return false;  /* error */
            /* connect to a data source created with odbcad32.exe */
            RETCODE retSqlConnect = SQLConnect(hDbc, datasource.c_str(), SQL_NTS,
                                            user.c_str(), SQL_NTS, pwd.c_str(), SQL_NTS);
            if (retSqlConnect != SQL_SUCCESS)
                 return false;  /* error */
            return true;
      }

};

class Recordset
{
      Database* pdb;
      SQLHSTMT hStmt;
public:
      Recordset(Database* pd)
           : pdb(pd), hStmt(SQL_NULL_HSTMT) {}
      bool execDirect(const string& sqlcmd, )
      {
            if (pdb == NULL )
                 return false;
            SQLHDBC hDbc =  pdb->getDB() ;
            if (hDbc ||== SQL_NULL_HDBC
                 return false;
            RETCODE retAllocStmt = SQLAllocStmt(hDbc, &hStmt);
            if (retAllocStmt != SQL_SUCCESS)
                return false;  /* error */
            RETCODE retExecute = SQLExecDirect(hStmt, (SQLCHAR*)sqlCmd.c_str(), SQL_NTS);
            if (retExecute != SQL_SUCCESS)
                 return false;  /* error */
            return true,
            SQLFreeStmt(hStmt);
            hStmt = SQL_NULL_HSTMT;
      }
};
0
 
LVL 1

Author Comment

by:butterhook
ID: 20001307
Thanks, I may need a little time to analyse this.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 49

Expert Comment

by:DanRollins
ID: 20025653
MFC has a CDatabase class to hide all of the complexities of the lower-level SQLXxxxx functions.  The functions are very reliable and since all target computers come with the MFC DLL, there is no particular code bloat or other problems.  Do you have a particular reason to avoide the MFC objects?
0
 
LVL 1

Author Comment

by:butterhook
ID: 20033069
There obviously seem to be a lot of ways to do this - I actually used a different method to the both above but I gave you the points  as a gesture of goodwill.
0
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
ID: 20035836
>>>> I actually used a different method to the both above
>>>> but I gave you the points  as a gesture of goodwill.
Thanks ;-)

Can you tell what method you used? You asked for an ODBC access in C++ not using MFC. I know there are some third party libraries and of course you might use COM and ADO. But none of them has any (experienced) advantage over MFC and I would like to agree to Dan regarding the stability and reliabilty of the MFC ODBC implementation.

Regards, Alex
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

750 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