|
WTSupported in traditional Synergy on Windows
|
WNSupported in Synergy .NET on Windows
|
USupported on UNIX
|
VSupported on OpenVMS
|
value = %SSC_CMD(dbchannel, [dbcursor], 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)
dbcursor
(optional) This argument is currently ignored. (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:
- If the option value is not supported by the database driver, it is ignored.
- Option settings whose duration of setting is listed as “Connection” in the table below last for the duration of the connection or until a subsequent %SSC_CMD call changes the setting.
- Many of these options affect subsequent opens with %SSC_OPEN, but don’t affect current cursors.
|
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_SCROLL |
For a scrolling cursor, determines which result set row will be retrieved with the next fetch. Parameters: See SSQL_CMD_SCROLL below. |
All (except SSQL_SCROLL_PRIOR and SSQL_SCROLL_RELATIVE, which do not work with MySQL) |
Cursor |
| 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 the next %SSC_OPEN. Parameters: Default for the VTX12_* drivers is SSQL_CURSOR_DYNAMIC. Default for VTX11 is SSQL_CURSOR_FORWARD_ONLY. See SSQL_CURSOR_TYPE below. |
VTX11 |
Connection |
|
SSQL_KEEP_OPEN |
Prevents 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: 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 See SSQL_ODBC_AUTOCOMMIT below. |
VTX11 |
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 See SSQL_ONEPID below. |
VTX2 |
Connection |
|
SSQL_RAWDATE |
Specifies whether to return date/time untouched. Parameters: yes|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 See SSQL_RETURN_ROWID below. |
VTX0 |
Connection |
|
SSQL_RO_CURSOR |
Specifies fast-forward cursors (SQL_CO_FFO) if possible for next cursor opened with %SSC_OPEN. Parameters: yes|no See SSQL_RO_CURSOR below. |
VTX12_SQLNATIVE |
Connection |
|
SSQL_SQL_BULK_INSERT |
Enables or disables bulk inserts. Parameters: yes|no See SSQL_SQL_BULK_INSERT below. |
VTX12_SQLNATIVE |
Connection |
|
SSQL_SYB_BLANK |
Specifies whether to return blank instead of null. Parameters: yes|no See SSQL_SYB_BLANK below. |
VTX2 |
Connection |
|
SSQL_TIMEOUT |
Sets resource time-out to n number of seconds. Parameters: n See SSQL_TIMEOUT below. |
All |
Connection |
|
SSQL_TRIMCHAR |
Changes data type (dty) for character string conversions. Parameters: dty See SSQL_TRIMCHAR below. |
VTX0 |
Connection |
|
SSQL_TXN_ISOLEVEL |
Sets the ODBC cursor isolation level. Parameters: See SSQL_TXN_ISOLEVEL below. |
VTX11 |
Connection |
|
SSQL_USEDB |
Specifies a database name for connections strings in subsequent %SSC_OPEN calls. Parameters: dbname See SSQL_USEDB below. |
VTX2 |
Connection |
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.
When SSQL_CACHE_CHAIN is identical to SSQL_CACHE_CONNECTION, except that with SSQL_CACHE_CONNECTION, cached connections are closed when a program chains. (See below.)
On OpenVMS, this option causes errors.
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.
Determines which row will be retrieved in the next fetch for a scrolling cursor (a cursor opened with one of the %SSC_OPEN options that begin with SSQL_SCROLL):
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 scrolling cursors, see Cursor types and Specifying a cursor type.
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 SSL_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 this 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 which TLS protocol level(s) the client will accept by default for SSL encryption. Levels 1.0, 1.1, and 1.2 are supported, but current security best practices require TLS 1.2. To specify more than one level, separate them with a comma. (Order is not important.)
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 levels 1.1 and 1.2:
3;SSL=yes;SSL_CERTIFICATE=/etc/certs/crt.pem;SSL_PROTOCOL=1.1,1.2
This option sets the database cursor type for subsequent %SSC_OPEN calls:
SSQL_CURSOR_DEFAULT
Default cursor (same as SSQL_CURSOR_FORWARD_ONLY)
SSQL_CURSOR_DYNAMIC
Dynamic cursor
SSQL_CURSOR_FORWARD_ONLY
Forward-only cursor
SSQL_CURSOR_KEYSET_DRIVEN
Keyset-driven cursor
SSQL_CURSOR_STATIC
Static cursor
For information on cursors, including cursor types and how to set them, see Using cursors with SQL Connection and your database documentation. Additionally, note the following:
- None of these options creates a scrolling cursor unless you also use one of the scrolling options for %SSC_OPEN (options that begin with SSQL_SCROLL). For example, if you set SSQL_CURSOR_DYNAMIC in an %SSC_CMD call and don’t use any of the scrolling options in the %SSC_OPEN call, the cursor will be dynamic (if supported by the database) and forward-only.
- The %SSC_OPEN scrolling options (except SSQL_SCROLL) override the SSQL_CURSOR_TYPE options. So 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.)
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.
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.
Instructs Oracle to use BLOB or CLOB data rather than LONG RAW or LONG data.
Enables you to turn autocommit mode on or off.
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.
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 it 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 it is set.
Returns date/time untouched (does not convert date/time to the data type of the defined variable).
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.
Instructs SQL Connection to use fast-forward cursors (SQL_CO_FFO) if possible, so we recommend using this option. However, this option applies only if your application uses VTX12_SQLNATIVE, uses static or dynamic cursors, and does not update the database.
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.
Specifies that a single blank VARCHAR data field should return a space instead of null on Sybase.
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.
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.
Specifies the ODBC cursor isolation level:
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 set SSQL_TXN_READ_UNCOMMITED.
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
