For
ODBC
User's Guide
Version 2.2
Newsgroup:
news://dataaccess.com/dac-public-newsgroups.Connectivity-Kit_Support
Internet Address (URL): http://www.dataaccess.com
FTP Site:
ftp://ftp.dataaccess.com
Copyright 2003 DATA ACCESS CORPORATION. All rights reserved. Windows, when used in this manual, refers to the Microsoft Windows operating system.
No part of this publication may be copied or distributed, transmitted, transcribed, stored in a retrieval system, or translated into any human or computer language, in any form or by any means, electronic, mechanical, magnetic, manual, or otherwise, or disclosed to third parties without the express written permission of Data Access Corporation, Miami, Florida, USA. DISCLAIMER
Data Access Corporation makes no representation or warranties, express or implied, with respect to this publication, or any Data Access Corporation product, including but not limited to warranties of merchantability or fitness for any particular purpose.
Data Access Corporation reserves to itself the right to make changes, enhancements, revisions and alterations of any kind to this publication or the product(s) it covers without obligation to notify any person, institution or organization of such changes, enhancements, revisions and alterations. TRADEMARKS
Windows NT, Windows 95 and Windows 98 are registered trademarks of Microsoft Corporation.
DataFlex and Visual DataFlex are registered trademarks of Data Access Corporation.
All other company, brand, and product names are registered trademarks or trademarks of their respective holders.
Chapter 1 - Introduction
... 7
Chapter 2 - Overview of the Connectivity Kit for ODBC
.... 9
ODBC
... 9
DataFlex
... 11
DataFlex and ODBC
... 11
What you Need
... 12
Chapter 3 - Installation
... 15
Chapter 4 - Converting Data to ODBC
... 19
Database Builder Conversion
... 21
CKMgr Conversion
... 25
Chapter 5 - Connecting to Existing ODBC Data
... 29
Manual connect
... 29
Connect using Database Builder
... 30
Chapter 6 – Structure caching
... 35
Chapter 7 - Intermediate File
... 37
Header
... 39
Table Keywords
... 41
Column Keywords
... 46
Setting Character Formats
...53
Recommendations
...54
Converting Table Formats
...54
Chapter 9 - ODBC Specific Commands and Techniques
57
Attributes
...57
Commands
...61
Chapter 10 - Record Identity
... 73
What is a Record Identity?
...73
Defining the Record Identity
...75
Recnum in Programs
...76
RECNUM relationships
...77
Chapter 11 - NULL Values and Defaults
... 79
Null values
...79
Default values
...84
Configuring Null and Default values for conversion
...86
Recommendations
...87
Chapter 12 - Transactions
... 89
Concurrency
...89
Transactions and locking in the DataFlex language
...92
Transactions and the DataFlex Connectivity Kit for ODBC
. 102
Chapter 13 – Database Builder
... 111
Database Menu
... 111
File menu
... 114
Maintenance menu
... 120
Chapter 14 - Error Handling
... 123
Driver Level Errors
... 123
Database Level Errors
... 126
Appendix A – ODBC Escape sequences
... 133
Literals
... 133
Scalar functions
... 133
Appendix B - ODBC Data Sources
... 143
Appendix C – Configuration file ODBC_DRV.INT
... 147
Appendix D - Getting Support
... 156
How to Get Technical Support
... 156
DataFlex Connectivity Kit for ODBC
1
In
troduct
ion
Chapter 1 - Introduction
The DataFlex Connectivity Kit for ODBC is used to access ODBC databases from DataFlex programs. ODBC (Open DataBase Connectivity) is an industry standard that allows programs to access multiple database types without the need to rewrite the code for the program or having to re-link the program. A variety of database management systems can be accessed through ODBC. These include enterprise database systems such as Oracle, Sybase, SQL Server, flat file systems as dBase, Paradox and even non-database systems as Excel and ASCII.
Many existing DataFlex applications, without change, will be able to use these non-DataFlex database systems. Some other DataFlex applications require changes in order to make them perform well with these database systems. This document describes the steps you need to take to convert existing DataFlex data to an ODBC data source, how to connect a DataFlex program to an already existing ODBC data source, and information on how to adjust program code so that it takes advantage of the connectivity options of DataFlex.
DataFlex Connectivity Kit for ODBC
2
Over
vi
ew
Chapter 2 - Overview of the Connectivity Kit for
ODBC
ODBC
ODBC or Open DataBase Connectivity defines a method of connecting to data sources that are open to as many applications and data sources as possible. To accomplish this, the application and the data source have agreed upon a common method to access the database. This agreement defines a complete set of API function calls and a complete SQL syntax set together forming ODBC.
The database side of this open connectivity is provided by drivers, contained in Dynamically Linked Libraries (DLLs). These drivers transform the ODBC API functions into functions supported by the particular data source being used. The ODBC SQL syntax is translated in a similar way into syntax accepted by the data source. The manufacturer of the database system usually produces these drivers, but there are many third party vendors as well.
The ODBC architecture consists of four major components. They are described as follows.
Data source
A Data Source identifies the server and database therein that will be
accessed. A Data Source defines the ODBC driver to use for the connection. Depending on the driver it also defines the location of the data, the server on which the data resides, the database in which the data resides and so forth. Data Sources are created through the ODBC Administrator; the administrator can be started from the Control Panel or from the DataFlex Database Builder.
Driver
The driver is a DLL that sits between the driver manager and the data source. It processes ODBC function calls. It passes commands from the application to the data source, after possible translation. It also receives the result for the commands from the data source and passes it back to the application
Over
vi
ew
Driver Manager
This DLL is generally provided by Microsoft as part the ODBC installation. It loads driver DLLs and directs function calls to them.
Application
A program that processes data.
Application
Driver Manager
Driver Driver Driver
Data Source Data Source Data Source
Four components of ODBC architecture.
It is important to understand that ODBC is designed to expose database capabilities, not supplement them. Thus, application writers should not expect that ODBC would suddenly transform a simple database into a fully featured relational database engine. Nor are driver writers expected to implement functionality not found in the underlying database.
Over
vi
ew
DataFlex
Data Access Corporation introduced DataFlex in 1981. It was the first fourth generation language (4GL) and RDBMS available for Local Area Networks (LANs). In 1991, Data Access Corporation introduced DataFlex revision 3.0; the first commercially available object oriented 4GL and RDBMS in the industry.
Today, DataFlex still comes with the closely integrated database but also includes an open database API to access industry-leading databases like SQL Server, Pervasive.SQL and DB2.
DataFlex offers a complete suite of products that let developers create real world software solutions. It runs on DOS, LANs, Windows and all popular Unix platforms. Visual DataFlex is available for Windows. The DataFlex® WebApp™ Server is available for Windows NT using the Microsoft Internet Information Server.
DataFlex 3.1 and higher and Visual DataFlex are API enabled runtimes. All database access uses the same internal core functions in the API part of the runtime. Native DataFlex tables are accessed through a call to the API, the API then passes these requests to an internal DataFlex native driver. This driver is linked to the runtime.
The runtime also has the ability to load other database drivers dynamically. For example, the DataFlex Connectivity Kit for ODBC is loaded dynamically. This approach lets the basic DataFlex code remain the same even when the backend data is within an ODBC data source. Installing a database and loading a driver into an existing DataFlex environment makes that environment capable of using the driver and accessing the backend supported by that driver.
This flexible driver technique allows you to update drivers independent of the runtime, or vice-versa.
DataFlex and ODBC
The Data Access Database API expects database drivers to support a certain set of attributes for a table. These attributes may or may not be available in the supported backend. If such an attribute is not available in the backend it needs to be stored somewhere outside of the backend database. The API uses so-called “intermediate files” to store such information. The contents of an intermediate file are partly API defined and partly driver specific.
Over
vi
ew
The DataFlex Connectivity Kit for ODBC will use the backend for its attributes whenever possible. The goal is to keep the intermediate file as small as possible. Some information can only be stored in the intermediate file because ODBC does not support the attribute essential to DataFlex. The ODBC Connectivity Kit expects an intermediate file to be present for every table that is accessed through it.
A design goal of the ODBC client was that a DataFlex program should be able to switch the underlying database to ODBC without the need to adjust that program. This goal imposes a requirement on the tables that can be accessed through the driver. Every table must have a record identity. If you use the conversion utilities supplied with the driver, record identities can be
automatically created for you. If you want to use the driver to access existing ODBC tables, those tables must have a record identity. If a table fails to have a record identity, it cannot be accessed through the driver.
What you Need
In order to use the DataFlex Connectivity Kit for ODBC you will need the following components:
• A DataFlex program
• An API enabled DataFlex runtime that supports loading dynamic libraries • The DataFlex Connectivity Kit for ODBC
• ODBC (version 3.0 or higher) • At least one ODBC driver
• Depending on the database you may need a Database Management System and the appropriate means to communicate with it
A DataFlex Program
In order to access the data there must be a program; you, the end user, supply this program.
The DataFlex Runtime
Several DataFlex runtimes support the Connectivity Kit. For character mode applications, this is the DataFlex 3.1d (or higher) runtime using the Console Mode runtime operating on Windows. For Graphical User Interface (GUI) applications, this is Visual DataFlex 7 service pack 3 (or higher).
Over
vi
ew
All these runtimes include the Data Access Database API. This API allows DataFlex to communicate with supported database systems with little or no regard to which specific database system is being used. The API has the ability to support several different database systems. This is accomplished by an agreed upon common method of accessing a database.
A database system is accessed through a dynamic library known as an API driver. Such a driver implements the common methods the API expects. The API enabled runtime has the ability to load API drivers dynamically. This enables DataFlex to communicate with every database system that utilizes an API driver. The DataFlex Connectivity Kit for Microsoft ODBC is simply an API driver.
The DataFlex Connectivity Kit for ODBC
The DataFlex Connectivity Kit for ODBC is the API driver for ODBC, supplied by Data Access Corporation.
It uses Call Level Interface (CLI). The DataFlex Connectivity Kit for ODBC will translate DataFlex runtime commands into SQL statements. The statement will be executed on the data source and the result is returned to the DataFlex runtime.
ODBC
ODBC alone cannot access database tables. ODBC only consists of the driver manager and some applications that enable the user to setup ODBC Data Sources.
It is not the intention of this document to fully explain how to setup data sources for particular database systems. Specific examples will be given in this document. For further information regarding installation, access and use of ODBC refer to the instructions supplied by Microsoft or the manufacturer of your ODBC database driver.
An ODBC driver
For each database system to be accessed an ODBC driver is needed. The database system manufacturer typically provides an ODBC driver. Third party vendors also have made ODBC database drivers available for many different database systems.
Over
vi
ew
A database system
Some database systems will not require anything else than the ODBC driver. Usually these are flat-file database systems. Some systems however require a Database Management System to be installed. Such systems typically include connectivity pieces to connect workstations to the DBMS.
DataFlex Connectivity Kit for ODBC
3
In
sta
lla
tio
n
Chapter 3 - Installation
You can install the DataFlex Connectivity Kit for ODBC on Windows platforms. The kit must be installed on a machine that already has an API enabled runtime installed. The installation program will not install database client or server software; it is assumed that you already have a database environment installed. For more information on installing a database environment, refer to the database’s manual.
To install the Connectivity Kit you need to run the dfodbc.exe installation program. This program will take you through the steps needed to install the Connectivity Kit in an existing DataFlex console mode and/or Visual DataFlex environment. For more information on installing DataFlex or Visual DataFlex please refer to the respective Installation and Environment Guide.
1. Select Component
When you start the installation program, the first step is to select the components to install. You have several options:
OPTION
DESCRIPTION
Client for Visual DataFlex Select this option if you want to install the Connectivity Kit in a Visual DataFlex (VDF) 4.0c or higher environment. The installation program prompts you for the VDF directory. This is the root directory of the VDF install.
Client for DataFlex Console
Mode Select this option if you want to installthe Connectivity Kit in a DataFlex Console Mode 3.1c or higher
environment. The installation program will prompt you for the Console Mode directory. This is the root directory of the Console Mode install.
Online Documentation Select this option if you want the online documentation installed on your machine. It will be installed in the destination directory.
Create Program Group Select this option if you want a program group created in the start menu. The
In
st
alla
tio
n
OPTION
DESCRIPTION
program group enables you to open the documents or to uninstall the driver. Development install Select this option if you want the
Connectivity Kit development packages installed in the DataFlex development environment. The packages will be installed in the Visual DataFlex and/or the DataFlex Console Mode
development environment depending on the selections made.
This option will also install sample program source code. The sources of the conversion programs will be
installed if this option has been selected and the “Tools & Utilities” option is selected.
Tools & Utilities Select this option if you want to install conversion tools and utilities. The OEM Ansi converter will be installed if this option is selected. If you are installing for DataFlex Console Mode, the database conversion program, ckmgr, will also be installed.
2. Select Directories and Setup Installation Options
The next steps let you select the directories where you want the components installed. In most cases, the default directories should be accepted. In addition, you can setup a number of installation options.
3. Finish Installation
Finally, after entering the installation information, the actual installation starts. All files of the selected components will be installed in the directories you have specified.
In
sta
lla
tio
n
4. Register
After the components have been installed, you must register the Connectivity Kit for use of Embedded SQL. The registration must be done in every DataFlex environment where the kit is installed (VDF and Console mode). You can choose to register later. In that case the Embedded SQL functionality Connectivity Kit will run in 31-day evaluation mode.
Registering is only needed for Embedded SQL. All other parts of the DataFlex Connectivity Kit for ODBC do not require registration. They run with or without a valid registration.
Uninstall
You can uninstall the Connectivity Kit by using Add/Remove Programs in Control Panel.
DataFlex Connectivity Kit for ODBC
4
Conver
ting
Data
Chapter 4 - Converting Data to ODBC
The DataFlex Connectivity Kit for ODBC is used for two main purposes: • To convert data to ODBC
• To attach to existing ODBC data This chapter discusses the conversion.
Restrictions
Using the DataFlex Connectivity Kit for ODBC, you can connect to a variety of database systems. These systems all have their own rules on what
constructions are legal and which data can be put into which column. Some database systems are more restrictive then DataFlex on the columns that can be placed in an index, others do not allow indexed columns to have an offset greater then the defined page size, and so forth. If a table contains a
construction that is illegal in the target platform, the conversion will not succeed.
There is no way to determine up front through ODBC, if a construction is legal in the target database system. Knowledge about the target database system can avoid a lot a frustration in this area and save a lot of time. If an illegal construction is present, the only solution is to change the data definition so that it is acceptable by the target database system. Usually when the data definition changes, the programs need to be adjusted too.
Next to illegal constructions, it is possible that certain columns have values that cannot be accepted by the target database. Many database systems do not accept two (2) digit year dates for example. If you have such dates in your original DataFlex files the data needs to be adjusted before it can be
converted.
Record Identity
The conversion can automatically add a record identity column to the ODBC table. This column will be named DFRECNUM. The column will not be automatically maintained. For more information on this subject, see Chapter 10 - Record Identity.
Conver
ting
Da
ta
Null & default values
The DataFlex Connectivity Kit for ODBC will use a configuration file
(ODBC_DRV.INT) to determine the “nullability” and default value of columns created in the conversion process. If no configuration file is found the default settings will be used. You can specify target setting per DataFlex type. For example, you can define that Numeric fields should be converted not to accept null values and use a default value of 0. See chapter Chapter 11 -NULL Values and Defaults for more information.
If more control over the target settings is desired convert the definition only. Adjust the “nullability” and default values to your specifications. After that copy the data from the original table to the new converted table. Most database systems only allow table definition adjustments to be made on empty tables. In this way a table that has column specific defaults can be created. The table definition can be adjusted through Database Builder or the utilities of the target database system.
We have found that null values can degrade finding performance
considerably. It is recommended not to allow null values in indexed columns. Table character format
DataFlex data is stored in OEM format. Non-DataFlex back ends may expect the data to be stored in Ansi format. When defining the conversion options you can define the table character format to be used in the converted table. For more information on this subject, see Chapter 8 - Character formats (OEM or Ansi).
Utilities
Every environment has its own conversion utility. The Console Mode runtime uses a utility called ckmgr.flx. Type dfruncon ckmgr to start the utility. In Visual DataFlex, you should use Database Builder version 1.143 or higher.
Create an ODBC data source
Before you can convert anything to ODBC, you need to create an ODBC data source for your target database system. The data source must be created with the ODBC administrator program. Refer to the documentation of the driver you are using for specific details on how to setup a data source. There are two types of Data Sources, machine- and file Data Sources. Both types contain similar information, they differ in the way the information is
Conver
ting
Data
stored. Because of these differences, they are used in a somewhat different manner.
Machine Data Sources are stored on the client system. Associated with the Data Source is all the information the ODBC Manager and database driver need to connect to the specified database. There are two Data Source subtypes, User- and System Data Sources. One specific user of the machine can use a User Data Source; all users that use the machine where the Data Source is defined can use a System Data Source.
File Data Sources are stored in a file with extension .dsn (in ASCII format). The file Data Source stores all the information the ODBC Manager and database driver need to connect to the specified database. The file can be manipulated like any other text file.
See Appendix B - ODBC Data Sources for more information on Data Sources.
Database Builder Conversion
Before data can be converted, the driver needs to be loaded. If you selected to automatically load the driver during install, the driver will be loaded on startup. Otherwise, you can load the driver by choosing the “Load database driver” menu choice in the “Database” menu of Database Builder. From the file dialog that displays, choose the ODBC_DRV.DLL file.
After the driver has been loaded, the “Database” menu will be expanded with extra items, of these new items choose “Convert to ODBC.” Or “Convert to ODBC from script”.
Convert to ODBC
After selecting Convert to ODBC, a list of all available tables is displayed. Select the tables you want to convert and click the Convert button. This activates the next dialog where you can specify the conversion options.
Convert To ODBC from script
After selecting this menu item, you will be prompted to select a conversion script file. Conversion script files, by default, have an extension of CNV but other extensions are allowed.
A conversion script file is an ASCII file that specifies what files to convert. For each file you want to convert to ODBC a line must be created in the
Conver
ting
Da
ta
<FileNumber>, <PrimaryFieldName>where:
<FileNumber> The filelist number of the file you want to convert to ODBC
<PrimaryFieldName> The name of the field that will be the record identifier. Such a field must be numeric and must have a main index that contains only this field. The index must already exist.
A conversion script file for the order entry sample application of Visual DataFlex looks like:
21, ID 25, NUMBER 30, ORDER_NUMBER
After selecting the file(s) you need to specify conversion options in the “Convert to ODBC, set options” panel. This panel is discussed below; in the convert from script process the Recnum support checkbox is disabled.
Conver
ting
Data
From the Convert to ODBC, set options dialogue you can setup the conversion. In the fields enter:
1. The data source name (DSN) of the data source you created in the ODBC administrator.
2. The user ID. This is not needed for all target database systems. If you fill in the information here, it will serve as a default when the ODBC driver prompts for the login information. If however you also specify a password the ODBC driver will not prompt for login information and the entered user id and password will be used always. (See discussion of password below).
3. The password of the user. This is not needed for all target database systems. If you fill in a password, you must also fill in the user
Conver
ting
Da
ta
you do not supply the password during conversion you will be
prompted for the password every time the table(s) you convert is (are) opened.
4. The schema name within the database where the tables must be placed. This is only required if the database supports schema and you want to use a different schema than the default.
5. Uncheck “Delete original after conversion” if you want the original table to keep on existing after conversion. If any errors occur during
conversion the original table will not be removed regardless of the selection you make here. This option should be unchecked if you want to copy the data to the converted table at a later moment.
6. Check “Recnum support” if you want the automatic record identity column to be added to the ODBC table. When checked this will add a column called DFRECNUM to the table definition. The new column will be placed in a new index. That index will automatically be set to be the primary index of the table. If you do not check the “Recum support” the table will be converted “as is”. The option is not available when
converting from script.
7. Check the “Convert definition only” checkbox if you do not want to copy the records of the original table over to the new table. This option can also be used to create an empty table in the target environment, manipulate its definition and copy the data at a later time. If you want to copy the data at a later moment you will have to uncheck “Delete original after conversion”.
8. Check the “File DSN” checkbox when the data source to convert to is a file data source.
9. Check the “Run unattended” checkbox if you do not want errors to popup during conversion. If you select this option, a log file will be created in which results of the conversion including the error messages, if they occur, will be written. The log file is called
ODBC_DRV.LOG and will be placed in the current working directory. This is the data directory of the current workspace. If there is already a file with this name, the log information will be appended to the existing file.
10. Select the “RECNUM Index conversion” type. A RECNUM index is an index that contains RECNUM as a segment. Such indexes can be converted in two ways. The conversion process can replace RECNUM by the record identity, or it can create non-unique indexes. Be aware that using non-unique indexes can cause behavior differences between programs working on DataFlex data and ODBC data. The DataFlex
Conver
ting
Data
database does not support non-unique indexes.
11. Select the table character format of the resulting table. Data can be stored in Ansi or OEM format. The first time the option panel is started; the default format will be read from the
DEFAULT_TABLE_CHARACTER_FORMAT setting in the
ODBC_DRV.INT configuration file. For more information on this subject see Chapter 8 - Character formats (OEM or Ansi). For more information on the configuration file see Appendix C – Configuration file
ODBC_DRV.INT.
12. Click the OK button to start the conversion.
CKMgr Conversion
To start the conversion utility type dfruncon ckmgr. After the utility is started, choose the Convert files to ODBC option from the File menu.
Convert to ODBC
After selecting Convert to ODBC, a list of all available tables is displayed. Select the tables you want to convert and click the Convert button. This activates the next dialog where you can specify the conversion options.
Conver
ting
Da
ta
conversion. In the fields enter:
The data source name (DSN) of the data source you created in the ODBC administrator.
The user ID. This is not needed for all target database systems. If you fill in the information here, it will serve as a default when the ODBC driver prompts for the login information. If however you also specify a password the ODBC driver will not prompt for login information and the entered user id and password will be used always. (See discussion of password below).
The password of the user. This is not needed for all target database systems. If you fill in a password, you must also fill in the user identification. It is not recommended to supply the information here. If you do not supply the
password during conversion you will be prompted for the password every time the table(s) you convert is (are) opened.
The schema name within the database where the tables must be placed. This is only required if the database supports schema and you want to use a different schema than the default.
Uncheck “Delete original after conversion” if you want the original table to keep on existing after conversion. If any errors occur during conversion the original table will not be removed regardless of the selection you make here. This option should be unchecked if you want to copy the data to the converted table at a later moment.
Check “Recnum support” if you want the automatic record identity column to be added to the ODBC table. When checked this will add a column called DFRECNUM to the table definition. The new column will be placed in a new index. That index will automatically be set to be the primary index of the table. If you do not check the “Recum support” the table will be converted “as is”. The option is not available when converting from script.
Check the “Convert definition only” checkbox if you do not want to copy the records of the original table over to the new table. This option can also be used to create an empty table in the target environment, manipulate its definition and copy the data at a later time. If you want to copy the data at a later moment you will have to uncheck “Delete original after conversion”. Check the “File DSN” checkbox when the data source to convert to is a file data source.
Check the “Run unattended” checkbox if you do not want errors to popup during conversion. If you select this option, a log file will be created in which results of the conversion including the error messages, if they occur, will be
Conver
ting
Data
written. The log file is called ODBC_DRV.LOG and will be placed in the current working directory. If there is already a file with this name, the log information will be appended to the existing file.
Select the “RECNUM Index conversion” type. A RECNUM index is an index that contains RECNUM as a segment. Such indexes can be converted in two ways. The conversion process can replace RECNUM by the record identity, or it can create non-unique indexes. Be aware that using non-unique indexes can cause behavior differences between programs working on DataFlex data and ODBC data. The DataFlex database does not support non-unique indexes.
Select the table character format of the resulting table. Data can be stored in Ansi or OEM format. The first time the option panel is started; the default format will be read from the DEFAULT_TABLE_CHARACTER_FORMAT setting in the ODBC_DRV.INT configuration file. For more information on this subject see Chapter 8 - Character formats (OEM or Ansi). For more
information on the configuration file see Appendix C – Configuration file ODBC_DRV.INT.
Click the OK button to start the conversion. Source code for CKMGR
If the “Development” and the “Tools & Utilities” option were chosen during install the source code for the CKMGR program will be installed in the src directory of the DataFlex environment. A subdirectory ckmgr has been created that contains all sources needed to compile the CKMGR program.
DataFlex Connectivity Kit for ODBC
5
Connect
ing t
o
Data
Chapter 5 - Connecting to Existing ODBC Data
The DataFlex Connectivity Kit for ODBC is also used for attaching to existing ODBC data. This chapter discusses that option.The connection process consists of three manual steps: 1. Creating an intermediate file.
2. Adding a filelist entry. 3. Generating a FD file.
Alternatively you can use Database Builder to connect to existing ODBC data.
Manual connect
Creating an Intermediate File
To connect to existing ODBC data, you need to create an intermediate file. The intermediate file should at least set the intermediate file keywords DRIVER_NAME and SERVER_NAME. Normally you would also set the DATABASE_NAME keyword. The table to connect to must have at least one unique index defined. This index must have one segment; the segment must be a numeric column.
For example, if we want to connect to the table Department, in the data source Company, we would create an intermediate file, called dept.int, with the following content:
DRIVER_NAME ODBC_DRV SERVER_NAME DSN=Company DATABASE_NAME Department
Creating a Filelist Entry
The next step is to place a reference to the created intermediate file in the filelist.
In a character mode environment • Use DFAdmin (dfruncon DFAdmin) • Select the appropriate filelist
Connect
ing t
o
Da
ta
In VDF• Use Database Builder
• Select the appropriate workspace
• From the “Filelist” menu choose “New entry…”
For the new entry you should set the root name to the name of the
intermediate file you just created (dept.int). The user and logical name can be set to your specifications. Typically, in the above example, they would be set to “Departments in the company” and “dept” respectively.
Generating an FD File
The last step is to generate a FD file for the new filelist entry. The DataFlex compiler uses FD files to import table definitions. In a character mode
environment, use DFAdmin, in VDF use Database Builder to generate the FD.
Connect using Database Builder
Before Database Builder can be used to connect to a data source, the Connectivity Kit needs to be loaded. If you selected to automatically load the driver during install, the driver will be loaded on startup. Otherwise, you can load the driver by choosing the “Load database driver” menu choice in the “Database” menu of Database Builder. From the file dialog that displays, choose the ODBC_DRV.DLL file.
After the driver has been loaded, the “Database” menu will be expanded with extra items, of these new items choose “Connect to ODBC.”
Connect to ODBC
After selecting the “Connect To ODBC Table…” menu item, a list of available data sources is presented. Select a data source by clicking on the item in the list and then clicking on the OK button.
Connect
in
g
Data
You can adjust the contents of the data source list by choosing the type of data sources you want to see. The list can display user- or system data sources. To select a file data source click on the File DSN button and select the data source.
When the data source has been selected a list of available tables in the data source will be presented. It is possible that you need to login to the data source. This depends on the database management system pointed to by the data source. Select a table in the same manner as selecting a data source by clicking on the desired table and then clicking on the OK button.
Connect
ing t
o
Da
ta
After the table has been selected you will see the intermediate file definition screen. This screen will generate an intermediate file for the table you select to connect to. If an intermediate file already exists with the proposed name, its settings will not be read in and the file will be overwritten.
The screen has two tab pages, one for file level intermediate file settings and one for defining indexes.
Connect
in
g
Data
The screen only helps in generating an intermediate file. It will not check if the definition is correct. The indexes defined here do not have to exist in the ODBC table. In general, it will be faster if the indexes actually exist but it is not required. Remember that a primary index is required and this index must have only one segment that is numeric. The primary index segment should
uniquely identify a record.
You can define indexes by selecting the field you want added to the index and then clicking on the Segment button. This will add the field to the list of segments. Segments can be moved by the Up and Down buttons. You can delete a segment by clicking the Delete button.
In this case, you need to find out the definition of the DEPT table from Oracle in order to create the correct intermediate file. The Oracle definition has one unique index for the DEPTNO column. This is also a numeric column so we use it as primary index. The following intermediate file will be generated when you click on the OK button.
DRIVER_NAME ODBC_DRV SERVER_NAME DSN=Oracle DATABASE_NAME DEPT
Connect
ing t
o
Da
ta
PRIMARY_INDEX 1 INDEX_NUMBER 1 INDEX_NUMBER_SEGMENTS 1 INDEX_SEGMENT_FIELD 1DataFlex Connectivity Kit for ODBC
6
Str
uctur
e cache
Chapter 6 – Structure caching
When opening a table through the Connectivity Kit information on the table definition will be assembled. The information will be obtained from the meta database. Since this involves a number of database find operations it may take some time to get all this information. In general table definitions are very static, especially in a deploy environment. For this reason structure caching was implemented. Structure caching will dramatically speed up the opening of tables. It stores the table definition in a disk file (*.cch) and reads this disk file instead of getting the information from the meta database.
Storing information in two places is always dangerous. It is possible for the cache to get “out of sync” with the actual table definition. The structure cache mechanism was designed to prevent getting “out of sync” as much as possible.
Since environments and requirements vary between different installations, structure cache behavior can be setup through the Connectivity Kit configuration file.
What is structure caching
Structure caching will store all information (table, column and index) of a table in a cache file (*.cch) when the table is first opened through the Connectivity Kit. The next time the table is opened, this cache file will be used to get the structure information instead of using the meta database.
Location of cache files
By default the structure cache files will be placed in the same directory as the intermediate file for the table. You can specify a cache path in the
Connectivity Kit configuration file. If this path is defined it must point to a valid directory. All cache files will be placed in that directory.
Cache paths can be used for easy maintenance or when using multiple Connectivity Kits. Other Connectivity Kits also support a structure cache mechanism. In a mixed environment it may be needed to give each Connectivity Kit its own cache directory.
Lifetime of cache files
Cache files can get “out of sync”. There are a number of reasons why this happens. Most of the time the Connectivity Kit will automatically detect the
Str
uctur
e cache
“out of syncness” and regenerate the cache file automatically. The reasons why a cache file will get “out of sync” are:
• The intermediate file changes.
• A restructure operation is done through the DataFlex API
• The table definition changes not using the Connectivity Kit to make the definition change.
• The Connectivity Kit uses a new cache format. Most of the cases above will be handled automatically.
The cache mechanism will compare he timestamps of the cache file and the intermediate file. If the intermediate file is newer then the cache file, the cache file will be regenerated. You can switch off the timestamp check in the
Connectivity Kit configuration file.
When a restructure operation is done, the cache file will be deleted. Upon the next open of the table, the cache file will be regenerated.
Every cache file contains a signature. This signature defines the format of the cache file. When a cache file is opened but the signature does not comply with the expected format, the cache file will be regenerated.
The only situation that is not automatically detected and handled is when table definition changes are made outside the Connectivity Kit. In that case the cache files of the changed tables should be deleted. When the tables are opened the next time, a new cache file will be generated.
It is always allowed to delete cache files, they will be regenerated automatically.
DataFlex Connectivity Kit for ODBC
7
In
te
rme
dia
te
F
ile
Chapter 7 - Intermediate File
A DataFlex driver in general must support certain functionality. Not all of that functionality is available for all target database formats. The intermediate file is the location where this type of information is stored. The contents of an intermediate file can be divided into three sections.
• A section that allows the Data Access API to determine the database driver to use
• A section that contains information needed before a table can be opened • A section that contains information needed after a table has been
opened
The general format of the intermediate file consists of lines of text separated by carriage return and line feed (CR/LF) characters. Each line may be up to 255 characters long and consists of a keyword and value pair or a comment. Keywords may be upper, lower or mixed case. Comments are preceded by the semicolon (;) character. In line comments are not supported. White space, defined as blank spaces and tab characters, is not significant and may be used for legibility.
The specific meaning and usage of intermediate file keywords are entirely dependent upon the specific database driver with the exception of the DRIVER_NAME keyword.
Every table that is accessed through the DataFlex Connectivity Kit for ODBC must have an intermediate file associated with it. The intermediate file specifies the location of the table by specifying a server, a database on the server and the table therein to connect.
Order of Keywords
Intermediate file keywords must be placed in the following order: a header, columns and indexes. The upcoming paragraphs will discuss all supported intermediate file keywords, the value they can be set to and the associated attribute, if any.
In
te
rme
dia
te
F
ile
<Keyword> (<Usage>)
Value <Possible values>
Associated attribute <Attribute_name> (<Type>)
Where
<Keyword> The keyword to set in the intermediate file.
(<Usage>) “Required.” Since most keywords are optional, this is not mentioned in the keyword’s description. It is only mentioned if a keyword is required, all others are optional. Optional keywords can be omitted; required keyword must be set.
<Possible values> A list of values or a description of possible values for the keyword
<Attribute_name> The name of the attribute associated with the keyword.
(<Type>) The type of the associated attribute. Associated Attributes
Some keywords have a so-called associated attribute. The behavior that is setup by such a keyword can also be setup by using the attribute. Attributes can be set by the Set_attribute command; their value can be queried by the Get_attribute command.
Setting up behavior through the intermediate file creates a global, persistent setting. Using the Set_Attribute command will result in a local setting only valid until the next Set_attribute command. It depends on your specific needs, which of the two ways should be used in a specific case.
The Primary_Index_Trigger keyword, for example, has an associated attribute DF_FILE_PRIMARY_INDEX_TRIGGER of type Boolean. There are two ways to switch the attribute on:
Primary_index_trigger YES ; Intermediate file line Set Attribute DF_FILE_PRIMARY_INDEX_TRIGGER of ;
MyTable.File_number To True
Alternatively, you can use the following to switch the attribute off: Primary_index_trigger NO ; Intermediate file line Set Attribute DF_FILE_PRIMARY_INDEX_TRIGGER of ;
In
te
rme
dia
te
F
ile
MyTable.File_number To FalseHeader
The keywords for the intermediate file header must be placed at the beginning of the intermediate file in the order they are discussed here. If an intermediate file does not have this information at the beginning of the file, the file and the associated table will fail to open.
Driver_Name (Required)
Value “ODBC_DRV”
Associated attribute DF_FILE_DRIVER (String)
The Driver_Name keyword is used by the Data Access API to determine the driver that must be used to open the table that is associated with the
intermediate file. The driver will be loaded if necessary. Normally, this
keyword has been parsed before the ODBC Client is called. The ODBC Client will then start parsing the rest of the intermediate file information. This
keyword must be the first keyword in any intermediate file.
Server_Name (Required)
Value Connection string
Associated attribute DF_FILE_LOGIN (String)
The Server_Name keyword must be set to a connection string. The connection string identifies the data source to connect to. The string can contain user information, including password, but that is not required. The string is made up of a number of Keyword=Value pairs separated by semicolons (;).
CONNECTION KEYWORD
DESCRIPTION OF THE VALUE
DSN Name of the data source to connect to.
UID A user id if needed by the data source.
PWD The password corresponding to the
user id name.
Configuration keyword Backend specific defined keyword. For a list of configuration keywords and
In
te
rme
dia
te
F
ile
CONNECTION KEYWORD
DESCRIPTION OF THE VALUE
their description see your database’s manual.
For example, if we want to connect to the table Department, in the data source Company, we would create an intermediate file, called dept.int, with the following content:
DRIVER_NAME ODBC_DRV SERVER_NAME DSN=Company DATABASE_NAME Department
Database_Name
1Value The name of the table to connect to Associated attribute None
The name of the table associated with the intermediate file. If this keyword is not set, the intermediate file name (without the .int extension) will be used.
Schema_Name
Value The name of the database owner
Associated attribute DF_FILE_OWNER (String)
The name of the schema the table belongs to. A schema is a collection of names or objects. A schema can contain tables, views, and triggers. Schemas provide a logical classification of objects in the database. If the keyword is not set, the user ID used to login to the data source is used as the schema name.
1 The Database_name keyword was created and designed before actually
connecting to SQL databases. This is why it has the “old” DataFlex meaning of table. In a future revision this may be adjusted to a more SQL oriented keyword.
In
te
rme
dia
te
F
ile
Table Keywords
The following keywords set attributes on table level. They must be set directly after the intermediate file header and before the column keywords (if any).
Dummy_Update_Column
Desktop databases usually do not support positioned updates. In these cases a different update mechanism, called SQLSetPos that allows for records to be locked is used. If the backend does not support exclusive locks on records, the Connectivity Kit will perform a “dummy update” to lock the record. This mechanism will ensure that records are locked when they are found while in a transaction. Since most ODBC drivers do not support table locking there is a difference between DataFlex locking and ODBC locking.
The dummy update mechanism updates a record directly after it has been found. The record identity column is set to the value just got from the find. This happens in the find logic. If the record identity column is not "updatable", it could be an auto increment for example; you can setup an alternative column by using the intermediate file keyword DUMMY_UPDATE_COLUMN. The Dummy_Update_Column keyword should be set to the number of the column you want to "dummy update". Column numbers start at 1.
A lot of data sources use a so-called optimistic locking strategy. DataFlex programs cannot handle this strategy. Most environments allow the user to change the locking strategy from optimistic to pessimistic. In order for DataFlex programs to correctly lock data, the locking strategy must be set to pessimistic locking!
For Access, one needs to setup pessimistic locking as described in the following article:
http://support.microsoft.com/support/kb/articles/Q225/9/26.ASP. At this moment there is no facility to determine how the backend in use supports locking. There are several ways this can be done depending on the backend in use. Possible behaviors are:
• Positioned updates. If the backend supports positioned updates, locking records is supported, no dummy updates are required.
• SQLSetPos + exclusive lock. Some back ends support the SQLSetPos logic with the possibility to lock a record exclusively. No dummy updates are required.
In
te
rme
dia
te
F
ile
locks on records, a dummy update will be done directly after a record has been found while in a transaction. In this case, the
DUMMY_UPDATE_COLUMN intermediate file setting is used to determine the column to use in the dummy update operation.
• None. Some back ends are read only and do not support locking at all. Tested environments are:
Environment Update behavior DB2 UDB v7.1 Positioned updates MS SQL 7.0 Positioned updates Sybase ASA 7 Positioned updates Oracle 8.0 Positioned updates Oracle 8i Positioned updates
MS Access 2000 SQLSetPos + dummy update
Get_RID_After_Create
Value YES, NO
Associated attribute DF_FILE_GET_RID_AFTER_CREATE (Boolean)
This keyword handles the behavior of the Connectivity Kit when the
Primary_Index_Trigger keyword is set to YES. After records are created in a table with a triggered primary index, the assigned record identity is moved to the record buffer used in DataFlex. Setting this keyword to NO will switch of moving the new identity to the record buffer. This eliminates a client server communication roundtrip, thus speeding up performance when creating records.
Note: Be aware that setting this keyword to NO can result in unwanted or erroneous behavior. Switching off the move behavior can speed up bulk creation of records. This keyword will be rarely used; normally you will only use this attribute from within the program logic where it will be used in massive creating of records. In such cases it will be switched off, then the records are created after which the attribute is switched back on.
In
te
rme
dia
te
F
ile
Max_Rows_Fetched
Value 0 ..Associated attribute DF_FILE_MAX_ROWS_FETCHED (Positive integer)
The number of records result sets of find operation on this table should be limited to. The default value is 0 (zero), which means that result sets will not be limited. All other positive integer values will limit the result set to that value. ODBC uses SQL as its language to manipulate data. SQL is a set oriented language. Every statement works on set(s) and result in a set. A DataFlex find command will be translated into its SQL counterpart, which is sent to the database server. The SQL statement will result in a set of rows that satisfy the condition of the statement for the find command. This result set can have no, one or more rows. In some cases, a result set will contain all rows of a table. Limiting the result set can improve performance. Unfortunately, there is no rule that can be followed when using this attribute. The best setting for the attribute depends on the program logic and may vary in different functional areas in one program. Normally, the best setting to use when experimenting with values for this attribute is 0, 1 and the number of records that are shown on the screen (in lists).
Primary_Index
Value 1.. highest index number defined for the table Associated attribute DF_FILE_RECORD_IDENTITY (Integer) The number of the identity index. The DataFlex API requires every table to have a record identity. This is a column in the table that uniquely identifies a row in the table. This column should be numeric and an index should be defined for it. For more information on record identity, see Chapter 10 -Record Identity.
NOTE: The DF_FILE_RECORD_IDENTITY attribute uses column numbers. The Primary_index intermediate file keyword uses index numbers. When setting up the primary index in the intermediate file the record identity field is set indirectly.
In
te
rme
dia
te
F
ile
Primary_Index_Trigger
Value YES, NOAssociated attribute DF_FILE_PRIMARY_INDEX_TRIGGER (Boolean)
Indicates that the record identity column is filled by the backend automatically when creating records. After records have been created, the Connectivity Kit will move the assigned number to the column by performing a “select (max(recordidentitycolumn)) from table” statement.
The default value for the keyword is NO.
Refind_After_Save
Value YES, NO
Associated attribute DF_FILE_REFIND_AFTER_SAVE (Boolean)
If triggers are defined on a table that fill one (or more) columns in a row that is created or updated, you may use this setting if you want these new column values to be available directly after the save operation. Setting this keyword will ensure that the record buffer is up to date. Setting this keyword to NO, the default, will switch re-fetching the row off. This eliminates a client server communication roundtrip, thus speeding up performance when creating records.
System_File
Value YES, NO
Associated attribute DF_FILE_IS_SYSTEM_FILE (Boolean)
Indicates if the table is a DataFlex system table. System tables in DataFlex are treated in a special way. They should contain one record and one record only. When a system table is opened, the first (and only) row in the table will be placed in the table’s record buffer.
DataFlex determines this attribute by the maximum number of records. In ODBC we cannot set a maximum number of rows. Be aware that the number of records in the table does not need to be one. This is determined by
In
te
rme
dia
te
F
ile
program logic, not by the Connectivity Kit. If there is more than one record, the record that will be placed in the record buffer is the first one according to the record identifier sort order.
Table_Character_Format
Value ANSI, OEM
Associated Attribute DF_FILE_TABLE_CHARACTER_FORMAT (String)
The format of the data in the table. This format can be set to Ansi or to OEM. DataFlex programs use data in OEM format. Most Windows applications expect data to be in Ansi format. If you want to access data from DataFlex and some other Windows tool, it may be required to store the data in Ansi format.
Be aware that setting this value by editing the intermediate file will only change the way data is presented and new data is stored. Existing data will not be converted from OEM to Ansi or vice versa. You need to run a conversion utility to convert existing data.
If you are creating new tables (when converting, by using Database Builder or doing a structure operation) the setting of the driver configuration keyword DEFAUL_TABLE_CHARACTER_FORMAT will be used to determine the initial setting of the attribute.
For more information on character formats see Chapter 8 - Character formats (OEM or Ansi).
Use_Dummy_Zero_Date
Value YES, NO
Associated Attribute DF_FILE_USE_DUMMY_ZERO_DATE (Boolean) Indicates if dummy zero dates must be used for date columns that do not allow null values. The setting has no effect on date columns that allow null values. When set to YES, the Connectivity Kit will translate the DataFlex zero date to the dummy zero date value “0001-01-01”. This avoids problems that can arise with sorting on indexes that use the date columns. For more information see Chapter 11 - NULL Values and Defaults.
In
te
rme
dia
te
F
ile
Column Keywords
The following keywords set attributes on column level. They must be set directly after the table keywords and before the index keywords (if any). Column keywords are grouped per column. If you want to set a column keyword, the column must be identified first (Field_Number), and then you can set one or more column keywords for that particular column. The keywords will apply to the last Field_Number that was specified.
Most of the keywords discussed will be automatically set by the Connectivity Kit and will never be added to the intermediate file. The length of a column for example, is defined in ODBC and normally this is not changed on the
DataFlex side. In some situations, you may want to change the attribute on the DataFlex side only. In those cases, you should use the keywords or associated attributes to setup the desired value.
Field_Index
Value 0 .. highest index number defined for the table
Associated attribute DF_FIELD_INDEX (Integer)
This keyword will set the main index attribute for a column. If this attribute is not set in the intermediate file, the DF_FIELD_INDEX attribute will be set to the first index the column appears in as a segment. See the DataFlex documentation for more information on main indexes.
Field_Length
Value 1 .. Maximum length for the type of the column
Associated attribute DF_FIELD_LENGTH (Integer)
The Connectivity Kit will get the lengths defined in ODBC. If you want to define a different length in DataFlex from the length in ODBC, you should use this setting. It defines the length of the column (together with Field_Precision). This keyword is normally used to make sure that two relating columns have the same length. This can be a problem when relating between tables of different back ends. For example, DataFlex will report the length of a numeric field as a multiple of 2. ODBC supports lengths for numeric data that is not a
In
te
rme
dia
te
F
ile
multiple of 2. If you want to relate between an ODBC numeric column of length 3 and a DataFlex field of length 4, you can adjust the field length of the ODBC column in DataFlex only.
Field_Name
Value The name of the column as reported by DataFlex
Associated attribute DF_FIELD_NAME (String)
The name of a column as reported by the Connectivity Kit. The keyword is generally used to setup overlap column names. It can also be used to setup a different name for a DataFlex environment from the name in ODBC.
There are column names that are legal in the data source but not in DataFlex. If you want to access a table with such a column, use the Field_Name
keyword to set it to a legal DataFlex name. An example of this type of name would be File_Number.
Field_Number
Value 0 .. Maximum number of columns in a table. Associated attribute DF_FIELD_NUMBER (Integer)
The Field_Number keyword defines a column keyword group. All subsequent column keyword settings will apply to the column with the number specified. The column number will change when another Field_Number keyword is used.
It is not possible to separate settings under multiple column groups. Every new column number will overwrite any preexisting settings.
Field_Overlap_Start, Field_Overlap_End
Value 1 .. Number of columns in the table
Associated attribute DF_FIELD_LENGTH (Integer), DF_FIELD_OFFSET (Integer)
The Field_Overlap_Start and Field_Overlap_End keywords are used to define an overlap column. An overlap column is a logical column that “overlaps”
In
te
rme
dia
te
F
ile
multiple underlying real columns. The keywords are set to the numbers of the columns that are overlapped.
If we want to define a column at number 5 called My_Overlap starting at column 3 and ending at column 4 (inclusive) we need to place the following lines in the intermediate file.
Field_Number 5
Field_Name My_Overlap
Field_Overlap_Start 3 Field_Overlap_End 4
The lines above will insert a column in the table definition. Column number 5 is a logical column; the original column number 5 and higher will shift one position. Overlaps defined in this way always overlap complete columns. Please note that the overlap column will be inserted when the column
definition is finished at a new field group header, an index group header or the end of the intermediate file. Therefore, the following intermediate file definition will generate two subsequent overlap columns at position 5 and 6. The original column number 5 and higher will have shifted two positions.
Field_Number 5 Field_Name Eventually_Field_6 Field_Overlap_Start 2 Field_Overlap_End 3 Field_Number 5 Field_Name Eventually_Field_5 Field_Overlap_Start 1 Field_Overlap_End 3
Field_Overlap_Offset_Start, Field_Overlap_Offset_End
Value 1 .. Length of a row in the tableAssociated attribute DF_FIELD_LENGTH (Integer), DF_FIELD_OFFSET (Integer)
The Field_Overlap_Offset_Start and Field_Overlap_Offset_End keywords are used to define an overlap column. An overlap column is a logical column that “overlaps” multiple underlying real columns. The keywords are set to the offset in the row of the columns that are overlapped. These keywords can be used to create so-called “underlaps.”
In
te
rme
dia
te
F
ile
operations. If you perform a restructure operation on a table with “underlaps,” they will be forced to complete overlap by the Structure_End logic.
Please note that every backend has its own rules for offsets and field lengths. Using the same settings in DataFlex and ODBC can result in different
overlaps. Be sure to check if the overlap definition is correct.
See the discussion on the Field_Overlap_Start and Field_Overlap_End keywords for more information on inserting overlaps and the consequences for other column’s numbers.
Field_Precision
Value 1 .. Maximum precision for the type of the column
Associated attribute DF_FIELD_PRECISION (Integer)
The Connectivity Kit will get the precision defined in ODBC. If you want to define a different precision in DataFlex from the precision in ODBC, you should use this setting. It defines the length of the column (together with Field_Length).
This keyword is normally used to make sure that two relating columns have the same length. This can be a problem when relating between tables of different back ends. For example, DataFlex will report the length of a numeric field as a multiple of 2. ODBC supports lengths for numeric data that is not a multiple of 2. If you want to relate between an ODBC numeric column of length 3 and a DataFlex field of length 4, you can adjust the field length of the ODBC column in DataFlex only.
Field_Related_File, Field_Related_Field
Value 1 .. Maximum number of tables supported by DataFlex and 1.. Number of columns in the “relates to” table
Associated attribute DF_FIELD_RELATED_FILE (Integer), DF_FIELD_RELATED_FIELD (Integer) DataFlex relations are setup through the DataFlex filelist. In the filelist, all tables are assigned a number. This number is used within DataFlex and the DataFlex API to identify the table; columns are also identified by their number.
