Data Access for ISAM User Manual - Technical Reference

Technical Reference for Easysoft Data Access for ISAM

This appendix documents the ODBC and SQL conformance in Easysoft Data Access for ISAM, supported ISAM data types and transactions when using D-ISAM data.

Appendix Guide

Conformance

This section specifies the level of SQL and ODBC conformance in Easysoft Data Access for ISAM.

API Support

Easysoft Data Access for ISAM provides the following API Entry points:

Function ODBC Conformance
SQLAllocHandle Core
SQLBindCol Core
SQLBindParameter Core
SQLBrowseConnect Level 1
SQLCancel Core
SQLCloseCursor Core
SQLColAttribute Core
SQLColumnPrivileges Level 2
SQLColumns Core
SQLConnect Core
SQLDescribeCol Core
SQLDescribeParam Level 2
SQLDisconnect Core
SQLDriverConnect Core
SQLEndTran Core
SQLExecDirect Core
SQLExecute Core
SQLFetch Core
SQLFetchScroll Core
SQLForeignKeys Level 2
SQLFreeHandle Core
SQLFreeStmt Core
SQLGetConnectAttr Core
SQLGetCursorName Core
SQLGetData Core
SQLGetDescField Core
SQLGetDescRec Core
SQLGetDiagField Core
SQLGetDiagRec Core
SQLGetEnvAttr Core
SQLGetFunctions Core
SQLGetInfo Core
SQLGetStmtAttr Core
SQLGetTypeInfo Core
SQLMoreResults Level 1
SQLNativeSql Core
SQLNumParams Core
SQLNumResultCols Core
SQLParamData Core
SQLPrepare Core
SQLPrimaryKeys Level 1
SQLProcedureColumns Level 1
SQLProcedures Level 1
SQLPutData Core
SQLRowCount Core
SQLSetConnectAttr Core
SQLSetCursorName Core
SQLSetDescField Core
SQLSetDescRec Core
SQLSetEnvAttr Core
SQLSetPos Level 1
SQLSetStmtAttr Core
SQLSpecialColumns Core
SQLStatistics Core
SQLTablePrivileges Level 2
SQLTables Core

Figure 32: Easysoft Data Access for ISAM API Entry Points

The following functions are provided by the ODBC Driver Manager:

Function ODBC Conformance
SQLDataSources Core
SQLDrivers Core
SQLAllocConnect Core
SQLAllocEnv Core
SQLError Core
SQLGetConnectOption Core
SQLSetParam Core

Figure 33: Easysoft Data Access for ISAM ODBC Driver Manager functions

The following functions are provided by the Setup DLL:

Function ODBC Conformance
SQLConfigDriver Core
SQLConfigDSN Core

Figure 34: Easysoft Data Access for ISAM Setup DLL functions

The following functions are currently not supported, but are planned for future release:

Function ODBC Conformance
SQLBulkOperations Level 1
SQLCopyDesc Core

Figure 35: Easysoft Data Access for ISAM unsupported functions

Statement types

Easysoft Data Access for ISAM supports the following statements:

Unions

Easysoft Data Access for ISAM supports UNION and UNION ALL.

Table reference

The Table reference list in a select can contain all or any of:

Joins

Easysoft Data Access for ISAM supports the following types of joins:

The joining condition may be specified with the ON or USING clause. Both the left and right source can be a table name, sub query or another join. Joins can be nested with no restriction on depth.

Joins can be specified in both SQL92 and ODBC format:

SQL

SELECT * from x LEFT OUTER JOIN y ON x.a = y.a

ODBC

SELECT * from {oj x LEFT OUTER y ON x.a = y.a}

Predicates

Easysoft Data Access for ISAM supports the following predicates:

The sub queries in predicates can be correlated or non correlated:

Correlated

SELECT a FROM b WHERE c = ALL ( SELECT x FROM y WHERE z = a )

Non-correlated

SELECT a FROM b WHERE c = ALL ( SELECT x FROM y WHERE z = 12 )

Scalar Functions

Easysoft Data Access for ISAM provides all the functions required by ODBC and also functions from SQL92. Functions can be specified in SQL92 or ODBC format:

SQL

SELECT CURRENT_DATE, EXTRACT( YEAR FROM Employee.data_of_birth ) FROM Employee

ODBC

SELECT {fn CURRENT_DATE()}, {fn EXTRACT( YEAR FROM Employee.data_of_birth )} FROM Employee

The following is a complete set of supported functions:

Pseudo Variable Functions
SQL92 Functions
ODBC Functions

Aggregate Functions

Easysoft Data Access for ISAM supports the following aggregate functions:

Conversion functions

Easysoft Data Access for ISAM supports both the SQL92 CAST function and the ODBC CONVERT FUNCTION for conversion between compatible data types.

Conditional Functions

Easysoft Data Access for ISAM supports CASE statements and the shorthand forms NULLIF and COALESCE.

Literals

All SQL92 and ODBC32 literals are supported and can be specified in either form:

SQL92

DATE '1999-01-02', INTERVAL '10-2' YEAR TO MONTH

ODBC

{d '1999-01-02'}, {INTERVAL '10-2' YEAR TO MONTH}

Optimization

Easysoft Data Access for ISAM performs several optimizations to improve performance including the following:

Query optimization

The WHERE clause of a query will be rewritten into a form that allows more efficient processing of data.

For example the query:

SELECT * FROM x WHERE (a = 10 or b = 20) and c = 30

will be changed into the equivalent:

SELECT * FROM x WHERE a = 10 and c = 30

UNION

SELECT * FROM x WHERE b = 20 and c = 30

Table Optimization

In cases where indexes are present on tables, Easysoft Data Access for ISAM will, if necessary, rearrange the order in which tables are processed to enable the index to be used, which can lead to considerable increases in performance.

For example, consider the following query where tables a, b and c each have 800 rows and an index on catalog_number.

SELECT * FROM a, b, c

WHERE a.catalog_number = c.catalog_number

AND b.catalog_number = a.catalog_number

AND a."desc" = b."desc"

AND c.retail = a.retail

AND a.catalog_number = b.catalog_number

When run with the table order specified the query takes about 350 seconds on a given test configuration. If table optimization is enabled then, on the same machine, the query takes under 2 seconds.

The effect of this optimization is most noticeable on some of the queries that comprise the TPC-D benchmark set. Without this optimization some of the queries can be considered to never end (still running after a day), with optimization the same query will return in under 10 seconds.

Informational Schema

Easysoft Data Access for ISAM provides an informational schema view of the tables supplied by the target data sources. The following tables are available:

Data Types

Easysoft Data Access for ISAM supports the following SQL data types:

ODBC Features

Cursors

Easysoft Data Access for ISAM provides FORWARD ONLY, STATIC and KEYSET CURSORS.

It also provides the following additional ODBC features (reported via the SQLGetIinfo API call):

Asynchronous operation is supported, both at the statement and connection level (SQL_AM_STATEMENT)

Column alias is supported using the optional AS clause

Correlation names are supported and can be any valid user-defined name

All SQL92 Datetime literals are supported

SQLGetData can be called for any column, bound or unbound

The columns in the GROUP BY clause and the select list are not related (SQL_GB_NO_RELATION)

All keywords are supported

ISAM to SQL data type conversions

The following table lists the ISAM data types and edit masks which are supported by Easysoft Data Access for ISAM, and the SQL data types to which the data is converted.

Edit Mask SQL data type
CHAR Non-terminated Character string padded with trailing spaces
DEC Byte designs packed decimal
DOUBLE Platform specific double
FLOAT Platform specific float
INT 2 byte big endian integer
LONG 4 byte big endian integer
MINT 2 byte platform specific integer
MLONG 4 byte platform specific integer
STRING Character string trimmed and padded with 0x00

Figure 36: Easysoft Data Access for ISAM and ISAM data types

Easysoft Data Access for ISAM data types



Group Name Description SQL Group
SQL92 BIGINT Exact numeric value with precision 19 number
SQL92 BINARY Binary data of fixed length with a maximum length 255 binary
SQL92 BIT Single bit binary data number
SQL92 CHAR Character string of fixed string length with a maximum length 255 string
SQL92 DATE Year, month, and day fields, conforming to the rules of the Gregorian calendar datetime
SQL92 DECIMAL Signed, exact, numeric value with a precision and scale number
SQL92 DOUBLE Signed, approximate, numeric value with a binary precision 53 number
SQL92 FLOAT Signed, approximate, numeric value number
SQL92 INTEGER Exact numeric value with precision 10 and scale 0 number
SQL92 INTERVAL_DAY Number of days between two dates interval
SQL92 INTERVAL_DAY_TO_HOUR Number of days/hours between two date/times interval
SQL92 INTERVAL_DAY_TO_MINUTE Number of days/hours/minutes between two date/times interval
SQL92 INTERVAL_DAY_TO_SECOND Number of days/hours/minutes/seconds between two date/times interval
SQL92 INTERVAL_HOUR Number of hours between two dates/times interval
SQL92 INTERVAL_HOUR_TO_MINUTE Number of hours/minutes between two dates/times interval
SQL92 INTERVAL_HOUR_TO_SECOND Number of hours/minutes/seconds between two dates/times interval
SQL92 INTERVAL_MINUTE Number of minutes between two dates/times interval
SQL92 INTERVAL_MINUTE_TO_SECOND Number of minutes/seconds between two dates/times interval
SQL92 INTERVAL_MONTH Number of months between two dates interval
SQL92 INTERVAL_SECOND Number of seconds between two dates/times interval
SQL92 INTERVAL_YEAR Number of years between two dates interval
SQL92 INTERVAL_YEAR_TO_MONTH Number of years and months between two dates interval
SQL92 INTEGER AUTO Exact numeric value with precision 10 auto incrementing number
SQL92 LONG VARBINARY Variable length binary data binary
SQL92 LONG VARCHAR Variable length character data string
SQL92 NUMERIC Signed, exact, numeric value with a precision and scale number
SQL92 REAL Signed, approximate, numeric value with a binary precision 24 number
SQL92 SMALLINT Exact numeric value with precision 5 and scale 0 number
SQL92 TIME Hour, minute, and second fields datetime
SQL92 TIMESTAMP Year, month, day, hour, minute, and second fields datetime
SQL92 TINYINT Exact numeric value with precision 3 and scale 0 number
SQL92 VARBINARY Variable length binary data with a maximum length 255 binary
SQL92 VARCHAR Variable length character data with a maximum length 255 string
ASCII NULL-PAD CROSS PLATFORM : Null padded (0x00) character string with a maximum length of 32000 with a null values of 0x00. string
ASCII SPACE-PAD CROSS PLATFORM : Space padded (0x20) character string with a maximum length of 32000 with a null value of spaces. string
ASCII-NUMERIC NULL-LEFT CROSS PLATFORM : Null padded left justified unsigned numeric. number
ASCII-NUMERIC NULL-LEFT-D CROSS PLATFORM : Null padded left justified unsigned numeric with a decimal point '.'. number
ASCII-NUMERIC NULL-LEFT-LS CROSS PLATFORM : Null padded left justified numeric with a leading sign (+ and -). number
ASCII-NUMERIC NULL-LEFT-LSD CROSS PLATFORM : Null padded left justified numeric with a leading sign (+ and -) and contains a decimal point '.'. number
ASCII-NUMERIC NULL-LEFT-LN CROSS PLATFORM : Null padded left justified numeric with a leading negative sign (-). number
ASCII-NUMERIC NULL-LEFT-LND CROSS PLATFORM : Null padded left justified numeric with a leading negative sign (-). number
ASCII-NUMERIC NULL-LEFT-TS CROSS PLATFORM : Null padded left justified numeric with a trailing sign (+ and -). number
ASCII-NUMERIC NULL-LEFT-TSD CROSS PLATFORM : Null padded left justified numeric with a trailing sign (+ and -) and contains a decimal point '.'. number
ASCII-NUMERIC NULL-LEFT-TN CROSS PLATFORM : Null padded left justified numeric with a trailing negative sign (-). number
ASCII-NUMERIC NULL-LEFT-TND CROSS PLATFORM : Null padded left justified numeric with a trailing negative sign (-). number
ASCII-NUMERIC NULL-RIGHT CROSS PLATFORM : Null padded right justified unsigned numeric. number
ASCII-NUMERIC NULL-RIGHT-D CROSS PLATFORM : Null padded right justified unsigned numeric with a decimal point '.'. number
ASCII-NUMERIC NULL-RIGHT-LS CROSS PLATFORM : Null padded right justified numeric with a leading sign (+ and -). number
ASCII-NUMERIC NULL-RIGHT-LSD CROSS PLATFORM : Null padded right justified numeric with a leading sign (+ and -) and contains a decimal point '.'. number
ASCII-NUMERIC NULL-RIGHT-LN CROSS PLATFORM : Null padded right justified numeric with a leading negative sign (-). number
ASCII-NUMERIC NULL-RIGHT-LND CROSS PLATFORM : Null padded right justified numeric with a leading negative sign (-). number
ASCII-NUMERIC NULL-RIGHT-TS CROSS PLATFORM : Null padded right justified numeric with a trailing sign (+ and -). number
ASCII-NUMERIC NULL-RIGHT-TSD CROSS PLATFORM : Null padded right justified numeric with a trailing sign (+ and -) and contains a decimal point '.'. number
ASCII-NUMERIC NULL-RIGHT-TN CROSS PLATFORM : Null padded right justified numeric with a trailing negative sign (-). number
ASCII-NUMERIC NULL-RIGHT-TND CROSS PLATFORM : Null padded right justified numeric with a trailing negative sign (-). number
ASCII-NUMERIC SPACE-LEFT CROSS PLATFORM : Space padded left justified unsigned numeric. number
ASCII-NUMERIC SPACE-LEFT-D CROSS PLATFORM : Space padded left justified unsigned numeric and contains a decimal point '.'. number
ASCII-NUMERIC SPACE-LEFT-LS CROSS PLATFORM : Space padded left justified numeric with a leading sign (+ and -). number
ASCII-NUMERIC SPACE-LEFT-LSD CROSS PLATFORM : Space piadded left justified numeric with a leading sign (+ and -) and contains a decimal point '.'. number
ASCII-NUMERIC SPACE-LEFT-LN CROSS PLATFORM : Space padded left justified numeric with a leading negative sign (-). number
ASCII-NUMERIC SPACE-LEFT-LND CROSS PLATFORM : Space padded left justified numeric with a leading negative sign (-). number
ASCII-NUMERIC SPACE-LEFT-TS CROSS PLATFORM : Space padded left justified numeric with a trailing sign (+ and -). number
ASCII-NUMERIC SPACE-LEFT-TSD CROSS PLATFORM : Space padded left justified numeric with a trailing sign (+ and -) and contains a decimal point '.'. number
ASCII-NUMERIC SPACE-LEFT-TN CROSS PLATFORM : Space padded left justified numeric with a trailing negative sign (-). number
ASCII-NUMERIC SPACE-LEFT-TND CROSS PLATFORM : Space padded left justified numeric with a trailing negative sign (-). number
ASCII-NUMERIC SPACE-RIGHT CROSS PLATFORM : Space padded right justified unsigned numeric. number
ASCII-NUMERIC SPACE-RIGHT-D CROSS PLATFORM : Space padded right justified unsigned numeric with a decimal point '.'. number
ASCII-NUMERIC SPACE-RIGHT-LS CROSS PLATFORM : Space padded right justified numeric with a leading sign (+ and -). number
ASCII-NUMERIC SPACE-RIGHT-LSD CROSS PLATFORM : Space padded right justified numeric with a leading sign (+ and -) and contains a decimal point '.'. number
ASCII-NUMERIC SPACE-RIGHT-LN CROSS PLATFORM : Space padded right justified numeric with a leading negative sign (-). number
ASCII-NUMERIC SPACE-RIGHT-LND CROSS PLATFORM : Space padded right justified numeric with a leading negative sign (-). number
ASCII-NUMERIC SPACE-RIGHT-TS CROSS PLATFORM : Space padded right justified numeric with a trailing sign (+ and -). number
ASCII-NUMERIC SPACE-RIGHT-TSD CROSS PLATFORM : Space padded right justified numeric with a trailing sign (+ and -) and contains a decimal point '.'. number
ASCII-NUMERIC SPACE-RIGHT-TN CROSS PLATFORM : Space padded right justified numeric with a trailing negative sign (-). number
ASCII-NUMERIC SPACE-RIGHT-TND CROSS PLATFORM : Space padded right justified numeric with a trailing negative sign (-). number
ASCII-NUMERIC ZERO-RIGHT CROSS PLATFORM : Zero padded right justified unsigned numeric. number
ASCII-NUMERIC ZERO-RIGHT-D CROSS PLATFORM : Zero padded right justified unsigned numeric with a decimal point '.'. number
ASCII-NUMERIC ZERO-RIGHT-LS CROSS PLATFORM : Zero padded right justified numeric with a leading sign (+ and -). number
ASCII-NUMERIC ZERO-RIGHT-LSD CROSS PLATFORM : Zero padded right justified numeric with a leading sign (+ and -) and contains a decimal point '.'. number
ASCII-NUMERIC ZERO-RIGHT-LN CROSS PLATFORM : Zero padded right justified numeric with a leading negative sign (-). number
ASCII-NUMERIC ZERO-RIGHT-LND CROSS PLATFORM : Zero padded right justified numeric with a leading negative sign (-). number
ASCII-NUMERIC ZERO-RIGHT-TS CROSS PLATFORM : Zero padded right justified numeric with a trailing sign (+ and -). number
ASCII-NUMERIC ZERO-RIGHT-TSD CROSS PLATFORM : Zero padded right justified numeric with a trailing sign (+ and -) and contains a decimal point '.'. number
ASCII-NUMERIC ZERO-RIGHT-TN CROSS PLATFORM : Zero padded right justified numeric with a trailing negative sign (-). number
ASCII-NUMERIC ZERO-RIGHT-TND CROSS PLATFORM : Zero padded right justified numeric with a trailing negative sign (-). number
ASCII-DATE DAYS CROSS PLATFORM : Space padded left justified numeric containing a count of days. datetime
ASCII-DATE FORMAT CROSS PLATFORM : Space padded left justified numeric containing a formatted date. datetime
ASCII-TIME NANOSECONDS CROSS PLATFORM : Space padded left justified numeric containing a count of nano seconds. datetime
ASCII-TIME SECONDS CROSS PLATFORM : Space padded left justified numeric containing a count of seconds. datetime
ASCII-TIME FORMAT CROSS PLATFORM : Space padded left justified numeric containing a formatted time. datetime
ASCII-TIMESTAMP NANOSECONDS CROSS PLATFORM : Space padded left justified numeric containing a count of nano seconds. datetime
ASCII-TIMESTAMP SECONDS CROSS PLATFORM : Space padded left justified numeric containing a count of seconds. datetime
ASCII-TIMESTAMP FORMAT CROSS PLATFORM : Space padded left justified numeric containing a formatted timestamp. datetime
FLOATING-POINT FLOAT PLATFORM SPECIFIC : 32 bit single precision floating point number. number
FLOATING-POINT DOUBLE PLATFORM SPECIFIC : 64 bit double precision floating point number. number
FLOATING-POINT IEEE-FLOAT-BE CROSS PLATFORM : 32 bit single precision IEEE big endian float. number
FLOATING-POINT IEEE-FLOAT-LE CROSS PLATFORM : 32 bit single precision IEEE little endian float. number
FLOATING-POINT IEEE-DOUBLE-BE CROSS PLATFORM : 64 bit double precision IEEE big endian float. number
FLOATING-POINT IEEE-DOUBLE-LE CROSS PLATFORM : 64 bit double precision IEEE little endian float. number
INTEGER SIGNED PLATFORM SPECIFIC : signed integer with a maximum length of 8. number
INTEGER UNSIGNED PLATFORM SPECIFIC : unsigned integer with a maximum length of 8. number
INTEGER SIGNED-BE CROSS PLATFORM : signed big endian integer with a maximum length of 8. number
INTEGER UNSIGNED-BE CROSS PLATFORM : unsigned big endian integer with a maximum length of 8. number
INTEGER SIGNED-LE CROSS PLATFORM : signed little endian integer with a maximum length of 8. number
INTEGER UNSIGNED-LE CROSS PLATFORM : unsigned little endian integer with a maximum length of 8. number
INTEGER SIGNED-RE PLATFORM SPECIFIC : signed reversed endian integer with a maximum length of 8. number
INTEGER UNSIGNED-RE PLATFORM SPECIFIC : unsigned reversed endian integer with a maximum length of 8. number
INTEGER-DATE DAYS PLATFORM SPECIFIC : unsigned integer (maximum length 8) containing a count of days. datetime
INTEGER-DATE DAYS-BE CROSS PLATFORM : unsigned big endian integer (maximum length 8) containing a count of days. datetime
INTEGER-DATE DAYS-LE CROSS PLATFORM : unsigned little endian integer (maximum length 8) containing a count of days. datetime
INTEGER-DATE DAYS-RE CROSS PLATFORM : unsigned reverse endian integer (maximum length 8) containing a count of days. datetime
INTEGER-DATE FORMAT PLATFORM SPECIFIC : unsigned integer (maximum length 8) containing a date with a default format of YYYYMMDD. datetime
INTEGER-DATE FORMAT-BE CROSS PLATFORM : unsigned big endian integer (maximum length 8) containing a date with a default format of YYYYMMDD. datetime
INTEGER-DATE FORMAT-LE CROSS PLATFORM : unsigned little endian integer (maximum length 8) containing a date with a default format of YYYYMMDD. datetime
INTEGER-DATE FORMAT-RE CROSS PLATFORM : unsigned reverse endian integer (maximum length 8) containing a date with a default format of YYYYMMDD. datetime
INTEGER-TIME FORMAT PLATFORM SPECIFIC : unsigned integer (maximum length 8) containing a time with a default format of HHMNSS. datetime
INTEGER-TIME FORMAT-BE CROSS PLATFORM : unsigned big endian integer (maximum length 8) containing a time with a default format of HHMNSS. datetime
INTEGER-TIME FORMAT-LE CROSS PLATFORM : unsigned little endian integer (maximum length 8) containing a time with a default format of HHMNSS. datetime
INTEGER-TIME FORMAT-RE CROSS PLATFORM : unsigned reverse endian integer (maximum length 8) containing a time with a default format of HHMNSS. datetime
INTEGER-TIME NANOSECONDS PLATFORM SPECIFIC : unsigned integer (maximum length 8) containing a count of nano seconds. datetime
INTEGER-TIME NANOSECONDS-BE CROSS PLATFORM : unsigned big endian integer (maximum length 8) containing a count of nano seconds. datetime
INTEGER-TIME NANOSECONDS-LE CROSS PLATFORM : unsigned little endian integer (maximum length 8) containing a count of nano seconds. datetime
INTEGER-TIME NANOSECONDS-RE CROSS PLATFORM : unsigned reverse endian integer (maximum length 8) containing a count of nano seconds. datetime
INTEGER-TIME SECONDS PLATFORM SPECIFIC : unsigned integer (maximum length 8) containing a count of seconds. datetime
INTEGER-TIME SECONDS-BE CROSS PLATFORM : unsigned big endian integer (maximum length 8) containing a count of seconds. datetime
INTEGER-TIME SECONDS-LE CROSS PLATFORM : unsigned little endian integer (maximum length 8) containing a count of seconds. datetime
INTEGER-TIME SECONDS-RE CROSS PLATFORM : unsigned reverse endian integer (maximum length 8) containing a count of seconds. datetime
INTEGER-TIMESTAMP FORMAT PLATFORM SPECIFIC : unsigned integer (maximum length 8) containing a formatted timestamp. datetime
INTEGER-TIMESTAMP FORMAT-BE CROSS PLATFORM : unsigned big endian integer (maximum length 8) containing a formatted timestamp. datetime
INTEGER-TIMESTAMP FORMAT-LE CROSS PLATFORM : unsigned little endian integer (maximum length 8) containing a formatted timestamp. datetime
INTEGER-TIMESTAMP FORMAT-RE CROSS PLATFORM : unsigned reverse endian integer (maximum length 8) containing a formatted timestamp. datetime
INTEGER-TIMESTAMP NANOSECONDS PLATFORM SPECIFIC : unsigned integer (maximum length 8) containing a count of nano seconds. datetime
INTEGER-TIMESTAMP NANOSECONDS-BE CROSS PLATFORM : unsigned big endian integer (maximum length 8) containing a count of nano seconds. datetime
INTEGER-TIMESTAMP NANOSECONDS-LE CROSS PLATFORM : unsigned little endian integer (maximum length 8) containing a count of nano seconds. datetime
INTEGER-TIMESTAMP NANOSECONDS-RE CROSS PLATFORM : unsigned reverse endian integer (maximum length 8) containing a count of nano seconds. datetime
INTEGER-TIMESTAMP SECONDS PLATFORM SPECIFIC : unsigned integer (maximum length 8) containing a count of seconds. datetime
INTEGER-TIMESTAMP SECONDS-BE CROSS PLATFORM : unsigned big endian integer (maximum length 8) containing a count of seconds. datetime
INTEGER-TIMESTAMP SECONDS-LE CROSS PLATFORM : unsigned little endian integer (maximum length 8) containing a count of seconds. datetime
INTEGER-TIMESTAMP SECONDS-RE CROSS PLATFORM : unsigned reverse endian integer (maximum length 8) containing a count of seconds. datetime
PACKED DECIMAL_STRING CROSS PLATFORM : Right justified packed decimal with sign stored in low nibble of last byte and data encoded from left to right (high and low nibble). number
PACKED DECIMAL_STRING-U CROSS PLATFORM : Right justified unsigned packed decimal with data encoded from left to right (high and low nibble). number
PACKED BCD CROSS PLATFORM : Binary coded decimal with encoded exponent in first byte and each subsequent byte contains a two digit number, negative number digits are stored subtracted from 100 (to maintain order). number
PACKED BCD-ONESCOMP CROSS PLATFORM : Binary coded decimal with encoded exponent in first byte and each subsequent byte contains a two digit number, negative numbers are stored in ones complement. e.g. 0x2234 (decodes to 3252). number
ISAM CHAR Non-terminated character string padded with trailing spaces string
ISAM DECIMAL Byte designs packed decimal number
ISAM DOUBLE Platform specific double number
ISAM FLOAT Platform specific float number
ISAM INT 2 byte big endian integer number
ISAM LONG 4 byte big endian integer number
ISAM MINT 2 byte platform specific integer number
ISAM MLONG 4 byte platform specific integer number
ISAM ROWID 4 byte platform specific integer number
ISAM STRING Character string trimmed and padded with 0x00 string
V14 ABC-PACKED Packed numeric with positive sign (3) and negative_sign (5) number
V14 ABC-STRING Ascii string with length encoded into first byte string
V14 AMOS-F6 AMOS 6 byte float number
V14 BASIC-DATE 2 byte native endian integer date with YYDDD format datetime
V14 BASIC-DATE4 4 byte native endian integer date with YYYYDDD format datetime
V14 BD-DEC Byte Designs decimal number
V14 BDC-OVERPUNCHED-NUMERIC Right justified overpunched trailing overpunched sign numeric number
V14 BINDATE 4 byte native endian integer date with format YYYYMMDD datetime
V14 BINDATE-REVERSED 4 byte reversed endian integer date with format YYYYMMDD datetime
V14 BINTIME 4 byte native endian integer time with format HHMNSSFF datetime
V14 BINTIME-2-U 2 byte unsigned native endian integer time with format HHMN datetime
V14 BINTIME-U 4 byte unsigned native endian integer time with format HHMNSSFF datetime
V14 BIT-1 Datatype to return bit 1 of a single byte field number
V14 BIT-2 Datatype to return bit 2 of a single byte field number
V14 BIT-3 Datatype to return bit 3 of a single byte field number
V14 BIT-4 Datatype to return bit 4 of a single byte field number
V14 BIT-5 Datatype to return bit 5 of a single byte field number
V14 BIT-6 Datatype to return bit 6 of a single byte field number
V14 BIT-7 Datatype to return bit 7 of a single byte field number
V14 BIT-8 Datatype to return bit 8 of a single byte field number
V14 BPDATE Bridgeport datatypes date datetime
V14 BPDOUBLE Byte designs packed decimal number
V14 BYTE 1 byte native endian integer number
V14 C-TIME 4 byte native endian integer julian date, starting at 01-01-1970 and incrementing in seconds datetime
V14 C-TIME-REV 4 byte reverse endian integer julian date, starting at 01-01-1970 and incrementing in seconds datetime
V14 C-TIME2 4 byte native endian integer julian date, starting at 01-01-1900 and incrementing in seconds datetime
V14 CDS-DATE 2 byte native endian integer calculated date, starting at 01-01-1900 with format YYDDD datetime
V14 CDS-DATE2 2 byte reverse endian integer julian date, starting at 24-10-1924 and incrementing in days datetime
V14 CDS-TIME3 3 byte native endian binary date with bitpattern HH[0:0:8] MN[1:0:8] SS[2:0:8] datetime
V14 CODA-DATE2 4 byte native endian integer julian date, starting at 17-11-1858 and incrementing in minutes datetime
V14 D-FLOATING OpenVMS D-FLOATING number
V14 D-FLOATING-REV OpenVMS D-FLOATING number
V14 DATE Ascii space padded date with format DD/MMM/YYYY datetime
V14 DATE-LOWER-CASE Ascii space padded lowercase date with format DD/MMM/YYYY datetime
V14 DATE-MIXED-CASE Ascii space padded mixedcase date (e.g. Jan) with format DD/MMM/YYYY datetime
V14 DATE-PAD-ZERO Ascii zero padded date with format DDMMYYYY datetime
V14 DIAL-199-DATE 3 byte native endian binary date with bitpattern YY[0:0:8] MM[1:0:8] DD[2:0:8], all bytes are stored subtracted from 199 datetime
V14 DIAL-99-DATE 3 byte native endian binary date with bitpattern YY[0:0:8] MM[1:0:8] DD[2:0:8], all bytes are stored subtracted from 99 datetime
V14 DIAL-DATE3 3 byte native endian date with bitpattern YY[0:0:8] MM[1:0:8] DD[2:0:8] datetime
V14 DIAL-DATE6 6 byte ascii zero padded date with format XYMMDD, starting at 01-01-1900 datetime
V14 DIBOL-DATE-5 5 byte ascii julian date starting at 31-12-1900 datetime
V14 DIBOL-TIME-5 5 byte ascii time (count of seconds) datetime
V14 DIBOL-OVERPUNCHED-NUMERIC Dibol right justified overpunched trailing sign numeric number
V14 DOUBLE-SPACES-NULL Platform specific double with a null value of spaces number
V14 F-FLOATING OpenVMS F FLOAT number
V14 FLOATING-DATE-4 4 byte platform specific floating point julian date starting at 01-01-0001 datetime
V14 FLOATING-DATE-8 8 byte platform specific floating point julian date stating at 01-01-0001 datetime
V14 FLOATING-JULIAN 8 byte platform specific floating point julian date starting at 01-01-1800 datetime
V14 FMBINDATE 4 byte native endian integer date with format YYYYMMDD and starting at 01-01-1900 datetime
V14 FREEFORM Right justified leading sign ascii numeric number
V14 FUG-DATE 3 byte native endian date with bitpattern YY[0:0:8] MM[1:0:8] DD[2:0:8] starting at 01-01-1800 datetime
V14 G-FLOATING OpenVMS G FLOAT number
V14 G-FLOATING-REV Byte reversed OpenVMS G FLOAT number
V14 H-FLOATING OpenVMS H FLOAT number
V14 HEXADECIMAL Represent binary data as hexadecimal strings string
V14 HEXADECIMAL-REVERSED Byte reversed binary data represented as hexadecimal strings string
V14 HEXDATE 4 byte native endian date with bitpattern DD[0:0:8],MM[1:0:8],YYYY[2:0:8],YYYY[3:0:8] datetime
V14 HEXDATE-REVERSED 4 byte reversed endian date with bitpattern DD[0:0:8],MM[1:0:8],YYYY[2:0:8],YYYY[3:0:8] datetime
V14 HEXDATE3 3 byte native endian date with bitpatter DD[0:0:8],MM[1:0:8],YY[2:0:8] stating at 01-01-1900 datetime
V14 HEXDATE3-REVERSED 3 byte reversed endian date with bitpatter DD[0:0:8],MM[1:0:8],YY[2:0:8] stating at 01-01-1900 datetime
V14 IBASEDATE 8 byte native endian julian timestamp in nanoseconds starting at 17-11-1858 datetime
V14 ICC-INT1 1 byte big endian ones complemented integer number
V14 ICC-INT2 2 byte big endian ones complemented integer number
V14 ICC-INT3 3 byte big endian ones complemented integer number
V14 ICC-INT4 4 byte big endian ones complemented integer number
V14 INFO-L-TYPE Right justified, leading sign, zero padded ascii numeric number
V14 INFO-N-TYPE Right justified, leading sign, space padded ascii numeric number
V14 INFO-T-TYPE Right justified, trailing sign, zero padded ascii numeric number
V14 INFO-V-TYPE Right justified, absolute leading sign, zero padded ascii numeric number
V14 INFORMIX-STRING Character string padded with 0x00 string
V14 INTEGER-S 4 byte native endian integer number
V14 INTEGER1 1 byte native endian integer number
V14 INTEGER2 2 byte native endian integer number
V14 INTEGER3 3 byte native endian integer number
V14 INTEGER4 4 byte native endian integer number
V14 INTEGER5 5 byte native endian integer number
V14 INTEGER6 6 byte native endian integer number
V14 INTEGER7 7 byte native endian integer number
V14 INTEGER8 8 byte native endian integer number
V14 JDATE 2 byte native endian date with bitpattern DDD[0:0:8],DDD[1:0:1],YY[1:1:7] starting at 01-01-1900 datetime
V14 KD-DATE 7 byte ascii numeric date with format YYYYDDD datetime
V14 LDA-NUMERIC Right justified, trailing sign, zero padded ascii numeric with decimal point number
V14 LDA-LEADING-SIGN-NUMERIC Right justified, leading sign, zero padded ascii numeric with decimal point number
V14 LEADING-OVERPUNCHED-NUMERIC Left justified, overpunched leading sign, null padded ascii numeric number
V14 LEADING-SIGN-NUMERIC Left justified, leading sign, space padded ascii numeric number
V14 LONG 4 byte native endian integer number
V14 LONGWORD 4 byte native endian integer number
V14 LONGWORD-REVERSED 4 byte reverse endian integer number
V14 MAGIC-DATE Magic Julian Date datetime
V14 MAGIC-INTEGER-DATE 4 byte native endian julian integer date starting at 01-01-0001 datetime
V14 MAGIC-INTEGER-DATE-REVERSED 4 byte reversed endian julian integer date starting at 01-01-0001 datetime
V14 MAGIC-INTEGER-TIME 4 byte native endian julian integer time datetime
V14 MAGIC-INTEGER-TIME-REVERSED 4 byte reversed endian julian integer time datetime
V14 MAGIC-NUMBER Magic Number number
V14 MAGIC-STRING-MEMO Character string with length encoded into first two bytes string
V14 MAGIC-STRING-MEMO-4 Character string with length encoded into first four bytes string
V14 MAGIC-TIME Magic Time datetime
V14 MICROFOCUS-COMP-5 Microfocus COBOL COMP-5 number
V14 MOTOROLA-LONGWORD Motorola 4 byte native endian, word reversed integer number
V14 MOTOROLA-LONGWORD-REVERSED Motorola 4 byte reverse endian, word reversed integer number
V14 NULL-TERMINATED-STRING Null terminated/padded character string string
V14 NUMERIC Right justified, leading_sign, space padded ascii numeric number
V14 OVERPUNCHED-DATE-1 6 byte right justified, trailing overpunched sign ascii numeric julian date starting at 31-12-1799 datetime
V14 PACKED Right justified, signed packed numeric number
V14 PACKED-S Right justified, signed packed numeric number
V14 PHDATE 2 byte native endian date with bitpattern DD[0:0:5],MM[0:5:3],MM[1:0:1],YY[1:1:7] starting at 01-01-1900 datetime
V14 PHDATE4 4 byte native endian date with bitpattern DD[0:0:5],MM[0:5:3],MM[1:0:1],YYYY[1:1:7],YYYY[2:0:8],YYYY[3:0:8] datetime
V14 PHDATETIME 8 byte unsigned native endian integer timestamp with format YYYYMMDDHHMNSSFF datetime
V14 QUADWORD 8 byte native endian integer number
V14 QUADWORD-REVERSED 8 byte reversed endian integer number
V14 RFA OpenVMS Record File Address binary
V14 RMC-ASCDATE-DDMMYY 6 byte ascii date with a format DDMMYY starting at 01-01-1951 with the century for years >= 51 being 19 and < 51 20. datetime
V14 RMC-ASCDATE-YYMMDD 6 byte ascii date with a format YYMMDD starting at 01-01-1951 with the century for years >= 51 being 19 and < 51 20. datetime
V14 RMC-BINDATE-DDMMYY 4 byte native endian integer date with a format DDMMYY starting at 01-01-1951 with the century for years >= 51 being 19 and < 51 20. datetime
V14 RMC-BINDATE-YYMMDD 4 byte native endian integer date with a format YYMMDD starting at 01-01-1951 with the century for years >= 51 being 19 and < 51 20. datetime
V14 SAIC-TIMESTAMP 4 byte SAIC timestamp. datetime
V14 SEB-DATE 6 byte native endian binary date with bitpattern YYYY[0:0:8],YYYY[1:0:8],MM[2:0:8],MM[3:0:8],DD[4:0:8],DD[5:0:8]). datetime
V14 SINT3 3 byte native endian signed integer. number
V14 SINT5 5 byte native endian signed integer. number
V14 SINT6 6 byte native endian signed integer. number
V14 SINT7 7 byte native endian signed integer. number
V14 SIRSI-MONEY 8 byte monetary values made up of two native_endian integers. number
V14 SMITHSONIAN-DATE 4 byte native endian julian integer date starting at 17-11-1858. datetime
V14 SOCIALE-DATE 4 byte packed decimal date with format DDMMYYYY. datetime
V14 SOCIALE-DATE-REV 4 byte packed decimal date with format DDMMYYYY. datetime
V14 SOCIALE-INT1 1 byte right justified packed numeric. number
V14 SOCIALE-INT2 2 byte right justified packed numeric. number
V14 SOCIALE-INT3 3 byte right justified packed numeric. number
V14 SOCIALE-INT4 4 byte right justified packed numeric. number
V14 SOCIALE-INT5 5 byte right justified packed numeric. number
V14 SOCIALE-INT6 6 byte right justified packed numeric. number
V14 SOCIALE-INT7 7 byte right justified packed numeric. number
V14 SPECIAL-1P
string
V14 SPECIAL-2P
string
V14 SPECIAL-3P
string
V14 SPECIAL-4P
string
V14 SPECIAL-5P
string
V14 SPECIAL-6P
string
V14 SPECIAL-DATE-1 2 byte native endian julian integer date starting at 05-08-1948. datetime
V14 SPECIAL-DATE-2 2 byte native endian julian integer date starting at 31-12-1919. datetime
V14 SPECIAL-DATE-3 5 byte ascii numeric julian date starting at 31-12-1899. datetime
V14 SPECIAL-DATE-4 5 byte ascii numeric zero padded date with format YYDDD starting at 01-01-1900. datetime
V14 SPECIAL-DATE-5 4 byte native endian integer timestamp starting at 01-01-1984. datetime
V14 SPECIAL-DATE-6 5 byte ascii numeric julian date starting at 31-12-1900. datetime
V14 SPECIAL-DATE-7 2 byte native endian unsigned integer date with a format YYDDD starting at 01-01-1970. datetime
V14 SPECIAL-DATE-8 2 byte native endian julian integer date starting at 01-01-1970. datetime
V14 SPECIAL-DATE-9 3 byte zero padded ascii numeric date with format DDD starting at 01-01-1900. datetime
V14 SPECIAL-DATE-10 5 byte ascii numeric julian date starting at 31-12-1799. datetime
V14 SPECIAL-DATE-11 2 byte reversed endian integer date with format YYDDD starting at 01-01-1970. datetime
V14 SPECIAL-DATE-12 5 byte packed numeric date with format DDMMYYYY. datetime
V14 SPECIAL-DATE-13 6 byte native endian binary date with bitpattern MM[0:0:8],MM[1:0:8],DD[2:0:8],DD[3:0:8],YYYY[4:0:8],YYYY[5:0:8] datetime
V14 SPECIAL-DATE-14 4 byte reversed endian binary date with bitpattern DDD[0:0:8],DDD[1:0:8],YYYY[2:0:8],YYYY[3:0:8] datetime
V14 SPECIAL-DATE-15 Time stored in native float. datetime
V14 SPECIAL-DATE-16 3 byte big endian julian integer date starting at 31-12-1599. datetime
V14 SPECIAL-DATE-17 6 byte ascii numeric zero padded date with format YY,MM,DD starting at 01-01-1900. datetime
V14 SPECIAL-DATE-18 2 byte native endian julian integer date starting at 31-12-1969. datetime
V14 SPECIAL-DATE-19 4 byte packed numeric date with format YYMMDD starting at 01-01-1927 with century for years > 26 being 19 and <= 26 being 20. datetime
V14 SPECIAL-DATE-20 3 byte packed numeric time with format HHMN. datetime
V14 SPECIAL-DATE-21 2 byte native endian unsigned integer date with YYDDD format datetime
V14 STAPLES-DATE 6 byte ascii numeric julian date starting at 31-12-1899. datetime
V14 STARDATE 8 byte native endian julian timestamp in nanoseconds starting at 17-11-1858. datetime
V14 STRING Space padded non-terminated character string. string
V14 TETRA-DATE 8 byte ascii numeric zero padded date with format DD/MM/YY starting at 01-01-1940 with century for years > 20 being 19 and <= 40 being 20. datetime
V14 TETRA-JULIAN-DATE 6 byte ascii numeric julian date starting at 31-12-1899. datetime
V14 TIME Ascii numeric time with variable format. datetime
V14 TIMESTAMP 8 byte native endian julian timestamp in nanoseconds starting at 17-11-1858. datetime
V14 TRAILING-NEGSIGN-DECIMAL Right justified, space padded ascii numeric with trailing sign. number
V14 TRAILING-OVERPUNCHED-NUMERIC Right justified, zero padded ascii numeric with trailing overpunched sign. number
V14 TRAILING-SIGN-NUMERIC Left justified, space padded ascii numeric with trailing sign. number
V14 TRESTLE-DATE 2 byte native_endian binary date with bitpattern DD[0:0:5],MM[0:5:3],MM[1:0:1],YY[1:1:7] starting at 01-01-1980. datetime
V14 TRESTLE-TIME 3 byte native endian time with bitpattern HH[0:0:8], MN[1:0:8], SS[2:0:8]. datetime
V14 UINT3 3 byte unsigned native endian integer. number
V14 UINT5 5 byte unsigned native endian integer. number
V14 UINT6 6 byte unsigned native endian integer. number
V14 UINT7 7 byte unsigned native endian integer. number
V14 UINTEGER1 1 byte native endian unsigned integer. number
V14 UINTEGER2 2 byte native endian unsigned integer. number
V14 UINTEGER3 3 byte native endian unsigned integer. number
V14 ULONG 4 byte native endian unsigned integer. number
V14 ULONGWORD-REVERSED 4 byte reversed endian unsigned integer. number
V14 UP3-DATE 3 byte packed date with format YYDDD stating at 01-01-1900. datetime
V14 UQUADWORD 8 byte native endian unsigned integer. number
V14 UQUADWORD-REVERSED 8 byte reversed endian unsigned integer. number
V14 UWORD 2 byte unsigned native endian integer. number
V14 UWORD-REVERSED 2 byte reversed endian unsigned integer. number
V14 VMSDATE4 4 byte native endian julian integer timestamp starting at 17-11-1858. datetime
V14 VMSDATE4-REVERSED 4 byte reversed endian julian integer timestamp starting at 17-11-1858. datetime
V14 VMSDATE8 8 byte native endian julian integer timestamp in nanoseconds starting at 17-11-1858. datetime
V14 VMSDATE8-REVERSED 8 byte reversed endian integer timestamp in nanoseconds starting at 17-11-1858. datetime
V14 WORD 2 byte native endian integer. number
V14 WORD-REVERSED 2 byte reversed endian integer. number
V14 ZONED Right justified, zero padded ascii numeric. number
V14 ZONED-NUM Right justified, zero padded, overpunched trailing sign ascii numeric. number
V14 ZONED-S Right justified, zero padded, overpunched trailing sign ascii numeric. number
V14 ZONED-U Right justified, zero padded ascii numeric. number
V14 PACKED-U Right justified, unsigned packed numeric. number
V14 SP3-DATE 5 byte signed packed numeric date with format YYDDD starting at 01-01-1900. datetime
V14 SPD-DATE 6 byte signed packed numeric date with format YYYYMMDD. datetime
V14 SPD-DATE-5 5 byte signed packed numeric date with format YYYYMMDD. datetime
V14 PSI-DATE 4 byte signed packed numeric date with format YYYYDDD. datetime
V14 DOUBLE Native double. number
V14 DOUBLE-REVERSED Byte reversed double. number
V14 FLOAT Native float. number
V14 FLOAT-REVERSED Byte reversed double. number
V14 INFORMIX-DATE-4 4 byte native endian julian integer date starting at 31-12-1899 with a null value of 0x00000080. datetime
V14 INFORMIX-DATE-4-REV 4 byte reversed endian julian integer date starting at 31-12-1899 with a null value of 0x80000000. datetime
V14 INFORMIX-INTEGER 4 byte native endian integer with a null value of 0x00000080. number
V14 INFORMIX-INTEGER-REVERSED 4 byte reversed endian integer with a null value of 0x80000000. number
V14 INFORMIX-SMALLINT 2 byte native endian integer with a null value of 0x0080. number
V14 INFORMIX-SMALLINT-REVERSED 2 byte reversed endian integer with a null value of 0x8000. number
POWERHOUSE BIG INTEGER SIGNED PowerHouse INTEGER SIGNED datatype with a length between 4 and 8 bytes number
POWERHOUSE BIG INTEGER UNSIGNED PowerHouse INTEGER UNSIGNED datatype with a length between 4 and 8 bytes number
POWERHOUSE CHARACTER PowerHouse CHARACTER datatype one character per byte, using the native character set of the host. string
POWERHOUSE FLOAT PowerHouse 4 byte FLOAT datatype number
POWERHOUSE DOUBLE PowerHouse 8 byte FLOAT datatype. number
POWERHOUSE FREEFORM PowerHouse FREEFORM datatype. number
POWERHOUSE IBASEDATE PowerHouse IBASEDATE datatype. datetime
POWERHOUSE INTEGER SIGNED PowerHouse INTEGER SIGNED datatype with a maximum length of 4 bytes (see BIG INTEGER SIGNED for sizes > 4 bytes). number
POWERHOUSE INTEGER UNSIGNED PowerHouse INTEGER UNSIGNED datatype with a maximum length of 4 bytes (see BIG INTEGER UNSIGNED for size > 4 bytes). number
POWERHOUSE JDATE PowerHouse JDATE datatype. datetime
POWERHOUSE PACKED SIGNED PowerHouse PACKED SIGNED datatype. number
POWERHOUSE PACKED UNSIGNED PowerHouse PACKED UNSIGNED datatype. number
POWERHOUSE PHDATE PowerHouse PHDATE datatype. datetime
POWERHOUSE VARCHAR PowerHouse VARCHAR datatype. string
POWERHOUSE ZONED PowerHouse ZONED datatype. number

Figure 37: Easysoft Data Access for ISAM data types

Transactions with D-ISAM

How D-ISAM performs transactions

Transaction processing defines a collection of changes to a group of data files in such a way as to be able to undo these changes at any time during the execution of a given operation.

Changes to records during D-ISAM transactions are performed immediately (other users will see changes made during a D-ISAM transaction before the transaction is committed), but it is possible to undo these changes by rolling back a given transaction.

Note that the transaction processing logic will lock all records that are changed within a transaction for the duration of that transaction.

In some instances it pays to keep the transaction cycle as short as possible to allow other processes access to these records.

Enabling D-ISAM transactions

Two settings must be made to the server side data source to enable D-ISAM based transactions:

All users using this facility must have read/write access to the transaction log file


NB

 The transaction log file will continually grow in size and can be backed up and purged on a regular basis to reduce the file size.

On Unix

Edit the relevant data source section of /etc/odbc.ini as follows:

transactions = 1

transaction_log =

{full transaction log path and file name}

On Windows

Edit one of the following sections of the Registry:

for a system data source

¯ OR ¯

for a user data source

From the list of data sources, click on the required data source and all current data source values will be listed in the right hand panel.

To enable transactions, right click in the right hand panel and select New -> String Value. Name the entry "transactions" and set the value to "1" (by double clicking the newly created entry).

To define a transaction log file, repeat the procedure, creating an entry called "transaction_log" and setting the value to the current location of the ISAM transaction log (if no transaction log exists, set the value to "C:\disam.trn").

Transaction Restrictions

ALTER, CREATE, DROP, GRANT and REVOKE statements are performed outside of the transaction processing cycle.

INSERT, UPDATE and DELETE are the only statements on which transaction processing can be performed.