Interactive COBOL ODBC Driver 4.89 15-Dec-2015 Interactive COBOL provides a common development and runtime environment on Windows and UNIX that allows complete applications (including code and data) to be moved from one system to another without change. Under UNIX, the Interactive COBOL ODBC Driver (ICODBC.SO) for UNIX is a 32-bit ODBC Driver that provides an ODBC-compliant interface via icodbc.so. This interface can be used with the unixODBC Driver Manager. One program in particular that makes use of this interface is the JDBC-ODBC Bridge under the Java Runtime. More can be found on this product at the end of this readme. Under Windows, the Interactive COBOL ODBC Driver (ICODBC32) for Windows is a fully functional 32-bit ODBC Driver (SQL-92 Entry level compliant) providing access to Interactive COBOL data records stored in INDEXED ORGANIZATION files. Through this mechanism it is possible for ODBC-enabled programs (e.g., Crystal Reports, Visual Basic, PowerBuilder, Microsoft Access, etc...) to access legacy Interactive COBOL INDEXED file data records as if they were rows of a table of an SQL relational database. General Information ------------------- Under Windows, the Interactive COBOL ODBC Driver is a 32-bit product. It is accessible from 32-bit ODBC-enabled applications. The Interactive COBOL ODBC Driver was built using the Microsoft Visual C++ 7.1 compiler with the Microsoft ODBC Software Development Kit (SDK) version 3.51 and the SYWARE Dr. DeeBee ODBC Driver Kit (Gold Edition). Portions of the product are copyrighted by SYWARE Inc. and Microsoft. The driver will run under x64 editions but must be loaded/called from 32-bit executables. Under UNIX, the inode number is used for commoning. Under Windows, for the purpose of buffering file data within an application, multiple opens of the same local/redirected file are commoned by using the lower case rendition of the file name supplied to the driver. It is important to always specify the identical filename (or alias) and not a different alias to refer to a particular file. This applies to database and table definition files as well as data (INDEXED) file names. On UNIX and Windows, the Interactive COBOL ODBC Driver optionally connects to the shared memory area created and initialized by the Interactive COBOL System Executive Program (ICEXEC). Multi-user file sharing, buffering, and record locking are handled more efficiently through this mechanism as opposed to a stand-alone (single-user) environment. This version of the ODBC driver will only connect with shared memory areas created by ICEXEC Revision 4.8+ (and later); if an earlier version of the shared memory area exists the driver will not connect. To use the Interactive COBOL ODBC Driver, a valid ICOBOL ODBC Driver license (ICODBCDR) must be available via the license server (ICPERMIT). To allow write access (INSERT, UPDATE, etc.) to files, the WRITE option must also be present on the ICODBCDR license, otherwise only read access is allowed. (64-bit Windows) If you are using 64-bit Windows, ICODBC access is ONLY available from 32-bit processes. If you're using 64-bit Windows and want to edit the configuration of the 32-bit ODBC drivers, you can do this by launching the program %SystemRoot%\SysWOW64\odbcad32.exe, which brings up the 32-bit ODBC console. The default ODBC interface, the one invoked from the Control Panel, is 64-bit only. (64-bit Linux) If you're using 64-bit Linux ICODBC access is ONLY available from 32-bit processes. Installation ------------ * Under UNIX, the Interactive COBOL ODBC Driver is always loaded when un-taring from the release file. See the section at the bottom of this readme for more on the UNIX version. The actual shared object can be made accessible to all programs via the installic script located in the examples sub-directory. Just execute: examples/installic icodbc This will install icodbc.so in the /usr/lib directory. * Under Windows, the Interactive COBOL ODBC Driver and related ODBC components can be installed as an optional component selection from the Interactive COBOL Full installation program. In order to install it, you must select it from the "Select Components" setup dialog box by checking the box next to the "ODBC Driver" item. Changes in 4.86-4.89 -------------------- None. Changes in 4.85 --------------- 1. Linux ICODBC.SO was not loading. (Broke in 4.80). Changes in 4.81 - 4.84 ---------------------- None. Changes in 4.80 ---------------- 1. Under Windows when using a GUI application or ICODBC from a GUI application, pop up error message boxes will be disabled if both auditing and quiet are enabled. Changes in 4.71 - 4.72 ---------------------- None. Changes in 4.70 ---------------- 1. Under Windows, an ICODBC-only install was installing the IC Services Manager. The IC Services Manager is not needed as no services are installed. Changes in 4.54 - 4.55, 4.60 ---------------------------- None. Changes in 4.53 ----------------- 1. Under Windows, the ICODBC Driver setup (accessed from the ODBC Data Source Administrator) now has a browse (...) selection to find the needed .xdb file. 2. Under Windows, some sample files have been released in the examples subdirectory that can be used to see a very simple .xdb/.xdt description for a simple isam file. These files are: sample_odbc.xdb, sample_odbc.xdt, sample_file.xd, sample_file.nx A very simple set up is: A) Start the ODBC Administrator B) From the User or System tab click ADD C) Select the Interactive COBOL ODBC Driver D) click Finish E) the ICODBC Setup screen will be shown, name the Data Source "SampleFile" F) now browse (the ...) to find the sample_odbc.xdb file. G) click OK You have now defined the ODBC Data Source SampleFile. This data source can be used where ever an ODBC datasource is allowed/requested. The sample_odbc.* files can be viewed with Notepad as they are text files that are used to describe the data as stored in the sample_file Indexed file. Changes in 4.52 ----------------- 1. The ICODBC Driver will now append the .xdb and .xdt extensions to database and table filenames only when required. Changes in 4.43 - 4.44, 4.50 - 4.51 ----------------------------------- None. Changes in 4.42 ---------------- 1. When using auditing and tracing an error could be given. Changes in 4.40 - 4.41 ---------------------- None. Changes in 4.25, 4.30 - 4.32, 4.40 - 4.41 ----------------------------------------- None. Changes in 4.24 --------------- * Incorporated the latest (through September 25, 2009) bug fixes in the Dr. DeeBee ODBC Driver Kit (Gold Edition) from SYWARE. No 4.23 revision ---------------- n/a Changes in 4.12, 4.20 - 4.22 ---------------------------- None. Changes in 4.11 --------------- 1. The Interactive COBOL ODBC Driver now supports tracing. In trace mode, the routine, passed parameters, and results are shown in the audit log. Tracing should be used sparingly as it will build a rather large log. Mainly useful for debugging. 2. The Interactive COBOL ODBC Driver now reads the environment variable ICODBC32 at startup to process audit, quiet, and trace options. Valid options are: -a[:aflag] Audit to icodbc32.lg -A path[:aflag] Audit to path, or path/icodbc32.lg if path is a directory aflag is a|b|d|da|db|p|pa|pb|t|ta|tb|u|ua|ub a=append, b=backup, d=datestamp, p=process-id, t=timestamp, u=username -q Enable Quiet operation -t Enable tracing To enable auditing for the ICODBC driver set the ICODBC32 environment variable to "-a:p". Then an audit log "icodbc32_.lg" will be created in the current directory for all connections. Setting the ICODBC32 envirnment variable to "-a" would create an audit log as "icodbc32.lg" in the current directory for each connection. This would over write multiple logs. Setting the -q quiet switch will disable any output to standard-out or standard-error. Setting the -t trace switch will enable tracing of the ODBC calls to the ICODBC driver, if auditing is enabled. Otherwise the trace switch is ignored. Changes in 4.00 - 4.10 ---------------------- None. Changes in 3.66 - 3.67 ---------------------- None. Changes in 3.65 --------------- * Incorporated the latest (through November 28, 2007) bug fixes in the Dr. DeeBee ODBC Driver Kit (Gold Edition) from SYWARE. Changes in 3.61 - 3.64 ---------------------- None. Changes in 3.60 --------------- 1. When dealing with binary numbers greater than 32-bits the driver could create incorrect lookup values. (Update 02) Changes in 3.57 --------------- None. Changes in 3.56 --------------- 1. At times, when encountering locked records, ICODBC could cause the system to hang. Changes in 3.54 - 3.55 ---------------------- None. Changes in 3.53 --------------- * Under Windows, at times ICODBC would not convert 64-bit integers correctly to a character string. Changes in 3.51 - 3.52 ---------------------- None. Changes in 3.50 --------------- * Incorporated the latest (through March 17, 2005) bug fixes in the Dr. DeeBee ODBC Driver Kit (Gold Edition) from SYWARE. Changes in 3.43 - 3.48 ---------------------- None. Changes in 3.42 --------------- 1. Under Windows, the ICODBC and the ICAPI products were not prompting for username/password when using ICNETD. (Broke in 3.40). Changes in 3.41 --------------- None. Changes in 3.40 --------------- * Incorporated the latest (through Movember 07, 2003) bug fixes in the Dr. DeeBee ODBC Driver Kit (Gold Edition) from SYWARE. * Revision number changed to match ICOBOL revision. * Under UNIX, the name of the actual shared object has now changed. It is now icodbc.so. (It depends on the shared object icsys.so. of the same revision. So these shared objects should be installed as a pair.) * Under UNIX, if a previous version had been installed since the name has been changed, then you need to edit the odbcinst.ini file and change the name of the icodbc driver. * Under UNIX, at times an error would be returned when building a result-set that needed to be sorted. * Under UNIX, returns error message strings better. * Under Windows, the icodbc.dll driver should now be thread-safe. * Under Windows, icodbc32.dll depends on icsys.dll of the same revision. Changes previous to 3.40 removed. Using the Driver ---------------- In order for the Interactive COBOL ODBC driver to provide for the INDEXED to SQL data translation, the application builder must supply information regarding the database, the tables comprising the database, and the rows and columns comprising each table. Simply speaking, an INDEXED file can be viewed as a table (or tables) comprised of a set of rows (records), each one specifying a value for each column (field). The ICODBC driver utilizes two ASCII text files, which are formatted according to Microsoft Windows initialization (.ini) file conventions, to describe the appropriate view of a database and the tables which it contains. The two files are the .xdb (Database Definition) file, which describes a database; and the .xdt (Table Definition) file, which describes a table. For a given database, the .xdb file explicitly specifies the number of tables comprising the database, defining the name, the INDEXED file, and the Table definition file for each one. Although there is an obvious relationship between an INDEXED file and a Table Definition file, there is no forced association required by the driver. Thus it is possible to describe different databases using the same Table Definitions paired with different instances of INDEXED files. For a table, the .xdt file explicitly specifies the number of columns comprising the table, defining the name, the position, size, and type of the data field corresponding to each. Although there may be obvious relationships between the data fields of a record and the columns of a table, the driver does not enforce a particular correspondence. Thus it is possible to describe different columns of a table using the same data field, or to describe the columns of a table using only some of the data fields available. Documentation ------------- Documentation for the ICODBC Driver is included in the Language Reference & Developer's Guide. Driver Limitations ------------------ * Entry level SQL-92 compliant, with some additional Intermediate and/or Full level functionality. See the SQL grammar section at the end of this file for the SQL grammar supported. Some modification statements (CREATE or DROP) are not supported semantically, although they are supported syntactically. * SQLBrowseConnect, SQLTablePrivileges, SQLColumnPrivileges, SQLProcedures, and SQLProcedureColumns are not supported. These ODBC API calls are not SQL-92 compliant CLI Calls and are not commonly used. * Character and binary values supplied for parameterized queries (SELECT * FROM EMPLOYEE WHERE NAME = ?) are limited to 255 bytes. * Interval types are not supported. * Qualifiers or owners are not allowed on databases, tables, etc. * Transactions are not supported. * Only SQL_CHAR, SQL_NUMERIC, SQL_BINARY, SQL_TIME, SQL_DATE, and SQL_TIMESTAMP are supported. * The following are the (maximum) limits of various implementation defined elements: Character Literal Length...........255 Binary Literal Length..............255 Database Name Length...............127 Column Name Length..................63 Index Name Length...................63 Table Name Length...................63 Key Name Length.....................63 User Name Length....................63 Password Length.....................63 Number of Columns in Order By.......20 Number of Columns in a Key..........15 Number of Columns in Index..........15 Number of Foreign Keys in a Table...15 * Queries that specify columns which are components of an INDEXED key are satisfied based on the internal ordering of the key, which may not be equivalent to the external ordering. The Database Definition File (.xdb) ----------------------------------- Syntax ------ [Database] OpenMode= NumTables= BaseYear= BaseYearPivot= EpochYear= EpochDay= EpochTick= ProxyDate= ProxyTime= ProxyTimestamp= [Tables] ... [] DataFile= TableFile= ... [] DataFile= TableFile= General Rules ------------- * The "OpenMode" key is optional and serves to specify the open mode for the data files comprising the database. The value of must be one of either "INPUT", "OUTPUT", "I-O", or "EXTEND". If this key is not specified, a default value of "INPUT" is implied. If the value of is either "OUTPUT", "I-O", or "EXTEND", the "WRITE" option must be present in the license authorizing the driver. This value can be overridden by the presence of an "OpenMode" key in the [Table] section of the individual Table Definition files (see below). * The "NumTables" key is required and the value of must match the number of table names listed in the [Tables] section and the number of [] sections. * The "BaseYear" key is optional and serves to specify the century year to be added to the two or three digit year values of the DAY and DATE data types described below. The value of must be a valid numeric edited string literal, and must specify a year greater than or equal to 1600 (up to 32700) that is a century (i.e., divisible by 100). If this key is not specified, a default value of 1900 is implied. * The "BaseYearPivot" key is optional and serves to specify the two-digit year value of the DAY and DATE data types described below, to which, if less than, a century (i.e., 100 years) will be added, in addition to the value of . The value of must be a valid numeric edited string literal, and must be greater than 0 and less than 99. If this key is not specified, a default value of 0 is implied. * The "EpochYear" key is optional and serves to specify the starting year of time (as represented by a zero value) for the corresponding epoch data types described below. The value of must be a valid numeric edited string literal, and must specify a year greater than (up to 32767) or equal to 1601. If this key is not specified, a default value of 1601 is implied. * The "EpochDay" key is optional and serves to specify the starting day of time (as represented by a zero value) for the corresponding epoch data types described below. The value of must be a valid numeric edited string literal, and must specify a day greater than or equal to 1 and less than or equal to 365 (or 366 if represents a leap year). If this key is not specified, a default value of '01-01' (i.e., January 1) is implied. * The "EpochTick" key is optional and serves to specify the discrete unit of time that passes between single value increments of epoch data types described below. The value of must be must be one of either "SECOND", "BISECOND", "MINUTE", or "DAY". If this key is not specified, a default value of "SECOND" is implied. * The "ProxyDate" key is optional and serves to specify a particular value to be substituted for otherwise invalid values when retrieving SQL_DATE data. The value of must be of the form 'yyyy-mm-dd' (e.g. "0001-01-01"). * The "ProxyTime" key is optional and serves to specify a particular value to be substituted for otherwise invalid values for SQL_TIME data. The value of must be of the form 'hh:mm:ss' (e.g. "00:00:00"). * The "ProxyTimestamp" key is optional and serves to specify a particular value to be substituted for otherwise invalid values for SQL_TIMESTAMP data. The value of must be of the form 'yyyy-mm-dd hh:mm:ss.ff' (e.g. "0001-01-01 00:00:00.00"). * There must be an identically named section for each specified in the [Tables] section. * The must be a valid pathname specifying an ICOBOL INDEXED file. It may be a 'relative' (as opposed to 'absolute') pathname, in which case the pathname specifier for the Database Definition (.xdb) file in the Data Source will be automatically prefixed to it. It may also be a URL specification as documented for ICNETD in the Interactive COBOL Utilities Manual. It may contain a single variable name reference to be substituted with an assigned value when a connection to the database is established. Variable names are delimited by a leading and trailing percent character ('%'). * The must be a valid pathname specifying a valid Table Definition (.xdt) file, although the ".xdt" extension must not be present. It may be a 'relative' (as opposed to 'absolute') pathname, in which case the pathname specifier for the Database Definition (.xdb) file in the Data Source will be automatically prefixed to it. It may also be a URL specification as documented for ICNETD in the Interactive COBOL Utilities Manual. It may contain a single variable name reference to be substituted with an assigned value when a connection to the database is established. Variable names are delimited by a leading and trailing percent character ('%'). Example ------- [Database] NumTables=4 OpenMode=I-O ; AOS/VS uses biseconds since 1968-01-01 00:00:00 ; EpochYear=1968 ; EpochTick=BISECOND ; UNIX uses seconds since 1970-01-01 00:00:00 ; EpochYear=1970 ; EpochTick=SECOND ; MacOS uses seconds since 1903-01-01 00:00:00 ; EpochYear=1904 ; EpochTick=SECOND ; CBS uses days since 1876-12-31 00:00:00 ; EpochYear=1876 ; EpochDay=366 ; EpochTick=DAY [Tables] Customers Companies Orders Products [Customers] TableFile=c:\application\odbcdesc\anycust DataFile=c:\application\livedata\%this%cust [Companies] TableFile=c:\application\odbcdesc\anycomp DataFile=c:\application\livedata\%this%comps [Orders] TableFile=c:\application\odbcdesc\anyorder DataFile=c:\application\livedata\%this%order [Products] TableFile=c:\application\odbcdesc\product DataFile=c:\application\livedata\products The Table Definition File (.xdt) -------------------------------- Syntax ------ [Table] OpenMode= NumColumns= MaxRecordSize= MinRecordSize= PrimaryKeyName= NumSelectors= [Selector] Type= Position= Length= Precision= Scale= Value= Relation= [Selectors] ... [] Type= Position= Length= Precision= Scale= Value= Relation= ... [] Type= Position= Length= Precision= Scale= Value= Relation= [Columns] ... [Primary Key] ... [Foreign Keys] = ... = [] ... [] Type= Position= Length= Precision= Scale= Picture= Suppress= Padding= Default= ... [] Type= Position= Length= Precision= Scale= Picture= Suppress= Padding= Default= General Rules ------------- * The "OpenMode" key is optional and serves to specify the open mode for the data file of the table. The value of must be one of either "INPUT", "OUTPUT", "I-O", or "EXTEND". If this key is not specified, the value specified by the "OpenMode" key in the [Database] section of the Database Definition file is implied. * The "NumColumns" key is required and serves to specify the number of columns in the table. The value of must match the number of column names listed in the [Columns] section and the number of [] sections. * The values of and must respectively match the actual maximum and minimum record sizes of the INDEXED file; and they must be the same if the records are fixed-length. * The "PrimaryKeyName" key is optional and serves to identify the primary key for the purposes of foreign key reference. * The [Primary Key] section is optional and serves to identify the columns of the table which comprise the primary key. This section must be specified, and all of the columns specified must be either "ALPHABETIC" or "ALPHANUMERIC", in order for the Microsoft Jet Database Engine (see above) to be able to create a dynaset over rows of the table. * The [Foreign Keys] section is optional and serves to identify the tables whose primary keys are referenced by foreign keys from the table. * There must be an identically named key in the [Tables] section of the Database Definition (.xdb) file be for each . * There must be an identically named section in the Table Definition (.xdt) file for each . These sections serve to identify the columns of the table which comprise the foreign key. * The "NumSelectors" key is optional and serves to specify the number of record selectors for the table. A record selector specifies a subset of the records in the INDEXED file which are to be considered as rows in the table. The value of must match the number of selector names listed in the [Selectors] section and the number of [] sections. * The [Selector] section is optional and serves to specify the simple definition of only one record selector for the table. It may not be present when the "NumSelectors" key is present. * The "Type" key is required for the [Selector], [], or the [] section if specified, and serves to specify the data storage type of the item. * The value of must be one of either "BYTE", "ALPHABETIC", "ALPHANUMERIC", "DISPLAY", "TRAILING OVERPUNCH", "TRAILING SEPARATE", "LEADING OVERPUNCH", "LEADING SEPARATE", "UNSIGNED DISPLAY", "COMP", "UNSIGNED COMP", "COMP-3", "UNSIGNED COMP-3", "COMP-5", "UNSIGNED COMP-5", "DAY", "COMP DAY", "DATE", "COMP DATE", "COMP DATE GROUP", "TIME", "COMP TIME", "COMP TIME GROUP", "FULLDATE", "EPOCH TIMESTAMP", or "COMP EPOCH TIMESTAMP", matching the ICOBOL data type of the corresponding field. * The "Value" key is required for the [Selector] or [] section if specified, and serves to specify the value to be used in determining the desired subset. * The value of may be any character string literal (unquoted) if the of the item is "ALPHABETIC" or "ALPHANUMERIC". Otherwise, if the of the item is "BYTE", the value of must be a valid hexadecimal string literal (unquoted). Otherwise, if the of the item is "DISPLAY", "TRAILING OVERPUNCH", "TRAILING SEPARATE", "LEADING OVERPUNCH", "LEADING SEPARATE", "UNSIGNED DISPLAY", "COMP", "UNSIGNED COMP", "COMP-3", "UNSIGNED COMP-3", "COMP-5", or "UNSIGNED COMP-5", the value of must be a valid numeric edited string literal. Otherwise, if the of the item is "DAY", "COMP DAY", "DATE", "COMP DATE", or "COMP DATE GROUP", the value of must be a character string literal (unquoted) of the form 'yyyy-mm-dd'. Otherwise, if the of the item is "TIME", "COMP TIME", or "COMP TIME GROUP", the value of must be a character string literal (unquoted) of the form 'hh:mm:ss'. Otherwise, if the of the item is "FULLDATE", "EPOCH TIMESTAMP", or "COMP EPOCH TIMESTAMP", the value of must be a character string literal (unquoted) of the form 'yyyy-mm-dd hh:mm:ss.ff'. * The "Relation" key is optional for the [Selector] or [] section if specified, and serves to specify the comparison operation to be applied in determining the desired subset. The value of must be one of either "EQ", "NE", "GT", "GE", "LT", or "LE" and specifies the relationship between the value of the selector in a record and which must be true for a record to be included in the desired subset. If this key is not specified, a default value of "EQ" is implied (i.e., records for which the value of their selector field is equal to the value of are included in the subset). * The "Position" key is required for the [Selector], [], or the [] section if specified, and serves to specify the data byte position of the item. * The value of must be the byte position (one-based) within a record to the data field corresponding to the column. * The "Length" key is required for either the [Selector] or the [] section if specified, and serves to specify the data byte length of the item. * The value of must be the length in bytes within a record of the data field corresponding to the column. * For a section where the value of is "BYTE", "ALPHABETIC" or "ALPHANUMERIC", neither the "Precision" key nor the "Scale" key may be present. * For a section where the value of is not "BYTE", "ALPHABETIC" or "ALPHANUMERIC", the "Precision" key must be present. * For a section where the value of is "DAY", "COMP DAY", "DATE", "COMP DATE", "COMP DATE GROUP", "TIME", "COMP TIME", "COMP TIME GROUP", "FULLDATE", "EPOCH TIMESTAMP", or "COMP EPOCH TIMESTAMP" the "Scale" key must not be present. * For a section where the value of is "COMP DATE GROUP" or "COMP TIME GROUP", the value of must be the total number of decimal digits in all the elementary items of the group. * The value of must be the total number of decimal digits to the left and right of the decimal point defined for the data field corresponding to the column. (e.g. if the field is defined as "PIC 9999V99", the value is 6). For COMP items you may need to adjust this value depending on the values actually stored in this data item. For example, a PIC 9(4) COMP would need a precision=5 to hold 0-65535 if values above 9999 are allowed. * The value of must be the number of decimal digits to the right of the decimal point defined for the data field corresponding to the column. (e.g. if the field is defined as "PIC 9999V99", the value is 2). * The "Picture" key is optional and may be present only for a section where the value of is "DATE", "COMP DATE" or "COMP DATE GROUP". The value of must be one of either "YYYYMMDD", "YYYYDDMM", "MMDDYYYY", "MMYYYYDD", "DDMMYYYY", "DDYYYYMM", "CCYYMMDD", "CCYYDDMM", "MMDDCCYY", "MMCCYYDD", "DDMMCCYY", or "DDCCYYMM" if the value of is 8; it must be one of either "YYYMMDD", "YYYDDMM", "MMDDYYY", "MMYYYDD", "DDMMYYY", or "DDYYYMM" if the value is 7; and it must be one of either "YYMMDD", "YYDDMM", "MMDDYY", "MMYYDD", "DDMMYY", or "DDYYMM" if the value is 6. * The "Suppress" key is optional; but if present, the value of must be the numeric value (0 to 255) which when present in all bytes of the data field indicates that the value of the column is considered empty or null. * The "Padding" key is optional; but if present, the value of must be the numeric value (0 to 255) which will be used to pad the value of the column to its full length of when a shorter value is specified. * For a section where the value of is not "ALPHABETIC" or "ALPHANUMERIC", the "Padding" key must not be present. * The "Default" key is optional and serves to specify the default value to be stored for the column if no value is specified as part of the INSERT statement. NOTE: If you plan on adding records (rows) to a database, consider setting this value, especially for DATE / TIME columns, as on an INSERT sometimes a "null" record is inserted and then the values that the user had specified are individually PUT into the columns, thus the INSERT would fail with an Invalid data-type-value for a DATE column if the "Default" is NOT specified. * There must be an identically named section for each specified in the [Columns] section. Example ------- [Table] NumColumns=6 MinRecordSize=100 MaxRecordSize=100 PrimaryKeyName=CustomerKey [Columns] CustomerId Company Address City State ZipCode [Primary Key] CustomerId [Foreign Keys] Companies=CompanyKey [CompanyKey] Company [CustomerId] Type=UNSIGNED DISPLAY Position=1 Length=10 Precision=10 Scale=0 [Company] Type=ALPHANUMERIC Position=11 Length=20 [Address] Type=ALPHANUMERIC Position=31 Length=40 [City] Type=ALPHANUMERIC Position=71 Length=20 [State] Type=ALPHABETIC Position=91 Length=2 [ZipCode] Type=UNSIGNED DISPLAY Position=93 Length=5 Precision=5 Scale=0 SQL Grammar Supported --------------------- statement ::= CREATE create | DROP drop | SELECT select orderby | INSERT insert | DELETE delete | UPDATE update create ::= TABLE tablename ( createcols ) | INDEX indexname ON tablename ( indexcolumns ) indexcolumns ::= indexcolumn | indexcolumn , indexcolumns indexcolumn ::= columnname asc createcols ::= createcol , createcols | createcol createcol ::= columnname datatype | columnname datatype ( integer ) | columnname datatype ( integer , integer ) drop ::= TABLE tablename | INDEX indexname select ::= selectcols FROM tablelist where groupby having delete ::= FROM tablename where insert ::= INTO tablename insertvals update ::= tablename SET setlist where setlist ::= set | setlist , set set ::= columnname = NULL | columnname = expression insertvals ::= ( columnlist ) VALUES ( valuelist ) | VALUES ( valuelist ) | ( columnlist ) VALUES ( SELECT select ) | VALUES ( SELECT select ) columnlist ::= columname , columnlist | columname valuelist ::= NULL , valuelist | expression , valuelist | expression | NULL selectcols ::= selectallcols * | selectallcols selectlist selectallcols ::= | ALL | DISTINCT selectlist ::= selectlistitem , selectlist | selectlistitem selectlistitem ::= expression | expression aliasname | expression AS aliasname | aliasname.* where ::= | WHERE boolean having ::= | HAVING boolean boolean ::= and | and OR boolean and ::= not | not AND and not ::= comparison | NOT comparison comparison ::= ( boolean ) | colref IS NULL | colref IS NOT NULL | expression LIKE pattern | expression NOT LIKE pattern | expression IN ( valuelist ) | expression NOT IN ( valuelist ) | expression op expression | EXISTS ( SELECT select ) | expression op selectop ( SELECT select ) | expression IN ( SELECT select ) | expression NOT IN ( SELECT select ) expression BETWEEN expression AND expression expression NOT BETWEEN expression AND expression selectop ::= | ALL | ANY op ::= > | >= | < | <= | = | <> pattern ::= string | ? | USER expression ::= expression + times | expression - times | times times ::= times * neg | times / neg | neg neg ::= term | + term | - term term ::= ( expression ) | colref | simpleterm | aggterm | scalar scalar ::= scalarescape | scalarshorthand scalarescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) FN fn )*-- scalarshorthand ::= { FN fn } fn ::= functionname ( valuelist ) | functionname () | POSITION ( expression IN expression ) | EXTRACT ( expression FROM expression ) aggterm ::= COUNT ( * ) | AVG ( expression ) | MAX ( expression ) | MIN ( expression ) | SUM ( expression ) | COUNT ( expression ) simpleterm ::= string | realnumber | ? | USER | date | time | timestamp groupby ::= | GROUP BY groupbyterms groupbyterms ::= colref | colref , groupbyterms orderby ::= | ORDER BY orderbyterms orderbyterms ::= orderbyterm | orderbyterm , orderbyterms orderbyterm ::= colref asc | integer asc asc ::= | ASC | DESC colref ::= aliasname . columnname | columnname tablelist ::= tablelistitem , tablelist | tablelistitem tablelistitem ::= tableref | outerjoin outerjoin ::= ojescape | ojshorthand ojescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) OJ oj )*-- ojshorthand ::= { OJ oj } oj ::= tableref LEFT OUTER JOIN tableref ON boolean | tableref LEFT OUTER JOIN oj ON boolean | tableref INNER JOIN tableref ON boolean | tableref INNER JOIN oj ON boolean tableref ::= tablename | tablename aliasname indexname ::= identifier functionname ::= identifier tablename ::= identifier datatype ::= identifier columnname ::= identifier aliasname ::= identifier identifier ::= an identifier (identifiers containing spaces must be enclosed in double quotes) string ::= a string (enclosed in single quotes) realnumber ::= a non-negative real number (including E notation) integer ::= a non-negative integer date ::= dateescape | dateshorthand dateescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) d dateval )*-- dateshorthand ::= { d dateval } dateval ::= a date in yyyy-mm-dd format in single quotes (for example, '1996-02-05') time ::= timeescape | timeshorthand timeescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) t timeval )*-- timeshorthand ::= { t timeval } timeval ::= a time in hh:mm:ss format in single quotes (for example, '10:19:48') timestamp ::= timestampescape | timestampshorthand timestampescape ::= --*(*VENDOR(MICROSOFT),PRODUCT(ODBC) ts timestampval )*-- timestampshorthand ::= { ts timestampval } timestampval ::= a timestamp in yyyy-mm-dd hh:mm:ss[.ffffff] format in single quotes (for example, '1996-02-05 10:19:48.529') Notes and Warnings ------------------ Microsoft Access ---------------- If you use Access to link to a table only the first 101 records will be retrieved. If you had imported the table all the data is retrieved. That is the default behavior for Jet, (the actual database engine under Access) as to records returned when linking to an external data source. You can: a) Position to the last record and hit Shift-F9 for the next block of records to be retrieved (usually in groups of 100 records but sometimes not) or b) Create/edit the Access configuration table MSysConf so that the default is greater. See your Access documentation for more details. Crystal Reports --------------- If you are using Crystal Reports 9+ you may get an error message when working with more that 1 table: failed to open a rowset Details: 42000: [Envry Corporation [ICOBOLODBC Driver] Unexpected extra token: INNER This is because Crystal did not generate an ODBC compliant SQL INNER JOIN statement. This is a known bug in revision 9. Until Crystal fixes this, to get around the problem you can: 1) go back to Crystal revision 8.5 2) manually edit the SQL statement so that it conforms to the ODBC standard for an inner join. Crystal has a tendency to generate SQL statements that are not ODBC standard compliant. UNIX ICODBC Driver ------------------ The ICODBC Unix driver is intended to provide an ODBC interface to those UNIX applications that can make use of a shared object interface to ODBC. The ODBC "clue" logic under UNIX is provided with the unixODBC project. unixODBC is available from www.unixodbc.org and must be installed before using the ICODBC Unix driver (icodbc.so). UnixODBC is not so much an end user program, but rather an intermediary between a program (application)and one or more databases. It provides the ODBC Driver Manager and Administrator. In this case the database is ICISAM files provided via icodbc.so. The isql program that comes with unixODBC can be used to perform simple connections and queries to test that ICODBC is installed correctly. One application that makes use of the unixODBC project is the JDBC-ODBC Bridge that is provided with the Java 2 Runtime environment. A java runtime can be downloaded from www.java.sun.com/products. The JDBC-ODBC Bridge enable java programs to access ODBC data when a JDBC compliant interface is not avaliable to access the same data. Under Java, the JDBC-ODBC Driver can be loaded with the ClassforName("sun.jdbc.odbc.JdbcodbcDriver") A Connection to an ICOBOL Isam database can be made via: Connect con = DriverManager.getConnection( jdbc.odbc., , ); where is the DataSetName for the ICOBOL database. unixODBC -------- Getting Started UnixODBC is available in source code only. This means that you download a tar file (or zipped tar file) from http://www.unixODBC.org, extract it, compile it, and install it. Installing unixODBC As mentioned before, get the source tar file from http://www.unixodbc.org. As root, move the tar file to /op or /usr/local or where ever you want the source to reside. If zipped, then unzip the file. Untar the file and run the following commands from the command line in the unixODBC source directory: ./configure make make install Assuming that you have all the libraries and tools that it needs, you should be breezing through this compile. UnixODBC takes quite a while to compile, actually all of these packages do. Relax and enjoy it. After installing you will probably have to set the path for shared objects (LD_LIBRARY_PATH on Linux). Installing Prerequisets: Under UNIX, there is no ODBC Administrator so the data-sources must be configured in a text file. System versus User ODBC distingushes between two types of ini files. System ini files are designed to be accessable but not modifable by any user, and user files are private to a particular user, and may be modified by that user. The system files are odbcinst.ini and odbc.ini (note no leading dot), and the user file is ~/.odbc.ini in each user's home directory (note leading dot). The system file odbcinst.ini contains information about ODBC drivers available to all users, and the odbc.ini file contains information about DSN's available to all users. These "System DSN's" are useful for application such as web servers that may not be running as a real user and so will not have a home directory to contain a .odbc.ini file. A good example of this is Apache and PHP with ODBC support. When the http server is first started it calls SQLAllocEnv as root. It then at a later time changes to the specified user (in my case nobody) and calls SQLConnect. If the DSN's was not a system DSN then this fails. The ~/.odbc.ini in the user's home directory are "User DSN's". These are only useful for cases of testing or when you do not need to share datasets. The unixODBC library uses the odbcinst.ini file to administrator the driver manager. Again this file is in a .ini format and has the following format. odbcinst.ini ------------ This ini file simply lists all installed drivers. It is located in /usr/local/etc/odbcinst.ini. The syntax is simple; a name followed by a property which tells us the drivers file name. For example; [Sybase 11] Comment = Super Duper Sybase Server Driver = /usr/lib/libsybase.so Setup = /usr/lib/libsybaseS.so FileUsage = 1 The Driver file name ( i.e., /usr/lib/libsybase.so ) should be unique. The friendly name ( i.e., Sybase 11 ) must also be unique. The Setup property points to a shared object containing functions to be called by ODBC Config. ODBC Config will call this share to get driver specific property names during data source configuration. If ODBC Config can not find/use this file it will assume some defaults such as; Data Source Name, Host, and default Database. (Setup is NOT SUPPORTED BY ICODBC at this time.) One can modify this file either using the ODBCINST shared object, by using the command line equivalent odbcinst, or a standard UNIX text editor. The odbcinst command can be used to add ICODBC to this file. Enter the following into a temp file: [ICODBC] Comment = Interactive COBOL ISAM ODBC Driver for Linux Driver = /usr/lib/icodbc.so FileUsage = 1 Now invoke odbcinst with the following arguments assuming you have created the above file template_icodbc: "odbcinst -i -d -f template_icodbc" The args to odbcinst are as follows: -i (install) -d (driver name) -f (name of template) Make sure you copy or link the released file, icodbc.so, to /usr/lib. If you had specified a simple name in the Driver line above, then the path for shared objects can be used to find the icodbc driver. (LD_LIBRARY_PATH under Linux.) You can use the installic script in the examples sub-directory of the icobol release to install icodbc.so. Just execute: "examples/installic icodbc". If you wish to turn on ODBC tracing then the following needs to be added to the odbcinst.ini file: [ODBC] Trace = Yes Trace File = filename If not specified, Trace defaults to NO and Trace File defaults to /tmp/sql.log. odbc.ini or ~/.odbc.ini ----------------------- These files describe the data-set to be used. They have the same format but refer to SystemDSN and UserDSN's respectively. The environment variable ODBCSYSINI can be used to find the system odbc.ini file and the environment variable HOME is used to find the user .odbc.ini file. If the system file is not found then the "$HOME"/.odbc.ini file is tried. If it is not found then the ODBC call will fail on the DriverConnect. Thus you must have ODBCSYSINI set if you are not using UserDSN's. The contents of the odbc.ini files give a section that is the data-set name, then a description, driver, and the DBQ (database) entry. Generally each driver requires different entries. The entries may be added in the same way using odbcinst, or a text editor. A sample entry to match the above driver could be [TESTDSN] Description = Test IC Dataset Driver = ICODBC DBQ = /home/data/datafile89 UID = user-id PWD = user-password Threading = 3 And this may be written to a template file, and inserted in the ini file for the current user by: "odbcinst -i -s -f template_file" The individual entries of course may vary. The Driver line is used to match the [section] entry in the odbcinst.ini file and the Driver line in the odbcinst file and is used to find the path for the driver library, and this is loaded and the connection is then established. It's possible to replace the driver entry with a path to the driver itself. This can be used, for example if the user can't get root access to setup anything in /etc (less important now because of the movable etc path). The DBQ line specifies the actual ICISAM database file to open. UID/PWD specify the user-id and password if any are required to access the database. The Threading line instructs unixODBC to not allow any threading. This should be the default. Threading sets the level of protection from threading for the driver and can be from 0 - 3. 0 is no protection, 1 is protect at the statement level, 2 is protect at the DBC level, 3 is protect at the environment level (i.e. no threading at all). The Unix ICODBC driver has been tested under various Linux (Intel) revisions. To enable auditing for the ICODBC driver to be like revisions previous to 4.11 set the ICODBC32 envirnment variable to: "-a:p" An audit log "icodbc_.lg" will be created in the current directory for all connections. The isql command that comes with unixODBC can be used to connect to a dataset and execute some simple SQL commands. ERROR CONDITIONS ---------------- A. You get: Error: Connection refused (oserr=111) Connecting to localhost:7334 java.sql.SQLException:[unixODBC] Icpermit is not running. B. You get: java.sql.SQLException: No suitable driver The unixODBC driver(s) cannot be found. Make sure the load path for .so files is set. (Under Linux, LD_LIBRARY_PATH=/usr/local/lib). C. You get: Warning: The file was not found (oserr=2): messages/system.ms Proceeding without messages ICROOT is not set. D. You get: java.sql.SQLException: [unixODBC] The icodbc.so driver cannot find "$ODBCSYSINI"/odbc.ini or "$HOME"/.odbc.ini to find the data set name. You must have an ICODBC license available from the license manager. End of Readodbc