A Simple ODBC Primer (updated February 2021)

[TI-994A]

Since the topic has not been thoroughly addressed in this forum, here's a simple primer for those who might be interested. The sections have been enclosed in quote boxes just for clarity purposes.


The Sample Database used:

This example makes use of the sample AgeRange.mdb database provided by the ms-access2010.com website. It is contained in the sample databases zip file, which can be downloaded here:

> Access 2010 sample databases


The unzipped MDB files could also be downloaded from my Dropbox folder, here:

> AgeRange.mdb

> AgeRangex64.mdb (added 23/2/2021)


The examples here will be reading from the table [tblEmployees], which contain these fields:

[employee number, last name, first name, job title, name title, birth date, hire date, address, city, state, zip code, country, phone, extension, notes]

The use of ODBC requires data sources to be set up beforehand. This could be done through the control panel, or programmatically through the SQLConfigDataSource() API function.

1. Setting up the ODBC data source through the Control Panel:

1. Under System & Security in the control panel, select Administrative Tools.
2. From the file explorer that appears, select ODBC Data Sources (32-bit or 64-bit).
3. From the ensuing dialog, click ADD, and select the database type.
-> for this example, select Microsoft Access Driver (*.mdb, *.accdb)
4. In the next dialog, only the Data Source Name is required.
-> for this example, the name being used is dbSourcename.
5. Then click SELECT, to browse for the database file to use as the data source.
-> for this example, browse to the folder containing the sample database AgeRange.mdb.
6. Click OK, and the new data source should now be ready for use.

This newly created data source should now be accessible from PureBasic as follows:

Code: Select all

;uses AgeRange.mdb as the data source, downloadable from:
;1. http://www.ms-access2010.com/tutorials/download.html (samples database zip file)
;2. https://www.dropbox.com/s/ooiem87897ybpzi/AgeRange.mdb?dl=1
;3. https://www.dropbox.com/s/z23igvk77kmuyix/AgeRangex64.mdb?dl=1

UseODBCDatabase()

If ExamineDatabaseDrivers()
  ;this should display the datasource that was just created
  While NextDatabaseDriver()
    Debug  DatabaseDriverName() 
  Wend
  
  ;use the Data Source Name provided in the ODBC setup
  If OpenDatabase(0, "dbSourcename", "", "", #PB_Database_ODBC)
    ;reading from the [tblEmployess] table
    If DatabaseQuery(0, "Select * From tblEmployees")
      While NextDatabaseRow(0)
        ;displays all the first and last names
        Debug GetDatabaseString(0, 2) + " " + GetDatabaseString(0, 1)
      Wend
    Else  
      MessageRequester("ODBC", DatabaseError())
    EndIf
    CloseDatabase(0)
  Else
    MessageRequester("ODBC", DatabaseError())
  EndIf
Else
  MessageRequester("ODBC", "No ODBC data sources found!")
EndIf

2. Setting up the ODBC data source programmatically through the API:

This approach requires the use of the SQLConfigDataSource() API function, which is called directly from within PureBasic, as follows:

Code: Select all

SQLConfigDataSource_(#Null, #ODBC_ADD_DSN, "Microsoft Access Driver (*.mdb, *.accdb)",
                     "Server=127.0.0.1; Description=dbDescription; " + 
                     "DSN=dbSourceName; DBQ=" + dbFile$ + "; UID=; PWD=;")

This sets up the ODBC data source dynamically, doing away with the manual Control Panel approach. The command format is quite sensitive, so please adhere to the given spacings and separators.

The complete code would now look like this:[/color]

Code: Select all

;uses AgeRange.mdb as the data source, downloadable from:
;1. http://www.ms-access2010.com/tutorials/download.html (samples database zip file)
;2. https://www.dropbox.com/s/ooiem87897ybpzi/AgeRange.mdb?dl=1
;3. https://www.dropbox.com/s/z23igvk77kmuyix/AgeRangex64.mdb?dl=1

UseODBCDatabase()
#ODBC_ADD_DSN = 1

dbFile$ = OpenFileRequester("Select database", GetCurrentDirectory(), 
                            "Access Files|*.mdb;*.accdb", 0)
If dbFile$
  If SQLConfigDataSource_(#Null, #ODBC_ADD_DSN, "Microsoft Access Driver (*.mdb, *.accdb)",
                          "Server=127.0.0.1; Description=dbDescription; " + 
                          "DSN=dbSourceName; DBQ=" + dbFile$ + "; UID=; PWD=;")
    If OpenDatabase(0, "dbSourceName", "", "", #PB_Database_ODBC)
      ;reading from the [tblEmployess] table
      If DatabaseQuery(0, "Select * From tblEmployees")
        While NextDatabaseRow(0)
          Debug GetDatabaseString(0, 2) + " " + GetDatabaseString(0, 1)
        Wend
      Else  
        MessageRequester("ODBC", DatabaseError())
      EndIf
      CloseDatabase(0)
    Else
      MessageRequester("ODBC", DatabaseError())
    EndIf    
  Else
    MessageRequester("ODBC", "ODBC initialisation error!")
  EndIf
EndIf

That's about it. Hope it'll be helpful.


* Notes & Edits:

18th February 2019:
1. updated download links


23rd February 2021:
1. added link to an Access 2000 x64 version of the AgeRange database (AgeRangex64.mdb)

2. Databases created with 32-bit applications must be mounted as 32-bit data sources, using the 32-bit version of the ODBC Data Source Administrator, and accessed with PureBasic x86.

3. Databases created with 64-bit applications must be mounted as 64-bit data sources, using the 64-bit version of the ODBC Data Source Administrator, and accessed with PureBasic x64.

4. The two most common errors when using ODBC are:

a. Cannot open a database created with a previous version of your application.
This occurs when a data source is mounted with the wrong bit size.

b. The specified DSN contains an architecture mismatch between the Driver and Application.
This occurs when attempting to open a data source with an application of a different bit size.

[Keya]

thankyou for sharing. Ive been interested in databases for a while now ever since i saw how easy Purebasic makes them to use, but i was still missing the other pieces of the puzzle to get started (ie. everything above!). Maybe this holidays i can give them a go

[skywalk]

TI-994A - Thanks for the topic, I have to read an Access database briefly.
But your dynamic example does not work?
GetLastError_() reports:
Error 3: The system cannot find the path specified.
ODBC: ODBC initialize error!

I am running x64. Does this work for both x86 and x64?
This example shows null terminated strings in the SQLConfigDataSource() statement.

[mk-soft]

Did you install the 64Bit database driver from MS?

By default only the SQL-Server driver is installed for 64bit.

[skywalk]

I noticed there was only SQL Server in ODBC Data Source Administrator (64-bit)
But, it is shown in 32-bit. What the heck!
EDIT: It looks like I have to install a bridge to connect to 32-bit Access ODBC drivers? What a pain. Has anyone done this already or just made 32-bit apps for this connection?

[mk-soft]

N0,
Download Microsoft Access Database Engine 2010 X64

Link: https://www.microsoft.com/en-US/downloa ... x?id=13255

[skywalk]

Nice, I'll give that a try.

[collectordave]

Brilliant.

Downloaded driver ran the code selected accdb database and all there.

If anyone knows how to get a list of tables from the database that would be great.

Found how to output the data and table schema just cannot get a list of tables.

Thanks


CD

[mk-soft]

Show ExDataBase.pbi

Link: viewtopic.php?f=12&t=67180