BBC BASIC for Windows
Programming >> Database and Files >> ODBC library
http://bb4w.conforums.com/index.cgi?board=database&action=display&num=1420544291
ODBC library
Post by joecurtis on Jan 6th, 2015, 10:38am

I am a new boy to BBC basic for windows and have been somewhat confused trying to access a MySQL database using the MSDN ODBC literature for guidance.

The literature is all based on ODBC3.x. In particular, SQLAllocEnv, SQLAllocConnect and SQLAllocStmt have been replaced with a single SQLAllocHandle procedure with a variety of parameters to get the relevant handles.

As I couldn't get SQLAllocHandle to work I reverted to the three procedures shown above and in the example shown in the 'Oracle Database Connection' message in these pages which worked without a problem.

From this I assume that the library odbc.dll available in BB4W must be for ODBC2.x.

Is a library for ODBC3.x available? 3.x seems to have been around for some time. There seems to be quite a few changes from 2.x and the only literature I could find on 2.x procedures was how each of the superceded ones were mapped to their 3.x equivalents.
Re: ODBC library
Post by rtr2 on Jan 6th, 2015, 12:06pm

on Jan 6th, 2015, 10:38am, joecurtis wrote:
From this I assume that the library odbc.dll available in BB4W must be for ODBC2.x.

When you say "available in BB4W" where did you find it? ODBC32.DLL is a standard Windows component; on my Windows 8.1 laptop it's in C:\Windows\System32 (64 bits) and C:\Windows\SysWow64 (32 bits). So, although I don't know what version of ODBC it corresponds to, I would have expected it to be 'current'.

The DLL is dated 22/08/2013 and the version is reported as 6.3.9600.16384. Is that the file you are referring to?

Edit: SQLAllocHandle is definitely exported from ODBC32.DLL, so doesn't that mean it's ODBC3?

Code:
      SYS "LoadLibrary", "ODBC32.DLL" TO odbc32%
      SYS "GetProcAddress", odbc32%, "SQLAllocHandle" TO `SQLAllocHandle`
      PRINT ~`SQLAllocHandle`

Richard.
Re: ODBC library
Post by joecurtis on Jan 6th, 2015, 3:11pm

I agree with what you are saying but these three sections of code:-

SYS "LoadLibrary", "ODBC32.DLL" TO odbc32%
SYS "GetProcAddress", odbc32%, "SQLAllocHandle" TO `SQLAllocHandle`
PRINT ~`SQLAllocHandle`

SYS `SQLAllocHandle`, "SQL_HANDLE_ENV", "SQL_NULL_HANDLE", ^env%
PRINT env%

SYS "GetProcAddress", odbc32%, "SQLAllocEnv" TO `SQLAllocEnv`
SYS `SQLAllocEnv`, ^env%
PRINT env%

Give the following three results:-

68E9A575
0
35412224

So the second one returns 0, i.e. an error when I test for it. So I am at a loss to see where I am going wrong.
Re: ODBC library
Post by rtr2 on Jan 6th, 2015, 3:58pm

on Jan 6th, 2015, 3:11pm, joecurtis wrote:
So I am at a loss to see where I am going wrong.

As I read the docs for SQLAllocHandle the first and second parameters aren't strings, as you are sending, but are numeric constants:

http://msdn.microsoft.com/en-us/library/ms712455.aspx

If I modify your code accordingly I get a non-zero response:

Code:
      REM!WC Windows Constants:
      SQL_HANDLE_ENV = 1
      SQL_NULL_HANDLE = 0

      SYS "LoadLibrary", "ODBC32.DLL" TO odbc32%
      SYS "GetProcAddress", odbc32%, "SQLAllocHandle" TO `SQLAllocHandle`
      PRINT ~`SQLAllocHandle`

      SYS `SQLAllocHandle`, SQL_HANDLE_ENV, SQL_NULL_HANDLE, ^env%
      PRINT env%

      SYS "GetProcAddress", odbc32%, "SQLAllocEnv" TO `SQLAllocEnv`
      SYS `SQLAllocEnv`, ^env%
      PRINT env%

which gives:

6A7F552E
5779320
5779448

Richard.
Re: ODBC library
Post by joecurtis on Jan 6th, 2015, 4:50pm

Many thanks for that Richard.

I find the MSDN documentation extreemly laberinthine when trying to work out the format of entries. Perhaps more exposure will improve my understanding.

Joe Curtis
Re: ODBC library
Post by joecurtis on Jan 6th, 2015, 6:19pm

One final point, which might help other naive users like myself navigate the ODBC api labyrinth is the link between the api and the C++ sql.h library where all the SQL definitions are defined. Now I understand that it has all become much clearer.
Re: ODBC library
Post by rtr2 on Jul 9th, 2015, 5:44pm

on Jan 6th, 2015, 6:19pm, joecurtis wrote:
One final point, which might help other naive users like myself navigate the ODBC api labyrinth is the link between the api and the C++ sql.h library where all the SQL definitions are defined.

Just to add that at least some of the SQL constant definitions are already included in the Windows Constants utility (slot 8 in the BB4W Utilities menu, usually) so it's not necessary to refer to SQL.H for these.

Richard.
Re: ODBC library
Post by joecurtis on Jul 10th, 2015, 2:34pm

Thanks for that Richard - most useful. Up until now I have been using the NBCI C++ free search facility at http://www.ncbi.nlm.nih.gov/IEB/ToolBox/CPP_DOC/lxr/search to find values for C++ constants.
Re: ODBC library
Post by rtr2 on Jul 10th, 2015, 5:23pm

on Jul 10th, 2015, 2:34pm, joecurtis wrote:
Thanks for that Richard - most useful.

For your added information, I have counted them and the Windows Constant utility's database contains 1,600 constants starting SQL_ smiley

Richard.