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.exe <filename> [options]

Valid [options] values:

• <filename> - data file name/path (relative paths to FairCom DB directory)
• -h - display usage help
• -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 - user password for authorization
• -n symbolic - set SQL table name to symbolic
• -o userid - set owner of table to userid
• -i - non-interactive mode: ignore errors and continue
• -r - remove table from dictionaries (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
• -t - do not promote unsigned types to greater signed type
• -m idxname - set index idxname as primary key
• -z - allow indexes with missing string terminator in key segments
• -l <size> - specify LONGVAR* field size threshold
• -g - ignore existing index name in IFIL resource
• -b - grant public access permissions to linked table
• -B - grant 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
• -e xmlfile - get DODA definitions from external XML file xmlfile
• -f s(f) | z(f) | sz(f) - force string padding to (s)paces (z)eroes or (sz)spaces zero terminated. (f)orce presence of field delimiter
• -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)
• -y - keep existing permissions and synonym
• --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)
• --ignore_index_case - ignore case in segment modes when linking the index (this may cause incorrect query result)

Note: The parameters are case-sensitive. By default they are lowercase 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, FairCom RTG sqllink and sqlize) for Unicode has been updated to take into consideration the string encoding set at the FairCom DB API 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.