` ODBC -- Making the first connection (Access) (John Morter ) - Icetips Article
Icetips - Templates, Tools & Utilities for Clarion Developers

Templates, Tools and Utilities
for Clarion Developers

Icetips Article

Back to article list   Search Articles     Add Comment     Printer friendly     Direct link  

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

Today is April 24, 2024, 7:52 am
This article has been viewed 35112 times.


Back to article list   Search Articles   Add Comment   Printer friendly

Login

User Name:

Password:


Gold Subscription
Buy Now
Silver Subscription
Buy Now
Solo Subscription
Buy Now
Multiple Copy Pricing
Renew Subscription