sqlcp - modify low-level SQL control parameters

SYNOPSIS

<sqlcp $name $value [...]>

DESCRIPTION
The sqlcp function allows the setting of various low-level and debug parameters affecting the behavior of SQL statements. (Note: For Metamorph-level query processing (thesaurus, suffixes, etc.) see the apicp function on p. ). The $name parameter specifies a setting to change, and $value its new value. The settings may also affect Vortex math in IF, WHILE, assignment, etc. statements as well. Some settings have additional optional args after $value.

The $value parameter is interpreted as a boolean value, integer, string, or list of strings, depending on the value of $name. Boolean values are ``true'', ``yes'', ``on'' or a non-zero integer for true; the opposite for false. The possible values for $name and what argument(s) they expect are:

• cache close [db $db]
  Closes the Texis handle cache in Vortex. This can be used to ensure the process is not still using a database that may be about to be deleted. Returns 1 on success, 0 on failure. The optional db $list arguments were added in version 5.01.1111164819 20050318; if specified, only handles using database(s) specified by $db will be closed.

• cache stats
  Added in version 3.0.958600000 20000517. Prints SQL cache statistics.

• cache resetstats or cache statsreset
  Added in version 3.0.958600000 20000517. Clears SQL cache statistics. (The format of these statistics is subject to change without notice.)

• singleuser (boolean, off by default)
  If true, single-user mode is set in Texis. This means that one of two conditions must be met at all times:

  • This must be the only process accessing the database. Any action, including selects, inserts, deletes, and updates, is permitted in this case.

  • All processes accessing the database-whether single-user or not--must be ``read-only'': only select statements are permitted.

  Note: If both of these conditions are violated when single-user mode is in effect, severe database corruption may result. Do not set single-user mode unless you know what you are doing!

  Not only do SQL statements access the database, but so do variables EXPORTed to the state table, the adminsql function, other executables like tsql etc. All access to a database must be taken into account.

  By guaranteeing no simultaneous writes will ever occur to the database, the normal locking mechanisms in Texis can be bypassed, speeding up read/search access to the database.

  Returns previous setting of singleuser (1 or 0). (Note: Version 3.0.947100000 20000105 and earlier always returned 1.) The SQL cache is also closed (reset) by this call.

• tracesql (boolean/integer, 0 by default)
  Debug setting; enables tracing of SQL statements. If greater than zero, SQL statements are printed as informational, <putmsg>-capturable messages whenever executed, including their parameters.

  This setting can be used to trace complex, constructed-on-the-fly SQL statements when debugging scripts. The command-line option -tracesql overrides this (and can be used when it's not appropriate to edit the script). Returns the previous setting. Added in version 3.0.947100000 20000105. See also the <TRACESQL> directive (p. ) for details on the various possible values, and the -tracesql command line option (p. ).

• traceidx or traceindex (boolean/integer, 0 by default)
  Debug setting; enables tracing of Metamorph index searches. Unsupported/internal, subject to change without notice. Returns the previous setting. Added in version 3.0.947100000 20000105. See also the -traceidx command-line option.

• tracekdbf or kdbfiostats (integer/string, 0 by default)
  Debug setting; enables tracing of KDBF I/O. Unsupported/internal, subject to change without notice. Returns the previous setting. Added in version 3.01.967500000 20000828. The $value can be ``summary'', in which case a summary of KDBF handle opens is printed. If a KDBF file name (without directory prefix) is given, I/O for that particular file is summarized. If 1 is given, I/O for all non-SYS KDBF files is summarized; if 2, for all KDBF files. If the value is bitwise-ORed with 4, then specific KDBF opens and closes are also printed. Hex/octal values may be given in version 4.03.1081500000 20040409 and later.

• autocreatedb or autocreate (boolean, on by default)
  Whether to attempt to automatically create a database when needed for Texis math in variable assignment, IF and WHILE statements. The current database must be opened for these statements; if it does not exist, Vortex attempts to create it, which may be a problem in some circumstances. With autocreatedb off, the database is not automatically created and such statements fail. Added in version 3.01.963600000 20000714. Returns the previous setting.

• lookahead (boolean, on by default)
  Whether to do a one-row look-ahead for SQL select statements. Normally the next result row beyond the current one being delivered is fetched at each loop iteration, so that it's always known whether there's at least one more row, even for unindexed queries. However, in some instances this may be undesired, eg. to save the processing time on one extra row when there's a small MAX limit, $rows.min isn't needed, and/or the query is unindexed. (Non-select statements never do look-ahead, to avoid doing more deletes/updates than the programmer may expect.) Added in version 3.0.958600000 20000517. Returns the previous setting.

• rmlocks [force] [verbose] $db
  Removes any stale locks on database $db. If the force option is given, all locks are removed, and the lock structure (including shared memory segment if applicable) is removed. This can be used prior to removing a database to clean up any attached resources. Note: Removing locks on an active database can cause data corruption. Returns 1 if successful, 0 if not. Added in version 3.01.985400000 20010323.

• addtable $file [$tbname [$db [$com [$user [$pass [$bits]]]]]]
  Adds a raw table $file to the database, like the command-line program addtable (see the Texis manual). This can be used when manually copying a .tbl file from one database to another, to register it with Texis for SQL access. The default SQL name for the table will be derived from the root name of $file, unless the $tbname parameter is given: eg. if $file is ``mybooks.tbl'', the table name defaults to ``mybooks''. The table will be added to the current (<DB>) database, unless the $db argument is given. A comment for SYSTABLES can be provided with the $com argument, and the table will be owned by $user (default PUBLIC). Note: The source table must have been created by the same platform type as the destination running addtable: the platform is printed in parentheses by texis -version. Do not manually copy a Texis file while it is being modified. Returns 1 if successful, 0 if not. Added in version 3.0.990500000 20010521.

  The $nbits argument (version 4.01.1030378283 20020826 and later) indicates the source file bit-size of $file, if it differs from the current Texis version. The file bit-size is indicated by the 4th dash-separated value in parentheses printed by texis -version. Setting this parameter allows a table produced by the same platform Texis (but a different file bit-size) to be adapted to the destination file bit-size. (All other values in the platform string should otherwise be identical.)

• copydbf $src $dest [$skip [$max]]
  Copies as much valid data as possible from Texis KDBF file $src and appends to file $dest, creating it if it does not exist, like the command-line program copydbf (see the Texis manual). This can be used to fix a corrupted table (see also the kdbfchk program in the Texis manual), or to compress a table by removing free space. The $dest file may be the same as $src, in which case the file will be overwritten in place; note however that if the copy fails, the source data will also be corrupted. If the $skip argument is given, that many blocks of initial data are skipped on input before copying begins. If $max is given, at most that many blocks (after $skip) are copied. Note: The source file must be from a machine of the same platform as its destination: the platform is printed in parentheses by texis -version and should be identical on both machines. Do not copy a Texis file while it is being modified, as no locking is used by copydbf. All indexes involving the destination file, if it is a table, must be dropped and re-created. Note: There is a leading KDBF block in table files before any SQL rows; add 1 to $skip/$max as appropriate to included it if needed. Returns 1 if successful, 0 if not. Added in version 3.0.990600000 20010522.

DIAGNOSTICS
sqlcp returns a setting-specific value.

EXAMPLE

<sqlcp cache close>

CAVEATS
The sqlcp function was added in version 2.1.905400000 19980910.

As stated above, single-user mode is dangerous. A sqlcp call to set single-user mode must take place at the start of a script, before any Texis handles are open.

The addtable and copydbf commands involve low-level manipulation of Texis files, and should be used with care.

No sqlcp calls should take place inside a SQL loop.

Additional control parameters are settable via a ``set var=value'' SQL statement; see the Texis manual for details.

SEE ALSO
apicp, SQLCACHE