ODBC / SQL with C++

Here is an example:

http://as400bks.rochester.ibm.com/pubs/html/as400/v4r4/ic2924/info/db2/rzadpmst55.htm

I'd encourage you to rethink your opposition to MFC in this case.  The encapsulation of ADODB via CDaoDatabase is a MAJOR time saver.

Perhaps, however, you're a "real-man" kind of programmer who doesn't want to use any of those wimpy class libraries.  In that case, you have the full power of the Windows DAO librries at your disposal.

this is a simple example ive knocked up for you, create a simple access or sql server database and create
a system dsn for it in control panel. the open function uses global varaibles etc so you will probably
want to chage this.

/* for db stuff */
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>

HENV henv;
HDBC hdbc;
int dbopen=0;

/* opens a connection using global variables , bad i know */
#define PM_DSN "your_system_dsn"
#define PM_DSN_USER "your_db_username_if_any"
#define PM_DSN_PW "your_password_if_any"

int DBopen(void)
{
    int res=0;
    RETCODE retcode;
    /*allocate the environment handle*/
    if(SQLAllocEnv(&henv)==SQL_SUCCESS)
    {
         /*allocate the connection handle*/
         if(SQLAllocConnect(henv, &hdbc)==SQL_SUCCESS)
         {
              /* Set login timeout to 5 seconds. */
              SQLSetConnectOption(hdbc, SQL_LOGIN_TIMEOUT, 5);
              SQLSetConnectOption(hdbc, SQL_CURSOR_TYPE, SQL_CURSOR_STATIC);
              /* Connect to data source */
              retcode = SQLConnect(hdbc, PM_DSN, SQL_NTS, PM_DSN_USER, SQL_NTS, PM_DSN_PW, SQL_NTS);

              if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
              {
                        res=1;
              }
         }
         else
         {
              SQLFreeConnect(hdbc);
         }
    }
    else
    {
         SQLFreeEnv(henv);
    }
    dbopen=res;
    return res;
}

void DBclose(void)
{
    if(dbopen)
    {
         SQLDisconnect(hdbc);
         SQLFreeConnect(hdbc);
         SQLFreeEnv(henv);
         if(DATABASE_NAME)
         {
              free(DATABASE_NAME);
         }
    }
    dbopen=0;
}
int DBexecute(char *sql,HSTMT *hstmt)
{
    int res=0;
    RETCODE retcode;

    if(SQLAllocStmt(hdbc, hstmt)== SQL_SUCCESS)
    {
         retcode=SQLPrepare(*hstmt,sql,strlen(sql));
         if(retcode==SQL_SUCCESS)
         {
              retcode=SQLExecute(*hstmt);
              if(retcode==SQL_SUCCESS)
              {
                   res=1;
              }
         }
    }
    return res;
}

void DBcloseCursor(HSTMT hstmt)
{
    SQLFreeStmt(hstmt, SQL_DROP);
}

void main()
{
    char sql[255];
    HSTMT fstmt;
    long lens;
    RETCODE retcode;
    char name[100];

    DBopen();

    sprintf(sql,"SELECT name FROM users");
    if(DBexecute(sql,&fstmt))
    {      
         SQLBindCol(fstmt,1,SQL_C_CHAR, name,sizeof(name),&lens);
         
         retcode = SQLFetch(fstmt);    
         while(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
         {
              printf("%s\n",name);
              retcode = SQLFetch(fstmt);
         }
         DBcloseCursor(fstmt);
    }
    DBclose();
}

Thanks a lot guys for your prompt response. I liked your tone JHance because I hate MFC. I love Win32 APIs. Thanks a lot Makerp. I will try that tonight. I am a Java programmer, but I have always been in love with C (ANSI), which I use to code for fun. Now I am trying seriously to reach the same level I enjoy in Java in C++. I know that's the most powerful language of them all.

Thanks again.
I'll get back to you guys tomorrow.

all ODBC calls begin with SQL, so if you type in SQL in the VC++ help index you will get a list of all data structs and functions etc. rememeber all of the MFC classes are built on the ODBC API anyway, look at the MFC code and see

Hi guys. Following is the code I am using to try to connect and query a MSSQLServer database. Of course, it is a modification of "makerp's" code. However, for some reason the function SQLExecDirect(), is not working properly. It returns a SQL_ERROR code. I have been trying to find what's wrong, but without luck.
Can you guys look at this function to see what's wrong with it?????

Thanks a lot
Here is my code :

#include <iostream.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
//#include <stdio.h>

//HENV henv;//environment handle
//SQLHENV henv1;
SQLHANDLE henv1;
//HDBC hdbc;//Connection Handle
//SQLHDBC hdbc1;
SQLHANDLE hdbc1;
//Statement Handler
SQLHANDLE StmtHandler;
int dbopen=0;

/* opens a connection using global variables , bad i know */
#define PM_DSN "BPSA"
#define PM_DSN_USER "sa"
#define PM_DSN_PW ""

int DBopen(void)
{
   int res=0;
   SQLRETURN retcode;
   int ODBCVersion = SQL_OV_ODBC3;
   int LoginTimeOUT = 5;
   int ODBCCursor = SQL_CUR_USE_IF_NEEDED;
   char *SQLQuery = {"SELECT USER_ID,PASSWORD FROM accountInfo"};
   char UserID[50];
   char AccountNumber[50];
   char Password[50];
   char Name[50];
   char ClientName[50];
   long c;

   /*allocate the environment handle*/
   //if(SQLAllocEnv(&henv)==SQL_SUCCESS)
   if(SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE, &henv1)==SQL_SUCCESS)
   {
        /*allocate the connection handle*/
            SQLSetEnvAttr(henv1,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
            retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv1, &hdbc1);
        if(retcode==SQL_SUCCESS)
        {
             /* Set login timeout to 5 seconds. */
             SQLSetConnectAttr(hdbc1, SQL_ATTR_LOGIN_TIMEOUT, &LoginTimeOUT, 0);
             SQLSetConnectAttr(hdbc1, SQL_ATTR_ODBC_CURSORS, &ODBCCursor, 0);
             /* Connect to data source */
             retcode = SQLConnect(hdbc1, (unsigned char *)PM_DSN,5,(unsigned char *)PM_DSN_USER,3,(unsigned char *)PM_DSN_PW,0);


             if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
             {
                         //Getting a Statement Handler, so to submit SQL queries.
                         if(SQLAllocHandle(SQL_HANDLE_STMT,hdbc1, &StmtHandler)==SQL_SUCCESS)
                         {
                              res=1;
                              cout<<"All Handlers allocated correctly ...!!!"<<endl;
                              retcode = SQLExecDirect(StmtHandler, (unsigned char *)SQLQuery,strlen(SQLQuery));
                              if(retcode==SQL_SUCCESS)
                              {
                                    cout<<"Statement Executed successfully"<<endl<<endl;
                                    cout<<"User_ID    AccountNumber    Password    Name    ClientName"<<endl;
                                    while(true)
                                    {
                                          retcode = SQLFetch(StmtHandler);
                                          if((retcode==SQL_ERROR) || (retcode==SQL_SUCCESS_WITH_INFO))
                                          {
                                                cout<<"Error fetching the data...!!"<<endl;
                                          }
                                          else
                                          {
                                                if(retcode==SQL_SUCCESS)
                                                {
                                                      cout<<"Data fetched successfully....!!!!"<<endl;
                                                      SQLGetData(StmtHandler,1,SQL_C_CHAR,UserID,50,&c);
                                                      SQLGetData(StmtHandler,2,SQL_C_CHAR,AccountNumber,50,&c);
                                                      SQLGetData(StmtHandler,3,SQL_C_CHAR,Password,50,&c);
                                                      SQLGetData(StmtHandler,4,SQL_C_CHAR,Name,50,&c);
                                                      cout<<UserID<<"----"<<AccountNumber<<"----"<<Password<<"----"<<Name<<"----"<<ClientName<<endl;

                                                }
                                                else
                                                {
                                                      cout<<"probable end of result-set"<<endl;
                                                      SQLFreeHandle(SQL_HANDLE_STMT,StmtHandler);
                                                      SQLFreeHandle(SQL_HANDLE_DBC,hdbc1);
                                                      SQLFreeHandle(SQL_HANDLE_ENV,henv1);
                                                }
                                          }
                                    }
                              }
                              else
                              {
                                    cout<<"Error executing the statement...!!! --- error = "<<retcode<<endl;
                                    cout<<"Query length = "<<strlen(SQLQuery)<<endl;
                                    if(retcode==SQL_SUCCESS_WITH_INFO)
                                          cout<<"Code = SQL_SUCCESS_WITH_INFO"<<endl;
                                    else if(retcode==SQL_NEED_DATA)
                                          cout<<"Code = SQL_NEED_DATA"<<endl;
                                    else if(retcode==SQL_STILL_EXECUTING)
                                          cout<<"Code = SQL_STILL_EXECUTING"<<endl;
                                    else if(retcode==SQL_ERROR)
                                          cout<<"Code = SQL_ERROR"<<endl;
                                    else if(retcode==SQL_NO_DATA)
                                          cout<<"Code = SQL_NO_DATA"<<endl;
                                    else if(retcode==SQL_INVALID_HANDLE)
                                          cout<<"Code = SQL_INVALID_HANDLE"<<endl;
                                    SQLFreeHandle(SQL_HANDLE_STMT,StmtHandler);
                              }
                         }
             }
        }
        else
        {
                   cout<<"Error allocating connection handler --- error = "<<retcode<<endl;
             SQLFreeHandle(SQL_HANDLE_DBC,hdbc1);
        }
   }
   else
   {
         cout<<"Error allocating environment handler --- error = "<<retcode<<endl;
        SQLFreeHandle(SQL_HANDLE_ENV,henv1);
   }
   dbopen=res;
   return res;
}

int main()
{
      cout<<"Starting....."<<endl;
      DBopen();
      cout<<"end of function ...!!!"<<endl;
      return 0;
}

sorry, i have not used this api for a while so i dont know why it dont work.

BTW
when you pass the stlen of the sql string to the prepare or exec function add 1 for the null, on MS db's this is not required and will make no difference but it will crash if using an oracle driver

why do you not use my prewritten functions

Makerp : Documentation say those functions are deprecated. Is that ok, if I use them anyway. Will it work on Windows 2000 and MSSQLServer 7.0????


Thanks a lot for your time.

The ODBC driver manager will also translate calls from deprecated (ODBC 1.0 & 2.0) into ODBC 3.0 where the driver demands it.

Its slower but it make life easy.

    Andy.