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 here). 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 cleanupinterval $n

  Sets the cleanup interval in seconds for Vortex's Texis SQL handle cache. Returns 1 on success, 0 on failure, -1 on syntax error. Default interval is 10 seconds. Added in Vortex version 6.00.1306189000 20110523. During cleanup, old/stale handles may be closed if detected.

• cache close [db|exceptdb $dbList] Closes the Texis SQL 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 $dbList arguments were added in version 5.01.1111164819 20050318; if specified, only handles using database(s) given in $dbList will be closed. The optional exceptdb $dbList arguments were added in version 6.00.1293076410 20101222; if specified, all handles except those using database(s) given in $dbList will be closed.

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

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

• expressioncache close|{maxnum $N}

  If close given, closes the compiled SQL expression cache. If maxnum $N given, sets the maximum number of open expressions in the cache to $N; the default is 20. Note that the overhead for a compiled SQL expression is much smaller than for the equivalent cached SQL handle; in particular, no file, semaphore or shared-mem resources are needed. See the compilesqlexpressions pragma (here for more on compiled SQL expressions. Returns 1 on success, 0 on failure, -1 on syntax error. Added in version 6.01.

• 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.

   

• arrayconvert [func ...] [params|results ...] on|off [type ...] arrayconvert default|builtin

  Controls whether to convert Vortex arrays (multi-value variables) to multi-value fields and/or vice versa, when passing in and out of Texis. For example, a multi-value varchar Vortex variable might be converted to a single strlst for Texis. Converting arrays to multi-value fields and back allows lists to be manipulated easier, in their entirety, as <loop>ing over the array or hand-computing a merged value can often be avoided. This is especially handy with SQL functions that deal with lists of strings, e.g. the XML API.

  $func describes under what functions to do array conversion. It is zero or more of the values sql, timport, assign, expr; or all (the default) to indicate all functions. assign refers to variable assignment via SQL, i.e. in parentheses. expr refers to SQL expressions, e.g. in complex <if> statements.

  params or results indicates in which direction to do array conversion: params will convert Vortex $-variable parameters from arrays to multi-value fields, whereas results will convert multi-value result fields to Vortex arrays. The default is both ways.

  on or off indicates whether to turn the indicated conversion(s) on or off.

  $type is a list of zero or more source SQL types to do the conversion for, or alltypes to indicate all types. The default is every type, except char, indirect and byte for results (which would otherwise be split into single characters - usually undesired). For params, arrays of char, indirect and byte will be converted to strlst. Numeric, date, counter and recid params will be converted to the multi-value variable (var...) version of the same type. Arrays of strlst values will be merged into one strlst. Internal types will become a list. All other types cannot be converted and will pass as-is with an error message. For results, char, indirect and byte types will be split into one-char-per-value Vortex arrays (this is not normally enabled). strlst results will be split into Vortex varchar arrays. Numeric, date, counter and recid results will be split into non-var Vortex arrays. Internal types will be split into arrays. All other types cannot be converted.

  Alternatively, just default may be set to restore the default values; these are alterable with conf/texis.ini (here). Or just builtin may be set to restore the factory builtin defaults.

  Note: params conversion - from Vortex $-variable array to multi-value type - will only occur when the variable has more than one value in the current context. Thus, a single-value variable, or a multi-value variable in a loop context, will not be converted. In Texis version 6 and earlier, it may be useful to set the SQL setting varchartostrlstsep to create (which is already the default in version 7 and later) when inserting Vortex arrays into a strlst table column, as that setting will help convert single-value Vortex string arrays that arrayconvert will otherwise leave alone.

  Note: results conversion - from multi-value type to Vortex array - can cause parallel Vortex variables that are assigned in a looping function to become out of sync. E.g. <sql> results might have more than one value added to a variable per row, due to array conversion.

  Returns 1 on success, 0 on error. Added in version 6. The default is on for all types, except char, indirect and byte for results. Previous versions did not do array conversion, with the exception of multi-value varchar variable parameters to <sql>, which were converted into a parenthetical comma-separated list (i.e. for Metamorph). See also the metamorphstrlstmode SQL setting for how Metamorph deals with strlst queries, and the varchartostrlstsep SQL setting.

• 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, as well as other aspects of SQL engine use. 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 (here) for details on the various possible values, and the -tracesql command line option (here).

• 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, e.g. to save the processing time on one extra row when there's a small MAX limit, $rows.min / $sqlresult....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: e.g. 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