Database Integrity Utilities

ctsqlimp - SQL Import Utility

The FairCom DB SQL import utility, ctsqlimp, utility “registers” or links existing c-tree files with a FairCom DB SQL database without modifying the table structure such that the files remain accessible from the original application and also accessible from FairCom DB SQL.

For more information about this utility, see ctsqlimp - SQL Import Utility.

Operational Model:

• Client

ctsqlimp is used as follows (after creating the database file):

ctsqlimp <filename> [-d database] [-s server] [-u userid] [-a password]
[-n symbolic] [-o userid]
[-i] [-r] [-k] [-c] [-x] [-p|-P] [-z] [-g] [-b|-B] [-j]
[-w script] [-m idxname] [-e xmlfile] [-f s|z|sz] [-l size] [-q prefix]
[-h] [--rowid_fld] [--rowid_idx] [--tls] [--tls_cert cert]

• <filename> - data file name/path (relative paths to FairCom DB directory)
• -d database - database name (default: ctreeSQL)
• -s server - FairCom DB SQL Server name (default: FAIRCOMS)
• -u userid - userid for logging into FairCom DB SQL
• -a password - password for authorization
• -n symbolic - set SQL table name to symbolic symbolic
• -o userid - set owner of table to userid
• -i - non interactive mode: ignore errors and continue
• -r - remove existing linked table (file is not deleted)
• -k - skip fields that don't comply with conventional identifiers rules
• -c - allow table names not complying with conventional identifiers rules
• -x - skip indexes
• -p - promote unsigned integer to greater signed type
• -P - promote unsigned types to greater signed type and set check for fitting value
• -z - allow indexes with missing string terminator in key segments
• -g - ignore existing index name in IFIL resource
• -b - (lowercase) switch grants all permissions on the table to the public
• -B - (uppercase) switch grants read-only permissions on the table to the public

  Note: If both -B and -b are specified, the read-only setting takes precedence. Notice that the owner of the table and the DBA have all the permissions.
  This introduces a change in behavior for existing applications because this switch is now-case sensitive.

• -j - non-interactive relink of existing table
• -w script - write CREATE statements into script file script instead of importing the table
• -m idxname - set index idxname as primary key
• -e xmlfile - get DODA definitions from external XML file xmlfile
• -f s | z | sz - force string padding to (s)paces (z)eroes or (sz)spaces zero terminated
• -l size - specify LONGVAR* field size threshold
• -q prefix - prefix SQL table name with prefix (when the -q option is combined with the symbolic table name option, -n, the prefix is prepended to the symbolic name instead to the table name)
• -h - display usage help
• --rowid_fld fldname - expose the ROWID value as field with name (table must have $ROWID$ field)
• --rowid_idx idxname - expose index on the ROWID value with name (--rowid_fld option must be specified)
• --tls - use a TLS connection with no certificate check (mutually exclusive with --tls_cert)
• --tls_cert cert - use a TLS connection with certificate 'cert' (mutually exclusive with --tls)

Note: The parameters are case-sensitive. By default they are lower case unless otherwise stated.

Example

To make existing c-tree files mydata.dat (containing proper IFIL and DODA resources) and mydata.idx accessible via FairCom DB SQL, follow these steps:

1. Create a database named ctreeSQL.
2. Copy your ISAM custmast.dat and custmast.idx files into the ctreeSQL.dbs subdirectory of the server's working directory.
3. Ensure FairCom DB SQL is running, as ctsqlimp is a client application.
4. Run the ctsqlimp utility found in the /tools/cmdline/admin/client/ directory of your FairCom DB installation:

   ctsqlimp custmast.dat -u ADMIN -a ADMIN -s FAIRCOMS

5. Run the Interactive SQL, isql, utility and issue the command:

   SELECT * FROM custmast;

Unicode

In V11.8 and later, the SQL import logic (affecting the ctsqlimp utility, the ctSqlImportTable() function, c-treeRTG sqllink and sqlize) for Unicode has been updated to take into consideration the string encoding set at the c-treeDB level by the ctdbSetFieldStringEncoding() function. When the SQL import logic identifies a CHAR or a VARCHAR field and the string encoding has been set, the logic uses it to set the charset in the system table. If not present, the charset is not set (which was the behavior before this modification) and so SQL interprets the content as Latin‑1.