NULL Values and Defaults

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

TTRIBUTE

I

DENTIFICATION

S

HORT DESCRIPTION

DRVR_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 // ShowDSNs

This 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. Use ODBC_DRV

Procedure ShowTables String DSNName Local String TableName

Local Integer Count Local Integer NumTables

ODBCEnumerateTables DSNName To NumTables For Count From 1 To NumTables

ODBCTableName Count To TableName Showln “Table “ Count “: “TableName Loop

End_Procedure // ShowTables

This command will enumerate the tables in the specified data source. The names of the tables are stored in memory for further use. The names can be queried by the ODBCTableName command, the schema can be queried by the ODBCSchemaName command.

ODBCFieldName Get the name of the field. Use ODBC_DRV

Procedure ShowFields String DSNName String TableName Local String FieldName

Local Integer Count Local Integer NumFields

Commands

and Techni

que

s

ODBCEnumerateFields DSNName TableName To NumFields For Count From 1 To NumFields

ODBCFieldName Count To FieldName Showln “Field “ Count “: “FieldName Loop

End_Procedure // ShowFields

This command will get the field name of the table. This command must be preceded by an ODBCEnumerateFields command. The enumerate command will store the field names in memory for further use.

ODBCManager

This command has been deprecated. It is replaced by the ODBCAdministrator command. See the description of the ODBCAdministrator command for details.

ODBCSchemaName

Get the schema name of the specified table. Use ODBC_DRV

Procedure ShowSchemas String DSNName Local String SchemaName

Local Integer Count Local Integer NumTables

ODBCEnumerateTables DSNName To NumTables For Count From 1 To NumTables

ODBCSchemaName Count To SchemaName Showln “Schema “ Count “: “SchemaName Loop

End_Procedure // ShowSchemas

This command will get the schema name of a table. This command must be preceded by an ODBCEnumerateTables command. The enumerate

command will store the schema names in memory for further use. ODBCTableName

Get the name of the specified table. Use ODBC_DRV

Commands

and Techni

que

s

Procedure ShowTables String DSNName Local String TableName

Local Integer Count Local Integer NumTables

ODBCEnumerateTables DSNName To NumTables For Count From 1 To NumTables

ODBCTableName Count To TableName Showln “Table “ Count “: “TableName Loop

End_Procedure // ShowTables

This command will get the table name. This command must be preceded by an ODBCEnumerateTables command. The enumerate command will store the table names in memory for further use.

DataFlex Connectivity Kit for ODBC

10

Recor

d

Id

en

tity

Chapter 10 - Record Identity

The Data Access Database API allows DataFlex programs to use non- DataFlex databases. In order for the API to function, it imposes some

requirements on the structure of the data it can connect to. The API demands that every table that it connects to contain a so-called record identity column. If a table fails to have a record identity, the API will not be able to connect to that table.

What is a Record Identity?

A record identity is required for historic reasons. A short introduction on record numbers explains that history.

DataFlex Record Numbers

The DataFlex database stores its information in disk files. Every table is stored in several disk files. One of the files contains the actual data, the other files contain the indexes, a DataFlex compiler include file (so the table can be used in programs) and a column name file.

DataFlex data is accessed according to a defined sort order called an index. One order always exists and that is the “storage order.” This order can be used from within a DataFlex environment and is referred to as the record number (RECNUM) order. The record number is a consecutive positive number starting at 1 that indicates a record’s relative position in the table. It can be used to search the table for a specific record.

Since record numbers indicate a record’s relative position in the table, it can be used in conjunction with the record size to determine the records position in the disk file. This involves minimal disk access and results in a fast operation.

The record number can be used within the DataFlex environment as if it were a field in the table. In reality, it is a logical column; no disk space is allocated to store record numbers. Record numbers are assigned when creating records. DataFlex programmers cannot control the record number for a new record.

It has been strongly discouraged by Data Access to use record numbers as meaningful information. Older DataFlex systems used to misuse the logical column as customer number or order number.

Recor

d

Id

en

tity

DataFlex record numbers have the following characteristics: • Uniquely identify a row

• Automatically available • Automatically maintained • Unchangeable for a given row • Reusable

• Offers faster access than other indexes

• Sequential consecutive number ranging from 1 to 16 million

The DataFlex runtime and packages rely on the existence of record numbers. The most common way to keep track of the current record is to store the record number in some type of integer variable (be it a property, local or

In document DataFlex Connectivity Kit For ODBC User's Guide. Version 2.2 (Page 60-89)