# 7. Data Access Interfaces

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
Data Access Interfaces
Virtuoso .NET Data Provider
Interactive SQL Utility
Virtuoso Driver for ODBC
Windows ODBC Driver Configuration Using X509 Certificates With ODBC Connection ODBC Compliance Virtuoso Scrollable Cursor Engine Effect of Connection & Statement Options Efficient Use of API
Virtuoso Driver for JDBC
OLE DB Provider for Virtuoso
Virtuoso In-Process Client
Unix Domain Socket Connections

## 7.3. Virtuoso Driver for ODBC

### 7.3.1. Windows ODBC Driver Configuration

At installation time two ODBC data source names (DSN's) are created with default values by the Virtuoso installer, the first DSN named "Local Virtuoso" provides a link to a local default Virtuoso database server instance, while the other named "Local Virtuoso Demo" provides a link to a local Virtuoso server for the Virtuoso demonstration database.

The process of creating additional ODBC DSN's for you Virtuoso drivers for ODBC is explained in the steps that follow:

1. Go to the Windows Control panel.

2. Double click on the ODBC Administrator applet. On Windows 2000 the ODBC Administrator applet may be called Data Source (ODBC) and may be found under Administrative tools icon of the Control Panel.

3. Once the ODBC Administrator has been loaded choose by selecting the appropriate panel whether you want a new User or System Data Source. User Data Sources will only be available to the user that created them. System Data Sources will be available to all users and applications on the system.

4. Click on the Add Data Source Name button

5. Select the Driver named OpenLink Virtuoso Driver

6. Enter values into the fields presented by the Virtuoso Driver's DSN configuration dialog:

   Figure: 7.3.1.1. Virtuoso ODBC Driver Setup Dialogue for Windows

   DSN: provide a name that will act as a logical reference to the Virtuoso database server that you will be connecting to. Subsequent references to this database will be made to this value when ODBC compliant applications interact with your Virtuoso driver.

   Description: allows you to provide a short description about the nature of the connection. This is optional.

   Server: enter the hostname or IP address of the machine hosting your Virtuoso server.

   Port: enter the port number that Virtuoso is listening at. This is configured in the Virtuoso ini file on the server.

7. Press the Next button to configure more details about the connection

   Figure: 7.3.1.1. Virtuoso ODBC Driver Setup Dialogue for Windows

   User name: allows you to specify the default username for the connection.

   Automatically adjust clock for daylight saving changes lets you choose whether time data values are adjusted to counter the effects of daylight saving.

   Connect to the Virtuoso Server to obtain databases list when checked will allow you to provide a Password which will be used with the default user name setting to automatically interrogate the database for a list of database names.

   Password: where you specify the password required for the automatic connection to the database for retrieval of database names

   Database: allows you to select the default database for the connection. You will need to check the box above and supply a password to refresh this list. Objects created or selected without an explicit catalogue/database qualifier will automatically be sought from this select database.

   Charset: lets you choose the default character set for the connection.

   Test database connection can be used to test your configuration

   Figure: 7.3.1.2. Virtuoso ODBC Driver Setup Dialogue for Windows

   supply the remaining details (usually only password) and press ok to test. If all is well then you will have returned the confirmation box

   Figure: 7.3.1.3. Virtuoso ODBC Driver Setup Dialogue for Windows

8. Press Next to configure encryption

   Figure: 7.3.1.1. Virtuoso ODBC Driver Setup Dialogue for Windows

   The Encrypted checkbox specifies an encrypted connection when checked.

   A PKCS#12 file certificate must be supplied for the server to authorize the connection. See Using SSL For Secure ODBC connections for more information regarding this.

9. When the configuration is complete, and indeed at any time you are satisfied with the configuration press the Finish button to save the DSN.

### 7.3.2. Using X509 Certificates With ODBC Connection

Virtuoso can be configured to authenticate ODBC logins with a single X.509 certificate. The certificate must be registered server side for this purpose and may contain an additional custom attribute for the users SQL account name. In this way all login information is held in the possibly password protected certificate. The user specifies the certificate path in the place of the user name and the certificate encryption password as the password. This works with the login dialog of the ODBC driver or within a SQLDriverConnect login string.

It is also possible to set up the ODBC client to check for the server's X.509 certificate by specifying a client side CA (Certificate Authority) list.

The X509 certificate can be used for authentication with the Virtuoso server via SSL. In this case the ODBC client will use an X.509 certificate which contains a SQL username as an extension. This extension is added when the certificate is issued. Since an object ID (OID) of the username attribute can be used any valid object identifier that does not conflict with existing OIDs (for example 2.16.840.1.NNNNNN). It will be best to have one's own registered OID for that purpose.

#### 7.3.2.1. ODBC Client Certificate Generation Using "openssl" Tool

To generate X509 certificates one can use the "openssl" tool obtained freely from www.openssl.org. The openssl.cnf configuration file must be edited to include the new extension for sqlUserName, but first we have to find the hexedecimal representation of the SQL Username. Then you can proceed the use the openssl to create and confirm the certificate. Follow the steps below:

1. Find the HEX representation of the SQL Username

   You can either work this out from ASCII codes or use a hexdump utility found on most Unix platforms. The following command would be sufficient, replacing <user_name> with the actual username:

       $ echo -n "<user_name>" | hexdump -e '":" 1/1 "%02X"'

   For example, the username 'dba' would be :64:62:61.

2. Edit the OpenSSL config file: openssl.cnf

   Open the file in a text editor of your choosing and add the following:

       [ new_oids ]
       sqlUserName=2.16.840.1.NNNNNN.1

       ...
       [ usr_cert ]
       ...
       sqlUserName = DER:NN:NN:NN:NN:NN.....

   replacing :NN:NN..... with the hexadecimal represantation of the username we discovered in the previous step.

3. Make New Certificate Request

   Make new certificate request using the command:

       $ openssl req -new

   The tool will ask for certain details. Once completed it will generate a private key also.

4. Generate Certificate

   Assuming the role of CA, generate a certificate using the request from the previous step, using the following command:

       $ openssl ca -in <req_file>

   The tool will print the details of request and will ask you to sign and commit the certificate into the CA repository.

5. Verify New Certificate

   Check that the certificate contains the right SQL account name; use the following command to obtain a text dump of the certifcate:

       openssl x509 -in <certfile> -text -noout

   Now we can scan the contents of the output for the extension entries we added earlier:

       >>>>>
       X509v3 extensions:
       ....
       2.16.840.1.NNNNNNN.1:
       <SqlAccountName>
       ^^^^^^ this should match the new OID ^^^^^
       >>>>>

#### 7.3.2.2. Virtuoso Server Configuration

The Virtuoso server can work in two modes based on an SSL connection:

- basic - this is when only connection is secured, no certificate verification. The client is not required to have its own trusted certificate.
- trusted - additional mechanisms are enabled to check client's certificate. in this case the client is required to have a trusted certificate.

To allow Virtuoso to check the client's certificates, the virtuoso.ini file must contain the following entries in [Parameters] section:

    ; Basic session encryption only parameters
    SSLServerPort           = 1113
    SSLCertificate          = ./srv.cert.pem ; server's certificate
    SSLPrivateKey           = ./srv.key.pem  ; server's private key, must match the certificate
    ; Trusted operation parameters
    X509ClientVerify        = 1
    X509ClientVerifyCAFile  = ./ca.pem	   ; CA list; file containing certificates of acceptable CA
    X509ClientVerifyDepth   = 1 		   ; dependent of type of certificate checking can be &gt;1
    X509ExtensionOID	  = 2.16.840.1.NNNNNNN.1 ; the OID value, same as that used to make client's certificates

All certificate/key files need to be in PEM format.

The server needs an "SSLCertificate" and "SSLPrivateKey" to begin listening on the "SSLServerPort". These are essential for the secure operations. Furthermore the certificate must match the private key; non-matching certificate and private keys will prevent server startup and an error will be logged. The private key is required to guarantee that the certificate's claim is true. The server certificate is used by the client to identify the server. The client can retrieve and verify this key and choose whether the server an be trusted depending on circumstances.

Note:

basic operation (SSL/TLS) encryption only cannot be used to identify a client with certificate.

The "X509ClientVerifyCAFile" is a file containing a CA's (Certificate Authority) certificates that the server can use to verify client certificates. The client certificate verification in general depends on the "X509ClientVerify" flag, which enables or disable this feature. The 'X509ClientVerifyDepth" parameter is used to verify to what degree server will trust the client's certificate. The lower the value, the higher the restriction levels, with a minimum value of 1. This means that the server will look in CA's certificates to find who has issued the client certificate. If there is no matching CA entry the connection will be rejected; If there is a matching entry then verify the issuer chain; If issuer chain is greater than "X509ClientVerifyDepth", the connection will be rejected.

All file paths above must be either absolute or relative to the server working directory.

Note:

If primary key file is encrypted then the server must be started in foreground mode so that a password can be supplied in order to open the file.

#### 7.3.2.3. Virtuoso Client Configuration

The following connection options control the client's behavior regarding SSL:

- Encrypt - specifies type of secure connection to be used.
- ServerCert - (optional) to specify which certificate(s) are to be used to verify server certificates.

The Encrypt option can be set to '1' to specify a basic secure connection; no server identity verification will be performed. Note that this is only possible when the server is also set to make basic SSL connections.

To ensure server's identity the Encrypt option must be set to full or relative (to application working directory) path to the file containing client's certificate and private key. This file can be encoded in PKCS#12 or PEM format. The certificate and private key contained may be generated using the steps outlined above.

When the Encrypt option is set to point to a cetificate file, the ServerCert option must be set to the full or relative path to file containing list of CA certificates in PEM format. The content of this file will be used to verify server's certificate.

When using these options the UID connection-option must be set to an empty string to enable certificate authorization. The PWD option will be used to open the private key.

Here is an example of an ODBC connect-string:

    ...
    connectString =
    "HOST=localhost:1113;UID=;PWD=keysecret;ENCRYPT=c:\certs\mycertwithkey.p12;SERVERCERT=c:\certs\ca.pem"
    ...

If client's certificate does not contains user name for SQL login then the server will try matching the certificate fingerprint against registered certificates. If any SQL account has such a certificate registered it will be used for login. Otherwise login will be rejected.

#### 7.3.2.4. Registering Keys with Virtuoso

To enable a client certificate to be used for authorization, the DBA must register it's MD5 fingerprint (checksum) in the database. Registered certificates can be removed from system. Two functions exist for the purposes: USER_CERT_REGISTER(), and USER_CERT_UNREGISTER(). Both functions rely on the MD5 checksum of the certificates being registered or un-registered, therefor you have the option of supplying these functions with the certificate file or the MD5 checksum directly.

The functions are:

USER_CERT_REGISTER (user_name, certificate, password, type);

USER_CERT_UNREGISTER (user_name, certificate, password, type);

The registered certificate's fingerprints are kept in the DB.DBA.SYS_USERS table as vectors of strings under the 'LOGIN_CERTIFICATES' user option value (U_OPTS column). The data stored there can be retrieved using a third function:

USER_GET_OPTION()

For example, one might invoke:

    USER_CERT_REGISTER ('DBA', 'file:/dba.pem', '', 'PEM');

Note that 'file:' URL is needed to designate certificate is in a file on file system.

The above action can be performed also without certificate supplied if the MD5 fingerprint is known:

    USER_CERT_REGISTER ('DBA', 'D9:6D:47:D7:67:47:D7:3C:2C:E0:89:91:F3:BC:E7:59');

and

    USER_CERT_UNREGISTER ('DBA', 'D9:6D:47:D7:67:47:D7:3C:2C:E0:89:91:F3:BC:E7:59');

### 7.3.3. ODBC Compliance

The Virtuoso Driver for ODBC conforms to both the ODBC 1.x,2.x,and 3.x versions of the ODBC specification, it implements Core, Level 1, Level 2, and Extensions functionality. It also has a native support for the wide versions of the ODBC API (e.g. SQLColumnsW) in Windows. This driver enables you to communicate with local or remote Virtuoso servers across any combination of platforms supported by Virtuoso.

#### 7.3.3.1. ODBC API implementation details

##### 7.3.3.1.1. SQLAllocHandle

Virtuoso ODBC driver does not allow allocation and usage of explicitly allocated descriptor handles. That is why the SQLAllocHandle (SQL_HANDLE_DESC) will return an error.

##### 7.3.3.1.2. SQLBulkOperations

Only the SQL_ADD operation is supported.

##### 7.3.3.1.3. SQLColAttributes

The virtuoso ODBC driver does not return information for the following attributes:

- SQL_COLUMN_TABLE_NAME

  Returns an empty string instead

- SQL_COLUMN_OWNER_NAME

  Returns an empty string instead

- SQL_COLUMN_QUALIFIER_NAME

  Returns an empty string instead

- SQL_COLUMN_CASE_SENSITIVE

  Returns 1 instead

- SQL_COLUMN_AUTO_INCREMENT

  Returns 0 instead

- SQL_COLUMN_MONEY

  Returns 0 instead

- SQL_COLUMN_UNSIGNED

  Returns 0 instead

##### 7.3.3.1.4. SQLDriverConnect

The Virtuoso ODBC driver recognizes the following SQLDriverConnect connection string keywords:

- DSN

  The data source name.

- HOST

  The virtuoso server host specification (in the form : [<hostname>[:]][<portnumber>]

- UID

  The virtuoso user ID used to connect

- PWD

  The login password used to connect

- DATABASE

  The qualifier to use when connected (overrides the user's default qualifier)

- CHARSET

  The name of the character set to use for wide/narrow conversions

- DAYLIGHT

  Boolean parameter (1/0). When ON (1) it takes into account the client OS daylight savings settings.

- ENCRYPT

  String parameter (file name or "1"). Specifies how the ODBC connection will be encrypted. see the Using SSL For Secure ODBC connections for details.

- PWDCLEAR

  Integer parameter (default to 0). Specifies how the password will be sent over the wire. 0 - send digest of the password, 1 - send password in cleartext, 2 - send password garbaled.

- SERVERCERT

  String parameter (file name). Specifies the path for the CA list used to verify the server's certificate (in PEM format). see the Using SSL For Secure ODBC connections for details.

- FORCE_DBMS_NAME

  String parameter (default "OpenLink Virtuoso"). When set it alters the result of SQLGetInfo (SQL_DBMS_NAME).

- NoSystemTables

  Boolean parameter (1/0) (default 0). When set it alters the result of SQLTables () so that it won't find or return tables of type SYSTEM TABLE.

- IsolationLevel

  String parameter ("Read Uncommitted"/"Read Committed"/"Repeatable Read"/"Serializable"). When set it specifies the initial transaction isolation mode for that connection.

##### 7.3.3.1.5. SQLGetEnvAttr

The SQL_ATTR_OUTPUT_NTS does not have any effect on the Virtuoso driver. It allows the value to be set and retrieved but with no further effect.

##### 7.3.3.1.6. SQLNativeSql

All ODBC syntax is parsed server side. The native SQL syntax for Virtuoso is the ODBC syntax.

##### 7.3.3.1.7. Not Supported ODBC API functions

Virtuoso ODBC driver does not support the following ODBC API functions:

- SQLCopyDesc

### 7.3.4. Virtuoso Scrollable Cursor Engine

Virtuoso implements server side scrollable cursors. ODBC 2.0, ODBC 3.5 and JDBC 2.0 API's are supported.

Cursor types include:

- Forward only

  This is the default, non-scrollable cursor.

- Static

  The cursor's evaluation is computed when the cursor statement is first executed. Positioned operations are possible but their effect will not show nor will changes be detected.

- Keyset

  When the cursor is opened a keyset is built. Rows within the keyset can be fetched and modified by positioned operations. Changes will show when refreshing data and changes by other transactions will be detected for update and delete. Inserts by the same or different transaction will not appear. A keyset cursor may have a finite keyset size. When scrolling outside of the given keyset the keyset will shift to cover the new rows.

- Dynamic

  A dynamic cursor will reflect all changes by the same and different transactions. The dynamic cursor's evaluation is constructed as needed, hence it generally has less overhead than other types of cursors.

- Mixed

  A mixed cursor is a combination of a keyset-driven cursor and a dynamic cursor. It is used when the result set is too large to reasonably generate a keyset for the entire result set. Mixed cursors use a keyset smaller than the entire result set but larger than the rowset.

  While the application is scrolling within the keyset, the behavior is keyset-driven. When the application scrolls beyond the keyset, the behavior becomes dynamic to fetch the requested rows and generate a new keyset. The behavior then reverts back to keyset-driven within that keyset, as before.

#### 7.3.4.1. Forward Only Cursors

A forward only cursor is substantially more efficient than a scrollable cursor. It however does not allow positioned operations (the WHERE CURRENT OF SQL phrase), or SQLSetPos. The SQLExtendedFetch function is supported but only the SQL_FETCH_NEXT fetch type is then allowed.

#### 7.3.4.2. Cursor Operations

Virtuoso supports all ODBC scrollable cursor operations. These include

SQLExtendedFetch / SQLScrollFetch fetch type

- SQL_FETCH_FIRST

- SQL_FETCH_LAST

- SQL_FETCH_NEXT

- SQL_FETCH_PRIOR

- SQL_FETCH_RELATIVE

- SQL_FETCH_ABSOLUTE

- SQL_FETCH_BOOKMARK

SQLSetPos operations

- SQL_POSITION

- SQL_REFRESH

- SQL_ADD

- SQL_UPDATE

- SQL_DELETE

Positioned SQL statements, i.e. the WHERE CURRENT OF clause, is supported for scrollable cursors.

#### 7.3.4.3. Cursor Options

The cursor options

- SQL_CURSOR_TYPE

- SQL_CONCURRENCY

- SQL_KEYSET_SIZE

- Cursor name (SQLSetCursorName)

have to be set before a SQLPrepare or SQLExecDirect.

The SQL_ROWSET_SIZE can be varied while a cursor is open.

#### 7.3.4.4. Cursors and Transactions

All forward only or scrollable cursors survive committing or rolling back transactions. A cursor maintains its position over a transaction's end. Hence the next fetch operation will resume from the correct place. If a dynamic cursor's current row / rowset is deleted, the cursor will continue from the next greater / lesser row in the order of the cursor's ordering columns. This also applies to mixed mode (keyset with finite keyset size) cursors scrolling outside of the keyset bounds. A forward only cursor will retain its logical position across commit/rollback.

The SQL_CONCURRENCY values of SQL_CONCUR_READ_ONLY and SQL_CONCUR_LOCK cause Virtuoso to lock the rows in the keyset / rowset in shared or exclusive mode, respectively.

#### 7.3.4.5. Optimistic Concurrency Control

A scrollable cursor may have a SQL_CONCURRENCY setting of SQL_CONCUR_VALUES. This enables optimistic concurrency control. This is a mechanism which will reflect an update or delete of a row if the row has been modified by a third party after the time the application last read the row.

A 'updated meanwhile' condition detected in this manner will prevent the operation and return a SQL state of 01001 with SQL_SUCCESS_WITH_INFO from SQLSetPos.

The updates and deletes made through SQLSetPos are still subject to being committed or rolled back by normal transaction control. The 01001 state does not prevent the current transaction from committing.

The 01001 state is only detected if the update or delete is made by SQLSetPos and the row at hand has been changed by any statement of any transaction. If the update of updated operation is carried out by any other operation than SQLSetPos of the statement that last read the value the condition cannot be detected.

Note that the time between the last read and the SQLSetPos update can be long and can span multiple transactions.

#### 7.3.4.6. Cursor Information

Table: 7.3.4.6.1. Cursor Support
| Static | Keyset | Dynamic
--- | --- | --- | ---
SQLRowCount | x | x |
SQL_BOOKMARK | x | x | x
SQL_ROW_NUMBER | x | x |
reflect update |  | x | x
reflect delete |  |  | x
reflect inx |  |  | x
Notice update | x | x | x
Notice delete | x | x | x

SQLRowCount is set after the initial SQLExecute. This is the actual row count or the keyset size for a mixed mode cursor. A dynamic cursor does not know this since it builds the evaluation as needed.

All cursors support bookmarks.

The SQL_ROW_NUMBER is the ordinal number of the current row in the cursor's evaluation. A dynamic cursor cannot know this, as the scrolling may start from end and rows may appear on either side of the current row while the cursor is open.

'reflect' means that the new values or added / deleted rows appear when re-scrolling over the rows. A deletion is reflected by omitting the row.

'notice' means that the row is flagged by SQL_UPDATE, SQL_DELETED or SQL_ADDED in the SQLExtendedFetch status array when the cursor re-scrolls over the rows in question.

#### 7.3.4.7. Cursors and Virtual Database

The cursor subsystem modifies the cursor's defining select statement to make various backward and forward read statements, update and delete statements etc. These transformations can be seen through the explain function.

Since cursors are implemented by generating SQL statements these work transparently against remote database, independently of their possible native cursor support.

#### 7.3.4.8. Cursor Special Cases

SQL SELECT statements fall in two categories: Simple, which consist of one or more tables, an arbitrary WHERE clause and an optional ORDER BY, and Complex, which includes all other SELECT's, e.g. ones with GROUP BY, derived tables, full outer joins, UNION's etc.

A simple statement can easily be modified to read backwards or forwards from a given point. Also, each result row of a simple statement has a physical counterpart, or many physical counterparts for a join. Having a physical counterpart makes it possible to define the meaning of a positioned update or delete. A GROUP BY is a counter-example.

All complex SQL statements occurring as cursors are processed as static cursors regardless of the declared cursor type.

#### 7.3.4.9. Cursors and Performance

If a cursor's ordering corresponds to an ordering in an index, dynamic cursors will generally perform best. This is understandable since the engine can quickly locate the current row based on an index and then just read backward or forward on that index. On the other hand, if the result set is very sparsely spread over the table or if there is no ordering index dynamic cursors will have a substantial penalty.

If used as a forward only cursor in a single transaction, a dynamic cursor is only some 30% slower than a forward only cursor.

A static or keyset cursor has a relatively long execute time since the initial execute will make the entire keyset. The initial execute of a dynamic cursor is instantaneous since the fetch operations will do the work as needed.

With most off the shelf applications, e.g. Microsoft ADO, static and keyset cursors are preferable because the applications may rely on row counts and row numbers which are not known for dynamic cursors.

Positioned operations are not affected by cursor type.

### 7.3.5. Effect of Connection & Statement Options

#### 7.3.5.1. Connection Options

##### 7.3.5.1.1. SQL_AUTOCOMMIT

The autocommit option is set at the connection level and affects every statement executed after it being set. Setting the option does not communicate itself with the server and is therefore fast.

Autocommit is on by default. Autocommitting SELECT statements are executed with read committed isolation. This is appropriate since any update based on the autocommitting read would be in a different transaction and hence would block to wait for access to the selected row. Also re-evaluating a select in autocommit mode would read the data in a different transaction. Hence there is no point in repeatable read isolation for autocommitting cursors. Cursors inside procedures have the normal repeatable read isolation regardless of whether the procedure was called in autocommit mode.

When an autocommitting statement starts it is executed in the transaction That is the connection's current transaction when it is received. Before starting the autocommitting statement sets the connection's current transaction to a new one. In this manner a client can issue multiple asynchronous autocommitting statements at the same time and the statements will execute concurrently, each in its own transaction.

If array parameters are used in a statement on an autocommitting connection each parameter row will be processed in its own transaction in sequential order. Multiple Asynchronous statements must be used to execute one client's statements in parallel.

To commit or roll back a transaction in manual-commit mode, an application should call SQLEndTran. Applications should not attempt to commit or roll back transactions by executing COMMIT or ROLLBACK statements with SQLExecute or SQLExecDirect. The effects of doing this are undefined.

##### 7.3.5.1.2. SQL_TXN_ISOLATION

This option allows all the values defined in ODBC,

The isolation of an operation is the property of the operation rather than of the transaction within which it takes place. Once an operation has started, e.g. a cursor has been opened, its isolation cannot be changed.

The value of this option will affect any subsequently executed statement. Note that setting this option to different values during a transaction will work, thus a transaction can have cursors with different isolations although that is presumably not the intention of the ODBC specification.

See the transaction model for a definition of the different isolation levels.

A statement in autocommit mode executes in the same transaction as the previous statement. The transaction is committed when the statement successfully completes. The next statement in the connection will execute in the fresh transaction that was associated to the connection at the start of the previous autocommitting statement.

As a consequence of this multiple concurrent autocommitting transactions may execute on the same connection at the same time.

Statements executed with array parameters execute each set of parameters as a separate transaction if the connection is in autocommit mode.

##### 7.3.5.1.3. SQL_ACCESS_MODE

This has the effect of reversing any SQL_CONCUR_LOCK concurrency to SQL_CONCUR_READ_ONLY. The statement option's value is not changed though.

##### 7.3.5.1.4. SQL_CURRENT_QUALIFIER

This sets or gets the current qualifier. The initial value is obtained from the server at connect time. The values reflect the effects of any USE statements.

##### 7.3.5.1.5. SQL_NO_CHAR_C_ESCAPE (=5002)

This has the same effect as the NO_CHAR_C_ESCAPE option in the SET statement. It takes boolean int values (0/non-0)

##### 7.3.5.1.6. SQL_CHARSET (=5003)

This has the same effect as the CHARSET option in SET statement. It takes string values (the name of the character set to use).

##### 7.3.5.1.7. SQL_ENCRYPT_CONNECTION (=5004)

Usable only with the Virtuoso CLI (because the ODBC/iODBC driver manager does not pass-through the custom options to the driver on SQLConnect/SQLDriverConnect). When set to the string "1" means use SSL but no X509 certificates. Setting it to a NULL (default) means no encryption of the ODBC connection. Any other string is treated as a file name of one PKCS12 package to get the data from for establishing an encrypted SSL connection using X509 certificates (see the -E/-X ISQL options).

##### 7.3.5.1.8. SQL_SHUTDOWN_ON_CONNECT (=5005)

Usable only with the Virtuoso CLI (because the ODBC/iODBC driver manager does not pass through the custom options to the driver on SQLConnect/SQLDriverConnect). Shuts down the server on connection after authenticating the DBA user (see the -K ISQL option).

#### 7.3.5.2. SQLSetStmtOption Statement Options

Virtuoso supports all ODBC 2.x and ODBC 3.x statement options. The following options are mentioned below due to implementation specific details.

##### 7.3.5.2.1. SQL_CONCURRENCY

The supported values are SQL_CONCUR_READ_ONLY, SQL_CONCUR_LOCK and SQL_CONCUR_VALUES, the last option is only available for scrollable cursors. A select statement with SQL_CONCUR_READ_ONLY will make shared locks when locking for repeatable read or serializable transactions. The SQL_CONCUR_LOCK for a select statement will cause it to make exclusive locks, as if it had the FOR UPDATE clause specified.

See the section on scrollable cursors for the effect of SQL_CONCUR_VALUES. For all statements except scrollable cursors this value reverts to SQL_CONCUR_READ_ONLY.

Any searched update or delete statements will make exclusive locks in all cases.

##### 7.3.5.2.2. SQL_MAX_ROWS

This option only affects static cursors.

##### 7.3.5.2.3. SQL_TXN_TIMEOUT

This is an extension that allows setting a time limit on the current transaction. The time limit starts at the execution of the statement specifying this option. The transaction will terminate the indicated number of seconds after the execute whether the statement has completed or not.

##### 7.3.5.2.4. SQL_PREFETCH_SIZE

This is an extension option that controls how many rows of a forward only cursor are prefetched by the execute and fetch calls. A high value is advantageous for long consecutive reads since it cuts down on the number of client server messages exchanged. On the other hand a large value can result in unnecessary data transfer and locking if only the first few rows of a cursor are fetched. A value of -1 will cause the entire rowset to be fetched at the execute, so that no matter the select size, only one message is exchanged. The default value is 20. This can also be set in the virtuoso.ini file.

A select with array parameters will always work as with a SQL_PREFETCH_SIZE of -1, meaning that all the result sets are computed and sent to the client by the SQLExecute call that opens the cursor.

##### 7.3.5.2.5. SQL_CURSOR_TYPE

##### 7.3.5.2.6. SQL_KEYSET_SIZE

##### 7.3.5.2.7. SQL_CONCURRENCY

The cursor type options should be set before preparing a statement. Other options may be set at any time. The rowset and prefetch opions options should not be modified after executing a SELECT.

##### 7.3.5.2.8. SQL_GETLASTSERIAL

This is an extension that returns the last assigned identity column value. The return buffer pvParam is of type SQLINTEGER. For this to be meaningful, the statement in question must be an INSERT into a table that has an identity column. Note that if there are more than one identity columns or if triggers make inserts with identity columns the value will be undefined.

### 7.3.6. Efficient Use of API

DO NOT USE SQLExecDirect. If a statement is executed more than once it is much faster to prepare the statement with SQLPrepare and then use SQLExecute repeatedly. The system only compiles the statement once, therefore there is no parsing overhead for repeatedly compiling the same text.

Array parameters for insert, update or single row selects are about twice as fast as the same operations on a single parameter set.

The OR connective in SQL can result in slow queries with extensive locking.

Autocommit should be used when possible, i.e. make the last statement of a transaction autocommitting to avoid having to commit the transaction as a separate operation.

Previous Interactive SQL Utility | Chapter Contents | Next Virtuoso Driver for JDBC