C++

Forum

 
Open Database Connnectivity - ODBC Revisited

freddie1 (1838)
Occasionally I get a PM or email about various posts I've made here over the years regarding ODBC (Open Database Connectivity) technology and connecting to various data sources such as Microsoft Access, SQL Server, or Microsoft Excel. Some of that code I originally posted is quite old, i.e. five to ten years old. Recently I unearthed some of that code (in response to a PM from member FlatlanD) ...

http://www.cplusplus.com/forum/windows/51320/

...and retested it with recent compilers. So I want to repost updated versions of it. It has come to my attention that various members have had difficulties with that code, and I want to address that. Its a tricky but neat example, but I want to start simpler and more basic this time around.

What I want to do is post very small and simple examples of the series of operations necessary to initialize the ODBC libraries. These short examples can be built x86 or x64 on any system. In fact, I believe they'll work with no or few modifications on Linux. After that I'll put together a Class for making ODBC Connections. Only then will we tackle that original neat but tricky example of mine.

In case you are wondering what ODBC is, its a very low level and foundational database access technology built by Microsoft in the 1990s. It has become a standard across many platforms. It was recently updated by Microsoft along with all the other necessary changes to work in 64 bit architectures. Its a C based procedural thing (unlike OLE/COM ADO), but some of its nastiness can be encapsulated neatly away into classes, which I'll get to.

My first example is Test1.cpp. All it does is allocate something known as an environment handle. Its a first step...

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
 
/*
  ODBC Step 1: Getting ODBC Environment Handle

  Test1.cpp shows fundamental operation of beginning ODBC connection sequence. 
  To set up a database connection one needs to obtain a handle to an ODBC
  environment - SQLHENV, then call SQLSetEnvAttr() to set the ODBC version
  being used.  Next one needs to allocate and obtain a connection handle -
  SQLHDBC.  With that one can finally call SQLDriverConnect().  This program
  only shows getting an environment handle.  Top two lines below are command 
  compilation strings for linkage to TCLib and LIBCMT.LIB.  After that are
  program sizes using each for production of stand alone static linked
  binaries.  You don't have TCLib so you'll have to use the second shorter
  command line string if compiling from the command line.  Otherwise you'll
  need to set up your development environment to link with odbc32.lib.  Program
  can be compiled either x86 or x64.
*/

// cl  Test1.cpp /O1 /Os /GS- TCLib.lib kernel32.lib user32.lib odbc32.lib
// cl  Test1.cpp /O1 /Os /MT /GS- odbc32.lib
// g++ Test1.cpp -oTest1.exe -mconsole -m64 -Os -s -lodbc32
// 102,912 Bytes VC19 (VStudio 2015) x86 ansi LIBCMT Linkage
//  55,808 Bytes VC15 (VStudio 2008) x64 ansi LIBCMT Linkage
//  38,400 Bytes GCC  4.9 x64 ansi Std. C Runtime Linkage 
//   3,584 Bytes VC19 (VStudio 2015) x64 ansi TCLib  Linkage
//   3,584 Bytes VC15 (VStudio 2008) x64 ansi TCLib  Linkage
//   3,072 Bytes VC19 (VStudio 2015) x86 ansi TCLib  Linkage
//   2,048 Bytes VC15 (VStudio 2008) x86 ansi TCLib  Linkage
//#define TCLib
#include <windows.h>
#include <sqlext.h>
#ifdef TCLib
   #include "stdlib.h"
   #include "stdio.h"
#else
   #include <cstdlib>
   #include <cstdio>
#endif

int main()
{
 SQLRETURN iReturn = 0;
 SQLHENV   hEnvr   = NULL;

 iReturn=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&hEnvr);
 if(iReturn==SQL_SUCCESS || iReturn==SQL_SUCCESS_WITH_INFO)
 {
    printf("SQLAllocHandle(SQL_HANDLE_ENV) Succeeded!\n");
    SQLFreeHandle(SQL_HANDLE_ENV,hEnvr);
 }
 else
    printf("SQLAllocHandle(SQL_HANDLE_ENV) Failed!\n");
 getchar();

 return 0;
}


/*
SQLRETURN SQLAllocHandle
(
  SQLSMALLINT   HandleType,
  SQLHANDLE     InputHandle,
  SQLHANDLE*    OutputHandlePtr
);

C:\Code\VStudio\VC15\ODBC\x86\Test1>cl Test1.cpp /O1 /Os /GS- TCLib.lib kernel32.lib user32.lib odbc32.lib
Microsoft (R) C/C++ Optimizing Compiler Version 19.00.23506 for x86
Copyright (C) Microsoft Corporation.  All rights reserved.

Test1.cpp
Microsoft (R) Incremental Linker Version 14.00.23506.0
Copyright (C) Microsoft Corporation.  All rights reserved.

/out:Test1.exe
Test1.obj
TCLib.lib
kernel32.lib
user32.lib
odbc32.lib

C:\Code\VStudio\VC15\ODBC\x86\Test1>Test1
SQLAllocHandle(SQL_HANDLE_ENV) Succeeded!
*/


To get this to build and work you need to do whatever your IDE requires to add odbc32.lib to your build system. Personally, I don't build my apps through use of an IDE, but I rather prefer to build them from the command line with build scripts, batch files, make files or whatever strikes my fancy at the time. Above I've included about a half a dozen command line strings for various compilers ranging from old and new versions of Microsoft's MSVC to GCC. Being an old timer program size interests me and you can see I've listed the resulting executable sizes next to each compiler, and they range from a large of 102,912 bytes x64 with VC 19 (Visual Studio 2015 Community Edition) statically linked against the C Standard Library (LIBCMT.LIB), to a small of 2,048 bytes x86 using my own C Runtime startup code (TCLib.lib). All sizes are with static linkage, i.e., no runtime redistributables required.
freddie1 (1838)
Next step is setting the environmental attribute. Its a bunch or nerdy stuff....

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
 
/*
  ODBC Step 2: Setting ODBC Environment Version
  
  Shows how to set and call SQLSetEnvAttr().  I believe Version 3 is the latest
  version.
  
  I haven't talked about ODBC variable types yet.  Not related to setting the
  environment version in any way, but I'll mention it here.  For whatever reason
  the creators of ODBC were not happy using the typical C and C++ data types, e.g.,
  char, wchar_t, int, long, float, double, etc.  They decided to create all their
  own with #define macros or typedefs.  Here is a heavily modified version 
  (modified by me just for expository purposes) - the real ones can be found in
  sqltypes.h....
  
  typedef unsigned char           SQLCHAR;
  typedef signed char             SQLSCHAR;
  typedef unsigned char           SQLDATE;
  typedef unsigned char           SQLDECIMAL;
  typedef double                  SQLDOUBLE;
  typedef double                  SQLFLOAT;
  
  typedef long                    SQLINTEGER;
  typedef unsigned long           SQLUINTEGER;

  typedef INT64                   SQLLEN;
  typedef UINT64                  SQLULEN;
  typedef UINT64                  SQLSETPOSIROW;
  #define SQLLEN                  SQLINTEGER
  #define SQLULEN                 SQLUINTEGER
  #define SQLSETPOSIROW           SQLUSMALLINT
  
  typedef void *                  SQLPOINTER;
  typedef float                   SQLREAL;
  typedef short                   SQLSMALLINT;
  typedef unsigned short          SQLUSMALLINT;
  typedef SQLSMALLINT             SQLRETURN;

  typedef void*                   SQLHANDLE;
  typedef SQLINTEGER              SQLHANDLE;
  typedef SQLHANDLE               SQLHENV;
  typedef SQLHANDLE               SQLHDBC;
  typedef SQLHANDLE               SQLHSTMT;
  typedef SQLHANDLE               SQLHDESC;
  typedef SQLINTEGER              SQLHENV;
  typedef SQLINTEGER              SQLHDBC;
  typedef SQLINTEGER              SQLHSTMT;

  typedef unsigned char           UCHAR;
  typedef signed char             SCHAR;
  typedef SCHAR                   SQLSCHAR;
  typedef long int                SDWORD;
  typedef short int               SWORD;
  typedef unsigned long int       UDWORD;
  typedef unsigned short int      UWORD;

  typedef signed long             SLONG;
  typedef signed short            SSHORT;
  typedef unsigned long           ULONG;
  typedef unsigned short          USHORT;
  typedef double                  SDOUBLE;
  typedef double            		    LDOUBLE;
  typedef float                   SFLOAT;

  typedef void*                   HENV;
  typedef void*                   HDBC;
  typedef void*                   HSTMT;
  typedef signed short            RETCODE;

  The above should give you the flavor of what I'm talking about.  It gives you
  two choices in your coding.  Choice #1 is to resist and attempt to use the C/C++
  data types we all know and love.  This can work.  However, to make it work one
  has the onerous job of having to cast every parameter in ODBC function calls to
  the ODBC defined types.  Otherwise the compiler will bitch.  It won't compile
  the code. Choice #2 is to accept that 'Resistence Is Futile' and just declare
  all your variables using the ODBC data types.  For years I resisted and used the
  casting techniques.  But I eventually got wore down and decided to give in and 
  just accept that they'd won.   
*/

// cl Test2.cpp /O1 /Os /GS- TCLib.lib kernel32.lib user32.lib odbc32.lib
// cl Test2.cpp /O1 /Os /GS- /MT odbc32.lib
// 102,912 Bytes VC19 (VC15 - VStudio 2015) x86 ansi LIBCMT.LIB Linkage
//   2,560 Bytes VC15 (VC9  - VStudio 2008) x86 ansi TCLib.lib Linkage
//   3,072 Bytes VC19 (VC15 - VStudio 2015) x86 ansi TCLib.lib Linkage
//   3,584 Bytes VC19 (VC15 - VStudio 2015) x64 ansi TCLib.lib Linkage
//#define TCLib
#include <windows.h>
#include <sqlext.h>
#ifdef TCLib
   #include "stdlib.h"
   #include "stdio.h"
#else
   #include <cstdlib>
   #include <cstdio>
#endif

int main()
{
 SQLRETURN iReturn = 0;
 SQLHENV   hEnvr   = NULL;

 iReturn=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&hEnvr);
 if(iReturn==SQL_SUCCESS || iReturn==SQL_SUCCESS_WITH_INFO)
 {
    printf("SQLAllocHandle(SQL_HANDLE_ENV) Succeeded!\n");
    iReturn=SQLSetEnvAttr(hEnvr,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);
    if(iReturn==SQL_SUCCESS || iReturn==SQL_SUCCESS_WITH_INFO)
    {
       printf("SQLSetEnvAttr(SQL_OV_ODBC3) Succeeded!\n");
    }
    else
       printf("SQLSetEnvAttr(SQL_OV_ODBC3) Failed!\n");
    SQLFreeHandle(SQL_HANDLE_ENV,hEnvr);
 }
 else
    printf("SQLAllocHandle(SQL_HANDLE_ENV) Failed!\n");
 getchar();

 return 0;
}


/*
SQLRETURN SQLAllocHandle
(
  SQLSMALLINT   HandleType,
  SQLHANDLE     InputHandle,
  SQLHANDLE*    OutputHandlePtr
);

SQLRETURN SQLSetEnvAttr
(
  SQLHENVEnvironmentHandle,
  SQLINTEGERAttribute,
  SQLPOINTERValuePtr,
  SQLINTEGERStringLength
);

C:\Code\VStudio\VC15\ODBC\x86\Test1>cl Test2.cpp /O1 /Os /GS- TCLib.lib kernel32.lib user32.lib odbc32.lib
Microsoft (R) C/C++ Optimizing Compiler Version 19.00.23506 for x86
Copyright (C) Microsoft Corporation.  All rights reserved.

Test2.cpp
Microsoft (R) Incremental Linker Version 14.00.23506.0
Copyright (C) Microsoft Corporation.  All rights reserved.

/out:Test2.exe
Test2.obj
TCLib.lib
kernel32.lib
user32.lib
odbc32.lib

C:\Code\VStudio\VC15\ODBC\x86\Test1>Test2
SQLAllocHandle(SQL_HANDLE_ENV) Succeeded!
SQLSetEnvAttr(SQL_OV_ODBC3) Succeeded!
*/


The command line output is shown above. Everything is working so far (at least for me - your mileage may vary)!
freddie1 (1838)
Next step is allocating a connection handle....

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
 
/*
  Test3.cpp - Allocating A Connection Handle
  
  The final step before calling the devastatingly difficult SQLDriverConnect() 
  function is to get a Connection Handle.  Like getting an Environment Handle
  one uses SQLAllocHandle(), but in the parameters one specifies that one wants
  a connection handle.  
  
  Note the way these functions work.  The last parameter is specified as a pointer
  to a handle, and one uses the address of operator to place in in the function, 
  i.e.,
  
  SQLHDBC   hConn   = NULL;
  SQLAllocHandle(SQL_HANDLE_DBC,hEnvr,&hConn);
  
  This is a typical design pattern in ODBC, COM, and other software technologies.
  This is know generically as using an output parameter to return a result.

  To use ODBC as a database access technology one should become as failiar as 
  possible with the three main ODBC includes which are...

  sqltypes.h
  sql.h
  sqlext.h 

  All these programs should compile as in x64.  
*/

// cl Test3.cpp /O1 /Os /GS- TCLib.lib kernel32.lib user32.lib odbc32.lib
// cl Test3.cpp /O1 /Os /GS- /MT odbc32.lib
//   2,560 Bytes VC9  (VC9  - VStudio 2008) x86 ansi TCLib.lib Linkage
//   3,072 Bytes VC19 (VC15 - VStudio 2015) x86 ansi TCLib.lib Linkage
//   3,584 Bytes VC19 (VC15 - VStudio 2015) x64 ansi TCLib.lib Linkage
// 102,912 Bytes VC19 (VC15 - VStudio 2015) x86 ansi LIBCMT.LIB Linkage

//#define TCLib
#include <windows.h>
#include <sqlext.h>
#ifdef TCLib
   #include "stdlib.h"
   #include "stdio.h"
#else
   #include <cstdlib>
   #include <cstdio>
#endif

int main()
{
 SQLRETURN iReturn = 0;
 SQLHENV   hEnvr   = NULL;
 SQLHDBC   hConn   = NULL;

 iReturn=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&hEnvr);
 if(iReturn==SQL_SUCCESS || iReturn==SQL_SUCCESS_WITH_INFO)
 {
    printf("SQLAllocHandle(SQL_HANDLE_ENV) Succeeded!\n");
    iReturn=SQLSetEnvAttr(hEnvr,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);
    if(iReturn==SQL_SUCCESS || iReturn==SQL_SUCCESS_WITH_INFO)
    {
       printf("SQLSetEnvAttr(SQL_OV_ODBC3) Succeeded!\n");
       iReturn=SQLAllocHandle(SQL_HANDLE_DBC,hEnvr,&hConn);
       if(iReturn==SQL_SUCCESS || iReturn==SQL_SUCCESS_WITH_INFO)
       {
          printf("SQLAllocHandle(SQL_HANDLE_DBC) Succeeded!\n");
          SQLFreeHandle(SQL_HANDLE_DBC,hConn);
       }
       else
          printf("SQLAllocHandle(SQL_HANDLE_DBC) Failed!\n");
    }
    else
       printf("SQLSetEnvAttr(SQL_OV_ODBC3) Failed!\n");
    SQLFreeHandle(SQL_HANDLE_ENV,hEnvr);
 }
 else
    printf("SQLAllocHandle(SQL_HANDLE_ENV) Failed!\n");
 getchar();

 return 0;
}


/*
SQLRETURN SQLAllocHandle
(
SQLSMALLINT HandleType,
SQLHANDLE InputHandle,
SQLHANDLE* OutputHandlePtr
);


C:\Code\VStudio\VC15\ODBC\x86\Test1>cl Test3.cpp /O1 /Os /GS- TCLib.lib kernel32.lib user32.lib odbc32.lib
Microsoft (R) C/C++ Optimizing Compiler Version 19.00.23506 for x86
Copyright (C) Microsoft Corporation. All rights reserved.

Test3.cpp
Microsoft (R) Incremental Linker Version 14.00.23506.0
Copyright (C) Microsoft Corporation. All rights reserved.

/out:Test3.exe
Test3.obj
TCLib.lib
kernel32.lib
user32.lib
odbc32.lib

C:\Code\VStudio\VC15\ODBC\x86\Test1>Test3
SQLAllocHandle(SQL_HANDLE_ENV) Succeeded!
SQLSetEnvAttr(SQL_OV_ODBC3) Succeeded!
SQLAllocHandle(SQL_HANDLE_DBC) Succeeded!

C:\Code\VStudio\VC15\ODBC\x86\Test1>
*/
Last edited on
freddie1 (1838)
OK, we need to make a small digression. In Test3.cpp we finally got our connection handle. The next step requires a database to connect to. I don't have a clue what databases you may have on your systems. The only ones I have are various Microsoft databases such as Microsoft Access, SQL Server, and Microsoft Excel. Yes, Excel is a 'database' , of sorts - but not a very good one, which can be connected to.

Now, I could, and later might, provide you code to 'dump' your database drivers to see what they are, but for the moment I'll proceed on the assumption that you don't have any databases, but you do have the stock database drivers that came installed with your system. As far as I know, even the most bare bones install of any version of Windows will provide some drivers - particularly the Microsoft Jet Database Engine which allows connections to MS Access and MS Excel, and SQL Server too.

So what I'll provide next is a little program to create an MS Access database named Testdata.mdb. An obscure, old, and little known or used Windows Api function named SQLConfigDataSource() can be used to create an MS Access database programatically. After having done that, we can move on to Test4.cpp where we'll connect to it. Here is ConfigDataSource.cpp...

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
 
// ConfigDataSource.cpp
// cl ConfigDataSource.cpp /O1 /Os /MT user32.lib odbc32.lib odbccp32.lib    // legacy_stdio_definitions.lib
#include <windows.h>
#include <odbcinst.h>
#include <cstdlib>
#include <cstdio>

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

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

 return (unsigned int)pErr;
}

int main()
{
 char szCreate[64];
 
 strcpy(szCreate,"CREATE_DB=");
 strcat(szCreate,"Testdata.mdb");
 if(!SQLConfigDataSource(0,ODBC_ADD_DSN,"Microsoft Access Driver (*.mdb)",szCreate))
    printf("iInstallerError = %u\n",iInstallerError());
 else
    printf("Database Creation Apparently Succeeded!\n"); 
 getchar();   
    
 return 0;   
}

#if 0

C:\Code\VStudio\VC++9\ODBC\MkSqlServerDB\MyStrCls>cl ConfigDataSource.cpp /O1 /Os /MT user32.lib odbc32.lib odbccp32.lib
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 15.00.21022.08 for 80x86
Copyright (C) Microsoft Corporation.  All rights reserved.

ConfigDataSource.cpp
Microsoft (R) Incremental Linker Version 9.00.21022.08
Copyright (C) Microsoft Corporation.  All rights reserved.

/out:ConfigDataSource.exe
ConfigDataSource.obj
user32.lib
odbc32.lib
odbccp32.lib

C:\Code\VStudio\VC++9\ODBC\MkSqlServerDB\MyStrCls>ConfigDataSource
Database Creation Apparently Succeeded!


C:\Code\VStudio\VC++9\ODBC\MkSqlServerDB\MyStrCls>
#endif


This one's a little different from the previous three for several reasons. First, I'm not sure it'll work if built x64. It might. But the issue is that if it would work I'm expecting one would end up with a 64 bit Access database. Or doesn't such a thing even exist? Maybe the databases are bit size agnostic. I believe that's the case with SQL Server. But even if it worked 64 bit, if you wanted to connect to it running 64 bit code you would need a 64 bit Access Driver. And to get that you would have either had to install a 64 bit version of Access, or a 64 bit version of Microsoft Office. I do believe I read somewhere that one can't have both 32 bit Access drivers and 64 bit Access drivers on the same system. That's not the case with SQL Server, but my understanding is that that's the case with Access.

Next issue is that you may note there's nothing about my TCLib in the code above. Fact is, and much to my disappointment, I couldn't manage to get this code to build against my TCLib.lib. I tried hard but failed. Its the first program I encountered where I wasn't able to build it with my TCLib.

Third issue is that you need to include "odbcinst.h" and add the extra link library of odbccp32.lib to your build system. This function is in the ODBC Installer Dll.

Fourth issue is that with various recent Microsoft compilers the above code won't build. At least not easily. I have a 32 bit Windows 7 installation on a laptop where I installed the Windows 7 SDK without Visual Studio, and to get the above code to build on that I had to add this line after the includes... 

 
 
int (WINAPIV* __vsnprintf)(char *, size_t, const char*, va_list) = _vsnprintf;


On my Windows 10 HP Envy laptop on which I have Visual Studio 2015 Community installed, this command line string works instead...

 
 
cl ConfigDataSource.cpp /O1 /Os /MT user32.lib odbc32.lib odbccp32.lib legacy_stdio_definitions.lib


All of the above likely relates to my not being able to link it against my TCLib.lib. Its a strange function and I believe all kinds of strange stuff goes on within it. Having said that though it has been a very, very useful function for me which I've used the H*** out of in my work. The function call above as I have it is this...

 
 
SQLConfigDataSource(0, ODBC_ADD_DSN, "Microsoft Access Driver (*.mdb)", szCreate);


The 3rd parameter is the driver for Access *.mdb files. With Office 2007 Microsoft came out with the *.accdb file format. That driver would be this...

 
 
"Microsoft Access Driver (*.mdb, *.accdb)"


You can use that, but in the case above what you'll end up with is this for the created database...

 
 
"Testdata.accdb.mdb"


If in the next line in the code you execute this system call you can fix it...

 
 
system("Rename TestData.accdb.mdb TestData.accdb");


Finally, if you've ran the program once and successfully created the testdata.mdb file, if you run it again it'll collide with the already existing file and the function call will fail. Here is a run showing that...

1
2
3
4
 
C:\Code\VStudio\VC++9\ODBC\MkSqlServerDB\MyStrCls>ConfigDataSource
wErrNum  = 1
szErrMsg = Driver's ConfigDSN, ConfigDriver, or ConfigTranslator failed
iInstallerError = 11


That installer error #11 is a useful and actionable result which informs you the file already exists. OK, that was my small digression. Now, since you have a database we can connect to, we'll attempt that with Test4.cpp.
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
 
/*
   Test4.cpp - Using SQLDriverConnect() To Make ODBC Database Connection
   
   SQLDriverConnect() is brutal.  There are 8 parameters....
   
   SQLRETURN      SQLDriverConnect
   (
    SQLHDBC       ConnectionHandle,
    SQLHWND       WindowHandle,
    SQLCHAR*      InConnectionString,
    SQLSMALLINT   StringLength1,
    SQLCHAR*      OutConnectionString,
    SQLSMALLINT   BufferLength,
    SQLSMALLINT*  StringLength2Ptr,
    SQLUSMALLINT  DriverCompletion
   );
   
   The first one is the Connection Handle retrieved by a previous call to 
   SQLAllocHandle() where one inputs the Environment Handle and specifies 
   a Connection Handle as the desired output in the last parameter.
   
   The second parameter is the HWND (Handle of Window) of the application's
   window.  SQLDriverConnect() uses this if it puts up a dialog asking for
   additional connection information if it wasn't satisfied with the
   information you gave it in the other parameters.  This parameter can be
   NULL as in my example below.  This parameter is related to the 8th or
   last parameter where I specified SQL_DRIVER_NOPROMPT.  What this is 
   telling SQLDriverConnect() is that if it can't make a connection with
   the information provided it should simply fail.  In the code below, if
   SQLDriverConnect() fails, SQLGetDiagRec() is called, and that function
   produces error messages sent to the console to report on the nature of
   the error.  

   Have I told you that you need to be looking these functions up on MSDN
   or some local SDK documentation you may have?

   The 3rd parameter - InConnectionString, is an entity known among database
   programmers as a Connection String.  There is a whole web site full of
   Connection Strings for everything imaginable.  Do a search on Connection 
   Strings and you'll surely find it. It has been my experience that MS Access
   can be connected to simply with the path and name of the database and
   the name of the Jet Database Engine Driver...

   "DBQ=TestData.mdb;Driver={Microsoft Access Driver (*.mdb)};"   
   
   DBQ means Database Qualifier (I guess).  In the example below I'm
   assuming you have an Access database named TestData.mdb in whatever
   directory/folder your executable is executing from.  Otherwise you need
   the full path to the database file.  You can substitute whatever database
   you want to connect to here.  We're not really reading anything from the
   database - we're just trying to connect to it.  Reading it will come
   later.  You can't read from it or put anything in it if you can't connect
   to it, naturally.  
   
   The 4th parameter is StringLength1 and it is an input parameter.  With that
   parameter you tell ODBC how many characters are in the string 
   InConnectionString (last or 3rd parameter).  As you can see in the example
   below I used strlen(szCnIn) to get that.
   
   The 5th parameter is an output parameter.  Note in the code below I have
   it declared like so...
   
   char szCnOut[256];
   
   ...and I didn't modify it in any way before placing it in the function call.
   If SQLDriverConnect() succeeds it will write the full connection string it
   used to successfully make the connection to your database, and it may be
   different from the one you specified in szCnIn[].  

   Before it writes any bytes to szCnOut though it checks the count of characters
   you've told it that you have made room for in szCnOut.  In the code below I 
   made room for 256 characters counting the NULL byte.  So you can see I used 
   that number.  That accounts for the 6th parameter.

   Two more to go!

   The 7th parameter is another output parameter where ODBC informs you how
   many characters it actually wrote to szCnOut.  I'm sure you wanted to know.

   The last or 8th parameter is an equate of flag parameter where you specify
   to the driver various things.  In the example bewlow I told it to not prompt
   me for any additional connection information if it isn't satisfied with the
   information I gave it in the other 7 paraneters. In other words, I told it
   to simply fail if it wasn't satisfied.  
   
   What I'd recommend you do is try to get it working, and if you do remove a
   letter or something from the database name to cause it to fail, so you can
   get accustomed to SQLGetDiagRec().  That is a function that swill save your
   life because when things head south you desperately need some clue as to
   what's going wrong, and that function is a big help.  
   
   Here are the arguments to SQLGetDiagRec...
   
   SQLRETURN       SQLGetDiagRec
   (
     SQLSMALLINT    HandleType,
     SQLHANDLE      Handle,
     SQLSMALLINT    RecNumber,
     SQLCHAR*       SQLState,
     SQLINTEGER*    NativeErrorPtr,
     SQLCHAR*       MessageText,
     SQLSMALLINT    BufferLength,
     SQLSMALLINT*   TextLengthPtr
   );
*/

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
 
// cl Test4.cpp /O1 /Os /GS- TCLib.lib kernel32.lib user32.lib odbc32.lib
// cl Test4.cpp /O1 /Os /GS- /MT odbc32.lib
// 103,424 Bytes VC19 (Visual Studio 2015) x86 ansi LIBCMT.LIB Linkage
//  52,224 Bytes VC15 (Visual Studio 2008) x86 ansi LIBCMT.LIB Linkage
//   4,608 Bytes VC19 (Visual Studio 2015) x64 ansi TCLib.lib  Linkage (With SQL Server)
//   4,096 Bytes VC19 (Visual Studio 2015) x86 ansi TCLib.lib  Linkage
//   3,072 Bytes VC15 (Visual Studio 2008) x86 ansi TCLib.lib Linkage
//#define TCLib
#include <windows.h>
#include <sqlext.h>
#ifdef TCLib
   #include "stdlib.h"
   #include "stdio.h"
#else
   #include <cstdlib>
   #include <cstdio>
#endif 

int main()
{
 SQLRETURN iReturn   = 0;
 SQLHENV hEnvr       = NULL;
 SQLHDBC hConn       = NULL;
 char szCnOut[256];
 char szCnIn[256];
 WORD wStrLen=0;

 iReturn=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&hEnvr);
 if(iReturn==SQL_SUCCESS || iReturn==SQL_SUCCESS_WITH_INFO)
 {
    printf("SQLAllocHandle(SQL_HANDLE_ENV) Succeeded!\n");
    iReturn=SQLSetEnvAttr(hEnvr,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);
    if(iReturn==SQL_SUCCESS || iReturn==SQL_SUCCESS_WITH_INFO)
    {
       printf("SQLSetEnvAttr(SQL_OV_ODBC3) Succeeded!\n");
       iReturn=SQLAllocHandle(SQL_HANDLE_DBC,hEnvr,&hConn);
       if(iReturn==SQL_SUCCESS || iReturn==SQL_SUCCESS_WITH_INFO)
       {
          printf("SQLAllocHandle(SQL_HANDLE_DBC) Succeeded!\n");
          //strcpy(szCnIn,"DRIVER=SQL Server;SERVER=Win10-Envy\\SQLEXPRESS;UID=;Trusted_Connection=Yes;WSID=WIN10-ENVY;DATABASE=SaleData;");
          //strcpy(szCnIn,"DRIVER=SQL Server;SERVER=Fred-PC\\SQLEXPRESS;UID=Fred;Trusted_Connection=Yes;WSID=Fred-PC;DATABASE=SaleData;");
          //strcpy(szCnIn,"DRIVER=Microsoft Access Driver (*.mdb);DBQ=C:\\Code\\CodeBlks\\MkAccessDB\\TestData.mdb;");
          strcpy(szCnIn,"DRIVER=Microsoft Access Driver (*.mdb);DBQ=TestData.mdb;");
          iReturn=SQLDriverConnect
          (
            hConn,                        // Connection Handle Retrieved Above With SQLAllocHandle(SQL_HANDLE_DBC)
            NULL,                         // If you provide a HWND here And ODBC Isn't Satisfied With The Parameters, It'll Throw Up A Dialog Before Failing
            (SQLCHAR*)szCnIn,             // It Wants Your Valid Connection String For The Database as An Input Parameter
            (SQLSMALLINT)strlen(szCnIn),  // Here It Wants The Length Of Your szCnIn Connection String
            (SQLCHAR*)szCnOut,            // This Is An 'Output' Parameter.  If ODBC Succeeds In Connecting, It Will Write To This Buffer Its Actual Connection String It Used
            256,                          // Here Is Where You Tell ODBC The Length Of The Buffer You've Provided In The Last Parameter For the Connection String
            (SQLSMALLINT*)&wStrLen,       // This Is Also An 'Output' Parameter.  ODBC Uses It To Tell You How Many Characters It Has Actually Written To The 5th Parameter
            SQL_DRIVER_NOPROMPT           // Here I'm Telling ODBC Not To Prompt Me For Additional Connection Information But To Simply Fail
          );
          if(iReturn==SQL_SUCCESS || iReturn==SQL_SUCCESS_WITH_INFO)
          {
             printf("SQLDriverConnect() Succeeded!\n"); 
             printf("szCnIn  = %s\n",szCnIn);
             printf("szCnOut = %s\n",szCnOut);
             SQLDisconnect(hConn);
          }
          else
          { 
             printf("F***!  SQLDriverConnect() Failed!\n");
             SQLCHAR     szSqlState[16];
             SQLINTEGER  iNativeErrorPtr;
             SQLCHAR     szErrMsg[256];
             SQLSMALLINT iBufferLength;
             SQLSMALLINT iTextLengthPtr;
             SQLGetDiagRec(SQL_HANDLE_DBC,hConn,1,szSqlState,&iNativeErrorPtr,szErrMsg,256,&iTextLengthPtr);
             printf("szSqlState      = %s\n", szSqlState);
             printf("iNativeErrorPtr = %d\n",iNativeErrorPtr);
             printf("szErrMsg        = %s\n",szErrMsg);
             printf("iTextLengthPtr  = %d\n",iTextLengthPtr);
          }   
          SQLFreeHandle(SQL_HANDLE_DBC,hConn);
       }
       else
          printf("SQLAllocHandle(SQL_HANDLE_DBC) Failed!\n");
    }
    else
       printf("SQLSetEnvAttr(SQL_OV_ODBC3) Failed!\n");
    SQLFreeHandle(SQL_HANDLE_ENV,hEnvr);
 }
 else
    printf("SQLAllocHandle(SQL_HANDLE_ENV) Failed!\n");
 getchar();

 return 0;
}


OK, we've gotten through a lot. That last code is a real mess isn't it? Tomorrow, if I'm not banned for posting all this, I'll show how to develop an ODBC Class to wrap that mess up so we don't ever have to look at it again. Having done that we'll be able to connect to a database with a syntax as simple as this...

1
2
3
4
5
6
7
8
9
10
11
 
int main()
{
 CSql Sql;
 
 Sql.strDriver = _T("SQL Server");
 Sql.strServer = _T("Fred-PC\\SQLEXPRESS");
 Sql.ODBCConnect();
 if(Sql.blnConnected==TRUE)
 {
   ...
   ...

Last edited on
Chervil (7320)
Thanks for this post. I just compiled each one on a 32-bit MinGW system.
The compiler didn't complain until the very last code Test4.cpp, some small warning about unused variable iBufferLength and a change from %d to %ld in the printf at line 72.

At this stage I haven't tried to understand everything, there's a lot of material here.
freddie1 (1838)
Continuing where we left off last night with Test4.cpp which was essentially a C style program showing the ODBC connection sequence of...

Allocate ODBC Environment Handle
Set ODBC Environmental Version/Attribute
Allocate ODBC Connection Handle
Call SQLDriverConnect()

...today we'll hide all that nastiness away in a very simple ODBC based CSql Class. Bet you thought I was a lowly C coder incapable of rising to that higher and exalted plane of C++ and OOP! Ha! Take this...

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
 
// cl Test5.cpp CSql.cpp /O1 /Os /MT /EHsc kernel32.lib user32.lib odbc32.lib
// g++ Test5.cpp CSql.cpp -oTest5.exe -mconsole -m32 -Os -s -lodbc32
// 553,472 Bytes GCC-TDM 4.9 x86 ansi 
//  99,328 Bytes VC15 (VStudio 2008) x86 ansi LIBCMT.LIB
#include <windows.h>
#include <iostream>
#include <string>
#include <sqlext.h>
#include <tchar.h>
#include "CSql.h"

int main()
{
 CSql Sql;
 
 Sql.strDriver = "Microsoft Access Driver (*.mdb)";
 Sql.strDBQ    = "TestData.mdb";
 Sql.ODBCConnect();
 if(Sql.blnConnected==TRUE)
 {
    std::cout << "Successfully Connected To MS Access!" << std::endl;
    std::cout << "Sql.strConnectionString = " << Sql.strConnectionString << std::endl;
    Sql.ODBCDisconnect();
 }
 else
 { 
    std::cout << "Failed To Connect To Sql Server!" << std::endl; 
    std::cout << "Sql.szErrMsg = " << Sql.szErrMsg  << std::endl;
 }    
 
 return 0;
}


What I am incapable of is being able to happily digest a 25 line program that builds to 100 k to 500 k. But more about that later. To get the above program to work our recently manufactured "Testdata.mdb" file needs to be in the directory with your executable.

If you look at my command line compilation strings above you'll see a reference to CSql.cpp and in the list of includes is CSql.h. Here is CSql.h - our very simple ODBC Class of which I spoke...

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
 
//CSql.h
#if !defined(CSQL_H)
#define CSQL_H
#include  <sqltypes.h>

class CSql
{
 public:
 CSql();
 ~CSql();
 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;
 TCHAR             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


In examining the above you'll note all the ODBC variables we needed to ultimately call SQLDriverConnect() have been moved to our CSql Class. To make things easy and concise I simply left them as publics. If writing accessors/mutators floats your boat then feel free to modify the code yourself with lots of private data members.

In explaining the above it is necessary to understand the concept of 'Connection Strings'. In my Test4.cpp example, if you were able to get it to run, you would have seen my szCnIn and szCnOut variables were like so in the output...

1
2
 
szCnIn  = DRIVER=Microsoft Access Driver (*.mdb);DBQ=TestData.mdb;
szCnOut = DBQ=TestData.mdb;DefaultDir=.;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;


You'll note that these are Connection Strings, which are defined as attribute/value pairs separated by semicolons. Each database seems to have its own assortment of attribute terms that are uniquely significant to it. Some are common among many RDMS (Relational Database Management Systems) . DRIVER is a real common one. DBQ, which I believe stands for 'Database Qualifier' is used in MS Access. Note that this sort of thing lends itself to being decomposed into the object.property paradigm of object oriented languages. For example, in my Test5.cpp example above you'll see this...

1
2
3
4
5
 
CSql Sql;

Sql.strDriver = "Microsoft Access Driver (*.mdb)";
Sql.strDBQ    = "TestData.mdb";
Sql.ODBCConnect();


For MS Access databases a connection can be made through only setting those two 'properties', although for feeding into SQLDriverConnect() it wants a full Connection String - not an object.property syntax. Note in the above example szCnIn isn't equal to szCnOut. The database driver apparently adds some terms to the simpler connection string we fed into it. No doubt there are a pile of default settings it uses for certain things important to it if they are not provided in the connection string.

Although I haven't discussed SQL Server yet, here are the szCnIn and szCnOut variables from a connection attempt to SQL Server Express running on one of my laptops...

1
2
 
szCnIn  = DRIVER=SQL Server;SERVER=Win10-Envy\SQLEXPRESS;UID=;Trusted_Connection=Yes;WSID=WIN10-ENVY;DATABASE=SaleData;
szCnOut = DRIVER=SQL Server;SERVER=Win10-Envy\SQLEXPRESS;UID=frede;Trusted_Connection=Yes;WSID=WIN10-ENVY;DATABASE=SaleData


As can be seen, we have the same pattern of attribute/value pairs separated by semicolons. This all suggests then the nature of the code which must be written within our CSql::ODBCConnect() member in order to use the syntax I'm showing above. The code will have to simply concatenate attribute/value pairs together to form a valid connection string to be fed into the SQLDriverConnect() function. Where I got the idea for all this was my use of various database technologies back in the 1990s with Microsoft Visual Basic. First there was RDO (Remote Data Objects), then the much loved DAO (Data Access Objects), and finally ADO (ActiveX Data Objects), the latter which are still being used.

Note however that when I built my ODBC class I foresaw that sometimes it might be more convenient, if one already has a full connection string (they can be downloaded easily from various internet sites for any database you can think of), to be able to just set that in the class directly and call CSql::ODBCConnect() using that fully formed connection string, e.g., ...

1
2
3
4
 
CSql Sql;

Sql.strConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=TestData.mdb;";
Sql.ODBCConnect();


So I wanted to be able to use that syntax also with my class. Without further ado here is my CSql.cpp implementation file...

continued...
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
 
//CSql.cpp
#include  <windows.h>
#include  <sqlext.h>
#include  <string>
#include  <tchar.h>
#include  "CSql.h"


CSql::CSql()        // CSql Constructor
{
 szCnOut[0]         = 0;
 szErrMsg[0]        = 0;
 szErrCode[0]       = 0;
 this->hConn        = NULL;
 this->hEnvr        = NULL;
 this->blnConnected = 0;
}


CSql::~CSql()       // CSql Destructor
{
 if(this->hConn)
 {
    if(this->blnConnected)
       SQLDisconnect(this->hConn);
    SQLFreeHandle(SQL_HANDLE_DBC,this->hConn);
 }
 if(this->hEnvr)
    SQLFreeHandle(SQL_HANDLE_ENV,this->hEnvr);
}


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


void CSql::ODBCConnect(void)
{
 TCHAR szCnIn[512];
 UINT 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);
 _tcscpy(szCnIn,strConnectionString.c_str());
 iResult=SQLDriverConnect(hConn,NULL,(SQLTCHAR*)szCnIn,(SQLSMALLINT)_tcslen(szCnIn),(SQLTCHAR*)szCnOut,512,&swStrLen,SQL_DRIVER_NOPROMPT);
 if(iResult==SQL_SUCCESS || iResult==SQL_SUCCESS_WITH_INFO)
 {
    blnConnected=TRUE;
    this->strConnectionString=szCnOut;
 }
 else
 {
    SQLGetDiagRec(SQL_HANDLE_DBC,hConn,1,szErrCode,&iNativeErrPtr,szErrMsg,512,&iTextLenPtr);
    blnConnected=FALSE;
    SQLFreeHandle(SQL_HANDLE_DBC,this->hConn), this->hConn=NULL;
    SQLFreeHandle(SQL_HANDLE_ENV,this->hEnvr), this->hEnvr=NULL;
 }
}


void CSql::ODBCDisconnect(void)
{
 if(blnConnected==TRUE)
 {
    SQLDisconnect(hConn);
    SQLFreeHandle(SQL_HANDLE_DBC,hConn), hConn=NULL;
    SQLFreeHandle(SQL_HANDLE_ENV,hEnvr), hEnvr=NULL;
    this->blnConnected=FALSE;
 }
 this->strConnectionString=_T("");
}


With the above you should be able to build and run Test5.cpp. I expect many users might be using the Visual Studio IDE and attempting to run the code within the IDE and you might have issues with all the directories Visual Studio creates such as \Debug, \Release, etc., as related to the executable itself, and the Testdata.mdb file. You'll have to resolve this issue yourself. Personally, I don't use Visual Studio, nor do I run code from within code editors, so I don't encounter such issues.
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
 
/*
   Program creates MS Access Database Testdata.mdb in same directory as executable, and
   creates a Table1 in database with four fields named and populated with data as follows....

   Id	   Float_Point	   Date_Field	   Text_Field
   ===================================================
   1	     3.14159	       11/15/1952  My Birthday
   2	     1.23456	       6/30/1969  Walk On Moon?
   3	     15.1234	       1/1/2006	    Some String
   4	     0.54321	       4/1/2006	    April Fools Day

   Finally, the program dumps the data to the console window.

   WARNING!!!!!!!   DANGER!!!!!   WARNING!!!!!   DANGER!!!!!!!!!

   THE PROGRAM WON'T OR MAY NOT FUNCTION UNDER THE FOLLOWING TWO CONDITIONS:

   1) If run from within an IDE (Integrated Development Environment) where the IDE is
      redirecting file access through usages such as ./, ../, .../, etc, in support of
      complicated nested directory structures possibly involving ./debug and ./release
      subdirectories (the way Visual Studio operates);

   2) If run from anywhere within C:\Users or C:\Documents and Settings directories and
      any of their subdirectories (also the way Visual Studio operates).

   If your IDE refuses to allow you to create projects elsewhere on your computer, then
   simply take the executable produced and put it somewhere other than the locations I've
   identified above.  Execute it from there and it'll work.

   Alternately, if you execute the program with administrator credentials it should be
   able to create the database within those special directories (right click on executable
   and choose 'Run As Administrator'.  Or you could embed a manifest in the executable
   and set the execution level.

   A typical output run looks like this...

   Sql.strDBQ = C:\Code\VStudio\VC++9\ODBC\MkAccessDB\CStdLib\TestData.mdb
   Sql.blnConnected        = True
   Sql.szCnOut             = DBQ=C:\Code\VStudio\VC++9\ODBC\MkAccessDB\CStdLib\Test
   Data.mdb;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;
   Sql.strConnectionString = DRIVER=Microsoft Access Driver (*.mdb);DBQ=C:\Code\VStudio\VC++9\ODBC\MkAccessDB\CStdLib\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

   SELECT Table1.Id,Table1.Float_Point,Table1.Date_Field,Table1.Text_Field FROM Table1;

   iId    Double        Date             String          String Bytes Read (iSqlLen[3]
   ===================================================================================
   1       3.14      11/15/1952         My Birthday                  15
   2       1.23       6/30/1969         Walk On Moon?                15
   3      15.12        1/1/2006         Some String                  15
   4       0.54        4/1/2006         April Fools Day              15

   To get the program to build with Windows 7 SDK you will need the following line at the top of the main source
   code file....

   int (WINAPIV* __vsnprintf)(char *, size_t, const char*, va_list) = _vsnprintf;

   For further iformation on that see...

   http://stackoverflow.com/questions/31053670/unresolved-external-symbol-vsnprintf-in-dxerr-lib

   For Visual Studio 2015 this lib can alternately be added to the linkages...

   legacy_stdio_definitions.lib

   See...

   https://msdn.microsoft.com/en-us/library/bb531344.aspx

   In any case, these are the libraries which need to be included in the build...

   kernel32.lib user32.lib odbc32.lib odbccp32.lib legacy_stdio_definitions.lib
*/

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
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
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
 
// cl Main.cpp CSql.cpp /O1 /Os /MT /EHsc /FeMkDB.exe kernel32.lib user32.lib odbc32.lib odbccp32.lib legacy_stdio_definitions.lib
// g++ Main.cpp CSql.cpp -oMkDB.exe -mconsole -m32 -Os -s -lodbc32 -lodbccp32
// 137,216 Bytes VC19 (Visual Studio 2015) x86 ansi LIBCMT.LIB Linkage
// 459,776 Bytes TDM-GCC 4.9 x86 ansi
#include <windows.h>
#include <odbcinst.h>
#include <cstdio>
#include <sqlext.h>
#include "CSql.h"


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

 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* szDriver, char const* szDBName)
{
 char szCreate[256];

 strcpy(szCreate,"CREATE_DB=");
 strcat(szCreate,szDBName);
 if(SQLConfigDataSource(0,ODBC_ADD_DSN,szDriver,szCreate))
    return TRUE;
 else
    return iInstallerError();
}


int GetRecordCount(CSql& Sql)
{
 unsigned int iRecCt=0;
 TCHAR szQuery[128];
 SQLHSTMT hStmt;
 size_t iLen;

 strcpy(szQuery,"SELECT Count(*)  As RecordCount From Table1;");
 SQLAllocHandle(SQL_HANDLE_STMT,Sql.hConn,&hStmt);
 SQLBindCol(hStmt,1,SQL_C_ULONG,&iRecCt,0,(SQLLEN*)&iLen);
 if(SQLExecDirect(hStmt,(SQLTCHAR*)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(const char* szDate, const char* szFormat, const char* szDelimiter)
{
 UINT i=0,j=0,k=0;
 TIMESTAMP_STRUCT ts;
 char buf[4][4];
 const char* p;

 memset(buf,0,sizeof(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(CSql& Sql)
{
 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(15));");
 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(CSql& sql)
{
 const char* szStr[]={"My Birthday","Walk On Moon?","Some String","April Fools Day"};
 const char* szDate[]={"11/15/1952","6/30/1969","1/1/2006","4/1/2006"};
 double dblNum[]={3.14159,1.23456,15.1234,0.54321};
 char  szQuery[100],szString[16];
 UINT i,id,iRet=FALSE;
 TIMESTAMP_STRUCT ts;
 SQLHSTMT hStmt;
 size_t iLen[4];
 double dbl;

 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,(SQLLEN*)&iLen[0]);
       SQLBindParameter(hStmt,2,SQL_PARAM_INPUT,SQL_C_DOUBLE,SQL_DOUBLE,0,0,&dbl,0,(SQLLEN*)&iLen[1]);
       SQLBindParameter(hStmt,3,SQL_PARAM_INPUT,SQL_C_TYPE_DATE,SQL_TYPE_TIMESTAMP,16,0,&ts,0,(SQLLEN*)&iLen[2]);
       SQLBindParameter(hStmt,4,SQL_PARAM_INPUT,SQL_C_TCHAR,SQL_CHAR,15*sizeof(TCHAR),0,szString,16*sizeof(TCHAR),(SQLLEN*)&iLen[3]);
       for(i=0; i<4; i++)
       {
           id=i+1, dbl=dblNum[i];
           ts=ParseDate(szDate[i],"mdy","/");
           strcpy(szString,szStr[i]);
           iLen[0]=sizeof(int);
           iLen[1]=sizeof(float);
           iLen[2]=sizeof(TIMESTAMP_STRUCT);
           iLen[3]=strlen(szString);
           if(SQLExecute(hStmt)==SQL_SUCCESS)
              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;
    }
    SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
 }

 return iRet;
}


UINT blnDumpData(CSql& sql)
{
 char szQuery[100],szDate[12];
 SQLCHAR szString[16];
 TIMESTAMP_STRUCT ts;
 size_t iSqlLen[4];
 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;");
    printf("%s\n\n",szQuery);
    SQLBindCol(hStmt,1,SQL_C_ULONG,&iId,0,(SQLLEN*)&iSqlLen[0]);
    SQLBindCol(hStmt,2,SQL_C_DOUBLE,&dblNum,0,(SQLLEN*)&iSqlLen[1]);
    SQLBindCol(hStmt,3,SQL_C_TYPE_DATE,&ts,0,(SQLLEN*)&iSqlLen[2]);
    SQLBindCol(hStmt,4,SQL_C_TCHAR,szString,16*sizeof(TCHAR),(SQLLEN*)&iSqlLen[3]);
    if(SQLExecDirect(hStmt,(SQLTCHAR*)szQuery,SQL_NTS)==SQL_SUCCESS)
    {
       printf("iId    Double        Date             String          String Bytes Read (iSqlLen[3]\n");
       printf("===================================================================================\n");
       while(SQLFetch(hStmt)!=SQL_NO_DATA)
       {
          MkDate(ts,szDate);
          printf("%-4u%8.2f%16s%24s%16u\n",iId,dblNum,szDate,szString,iSqlLen[3]);
       };
    }
    SQLCloseCursor(hStmt);
    SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
    return TRUE;
 }
 getchar();

 return FALSE;
}


continued...
freddie1 (1838)
finally, main()...

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
 
int main()
{
 std::string s1;
 CSql Sql;

 Sql.strDriver="Microsoft Access Driver (*.mdb)";
 //Sql.strDriver="Microsoft Access Driver (*.mdb, *.accdb)";
 //Sql.strDBQ=s1+"\\"+"TestData.accdb";
 Sql.strDBQ="Testdata.mdb";
 printf("Sql.strDBQ = %s\n",Sql.strDBQ.c_str());
 if(iCreateDB(Sql.strDriver.c_str(), Sql.strDBQ.c_str())==TRUE)
 {
    //system("Rename TestData.accdb.mdb TestData.accdb");
    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("\nblnInsert() 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;
}


Perhaps I'll post an SQL Server version of the above yet. And always fun is SQLDrivers(), not to mention an Excel version!.
Last edited on
Topic archived. No new replies allowed.