%SSC_CMD

Set database-specific options

WTSupported in traditional Synergy on Windows
WNSupported in Synergy .NET on Windows
USupported on UNIX
VSupported on OpenVMS
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. Note the following:

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.

Parameters: none

See SSQL_CACHE_CHAIN below.

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.

Parameters: none

See SSQL_CACHE_CONNECTION below.

All (on Windows and UNIX only)

Runtime instance

SSQL_CMD_ODBC_CONNATTR

Sets SqlSetConnectAttr options for SQL Server.

Parameters:

SSQL_COPT_SS_TXN_ISOLATION+" option"
SSQL_COPT_SS_ENCRYPT+" switch"
SSQL_COPT_SS_INTEGRATED_SECURE+" switch"
SSQL_COPT_SS_MARS_ENABLED+" switch"

See SSQL_CMD_ODBC_CONNATTR below.

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.

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"

See SSQL_CMD_SCROLL below.

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.

Parameters:
SSQL_CURSOR_DYNAMIC
SSQL_CURSOR_FORWARD_ONLY
SSQL_CURSOR_KEYSET_DRIVEN
SSQL_CURSOR_STATIC
SSQL_CURSOR_DEFAULT (same as SSQL_CURSOR_FORWARD_ONLY)

We recommend setting this to SSQL_CURSOR_FORWARD_ONLY.

See SSQL_CURSOR_TYPE below.

VTX11
VTX12_SQLNATIVE

Connection

SSQL_KEEP_OPEN

Prevents the runtime from closing connections on a program chain.

Parameters: none

See SSQL_KEEP_OPEN below.

All (on Windows and UNIX only)

Runtime instance

SSQL_LANGVER

Specifies Oracle parser syntax compatibility.

Parameters:
OCI_NTV_SYNTAX
OCI_V7_SYNTAX (default)
OCI_V8_SYNTAX

See SSQL_LANGVER below.

VTX0_n

Connection

SSQL_NEW_BLOBS

Specifies use of BLOB/CLOB instead of LONG RAW/LONG.

Parameters: yes|no

Default is no.

See SSQL_NEW_BLOBS below.

VTX0 (when connected to Oracle 10 or Oracle 11)

Connection

SSQL_ODBC_AUTOCOMMIT

Turns autocommit on or off. Yes turns autocommit on, which means that every SQL statement is automatically committed.

Parameters: yes|no
Default is no for SQL Server and MySQL, yes for ODBC and Synergy Database.

See SSQL_ODBC_AUTOCOMMIT below.

VTX11
VTX4
VTX12_SQLNATIVE
VTX14

Connection

SSQL_OLD_ZONEDDATE

Specifies pre-7.1 behavior if %SSC_MOVE is used to move date fields to zoned fields.

Parameters: none

See SSQL_OLD_ZONEDDATE below.

All

Connection

SSQL_ONEPID

Ensures that a single process is used for a transaction.

Parameters: yes|no
Default is no.

See SSQL_ONEPID below.

VTX2

Connection

SSQL_PERFORMANCE_SQL

Improves SQL Server statement caching.

Parameters: yes|no
Default is no.

We recommend setting SSQL_PERFORMANCE_SQL to yes (or setting the SQLPERFORMANCESQL environment variable).

See SSQL_PERFORMANCE_SQL below.

VTX12_SQLNATIVE Connection

SSQL_RAWDATE

Specifies whether to return date/time untouched.

Parameters: yes|no
Default is no.

See SSQL_RAWDATE below.

All

Connection

SSQL_RETURN_ROWID

Determines whether a row ID will be returned for each SQL statement.

Parameters: yes|no
Default is yes.

See SSQL_RETURN_ROWID below.

VTX0
VTX5

Connection

SSQL_RO_CURSOR

Sets database cursors that are subsequently opened or rebound on the channel to read-only.

Parameters: yes|no
Default is no.

We recommend setting SSQL_RO_CURSOR to yes.

See Using cursors with SQL Connection for more information.

VTX11
VTX12_SQLNATIVE

Connection

SSQL_SQL_BULK_INSERT

Enables or disables bulk inserts.

Parameters: yes|no
Default is no.

See SSQL_SQL_BULK_INSERT below.

VTX12_SQLNATIVE

Connection

SSQL_SYB_BLANK

Specifies whether to return blank instead of null.

Parameters: yes|no
Default is no.

See SSQL_SYB_BLANK below.

VTX2

Connection

SSQL_TIMEOUT

Sets resource time-out to n number of seconds.

Parameters: n
Default is database-dependent.

See SSQL_TIMEOUT below.

All

Connection

SSQL_TRIMCHAR

Changes data type (dty) for character string conversions.

Parameters: dty
Default is 1, which is VARCHAR.

See SSQL_TRIMCHAR below.

VTX0

Connection

SSQL_TXN_ISOLEVEL

Sets the ODBC cursor isolation level for subsequently opened or rebound database cursors or subsequently executed statement cursors.

Parameters:
SSQL_TXN_READ_COMMITTED
SSQL_TXN_READ_UNCOMMITTED
SSQL_TXN_REPEATABLE_READ
SSQL_TXN_SERIALIZABLE

See SSQL_TXN_ISOLEVEL below.

VTX11
VTX12_SQLNATIVE

Connection

SSQL_USEDB

Specifies a database name for connections strings in subsequent %SSC_OPEN calls.

Parameters: dbname
No default.

See SSQL_USEDB below.

VTX2
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. See %SSC_RELEASE for more information.

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 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_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_ABSOLUTEn"

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 setting SSQL_CURSOR_FORWARD_ONLY. 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.

Note

%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_ONEPID

Instructs the database to use a single process for operations. We don’t recommend using this option, though it does work with Sybase. And, while this may eliminate some deadlock errors, it can also adversely affect performance. Additionally, nested queries (for example, combined fetch and update operations) will not work if this is set.

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

Note

Because this uses SQLDescribeParam, the case of column names in statements must match the case of the column names in the database.

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), or VTX5 (Informix). By default, a row ID will not be returned.

SSQL_RO_CURSOR

Instructs SQL Connection to use fast-forward cursors (SQL_CO_FFO) if possible for subsequently opened cursors (subsequent %SSC_OPEN calls) and for subsequently rebound cursors (subsequent %SSC_REBIND calls). We recommend using fast-forward cursors; however, this option applies only if your application uses VTX12_SQLNATIVE, uses static or dynamic cursors (the default), and does not update the database.

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:

SSQL_SYB_BLANK

Specifies that a single blank VARCHAR data field should return a space instead of null on Sybase.

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.

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 or Sybase.

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