` Printed Icetips Article
Icetips Article

Par2: ODBC -- Making the first connection (Access)
1998-11-14 -- John Morter
I'm a long time Clarion developer, but have always used Clarion and/or
Topspeed files. (Including a Topspeed file happily managing more than
12,000,000 records - that's 12 million! - as a data source to a
BusinessIntelligence environment.)

For something new I thought i'd have a go working with ODBC - motivated by
my attendance at the Victorian Clarion Developers' Conference in Ballarat
(near Melbourne, Australia) last weekend.

However, I found getting started not so simple;
- not because it's hard to do - but because the steps are not real obvious
(at least they were not to me).

So, having sorted myself out (with a few words of guidance from
FredUpchurch and ScottFerrett) I thought I'd lay out the process for those
who come behind me.

Assumptions: 
This document describes accessing the Access 7.0 (or Abcess as ScottF
fondly refers to it :-) Northwind demo database - which contains a number
of tables - via 32bitODBC in a Win95 environment.

1) See the ODBC Accelerator Driver section in the App.Handbook (CW4)
Read the "How ODBC Works" section
- this will give you some background.

2) Fire up CW  (CW4b in my case)
Create a new Dictionary - call it something like odbcdemo.dct

3) Take menu option File-ImportFile - from within the Dictionary Editor
- this is the recommended way of getting SQL table definitions into your CW
dictionary

You'll be asked to Select File Driver
- select ODBC

You'll be presented with a list of Data Sources (I'm refering here to the
32bit Data Sources)
- select the MSAccess data source 
- press the Next button

4) If you now get a Select Database dialogue box, then STOP !
Here was my main problem issue
- very simple, as it turned out, but it caused me no end of confusion.

If you navigate to the Northwind.mdb database via this dialogue then the
Import Wizard presents you with a list of tables and lets you select one
for import. It then happily imports all details about this table into your
dictionary.

The catch is, the Data Source is pretty much just a pointer to the database
you want to access - it describes the driver you need to use, and it should
also provide the path to the database - in this case, to Northwind.mdb

But, if you got this Select Database dialogue box then the pathname is
missing from the Data Source definition
- and your app. will always start up by asking you to Select Database
- 'cos it doesn't know where to find it
- and manually adding the pathname to the FullPathname in FileProperties
*will not* help !!

So, cancel out of the Select Database dialogue (for some reason you've
gotta do this twice - ScottF ?)

Back on the Data Sources window, select the New... button
- select an ODBC driver - in this case MSAccess
On the Data Source set-up window
- enter a Data Source Name of your choosing (eg. I created "MS Access 7.0
Northwind")
- press the Database-Select... button
You'll get the now familiar Select Database dialogue box
- navigate to, and select, Northwind.mdb
- OK

Now you've got a Data Source, based on the MSAccess ODBC driver, which
properly points to the Northwind demo database;
- select your newly created data source 
- press the Next button

5) You're now presented with a list of all the tables that reside within
the Northwind database
- select one of them
- press the Finish button

Repeat steps 3 and 5 for as many tables as you want
- if you hit step 4 again you've done something wrong - try again, or send
me e-mail.

6) Create a new App.
- enter your new odbcdemo.dct as the Dictionary
- check on the Application Wizard
- follow the wizard to create an app. for at least one of the tables in
your odbcdemo.dct

7) When the app. generation process has finished
- press the Project button - this calls up the Project Editor
- press the Properties button
- change the TargetOS to 32bit
- OK, OK

This is required because (according to ScottFerrett): "The trouble is that
there is no 16bit access to Abscess.  When Microsoft changes standards they
do not feel any need to support old ones. "

This is also the reason why the CW IDE (which is itself a 16bit app.)
cannot browse a datasource via a 32bitODBC.

But this really puzzled me on the way thru this process ...
- how come the Import Wizard was able to read the database table
definitions ?!
The answer, again, comes from ScottF: "We have a special thunking system
that does the import in 32bit, then passes the information down to the
16bit IDE."
- clever, huh !

8) Compile your 32bit app, and run it;
- you'll be able to browse entries in one of the Northwind tables.

It's working (something) like this;
- opening a file creates a link to the related table within Northwind.mdb
via the ODBC Driver Manager component using the data source "pointer"
- Clarion file I/O statements (SET, NEXT, etc) are converted to SQL
statements (SELECT, etc) by the ODBC "backend" driver to access table
entries (records)

9) But bear in mind, SQL works in record "sets";
ie. all those table entries (records) which satisfy some condition
- not record by record.
See the "SQL Driver Behaviour" and "Performance Considerations" sections of
the SQL Accelerator Drivers chapter in the App.Handbook.

HTH&GL
Printed November 21, 2024, 6:29 am
This article has been viewed/printed 35203 times.