Data Source Information (Magic xpa 2.x)

The utility uses the existing connection to the data source selected and sends the function SQLGetInfo, which returns general information about the data source associated with the connection’s allocated handle.

To retrieve the same information, simply connect to the data source using Connect\Full Connect, and select Connect\SQLGetInfo with the specific fInfoType you want. The result is in the rgbInfoValue field in the result window.

SQL_DATA_SOURCE_NAME = try-access

A character string with the data source name used during connection. If the application called SQLConnect, this is the value of the szDSN argument. If the application called SQLDriverConnect or SQLBrowseConnect, this is the value of the DSN keyword in the connection string passed to the driver. If the connection string did not contain the DSN keyword (such as when it contains the DRIVER keyword), this is an empty string.

SQL_ACCESSIBLE_TABLES = Y

A character string:

"Y" if the user is guaranteed SELECT privileges to all tables returned by SQLTables,

"N" if there may be tables returned that the user could not access.

SQL_CONCAT_NULL_BEHAVIOR =

Concatenation of a NULL value with no NULL value result is concatenation of non NULL valued

A 16-bit integer value indicating how the data source handles the concatenation of NULL valued character data type columns with non-NULL valued character data type columns:

SQL_CB_NULL = Result is NULL valued.

SQL_CB_NON_NULL = Result is concatenation of non-NULL valued column or columns.

SQL_DATA_SOURCE_READ_ONLY = N

A character string:

"Y" if the data source is set to READ ONLY mode.

"N" if the data source is not set to READ ONLY mode.

This characteristic pertains only to the data source itself; it is not a characteristic of the driver that enables access to the data source.

SQL_CURSOR_COMMIT_BEHAVIOR = Close cursors

A 16-bit integer value indicating how a COMMIT operation affects cursors and prepared statements in the data source:

SQL_CB_DELETE = Close cursors and delete prepared statements. To use the cursor again, the application must re-prepare and re-execute the statement handle.

SQL_CB_CLOSE = Close cursors. For prepared statements, the application can call SQLExecute on the statement handle without calling SQLPrepare again.

SQL_CB_PRESERVE = Preserve cursors in the same position as before the COMMIT operation. The application can continue to fetch data or it can close the cursor and re-execute the statement handle without re-preparing it. In Online programs, the cursors will not be closed even if there is a range after fetching for a screen. This rule applies only to drivers where SQL_CURSOR_COMMIT_BEHAVIOR = SQL_CB_PRESERVE.

SQL_CURSOR_ROLLBACK_BEHAVIOR = Close cursors

A 16-bit integer value indicating how a ROLLBACK operation affects cursors and prepared statements in the data source:

SQL_CB_DELETE = Close cursors and delete prepared statements. To use the cursor again, the application must again prepare and reexecute the statement handle.

SQL_CB_CLOSE = Close cursors. For prepared statements, the application can call SQLExecute on the statement handle without calling SQLPrepare again.

SQL_CB_PRESERVE = Preserve cursors in the same position as before the ROLLBACK operation. The application can continue to fetch data or it can close the cursor and re-execute the statement handle without re-preparing it.

SQL_DEFAULT_TXN_ISOLATION = SQL_TXN_READ_COMMITTED

A 32-bit integer that indicates the default transaction isolation level supported by the driver or data source, or zero if the data source does not support transactions. The following terms are used to define transaction isolation levels:

Dirty Read – Transaction 1 changes a row. Transaction 2 reads the changed row before transaction 1 commits the change. If transaction 1 rolls back the change, transaction 2 will have read a row that is considered to have never existed.

Non-repeatable Read – Transaction 1 reads a row. Transaction 2 updates or deletes that row and commits this change. If transaction 1 attempts to reread the row, it will receive different row values or discover that the row has been deleted.

Phantom – Transaction 1 reads a set of rows that satisfy some search criteria. Transaction 2 inserts a row that matches the search criteria. If transaction 1 re-executes the statement that read the rows, it receives a different set of rows.

If the data source supports transactions, the driver returns one of the following bit-masks:

SQL_TXN_READ_UNCOMMITTED = Dirty reads, non-repeatable reads, and phantoms are possible.

SQL_TXN_READ_COMMITTED = Dirty reads are not possible. Non-repeatable reads and phantoms are possible.

SQL_TXN_REPEATABLE_READ = Dirty reads and non-repeatable reads are not possible. Phantoms are possible.

SQL_TXN_SERIALIZABLE = Transactions are serializable. Dirty reads, non-repeatable reads, and phantoms are not possible.

SQL_TXN_VERSIONING = Transactions are serializable, but higher concurrency is possible than with SQL_TXN_SERIALIZABLE. Dirty reads are not possible. Typically, SQL_TXN_SERIALIZABLE is implemented by using locking protocols that reduce concurrency and SQL_TXN_VERSIONING is implemented by using a non-locking protocol such as record versioning. Oracle’s Read Consistency isolation level is an example of SQL_TXN_VERSIONING.

SQL_MULT_RESULT_SETS = N

A character string:

"Y" if the data source supports multiple result sets.

"N" if it does not.

SQL_MULTIPLE_ACTIVE_TXN = Y

A character string:

"Y" if active transactions on multiple connections are allowed

"N" if only one connection at a time can have an active transaction.

SQL_NEED_LONG_DATA_LEN = N

A character string:

"Y" if the data source needs the length of a long data value (the data type is SQL_LONGVARCHAR, SQL_LONGVARBINARY, or a long, data source-specific data type) before that value is sent to the data source.

"N" if it does not.

For more information, see the SQLBindParameter and SQLSetpos functions.

SQL_OWNER_TERM = NULL STRING

A character string with the data source vendor’s name for an owner. For example, owner, Authorization ID, or Scheme.

SQL_NULL_COLLATION = NULLs are sorted at the low end of the list

A 16-bit integer value specifying where NULLs are sorted in a list:

SQL_NC_END = NULLs are sorted at the end of the list, regardless of the sort order.

SQL_NC_HIGH = NULLs are sorted at the high end of the list.

SQL_NC_LOW = NULLs are sorted at the low end of the list.

SQL_NC_START = NULLs are sorted at the start of the list, regardless of the sort order.

For retrieving the value of SQL_AUTOCOMMIT, described immediately below, the utility uses the existing connection to the data source selected and sends the function SQLGetConnectOption, which returns the current settings of a connection option.

In order to retrieve the same information, simply use Connect\Full Connect to connect to the data source, and select Connect\SQLGetConnectOption with the specific fOption you want (SQL_AUTOCOMMIT in this case). The result is in the pvParam field in the result window.

SQL_AUTOCOMMIT on connection = SQL_AUTOCOMMIT_ON

A 32-bit integer value that specifies whether to use auto-commit or manual-commit mode:

SQL_AUTOCOMMIT_OFF = The driver uses manual-commit mode, and the application must explicitly commit or roll back transactions with SQLTransact.

SQL_AUTOCOMMIT_ON = The driver uses auto-commit mode. Each statement is committed immediately after it is executed. This is the default. Changing from manual-commit mode to auto-commit mode commits any open transactions on the connection.

Important: Some data sources delete the access plans and close the cursors for all statement handles on a connection handle each time a statement is committed. Autocommit mode can cause this to happen after each statement is executed.

SQL_USER_NAME = admin

A character string with the name used in a particular database, which can be different from the login name.