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 <filename".
If an error message occurs, try using the command "cpio -imdu <filename"
or "cpio -imduHodc <filename".
8. Install the server files and directories to the odbcdl4 home directory:
./install
9. Login as 'root'.
10. Configure the ODBC service port by adding a new line to the /etc/services
file. The /etc/services file is a text file and the new line can be
added with any text file editor such as "vi". The new entry in
/etc/services should look something like the following:
odbcdl4 9635/tcp # ODBCdL4 TCP/IP port
11. If the server has a software firewall such as IP filtering, enable access
to the ODBC service port from the desired systems. If all client systems
have IP addresses in a specific range, access can be limited to that IP
address range.
12. Configure the ODBC service in /etc/inetd.conf or /etc/xinetd.conf. First,
determine the type of network listener used by the system. Most Unix
systems use inetd.conf while current Linux systems use xinetd.conf. Apple
Mac OS X "Leopard" or later systems, use launchd (see below). If the
/etc/xinetd.conf file exists, the system most likely uses xinetd. This
can be confirmed by examining the process list displayed by a "ps -ef"
command ("ps -ax" on some systems) and finding a process named "xinetd".
Some Linux distributions do not install either inetd or xinetd; in that
case, it will be necessary to first install xinetd (see your operating
system documentation for instructions).
If the system uses inetd.conf, use any text editor such as "vi" to add
a line similar to the one below to /etc/inetd.conf:
# ODBCdL4 server for ODBC access to dL4 and UniBasic files
odbcdl4 stream tcp nowait odbcdl4 /home/odbcdl4/odbcdl4 odbcdl4 /home/odbcdl4/db
where "/home/odbcdl4" is the home directory of the odbcdl4 account. A
"-a" option can be added prior to "/home/odbcdl4/db" to enable access
control files. A "-c" option can be added to select case sensitive data
comparison and sorting. A "-e keyfile" option can be added to load
encryption file keys from the key file "keyfile". Please note that the
occurrence of "odbcdl4" after "/home/odcbdl4/odbcdl4" is required.
If the system uses xinetd, add a new text file named "odbcdl4" in the
/etc/xinetd.d directory with contents similar to:
# default: on
# description: ODBCdL4 server connections
service odbcdl4
{
flags = REUSE
socket_type = stream
wait = no
user = odbcdl4
groups = yes
server = /home/odbcdl4/odbcdl4
server_args = /home/odbcdl4/db
disable = no
cps = 5000 1
}
where "/home/odbcdl4" is the home directory of the odbcdl4 account. The
following options can be added to "server_args" before the database
directory:
-a Enable access control files.
-c Select case sensitive data comparison and sorting.
-e keyfile Load encryption file keys from the key file "keyfile".
For example, this setting enables access control files:
server_args = -a /home/odbcdl4/db
Apple Mac OS X "Leopard" or later systems use launchd instead of xinetd.d.
On those systems, an odbcdl4 service file must be added to the
/System/Library/LaunchDaemons directory. Check the system documentation
and the existing "plist" files in the LaunchDaemons directory for details
on file syntax.
13. Reinitialize the service tables by sending a SIGHUP signal to the inetd
or xinetd process, which ever is used by the system. This can be done
by using a "ps -ef" (or "ps -ax") command to determine the process
number and executing the following command
kill -HUP pid
where "pid" is the process number. Alternately, the service tables
can be reinitialized by rebooting the system.
14. Delete the distribution file or move it to a backup directory.
15. See the section below, "Defining ODBC Databases and Tables", for
instructions on how to setup ODBC access to uniBasic and dL4 data
files.
File list
=========
odbcsrvr/
odbcdl4 ODBC data server
instodbc.bat Service configuration script (Windows only)
checkdb Utility to test validity of table definitions
readme.txt This file
license.txt Runtime license terms and conditions
bookings Sample indexed contiguous file
bookings.idx Sample indexed contiguous file
customers Sample indexed contiguous file
customers.idx Sample indexed contiguous file
orders Sample indexed contiguous file
orders.idx Sample indexed contiguous file
db/
sample/ Sample database directory
bookings Profile for sample indexed contiguous file
customers Profile for sample indexed contiguous file
orders Profile for sample indexed contiguous file
byorders Profile for sample "LinkedRecord" table
bycustid Profile for sample "LinkOneToMany" table
New in This Release
===========================================================================
Aug 16 2010 (Release 1.5.1)
o Encrypted indexes are now supported in encrypted indexed contiguous
files.
o Date/time values are now supported as TIMESTAMP columns if the ODBCdL4
client driver is release 1.6 or later.
o A new command line option, "-s server_name", has been added to the
checkdb utility to test if the specified ODBCdL4 server responds to
network requests. The string "server_name" can either be a server name
or an IP address. If the server uses a port number other than 9635, the
port number must be appended to "server_name" after a colon character
("server:port"). Example:
checkdb -s 127.0.0.1
Please note that the local connection test may succeed even if client
connections are blocked by a firewall.
o Server connections can now be tested by making a telnet connection to the
server and typing a "Control-A" character followed by a carriage return.
The server will return the ODBCdL4 server name and version number.
o Bug fixed: the odbcdl4 and checkdb "-e" encryption key file option did
not work on some platforms.
Jun 10 2008 (Release 1.5)
o Improve performance for queries using OR conditions. This feature requires
release 1.4 or later of the ODBCdL4 driver for Windows.
o Improve performance for some Microsoft Access applications. This feature
requires release 1.4 or later of the ODBCdL4 driver for Windows.
o Sample tables added for linked records.
Nov 1 2007 (Maintenance Release 1.4.3)
o Bug fixed: multiple BASE options were not treated as errors.
Sep 12 2007 (Maintenance Release 1.4.2)
o Bug fixed: queries sometimes didn't return all of the intended rows when
"greater than" selections were used and the selection column was part of
a multipart key.
Aug 28 2007 (Maintenance Release 1.4.1)
o Bug fixed: partially encrypted indexed contiguous or contiguous files
couldn't be accessed without the encryption key.
Aug 24 2007 (Release 1.4)
o Table profiles can now include a single "[LinkOneToMany]" section to
import columns from another table and generate multiple rows. For
example, a profile might import order information into a customer table
based on the customer number. A separate row would be returned for
each order with each row containing the same customer information from
the customer table. The syntax of a "[LinkOneToMany]" 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 generate
rows for all of the orders for each customer in the customer table. If
no order records exist for a customer, then no rows will be returned
for the customer. Note that "/u/odbc/customer" and "/u/odbc/orders"
are table profiles rather than the actual data files. The customer
table fields and indexes could be defined directly in the profile, but
using the customer profile avoids duplication of the field definitions.
[FullISAMView]
File=/u/odbc/customer
OpenAs=Full-ISAM View
[Record]
Field=CustName,Name
Field=CustPhone,Phone
Field=CustContact,Contact
Field=CustCode,CustomerCode
[LinkOneToMany]
File=/u/odbc/orders
UseIndex=ByCustCode
KeyPart=CustCode
Field=ProductNum
Field=Quantity
Field=OrderDate
Multiple one-to-many links can be implemented by placing a "LinkOneToMany"
section in the table profile being linked (such as "/u/odbc/orders" in the
example above).
o When using a one-to-many link, a linked record of NULL values can be
optionally returned when the linked record does not exist. The option
NULLRecord=True
must be placed in the "[LinkOneToMany]" section before the "UseIndex"
line to enable NULL records. This option is particularly useful with
cross product links where the file in a one-to-many link contains a
one-to-many link to a third file.
o The ODBCdL4 server can now be installed on Windows 2000, Windows XP, or
Windows 2003 systems. After installation, the service is configured by
running the script instodbc.bat in the "/Program Files/ODBCdL4 Server"
directory (or wherever ODBCdL4 was installed) from a command prompt as
shown below:
cd "\Program Files\ODBCdL4 Server"
.\instodbc.bat <ip-address> <port> <database-dir>
where
<ip-address> is the IP address of the server or "0.0.0.0"
<port> is the TCP port number to be used
<database-dir> 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=<fieldname>{,<nameinfile>}
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=<date>" or
"MAXDATE=<date>" 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=<field_name>
where "<field_name>" is the field name to be given to the record number.
Records can then be selected according to the value of "<field_name>".
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=<fldnam>,<pos>,<fmt>,<align>,<fill>{,<opts>}
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=<fldnam>,<pos>,<fmt>,<prefix>,<charset>,<opts>
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 "<WL>" permissions on the "FILE=" line (for example,
"FILE=<WL>/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=<fldnam>,<name_in_Full-ISAM_file>
;
; 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=<fldnam>,<pos>,<fmt>{,<align>}{,<fill>}{,<opts>}
;
; The keyword "Field" must be replaced by "AltField" if the field being
; defined overlaps a previously defined field.
;
; <fldnam> 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.
; <pos> Byte displacement in the actual record or item number for
; formatted files.
; <fmt> Character length for strings, precision number and
; decimal places for numerics. For strings, <fmt> is just
; a number. For numerics, <fmt> has the syntax "p%" or
; "p.d%" where "p" is the precision and "d" is an optional
; number of decimal places.
; <align> "L" to left-align string data (default).
; "R" to right-align string data.
; <fill> fill character to use if string field sizes mismatch,
; default is " ".
; <opts> 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=<fldnam>
;
; 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=<fldnam>,<pos>,<fmt>{,<prefix>{,<charset>{,<opts>}}}
;
; <fldnam> 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.
; <pos> Byte displacement in idx-ctg key.
; <fmt> Character length for strings, precision number and
; decimal places for numerics. For strings, <fmt> is just
; a number. For numerics, <fmt> has the syntax "p%" or
; "p.d%" where "p" is the precision and "d" is an optional
; number of decimal places.
; <prefix> prefix character strings, unused.
; <charset> pseudo-key character set, unused.
; <opts> 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=<field>",
; 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=<fldnam>
;
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=<fldnam>
;
; 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=<fldnam>,<field_name_linked_record_file>>
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).
<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 OUTER 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 Like other Dynamic Concepts products, 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.
For Unix or Linux systems, the Passport software is distributed by Dynamic
Concepts as a separate installable product. For Windows systems, the
Passport software is included in the ODBCdL4 server installation file.
You can download the latest version of the Passport from the Dynamic
Concepts web site, www.dynamic.com.