ODBC

Last update:
March 10, 1998

What is ODBC?

Open Database Connectivity (ODBC) technology provides a common interface for accessing heterogeneous SQL databases. ODBC is based on Structured Query Language (SQL) as a standard for accessing data.

This interface provides maximum interoperability: Pragma 5 can access different SQL Database Management Systems (DBMS) through a common set of code.

Practically speaking that means that ODBC is a new file manager for Pragma 5, but a file manager that can access a great number of different databases, like Microsoft Access MDB, Microsoft SQL Server, Oracle, Excel, etc.

ODBC in Pragma 5

Implementation.  The present implementation of ODBC (build 21) uses 4 verbs:

ODBC Installation

ODBC Administrator

As of version 5.41 build 20 Pragma must have the ODBC Administrator and all its files installed on the operating system.

The ODBC Administrator is a Microsoft utility that is part of Windows NT 4.0 and Windows 98, but not of Windows 95. Certain programs, like Microsoft C++ or Microsoft Access install it automatically.

To see if the ODBC Administrator is present go to the control Panel and see if you have the ODBC icon, which looks like this, with the writing ODBC below.

But be sure that the icon you see is the right one. It must have the 32 written across. Pragma 5 being a pure 32 bit applications, can only use the 32 bit drivers, 16 bit drivers will not work.

If you do not find this icon you must download from our web site or from the Microsoft web site the file ODBCDMIN.EXE (1.041 Mb) and run it. The program will install the administrator, all the necessary files and the ODBC Administrator icon in the control panel.

Please heed the following warning, which comes straight from Microsoft:

"If you install the updated ODBC 3.0 Driver Manager or Windows NT 4.0 Service pack 3 (which automatically installs the updated ODBC 3.0 Driver Manager), and you have installed Office 97 Developer Edition, you must also install Office 97 Developer Edition Service Pack 2 to maintain the Setup Wizard".

The Microsoft ODBC Administrator program can be freely distributed.

Drivers

The ODBC Administrator itself does not come with any drivers. They must be installed separately. You only need drivers if you plan to use ODBC. Then you must have the driver for the database that you want to access.

To see if you have any drivers installed go to the control panel and click on the ODBC icon. (The one for the 32 bit drivers) and click on ODBC Drivers tab. You will see a listing all the installed drivers (if any).

If you installed on your computer a program like Visual C++, Visual Basic or Delphi you will find that you have drivers installed (or you possibly chose not to install them during the setup of the programs).

If you don't have any drivers you can download from our website the files DATAACC.EXE (4.510 Mb) and DATAACC.TXT (13 Kb).

DATAACC.EXE contains the Microsoft Data Access Pack installation program and DATAACC.TXT contains the Microsoft License Agreement for the datapack. Read it before you get into trouble with Microsoft.

The following drivers are supplied in the Data Access Pack:

SQL Server

Microsoft Access

Microsoft Fox Pro ®

dBase III ®, dBase IV ® and dBase 5 ® for Windows

Paradox ®

Microsoft Excel

Text

To install the drivers copy the DATAACC.EXE to your computer and run it. You will run the Microsoft Data Access Pack installation program. After you successfully installed the program go to the ODBC Administrator (in the Control panel) and check that  the drivers you wanted have been installed.

We do not have any additional drivers. Usually they are supplied with the database program. For instance, the Oracle ® driver is part of the Oracle database program.

DSN File

Before you can access a database file you must create its DSN (Data Source Name) file.   This text file contains the connect script for accessing the file.  Among other information the connect script gives the exact location of the file and the driver used to access it.

The procedure below outlines how you create the file for an access database.  You will be using the ODBC Administrator and the procedure is a bit confusing at first .

• On our website you can find the testfile STDREG32.MDB (131 Kb), a Microsoft Access  database used for testing. Copy it to any directory that can be seen by Pragma 5.
• Run Pragma 5, go to the View ODBC Data menu. In the ODBC Viewer window click on the "New…" button.   This will open a part of the ODBC Administrator.
• Click on the tab  "File Data Source" and there on the "New..."   button.
• In  the "Create New Data Source" select the driver you need (Microsoft Access Driver)  and then click on the "Next" button.
• Click on "Browse" button and you get to the "Save As" window.  There go to the directory where you want to save the DSN file.  Enter as filename the name you want to give to the DSN file.  In our example STDREG32.  Save.  The type must be  "ODBC File Data Source  ".
• You get back to the "Create New Data Source" window.   Click on "Next".
• Click on "Finish" and you get to the "ODBC Microsoft Access 97 Setup" window.  There click on "Select…. ".
• In the "Select Database" window select the file (in our example) STDREG32.MDB and then click on OK.
• Back in the "ODBC Microsoft Access 97" window click on OK.
• Back in the "Select Data Source" window click on "Cancel".

Test

Make sure that you created the DSN file in the directory you specified.  For each database you want to access you must create a DSN file.  Be aware that the procedure might change a bit for different drivers.  Just keep in mind that somehow you must create a DSN file in your chosen directory.

Then in Pragma's ODBC viewer click on the Search button and in the resulting "Input a DSN file" window find and click on the newly created STDREG32.DSN file.

Finally, when the Data source combo shows the path to your DSN file, click on the connect button.

If you did everything right, the table combo will fill with the available tables and the View Data and Properties button will be enabled. If somewhere you made a mistake, the Select Data Source window will reappear (you are back to square one).

Select a table and then hit the View Data and Properties button.

Pragma 5

To access an ODBC file from within a Pragma 5 program you can use the following verbs:

The verbs are documented in the Reference Help. 

To use the ODBC verbs you must first create a file.  The first noun of the file is the keynoun and is at present not used by ODBC.  The following nouns will get filled with the data from the ODBC file.  The name of the nouns is not important, nor is the number of nouns.  If the file has more nouns than the ODBC file has columns (fields) not all the nouns will get filled.  And vice versa.

To enable a file to use ODBC you must go to the header of the file and change the mock flag of the file to 32 and fill the two parameters SQL DSN filename and the SQL table name.

The tutorial vocab contains an example that accesses an ODBC database: FORM TEST ODBC.

Please remember that what you can do always depends on the capabilities of the ODBC driver that you use.

Various

Excel Files

To view Excel files you must first give a name to the rows and columns you want to view.

Go to the Excel program and select with the mouse the rows and columns you want to view.

Then go to Insert, Name, Define.  There enter a name (no spaces are allowed).   The name will then appear in ODBC as the table name.