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 SQL expressions 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.)

• createlocksmethods $methods

  Sets the methods to try (sequentially) to create the locks structure (global file mapping, shared memory segment, or file, depending on platform) when accessing a database. The $methods list is an ordered CSV list of one or more of the tokens direct or monitor. direct tries to create the locks directly; monitor requests the Texis Monitor create them. See the [Texis] Createlocks Methods setting in texis.ini for more details; this sqlcp setting supercedes that config setting. Added in version 7.00.1372118000 20130624.

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

• arrayconvertwarnifv8change off|loose|strict

  Controls whether to issue a warning (arrayconvertwarnifv8change: Converted multi-value variable $multiValueVar to multi-value type: only first value would have been used in syntaxversion 7, possible behavior change) when arrayconvert converts a multi-value variable (to e.g. strlst) in an <if>, <switch>, or <while> statement that would not have been converted in syntaxversion 7 (where just the first value would have been used). The statement's behavior (i.e. true/false/match) is not otherwise affected by this setting.

  For example, the following <if>:

  <$multiValueVar = "Y" "Y">
        <if $multiValueVar eq "Y">...</if>

  is true in syntaxversion 7: it is a simple (non-SQL) <if>, and only the first value of variables are used in such expressions. However, in syntaxversion 8, the statement is false, because in that syntax all <if> expressions are evaluated via SQL, and thus arrayconvert is applied, and the var becomes a two-value strlst. Setting arrayconvertwarnifv8change to loose or strict would cause a warning when this <if> is run. Added in version 8.00.1628287082 20210806. Overrides [Texis] Array Convert Warn If Version 8 Change setting (here). Either can be used when running syntaxversion 7 code that has been converted to version 8, to catch some possible run-time behavior changes not known at compile time.

  The value loose differs from strict in that the former will not issue a warning when an empty-string literal, single-value empty-string variable, or zero-values variable is also in the expression. This prevents nuisance warnings when checking the (possibly multi-valued) output of e.g. XML API functions:

  <$children = (xmlTreeGetChildren($root))>
        <if "" eq $children>Error</if>

  In the above example, $children would normally be multi-valued, and thus the warning would normally be triggered. It can safely be ignored (with loose) because regardless of passing the first value (syntaxversion 7) or all values (version 8), the test is valid in this case.

• nulloutputsring (string, "NULL" by default)

  Same as the SQL nulloutputstring property: sets the string to output for SQL NULL values. Note that this is different from the string for zero-integer date values, which is always "NULL". Added in version 7.02.1405382000 20140714. Returns 1 on success, 0 on error.

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

• tracemetamorph (boolean/integer, 0 by default)

  For debugging: trace Metamorph searches. This is an integer value whose bits control various Metamorph tracing messages. The bit flag values are subject to change without notice. Added in version 7.02.1406336000 20140725. Current values:

  • 0x0001 Set/phrase/pattern-matcher object open/close calls

  • 0x0002 findsel() calls

  • 0x0004 inset() rejection

  • 0x0008 remorph() checks

  • 0x0010 Phrase checks

  • 0x0100 Overall getmm() hit/miss

  • 0x1000 getppm() calls

  • 0x2000 PPM internal calls (pre-phrase)

  Returns previous value. Flag 0x0100 was added in version 7.07.1562009000 20190701.

• tracerowfields (string, empty by default)

  For debugging: trace Texis row reads. This is a CSV list of field(s) to print whenever table rows are read, in the form "table.field[, table.field ...]". Table and/or field may be "*" for all tables and/or all fields. Added in version 7.02.1406752000 20140730.

• 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 (integer)

  Debug setting; traces KDBF calls. Value is a set of bit flags, which may change in a future release; currently defined values are:

  • 0x00000001: After open()/close()

  • 0x00000002: Not used currently

  • 0x00000004: After read()

  • 0x00000008: After write()

  • 0x00000010: After ioctl() (seek-significant)

  • 0x00000020: After ioctl() (other)

  • 0x00000040: Data after read()

  • 0x00000080: Data after write()/ioctl()

  At least one of the following control flags must be given to enable the above "after" flags:

  • 0x00001000: After user calls

  • 0x00002000: After internal calls

  These flags issue messages before their action:

  • 0x00010000: Before open()/close()

  • 0x00020000: Not used currently

  • 0x00040000: Before read()

  • 0x00080000: Before write()

  • 0x00100000: Before ioctl() (seek-significant)

  • 0x00200000: Before ioctl() (other)

  • 0x00400000: Data before read()

  • 0x00800000: Data before write()/ioctl()

  At least one of the following control flags must be given to enable the above "before" flags:

  • 0x10000000: Before user calls

  • 0x20000000: Before internal calls

• tracekdbffile (string)

  Debug setting; may change in a future release. Controls which file(s) tracekdbf applies to; the default if empty/unset is all KDBF files. Value is an optional database directory with optional file, e.g. database/file.tbl: if no directory given, the given file is traced in any/all databases; if no file given, all files in just the given directory are traced. The file may also be SYS or USR to indicate all system or all user KDBF files (this may also be given with a directory prefix).

• kdbfiostats (integer or file, 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.

• verbose [sql|assign|expr|other|all ...] N|default

  Debug setting; increases verbosity in SQL engine to level N, or default level if default given. This is the same value that the SQL statement set verbose controls, except applied only during the usage(s) specified. Verbosity applies to SQL usage specified by zero or more tokens given before level:

  • sql <SQL> statements

  • assign Variable assignments

  • expr <if>/<while>/etc. expressions

  • other Other usage

  • all All of the above

  If no usage tokens are given, all is assumed. Added in version 6.00.1330636000 20120301.

• autocreatedb or autocreate (boolean, on by default)

  Whether to attempt to automatically create a database when needed for SQL expressions in variable assignment, <IF> and <WHILE> statements (but not <SQL>). If the current database is needed for these statements but cannot be opened, Vortex attempts to create it if autocreatedb is on, which may be a problem in some circumstances. With autocreatedb off, the database is not automatically created and such statements fail if the database does not exist. 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. In version 7.05 and later, the lookahead value at <sql> statement start is used throughout that <sql> loop; in previous versions changing the lookahead value inside the loop could have deleterious side-effects (e.g. early termination of the loop).

• 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