Wednesday January 3, 2024

ODBCdL4 Driver for Windows and Linux 1.9 Release
All Rights Reserved.  Copyright (c) 2005 - 2023 by:
Dynamic Concepts, Inc. Irvine, CA 92618 USA


Email address:  techsupport@dynamic.com
Information:    www.unibasic.com
Downloads:      www.unibasic.com or ftp.dynamic.com


Documentation
=============

This file is the only documentation provided for the ODBCdL4 driver.
Please see the following sections for information on using the driver:


    Driver connection string parameters


    SQL syntax supported by the ODBCdL4 server and driver


Highlights of This Release
==========================

o   The ODBCdL4 driver is available for Linux systems.

o   The drivers are available in MSI installation files.

o   All network traffic can be encrypted after the initial negotiation.


Pre-installation instructions
=============================

o   The ODBCdL4 Driver must only be used with the ODBCdL4 Server.

o   This release requires that the ODBCdL4 server have an SSN authorized
    for release 1 of ODBCdL4 and the expected number of users.  Without
    such an SSN, ODBCdL4 cannot be used.

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 


Installation instructions
=========================
1.  Login as 'root'.

2.  Copy the distribution file to any temporary directory on your system, e.g.
    "/tmp".

3.  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.

4.  Unpack the distribution file using the command "cpio -imdu <filename".
    If an error message occurs, try using the command "cpio -imcdu <filename"
    or "cpio -imduHodc <filename".

5.  Type "./install" to install the ODBCdL4 driver into /usr/lib (32 bit
    driver) or /usr/lib64 (64 bit driver).  Any existing driver will be 
    replaced. If the file /etc/odbcinst.ini does not contain an entry for the
    ODBCdL4 driver, a new entry will be added.  The files readme.txt and
    license.txt will be installed in /usr/lib/odbcdl4 or /usr/lib64/odbcdl4.

6.  Delete the distribution file or move it to a backup directory.

7.  On Linux systems, it may be necessary to install additional system
    libraries if the Linux version does not match the Linux version expected
    by the ODBCdL4 platform.  If you are installing a 32 bit platform (37,  38,
    or 39) on a 64-bit Linux system, the 32 bit version of libc/glibc is often
    missing in a default installation of Linux.  If the correct libc/glibc
    library is installed, the command "ldd /usr/lib*/libodbcdl4.so" can be used 
    to determine the names of any missing libraries.  Please see Linux system
    documentation for instructions on how to install missing system libraries.

    The following are typical commands needed to install missing 32 bit
    libraries on a 64 bit Red Hat Linux system:
    
      yum update
      yum install glibc.i686
      yum install openssl.i686 (or openssl-libs.i686)


File list
=========

readme.txt          This file
license.txt         Runtime license terms and conditions      
libodbcdl4.so       ODBCdL4 driver


New in This Release
=================================================================

Jan  3 2024 (Release 1.9)

o   The ODBCdL4 driver is now available for Linux systems.  The driver
    allows Linux applications to use ODBC to connect to an ODBC dL4 server
    to access uniBasic or dL4 files using SQL statements.

    PHP connection example:

        $conn = odbc_connect("DRIVER={ODBC dL4 Driver 64};
          RemoteHost=127.0.0.1;RemotePort=9635;DBQ=sample","","");

o   DSNs are now supported by Linux ODBCdL4 drivers.

o   The line "Threading=3" is now added when defining ODBCdL4 drivers in
    /etc/odbcinst.ini on Linux systems.  This prevents corruption of ODBC
    handles by multithreaded applications.

o   Bug fixed: the Linux ODBCdL4 driver did not work with PHP and some
    other applications on 64-bit platforms


Jul  7 2022 (Maintenance Release 1.8.2)

o   The ODBCdL4 Driver installer is now available as signed MSI files.
    There are separate MSI installers for the 32 bit and 64 bit drivers.

Oct 22 2020 (Maintenance Release 1.8.1)

o   ODBCdL4 Driver for Windows is now compiled and linked using Microsoft 
    Visual Studio 2019.  Windows versions prior to Windows 7 are no longer 
    supported.

Dec  9 2019 (Release 1.8)

o   SQL JOIN performance has been improved when using ODBCdL4 Server 1.8
    or later with the ODBCdL4 Driver 1.8 or later.

o   The ODBC SQLStatistics() function now returns information on all indexes
    defined in a table definition file.  ODBCdL4 has always used all of the
    index definitions, but the definitions were not visible to ODBC 
    applications.  This enhancement requires use of the current releases of
    both the ODBCdL4 Server and the ODBCdL4 Driver.

o   The SQL intrinsic functions, such as LENGTH, can now be used with columns
    and arguments of up to 1100 characters.

Oct 31 2017 (Release 1.7)

o   The ODBCdL4 driver now uses TLS/SSL encryption with the ODBCdL4 server
    after the initial connection if the server is version 1.6 or later.  If
    the server is older than version 1.6, all communication with that server
    will use clear text.  Please note that TLS/SSL certificates are not used
    by the server and there is no protection against man-in-the-middle
    attacks.

Feb  6 2017 (Maintenance Release 1.6.7)

o   Bug fixed: queries to ODBCdL4 linker servers failed in Microsoft SQL
    Server 2016 unless the result set was empty.

Jan 31 2017 (Maintenance Release 1.6.6)

o   The word "OUTER" in "LEFT OUTER JOIN" is now optional.

o   The ODBC SQLStatistics function now returns the database name and
    table name instead of "" or NULL.

Dec 12 2011 (Maintenance Release 1.6.5)

o   A DSN option has been added to enable or disable debug logging of
    ODBCdL4 activity on the server.  This option should only be used when
    directed by Dynamic Concepts support personnel.

Oct 26 2011 (Maintenance Release 1.6.4)

o   The OLE Provider for ODBC in Windows 2008R2 appears to have a bug that
    causes a memory violation when used with ODBCdL4.  Since SQL Server
    Linked Servers require using the OLE Provider, a new ODBCdL4 DSN option,
    "Use OLE Workaround", has been implemented to avoid the OLE bug.  When
    creating a DSN for use with an SQL Server Linked Server, set the "Use
    OLE workaround" check box to enable the option.  The OLE workaround
    option should only be enabled in DSNs that are used with SQL Server
    Linked Servers.  The option can be added to an existing DSN by using
    the ODBC Administrator.

Aug  3 2011 (Maintenance Release 1.6.3)

o   Bug fixed: the SQL Server administrator utility displayed duplicate 
    databases and tables for ODBCdL4 Linked Servers.

Aug 12 2010 (Maintenance Release 1.6.2)

o   Improve connection failed error messages.

Apr  6 2010 (Maintenance Release 1.6.1)

o   Bug fixed: fixed a memory leak.

Mar 15 2010 (Release 1.6)

o   On 64-bit Windows operating systems, a 64-bit version of the ODBCdL4
    driver is installed for use with 64-bit applications.  The 32-bit version
    of the driver is installed on both 64-bit and 32-bit Windows for use by
    32-bit applications.

o   ODBCdL4 now supports bracketed identifiers as in Microsoft SQL Server.
    This addition increases compatibility with Visual Studio 2005/2008
    which uses this non-standard syntax in some ODBC wizards.

o   Bug fixed: fix problem with Visual Studio 2008 that prevented the data
    wizard from being able to setup ODBCdL4 connections.

Dec 15 2009 (Maintenance Release 1.5.2)

o   Bug fixed: fix problem with the ODBC Administrator on Windows 7: the
    administrator utility (odbcad32.exe) crashed when creating a file DSN.

May 27 2009 (Maintenance Release 1.5.1)

o   Improve compatibility with the "Preview" button of the Excel ODBC 
    connection setup wizard.

Nov 20 2008 (Production Release 1.5)

o   Improve performance for queries using the IN or BETWEEN operators.  This
    feature requires release 1.5 or later of the ODBCdL4 server.

o   Increase compatibility with the Excel Data Wizard and some other ODBC
    configuration utilities.

Jun 10 2008 (Production Release 1.4)

o   Improve performance for queries using OR conditions.  This feature
    requires release 1.5 or later of the ODBCdL4 server.

o   Improve performance for some Microsoft Access applications.  This feature
    requires release 1.5 or later of the ODBCdL4 server.

Apr 27 2007 (Maintenance Release 1.3.1)

o   Increase query timeout period to support lengthy table scans that return
    very few records.

Jan 26 2007 (Production Release 1.3)

o   Simplify use of ODBCdL4 as a "linked server" under Microsoft SQL
    Server by treating character fields as VARCHAR or LONGVARCHAR columns.
    Previous releases treated character fields as CHAR columns.

o   Support installation on 64-bit (x64) Windows systems.

o   Support JOIN syntax required by Crystal Reports.

o   A brief description of the SQL syntax supported by ODBCdL4 has been
    added to this document (see "SQL syntax" below).

Dec 16 2005 (Production Release 1.2)

o   Support the access control feature of release 1.2 of the ODBCDl4 server.

o   Bug fixed: the odbcdL4 driver DLL was not always installed successfully.

Sep 14 2005 (Production 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.4)

o   Support has been added for packed data in keys and CONV format numeric
    fields in records.  For details, see the readme.txt file for the
    ODBCdL4 server.

May  6 2005 (Beta Release 1.1.1.3)

o   Support contiguous files, date conversions, record selection, and NULL
    values.  For details, see the readme.txt file for the ODBCdL4 server.

o   Due to network protocol changes, this version of ODBCdL4 for Windows
    cannot be used with older versions of the ODBCdL4 for Unix server.  All
    servers must be upgraded to version 1.1.1.5 or later of the ODBCdL4 for
    Unix server.

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.


Driver connection string parameters
===================================

The ODBCdL4 driver connection string supports the following parameters:

    DRIVER=<ODBC driver name>

    Use the value "ODBC dL4 Driver 32" or "ODBC dL4 Driver 64" to select the
    32-bit or the 64-bit driver.

    DBQ=<database name>

    Database=<database name>

    RemoteHost=<ODBCdL4 server name or IP address>

    Server=<ODBCdL4 server name or IP address>

    RemotePort=<TCP port number for the ODBCdL4 server; typically 9635>

    ServerPort=<TCP port number for the ODBCdL4 server; typically 9635>

    UseOLEWorkaround=Y

    This value enables a behavior which may be needed by some ODBC clients.

    DebugLog=Y

    This value enables logging for this connection on the ODBCdL4 server.

    PHP Example:

        $conn = odbc_connect("DRIVER={ODBC dL4 Driver 64};
          RemoteHost=127.0.0.1;RemotePort=9635;DBQ=sample","","");


Example:
    
    DRIVER=ODBC dL4 Driver 32;DBQ=sample;RemoteHost=127.0.0.1;RemotePort=9635


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).

<supported statements> ::= <select statement>

<select statement> ::= "SELECT" <select clause> { <order by clause> }

<select clause> ::= <select cols> "FROM" <table list> { <select opts> }

<select opts> := { <where clause> } { <group by clause> } { <having clause> }

<select cols> ::= { "ALL" | "DISTINCT" } "*" |
                  { "ALL" | "DISTINCT" } <select list>
    
<select list> ::= <select list item> { "," <select list item> } ...

<select list item> ::= <expression> |
                       <expression> <alias name> |
                       <expression> "AS" <alias name> |
                       <alias name> "." "*"

<where clause> ::= "WHERE" <boolean expr>
    
<having clause> ::= "HAVING" <boolean expr>

<value list> ::= "NULL" "," <value list> |
                 <expression> "," <value list> |
                 <expression> |
                 "NULL"

<boolean expr> ::= <boolean andexpr> { "OR" <boolean expr> }

<boolean andexpr> ::= { "NOT" } <boolean value> { "AND" <boolean andexpr> }

<boolean value> ::= ( <boolean expr> ) |
            <column ref> "IS NULL" |
            <column ref> "IS NOT NULL" | 
            <expression> "LIKE" <pattern> |
            <expression> "NOT LIKE" <pattern> |
            <expression> "IN" ( <value list> ) |
            <expression> "NOT IN" "(" <value list> ")" |
            <expression> <relation> <expression> |
            "EXISTS" "(" <selection> ")" |
            <expression> <relation> { "ALL" | "ANY" } "(" <selection> ")" |
            <expression> "IN" "(" <selection> ")" |
            <expression> "NOT IN" "(" <selection> ")" |
            <expression> "BETWEEN" <expression> "AND" <expression> |
            <expression> "NOT BETWEEN" <expression> "AND" <expression>

<selection> ::= "SELECT" <select clause>

<relation> ::= ">" | ">=" | "<" | "<=" | "=" | "<>"

<pattern> ::= <string> | "?" | "USER"

<expression> ::= <expression> "+" <mul expr> |
                 <expression> "-" <mul expr> |
                 <mul expr>

<mul expr> ::= <mul expr> "*" <unary expr> |
               <mul expr> "/" <unary expr> |
               <unary expr>

<unary expr> ::= { "+" | "-" } <term>

<term> ::= ( <expression> ) |
           <column ref> |
           <simple term> |
           <aggregate term> |
           <scalar>

<scalar> ::= <scalar escape> |
             <scalar shorthand>

<scalar escape> ::= <vendor escape leadin> "FN" <fn> ")*--"

<vendor escape leadin> ::= "--*(VENDOR(MICROSOFT),PRODUCT(ODBC)"

<scalar shorthand> ::= "{" "FN" <fn> "}"

<fn> ::= <function name> "(" <value list> ")" | <function name> "(" ")" |
         "POSITION" "(" <expression> "IN" <expression> ")" |
         "EXTRACT" "(" <expression> "FROM" <expression> ")"

<aggregate term> ::= "COUNT" "(" "*" ")" |
                     "AVG" "(" <expression> ")" |
                     "MAX" "(" <expression> ")" |
                     "MIN" "(" <expression> ")" |
                     "SUM" "(" <expression> ")" |
                     "COUNT" "(" <expression> ")"

<simple term> ::= <string> |
                  <real number> |
                  "?" |
                  "USER" |
                  <date> |
                  <time> |
                  <timestamp>

<group by clause> ::= "GROUP BY" <column ref> { , <column ref> } ...

<order by clause> ::= "ORDER BY" <sort option> { , <sort option> } ...

<sort option> ::= <column ref> { "ASC" | "DESC" } |
                  <integer> { "ASC" | "DESC" }

<column ref> ::= <alias name> "." <column name> |
                 <column name>

<table list> ::= <table list item> { "," <table list item > } ...

<table list item> ::= <table ref> |
                      <outerjoin>

<outerjoin> ::= <oj> |
                <oj escape> |
                <oj shorthand>

<oj> := <table ref> "LEFT OUTER JOIN" <table ref> ON <boolean expr> | 
        <table ref> "LEFT JOIN" <table ref> ON <boolean expr> | 
        <table ref> "LEFT OUTER JOIN" <oj> "ON" <boolean expr> |
        <table ref> "LEFT JOIN" <oj> "ON" <boolean expr> |
        "(" <inneroj> ")" "INNER JOIN" <table ref> "ON" <boolean expr> |
        "(" <inneroj> ")" "INNER JOIN" <inneroj> "ON" <boolean expr> |
        <inneroj>

<oj escape> ::= <vendor escape leadin> "OJ" <oj> ")*--"

<oj shorthand> ::= "{" "OJ" <oj> "}"

<inneroj> := <table ref> "INNER JOIN" <table ref> "ON" <boolean expr> |
             <table ref> "INNER JOIN" <inneroj> "ON" <boolean expr>

<table ref> ::= <table name> |
                <table name> <alias name>

<function name> ::= "ABS" |
                    "ACOS" |
                    "ASCII" |
                    "ASIN" |
                    "ATAN" |
                    "ATAN2" |
                    "BIT_LENGTH" |
                    "CEILING" |
                    "CHAR" |
                    "CHARACTER_LENGTH" |
                    "CHAR_LENGTH" |
                    "CONCAT" |
                    "CONVERT" |
                    "COS" |
                    "COT" |
                    "CURDATE" |
                    "CURRENT_DATE" |
                    "CURRENT_TIME" |
                    "CURRENT_TIMESTAMP" |
                    "CURTIME" |
                    "DATABASE" |
                    "DAYNAME" |
                    "DAYOFMONTH" |
                    "DAYOFWEEK" |
                    "DAYOFYEAR" |
                    "DEGREES" |
                    "DIFFERENCE" |
                    "EXP" |
                    "EXTRACT" |
                    "FLOOR" |
                    "HOUR" |
                    "IFNULL" |
                    "INSERT" |
                    "LCASE" |
                    "LEFT" |
                    "LENGTH" |
                    "LOCATE" |
                    "LOG" |
                    "LOG10" |
                    "LTRIM" |
                    "MINUTE" |
                    "MOD" |
                    "MONTH" |
                    "MONTHNAME" |
                    "NOW" |
                    "OCTET_LENGTH" |
                    "PI" |
                    "POSITION" |
                    "POWER" |
                    "QUARTER" |
                    "RADIANS" |
                    "RAND" |
                    "REPEAT" |
                    "REPLACE" |
                    "RIGHT" |
                    "ROUND" |
                    "RTRIM" |
                    "SECOND" |
                    "SIGN" |
                    "SIN" |
                    "SOUNDEX" |
                    "SPACE" |
                    "SQRT" |
                    "SUBSTRING" |
                    "TAN" |
                    "TIMESTAMPADD" |
                    "TIMESTAMPDIFF" |
                    "TRUNCATE" |
                    "UCASE" |
                    "USER" |
                    "WEEK" |
                    "YEAR"

<table name> ::= <identifier>
    
<column name> ::= <identifier>

<alias name> ::= <identifier>

<date> ::= <date escape> | <date shorthand>

<date escape> ::= <vendor escape leadin> "d" <date value> ")*--"

<date shorthand> ::= "{" "d" <date value> "}"

<time> ::= <time escape> | <time shorthand>

<time escape> ::= <vendor escape leadin> "t" <time value> ")*--"

<time shorthand> ::= "{" "t" <time value> "}"

<timestamp> ::= <timestamp escape> | <timestamp shorthand>

<timestamp escape> ::= <vendor escape leadin> "ts" <timestamp value> ")*--"

<timestamp shorthand> ::= "{" "ts" <timestamp value> "}"
 
<identifier> is an identifier.  If the identifier contains spaces, then the
identifier must be enclosed in double quotes or brackets ("[ab c]").

<string> is a string enclosed in single quotes.

<real number> is a non-negative real number including E notation.

<integer> is a non-negative integer.

<date value> is a value in the form 'yyyy-mm-dd' representing a date.  The
single quotes are required.  Example: '2005-10-05'.

<time value> is a value in the form 'hh:mm:ss' representing a time of day.
The single quotes are required.  Example: '14:52:29'.

<timestamp value> is a value in the form 'yyyy-mm-dd hh:mm:ss.ffffff' or 
'yyyy-mm-dd hh:mm:ss' representing a date and time.  The single quotes are
required.  Example: '2005-10-05 14:52:29.713'.


Notes
=====

o   To use the ODBCdL4 driver, the ODBCdL4 server must be  installed on
    a server which has access to the data files.  In addition,
    table definitions must be written and installed on the server for each 
    data file to be accessed.

o   The suggested TCP/IP port for use with ODBCdL4 is 9635, but
    any available port can be used.