SQL Operations Guide

ctsqlimp Usage

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;