SQLCHAR* and const char

[DSTR3A]

Here is how I am connecting to a MS Access 2010 database with DirectODBC. The connecting part is working fine. However; the SELECT COUNT and such is giving me a major headache.

This goes before main()
/* Data Access Method used for Access database */
const char* DAM = "Direct ODBC";

/* Connection string for Direct ODBC */
SQLCHAR szDSN[256] = 
    "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=C:\\FILEBLOCK\\Fileblocker.accdb;";

This is further down

HENV    hEnv;
HDBC    hDbc;
/* ODBC API return status */
SQLRETURN  rc;

SQLSMALLINT  iConnStrLength2Ptr;
SQLCHAR      szConnStrOut[255];
				    
SQLCHAR         chval1[128], chval2[128], colName[128];
SQLINTEGER      ret1, ret2;

/* Number of rows in result set */
SQLINTEGER      rowCount = 0;
				
HSTMT           hStmt;
				
/* Allocate an environment handle */
rc = SQLAllocEnv(&hEnv);
/* Allocate a connection handle */
rc = SQLAllocConnect(hEnv, &hDbc);
/* Connect to the 'Fileblocker.accdb' database */
rc = SQLDriverConnect(hDbc, NULL, szDSN,  _countof(szDSN), 
szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
if (SQL_SUCCEEDED(rc)) 
	{
	printf("%s: Successfully connected to database. Data source name: \n  %s\n", 
	DAM, szConnStrOut);
	const SQLCHAR* query = "SELECT COUNT(*) FROM tblIP WHERE IP = '%s' AND IPStatus = '1' AND IPType = '3' AND IPMax ='0'";
	mysql_query(conn, query); //query const SQLCHAR not matching with const char
	my_ulonglong i = 0;
	res_set = mysql_store_result(conn);
	my_ulonglong numrows = mysql_num_rows(res_set);
	LEGIT = mysql_fetch_row(res_set);
	//mysql_free_result(res_set);
	if (atoi(LEGIT[i]) == 1)

[freddie1]

Here is an updated Main() for you. Just main. It shows some debug output if the connection fails....

look at the if near bottom

int main()
{
 char szBuffer[512];
 std::string s1;
 SQL Sql;

 Sql.strDriver="Microsoft Access Driver (*.mdb)";
 GetCurrentDirectory(512,szBuffer);
 s1=szBuffer;
 Sql.strDBQ=s1+"\\"+"TestData.mdb";
 printf("Sql.strDBQ = %s\n",Sql.strDBQ.c_str());
 if(iCreateDB(Sql.strDBQ.c_str())==TRUE)
 {
   Sql.ODBCConnect();
   if(Sql.blnConnected==TRUE)
    {
       printf("Sql.blnConnected = True\n");
       if(blnMakeTable(Sql))
       {
          printf("blnMakeTable() Succeeded!\n");
          if(blnInsert(Sql))
          {
             printf("blnInsert() Succeeded!\n\n\n");
             blnDumpData(Sql);
          }
       }
       Sql.ODBCDisconnect();
    }
    else
    {
       printf("Sql.blnConnected                = FALSE\n");
       printf("Sql.strConnectionString.c_str() = %s\n",Sql.strConnectionString.c_str());
       printf("Sql.szErrMsg                    = %s\n",Sql.szErrMsg);
       printf("Sql.szCnOut                     = %s\n",Sql.szCnOut);
    }
 }
 getchar();

 return 0;
}


/*
Sql.strDBQ = C:\Code\CodeBlks\MkAccessDB\TestData.mdb
Sql.blnConnected = True
blnMakeTable() Succeeded!
                                                         SQLExecute(hStmt)
iId      Double           Date           String           0=SQL_SUCCESS
========================================================================
1         3.14           11/15/1952    My Birthday              0
2         1.23           6/30/1969     Walk On Moon?            0
3        15.12           1/1/2006      Some String              0
4         0.54           4/1/2006      April Fools Day          0

blnInsert() Succeeded!


iId      Double           Date           String           0=SQL_SUCCESS
========================================================================
1         3.14           11/15/1952    My Birthday              0
2         1.23           6/30/1969     Walk On Moon?            0
3        15.12           1/1/2006      Some String              0
4         0.54           4/1/2006      April Fools Day          0

*/

[freddie1]

Or better yet (and I think your problem is iCreateDB() is failing)....

int main()
{
 char szBuffer[512];
 std::string s1;
 SQL Sql;

 Sql.strDriver="Microsoft Access Driver (*.mdb)";
 GetCurrentDirectory(512,szBuffer);
 s1=szBuffer;
 Sql.strDBQ=s1+"\\"+"TestData.mdb";
 printf("Sql.strDBQ = %s\n",Sql.strDBQ.c_str());
 if(iCreateDB(Sql.strDBQ.c_str())==TRUE)
 {
    Sql.ODBCConnect();
   if(Sql.blnConnected==TRUE)
    {
       printf("Sql.blnConnected = True\n");
       if(blnMakeTable(Sql))
       {
          printf("blnMakeTable() Succeeded!\n");
          if(blnInsert(Sql))
          {
             printf("blnInsert() Succeeded!\n\n\n");
             blnDumpData(Sql);
          }
       }
       Sql.ODBCDisconnect();
    }
    else
    {
       printf("Sql.blnConnected                = FALSE\n");
       printf("Sql.strConnectionString.c_str() = %s\n",Sql.strConnectionString.c_str());
       printf("Sql.szErrMsg                    = %s\n",Sql.szErrMsg);
       printf("Sql.szCnOut                     = %s\n",Sql.szCnOut);
    }
 }
 else
 {
    printf("iCreateDB() Failed!!!\n");
 }
 getchar();

 return 0;
}

[freddie1]

I gotta head home now, so may not be able to answer for a couple hours.

[Lamblion]

Do I perhaps need to register the database through Control Panel? Could that be the problem? Did you have to register yours?

[DSTR3A]

lamblion did you see my code? It connects. My problem is SQLCHAR on the SELECT statement.

[Lamblion]

I don't know enough about it to make any helpful statements. I'm still trying to grasp the bare basics. I assume since you say it connects that the DB does NOT have to be registered.

In reading Ivor Horton's example on DB programm (with managed code, not C/C++, which I don't care about), he also says one must register the DB through Control Panel.

But I guess he's talking about something different since you obviously can connect without having to register it.

[DSTR3A]

I'm with you. I am as wll grasping at this point. From what I hear connecting is the hard part, so I guess I have it backwards because doing something with that connection is whats killing me!!!!!!! I'l keep you posted.

[freddie1]

Just to be clear, I am using multi-byte character set, /MTd (stand-alone debug), and no UAC.

Yes, all that is fine, with the possible exception of the UAC. That could be the problem. You absolutely must be running as administrator to get something like this to work, at least I think so. I haven't tried running as a standard user. So, possibly make a shortcut to the exe and right click to start it and choose 'Run As Administrator'?

And no, it doesn't need to be registered in 'Control Panel'. We're doing some cross posting here, so be careful.

Lamblion:
Did you try with my previous (several posts up) modifications to function Main(). The output from that will tell us where the failure is (its either iCreateDB()) or something in ODBCConnect. We need to find out).

DSTR3A:
I'd like to help with your problem too, but I've got kind of a one track mind. In other words, I don't multi-task too good. As soon as we can come to some kind of closure on Lamblion's difficulties, I'll look ay yours.

[Lamblion]

Okay, I put in the new main() and it tells me that iCreateDB() is where it fails.

[DSTR3A]

Thank you freddie1

[freddie1]

That is what I suspected. Here's the deal. The include below...

<odbcinst.h>

is necessary for SQLInstallerError() and SQLConfigDataSource(). Also, that is the reason for odbccp32.lib. I needed that include and that lib because it is the only way I know of to actually programatically create an Access Database. And this demo program needed to do that because that is kind of a standard, i.e., everybody running Windows has stock drivers installed for Microsoft Access whether or not they have Access installed.

I've updated Main.cpp below to provide added error reporting in cases of an installer error (what you are getting Lamblion) or a failure of a database connection. All I really modified was iInstallerError() and some more printf statements in Main....

[freddie1]

#include <windows.h>
#include <stdio.h>
#include <odbcinst.h>
#include <sql.h>
#include <sqlext.h>
#include "MySql.h"


unsigned int iInstallerError()
{
 DWORD pErr;
 char szErrMsg[512];
 WORD  cbMsgBuffer=512;
 WORD  cbRet;
 WORD  wErrNum=1;

 while(SQLInstallerError(wErrNum,&pErr,szErrMsg,cbMsgBuffer,&cbRet)!=SQL_NO_DATA)
 {
  printf("wErrNum    = %d\t",wErrNum);
  printf("szErrMsg = %s\n",szErrMsg);
  wErrNum++;
 };

 return (unsigned int)pErr;
}


unsigned int iCreateDB(char const* szDBName)    //To create a Microsoft Access Database from
{                                               //scratch, you use SQLConfigDataSource().  I
 char szCreate[256];                            //believe if the database already exists at the
                                                //specific location SQLInstallerError() returns
 strcpy(szCreate,"CREATE_DB=");                 //'11'.
 strcat(szCreate,szDBName);
 if(SQLConfigDataSource(0,ODBC_ADD_DSN,"Microsoft Access Driver (*.mdb)",szCreate))
    return TRUE;
 else
    return iInstallerError();
}


TIMESTAMP_STRUCT ParseDate(char* szDate, char* szFormat, char* szDelimiter)
{
 UINT i=0,j=0,k=0;
 TIMESTAMP_STRUCT ts;
 char buf[3][8];             //buf[0] for month, buf[1] for day, buf[2] for year
 char* p;

 memset(buf,0,sizeof(buf));  //zero out buf[]
 p=szDate;
 for(i=0;i<strlen((char*)szDate);i++)
 {
     if(*p!=*szDelimiter)
     {
        buf[j][k++]=*p;
        buf[j][k+1]='\0';
     }
     else
     {
        j++;
        k=0;
     }
     p++;
 }
 if(!stricmp((char*)szFormat,"MDY"))
 {
    ts.month=(short)atoi(buf[0]);
    ts.day=(short)atoi(buf[1]);
    ts.year=(short)atoi(buf[2]);
 }
 if(!stricmp((char*)szFormat,"DMY"))
 {
    ts.day=(short)atoi(buf[0]);
    ts.month=(short)atoi(buf[1]);
    ts.year=(short)atoi(buf[2]);
 }
 if(!stricmp((char*)szFormat,"YMD"))
 {
    ts.year=(short)atoi(buf[0]);
    ts.month=(short)atoi(buf[1]);
    ts.day=(short)atoi(buf[2]);
 }

 return ts;
}


void MkDate(TIMESTAMP_STRUCT& ts, char* szBuffer)
{
 char szMonth[4],szDay[4],szYear[8];

 sprintf(szMonth,"%u",ts.month);
 sprintf(szDay,"%u",ts.day);
 sprintf(szYear,"%u",ts.year);
 strcpy(szBuffer,szMonth);
 strcat(szBuffer,"/");
 strcat(szBuffer,szDay);
 strcat(szBuffer,"/");
 strcat(szBuffer,szYear);

 return;
}


UINT blnMakeTable(SQL& Sql)          //Uses SQL Create Table statement to add table
{                                    //to database represented by sql->hConn
 char szQuery[256];
 SQLHSTMT hStmt;

 strcpy(szQuery,"CREATE TABLE Table1 (Id LONG  NOT NULL PRIMARY KEY, Float_Point DOUBLE, Date_Field DATETIME, Text_Field CHAR(30));");
 SQLAllocHandle(SQL_HANDLE_STMT,Sql.hConn,&hStmt);
 if(SQLExecDirect(hStmt,(SQLTCHAR*)szQuery,SQL_NTS)==0)
 {
    SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
    return(TRUE);
 }
 else
    return(FALSE);
}


UINT blnInsert(SQL& sql)
{
 char* szStr[]={(char*)"My Birthday",(char*)"Walk On Moon?",(char*)"Some String",(char*)"April Fools Day"};
 char* szDate[]={(char*)"11/15/1952",(char*)"6/30/1969",(char*)"1/1/2006",(char*)"4/1/2006"};
 double dblNum[]={3.14159,1.23456,15.1234,0.54321};
 char  szQuery[100],szString[32],szBuffer[128];       //Let me give you a hint about something.  If you decide
 SQLINTEGER iNts=SQL_NTS;                             //to use raw ODBC as your database access methodology, the
 UINT i,id,iRet=FALSE;                                //hard part is SQLBindParameter() for inserting prepared
 TIMESTAMP_STRUCT ts;                                 //SQL statements, and SQLBindCol() for selecting data.  These
 SQLINTEGER iJnk;                                     //will inevitably take you some time to learn.  I chose an
 SQLHSTMT hStmt;                                      //integer, a double, a data, and a char string so as to get
 double dbl;                                          //you started on the most common data types.

 if(SQLAllocHandle(SQL_HANDLE_STMT,sql.hConn,&hStmt)==SQL_SUCCESS)
 {
    strcpy((char*)szQuery,"INSERT INTO Table1(Id, Float_Point, Date_Field, Text_Field) VALUES(?,?,?,?);");
    printf("                                                         SQLExecute(hStmt)\n");
    printf("iId      Double           Date           String           0=SQL_SUCCESS\n");
    printf("========================================================================\n");
    if(SQLPrepare(hStmt,(SQLTCHAR*)szQuery,SQL_NTS)==SQL_SUCCESS)
    {
       SQLBindParameter(hStmt,1,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,0,0,&id,0,&iJnk);
       SQLBindParameter(hStmt,2,SQL_PARAM_INPUT,SQL_C_DOUBLE,SQL_DOUBLE,0,0,&dbl,0,&iJnk);
       SQLBindParameter(hStmt,3,SQL_PARAM_INPUT,SQL_C_TYPE_DATE,SQL_TYPE_TIMESTAMP,16,0,&ts,0,&iJnk);
       SQLBindParameter(hStmt,4,SQL_PARAM_INPUT,SQL_C_TCHAR,SQL_CHAR,31,0,szString,32,&iNts);
       for(i=0;i<4;i++)
       {
           id=i+1, dbl=dblNum[i];
           ts=ParseDate(szDate[i],(char*)"mdy",(char*)"/");
           strcpy(szString,szStr[i]);
           if(SQLExecute(hStmt)==SQL_SUCCESS)
           {
              memset(szBuffer,0,128);
              printf("%-6u%8.2f           %-12.10s  %-20s%6u\n",id,dbl,szDate[i],szString,SQL_SUCCESS);
           }
           else
           {
              SQLGetDiagRec(SQL_HANDLE_STMT,hStmt,1,sql.szErrCode,&sql.iNativeErrPtr,sql.szErrMsg,512,&sql.iTextLenPtr);
              printf("  sql.dr.szErrCode = %s\n",sql.szErrCode);
              printf("  sql.dr.szErrMsg  = %s\n",sql.szErrMsg);
              SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
              return FALSE;
           }
       }
       iRet=TRUE;
       printf("\n");
    }
    SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
 }

 return iRet;
}


UINT blnDumpData(SQL& sql)
{
 char szQuery[100],szBuffer[128],szDate[12];
 SQLTCHAR szString[20];
 TIMESTAMP_STRUCT ts;
 SQLINTEGER iJnk;
 SQLHSTMT hStmt;
 double dblNum;
 UINT iId;

 if(SQLAllocHandle(SQL_HANDLE_STMT,sql.hConn,&hStmt)==SQL_SUCCESS)
 {
    strcpy(szQuery,"SELECT Table1.Id,Table1.Float_Point,Table1.Date_Field,Table1.Text_Field FROM Table1;");
    SQLBindCol(hStmt,1,SQL_C_ULONG,&iId,0,&iJnk);
    SQLBindCol(hStmt,2,SQL_C_DOUBLE,&dblNum,0,&iJnk);
    SQLBindCol(hStmt,3,SQL_C_TYPE_DATE,&ts,0,&iJnk);
    SQLBindCol(hStmt,4,SQL_C_TCHAR,szString,20,&iJnk);
    if(SQLExecDirect(hStmt,(SQLTCHAR*)szQuery,SQL_NTS)==SQL_SUCCESS)
    {
       printf("iId      Double           Date           String           0=SQL_SUCCESS\n");
       printf("========================================================================\n");
       do
       {
          if(SQLFetch(hStmt)==SQL_NO_DATA)
             break;
          memset(szBuffer,0,128);
          MkDate(ts,szDate);
          printf("%-6u%8.2f           %-12.10s  %-20s%6u\n",iId,dblNum,szDate,szString,SQL_SUCCESS);
       }  while(TRUE);
    }
    SQLCloseCursor(hStmt);
    SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
    return TRUE;
 }

 return FALSE;
}

[freddie1]

Main()

int main()
{
 char szBuffer[512];
 std::string s1;
 SQL Sql;

 Sql.strDriver="Microsoft Access Driver (*.mdb)";
 GetCurrentDirectory(512,szBuffer);
 s1=szBuffer;
 Sql.strDBQ=s1+"\\"+"TestData.mdb";
 printf("Sql.strDBQ = %s\n",Sql.strDBQ.c_str());
 if(iCreateDB(Sql.strDBQ.c_str())==TRUE)
 {
    Sql.ODBCConnect();
    if(Sql.blnConnected==TRUE)
    {
       printf("Sql.blnConnected = True\n");
       if(blnMakeTable(Sql))
       {
          printf("blnMakeTable() Succeeded!\n");
          if(blnInsert(Sql))
          {
             printf("blnInsert() Succeeded!\n\n\n");
             blnDumpData(Sql);
          }
       }
       Sql.ODBCDisconnect();
    }
    else
    {
        printf("Sql.blnConnected  = %d\n",Sql.blnConnected);
        printf("Sql.iNativeErrPtr = %ld\n",(SQLINTEGER)Sql.iNativeErrPtr);
        printf("Sql.szErrCode     = %s\n",Sql.szErrCode);
        printf("Sql.szErrMsg      = %s\n",Sql.szErrMsg);
    }
 }
 else
 {
    printf("Sql.blnConnected  = %d\n",Sql.blnConnected);
    printf("iCreateDB() Failed, i.e., we couldn't create the Access Database.  Nicht Gut!\n");
 }
 getchar();

 return 0;
}

[freddie1]

I'm kind of running out of ideas Lamblion as to tell you what to do. Are you running as an administrator?

If it still doesn't work I'm tempted to guess it has something to do with those linker problems. Did you say you were now using VS2008 instead of VS2010?

What you could possibly try is to create the database yourself manually, making pains to save it back as an Access version 97 - 2000 database or whatever with the save as, and seeing if the rest of the code works. I know you are telling the truth and it isn't working, but being as I'm unable to recreate the problem on any of my computers (I have several from Win98 up to Win7 64 bit) I'm having a hard time figguring out what the problem is.

Anyway, if you want to try that open Access and create a new blank database named TestData.mdb. First try saving it as an old version Access DB, like Access 2000 maybe. Then put it wherever your executable is. Then rem out the lines in main that are involved in creating the database. In otrher words, like this...

int main()
{
 char szBuffer[512];
 std::string s1;
 SQL Sql;

 Sql.strDriver="Microsoft Access Driver (*.mdb)";
 GetCurrentDirectory(512,szBuffer);
 s1=szBuffer;
 Sql.strDBQ=s1+"\\"+"TestData.mdb";
 printf("Sql.strDBQ = %s\n",Sql.strDBQ.c_str());
 //if(iCreateDB(Sql.strDBQ.c_str())==TRUE)
 //{
    Sql.ODBCConnect();
    if(Sql.blnConnected==TRUE)
    {
       printf("Sql.blnConnected = True\n");
       if(blnMakeTable(Sql))
       {
          printf("blnMakeTable() Succeeded!\n");
          if(blnInsert(Sql))
          {
             printf("blnInsert() Succeeded!\n\n\n");
             blnDumpData(Sql);
          }
       }
       Sql.ODBCDisconnect();
    }
    else
    {
        printf("Sql.blnConnected  = %d\n",Sql.blnConnected);
        printf("Sql.iNativeErrPtr = %ld\n",(SQLINTEGER)Sql.iNativeErrPtr);
        printf("Sql.szErrCode     = %s\n",Sql.szErrCode);
        printf("Sql.szErrMsg      = %s\n",Sql.szErrMsg);
    }
 //}
 //else
 //{
 //   printf("Sql.blnConnected  = %d\n",Sql.blnConnected);
 //   printf("iCreateDB() Failed, i.e., we couldn't create the Access Database.  Nicht Gut!\n");
 //}
 getchar();

 return 0;
}

If you want you could rem out <odbcinst.h> and remove odbccp32.lib from your linker settings. Then recompile and see if it can connect to your self made DB. I think that's worth a try 'till we can figure out what is going on with SQLConfigDataSource.

[freddie1]

On the other hand, you could install any other C++ compiler in ten minutes or so and in another ten minutes have the program working perfectly. I'd recommend Code::Blocks with MinGW.

[freddie1]

DSTR3A:

Did you open Access and go to 'Query View' >> 'SQL View' and try to execute this....

SELECT COUNT(*) FROM tblIP WHERE IP = '%s' AND IPStatus = '1' AND IPType = '3' AND IPMax ='0'

...directly within Access? Oftentimes when I work with SQL I first execute the queries directly in the database management system manually (actually, I usually develop them there first anyhow)?

That is the first thing to check, i.e., whether you have valid SQL. If the SQL statement has an error in it it doesn't matter how perfect your code is - it won't work.

[freddie1]

Also, you need to use SQLGetDiagRecord(). Check out my code to see how it works. ODBC outputs real decent error info and I have found it invaluable. I could never have learned ODBC without it. Basically, SQLGetDiagRecord() requires you to set up some character string buffers which it writes error info to. Like I said, check out my code.

Also, to try out that query you naturally need to put real values in the %s stuff. Further, if it is a statement that failed you'll be giving SQLGetDiagRecord a handle to a statement.

[DSTR3A]

Hello Freddie1. Yes the query works in Access. I think the problem I'm having is "Types" I'm connected but it seems that SQLCHAR* and const char don't match, I ran pretty much the same code in VS 2010 and the sample has the same error. It's definity the types as opposed to the query itself. If I change it to const SQLCHAR* query, ten further down the code "query" complains! Thank you for your help!

[freddie1]

You appear to be using some wrappers possibly unique to My Sql because I get lost in your code after you get your connection. However, I'll say this - Can you declare your variables as char or const char* or as char or const char arrays, i.e.,


char szQuery[]="SELECT COUNR (*) As Record_Count From......";

and them put SQLCHAR* casts on them where you are feeding them into your various wrapper functions?