C++

Forum

 
SQLCHAR* and const char

Pages: 123456
freddie1 (1838)
How about trying the code Lamblion is fighting with DSTR3A? That actually shows everything you want to do with the exception of getting a record count, and I can easily add that for you. Its brutally easy?
DSTR3A (97)
freddie1, Northwinds is fine. Lamblion is also fine. Like I said I am getting the info back, but this SQLCHAR* and const char are driving me nuts. I just hate having that error. I'll do whatever you come up with!!!! Thank you so very much!

 
 
SQLCHAR* query = reinterpret_cast<SQLCHAR*>("SELECT tblIP.[IPAddress], tblIP.[IPStatus], tblIP.[IPType] FROM tblIP ORDER BY tblIP.[IPAddress] ASC");

didn't work.
Last edited on
freddie1 (1838)
Tell you what DSTR3A, I think Lamblion is going to get his working very shortly, because I'm convinced the whole problem we are having concerned Microsoft's changing of the Driver Description as we found out. So I'm going to go ahead and post that working program as I now have it, and I added a procedure to get the record count. You'll see it in the code I post. I believe its named GetRecordCount(). I'll start here...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
 
#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();
}

int GetRecordCount(SQL& Sql)
{
 unsigned int iRecCt=0;
 char szQuery[128];
 SQLHSTMT hStmt;
 long iJnk;

 strcpy(szQuery,"SELECT Count(*)  As RecordCount From Table1;");
 SQLAllocHandle(SQL_HANDLE_STMT,Sql.hConn,&hStmt);
 SQLBindCol(hStmt,1,SQL_C_ULONG,&iRecCt,0,&iJnk);
 if(SQLExecDirect(hStmt,(SQLCHAR*)szQuery,SQL_NTS)!=SQL_SUCCESS)
 {
    SQLGetDiagRec(SQL_HANDLE_STMT,hStmt,1,Sql.szErrCode,&Sql.iNativeErrPtr,Sql.szErrMsg,512,&Sql.iTextLenPtr);
    SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
    return -1;
 }
 else
 {
    SQLFetch(hStmt);
    SQLCloseCursor(hStmt);
    SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
    return iRecCt;
 }
}


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;
}


continued...

freddie1 (1838)
At the bottom is the output I'm now getting...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
 
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;
}


int main()
{

 char szBuffer[512];
 std::string s1;
 SQL Sql;

 Sql.strDriver="Microsoft Access Driver (*.mdb, *.accdb)";
 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");
       printf("Sql.szCnOut             = %s\n",Sql.szCnOut);
       printf("Sql.strConnectionString = %s\n",Sql.strConnectionString.c_str());
       if(blnMakeTable(Sql))
       {
          printf("blnMakeTable() Succeeded!\n");
          if(blnInsert(Sql))
          {
             printf("blnInsert() Succeeded!\n");
             unsigned int iCount=GetRecordCount(Sql);
             printf("iCount = %u\n\n",iCount);
             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;
}

/*
Sql.strDBQ = C:\Code\CodeBlks\MkAccessDB\TestData.mdb
Sql.blnConnected        = True
Sql.szCnOut             = DBQ=C:\Code\CodeBlks\MkAccessDB\TestData.mdb;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;
Sql.strConnectionString = DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=C:
\Code\CodeBlks\MkAccessDB\TestData.mdb;
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!
iCount = 4

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
*/
Last edited on
freddie1 (1838)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
//MySql.h
#if !defined(SQL_H)
#define SQL_H
#include <string>
#include <sqltypes.h>

class SQL
{
 public:
 SQL();
 ~SQL();
 void MakeConnectionString(void);
 void ODBCConnect(void);
 void ODBCDisconnect(void);

 public:
 std::string       strConnectionString;
 std::string       strDatabase;
 std::string       strDriver;
 std::string       strServer;
 std::string       strDBQ;
 char              szCnOut[512];
 short int         iBytes;
 SWORD             swStrLen;
 SQLHENV           hEnvr;
 SQLHDBC           hConn;
 SQLINTEGER        iNativeErrPtr;
 SQLSMALLINT       iTextLenPtr;
 SQLTCHAR          szErrMsg[512];
 SQLTCHAR          szErrCode[8];
 unsigned int      blnConnected;
};

#endif
freddie1 (1838)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
 
//Sql.cpp
#include  <windows.h>
#include  <string>
#include  <cstdio>
#include  <odbcinst.h>
#include  <sqltypes.h>
#include  <sql.h>
#include  <sqlext.h>
#include  "MySql.h"


SQL::SQL() //Constructor
{
 ZeroMemory(szCnOut, 512);
 ZeroMemory(szErrMsg,512);
 strDBQ="";
}


SQL::~SQL()
{
 //Sql Destructor
}


void SQL::MakeConnectionString(void)
{
 if(strDriver=="SQL Server")
 {
    if(strDBQ=="")
    {
       strConnectionString="DRIVER=";
       strConnectionString=strConnectionString+strDriver+";"+"SERVER="+strServer+";";
    }
    else
    {
       strConnectionString="DRIVER=";
       strConnectionString=strConnectionString+strDriver+";"+"SERVER="+strServer+";"+ \
       "DATABASE=" + strDatabase + ";" + "DBQ=" + strDBQ + ";";
    }
 }
 else if(strDriver=="Microsoft Access Driver (*.mdb, *.accdb)")
 {
    strConnectionString="DRIVER=";
    strConnectionString=strConnectionString+strDriver+";"+"DBQ="+strDBQ+";";
 }
 else if(strDriver=="Microsoft Excel Driver (*.xls)")
 {
    strConnectionString="DRIVER=";
    strConnectionString=strConnectionString+strDriver+";"+"DBQ="+strDBQ+";";
 }
}


void SQL::ODBCConnect(void)
{
 char szCnIn[512];
 unsigned int iResult;

 MakeConnectionString();
 SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&hEnvr);
 SQLSetEnvAttr(hEnvr,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);
 SQLAllocHandle(SQL_HANDLE_DBC,hEnvr,&hConn);
 strcpy(szCnIn,strConnectionString.c_str());
 iResult=SQLDriverConnect(hConn,NULL,(SQLTCHAR*)szCnIn,(SQLSMALLINT)strlen(szCnIn),(SQLTCHAR*)szCnOut,512,&swStrLen,SQL_DRIVER_NOPROMPT);
 if(iResult==0 || iResult==1)
    blnConnected=TRUE;
 else
 {
    SQLGetDiagRec(SQL_HANDLE_DBC,hConn,1,szErrCode,&iNativeErrPtr,szErrMsg,512,&iTextLenPtr);
    blnConnected=FALSE;
    SQLDisconnect(hConn);
    SQLFreeHandle(SQL_HANDLE_DBC,hConn);
    SQLFreeHandle(SQL_HANDLE_ENV,hEnvr);
 }
}


void SQL::ODBCDisconnect(void)
{
 if(blnConnected==TRUE)
 {
    SQLDisconnect(hConn);
    SQLFreeHandle(SQL_HANDLE_DBC,hConn);
    SQLFreeHandle(SQL_HANDLE_ENV,hEnvr);
    blnConnected=FALSE;
 }
}
freddie1 (1838)
To get the above to work you need to set up your environment to link with odbc32.lib and odbccp32.lib. For MinGW that would be libodbc32.a and libodbccp32.a.
DSTR3A (97)
Thank you freddie1, I will play around with it. Just a quick question because from what I gather, you are very knowledgable about this, In the code I posted, why on earth can I not get around the const char* to SQLCHAR* problem? This code is being injected into a lot of other code that I had working with an online mysql database, however; I had a problem with the shared server always timing out and dropping connections so I decided to move my database to my local machine. I thought that would be simple! mysql with no front-end is a pain. I know access fairley well, so I build a database in access and thought that it would be a cinch to query that from my C++ code! This is turning into a nightmare. Even using the code that I posted, I would have to replace 6 lines of code with a 107 lines!!!!! Any input, much appreciated. Thank you once again.
Lamblion (642)
freddie1: I just copied your entire above code into a completely new project "database 02" and set multi-byte, /MTd (standalone debug), and I still get exactly the same error output.

Thanks for all your hard work on this, but I'd like to continue to see if we can get this solved.

I will TRY to go through it and see if I can figure out what is going on.

FWIW, I have Access 2010, and it has no problem running the older .mdb files. I am also running Win 7 64 bit, but I doubt that's causing the problem.
freddie1 (1838)
Say DSTR3A, I'll comment blnDumpData() below, because that seems to be where your are having your excrutiating difficulties with SQLCHAR* and such.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
UINT blnDumpData(SQL& sql)
{
 char szQuery[100],szBuffer[128],szDate[12];   //Note I'm using ordinary char arrays for query string 
 SQLTCHAR szString[20];                        //that will get fed into SQLExecDirect().  It is there
 TIMESTAMP_STRUCT ts;                          //in SQLExecDirect I'm putting a (SQLTCHAR*) cast on
 SQLINTEGER iJnk;                              //szQuery to stop the C++ compiler from complaining.
 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)  //Note cast
    {
       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 (1838)
Lamblion:

As I mentioned, the latest version of Access I have is that with Office 2007. I'm virtually certain the problem revolves around Microsoft screwing with Access Database Description names for whatever reason. I, too, would like to see this issue solved and working for you because where I work we have large amounts of code using ODBC to connect to Access databases, so eventually we'll need to get it working with newer releases of Access. Right now our main timber sale database is an Access database I created back around 2003 with Access 2000. We've never updated the database, even when we moved to XP and then Win7 32 bit last spring (that move is no where near complete yet).

But back to your situation I really have no idea at this point why it isn't working. I guess it won't work for DSTR3A either, so maybe I've wasted a lot of your time. Maybe ODBC doesn't work with Access *.mdbs anymore. It doesn't look like it does, to be perfectly honest. It might be time for an internet search to see if anyone has figured out how to use ODBC to connect to Microsoft's latest versions of Access. All I can say for sure is that this driver description string works with my Win7 32 bit with Office 2007...

Sql.strDriver="Microsoft Access Driver (*.mdb, *.accdb)";

Also, I'm having no problems with my 64 bit Win 7 either (running 32 bit code though).

In casting about trying to surmise what the problem might be, perhaps Microsoft has added some more requirements to the connection string for the later versions of Access. In my code above I'm only supplying a strDBQ, i.e., path to the database, and strDriver, i.e., the database description from the ODBC part of the registry. That's what that SQLDrivers() dumps. I think its time for an internet search of this problem. As you can imagine its hard for me to help much more because I can't get my code to fail on any systems I have access to.
freddie1 (1838)
I just went to the connection string website...

http://www.connectionstrings.com/access

and their examples don't even show the updated thingie with the *.accdb like I discovered!
freddie1 (1838)
I just saw this at the above site...

1
2
3
 
Enable admin statements
To enable certain programatically admin functions such as CREATE USER, CREATE GROUP, ADD USER, GRANT, REVOKE and DEFAULTS (when making CREATE TABLE statements) use this connection string.
Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;ExtendedAnsiSQL=1;


Why don't you try some of those extra descriptors to see if you can get it to work Lamblion?

DSTR3A:

I guess you will get stoped at the same point. How you coming with the SQLCHAR* thing? You know, I had big time troubles with that too years ago when I converted from C code to C++ code. C compilers didn't make a fuss. I ran into your problem with C++, and I think I even asked about it at www.daniweb.com, and the response I got was to just cast it away however possible. Its a lot easier programming this stuff in PowerBASIC. There you don't have to deal with casting and myriads of typedefs of basic 32 bit integral numbers.
Last edited on
DSTR3A (97)
Thank you. So it seems to be a cranky compiler issue! Being that I'm in deeper than I admit. How would I integrate this with the working code that I posted?
DSTR3A (97)
This fixed the puppy!

SQLCHAR* query = (SQLCHAR *)"SELECT tblIP.[IPAddress], tblIP.[IPStatus], tblIP.[IPType] FROM tblIP ORDER BY tblIP.[IPAddress] ASC;";
DSTR3A (97)
lamblion, I would use the code I posted as a starter and don't forget to update that line I just posted. It connects, it queries and returns! Life is good!

freddie1
Thank you so much for all of your help! You've been wonderful. Just one quick question. Based on the code I am using. 1) what is it called? not mysql? Does it have a name? Also how would I preform UPDATES AND INSERTS? Thank you once again. Goodluck lamblion!
freddie1 (1838)
I don't think I've been a help to anyone. No one else can successfully run any of my code, however well it might work for me.

All database operations using ODBC are carried out by executing SQL statements. SELECT statements retrieve data, and one binds variables one declares in one's procedures using SQLBindCol().

Inserting new records is done with SQL INSERT statements. You would use SQLBindParameter() to bind the address of a variable you declare in your procedure with the database field you want to insert into. Look at my procedure blnInsert in my worthless code. Generally, when dealing with inserts you need to deal with updates too. For example, if you try to insert a new row and there is a primary key involved, thje database management system won't allow the insert of the duplicate record. So what you usually must do is attempt to select the record, or do a count of the number of records that match some criteria, and only if there are none can you do an INSERT. If you find a matching record, then you must do an UPDATE query. Here is some code from one of my PowerBASIC applications showing how it is done by 1st doing a record count to see if the record exists, then an insert or update depending on that outcome...

freddie1 (1838)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
 
Function blnUpdateWildlife(hWnd As Dword) As Byte
  #Register None
  Local szSaleNumber As Asciiz*3,szType As Asciiz*2,szNetworkPath As Asciiz*64
  Local szDist As Asciiz*3,szYear As Asciiz*5,szDBName As Asciiz*64
  Local szWdl As Asciiz*3,szQuery As Asciiz*200
  Local szDeer As Asciiz*2,szAcres As Asciiz*5
  Local hStmt,hMain,hSaleTracking As Dword
  Local iMsg,fpLog,iRecCt,iJnk As Long
  Local ptrDBPaths As szPaths Pointer
  Local strType As String
  Local Sql As tagODBC
  
  hSaleTracking=GetWindowLong(hWnd,0)
  hMain=GetWindowLong(hSaleTracking,0)
  ptrDBPaths=GetWindowLong(hMain,0)
  szDBName=@ptrDBPaths.szDBName  
  szNetworkPath=@ptrDBPaths.szNetworkPath
  Call ODBCConnect(szDBName,Sql) 
  If Sql.blnConnected=%TRUE Then
     Call SendMessage(GetDlgItem(hSaleTracking,%IDC_CBO_TRACKING_DIST),%WM_GETTEXT,3,Varptr(szDist))
     Call SendMessage(GetDlgItem(hSaleTracking,%IDC_CBO_TRACKING_YEAR),%WM_GETTEXT,5,Varptr(szYear))
     Call SendMessage(GetDlgItem(hSaleTracking,%IDC_CBO_TRACKING_TYPE),%WM_GETTEXT,3,Varptr(szType)) 
     Call SendMessage(GetDlgItem(hSaleTracking,%IDC_CBO_TRACKING_NUM),%WM_GETTEXT,3,Varptr(szSaleNumber))
     Select Case szType
       Case "BC"
         strType="0"
       Case "B_"
         strType="1"
       Case "_C"
         strType="2"
       Case "DF"
         strType="3"  
     End Select 
    
     'Update Acreage In Main
     If SQLAllocHandle(%SQL_HANDLE_STMT,Sql.hConn,hStmt)<>0 Then
        iMsg=MsgBox("Unable To Obtain ODBC Statement Handle ", _
        %MB_ICONERROR,"ODBC Problem!")
        Call ODBCDisconnect(Sql)
        blnUpdateWildlife=%FALSE
        Exit Function 
     End If
     Call GetWindowText(GetDlgItem(hWnd,%IDC_EDIT_WILDLIFE_ACREAGE),szAcres,5)  
     szQuery= _
     "UPDATE " & _
       "Main " & _
     "SET " & _
       "Main.Acres="+Trim$(szAcres)+" " & _
     "WHERE " & _
       "Main.Dist="+szDist+" And " & _
       "Main.Yr="+szYear+" And " & _
       "Main.Type="+strType+" And " & _
       "Main.Num="+szSaleNumber+";"
     #If %Def(%MYDEBUG)  
     #EndIf
     If SQLExecDirect(hStmt,szQuery,%SQL_NTS)<>0 Then
        iMsg=MsgBox("SQLExecDirect(hStmt,szQuery,%SQL_NTS) Failed!!!", _
        %MB_ICONERROR,"Couldn't Update Acreage In Main!")
        Call SQLFreeHandle(%SQL_HANDLE_STMT,hStmt)
        Call ODBCDisconnect(Sql)
        blnUpdateWildlife=%FALSE
        Exit Function 
     End If
     Call SQLFreeHandle(%SQL_HANDLE_STMT,hStmt)         
     
     'Update Wildlife Table 
     Call GetWindowText(GetDlgItem(hWnd,%IDC_EDIT_WILDLIFE_DEER_IMPACT),szDeer,2)
     Call GetWindowText(GetDlgItem(hWnd,%IDC_EDIT_WILDLIFE_WMU),szWdl,3)  
     If SQLAllocHandle(%SQL_HANDLE_STMT,Sql.hConn,hStmt)<>0 Then
        iMsg=MsgBox("Unable To Obtain ODBC Statement Handle ", _
        %MB_ICONERROR,"ODBC Problem!")
        Call ODBCDisconnect(Sql)
        blnUpdateWildlife=%FALSE
        Exit Function 
     End If
     'Code to see if record is in wildlife
     szQuery= _
     "SELECT " & _
       "Count(*) As RecordCount " & _
     "FROM " & _
       "Wildlife " & _
     "WHERE " & _
       "Dist="+szDist+" And " & _
       "Yr="+szYear+" And " & _
       "Type="+strType+" And " & _
       "Num="+szSaleNumber+";"
     Call SQLBindCol(hStmt,1,%SQL_C_ULONG,iRecCt,0,iJnk)
     If SQLExecDirect(hStmt,szQuery,%SQL_NTS)<>0 Then
        Call SQLFreeHandle(%SQL_HANDLE_STMT,hStmt)
        iMsg=MsgBox("SQLExecDirect(hStmt,szQuery,%SQL_NTS) Failed In blnUpdateWildlife()", _
        %MB_ICONERROR,"Couldn't Determine If Record Exists In Wildlife!")
        Exit Function
     Else
        Print #fpLog, "SQLExecDirect(hStmt,szQuery,%SQL_NTS) In blnUpdateWildlife() Succeeded In Getting A RecordCount!"
     End If
     Call SQLFetch(hStmt)
     Call SQLCloseCursor(hStmt)
     Call SQLFreeHandle(%SQL_HANDLE_STMT,hStmt)
     If iRecCt=1 Then
        szQuery= _
        "UPDATE " & _
          "Wildlife " & _
        "SET " & _
          "Wildlife.iDeer="+Trim$(szDeer)+", " & _
          "Wildlife.strWMU="+Chr$(39)+szWdl+Chr$(39)+" " & _
        "WHERE " & _
          "Wildlife.Dist="+szDist+" And " & _
          "Wildlife.Yr="+szYear+" And " & _
          "Wildlife.Type="+strType+" And " & _
          "Wildlife.Num="+szSaleNumber+";" 
        Call SQLAllocHandle(%SQL_HANDLE_STMT,Sql.hConn,hStmt)
        If SQLExecDirect(hStmt,szQuery,%SQL_NTS)<>0 Then
           iMsg=MsgBox("(Update Wildlife) -- SQLExecDirect(hStmt,szQuery,%SQL_NTS) Failed!!!", _
           %MB_ICONERROR,"Couldn't Update Wildlife!")
           Call SQLFreeHandle(%SQL_HANDLE_STMT,hStmt)
           Call ODBCDisconnect(Sql)
           blnUpdateWildlife=%FALSE
           Exit Function 
        End If
        Call SQLFreeHandle(%SQL_HANDLE_STMT,hStmt)         
        MsgBox("Wildlife Data Updated In Main And Wildlife!")
        Call ODBCDisconnect(Sql)
        blnUpdateWildlife=%TRUE
     Else
        szQuery= _
        "INSERT INTO Wildlife " & _
        "(" & _
          "Dist, " & _
          "Yr, " & _
          "Type, " & _
          "Num," & _
          "iDeer," & _
          "strWMU" & _
        ") " & _
        "VALUES " & _
        "(" & _
          szDist & "," & _
          szYear & "," & _
          strType & "," & _
          szSaleNumber & "," & _
          Trim$(szDeer) & "," & _
          Chr$(39)+szWdl+Chr$(39) & _
        ");"
        If SQLAllocHandle(%SQL_HANDLE_STMT,Sql.hConn,hStmt)<>0 Then
           Call ODBCDisconnect(Sql)
           iMsg=MsgBox("Failed To Obtain ODBC Statement Handle!",%MB_ICONERROR,"Sale Name")
           Exit Function
        End If
        If SQLExecDirect(hStmt,szQuery,%SQL_NTS)<>0 Then
           Call SQLFreeHandle(%SQL_HANDLE_STMT,hStmt)
           Call ODBCDisconnect(Sql)
           iMsg=MsgBox("SQLExecuteDirect(hStmt,szQuery,%SQL_NTS) Failed The Insert Operation Into Wildlife!", _
           %MB_ICONERROR,"Sale Name")
        End If
        MsgBox("Sale Should Be In Wildlife")
        blnUpdateWildlife=%TRUE
        Call SQLFreeHandle(%SQL_HANDLE_STMT,hStmt)
        Call ODBCDisconnect(Sql)
     End If
  Else
     iMsg=MsgBox("Unable To Make ODBC Connection To " & szDBName & ".", _
     %MB_ICONERROR,"Database Connection Problem!")
     blnUpdateWildlife=%FALSE
  End If
End Function
Lamblion (642)
DSTR3A: I got yours working perfectly. Thanks very much for the code!!!

freddie1: I replaced the driver in your main with the above, but still no go. I wonder what in the heck it is???

I'm going to pull out my netbook and test it on that. It is three or four years old, running XP.

I'll let you know what happens.
DSTR3A (97)
Lamblion
I'm glad I could help you, Good luck.

Freddie1
Thank you for the extra code. I am going to run off and play around with it. You've been a big help and I appreciate it. Have you given it any thought about my other isuue of timing out on the server? Is there a way to keep that connection alive? I hate to junk my other code as eventually the program does have to query the database on the net. Let me know. I would like to keep the door open as I might be needing someone part-time down the road, remotely of course. Pennsylvania must be beautiful about now!
Pages: 123456