Catalog Tables

The Salesforce driver provides a standard set of catalog tables that maintain the information returned by various ODBC catalog functions such as SQLTables, SQLColumns, SQLDescribeParam and SQLDescribeCol. If possible use the ODBC catalog functions to obtain this information instead of querying the catalog tables directly.

The driver also provides additional catalog tables that maintain metadata specific to the Salesforce driver. This section defines the catalog tables that provide Salesforce driver-specific information. The catalog tables are defined in the INFORMATION_SCHEMA schema.

The SYSTEM_CACHES catalog table stores the definitions of the caches created on remote tables. The data in the SYSTEM_CACHES table provides the name, type (single table or relational), status, and other information for each defined cache. The table name returned for a remote relational cache is the name of the primary table of the relational cache; however, its type is REMOTE RELATIONAL. You can query SYSTEM_CACHES to determine the caches currently defined by the driver. The values in the SYSTEM_CACHES table are read-only. The referenced tables of a relational cache can be determined by querying the SYSTEM_CACHE_REFERENCES catalog table (see “SYSTEM_CACHE_REFERENCES Catalog Table”).

Table 21-4 describes the columns of the SYSTEM_CACHES table, which is sorted on the following columns: CACHE_TYPE, TABLE_SCHEMA, and TABLE_NAME.

Table 21-4. SYSTEM_CACHES Catalog Table
Column Name	Data Type	Description
TABLE_CAT	VARCHAR (128), NULLABLE	The catalog that contains the remote table on which the cache is defined. It is NULL for the Salesforce driver.
TABLE_SCHEM	VARCHAR (128), NULLABLE	The schema that contains the remote table on which the cache is defined.
TABLE_NAME	VARCHAR (128), NOT NULL	The name of the remote table on which the cache is defined.
CACHE_TYPE	VARCHAR (20), NOT NULL	The type cache, which can be either REMOTE TABLE or REMOTE RELATIONAL.
REFRESH_INTERVAL	INTEGER, NOT NULL	The refresh interval (in minutes).
INITIAL_CHECK	VARCHAR(20), NOT NULL	The value that defines when the initial refresh check is performed: ONFIRSTCONNECT or FIRSTUSE.
PERSIST	VARCHAR(20), NOT NULL	The value that defines whether the data in the cache is persisted past the lifetime of the connection: TEMPORARY, MEMORY, or DISK.
ENABLED	BOOLEAN, NOT NULL	The value that defines whether the cache is enabled for use with SQL statements: TRUE or FALSE.
CALL_LIMIT	INTEGER, NOT NULL	The maximum number of Web service calls that can be made when refreshing the cache. The value 0 indicates no call limit.
REFRESH_MODE	INTEGER, NOT NULL	For internal use only.
FILTER	VARCHAR(128), NULLABLE	The Where clause used to filter the rows that are cached.
LAST_REFRESH	DATETIME, NULLABLE	The time, in Coordinated Universal Time (UTC), the cache was last refreshed.
STATUS	VARCHAR(30)	The Cache status. Valid values are: New: The cache has been created, but the data has not been populated. Initialized: The cache has been created and the data has been populated. Load aborted: The cache has been created, but the last attempt to populate the data failed. The cache is still valid. The next access attempts to populate the data again. Invalid: The cache is invalid. The second attempt to populate the data failed. Dirty: An insert or update operation has been performed on the cache and the cache has not been refreshed.

Table 21-4. SYSTEM_CACHES Catalog Table

VARCHAR (128),
NULLABLE

The catalog that contains the remote table on which the cache is defined. It is NULL for the Salesforce driver.

TABLE_SCHEM

VARCHAR (128),
NULLABLE

The schema that contains the remote table on which the cache is defined.

TABLE_NAME

VARCHAR (128),
NOT NULL

The name of the remote table on which the cache is defined.

CACHE_TYPE

VARCHAR (20),
NOT NULL

The type cache, which can be either REMOTE TABLE or REMOTE RELATIONAL.

REFRESH_INTERVAL

INTEGER,
NOT NULL

The refresh interval (in minutes).

INITIAL_CHECK

VARCHAR(20),
NOT NULL

The value that defines when the initial refresh check is performed: ONFIRSTCONNECT or FIRSTUSE.

PERSIST

VARCHAR(20),
NOT NULL

The value that defines whether the data in the cache is persisted past the lifetime of the connection: TEMPORARY, MEMORY, or DISK.

ENABLED

BOOLEAN,
NOT NULL

The value that defines whether the cache is enabled for use with SQL statements: TRUE or FALSE.

CALL_LIMIT

INTEGER,
NOT NULL

The maximum number of Web service calls that can be made when refreshing the cache. The value 0 indicates no call limit.

REFRESH_MODE

INTEGER,
NOT NULL

For internal use only.

FILTER

VARCHAR(128),
NULLABLE

The Where clause used to filter the rows that are cached.

LAST_REFRESH

DATETIME,
NULLABLE

The time, in Coordinated Universal Time (UTC), the cache was last refreshed.

STATUS

VARCHAR(30)

The Cache status. Valid values are:

New: The cache has been created, but the data has not been populated.

Initialized: The cache has been created and the data has been populated.

Load aborted: The cache has been created, but the last attempt to populate the data failed. The cache is still valid. The next access attempts to populate the data again.

Invalid: The cache is invalid. The second attempt to populate the data failed.

Dirty: An insert or update operation has been performed on the cache and the cache has not been refreshed.

The referenced tables in a relational cache can be determined by querying the SYSTEM_CACHE_REFERENCES system table. This table contains the names of the referenced tables as well as the name of the primary table with which they are associated.

Table 21-5 defines the columns of the SYSTEM_CACHES table, which is sorted on the following columns: TABLE_SCHEMA, TABLE_NAME, and REF_TABLE_NAME.

Table 21-5. SYSTEM_CACHE_REFERENCES
Column	Data Type	Description
PRIMARY_TABLE_CAT	VARCHAR (128), NULLABLE	The catalog that contains the primary table of the relational cache. It is NULL for the Salesforce driver.
PRIMARY_TABLE_SCHEM	VARCHAR (128), NULLABLE	The schema that contains the primary table of the relational cache.
PRIMARY_TABLE_NAME	VARCHAR (128), NOT NULL	The primary table of the relational cache.
REF_TABLE_NAME	VARCHAR (128), NOT NULL	The name of the referenced table.
RELATIONSHIP_NAME	VARCHAR(128), NOT NULL	The name of the foreign key relationship used to relate this table to the primary table or one of the other tables in the relational cache.

Table 21-5. SYSTEM_CACHE_REFERENCES

VARCHAR (128), NULLABLE

The catalog that contains the primary table of the relational cache. It is NULL for the Salesforce driver.

PRIMARY_TABLE_SCHEM

VARCHAR (128), NULLABLE

The schema that contains the primary table of the relational cache.

PRIMARY_TABLE_NAME

VARCHAR (128), NOT NULL

The primary table of the relational cache.

REF_TABLE_NAME

VARCHAR (128), NOT NULL

The name of the referenced table.

RELATIONSHIP_NAME

VARCHAR(128), NOT NULL

The name of the foreign key relationship used to relate this table to the primary table or one of the other tables in the relational cache.

The system table named SYSTEM_REMOTE_SESSIONS stores information about the each of the remote sessions that are active for a given database. The values in the SYSTEM_REMOTE_SESSION table are read-only.

Table 21-6. SYSTEM_REMOTE_SESSIONS Catalog Table
Column Name	Data Type	Description
SESSION_ID	INTEGER, NOT NULL	The connection (session) id with which the remote session is associated.
SCHEMA	VARCHAR(128), NOT NULL	The schema name that is mapped to the remote session.
TYPE	VARCHAR(30), NOT NULL	The remote session type. The current valid type is Salesforce.
INSTANCE	VARCHAR(128)	The remote session instance name or null if the remote data source does not have multiple instances. The Salesforce value for INSTANCE has the following form: Organization_Name [Sandbox] where Organization_Name is the organization name of the Salesforce instance to which the connection is established. If the connection is established to a sandbox of the organization, then the word Sandbox is added to the end of the name.
VERSION	VARCHAR(30), NOT NULL	The version of the remote data source to which the session is connected. For Salesforce, this is the version of the Web Service API the driver is using to connect to Salesforce.
CONFIG_OPTIONS	LONGVARCHAR, NOT NULL	The configuration options used to define the remote data model to relational data model mapping.
SESSION_OPTIONS	LONGVARCHAR, NOT NULL	The options used to establish the remote connection. This typically is information needed to log into the remote data source. The password value is not displayed.
WS_CALL_COUNT	INTEGER, NOT NULL	The number of Web service calls made through this remote session. The value of the WS_CALL_COUNT column can be reset using the ALTER SESSION statement.
WS_AGGREGATE_CALL_COUNT	INTEGER, NOT NULL	The total of all of the Web service calls made to the same remote data source by all active connections using the same server name and user ID.
REST_AGGREGATE_CALL_COUNT	INTEGER, NOT NULL	The number of REST calls made by this connection. REST calls are used for bulk operations, invoking reports, and describing report parameters.

Table 21-6. SYSTEM_REMOTE_SESSIONS Catalog Table

INTEGER, NOT NULL

The connection (session) id with which the remote session is associated.

SCHEMA

VARCHAR(128), NOT NULL

The schema name that is mapped to the remote session.

TYPE

VARCHAR(30), NOT NULL

The remote session type. The current valid type is Salesforce.

INSTANCE

VARCHAR(128)

The remote session instance name or null if the remote data source does not have multiple instances.

The Salesforce value for INSTANCE has the following form:

Organization_Name [Sandbox]

where Organization_Name is the organization name of the Salesforce instance to which the connection is established. If the connection is established to a sandbox of the organization, then the word Sandbox is added to the end of the name.

VERSION

VARCHAR(30), NOT NULL

The version of the remote data source to which the session is connected.

For Salesforce, this is the version of the Web Service API the driver is using to connect to Salesforce.

CONFIG_OPTIONS

LONGVARCHAR, NOT NULL

The configuration options used to define the remote data model to relational data model mapping.

SESSION_OPTIONS

LONGVARCHAR, NOT NULL

The options used to establish the remote connection. This typically is information needed to log into the remote data source. The password value is not displayed.

WS_CALL_COUNT

INTEGER, NOT NULL

The number of Web service calls made through this remote session. The value of the WS_CALL_COUNT column can be reset using the ALTER SESSION statement.

WS_AGGREGATE_CALL_COUNT

INTEGER, NOT NULL

The total of all of the Web service calls made to the same remote data source by all active connections using the same server name and user ID.

REST_AGGREGATE_CALL_COUNT

INTEGER, NOT NULL

The number of REST calls made by this connection. REST calls are used for bulk operations, invoking reports, and describing report parameters.

Table 21-7. SYSTEM_SESSIONINFO Catalog Table
Key	Description
AUTOCOMMIT	Autocommit is always enabled.
DATABASE	The location and the filename prefix for the data mapping and configuration files.
DATABASE_ READONLY	Indicates whether the database the session is connected to is read only
DB_FILE_LOCATION	The fully qualified path to the directory or folder that contains the database and mapping files.
DB_FILE_PREFIX	The filename prefix of the database and mapping files the driver is using.
IDENTITY	Currently always zero
MAXROWS	Currently always zero
LOG_CONFIG_FILE	The fully qualified path to the directory or folder that contains the logging configuration file.
SCHEMA	The name of the remote Salesforce schema.
SESSION_ID	The ID for this session
SESSION_READONLY	Indicates whether the session is read only
USER	The user that is associated with this session.

Table 21-7. SYSTEM_SESSIONINFO Catalog Table

Key

Description

AUTOCOMMIT

Autocommit is always enabled.

DATABASE

The location and the filename prefix for the data mapping and configuration files.

DATABASE_ READONLY

Indicates whether the database the session is connected to is read only

DB_FILE_LOCATION

The fully qualified path to the directory or folder that contains the database and mapping files.

DB_FILE_PREFIX

The filename prefix of the database and mapping files the driver is using.

IDENTITY

Currently always zero

MAXROWS

Currently always zero

LOG_CONFIG_FILE

The fully qualified path to the directory or folder that contains the logging configuration file.

SCHEMA

The name of the remote Salesforce schema.

SESSION_ID

The ID for this session

SESSION_READONLY

Indicates whether the session is read only

USER

The user that is associated with this session.

Table 21-8. SYSTEM_SESSIONS
Column	Data Type	Description
SESSION_ID	INTEGER, NOT NULL	A unique ID that identifies this session. The system function CURSESSIONID( ) returns the session ID associated with the connection. Refer to Chapter 10 “SQL Statements and Extensions for the Salesforce Driver” in the DataDirect Connect Series for ODBC Reference for details on CURSESSIONID( ).
CONNECTED	DATETIME, NOT NULL	The date and time the session was established.
USER_NAME	VARCHAR (128), NOT NULL	The name of the embedded database that the session is using.
IS_ADMIN	BOOLEAN	For internal use only.
AUTOCOMMIT	BOOLEAN, NOT NULL	For future use.
READONLY	BOOLEAN, NOT NULL	True if the connection is in read-only mode. The READONLY status is based on whether the connection has been explicitly set to read-only mode by the Read Only connection option.
MAXROWS	INTEGER, NOT NULL	For future use.
LAST_IDENTITY	BIGINT, NULLABLE	For future use.
TRANSACTION_SIZE	INTEGER, NOT NULL	For future use.
CURRENT_SCHEMA	VARCHAR (128), NOT NULL	The current schema for the session. The current schema may be changed using the ALTER SESSION SET CURRENT_SCHEMA statement.
STMT_CALL_LIMIT	INTEGER, NOT NULL	The maximum number of Web service calls that the driver uses in attempting to execute a query to a remote data source. The statement call limit for the session may be changed via the ALTER SESSION SET STMT_CALL_LIMIT statement.

Table 21-8. SYSTEM_SESSIONS

INTEGER, NOT NULL

A unique ID that identifies this session. The system function CURSESSIONID( ) returns the session ID associated with the connection. Refer to Chapter 10 “SQL Statements and Extensions for the Salesforce Driver” in the DataDirect Connect Series for ODBC Reference for details on CURSESSIONID( ).

CONNECTED

DATETIME, NOT NULL

The date and time the session was established.

USER_NAME

VARCHAR (128), NOT NULL

The name of the embedded database that the session is using.

IS_ADMIN

BOOLEAN

For internal use only.

AUTOCOMMIT

BOOLEAN, NOT NULL

For future use.

READONLY

BOOLEAN, NOT NULL

True if the connection is in read-only mode. The READONLY status is based on whether the connection has been explicitly set to read-only mode by the Read Only connection option.

INTEGER, NOT NULL

BIGINT, NULLABLE

INTEGER, NOT NULL

VARCHAR (128), NOT NULL

The current schema for the session. The current schema may be changed using the ALTER SESSION SET CURRENT_SCHEMA statement.

STMT_CALL_LIMIT

INTEGER, NOT NULL

The maximum number of Web service calls that the driver uses in attempting to execute a query to a remote data source. The statement call limit for the session may be changed via the ALTER SESSION SET STMT_CALL_LIMIT statement.