Monday November 14, 2005 ODBCdL4 Server For Unix 1.1.1 Maintenance Release All Rights Reserved. Copyright (c) 2005 - 2005 by: Dynamic Concepts, Inc. Aliso Viejo, CA 92656 USA Email address: techsupport@dynamic.com Information: www.unibasic.com Downloads: 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 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 Unix server and sends that data over a TCP/IP network connection to the Windows 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 Passport 4.1 or later must be installed in order to use release 1 of the ODBCdL4 server. 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 Supports reading string, numeric, and date values from indexed contiguous and contiguous files. o Supports files containing multiple record types by allowing selection of records based on record fields, key fields, or record numbers. o Supports packed data in both record fields and in keys. o Supports conversion of specified values in records to SQL NULL values. 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 or ftp.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". 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 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 text 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. The "Name=" below 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 ("FILE=/acct/file"). Note that ignoring record locks may cause ODBCdL4 to read records that have been partially updated by an application. For example, 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 beta 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=... may not be necessary, depending on the database in question 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 ; DDDDDBaseDate=... is optional and is needed only if "DDDDD" format date ; values do not use the default value of 1968. DDDDDBaseDate=1968 ; Entries for fields occur in the "[Record]" section and look like: ; ; 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. ; 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 Strip trailing spaces from string fields ; after reading them from the full-ISAM file. ; 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 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 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 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. ; ; 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 by that field. ; A record number field must be defined for contiguous 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..]" 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 Strip trailing spaces from string fields ; after reading them from the full-ISAM file. ; 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"). ; 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 ; 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 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. ; STR(mask) Convert view numeric field from a character ; string according to the mask "mask" which ; consists of characters that 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 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 [Index3] Name=BYINVOICE KeyPart=INVOICE,0,6 Notes ===== o Like other Dynamic Concepts products for Unix, ODBC for UniBasic/dL4 requires a functioning Passport daemon to license the system, and an SSN specific to that license number to authorize use of the software. In the absence of SSN authorization, a single-user demo mode is invoked. The Passport software must be version 4.1 or later. The Passport software is distributed by Dynamic Concepts as a separate installable product. You can download the latest version of the Passport from the Dynamic Concepts web site, www.unibasic.com, or ftp site, ftp.dynamic.com.