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:

SQL

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

It takes the following parameters:

Parameter	Description
`name`	Name of the new connection
`jbossCLITemplateName`	Name of the CLI template used
`connectionOrResourceAdapterProperties`	Properties of the new connection; optional
`encryptedProperties`	Optional encrypted properties

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:

SQL

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

It takes the following parameters:

Parameter	Description
`name`	Name of the imported connection
`jbossCLITemplateName`	Name of the CLI template used
`connectionOrResourceAdapterProperties`	Properties of the new connection; optional
`encryptedProperties`	Encrypted properties; optional

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.removeConnection

This procedure removes a connection:

SQL

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:

SQL

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

It takes the following parameters:

Parameter	Description
`name`	Name of the new data source
`translator`	Name of translator
`modelProperties`	Model properties; optional
`translatorProperties`	Translator properties; optional
`encryptedModelProperties`	Encrypted model properties; optional
`encryptedTranslatorProperties`	Encrypted translator properties; optional

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:

SQL

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:

Parameter	Description
`name`	Name of the data source and its connection
`translator`	Name of translator
`modelProperties`	Model properties; optional
`translatorProperties`	Translator properties; optional
`encryptedModelProperties`	Encrypted model properties; optional
`encryptedTranslatorProperties`	Encrypted translator properties; optional
`connectionTemplateName`	Name of the CLI template used
`connectionProperties`	Properties of the connection; optional
`connectionEncryptedProperties`	Encrypted properties; optional
`enableReplace`	If set to `TRUE`, the procedure will try to replace an existing data source and connection with the same name if any of the parameters differ from the existing definition

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:

SQL

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

It takes the following parameters:

Parameter	Description
`name`	Name of the imported data source
`translator`	Name of translator
`modelProperties`	Model properties; optional
`translatorProperties`	Translator properties; optional
`encryptedModelProperties`	Encrypted model properties; optional
`encryptedTranslatorProperties`	Encrypted translator properties; optional

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:

SQL

SYSADMIN.recreateConnection(IN name string)

SYSADMIN.removeDataSource

This procedure removes the specified data source:

SQL

SYSADMIN.removeDataSource(IN name string)

SYSADMIN.refreshDataSource

This procedure refreshes the specified data source:

SQL

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

The procedure may have one of the following return values:

Return value	Description
`-1`	Value returned when refreshing a virtual or system schema (`VIEWS`, `SYS`, `SYSADMIN`, `PG_CATALOG`, `UTILS`, `SYSADMIN_VDB`, `SYSLOG`, and `INFORMATION_SCHEMA`)
`1`	Everything is OK
`0`	The procedure has already been called by someone else

SYSADMIN.refreshSchema

This procedure refreshes the specified schema, physical or virtual:

SQL

SYSADMIN.refreshSchema(IN name string)

SYSADMIN.refreshAllDataSources

This procedure refreshes all data sources:

SQL

SYSADMIN.refreshAllDataSources()

SYSADMIN.refreshAllSchemas

This procedure refreshes all schemas:

SQL

SYSADMIN.refreshAllSchemas()

SYSADMIN.testConnection

This procedure checks the connection with the given properties:

SQL

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:

SQL

SYSADMIN.getDataSourceMetadataDiff(IN name string)

SYSADMIN.getAllDataSourcesMetadataDiff

This procedure returns metadata differences for all data sources:

SQL

SYSADMIN.getAllDataSourcesMetadataDiff()

SYSADMIN.getCatalogs

This procedure returns the list of data source catalogs:

SQL

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:

Database	Returned values	Additional Information
BigQuery	`projectId`	Connection without a `projectId` cannot be created
PostgreSQL	database list
Redshift	database list
Snowflake	database list
SQL Server	database list

SYSADMIN.getSchemas

This procedure returns the list of data source schemas:

SQL

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:

Database	Returned values	Additional Information
BigQuery	Schemas within a project	Connection without a `projectId` cannot be created
Oracle	Namespace within a database	Connection without a database cannot be created
PostgreSQL	Schemas within a database	Without specifying a database, PostgreSQL only lists system schemas and the default one (public) for the default database (postgres)
Redshift	Schemas within a database	Redshift cannot list schemas without specifying a database
Snowflake	Schema list per database	Lists all schemas if the `db` property is not specified in the connection. If it is specified, only schemas within this database are listed
SQL Server	Schema list per database	All schemas are listed regardless of a database being specified

SYSADMIN.getDataSourcePropertiesHierarchy

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

SQL

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

Mandatory Parameters

The procedure has the following mandatory parameters:

Parameter	Description
`driverName`	The name of the driver

SYSADMIN.getDataSourcePropertiesHierarchy available since v4.6

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.