Tips for using the KCML ODBC Driver

The following information may be helpful when using the KCML ODBC driver:

Using Reserved Words

Do not use SQL reserved words as listed in the SQL grammar in Appendix C of the Microsoft ODBC Programmer's Reference as identifiers (that is, table or column names), unless you surround the word in double quotation marks (for example, "DATE" or "DESC"). Many ODBC aware products will do this for you. Lotus 123 Release 4 is a notable exception.

Column and Table Names

If column or table names contain any characters except letters, numbers, and underscores, they must be delimited. To delimit a column or table name, enclose the name in double quotes("). Characters with an ASCII value greater than 127 are converted to underscores. Many ODBC aware products will do this for you. Lotus 123 Release 4 is a notable exception.

Complex Tables

You may get the error `Unable to open table: xxxxx' if the table contains more than 255 columns. KCML allows up to 1024 columns in a table but many ODBC applications impose their own limitations (e.g. Microsoft Query). To workaround this, edit the data dictionary for the file and mark only the important fields as reportable.

Lotus 123 Release 4/5

Lotus 123 accesses ODBC data via its DataLens facility. It is therefore necessary to declare the names of the ODBC data files in the DataLens configuration file \lotusapp\datalens\lotus.bcf. This is a text file which can be edited with notepad. See the readme.txt file If you don't have such a file in your /lotusapp/datalens directory then reinstall Lotus making sure that you install at least one of their drivers (the dBase one is installed by default in the standard install). For Release 4 include a line like:

DN="ODBC" AC=UI,PW;

The DN= phrase names the driver and the AC= phrase tells DataLens to prompt for a user id and password. Note that it will prompt for this before asking for the data source which can be confusing if you have more than one database server. The phrases should be entered as shown above including the semicolon. For version 5 you will also need to specify a DL="DLODBC" e.g.

DN="ODBC" DL="DLODBC" AC=UI,PW;

Lotus 123 Release 4 has problems with tables with large numbers of columns. Reserved words are not quoted so you should not use columns whose name conflicts with an SQL reserved word.

MS Access

To use a KCML table in an Access query or report you should use the Attach Table dialog on the File menu and choose the <SQL database> data source. This will present you with a list of ODBC DSN's. When you choose a KCML database data source you will be invited to log in to the server and will get a list of the tables in that data source. When attaching a table to an Access database you are advised not to tick the checkbox to have Access save the userid and password with the table. Many Unix systems will expire passwords and force users to change them regularly. When this happens Access will no longer be able to attach to the table and it provides no method for changing the saved password. Consequently you will have to delete and reattach the tables.

While Microsoft Access will allow queries to be constructed joining tables from different data sources, the join is done locally and performance therefore may not be as good as if the join were handled by ODBC. The SQL passthrough feature introduced in Access 2 can be used to force Access to do a server based query.

Access will only link tables with 255 columns or less. This will cause an index error when linking if the indexed column is more than column 256.

MS Query 2

The Query application shipped with Excel 5, Word 6 and MSVC++ considers blank columns in the graphical display to be NULL so if you add a filter by double clicking a blank field in the data display, Query adds an Is NULL criterion. KCML considers blank to a perfectly valid value for a character field and will return NULL only for columns initialized to HEX(FF). To match blank columns you should filter on Is Equals ` '.

MS Query 8 and Office 97

These products really only support File DSN's though Access97 does support both File DSN's and user DSN's.

Word 97 has a serious bug in mail merge that corrupts SQL strings longer than 255 characters. This is not fixed in Office Service Release 1 and is still outstanding ( see MS Knowledge Base article Q157615). The Microsoft workaround is 'Shorten the Search String: For example, shorten the path to the data source, shorten the file name, or select fewer columns of data when you define your query.' In KCML terms this would mean using short column names using the advanced connection options in the File DSN.

Borland Delphi

Borland documentation on ODBC is obscure. To use ODBC you must configure the database engine DBE to automatically alias ODBC data sources by running the BDE Configuration Utility, going to the System page and setting AUTO ODBC to True. This will make available all your ODBC data sources as BDE database alias names.

Delphi issues its own login dialog and then connects to the data source with SQLConnect. Generally it uses two connections at run time per query and another one or two at design time. To allow the KCML ODBC driver to cache the login password and avoid continuous login requests you should turn off the 'Confirm password' checkbox in the Data Source using the ODBC administrator. Then when creating a form to access a KCML database you should include the optional database control setting its LoginPrompt property to FALSE. In the Params property you can set the USERNAME at design time if it is fixed. Otherwise this can be set in Pascal at runtime with a user login dialog e.g.

LoginParams.Values['USER NAME'] := username;

Powerbuilder 3.0 or 4.0

By default Powerbuilder lower cases all table names which stops KCML recognising them. This is commonly seen in the message that a table has no columns. To fix this use notepad to add an entry to the PBODB030.INI (version 3) or PBODB040.INI (version 4) file of the form

[KISAM]
IdentifierCase=3

to tell Powerbuilder that KCML is case sensitive.