Connection and Data Source Management

In the CData Virtuality Server, different data sources can be queried using specific connectors. On this page, you will find the syntax and lists of parameters for dedicated stored procedures; for more general information on their use, please refer to the Connecting Data Sources chapter.

SYSADMIN.createConnection

This procedure creates a connection:

SYSADMIN.createConnection(IN name string, IN jbossCLITemplateName string, IN connectionOrResourceAdapterProperties string, IN encryptedProperties string)

It takes the following parameters:

The actual JNDI name will be formed from the given name and the CLI template name joined with the minus sign (like template-name).

SYSADMIN.importConnection

This procedure imports a connection which will not fail if a connection cannot be established:

SYSADMIN.importConnection(IN name string, IN jbossCLITemplateName string, IN connectionOrResourceAdapterProperties string, IN encryptedProperties string)

It takes the following parameters:

The actual JNDI name will be formed from the given name and the CLI template name joined with the minus sign (like template-name).

SYSADMIN.dropConnection

This procedure drops a connection:

SYSADMIN.dropConnection(IN name string)

It takes a single parameter: name of the connection to be dropped.

SYSADMIN.removeConnection

This procedure removes a connection:

SYSADMIN.removeConnection(IN name string)

It takes a single parameter: name of the connection to be removed.

SYSADMIN.createDataSource

This procedure creates a data source:

SYSADMIN.createDataSource(IN name string, IN translator string, IN modelProperties string, IN translatorProperties string, encryptedModelProperties string, encryptedTranslatorProperties string)

It takes the following parameters:

If the data source is based on a modular connector, the modular connector should be deployed via the SYSADMIN.deployModularConnector procedure before creating the data source. Please refer to Modular Connectors Management for more details.

SYSADMIN.createOrReplaceDatasource

This procedure creates a data source together with a connection it is missing or replaces the existing one if any of the data source or connection parameters differ from the existing data source or connection definition:

SYSADMIN.createOrReplaceDatasource(IN name string, IN translator string, IN modelProperties string, IN translatorProperties string, IN encryptedModelProperties string, IN encryptedTranslatorProperties string, IN connectionTemplateName string, IN connectionProperties string, IN connectionEncryptedProperties string, IN enableReplace boolean DEFAULT 'false')

It takes the following parameters:

If the data source is based on a modular connector, the modular connector should be deployed via the SYSADMIN.deployModularConnector procedure before creating the data source. Please refer to Modular Connectors Management for more details.

SYSADMIN.importDataSource

This procedure imports a data source:

SYSADMIN.importDataSource(IN name string, IN translator string, IN modelProperties string, IN translatorProperties string, encryptedModelProperties string, encryptedTranslatorProperties string)

It takes the following parameters:

If the data source is based on a modular connector, the respective modular connector should be deployed via the SYSADMIN.deployModularConnector procedure before importing the data source. Please refer to Modular Connectors Management for more details.

SYSADMIN.recreateConnection

This procedure removes the specified connection and then recreates it using the stored template name and properties:

SYSADMIN.recreateConnection(IN name string)

SYSADMIN.dropDataSource

This procedure drops the specified data source. Set dropConnection to TRUE to drop the corresponding connection as well:

SYSADMIN.dropDataSource(IN name string, IN dropConnection boolean)

SYSADMIN.removeDataSource

This procedure removes the specified data source. Set dropConnection to TRUE to remove the corresponding connection as well:

SYSADMIN.removeDataSource(IN name string, IN dropConnection boolean)

SYSADMIN.refreshTables

This procedure refreshes the tables:

SYSADMIN.refreshTables(IN schemaName string NOT NULL, IN tableNamePattern string NOT NULL)

It refreshed tables selected by specified pattern (an SQL pattern, as for a LIKE clause).

The tableNamePattern value is case-sensitive.

The SYSADMIN.refreshTables is primarily intended for use with one or a small number of tables. If you need to refresh all tables or a large number of them, we recommend using SYSADMIN.refreshDataSource, as refreshing many tables via SYSADMIN.refreshTables may take a significantly longer time.

SYSADMIN.refreshDataSource

This procedure refreshes the specified data source:

SYSADMIN.refreshDataSource(IN name string, OUT reply integer NOT NULL RESULT)

The procedure may have one of the following return values:

SYSADMIN.refreshTables and SYSADMIN.refreshDataSource Parallel Calls Behaviour

SYSADMIN.refreshTables Calls

Calls to SYSADMIN.refreshTables for tables or patterns from the same data source are queued and executed in the order they are invoked. This mechanism prevents concurrency issues by ensuring that calls are handled sequentially.

Combined SYSADMIN.refreshDataSource with SYSADMIN.refreshTables Calls

When SYSADMIN.refreshDataSource calls are combined with SYSADMIN.refreshTables, they are queued and executed in the same sequential manner.

Multiple SYSADMIN.refreshDataSource Calls

If two or more SYSADMIN.refreshDataSource calls for the same data source occur without SYSADMIN.refreshTables calls in between:

• Only the first query is executed;

• Subsequent calls will wait for the first query to complete and will receive the same status as the first query upon its completion.

SYSADMIN.refreshSchema

This procedure refreshes the specified schema, physical or virtual:

SYSADMIN.refreshSchema(IN name string)

SYSADMIN.refreshAllDataSources

This procedure refreshes all data sources:

SYSADMIN.refreshAllDataSources() 

SYSADMIN.refreshAllSchemas

This procedure refreshes all schemas:

SYSADMIN.refreshAllSchemas() 

SYSADMIN.testConnection

This procedure checks the connection with the given properties:

SYSADMIN.testConnection(IN jbossCLITemplateName string, IN connectionOrResourceAdapterProperties string, IN translator string, IN translatorProperties string, IN encryptedProperties string, IN encryptedTranslatorProperties string)

encryptedProperties and encryptedTranslatorProperties properties of SYSADMIN.testConnection are available since v4.4

SYSADMIN.getDataSourceMetadataDiff

This procedure returns metadata difference for the specified data source:

SYSADMIN.getDataSourceMetadataDiff(IN name string)

SYSADMIN.getAllDataSourcesMetadataDiff

This procedure returns metadata differences for all data sources:

SYSADMIN.getAllDataSourcesMetadataDiff() 

SYSADMIN.getCatalogs

This procedure returns the list of data source catalogs:

SYSADMIN.getCatalogs"("connectionName" => 'connection_name')

It takes a single parameter, the connection name, and returns values which can be used in creating a data source:

SYSADMIN.getSchemas

This procedure returns the list of data source schemas:

SYSADMIN.getSchemas"("connectionName" => 'connection_name')

It takes a single parameter, the connection name, and returns values that can be used in creating a data source:

SYSADMIN.getDataSourcePropertiesHierarchy

This procedure returns the properties hierarchy for a CData driver in JSON format. The default context is _datahubh.

SYSADMIN.getDataSourcePropertiesHierarchy(IN driverName string NOT NULL, IN context string, OUT hierarchy string)

Mandatory Parameters

The procedure has the following mandatory parameters:

Connection Properties Validation

Connection properties are validated against the CLI templates when a connection is created, edited, deleted, and tested. Connection parameters not present in the CLI template are not accepted.