64 Bit ODBC (Open Database Connectivity)

[Frederick J. Harris]

I've got 64 bit on my mind I guess.  Spent most of the fall working with 64 bit COM, and I'm satisfied with the way things are working there....

http://www.jose.it-berater.org/smfforum/index.php?board=431.0

So next comes 64 bit ODBC database access.  Has anyone been thinking about that?  Wondering how it might differ across architectures?  Well, I have.  I thought I'd better look at these issues because I guess I'm not ready to dry up and blow away just  yet.  So if anyone is interested, I'll write a bit about what I've found out so far.   

Since we use Microsoft Access where I work, and my applications all use ODBC to connect with it, I was most interested in 64 bit Microsoft Access.  Nothing I read about that sounded comforting, however.  Yes, a 64 bit version of Access exists, as does a 64 bit version of Microsoft Office.  The bad news there seems to be however that one can't have both 32 bit and 64 bit ODBC drivers for Access on the same computer.  There were enough other forbidding sounding issues that I decided to forget about Access.  I did try, however, to compile and execute 64 bit C++ code to connect with my 32 bit Access *.mdb databases, and everything works OK up to the point of SQLDriverConnect().  The error message one gets is something to the effect that there isn't any available driver for that database, which makes sense.  If a 64 bit installation of a 64 bit version of Access isn't present, then there isn't any driver.  From what I read I didn't expect it to work - but it was worth trying.

So next I thought I'd dust off my old SqlDrivers program which dumps in a console window the ODBC database drivers available on a system.  I compiled a 32 bit and a 64 bit version of the program with my VC9 compiler, and here's the dump of 32 bit drivers (I'll post this little program later if anyone wants to see what they come up with on their boxes) ...

Code Select Expand


Microsoft ODBC for Oracle

  ptrAttributes[0] = UsageCount=1
  ptrAttributes[1] = SQLLevel=1
  ptrAttributes[2] = FileUsage=0
  ptrAttributes[3] = DriverODBCVer=02.50
  ptrAttributes[4] = ConnectFunctions=YYY
  ptrAttributes[5] = APILevel=1
  ptrAttributes[6] = CPTimeout=120


Microsoft Access Driver (*.mdb)

  ptrAttributes[0] = UsageCount=2
  ptrAttributes[1] = APILevel=1
  ptrAttributes[2] = ConnectFunctions=YYN
  ptrAttributes[3] = DriverODBCVer=02.50
  ptrAttributes[4] = FileUsage=2
  ptrAttributes[5] = FileExtns=*.mdb
  ptrAttributes[6] = SQLLevel=0


Microsoft dBase Driver (*.dbf)

  ptrAttributes[0] = UsageCount=1
  ptrAttributes[1] = APILevel=1
  ptrAttributes[2] = ConnectFunctions=YYN
  ptrAttributes[3] = DriverODBCVer=02.50
  ptrAttributes[4] = FileUsage=1
  ptrAttributes[5] = FileExtns=*.dbf
  ptrAttributes[6] = *.ndx
  ptrAttributes[7] = *.mdx
  ptrAttributes[8] = SQLLevel=0


Microsoft Excel Driver (*.xls)

  ptrAttributes[0] = UsageCount=1
  ptrAttributes[1] = APILevel=1
  ptrAttributes[2] = ConnectFunctions=YYN
  ptrAttributes[3] = DriverODBCVer=02.50
  ptrAttributes[4] = FileUsage=1
  ptrAttributes[5] = FileExtns=*.xls
  ptrAttributes[6] = SQLLevel=0


Microsoft Paradox Driver (*.db )

  ptrAttributes[0] = UsageCount=1
  ptrAttributes[1] = APILevel=1
  ptrAttributes[2] = ConnectFunctions=YYN
  ptrAttributes[3] = DriverODBCVer=02.50
  ptrAttributes[4] = FileUsage=1
  ptrAttributes[5] = FileExtns=*.db
  ptrAttributes[6] = SQLLevel=0


Microsoft Text Driver (*.txt; *.csv)

  ptrAttributes[0] = UsageCount=1
  ptrAttributes[1] = APILevel=1
  ptrAttributes[2] = ConnectFunctions=YYN
  ptrAttributes[3] = DriverODBCVer=02.50
  ptrAttributes[4] = FileUsage=1
  ptrAttributes[5] = FileExtns=*.
  ptrAttributes[6] = *.asc
  ptrAttributes[7] = *.csv
  ptrAttributes[8] = *.tab
  ptrAttributes[9] = *.txt
  ptrAttributes[10] = *.csv
  ptrAttributes[11] = SQLLevel=0


Microsoft Visual FoxPro Driver

  ptrAttributes[0] = UsageCount=2
  ptrAttributes[1] = APILevel=0
  ptrAttributes[2] = ConnectFunctions=YYN
  ptrAttributes[3] = DriverODBCVer=02.50
  ptrAttributes[4] = FileUsage=1
  ptrAttributes[5] = FileExtns=*.dbc
  ptrAttributes[6] = *.dbf
  ptrAttributes[7] = SQLLevel=0



Microsoft FoxPro VFP Driver (*.dbf)

  ptrAttributes[0] = UsageCount=1
  ptrAttributes[1] = APILevel=0
  ptrAttributes[2] = ConnectFunctions=YYN
  ptrAttributes[3] = DriverODBCVer=02.50
  ptrAttributes[4] = FileUsage=1
  ptrAttributes[5] = FileExtns=*.dbf
  ptrAttributes[6] = *.cdx
  ptrAttributes[7] = *.idx
  ptrAttributes[8] = *.fpt
  ptrAttributes[9] = SQLLevel=0


Microsoft dBase VFP Driver (*.dbf)

  ptrAttributes[0] = UsageCount=1
  ptrAttributes[1] = APILevel=0
  ptrAttributes[2] = ConnectFunctions=YYN
  ptrAttributes[3] = DriverODBCVer=02.50
  ptrAttributes[4] = FileUsage=1
  ptrAttributes[5] = FileExtns=*.dbf
  ptrAttributes[6] = *.cdx
  ptrAttributes[7] = *.idx
  ptrAttributes[8] = *.fpt
  ptrAttributes[9] = SQLLevel=0


SQL Native Client

  ptrAttributes[0] = UsageCount=1
  ptrAttributes[1] = APILevel=2
  ptrAttributes[2] = ConnectFunctions=YYY
  ptrAttributes[3] = CPTimeout=60
  ptrAttributes[4] = DriverODBCVer=09.00
  ptrAttributes[5] = FileUsage=0
  ptrAttributes[6] = SQLLevel=1


And here is a dump from the x64 version.  As you can see – much shorter!

Code Select Expand


SQL Server

  ptrAttributes[0] = UsageCount=1
  ptrAttributes[1] = SQLLevel=1
  ptrAttributes[2] = FileUsage=0
  ptrAttributes[3] = DriverODBCVer=03.50
  ptrAttributes[4] = ConnectFunctions=YYY
  ptrAttributes[5] = APILevel=2
  ptrAttributes[6] = CPTimeout=60



SQL Native Client

  ptrAttributes[0] = UsageCount=1
  ptrAttributes[1] = APILevel=2
  ptrAttributes[2] = ConnectFunctions=YYY
  ptrAttributes[3] = CPTimeout=60
  ptrAttributes[4] = DriverODBCVer=09.00
  ptrAttributes[5] = FileUsage=0
  ptrAttributes[6] = SQLLevel=1
 

So there you go!  Sql Server and Sql Native Client.  That's all!  In thinking about it, about 2 years ago when I purchased  a Windows 7 x64 machine, I installed my Visual Studio 2008 Pro on it.  I believe I installed SQL Server Express 2005 on it too.  So those two 64 bit SQL Server drivers could have come with the 64 bit Windows 7, or perhaps were added by the Visual Studio install.  I'm not sure which.  In fact, I wasn't even sure what SQL Native Client was.  Found this on the Microsoft website ...   

The SQL Server Native Client contains the SQL Server ODBC driver and the SQL Server OLE DB provider in one native dynamic link library (DLL) supporting applications using native-code APIs (ODBC, OLE DB and ADO) to Microsoft SQL Server.

Its somehow an extension or next generation package for old MDAC, which I'm to understand has been deprecated (I will be soon too).  Really though, the biggest question I had was whether the databases in themselves were different between 32 bit and 64 bit.  I could see it going either way, i.e., they're different or they are not.  In terms of a text file, I imagine it would be all the same between a 32 bit version of Notepad.exe or a 64 bit version of Notepad.  It isn't the file and the data that it contains that's different, but rather the program opening it.  Indeed, this is how it worked out with SQL Server.  I did a search on it and came up with this link ...

http://blogs.msdn.com/b/cindygross/archive/2010/04/01/moving-data-between-32-bit-and-64-bit-sql-server-instances.aspx

To wit ...

Moving data between 32-bit and 64-bit SQL Server instances

Cindy Gross

I was recently asked about whether SQL Server data can move between architectures, say from
x64 to x86.

Yes, you can move SQL Server data back and forth between x64, x86, and IA64 architectures. The data and log files themselves do not store anything that indicates the architecture and work the same on either 32-bit or 64-bit. The same applies to the backup files. Given those facts it becomes clear that we can easily move data between architectures. You can backup on x86 and restore to x64. Detach/attach works fine. Log shipping works because it is basically backup/restore with some scheduling. Mirroring and transactional replication take data from the transaction log and push the data to another system so again they work across architectures. Merge replication is basically just another application sitting on top of SQL Server, it moves
data by reading tables in one location and modifying data in another location. Again, this can all be done across architectures.

Hopefully you are not installing new x86 boxes, 64-bit handles memory so much better. If you have legacy x86 boxes you can easily do a backup or detach from that old system and restore or attach on the new x64 instance. You can also reverse the process and copy data from x64 back to x86. The same logic applies to the other technologies listed above.

Per BOL (I used the SQL 2008 R2 version):
The SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments.
Therefore, a database mirroring session can combine server instances that run in a 32-bit
environment and server instances that run in a 64-bit environment.

Because the SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments,
a replication topology can combine server instances that run in a 32-bit environment and server
instances that run in a 64-bit environment.

The SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments.
Therefore, a log shipping configuration can combine server instances that run in a 32-bit
environment and server instances that run in a 64-bit environment.

So, I dusted off some of my C++  ODBC code and ran it against some SQL Server databases I have - compiling with 64 bit however, and everything worked fine.  Exactly the same as 32 bit code.  Just wanted to mention these issues here in case anyone is wondering about these issues as I have.  Here is a code snippet I call my SQLDrivers program that will provide a console dump of your 32 bit or 64 bit ODBC drivers.  Note it uses my String Class.  That's kind of long and I have it posted here several times, so you can find a working version in my recent FHGrid posting ...

http://www.jose.it-berater.org/smfforum/index.php?topic=4926.0

Get the Strings.cpp and Strings.h files in Reply #8, and make sure the ...

//#define JUST_NEED_PARSE

... is commented out as above.  If compiled as x86 you'll get your 32 bit drivers, and on x64 you'll get your 64 bit drivers.  Here is the code with suitable compiler command lines for MSVC and MinGW...

Code Select Expand


// cl Main.cpp Strings.cpp Kernel32.lib odbc32.lib /MT /O1 /Os /FeSqlDrivers.exe
// g++ Main.cpp Strings.cpp -lodbc32 -oSqlDrivers_x64.exe -m64 -s -Os
// g++ Main.cpp Strings.cpp -lodbc32 -oSqlDrivers_x32.exe -m32 -s -Os
#ifndef UNICODE
#define UNICODE
#endif
#ifndef _UNICODE             //There are some 'issues' in getting the console
#define _UNICODE             //wide character output functions such as wprintf
#endif                       //to work on the newer GNU or MinGW compiler suites.
#include   <windows.h>       //If your string output is getting cut short after
#include   <tchar.h>         //outputting the first character, try using %S instead
#include   <cstdio>          //of %s in the wprintf format string.  No issue with
#include   <string.h>        //ansi strings.
#include   <sql.h>
#include   <sqlext.h>
#include   "Strings.h"

int main()
{
TCHAR szDriver[256], szAttributes[256];
String* ptrAttributes;
short iLen1,iLen2;
SQLHENV hEnvr;
int iCount;
TCHAR* pCh;
String s1;

if(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnvr)!= SQL_ERROR)
{
    SQLSetEnvAttr(hEnvr,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);
    while(SQLDrivers(hEnvr,SQL_FETCH_NEXT,(SQLTCHAR*)szDriver,256,&iLen1,(SQLTCHAR*)szAttributes,256,&iLen2)!=SQL_NO_DATA)
    {
          pCh=szAttributes;
          for(int i=0;i<iLen2;i++)
          {
              if(*pCh==_T('\0'))
                 *pCh=_T(',');
              pCh++;
          }
          *--pCh=_T('\0');
          s1=szAttributes;
          iCount=s1.ParseCount(_T(','));
          ptrAttributes = new String[iCount];
          s1.Parse(ptrAttributes,_T(','));
          _tprintf(_T("%s\n\n"),szDriver);
          for(int i=0;i<iCount;i++)
              _tprintf(_T("  ptrAttributes[%u] = %s\n"),i,ptrAttributes[i].lpStr());
          delete [] ptrAttributes;
          _tprintf(_T("\n\n\n"));
    };
    SQLFreeHandle(SQL_HANDLE_ENV,hEnvr);
}
getchar();

return 0;
}


So, what I'm working towards here, that is, the big picture, is an attempt to let C++ carry a bit more of my coding load than it has in the past, where I only used it for my handheld data recorder programming in Windows CE.  But for me to adopt it fully into my desktop programming, I would have to surmount the following obstacles, some of which are complete (marked With Check)....

Code Select Expand


String Class                         Check
64 Bit COM                           Check
64 Bit ODBC/ADO                      Check
Dynamic Multi-Dimensional Arrays     Next Battle