%SSC_CMD

Set database-specific options

value = %SSC_CMD(dbchannel, [cursor], option, parstring)

Return value

value

This function returns SSQL_NORMAL (success) or SSQL_FAILURE (failure). (i)

Arguments

dbchannel

An internal database channel previously initialized using %SSC_INIT and connected by %SSC_CONNECT. (n)

cursor

This argument is required for SSQL_CMD_SCROLL. It is ignored and can be omitted for all other options. (n)

option

An option that executes a database-specific command. (n)

parstring

Parameters for the specified option. Parameters must be separated by spaces. The maximum size of the string is 60 characters. (a)

Discussion

%SSC_CMD executes a database-specific command (option).

Supported option values are listed in the table below and are defined in the ssql.def file distributed with Connectivity Series. An option that is not supported by the database driver is ignored.

An option setting whose “Duration of setting” is listed as “Connection” lasts for the duration of the connection (the channel) or until a subsequent %SSC_CMD call changes the setting.

Database-Specific Options
Option	Description/parameters	Database driver(s)	Duration of setting
SSQL_CACHE_CHAIN	Instructs %SSC_RELEASE to cache connections and preserves the cache when chaining to other programs. See SSQL_CACHE_CHAIN below. Parameters: none	All (on Windows and UNIX only)	Runtime instance
SSQL_CACHE_CONNECTION	Instructs %SSC_RELEASE to cache connections, but allows cached connections to be closed when chaining to other programs. See SSQL_CACHE_CONNECTION below. Parameters: none	All (on Windows and UNIX only)	Runtime instance
SSQL_CMD_ODBC_CONNATTR	Sets SqlSetConnectAttr or SQLSetStmt options for SQL Server. See SSQL_CMD_ODBC_CONNATTR below. Parameters: SSQL_COPT_SS_TXN_ISOLATION+" option" SSQL_COPT_SS_ENCRYPT+" switch" SSQL_COPT_SS_INTEGRATED_SECURE+" switch" SSQL_COPT_SS_MARS_ENABLED+" switch" SSQL_COPT_SS_CURSOR_OPTIONS+" option"	VTX12_SQLNATIVE	Connection
SSQL_CMD_SCROLL	Sets scrolling behavior for a scrolling cursor (specified by the cursor argument) that has been opened on the channel. See SSQL_CMD_SCROLL below. Parameters: SSQL_SCROLL_CURRENT SSQL_SCROLL_FIRST SSQL_SCROLL_LAST SSQL_SCROLL_NEXT (default) SSQL_SCROLL_PRIOR SSQL_SCROLL_ABSOLUTE+" n" SSQL_SCROLL_RELATIVE+" [-]n"	All (except SSQL_SCROLL_PRIOR and SSQL_SCROLL_RELATIVE, which do not work with MySQL)	Connection
SSQL_CMD_SSL	Sets SQL OpenNet client settings for SSL encryption. See SSQL_CMD_SSL below.	All (when using SQL OpenNet)	Connection
SSQL_CURSOR_TYPE	Sets an ODBC cursor type for database cursors that are subsequently opened or rebound on the channel. See SSQL_CURSOR_TYPE below. Parameters: SSQL_CURSOR_DYNAMIC SSQL_CURSOR_FORWARD_ONLY SSQL_CURSOR_KEYSET_DRIVEN SSQL_CURSOR_STATIC SSQL_CURSOR_DEFAULT (same as SSQL_CURSOR_FORWARD_ONLY)	VTX11 VTX12_SQLNATIVE	Connection
SSQL_KEEP_OPEN	Prevents the runtime from closing connections on a program chain. See SSQL_KEEP_OPEN below. Parameters: none	All (on Windows and UNIX only)	Runtime instance
SSQL_LANGVER	Specifies Oracle parser syntax compatibility. See SSQL_LANGVER below. Parameters: OCI_NTV_SYNTAX OCI_V7_SYNTAX (default) OCI_V8_SYNTAX	VTX0_n	Connection
SSQL_NEW_BLOBS	Specifies use of BLOB/CLOB instead of LONG RAW/LONG. See SSQL_NEW_BLOBS below. Parameters: yes\|no Default is no.	VTX0_n	Connection
SSQL_ODBC_AUTOCOMMIT	Turns autocommit on or off. Yes turns autocommit on, which means that every SQL statement is automatically committed. See SSQL_ODBC_AUTOCOMMIT below. Parameters: yes\|no Default is no for SQL Server and MySQL, yes for ODBC and Synergy Database.	VTX4 VTX11 VTX12_SQLNATIVE VTX14_n	Connection
SSQL_OLD_ZONEDDATE	Specifies pre-7.1 behavior if %SSC_MOVE is used to move date fields to zoned fields. See SSQL_OLD_ZONEDDATE below. Parameters: none	All	Connection
SSQL_PERFORMANCE_SQL	Improves SQL Server statement caching. See SSQL_PERFORMANCE_SQL below. Parameters: yes\|no Default is no. We recommend setting SSQL_PERFORMANCE_SQL to yes (or setting the SQLPERFORMANCESQL environment variable).	VTX12_SQLNATIVE	Connection
SSQL_RAWDATE	Specifies whether to return date/time untouched. See SSQL_RAWDATE below. Parameters: yes\|no Default is no.	All	Connection
SSQL_RETURN_ROWID	Determines whether a row ID will be returned for each SQL statement. See SSQL_RETURN_ROWID below. Parameters: yes\|no Default is yes.	VTX0_n	Connection
SSQL_RO_CURSOR	Sets database cursors that are subsequently opened or rebound on the channel to read-only. See SSQL_RO_CURSOR below. Parameters: yes\|no Default is no. (We recommend setting to yes.)	VTX11 VTX12_SQLNATIVE	Connection
SSQL_SQL_BULK_INSERT	Enables or disables bulk inserts. See SSQL_SQL_BULK_INSERT below. Parameters: yes\|no Default is no.	VTX12_SQLNATIVE	Connection
SSQL_TIMEOUT	Sets resource time-out to n number of seconds. See SSQL_TIMEOUT below. Parameters: n Default is database-dependent.	All	Connection
SSQL_TRIMCHAR	Changes data type (dty) for character string conversions. See SSQL_TRIMCHAR below. Parameters: dty Default is 1, which is VARCHAR.	VTX0_n	Connection
SSQL_TXN_ISOLEVEL	Sets the ODBC cursor isolation level for subsequently opened or rebound database cursors or subsequently executed statement cursors. See SSQL_TXN_ISOLEVEL below. Parameters: SSQL_TXN_READ_COMMITTED SSQL_TXN_READ_UNCOMMITTED SSQL_TXN_REPEATABLE_READ SSQL_TXN_SERIALIZABLE	VTX11 VTX12_SQLNATIVE	Connection
SSQL_USEDB	Specifies a database name for connections strings in subsequent %SSC_OPEN calls. See SSQL_USEDB below. Parameters: dbname No default.	VTX12_SQLNATIVE	Connection

SSQL_CACHE_CHAIN

On Windows and UNIX, this option instructs %SSC_RELEASE to cache database connections and preserves the cache when chaining to other programs. When SSQL_CACHE_CHAIN is in effect, any program you chain to must still call %SSC_INIT and %SSC_CONNECT for each database connection, but %SSC_CONNECT checks connections cached by %SSC_RELEASE and uses one if possible. For a cached connection to be used, however, the connection string passed to %SSC_CONNECT must be identical to the connection string used for the cached connection.

You can use the force_release argument for %SSC_RELEASE to override this setting.

SSQL_CACHE_CHAIN is identical to SSQL_CACHE_CONNECTION, except that with SSQL_CACHE_CONNECTION cached connections are closed when a program chains.

On OpenVMS, this option causes errors.

SSQL_CACHE_CONNECTION

This option is identical to SSQL_CACHE_CHAIN except that with this option, cached connections are closed when a program chains. Note that it is generally better to use SSQL_CACHE_CHAIN and maintain the connection cache. Use SSQL_CACHE_CONNECTION only if connection strings for the new program (the program that is assuming control) are different than the connection strings in the original program.

On OpenVMS, this option causes errors.

SSQL_CMD_ODBC_CONNATTR

Sets one of the following SQLSetConnectAttr or SQLSetStmt options when using VTX12_SQLNATIVE. An SSQL_CMD_ODBC_CONNATTR setting will affect a connection only if it is set before the call to %SSC_CONNECT. See your database documentation for more information on these options.

SSQL_COPT_SS_TXN_ISOLATION+" option"

One option is available for this: SSQL_TXN_SS_SNAPSHOT. This option results in a single view of the database (a snapshot) for the duration of the connection. (By default this feature is not used.) For example, the following activates this feature:

sts=(%ssc_cmd(dbchn, cur2, SSQL_CMD_ODBC_CONNATTR, SSQL_COPT_SS_TXN_ISOLATION+" SSQL_TXN_SS_SNAPSHOT"))

SSQL_COPT_SS_ENCRYPT+" switch"

Determines whether connection encryption is used. Switch must be either SSQL_IS_ON, which activates this feature, or SSQL_IS_OFF (the default), which deactivates it. For example, the following activates this feature:

sts=(%ssc_cmd(dbchn, cur2, SSQL_CMD_ODBC_CONNATTR, SSQL_COPT_SS_ENCRYPT+" SSQL_IS_ON"))

SSQL_COPT_SS_INTEGRATED_SECURE+" switch"

Determines whether Windows Authentication is used when accessing SQL Server on a server. Switch must be either SSQL_IS_ON, which activates this feature, or SSQL_IS_OFF (the default), which deactivates it. For example, the following activates this feature:

sts=(%ssc_cmd(dbchn, cur2, SSQL_CMD_ODBC_CONNATTR, SSQL_COPT_SS_INTEGRATED_SECURE+" SSQL_IS_ON"))

SSQL_COPT_SS_MARS_ENABLED+" switch"

Determines whether the Multiple Active Result Sets (MARS) feature for SQL Server is used for the connection. Switch must be either SSQL_IS_ON, which activates this feature, or SSQL_IS_OFF (the default), which deactivates it. For example, the following activates this feature:

sts=(%ssc_cmd(dbchn, cur2, SSQL_CMD_ODBC_CONNATTR, SSQL_COPT_SS_MARS_ENABLED+" SSQL_IS_ON"))

SSQL_COPT_SS_CURSOR_OPTIONS+" option"

Enables you to set one of the following SQL Server ODBC driver cursor options. For more information on these settings, see Microsoft documentation (e.g., learn.microsoft.com/en-us/sql/relational-databases/native-client-odbc-api/sqlsetstmtattr#sql_sopt_ss_cursor_options).

SSQL_COPT_CO_FFO - Sets SQL_CO_FFO, which enables fast-forward read-only cursors.
SSQL_COPT_CO_AUTOFETCH - Sets SQL_CO_AF, which enables autofetch.
SSQL_COPT_CO_AUTOFETCHFFO - Sets SQL_CO_FFO_AF, which enables fast-forward read-only cursors with the autofetch option.
SSQL_COPT_CO_FIREHOSE - Sets SQL_CO_OFF, which disables fast-forward and read-only cursors with the autofetch option.

For example, the following sets SSQL_COPT_CO_AUTOFETCHFFO (which sets SQL_CO_FFO_AF):

sts=(%ssc_cmd(dbchn, cur2, SSQL_CMD_ODBC_CONNATTR, SSQL_COPT_SS_CURSOR_OPTIONS+" SSQL_COPT_CO_AUTOFETCHFFO"))

if SSQL_RO_CURSOR is set, SSQL_COPT_SS_CURSOR_OPTIONS is ignored, but either SQL_CO_FFO or SQL_CO_FFO_AF is set automatically. See SSQL_RO_CURSOR below for details.

SSQL_CMD_SCROLL

Sets the scrolling behavior for a scrolling cursor (specified by the cursor argument). This does not make a cursor into a scrolling cursor; it sets scrolling options for a scrolling cursor. (See %SSC_OPEN for options that create scrolling cursors.) An SSQL_CMD_SCROLL setting determines which row will be retrieved in the next fetch for a scrolling cursor:

SSQL_SCROLL_CURRENT

The row at the current cursor position

SSQL_SCROLL_FIRST

The first row in the result set

SSQL_SCROLL_LAST

The last row in the result set

SSQL_SCROLL_NEXT

The row that follows the row at the current cursor position (default)

SSQL_SCROLL_PRIOR

The row that proceeds the row at the current position

SSQL_SCROLL_ABSOLUTE" n"

A specific row in the result set where n is the number of the row you want retrieved with the next fetch. If n is a positive number, the nth row from the beginning of the result set will be fetched. If n is a negative number, the nth row from the end of the result set will be fetched. If n is 0, the row at the current cursor position will be the next row fetched. The following, for example, retrieves data from the third row from the end of the result set:

sts=(%ssc_cmd(dbchn, cur2, SSQL_CMD_SCROLL, SSQL_SCROLL_ABSOLUTE+" 3"))

SSQL_SCROLL_RELATIVE+" [-]n"

A specific row relative to the current cursor position where n is the number of the rows beyond the current cursor position if n is positive. If negative, the nth row before the current cursor position will be fetched. If n is 0, the row at the current cursor position will be the next row fetched. For example, the following fetches the previous row (the row that precedes the current cursor position):

sts=(%ssc_cmd(dbchn, cur2, SSQL_CMD_SCROLL, SSQL_SCROLL_RELATIVE+" -1"))

The next example fetches the next row (the row that follows the current cursor position):

sts=(%ssc_cmd(dbchn, cur2, SSQL_CMD_SCROLL, SSQL_SCROLL_RELATIVE+" 1"))

For more information on cursor scrolling, see Using cursors with SQL Connection and your database documentation.

SSQL_CMD_SSL

Sets SSL encryption (data packet encryption) client settings for SQL OpenNet connections. Use this after an %SSC_INIT call and before %SSC_CONNECT calls. Settings made with SSQL_CMD_SSL apply to subsequent connections and override net.ini settings for SSL (if SSL=yes is passed). An SSQL_CMD_SSL parameter string must start with “3;” (without quotation marks) and can include the following settings separated by semicolons:

SSL=yes|no

Specifies whether the SQL OpenNet client requires SSL encryption for subsequent connections. If this is set to yes, the SQL OpenNet service must be set to use SSL encryption. Additionally, if SSL_CERTIFICATE and SSL_PROTOCOL settings are included in the parameter string, they are used for subsequent connections (and SSL settings in net.ini are ignored).

If SSL is set to no (the default), the SSL_CERTIFICATE and SSL_PROTOCOL parameters are ignored if passed, and the client will require SSL encryption only if the SSL option in net.ini is set. (Note that SSL encryption is used if the SQL OpenNet server is set to use it, regardless of this setting or the SSL setting in net.ini.)

For information on installing and configuring SSL for SQL OpenNet, see Using data packet encryption for SQL OpenNet. For information on net.ini settings, see Setting SQL OpenNet client options in net.ini.

SSL_CERTIFICATE=file_spec

Specifies the name and location of a file on the client that is used to validate the server certificate for SSL (the certificate used for the SQL OpenNet service). If the server certificate is from a trusted certificate authority (CA), this setting should specify a certificate trust store file on the client (see Requesting a certificate from a certificate authority). If the certificate for the server is self-signed, this setting should specify a root certificate on the client (see Creating a local certificate authority).

If this is not specified for the client, the client will validate the server certificate only if a trust store file or root certificate is specified in net.ini.

This setting is used only if SSL=yes is passed in the parameter string.

SSL_PROTOCOL=level#[,level#...]

Specifies the TLS protocol level(s) the client requires for SSL encryption. Levels 1.1 and 1.2 are supported for SQL OpenNet, but server settings determine which levels are available for SQL OpenNet connections (see Encrypting data packets (-e)). Security best practices require TLS 1.2. If the level (or levels) specified by the client is lower than available levels for SQL OpenNet, an error will be reported.

To specify more than one TLS level, enter the levels separated by a comma. With OpenSSL 1.0.2, SQL OpenNet will use the highest of these levels that is available for the SQL OpenNet service. With OpenSSL 1.1.1x, SQL OpenNet will use the first level specified here as a minimum (if a higher level is available, it will use that), and it will ignore any other TLS levels specified here.

This setting is used only if SSL=yes is passed in the parameter string.

The following example specifies a certificate trust store file (cert.pem) and TLS level 1.2:

3;SSL=yes;SSL_CERTIFICATE=/etc/certs/crt.pem;SSL_PROTOCOL=1.2

SSQL_CURSOR_TYPE

Sets an ODBC cursor type for subsequently opened database cursors (%SSC_OPEN calls with SSQL_SELECT) and rebound cursors (%SSC_REBIND) on the channel when using VTX11 or VTX12_SQLNATIVE. Note that the default cursor type for VTX11 is forward-only, and the default for VTX12_SQLNATIVE is dynamic.

SSQL_CURSOR_DYNAMIC

Dynamic cursor

SSQL_CURSOR_FORWARD_ONLY

Forward-only cursor

SSQL_CURSOR_KEYSET_DRIVEN

Keyset-driven cursor

SSQL_CURSOR_STATIC

Static cursor

SSQL_CURSOR_DEFAULT

Equivalent to SSQL_CURSOR_FORWARD_ONLY

We recommend using SSQL_CURSOR_TYPE with SSQL_RO_CURSOR unless you want to set cursor types explicitly with the SSQL_CMD_ODBC_CONNATTR options. For more information on cursors, including information on database cursor types and how to set them, see Using cursors with SQL Connection and your database documentation.

%SSC_OPEN scrolling options (except SSQL_SCROLL) override the SSQL_CURSOR_TYPE options. For example, if you set SSQL_CURSOR_STATIC and then set SSQL_SCROLL_DYNAMIC in the %SSC_OPEN call, the cursor will be opened as a scrolling dynamic cursor. (However, if you set SSQL_CURSOR_STATIC in an %SSC_CMD call and then set SSQL_SCROLL in and %SSC_OPEN call, the cursor will be opened as a scrolling static cursor.)

SSQL_KEEP_OPEN

On Windows and UNIX, this option ensures that the Synergy runtime does not shut down connections in a program chain. If you use this option, do not use %SSC_INIT or %SSC_CONNECT in a program you’re chaining to.

On OpenVMS, this option causes errors.

SSQL_LANGVER

Specifies the version of the Oracle parser to be used.

OCI_NTV_SYNTAX

Instructs the database driver to use the default parser for the Oracle database the program is connected to.

OCI_V7_SYNTAX

Instructs the database driver to use Oracle7 syntax. This is the default.

OCI_V8_SYNTAX

Instructs the database driver to use Oracle8 syntax.

SSQL_NEW_BLOBS

Instructs Oracle to use BLOB or CLOB data rather than LONG RAW or LONG data.

SSQL_ODBC_AUTOCOMMIT

Enables you to turn autocommit mode on or off.

SSQL_OLD_ZONEDDATE

Restores pre-version 7 behavior when date fields are retrieved into decimal fields with %SSC_MOVE. When SSQL_OLD_ZONEDDATE is set, decimal fields are treated as alpha fields when used in conjunction with an %SSC_OPTION date-time mask. For this pre-version 7 behavior to take effect, this option must be set before using %SSC_OPEN.

SSQL_PERFORMANCE_SQL

Calls the ODBC API function SQLDescribeParam behind the scenes to improve performance and avoid excessive cache memory thrashing for SQL statements that have I/O parameters when accessing SQL Server (VTX12_SQLNATIVE). We recommend setting SSQL_PERFORMANCE_SQL to yes (or setting the SQLPERFORMANCESQL environment variable).

When this option is set to yes, this functionality is invoked for SQL Server cursors subsequently opened on the specified database channel. (For information on an environment variable that invokes this functionality for all SQL Server cursors, see SQLPERFORMANCESQL.)

This feature will work for a stored procedure only if the case of column names in the SQL statement matches the case of column names in the database. (This is a SQLDescribeParam limitation.) If inconsistent case prevents this feature from working, an %SSC_EXEC failure on SQLDescribeParam is written to the SSQLLOG file. See SQL Connection logging for information on SSQLLOG logging.

SSQL_RAWDATE

Returns date/time untouched (does not convert date/time to the data type of the defined variable).

SSQL_RETURN_ROWID

Determines whether a row ID will be returned for each SQL statement when using VTX0 (Oracle). By default, a row ID will not be returned.

SSQL_RO_CURSOR

Instructs SQL Connection to use fast-forward read-only cursors (SQL_CO_FFO) or fast-foward read-only cursors with autofetch (SQL_CO_FFO_AF) when possible for subsequently opened cursors (subsequent %SSC_OPEN calls) and for subsequently rebound cursors (subsequent %SSC_REBIND calls). SSQL_RO_CURSOR overrides SSQL_CMD_ODBC_CONNATTR settings, and it sets

SQL_CO_FFO if SSQL_CURSOR_TYPE is set to SSQL_CURSOR_DYNAMIC, SSQL_CURSOR_FORWARD_ONLY, or SSQL_CURSOR_KEYSET_DRIVEN. (This is equivalent to setting SSQL_CMD_ODBC_CONNATTR with SSQL_COPT_CO_FFO.)
SQL_CO_FFO_AF if SSQL_CURSOR_TYPE is set to SSQL_CURSOR_STATIC. (This is equivalent to setting SSQL_CMD_ODBC_CONNATTR with SSQL_COPT_CO_AUTOFETCHFFO.)

We recommend using SSQL_RO_CURSOR if your application uses VTX12_SQLNATIVE, uses static or dynamic cursors (the default), does not use multiple result sets, and does not update the database. Otherwise SSQL_RO_CURSOR doesn’t apply. (Note that you should avoid dynamic and static cursors whenever possible for performance reasons.)

SSQL_SQL_BULK_INSERT

If you use array variables in an %SSC_EXECUTE call for a SQL Server database, this option enables you to use bulk inserts, which improve performance. However, you should use this option only when no concurrent database activity is expected for the affected rows. You can use this option, for instance, to improve performance when loading initial data into database tables. Note the following:

The bulk insert feature does not use the full SQL statement passed in %SSC_OPEN. It uses the table information and the bind variables (:1, :2, etc.), but ignores the rest of the statement.
There must be as many bind variables as there are columns in the table, including any timestamp column. The first bind variable corresponds to the first column in the table, the second bind variable corresponds to the second column, and so forth.
If there is a timestamp column in the table, you must pass an empty string (“”) for this column if there is no data for the row.
Dates inserted using bulk insert must have the “YYYY-MM-DD” format.
SQL statements for bulk inserts cannot contain functions.

SSQL_TIMEOUT

Specifies the time-out for resources (locked records, query execution, stored procedure execution, etc.). SSQL_TIMEOUT sets the number of seconds to wait before returning a resource error. If a resource error occurs before the time-out, the database immediately returns the error (it does not wait for the time-out).

Each database’s support for resource time-out is different (whether it is supported, what qualifies for a resource time-out, the time-out default, etc.). For example, our default for SQL Server is 60 seconds, the default for MySQL is 50 seconds (to change the MySQL default, you must change the configuration for MySQL), and Oracle does not support resource time-out.

SSQL_TRIMCHAR

Defines the Oracle data type used for character conversions from SQL Connection to Oracle database char and varchar columns. Dty is the Oracle data type to use; see your Oracle documentation for the values of different Oracle data types.

1 instructs SQL Connection to use the VARCHAR data type when converting to a char field so that no trailing blanks are stored. If a field consists entirely of spaces, the field will be stored as null. This is the default.
96 instructs SQL Connection to use the CHAR data type when converting string data so that trailing blanks are stored.

SSQL_TXN_ISOLEVEL

Specifies one of the following ODBC cursor isolation levels for database cursors subsequently opened or reopened (with %SSC_OPEN), statement cursors subsequently executed (with %SSC_EXECUTE or %SSC_EXECIO), or cursors subsequently rebound (with %SSC_REBIND) or linked (with %SSC_SQLLINK):

SSQL_TXN_READ_COMMITTED

SSQL_TXN_READ_UNCOMMITTED

SSQL_TXN_REPEATABLE_READ

SSQL_TXN_SERIALIZABLE

See your database documentation for more information, and note that all SQL Server connections, including connections for SQL Azure, default to SSQL_TXN_READ_COMMITTED. However, for lock timeouts to work with SQL Azure, SSQL_TXN_ISOLEVEL must specify SSQL_TXN_READ_UNCOMMITED.

SSQL_USEDB

Specifies a default database name (database_name) for connect strings passed in subsequent %SSC_OPEN calls when connecting to SQL Server.

If you’ve submitted an SQL statement and want to use it for another database, use this option (rather than a USE DATABASE command) to specify the new database. (A USE DATABASE command generally causes errors in this situation because it allows cached statements for the original database to be used rather than submitting the statement anew to the specified database.)

The following example from the exam_fetch.dbl example program uses %SSC_CMD to select the SQL Server database “PUBS”.

if (%ssc_cmd(dbchn, cur3, SSQL_USEDB, "pubs")) exit