Monday August 16, 2010 ODBCdL4 Server 1.5.1 Maintenance Release All Rights Reserved. Copyright (c) 2005 - 2010 by: Dynamic Concepts, Inc. Aliso Viejo, CA 92656 USA Email address: techsupport@dynamic.com Information: www.dynamic.com Downloads: www.dynamic.com or ftp.dynamic.com Product Description =================== o The ODBCdL4 server, in combination with the ODBCdL4 driver for Windows, provides ODBC access to uniBasic or dL4 files. The ODBCdL4 server is installed on a Unix, Linux, or Windows server to satisfy data requests from the ODBCdL4 driver on a Windows client system. The server reads records from uniBasic or dL4 files (indexed contiguous or contiguous) on the server and sends that data over a TCP/IP network connection to the Windows client system. Pre-installation instructions ============================= o This release requires an SSN authorized for release 1 of ODBCdL4 and the expected number of users. Without such an SSN, ODBCdL4 cannot be used. o Due to network protocol changes, this version of the ODBC server cannot be used with older beta versions of ODBCdL4 for Windows. ODBCdL4 for Windows must be upgraded to version 1.1 or later on all Windows client systems. o On Unix or Linux servers, Passport 4.1 or later must be installed in order to use release 1 of the ODBCdL4 server. On Windows Servers, Passport 2.8.1 or later must be installed. o Problem reports should be emailed to techsupport@dynamic.com. All problem reports should contain a description of the problem, the operating system name/revision, and, if at all possible, a reproducible sequence. Highlights of This Release ========================== o Improved performance for queries using OR conditions. o Improved performance for some Microsoft Access applications. Installation instructions ========================= 1. Ensure that you have the latest Passport version installed on your application system. For common platforms, the latest Passport software can be obtained from either www.dynamic.com. 2. Create a new account, named "odbcdl4", for use by the ODBC server. 3. Login as "odbcdl4". 4. Copy the distribution file to any temporary directory on your system, e.g. "/tmp". 5. Change your current directory to the temporary directory: cd /tmp 6. If the distribution file has a ".Z" extension, uncompress the file using the command "uncompress filename.Z". On Linux systems, use the command "gzip -d filename.Z". If the file has an extension of ".z", rename the file with an uppercase "Z" before uncompressing the file. 7. Unpack the distribution file using the command "cpio -imcdu where is the IP address of the server or "0.0.0.0" is the TCP port number to be used is the quoted full path of the database directory For example, .\instodbc.bat 0.0.0.0 9635 "C:\Program Files\ODBCdL4 Server\DB" o Table profiles for indexed contiguous files can now specify indexes that exist in a separate indexed contiguous file. Each such external index should be defined in the table profile using the standard syntax but with an added "FILE=filepath" option. The "FILE=" option must be the first line in the index definition. A table profile for an indexed contiguous file can use both internal and external indexes. The index number specified in the "[IndexNN]" section name for an external index can be a duplicate of an index number used in other index sections. Example: [FullISAMView] File=/u/data/orders [Record] Field=CustomerCode,0,31 Field=Order,32,7% Field=Quantity,36,7% Field=Cost,40,3.2% Field=Resale,46,3.2% Field=ProductId,52,21 [Index1] Name=ByOrder KeyPart=Order,0,6,NTOC("&&&&&&") [Index1] File=/u/data/ordersbycode Name=ByCustomerCode KeyPart=CustomerCode,0,31 o Table profiles for indexed contiguous files can now contain empty "[Record]" sections if all of the desired fields are defined as KEYONLY fields in the indexes. As with any profile using KEYONLY fields, each index must contain the same KEYONLY fields, but the order of the fields can vary. Example: [FullISAMView] File=/u/data/orders [Record] [Index1] Name=ByOrderAndCode KeyPart=Order,0,6,NTOC("&&&&&&"),KeyOnly KeyPart=CustomerCode,6,31,KeyOnly [Index2] Name=ByCodeAndOrder KeyPart=CustomerCode,0,31,KeyOnly KeyPart=Order,31,6,NTOC("&&&&&&"),KeyOnly o A RecordNum field can now use logical record numbers that are offset by a fixed amount from the physical record number by using the new BASE option. For example, records 100 through 299 of a contiguous file might be used to store values based on a code between 0 and 199. A table profile can be created to return only those records and support searches on the code values by using a RecordNum definition similar to this: RecordNum=Code,Base(100),IS("100" To "299") o Encrypted data files are supported on those platforms on which dL4 or UniBasic support encrypted data files. To access encrypted data files, the ODBCdL4 encryption option must be enabled in the SSN. By default, fully encrypted data files cannot be accessed at all and partially encrypted data files can only be accessed using the defined filler values for encrypted fields. To read decrypted data fields, the "-e keyfile" option must be added to the ODBCdL4 server command line configuration as described in the installation sections above and "keyfile" must be the path of a key file containing the needed keys. Please note that allowing decrypted ODBC access to encrypted files may compromise data security. o Bug fixed: a memory leak occurred whenever a table was closed. o Bug fixed: when implicitly importing fields from a second table profile, duplicate field names could be created. o Bug fixed: when one-to-many key fields were implicitly imported, the link did not work. Jan 31 2007 (Maintenance Release 1.3.1) o Bug fixed: "[LinkedRecord]" sections were sometimes improperly reported as a syntax error in a table profile. Nov 3 2006 (Release 1.3) o Table profiles can now include "[LinkedRecord]" sections to import columns from other table profiles based on keys formed from previously defined columns. For example, a profile might import a customer name based on a customer number. The syntax of a "[LinkedRecord]" section is described below in "Defining ODBC Databases and Tables". Linked records offer more convenience and better performance than an SQL JOIN. The example below of a table profile uses linked records to import the customer name and other customer information fields using the customer code as the key. Note that "/u/odbcprofiles/customer" is another table profile rather than the actual customer data file. [FullISAMView] File=/u/data/orders [Record] Field=CustomerCode,0,31 Field=Order,32,7% Field=Quantity,36,7% Field=Cost,40,3.2% Field=Resale,46,3.2% Field=ProductId,52,21 [Index1] Name=ByOrder KeyPart=Order,0,6,NTOC("&&&&&&") [LinkedRecord] File=/u/odbcprofiles/customer UseIndex=ByCustCode KeyPart=CustomerCode Field=Name Field=Address Field=City Field=State Field=ZipCode Field=PhoneNumber If imported field names conflict with existing field names, an alternate form of the "FIELD=" line can specify a different name for the field followed by the name used in the linked file: Field=LocalName,ExternalName o Alternate definitions can now be created so that a string field can be seen as a single field and as multiple subfields in the same table profile. For example, a telephone number might be defined as both a 10 character field and as an area code field and 7 digit number field. The alternate field definitions are entered in the "[Record]" section of a table profile using the "AltField" keyword as shown below: Field=FullTelNo,40,10 AltField=AreaCode,40,3 AltField=TelNo,43,7 The "AltField" keyword must be used instead of "Field" whenever it is desired to define a field that overlaps an existing field. Alternate field definitions can be used with any field type, but they are intended only for use with space filled string fields. Alternate definitions for indexes can be created using the "[AltIndex..]" keyword instead of "[Index...]". An "AltIndex" section can use the same index number as a previous index section and makes it possible to search more efficiently when using alternate field values. Example: [Index1] Name=ByTelNo KeyPart=FullTelNo,0,10 [AltIndex1] Name=ByAreaCodeShortTelNo KeyPart=AreaCode,0,3 KeyPart=TelNo,3,7 A query selecting a specific area code would use the alternate index definition to access only the desired records instead of scanning through the entire file. o Table profiles can now import and rename selected fields from Full-ISAM files or from other table profiles. To do so, the table profile must include an "OPENAS=" option (either "Full-ISAM" or "Full-ISAM View") and use the following alternate field definition syntax: Field={,} o A brief description of the SQL syntax supported by ODBCdl4 has been added to this document (see "SQL syntax supported by the ODBCdL4 server and driver"). o Behavior change: date values earlier than 1753 or later than 200 years after the current date are now treated as errors and, by default, are converted to SQL NULL values. The minimum and maximum date limits can be changed by adding the global settings "MINDATE=" or "MAXDATE=" lines to a profile. Example: MaxDate=January 1, 2099 o Bug fixed: trailing spaces were not ignored in "USEINDEX=" or "NAME=" lines in profiles. o Beta bug fixed: "[LinkedRecord]" sections that did not explicitly import fields reported errors. o Beta bug fixed: "ALTINDEX=" could not not be used with KEYONLY fields. o Beta bug fixed: FoxPro Full-ISAM files could not be used with some ODBC client software. Mar 28 2006 (Maintenance Release 1.2.1) o A new global option, "SetBlankStringsToNULL", has been implemented to simplify table profiles. Setting the option to TRUE in the first section of the table profile is equivalent to adding an 'IFNULL("")' option to each character field that does not have an explicit IFNULL option. Character fields in the data file that are converted to numeric or date values using conversion options/functions will also be given 'IFNULL("")' options. Example: SetBlankStringsToNULL=True o A new command line option, "-a", has been added to the checkdb utility to select scanning of all records in each table. The option can be used to detect errors in individual records such as bad field or key values. o The LEFT, UCASE, and LCASE functions can now be used in KEYONLY fields. o The ODBCdL4 server process ("odbcdl4") will now exit if a TERM or QUIT signal is received. o Bug fixed: the DTOC option did not properly detect some bad date values causing queries to fail. Dec 16 2005 (Release 1.2) o Access to databases can now be limited to specified users by creating access control files. If the server "-a" option is added to the inetd.conf or xinetd ODBCdL4 definition, access to databases will be limited to the users listed in the "_access_ctl_" text file in each database directory. A sample access control file is shown below: [Users] acmewidgets\FEllis acmewidgets\LWilson=external bob The first line, "[Users]", is required and must be the first line in the file. User names should be listed one per line as "domain\username" where "domain" is the Windows domain and "username" is the user login name. Users may also be listed by name alone ("bob"), but such lines will ignore the Windows domain and match any user with the specified login name. Any user listed in the file will be given read access to any table in the database when the user connects from a system on the local network. If a user connects from outside the local network, the user will be denied access unless the "external" privilege is added to the user line (see example above). Lines beginning with a semicolon will be treated as comments. This access control mechanism is intended to prevent casual violation of database usage policies. The mechanism does NOT use a strong form of authentication and should not be used to protect data from hostile users. Client systems should be updated to release 1.2 of the ODBCdL4 driver if access control files are used. If earlier releases of the driver are used, domain names cannot be used in user definitions. o Trailing spaces are now removed by default from string field values. If client applications need the trailing spaces, the new option "RTrimStrings=False" can be added to the file section to match the behavior of earlier releases. o Three new record field options have been defined to enable deletion of leading and/or trailing spaces from string field values. The LTRIM option enables deletion of leading spaces, the RTRIM option enables deletion of trailing spaces, and the TRIM option deletes both leading and trailing spaces. Because the default behavior is to delete trailing spaces, the RTRIM and TRIM options are only needed if the global "RTrimStrings" option is set to FALSE. Example: Field=CustomerName,148,30,Trim o The prefix and character set fields in key part definitions are now optional. o Bug fixed: the precision of numeric fields using the NTOC option was set smaller than the actual value. The smaller precision caused overflow errors when large values were encountered in a table. o Bug fixed: Microsoft ACCESS was not able to use tables in which the primary index contain numeric key values of 0. Nov 14 2005 (Maintenance Release 1.1.1) o CONV statement and CHR function data in keys is now supported via the CONV, SCONV, TCONV, and STCONV options. The CONV and TCONV options are used with unsigned numbers. The SCONV and STCONV options are used with signed numbers. The TCONV and STCONV options are used with toggled character data such as produced by the UniBasic CHR string function. The numbers must not be zero except at the end of a key. Example: KeyPart=CustNum,0,1,"","",CONV o Two new data conversion options, TCONV and STCONV, have been added to support numeric fields in records where values are generated with the uniBasic CHR function or some other method that stores binary numbers as high bit toggled characters. Both options assume that all characters in the field are part of the value and thus the field must be between 1 and 4 characters long. The TCONV option supports unsigned values and the STCONV option supports signed values. For example, the line Field=U8,148,1,TCONV would convert the character at offset 148 to a value between 0 and 255. The line Field=S8,148,1,STCONV would convert the character at offset 148 to a value between -128 and 127. o Bug fixed: an overlapping field error was reported on fields that were immediately adjacent, but not overlapping. Sep 14 2005 (Release 1.1) o First production release of ODBCdL4. o The ODBCdL4 server now requires an SSN authorized for release 1 of ODBCdL4 and for the number of expected users. o The IS and NOT options are now supported on RecordNum fields. Aug 19 2005 (Beta Release 1.1.1.7) o Packed data in keys is now supported via the PACK15, PACK18, PACK20, PACK45, PACK45, PACK48, and PACKIMS options. These options convert packed data in the CALL 15, CALL 18, CALL 20, CALL 45, CALL 48, and CALL IMSPACK formats to unpacked character data. Example: KeyPart=CustNum,0,4,"","",PACK20,NTOC("&&&&&&&&") o Two new data conversion options, CONV and SCONV, have been added to support numeric fields in records where values are generated with the uniBasic or dL4 CONV statement. Both options assume that all characters in the field are part of the value and thus the field must be between 1 and 4 characters long. The CONV option supports unsigned values and the SCONV option supports signed values. For example, the line Field=U16,148,2,CONV would convert two characters at offset 148 to a value between 0 and 65535. The line Field=S16,148,2,SCONV would convert two characters at offset 148 to a value between -32768 and 32767. o Bad values, such as incorrectly formatted dates, are now returned as SQL NULL values rather than causing row access errors. The option "SetBadValuesToNULL" can be set in the first section of the profile to disable conversion to NULLs. Example: SetBadValuesToNULL=False o The parameter "DDDDDBaseDate" can now be set in the first section of the profile to define a base year for "DDDDD" format date values instead of using the default value of 1968. Example DDDDDBaseDate=1980 o Non-sortable date formats such as "MMDDYY" can now be used in index definitions. Please note that using such values in an index may prevent efficient use of the index in queries. o The checkdb utility now tests sample key values and will output a warning message if it discovers that the key definition does not match the actual key values. o An "install" script has been added to simplify installing or upgrading the ODBCdL4 server. Jun 21 2005 (Beta Release 1.1.1.6) o Formatted files can now be accessed by the ODBC server. Formatted files must be defined in profiles just like those for contiguous or indexed contiguous files except that there are no index sections, the byte displacement in field definitions is replaced by the item number, and there must be a "RecordNum" field to associate the record number with a field name. o FoxPro Full-ISAM files can now be accessed by the ODBC server. Full-ISAM files must be defined in profiles, but the profiles should only contain a file section to set the file path and driver name. Field and index definitions are supplied by the file itself. Profile example: [FullISAMView] File=/data/invoices.dbf o A new optional parameter, YYBASEYEAR, can now be set in the general section of a profile to determine the base year for YY date values (from DTOC or DTON options). By default, YY values are converted to 4 digit year values by using the closest century. The YYBASEYEAR value can be used to select a specific base value. For example, a YYBASEYEAR value of 1980 would make a "YY" value of 27 equal to 2007. o The IS and NOT options now support lists of comma separated values. Each value can be a specific value ("M1") or a value range ("M1" To "M2"). Field=RecType,134,3,IS("M1","M4" To "M7") o When used with character fields, the IS and NOT options now support an optional length parameter at the start of the value list. If present, the length parameter specifies the number of leading characters in the field to compare to the selection values. Field=Product,20,10,IS(2,"M1","M4" To "M7") o The DTOC mask now supports format code for space filled values: Yyyy Four digit year, space filled. Yy Two digit year with space fill. Mm Month, 1 - 12, space filled. Dd Day of month, 1 - 31, space filled. Ddd Day of year, 1 - 366 or 0 - 365, space filled. Ddddd Space filled day relative to base year. Hh Hour of day, space filled. Mm Minute of hour, space filled. Ss Second of minute, space filled. o The alignment and fill character fields in record field definitions in a profile are now optional. It is no longer necessary to add in empty values (",,,"). o The prefix and character set fields in key part definitions can now be omitted if no options (such as DTOC) are needed. o The length value is now optional in the UCASE and LCASE options of KEYONLY fields. o Bug fixed: memory violations sometimes occurred when using multiple indexes with key-only fields. o Bug fixed: queries and Microsoft Access linked tables sometimes did not work when date fields were used in indexes. May 6 2005 (Beta Release 1.1.1.5) o Due to network protocol changes, this version of the ODBC server cannot be used with older versions of ODBCdL4 for Windows. ODBCdL4 for Windows must be upgraded to version 1.1.1.3 or later on all Windows client systems. o The DTOC and DTON options are now supported to convert date strings and date numbers to ODBC date values. The options are described in the section "Defining ODBC Databases and Tables" below. Field=DateRcvd,46,8,,,DTOC("YYYYMMDD") Field=LastDate,66,2.0%,,,DTON("DDDDD") o The NTOC, NTVNTOC, STR, and NTVSTR options are now supported to convert numeric strings to ODBC numeric values. The options are described in the section "Defining ODBC Databases and Tables" below. KeyPart=PartNo,0,9,"","",NTOC("+&&&&&&&&") o The IFNULL option is now supported to convert specific string or numeric values in a field to SQL NULL values. The IFNULL option is described in the section "Defining ODBC Databases and Tables" below. Field=SalesCode,14,2.0%,,,IfNull("999999") o Packed data in record fields is now supported via the PACK15, PACK18, PACK20, PACK45, PACK45, PACK48, and PACKIMS options. These options convert packed data in the CALL 15, CALL 18, CALL 20, CALL 45, CALL 48, and CALL IMSPACK formats to unpacked character data. A packing option can be followed by a numeric conversion option such as STR to convert the unpacked character string into an ODBC numeric value. Example: Field=TotalSales,78,8,,,PACK15,STR("#") o Contiguous files can now be accessed by the ODBC server. Contiguous files must be defined in profiles just like those for indexed contiguous files except that there are no index sections and there must be a "RecordNum" field (described below) to associate the record number with a field name. o Both contiguous and indexed contiguous file records can now be accessed or linked by record number by defining a "RecordNum" field in the profile. A "RecordNum" field is defined in the record section of a profile and consists of a single line: RecordNum= where "" is the field name to be given to the record number. Records can then be selected according to the value of "". o Files with multiple record layouts can now be accessed by using the new IS and NOT field and key options to select records using a specific record layout. For example, if a file has two record layouts, the file can be read by defining two profiles, one for each record layout. In each profile, the IS or NOT option is used to select the appropriate records. When the IS option is used in a key part, the field name can be specified with a leading asterisk if the field isn't associated with a record field. Such fields will not be visible to ODBC users. Field=RecType,134,3,,,IS("TY") o Field names are now restricted to a combination of letters, digits, and underscores. The first character of a name must be a letter. Field names can be placed in quotation marks ("name") to enable use of any character other than asterisk. Such quoted field names may not be compatible with third-party ODBC tools. o Documentation change: table names (profile filenames) are restricted to be a combination of letters, digits, and underscores. Feb 28 2005 (Beta Release 1.1.1.4) o Bug fixed: queries for specific rows failed on files in which keys had trailing spaces. o Bug fixed: queries sometimes failed when accessing uniBasic non-universal files. Feb 24 2005 (Beta Release 1.1.1.3) o Bug fixed: a memory violation sometimes occurred in the odbcdl4 server when a query was performed on an index with multiple parts and the first part was selected for equality. Feb 1 2005 (Beta Release 1.1.1.2) o First public release. Jan 17 2005 (Beta Release 1.1.1.1) o First release. Defining ODBC Databases and Tables ================================== o In ODBC, data is accessed through databases which contain tables. Each table consists of rows, all of which contain the same columns and where each column has a name. When using the ODBCdL4 server, a database is any subdirectory within /home/odbcdl4/db and each table within the database is a text file that describes a uniBasic or dL4 indexed contiguous file. Thus, the file /home/odbcdl4/db/accounting/customers would appear in ODBC to be the table "customers" within the database "accounting". Each of these text files is a Full-ISAM View profile that specifies the full path of a data file and defines the columns that exist within the records of the data file. The name of the profile is restricted to a combination of letters, digits, and underscores. For each column, the profile must define a column name, a column position within the record, a data type, and the column size or precision. The profile also defines indexes which are used to make searches more efficient and to distinguish between active and deleted records (each profile must define at least one index). Any index defined in the profile must contain one key and only one key for each active record. Note that when using ODBC, searches can be applied to any data column, but a full sequential read of the entire file will occur if an index can't be used. The ODBCdL4 server operates by default in a case insensitive mode. When selecting or sorting by character columns, case will be ignored ("Abc" is considered equal to "aBC"). When using case insensitive mode, indexes should only be declared if they contain case insensitive keys. For example, if a key contains a name value and that name is always converted to upper or lower case in the key, then the index can be used (with the "UCase" or "LCase" option specified). If case sensitivity is desired, the "-c" option can be added to the inetd or xinetd command line arguments as described in the installation section. Case sensitivity is enabled or disabled for all server access: case sensitivity can not be selected for individual indexes, tables, or databases. The following is an example of a profile (lines beginning with semicolons are comments): [FullISAMView] ; The first line of the file must be "[FullISAMView]" File=/acct/customers [Record] ; Field=,,,,{,} Field=NAME,0,25 Field=ADDRESS1,25,25 Field=ADDRESS2,50,25 Field=CITY,75,15 Field=STATE,90,2 Field=ZIPCODE,92,5 Field=BALANCE,98,4.2% Field=CREDTLIMIT,106,4% [Index1] ; "[Index1]" declares a new index section and the index number within ; the actual indexed file. Each index section name has the format ; "[IndexNN]" where "NN" is the index number in the data file. If ; the index exists in a file separate from the data file, an optional ; "File=filename" line can be added immediately after the section name ; line to specify the external index file. The "Name=" line specifies ; the index name seen by ODBC (currently unused, but syntactically ; required). Name=BYNAME ; KeyPart=,,,,, KeyPart=NAME,0,25,"","",UCase(25) [Index2] Name=BYSTATEANDNAME KeyPart=STATE,0,2,"","",UCase(2) KeyPart=NAME,2,25,"","",UCase(12) The profile also controls the record locking behavior used to access a table. When ODBCdL4 encounters a locked record, the default action is to wait a short period of time and then, if the record is still locked, to skip the record. If desired, record locks can be ignored by specifying "" permissions on the "FILE=" line (for example, "FILE=/acct/file"). Note that ignoring record locks may cause ODBCdL4 to read records that have been partially updated by an application. If the "CITY" and "ADDRESS" fields of a record were being updated by an application using separate statements, ODBCdL4 might read a record with mismatching "CITY" and "ADDRESS" values. The checkdb utility can be used to check the validity of a profile. The command "/home/odbcdl4/checkdb path" will open and check the profile specified by "path" or, if "path" is a directory, checkdb will open and check each profile within the directory. A full detailed description of the profile syntax is given below. In this release, all of the listed options are supported. [FullISAMView] ; The first line of "[FullISAMView]" is used by the auto-selection ; driver File=sample ; The "File=" line supplies a file specification. Instead of a single ; string, the file specification can also be specified as separate ; values as follows. Filename=sample Options=options ; OpenAs=... is needed only to open Full-ISAM files or to force opening ; an indexed contiguous file as a contiguous file. OpenAs=Indexed-Contiguous ; YYBaseYear=... is optional and is needed only if "YY" values in DTOC() ; or DTON() options are set relative to a specific year. YYBaseYear=1970 ; SetBadValuesToNULL=... is optional and is needed only if bad values in ; conversion functions such as DTON are NOT to be treated as SQL NULL ; values. If set to false, bad values will cause row access errors. SetBadValuesToNULL=True ; SetBlankStringsToNULL=... is optional and is needed only if blank ; character fields should be treated as SQL NULL values. Setting the ; option to TRUE is equivalent to adding an 'IFNULL("")' option to ; each character field that does not have an explicit IFNULL option. ; Character fields that are converted to numeric or date values using ; conversion options/functions will also be given 'IFNULL("")' options. SetBlankStringsToNULL=False ; DDDDDBaseDate=... is optional and is needed only if "DDDDD" format ; date values do not use the default value of 1968. DDDDDBaseDate=1968 ; MinDate=... is optional and is needed only if the default minimum date ; value of January 2, 1753 must be changed. MinDate=January 2, 1753 ; MaxDate=... is optional and is needed only if the default maximum date ; value of the current date plus 200 years must be changed. MaxDate=November 3, 2206 ; RTrimStrings=... is optional and is needed only to change the default ; behavior of removing trailing spaces from string field values. RTrimStrings=True ; Entries for fields occur in the "[Record]" section and have two ; formats selected on whether the file is a Full-ISAM file or not. ; For Full-ISAM files, a field entry looks like: ; ; Field=, ; ; If there are no fields defined and the file is a Full-ISAM file, then ; all the fields defined by the data file will be imported. ; ; For all other files (indexed, contiguous, and formatted), a field ; entry looks like: ; ; Field=,,{,}{,}{,} ; ; The keyword "Field" must be replaced by "AltField" if the field being ; defined overlaps a previously defined field. ; ; Name of field in Full-ISAM view. The name must begin ; with an alphabetic character. All other characters ; in the name must be letters, digits, or underscores ("_"). ; If quoted ("name"), a name can use any character except ; for a leading asterisk ("*") or a quotation mark. Such ; names may not be compatible with all ODBC clients. ; Byte displacement in the actual record or item number for ; formatted files. ; Character length for strings, precision number and ; decimal places for numerics. For strings, is just ; a number. For numerics, has the syntax "p%" or ; "p.d%" where "p" is the precision and "d" is an optional ; number of decimal places. ; "L" to left-align string data (default). ; "R" to right-align string data. ; fill character to use if string field sizes mismatch, ; default is " ". ; Optional keyword (case insensitive) based options. The ; current options are: ; STRIP String values in the data file should be ; have trailing spaces removed. ; IS({len,}values) Ignore any record where the field is ; not equal to a specified value. "values" ; is a comma separated list of one or more ; quoted string values. If the field is ; numeric, the values must be numbers in ; quotes ("5"). Character fields can specify ; the "len" value so that only the first "len" ; characters of the field are compared against ; the values. A value range can be specified ; by using two quoted values separated by the ; word "to" ("M1" To "M3"). ; NOT({len,}values) Ignore any record where the field is ; equal to a specified value. "values" is a ; comma separated list of one or more quoted ; string values. If the field is numeric, the ; values must be numbers in quotes ("5"). ; Character fields can specify the "len" value ; so that only the first "len" characters of ; the field are compared against the values. ; A value range can be specified by using two ; quoted values separated by the word "to" ; ("M1" To "M3"). ; DTOC(mask) Convert view date field from a character ; string in the record image using "mask". ; All dates use local date/time. "mask" is ; a quoted string in which the following ; substrings have special meaning: ; YYYY Four digit year ; Yyyy Four digit year, space filled ; YY Two digit year with the century set ; so it is within 50 years of the ; current date. ; Yy Two digit year with space fill. ; AA Two digit year in which years after ; 1999 are specified as "A0" - "E9". ; MM Month, 1 - 12 ; Mm Month, 1 - 12, space filled. ; DD Day of month, 1 - 31 ; Dd Day of month, 1 - 31, space filled. ; DDD Day of year, 1 - 366 or 0 - 365 ; Ddd Day of year, 1 - 366 or 0 - 365, ; space filled. ; DDDDD Zero filled day relative to base year ; 1968. January 1, 1968 is "00001". ; Six or more "D"s can be also be used. ; Ddddd Space filled day relative to base ; year. ; HH Hour of day ; Hh Hour of day, space filled. ; NN Minute of hour ; Nn Minute of hour, space filled. ; SS Second of minute ; Ss Second of minute, space filled. ; DTON(mask) Convert view date field from a decimal ; number in the record image using "mask". ; All dates use local date/time. "mask" is ; a quoted string in which the following ; substrings have special meaning: ; YYYY Four digit year ; YY Two digit year with the century set ; so it is within 50 years of the ; current date. ; MM Month, 1 - 12 ; DD Day of month, 1 - 31 ; DDD Day of year, 1 - 366 or 0 - 365 ; DDDDD Day relative to base year 1968. ; January 1, 1968 is 1. Six or more ; "D"s can be also be used. ; HH Hour of day ; NN Minute of hour ; SS Second of minute ; NTOC(mask) Convert view numeric field from a character ; string according to the USING mask "mask". ; NTVNTOC(mask) Convert view numeric field from a character ; string according to the USING mask "mask" ; and the native locale. ; CONV Convert view numeric field from a character ; string in the manner of a uniBasic or dL4 ; CONV statement applied to all characters in ; field. The numeric value is unsigned. ; TCONV Convert view numeric field from a high bit ; toggled character string in the manner of a ; uniBasic CHR string function. The numeric ; value is unsigned. ; SCONV Convert view numeric field from a character ; string in the manner of a uniBasic or dL4 ; CONV statement applied to all characters in ; the field. The numeric value is signed and ; uses the bias mechanism described for the ; CONV statement in the uniBasic and dL4 ; reference manuals. ; STCONV Convert view numeric field from a high bit ; toggled character string in the manner of a ; uniBasic CHR string function applied to all ; characters in the field. The numeric value ; is signed and uses the bias mechanism ; described for the CONV statement in the ; uniBasic and dL4 reference manuals. ; STR(mask) Convert view numeric field from a character ; string according to the mask "mask" which ; consists of characters that are copied as is ; to the string and one "#" character that is ; replaced with a STR$() result. ; NTVSTR(mask) Convert view numeric field from a character ; string according to the mask "mask" which ; consists of characters that are copied as is ; to the string and one "#" character that is ; replaced with a STR$() result using the ; native locale. ; PACK15 Character data is packed using CALL 15 ; PACK18 Character data is packed using CALL 18/19 ; PACK20 Character data is packed using CALL 20/21 ; PACK45 Character data is packed using CALL 45/46 ; PACK48 Character data is packed using CALL 48/49 ; PACKIMS Character data is packed using CALL IMSPACK ; IFNULL(value) Convert value in record image to a NULL in ; the Full-ISAM view. ; LTRIM Delete leading spaces from a string field. ; RTRIM Delete trailing spaces from a string field. ; This is the default behavior unless the ; global "RTrimStrings" option is set to FALSE. ; TRIM Delete both leading and trailing spaces from ; a string field. ; ; A field can be defined for the current record number using the ; following format: ; ; RecordNum= ; ; This will define both a field and a Full-ISAM index using that field. ; The index will be named "ByRecordNumber". A record number field ; must be defined for contiguous or formatted files. Indexed-contiguous ; files that define a record number cannot use key-only fields (see ; below). [Record] Field=NAME,0,25 Field=ADDRESS1,25,25 Field=ADDRESS2,50,25 Field=CITY,75,15 Field=STATE,90,2 Field=ZIPCODE,92,5 Field=BALANCE,98,4.2% Field=CREDTLIMIT,106,4% Field=LASTPAYMNT,114,2%,DTON("YYDDD") ; Entries for keyparts occur in "[Index..]" or "[AltIndex..]" sections ; and look like: ; ; KeyPart=,,{,{,{,}}} ; ; Name of field in Full-ISAM view. The name must begin ; with an alphabetic character or an asterisk. All other ; characters in the name must be letters, digits, or ; underscores ("_"). If the first character is an ; asterisk, the field must specify an IS() option and the ; field will be treated as a constant and will not be ; visible. Such fields can contains multiple asterisks ; and/or spaces in the field name in addition to the ; normal characters. ; Byte displacement in idx-ctg key. ; Character length for strings, precision number and ; decimal places for numerics. For strings, is just ; a number. For numerics, has the syntax "p%" or ; "p.d%" where "p" is the precision and "d" is an optional ; number of decimal places. ; prefix character strings, unused. ; pseudo-key character set, unused. ; Optional keyword based options. The current options are: ; STRIP String values in the data file keys should ; have trailing spaces removed (only valid ; in the final key part). ; IS({len,}values) Ignore any record where the field is ; not equal to a specified value. "values" ; is a comma separated list of one or more ; quoted string values. The string value can ; use backslash notation for octal ("\ooo\") ; or hexadecimal ("\xhh\") character values. ; If the field is numeric, the values must be ; numbers in quotes ("5"). Character fields ; can specify the "len" value so that only the ; first "len" characters of the field are ; compared against the values. A value range ; can be specified by using two quoted values ; separated by the word "to" ("M1" To "M3"). ; NOT({len,}values) Ignore any record where the field is ; equal to a specified value. "values" is a ; comma separated list of one or more quoted ; string values. The string value can use ; backslash notation for octal ("\ooo\") or ; or hexadecimal ("\xhh\") character values. ; If the field is numeric, the values must be ; numbers in quotes ("5"). Character fields ; can specify the "len" value so that only the ; first "len" characters of the field are ; compared against the values. A value range ; can be specified by using two quoted values ; separated by the word "to" ("M1" To "M3"). ; NOSORT Specifies that this field does NOT sort ; according to the data value. For example, ; a field using NTOC("---#") would treat ; negative values as greater than positive ; values. The driver assumes that fields ; (except for STR and NTVSTR fields) sort ; correctly. This option does not change ; driver behavior except that the condition ; is reported when DCC_GETPARM is used to ; retrieve index information. ; KEYONLY The data for this field exists only in the ; key and not in the record. If KEYONLY fields ; are used in an index, they must be defined in ; each index (otherwise, the server would be ; unable to retrieve the KEYONLY fields in ; index N when accessing the file using index ; M). If the KEYONLY fields do not exist in ; each index, then separate profiles must be ; created to use the incompatible indexes. ; KEYONLY fields can not be used if a record ; number was defined in the record section. ; DTOC(mask) Convert view date field from a character ; string in the record image using "mask". ; All dates use local date/time. "mask" is ; a quoted string in which the following ; substrings have special meaning: ; YYYY Four digit year ; Yyyy Four digit year, space filled ; YY Two digit year with the century set ; so it is within 50 years of the ; current date. ; Yy Two digit year with space fill. ; AA Two digit year in which years after ; 1999 are specified as "A0" - "E9". ; MM Month, 1 - 12 ; Mm Month, 1 - 12, space filled. ; DD Day of month, 1 - 31 ; Dd Day of month, 1 - 31, space filled. ; DDD Day of year, 1 - 366 or 0 - 365 ; Ddd Day of year, 1 - 366 or 0 - 365, ; space filled. ; DDDDD Zero filled day relative to the base ; year 1968. January 1, 1968 is ; "00001". Six or more "D"s can be ; also be used. ; Ddddd Space filled day relative to the ; base year. ; HH Hour of day ; Hh Hour of day, space filled. ; NN Minute of hour ; Nn Minute of hour, space filled. ; SS Second of minute ; Ss Second of minute, space filled. ; NTOC(mask) Convert view numeric field from a character ; string according to the USING mask "mask". ; NTVNTOC(mask) Convert view numeric field from a character ; string according to the USING mask "mask" ; and the native locale. ; STR(mask) Convert view numeric field from a character ; string according to the mask "mask" which ; consists of characters that are copied as is ; to the string and one "#" character that is ; replaced with a STR$() result. ; NTVSTR(mask) Convert view numeric field from a character ; string according to the mask "mask" which ; consists of characters that are copied as is ; to the string and one "#" character that is ; replaced with a STR$() result using the ; native locale. ; CONV Convert view numeric field from a character ; string in the manner of a uniBasic or dL4 ; CONV statement applied to all characters in ; field. The numeric value is unsigned, but ; must not be zero except at the end of the ; key. ; TCONV Convert view numeric field from a high bit ; toggled character string in the manner of a ; uniBasic CHR string function. The numeric ; value is unsigned, but must not be zero ; except at the end of the key. ; SCONV Convert view numeric field from a character ; string in the manner of a uniBasic or dL4 ; CONV statement applied to all characters in ; the field. The numeric value is signed and ; uses the bias mechanism described for the ; CONV statement in the uniBasic and dL4 ; reference manuals. The value must not be ; zero except at the end of the key. ; STCONV Convert view numeric field from a high bit ; toggled character string in the manner of a ; uniBasic CHR string function applied to all ; characters in the field. The numeric value ; is signed and uses the bias mechanism ; described for the CONV statement in the ; uniBasic and dL4 reference manuals. The ; value must not be zero except at the end of ; the key. ; LEFT(len) Uses the first "len" characters of the field ; in the key. The field must be defined in ; the record section. ; UCASE(len) Uses the first "len" character of the field ; in the key in uppercase. If the field is ; defined in the record section the "len" ; value must be specified. If the field is ; KEYONLY, the length will default to the ; field length. ; LCASE(len) Uses the first "len" character of the field ; in the key in lowercase. If the field is ; defined in the record section the "len" ; value must be specified. If the field is ; KEYONLY, the length will default to the ; field length. ; PACK15 Character data is packed using CALL 15 ; PACK18 Character data is packed using CALL 18/19 ; PACK20 Character data is packed using CALL 20/21 ; PACK45 Character data is packed using CALL 45/46 ; PACK48 Character data is packed using CALL 48/49 ; PACKIMS Character data is packed using CALL IMSPACK ; IFNULL(value) Convert value in key image to NULL in ; Full-ISAM view. [Index1] Name=BYORDER KeyPart=ORDER,0,6 [Index2] Name=BYSTATE KeyPart=STATE,0,2 KeyPart=ORDER,2,6 ; If an index exists in a separate file, a "File=" line can be placed ; before the "Name=" line to specify the path of the external index file. [Index3] File=path_of_index_file Name=BYINVOICE KeyPart=INVOICE,0,6 ; Optionally, there can be one or more "[LinkedRecord]" or ; "[LinkOneToMany]" sections to bring in data fields from other files ; (similar to an SQL JOIN). Of these sections, only one can be a ; "[LinkOneToMany]" section. Each linked record section must begin with ; a file specification using a single "File=" line or a "Filename=" line ; followed by an "Options=" line. The file specified must be a ; Full-ISAM view profile. [LinkedRecord] File=customer.prf ; The "File=" line supplies a file specification. Instead of a single ; string, the file specification can also be specified as separate ; values as follows. Filename=customer.prf Options=options ; OpenAs=... is needed only to open Full-ISAM files. OpenAs=Full-ISAM ; In a "[LinkOneToMany]" section, the optional "NULLRecord=" line ; controls what happens if the linked record does not exist. If set ; to TRUE, a single row with NULL values for the linked fields will be ; returned if the record is not found. The "NULLRecord" option can only ; be used in a "[LinkOneToMany]" section. NULLRecord=True ; The file specification must be followed by a "UseIndex=" line and one ; or more "KeyPart=" lines. The "UseIndex=" line selects which index ; in the linked table profile will be used to find the linked record. ; To link by a record number field defined using "RecordNum=", ; the index name must be "ByRecordNumber". The "KeyPart=" lines select ; fields that supply values for the index key. The fields must match ; names defined or imported in previous "[Record]", "[Index..]", ; "[LinkedRecord]", or "[LinkOneToMany]" sections. For "[LinkedRecord]" ; sections, the key parts must match all of the key parts of the index ; specified by the "UseIndex=" line. For "[LinkOneToMany]" sections, ; the key parts must match all of the leading key parts of the index ; except for those trailing parts that are used to make each of the ; linked "many" records unique. The key parts that are not specified ; must be imported as data fields either explicitly or implicitly. ; ; KeyPart= ; UseIndex=BYCUSTOMER KeyPart=CUSTOMERNUMBER ; All of the fields from the linked record file will be imported unless ; "Field=" lines are specified. If "Field=" lines are used, only the ; specified fields will be imported. ; ; Field= ; ; If imported field names conflict with existing field names, an ; alternate form of the "FIELD=" line can specify a new name for the ; field followed by the name used in the linked file: ; ; Field=,> Field=NAME Field=ADDRESS Field=CITY Field=STATE Field=ZIPCODE Field=PHONENUMBER SQL syntax supported by the ODBCdL4 server and driver ===================================================== In the following syntax definitions, the character "|" indicates alternate selections. Enclosing braces ("{ x x x }") surround items that are optional. An ellipsis ("...") indicates that the previous term can be repeated one or more times. Words and symbols used in the syntax are enclosed in double quotes (the double quotes themselves are NOT part of the syntax unless otherwise specified). ::= ::= "SELECT" ::= } ::= { "ALL" | "DISTINCT" } "*" | { "ALL" | "DISTINCT" } ::= } ... ::= ">" | ">=" | "<" | "<=" | "=" | "<>" ::= | "?" | "USER" ::= "+" | "-" | ::= "*" | "/" | ::= { "+" | "-" } ::= ( ) | | | | ::= | ::= "FN" ")*--" ::= "--*(VENDOR(MICROSOFT),PRODUCT(ODBC)" ::= "{" "FN" "}" ::= "(" ")" | "(" ")" | "POSITION" "(" "IN" ")" | "EXTRACT" "(" "FROM" ")" ::= "COUNT" "(" "*" ")" | "AVG" "(" ")" | "MAX" "(" ")" | "MIN" "(" ")" | "SUM" "(" ")" | "COUNT" "(" ")" ::= | | "?" | "USER" | |