The DataFlex Connectivity Kit for ODBC contains a package called ODBC_DRV.PKG. The package contains ODBC specific definitions and commands. You can use these in DataFlex source code to make your application more ODBC specific. This might make the program unusable for other database types.
Next to the commands defined in ODBC_DRV.PKG, you can use Embedded SQL. How to use Embedded SQL is described in the Embedded SQL User’s Guide.
Attributes
The DataFlex API defines a number of attributes that give information about a table and its definition. Next to those attributes, some ODBC specific
attributes have been added. Attributes that concern the Connectivity Kit can be defined on table, column and index level.
Most attributes have the same behavior as described in the DataFlex documentation. We will discuss the attributes that differ from the standard DataFlex behavior or are defined specifically for ODBC. For more information on other attributes, refer to the DataFlex documentation.
Most attributes defined for ODBC specifically can also be set through intermediate file keywords. Refer to Chapter 7 – Intermediate File, for a description of those attributes.
ATTRIBUTE
INTERMEDIATE FILE
KEYWORD
DF_FILE_GET_RID_AFTER_CREATE Get_RID_After_Create
DF_FILE_MAX_ROWS FETCHED Max_Rows_Fetched
DF_FILE_PRIMARY_INDEX_TRIGGER Primary_Index_Trigger DF_FILE_REFIND_AFTER_SAVE Refind_After_Save DF_FILE_TABLE_CHARACTER_FORMAT Table_Charcater_Format DF_FILE_USE_DUMMY_ZERO_DATE Use_Dummy_Zero_Date DF_FIELD_DEFAULT_VALUE
Commands
and Techni
que
s
ATTRIBUTE
INTERMEDIATE FILE
KEYWORD
DF_FIELD_IS_NULL DF_FIELD_NULL_ALLOWED DF_FIELD_STORE_TIME Field_Store_Time DF_INDEX_NAME Index_Name Attribute usageThe attributes that are set for the Connectivity Kit are attributes on table-, column-, index- and index segment level.
Structure versus non-structure
Most of the extra attributes can be set within or outside of a structure operation. If set within a structure operation (within a Structure_Start .. Structure_End command pair), the setting is permanent. The intermediate file will be adjusted accordingly. If set outside of a structure operation, the setting will exist for the duration of the program or until it is reset.
The DF_FILE_USE_DUMMY_ZERO_DATE, DF_FIELD_NULL_ALLOWED, DF_FIELD_DEFAULT_VALUE and DF_INDEX_NAME attribute can only be set inside a structure operation. The DF_FIELD_IS_NULL attribute can only be set outside a structure operation. All others mentioned above can be set in- or outside of a structure operation.
DF_FILE_GET_RID_AFTER_CREATE (Boolean)
This attribute can also be set through the intermediate file keyword Get_RID_After_Create. Refer to Chapter 7 - Intermediate File for more information.
DF_FILE_MAX_ROWS_FETCHED (Integer)
This attribute can also be set through the intermediate file keyword Max_Rows_Fetched. Refer to Chapter 7 - Intermediate File for more information.
Commands
and Techni
que
s
DF_FILE_PRIMARY_INDEX_TRIGGER (Boolean)
This attribute can also be set through the intermediate file keyword Primary_Index_Trigger. Refer to Chapter 7 - Intermediate File for more information.
DF_FILE_REFIND_AFTER_SAVE (Boolean)
This attribute can also be set through the intermediate file keyword Refind_After_Save. Refer to Chapter 7 - Intermediate File for more information.
DF_FILE_TABLE_CHARACTER_FORMAT (String)
This attribute can also be set through the intermediate file keyword Table_Character_Format. Refer to Chapter 7 - Intermediate File for more information.
DF_FILE_USE_DUMMY_ZERO_DATE (Boolean)
This attribute can also be set through the intermediate file keyword Use_Dummy_Zero_Date. Refer to Chapter 7 - Intermediate File for more information.
DF_FIELD_DEFAULT_VALUE (String)
This attribute sets up the database default value for a column. You can setup data source defaults on a column in three different formats: literal, ODBC escape sequence or backend string.
A literal default is set by using a string in single quotes. It should be a valid literal value for the SQL type of the column. An ODBC escape sequence is enclosed in curly brackets {}, finally a backend string is enclosed in square brackets [].
Typical examples of setting default values are:
Set_Attribute DF_FIELD_DEFAULT_VALUE iMyFile iMyCharField ; To “‘Unknown’”
Set_Attribute DF_FIELD_DEFAULT_VALUE iMyFile iMyDateField ; To “{fn current_date()}”
Set_attribute DF_FIELD_DEFAULT_VALUE iMyFile iMyCharField ; To “[convert(char(30), CURRENT_USER)]”
Commands
and Techni
que
s
The default will be used when creating records. So if we were to have a table MyTable with columns A, B, C and use the code below, we would end up with a new row in MyTable having default values (if any) for columns B and C. Clear MyTable
Lock
Move “Not a default value” To MyTable.A Saverecord MyTable
Unlock
See Chapter 11 - NULL Values and Defaults and your database’s documentation for more information.
DF_FIELD_IS_NULL (Boolean)
This attribute indicates if a column has the “null value”. The attribute is true when a field has the null value and false otherwise.
A null value is a special marker that is used when the value of a column is unknown. You can set this attribute to force a column to have the null value. See Chapter 11 - NULL Values and Defaults and your database’s
documentation for more information.
DF_FIELD_NULL_ALLOWED (Boolean)
This attribute indicates if a column allows “null values” to be stored. The attribute is true when a column allows null values and false otherwise. A null value is a special marker that is used when the value of a column is unknown. See Chapter 11 - NULL Values and Defaults and your database’s documentation for more information.
DF_FIELD_STORE_TIME (Integer)
This attribute can also be set through the intermediate file keyword
Field_Store_Time. Refer to Chapter 7 - Intermediate File for more information. DF_INDEX_NAME (String)
This attribute can also be set through the intermediate file keyword Index_Name. Refer to Chapter 7 - Intermediate File for more information.
Commands
and Techni
que
s
Commands
The following commands have been added.
CLI_Set_Driver_Attribute / CLI_Get_Driver_Attribute
The CLI_Set- and CLI_Get_Driver_Attribute commands can be used to set and get API driver level attributes. The API driver attributes can also be set through the Connectivity Kit’s configuration file. The commands allow a DataFlex programmer more control over the driver level attributes. Setting the attributes will not change the Connectivity Kit configuration file.
The commands have the same syntax:
CLI_Set_Driver_Attribute <DriverId> <AttrId> To <sVar>
CLI_Get_Driver_Attribute <DriverId> <AttrId> To <sVar>
Where : <Driver_id>
Is the identification of the driver. <AttrId>
Is the attribute identification. Attribute identification can be:
A
TTRIBUTEI
DENTIFICATIONS
HORT DESCRIPTIONDRVR_DEFAULT_NULLABLE_ASCII Boolean that indicates if ASCII columns allow NULL values when new columns are created. See also the
DF_FIELD_NULL_ALLOWED field attribute.
DRVR_DEFAULT_NULLABLE_NUMERIC Boolean that indicates if Numeric columns allow NULL values when new columns are created. See also the
DF_FIELD_NULL_ALLOWED field attribute.
DRVR_DEFAULT_NULLABLE_DATE Boolean that indicates if Date columns allow NULL values when new columns are created. See also the
Commands
and Techni
que
s
DF_FIELD_NULL_ALLOWED field attribute.DRVR_DEFAULT_NULLABLE_TEXT Boolean that indicates if Text columns allow NULL values when new columns are created. See also the
DF_FIELD_NULL_ALLOWED field attribute.
DRVR_DEFAULT_NULLABLE_BINARY Boolean that indicates if Binary columns allow NULL values when new columns are created. See also the
DF_FIELD_NULL_ALLOWED field attribute.
DRVR_DEFAULT_DEFAULT_ASCII String that stores the default value of ASCII columns when new columns are created. See also the
DF_FIELD_DEFAULT_VALUE attribute.
DRVR_DEFAULT_DEFAULT_NUMERIC String that stores the default value of Numeric columns when new columns are created. See also the
DF_FIELD_DEFAULT_VALUE attribute.
DRVR_DEFAULT_DEFAULT_DATE String that stores the default value of Date columns when new columns are created. See also the
DF_FIELD_DEFAULT_VALUE attribute.
DRVR_DEFAULT_DEFAULT_TEXT String that stores the default value of Text columns when new columns are created. See also the
DF_FIELD_DEFAULT_VALUE attribute.
Commands
and Techni
que
s
DRVR_DEFAULT_DEFAULT_BINARY String that stores the default value of Binary columns when new columns are created. See also the
DF_FIELD_DEFAULT_VALUE attribute.
DRVR_MAX_ACTIVE_STATEMENTS Integer that holds the maximum number of statements allowed for a connection.
DRVR_ERROR_DEBUG_MODE Boolean that indicates if the
Connectivity Kit should popup errors in a message box before passing them to DataFlex.
DRVR_DRIVER_DECIMAL_SEPARATOR The decimal separator used by the ODBC driver in use. DRVR_DRIVER_THOUSANDS_SEPARATOR The thousands separator used
by the ODBC driver in use.
DRVR_DRIVER_DATE_FORMAT The date format used by the
ODBC driver in use. DRVR_DRIVER_DATE_SEPARATOR The date separator used by
the ODBC driver in use.
DRVR_USE_CACHE Boolean that indicates if
structure caching should be used.
DRVR_REPORT_CACHE_ERRORS Boolean that indicates if read errors on structure cache files should be reported.
DRVR_CACHE_PATH The path where structure
cache files are stored.
DRVR_USE_CACHE_EXPIRATION Boolean that indicates if cache expiration must be checked. DRVR_DEFAULT_TABLE_CHARACTER_FORMAT String that stores the default
table character format.
Commands
and Techni
que
s
zero date. This attribute should only be set if the lowest possible date of the database in use is not 0001-01-01. It should be set to the lowest possible date of the database in use.
DRVR_DEFAULT_USE_DUMMY_ZERO_DATE The default for the table attribute to use dummy zero dates. This default is used when creating new tables. For more information on API driver level attributes see Appendix C –
Configuration file ODBC_DRV.INT. ODBC_SetConstraint
This new command was added to give the programmer more control over the Select statement generated by the driver. It will effectively turn off the driver logic, that generates the where clause of a select statement, and replace that with what is passed in the command. Since you are overwriting internal logic, this command has the potential to generate unexpected results when
misused. That is why the command must be used in one defined way. You must setup the clause, find records in the result set by the index that you want the result ordered by in forward or backward direction (GT, LT), and remove the clause.
For example, if we want to use this functionality in the Order Entry sample of VDF to find all order lines for the MODEMS item, we could do this the following way:
Use ODBC_DRV String Clause
Move “ITEM_ID = ‘MODEMS’” To Clause
ODBC_SetConstraint OrderDtl.File_number Clause Repeat
Find Gt OrderDtl By Index.1
[Found] Showln “Order detail for a modem is: “ ;
OrderDtl.Order_Number “, “ OrderDtl.Detail_number Until [Not Found]
Commands
and Techni
que
s
This feature can speed up reporting in particular. Sometimes looking for related information is more efficient if the where clause is replaced by a “ChildColumn = ParentValue” construction. The select statements generated by the driver will select the desired record and all that follow according to the index used. By giving it a specific where clause having specific knowledge about the database, you are able to limit the size of the result sets and thereby increase performance.
The ODBC_SetConstraint command will reset the
DF_FILE_MAX_ROWS_FETCHED attribute when issued. If the command is issued with a clause, the attribute will be set to zero (0). If it is called with no clause (an empty string), the attribute will be reset to its original value. Please note that you need to use SQL syntax in the where clause that you specify. If you want to select on dates you must use the YYYY-MM-DD format that SQL uses. Normally, this is easiest accomplished by:
Integer OrgFmt OrgSep
Get_attribute DF_DATE_FORMAT To OrgFmt Get_attribute DF_DATE_SEPARATOR To OrgSep
Set_attribute DF_DATE_FORMAT To DF_DATE_MILITARY Set_attribute DF_DATE_SEPARATOR To (ASCII("-"))
ODBC_SetConstraint MyFile.File_number (MYDATECOLUMN = '” + string(MyDate) + "'")
Set_attribute DF_DATE_FORMAT To OrgFmt Set_attribute DF_DATE_SEPARATOR To OrgSep
Also note that string constants are placed in single quotes (‘).
Of Special Note
• If this command is used on a table you cannot use other finds on that table until ending the ODBC_SetConstraint since, as long as the constraint is in force it will overwrite the find logic of the driver. If, before ending ODBC_SetConstraint, we changed the example above to find on OrderDtl by some other index or in a different direction (not gt) on the same index, the result would be unpredictable.
Use ODBC_DRV String Clause
Commands
and Techni
que
s
ODBC_SetConstraint OrderDtl.File_number Clause Repeat
Find Gt OrderDtl By Index.1
[Found] Showln “Order detail for a modem is: “ ; OrderDtl.Order_Number “, “ OrderDtl.Detail_number
Find Lt OrderDtl By Index.1 // The result of this command is unpredictable
Until [Not Found]
ODBC_SetConstraint OrderDtl.File_number “”
• You cannot change the buffer between finds to re-seed the find logic. Doing that will result in the first record being found again. If we adjust the previous code to the example below, the repeat loop will turn into an endless loop since you are finding the same record repeatedly. Use ODBC_DRV
String Clause
Move “ITEM_ID = ‘MODEMS’” To Clause
ODBC_SetConstraint OrderDtl.File_number Clause Repeat
Find Gt OrderDtl By Index.1
[Found] Showln “Order detail for a modem is: “ ; OrderDtl.Order_Number “, “ OrderDtl.Detail_number
Move SomeValue To OrderDtl.Order_number Until [Not Found]
ODBC_SetConstraint OrderDtl.File_number “”
• You can use find, save and delete commands on other tables where no ODBC_SetConstraint is in force. You could, for example, expand the code above and show the customer names for the customers that bought modems. To achieve this you would need to find an OrderHeader and a Customer record.
• You can end looping through an ODBC_SetContraint set anytime you like. If we wanted to find only two records in the sample above we could add a counter, increment it for every found record and break the loop when the counter is two.
Use ODBC_DRV String Clause Integer Counter
Commands
and Techni
que
s
Move 0 To Counter
Move “ITEM_ID = ‘MODEMS’” To Clause
ODBC_SetConstraint OrderDtl.File_number Clause Repeat
Find Gt OrderDtl By Index.1
[Found] Showln “Order detail for a modem is: “ ; OrderDtl.Order_Number “, “ OrderDtl.Detail_number
Increment Counter If (Counter >= 2) Break Until [Not Found]
ODBC_SetConstraint OrderDtl.File_number “”
• You must use SQL syntax in the string that is passed. Familiarize yourself with SQL; there are many good books available.
• If you want to write portable applications that support multiple database drivers, you can use the general form of the ODBC_SetConstraint command, CLI_SetConstraint. CLI_SetConstraint performs the same functionality. In fact, it is called from the ODBC_SetConstraint command. The CLI_SetConstraint command works exactly as described for
ODBC_SetConstraint. It gets an extra parameter, the identification of the driver that you want to set the constraint for. If we changed the code above to be more generic, it would look like the sample below. Use ODBC_DRV
String Clause String DriverId
Move ODBC_DRV_ID To DriverId
Move “ITEM_ID = ‘MODEMS’” To Clause
CLI_SetConstraint OrderDtl.File_number Clause DriverId Repeat
Find Gt OrderDtl By Index.1
[Found] Showln “Order detail for a modem is: “ ; OrderDtl.Order_Number “, “ OrderDtl.Detail_number
Until [Not Found]
CLI_SetConstraint OrderDtl.File_number “” DriverId
Disclaimer
Data Access has done a lot of research into performance issues and design differences between the native DataFlex database and an ODBC Data
Commands
and Techni
que
s
Source. We found that the ODBC_SetConstraint or CLI_SetConstraint command can speed up a connection to an ODBC Data Source considerably. While we are looking into ways to improve internal driver logic to get the kind of result these commands can give, this is not a trivial task and outcome of further research is uncertain. In the meantime, Data Access wants to offer this solution to the DataFlex community.
You should be aware that the misuse of these commands could give
unexpected results. It is the developers responsibility to verify that the results are as expected. All risks associated with the use of these commands are the developer’s; Data Access Corporation disclaims any and all liability with respect to the use of the ODBC_SetConstraint or CLI_SetConstraint Command.
ODBCAdministrator
This command will start the ODBC administrator. Use ODBC_DRV
Procedure StartODBCAdministrator Local Integer WndHandle
Get Window_Handle To WndHandle ODBCAdministrator WndHandle
End_Procedure // StartODBCAdministrator
This command will start the ODBC Administator. The windows handle that is passed must be a valid window handle.
ODBCDSNName Get a data source name. Use ODBC_DRV
Procedure ShowDSNs Local String DSNName Local Integer Count Local Integer NumDSN
ODBCEnumerateDataSources NumDSN For Count From 1 To NumDSN
ODBCDSNName Count To DSNName
Commands
and Techni
que
s
Loop End_Procedure // ShowDSNsThis command will get the Data Source name as specified in the ODBCAdministrator. This command must be preceded by an
ODBCEnumerateDataSources command. The enumerate command will store the data source names in memory for further use.
ODBCEnumerateDataSources
Get the number of data sources and store the data source names in memory for further use.
Use ODBC_DRV Procedure ShowDSNs
Local String DSNName Local Integer Count Local Integer NumDSN
ODBCEnumerateDataSources NumDSN For Count From 1 To NumDSN
ODBCDSNName Count To DSNName
Showln “Data source “ Count “: “ DSNName Loop
End_Procedure // ShowDSNs
This command will enumerate the data sources as specified in the ODBC manager. The names of the data sources are stored in memory for further use. The names can be queried by the ODBCDSNName command.
ODBCEnumerateFields
Get the number of fields of the specified table and store the field names in memory for further use.
Use ODBC_DRV
Procedure ShowFields String DSNName String TableName Local String FieldName
Local Integer Count Local Integer NumFields
Commands
and Techni
que
s
For Count From 1 To NumFields ODBCFieldName Count To FieldName Showln “Field “ Count “: “FieldName Loop
End_Procedure // ShowFields
This command will enumerate the fields in the specified table. The names of the fields are stored in memory for further use. The names can be queried by the ODBCFieldName command.
ODBCEnumerateTables
Get the number of tables of the specified data source and store the table names and the schema the tables belong to in memory for further use.