Skip to main content
Skip table of contents

Connection Configuration Interface

The CData Virtuality Server can add new data sources on the fly. To connect them, you can use special stored procedures listed on this page.

System Stored Procedures: Short Description

  • createConnection(name, jbossCLITemplateName, connectionOrResourceAdapterProperties, encryptedProperties) creates a connection with the name passed with name and properties passed with connectionOrResourceAdapterProperties (may be null) using a CLI template jbossCLITemplateName. Actual JNDI name will be formed from the given JNDI name and CLI template name divided with a minus sign (like 'template-name'). Encrypted properties may be provided via encryptedProperties.

    SQL
    CALL SYSADMIN.createConnection('<data connection name>', '<cli template name>', 'server=<server>,port=<port>,database=<database>', ''); 
  • removeConnection(name) removes connection with the name passed with name.

    SQL
    CALL SYSADMIN.removeConnection('<data connection name>');
  • createDataSource(name, translator, modelProperties, translatorProperties, encryptedModelProperties, encryptedTranslatorProperties) creates a data source with the name passed with name and on top of translator passed with translator, model properties passed with modelProperties (may be null) and translator properties passed with translatorProperties (may be null). Encrypted properties may be provided via encryptedModelProperties and encryptedTranslatorProperties .

    SQL
    CALL SYSADMIN.createDatasource('<data source name>', '<server-type>', 'importer.useFullSchemaName=<TRUE|FALSE>, importer.schemaPattern=<schema names and/or patterns>', '', '', '')

    For the schemaPattern property usage details, please check the Data Source Properties section of the JDBC Connectors page.


  • removeDataSource(name) removes a data source with the name passed with name.

    SQL
    CALL SYSADMIN.removeDatasource('<data source name>');


  • refreshDataSource(name) refreshes a data source with the name passed with name.

    SQL
    CALL SYSADMIN.refreshDataSource('<data source name>');


  • refreshAllDataSources() refreshes all data sources.
  • getDataSourceMetadataDiff(name) returns metadata difference for the data source name passed with name.

    SQL
    SELECT * FROM (CALL SYSADMIN.getDataSourceMetadataDiff('<data source name>')) a;


  • getAllDataSourcesMetadataDiff() returns metadata difference for all data sources.
  • executeCli(script) executes an arbitrary JBoss CLI script passed with script. Each command in the script is on a different line (divided by the LF char).

    SQL
    CALL SYSADMIN.executeCli('/subsystem=resource-adapters/resource-adapter=somename:add(archive=somearchive.rar, transaction-support=NoTransaction)' || chr(10) ||   '/subsystem=resource-adapters/resource-adapter=${name}:activate');


  • recreateConnection(name) removes the specified connection and then recreates it using stored template name and properties.

For more details, see also System Procedures.

Properties Parser

The parser expects all property parameters to be in CSV-like form: user=<username>,host=<hostname>.

For now, the parser supports all features provided by StreamTokenizer. Particularly, "some,words" (quoted) will be rendered into some,words (without quotes), but the comma will not be treated as a property divider.
To add actual quote char, pass it as "\042" (quoted).

Here are some examples:

Before parsingAfter parsing
host=localhost,port=25 host=localhost,port=25
importer.schemaPattern=public,importer.tableTypes="TABLE,VIEW"importer.schemaPattern=public,importer.tableTypes=TABLE,VIEW
importer.schemaPattern=public,importer.tableTypes="\042TABLE,VIEW""\042" importer.schemaPattern=public,importer.tableTypes="TABLE,VIEW"

System Tables

  • Connections holds list of all connections registered (managed with *Connection() procedures)
  • DataSources holds list of all data sources (managed with *DataSource() procedures)

Predefined CLI Templates

Here is the list of predefined CLI templates with appropriate translator names and properties. A connection name parameter is added as implicit name property for a template.

AdWords

Template name: adwords

Appropriate translator name: adwords or adwordsmgmt

Properties:

  • ClientId
  • ClientSecret
  • RefreshToken
  • DeveloperToken
  • UserAgent (default: DataVirtuality)
  • authCode
  • redirectUri
  • authType (default: service)
  • min-pool-size (default: 0)
  • max-pool-size (default: 20)

Amazon Athena

Template name: amazonathena

Appropriate translator name: amazonathena

Properties:

  • user

  • password (default: empty)

  • profile (default: empty; an AWS profile name to connect to a database)

  • S3OutputLocation

  • AwsRegion

  • driver (default: amazonathena)

  • driver-class (default: com.simba.athena.jdbc.Driver)

  • jdbc-properties (arbitrary extra properties)

The profile connection property available since v4.1

Amazon Aurora MySQL

Template name: aws_aurora_mysql

Appropriate translator name: mysql or mysql5

Properties:

  • host (default: localhost)
  • port (default: 3306)
  • db
  • user-name
  • password (default: empty)
  • driver (default: mysql)
  • driver-class (default: com.mysql.jdbc.Driver)
  • ssl (TRUE|FALSE)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql (default: set SESSION sql_mode = 'ANSI')

  • check-valid-connection-sql (default: select 1)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Amazon Aurora PostgreSQL

Template name: aws_aurora_postgresql

Appropriate translator name: postgresql

Properties:

  • host (default: localhost)
  • port (default: 5432)
  • db
  • user-name
  • password (default: empty)
  • driver (default: org.postgresql)
  • driver-class (default: org.postgresql.Driver)
  • ssl (TRUE|FALSE; default: FALSE)
  • sslmode (can be set to one of the SSL modes available in PostgreSQL such as requireverify-ca, etc. Value is used and validated only if SSL is enabled; default: require)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql
  • check-valid-connection-sql (default: select 0)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Amazon Redshift

Template name: redshift

Appropriate translator name: redshift

Properties:

  • host (default: localhost)
  • port (default: 5439)
  • db
  • user-name
  • password (default: empty)
  • driver (default: redshift)
  • driver-class (default: com.amazon.redshift.jdbc.Driver)
  • ssl (TRUE|FALSE; default: FALSE)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql

  • check-valid-connection-sql (default: select 0)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Azure

Template name: azure

Appropriate translator name: azure

Properties:

  • host
  • port (default: 1433)
  • db (default: empty)
  • jdbc-properties (default: encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;)

  • user-name
  • password
  • storageAccountKey (for blob upload mode)
  • stringDelimiter

  • driver (default: com.microsoft.sqlserver)
  • driver-class (default: com.microsoft.sqlserver.jdbc.SQLServerDriver)
  • new-connection-sql (default: empty)

  • check-valid-connection-sql (default: select 1)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Azure MSI

Template name: azure_msi

Appropriate translator name: azure

Properties:

  • host
  • port (default: 1433)
  • db (default: empty)
  • jdbc-properties (default: encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;)

  • user-name
  • password
  • stringDelimiter

  • driver (default: com.microsoft.sqlserver)
  • driver-class (default: com.microsoft.sqlserver.jdbc.SQLServerDriver)
  • new-connection-sql (default: empty)
  • check-valid-connection-sql (default: select 1)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Azure Table

Template name: azuretable

Appropriate translator name: azuretable

Properties:

  • accountName (required)
  • accountKey
  • defaultEndpointsProtocol
  • endpointSuffix
  • numberOfThreads (controls the number of threads being used to write to Azure Table Storage; default: 3; reliable values: up to 15)

blob

Template name: blob

Appropriate translator name: ufile

Properties:

  • accountName (required)
  • accountKey
  • sasToken
  • containerName (required)
  • defaultEndpointsProtocol
  • prefix

  • batchSize

  • min-pool-size (default: 0)
  • max-pool-size (default: 100)

Cassandra

Template name: cassandra

Appropriate translator name: cassandra

Properties:

  • address (required)
  • keyspace (required)
  • username
  • password (default: empty)
  • port (default: 9042)
  • min-pool-size (default: 0)
  • max-pool-size (default: 20)

CData Virtuality

Template name: datavirtuality

Appropriate translator name: teiid

Properties:

  • host (default: localhost)
  • port (default: 31000; if ssl is set to TRUE: 31001)
  • vdb
  • user-name
  • password (default: empty)
  • driver (default: teiid)
  • driver-class (default: com.datavirtuality.dv.jdbc.Driver)
  • ssl (TRUE|FALSE; default: FALSE; if set to TRUE, port is automatically set to 31001, but still may be overridden using the port parameter)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql

  • check-valid-connection-sql (default: select 1)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

ClickHouse

Template name: clickhouse

Appropriate translator name: clickhouse

Properties:

  • host (required)
  • port (default: 8123)
  • db (required)
  • user-name (required)
  • password
  • jdbc-properties (arbitrary extra properties)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Databricks

Template name: databricks

Appropriate translator name: databricks

Properties:

  • host (required)
  • port (default: 443)
  • httpPath (required)
  • uid (default: token)
  • pwd (required)
  • jdbc-properties (arbitrary extra properties)
  • transportMode (default: http)
  • schemaName (default: default; relates to schema in driver optional parameters)
  • ssl (default: 1)
  • AuthMech (default: 3)

Derby

Template name: derby

Appropriate translator name: derby

Properties:

  • host (default: localhost)
  • port (default: 1527)
  • db
  • user-name
  • password (default: empty)
  • driver-class (default: org.apache.derby.jdbc.ClientDriver)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql

  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

EXASOL

Template name: exasol

Appropriate translator name: exasol

Properties:

  • host (default: localhost)
  • port (default: 8563)
  • user-name
  • password (default: empty)
  • driver (default: exasol)
  • driver-class (default: com.exasol.jdbc.EXADriver)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql

  • check-valid-connection-sql (default: select 0)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Excel

Template name: excel

Appropriate translator name: excel

Properties:

  • ParentDirectory (required)

  • AllowParentPaths (TRUE|FALSE; default: TRUE)

  • min-pool-size (default: 0)
  • max-pool-size (default: 20)

Facebook

Template name: facebook

Appropriate translator name: ws

Properties:

  • EndPoint
  • SecurityType
  • AuthUserName
  • AuthPassword
  • AccessToken
  • RefreshToken
  • ClientId
  • ClientSecret
  • AccessTokenEndpoint
  • ConfigFile
  • ConfigName
  • RequestTimeout
  • Scope
  • RedirectUri
  • min-pool-size (default: 0)
  • max-pool-size (default: 20)

File

Template name: file

Appropriate translator name: file

This template will be completely replaced by ufile in future. Please use ufile for new file datasources.

Properties:

  • ParentDirectory (FS path)
  • min-pool-size (default: 0)
  • max-pool-size (default: 20)

To read UTF-8 files, provide the translator property Encoding=utf8. If the UTF-8 file has UTF-8 BOM bytes, they are skipped automatically.

SQL
CALL SYSADMIN.createDataSource('csv', 'file', null, 'Encoding=utf8');

FTP

Template name: ftp

Appropriate translator name: ufile

Properties:

  • host (default: localhost)
  • port (default: 21)
  • user

  • remoteDirectory

  • secure
  • explicitTls

  • passive
  • decompressCompressedFiles

  • proxy

  • proxyHost

  • proxyPort

  • proxyUser

  • proxyPassword

  • serverTimeout (default: 60)

  • min-pool-size (default: 0)
  • max-pool-size (default: 100)

Google Analytics

Template name: analytics

Appropriate translator name: analytics

Properties:

  • clientemail
  • clientkeypath (if it starts from '/' it means path to a key file under jboss.home system property, otherwise it must contain base64-encoded binary key data)
  • timeout (connection timeout in seconds; default: 0, meaning an infinite timeout)
  • СlientId 
  • СlientSecret 
  • authCode 
  • redirectUri
  • authType (default: service)  (deprecated)
  • SecurityType
  • RefreshToken
  • dataSourceName
  • min-pool-size (default: 0)
  • max-pool-size (default: 20)

Google Analytics Data

Template name: ga4

Appropriate translator name: ga4

Properties:

  • authType
  • privateKeyId
  • privateKey
  • clientEmail
  • СlientId
  • clientSecret
  • refreshToken
  • authCode 
  • redirectUri
  • timeout
  • min-pool-size (default: 0)
  • max-pool-size (default: 20)

Google Analytics Data connector is available since v4.0.6

clientSecret and refreshToken support since v4.0.7

authCode and redirectUri support since v4.0.8

Google BigQuery

Template name: bigquery

Appropriate translator name: bigquery

Properties:

  • projectId (required)
  • transformQuery (default: TRUE); (obsolete)
  • credentialFactory (default: com.datavirtuality.dv.core.oauth.credential.BigQueryOAuthCredentialFactory)

  • allowLargeResults (obsolete)
  • largeResultsDefaultDataset
  • tableMaxResults
  • fetchSize
  • refreshToken

  • accessToken

  • expirationTimeMilliseconds

  • region

  • authCode

  • redirectUri
  • user-name (required)
  • password (default: empty)
  • ClientId
  • ClientSecret
  • driver (default: bigquery)
  • driver-class (default: com.datavirtuality.jdbc.bq.BQDriver)
  • storageProjectId (default: empty)
  • storageUser (default: empty)
  • storagePassword (default: empty)
  • new-connection-sql
  • check-valid-connection-sql (default: select 1)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)
  • readTimeout (default: 20000, 0 for an infinite, a negative for the default)
  • connectTimeout (default: 20000, 0 for an infinite, a negative for the default)

readTimeout and connectTimeout connection properties available since v4.6

Greenplum

Template name: greenplum

Appropriate translator name: jdbc-ansi

Properties:

  • host (default: localhost)
  • port (default: 5433)
  • db (required; if absent: No database name provided)
  • user-name
  • password (default: empty)
  • driver (default: greenplum)
  • driver-class (default: org.postgresql.Driver)
  • ssl (TRUE|FALSE)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql
  • check-valid-connection-sql (default: select 0)
  • min-pool-size (default: 0)
  • max-pool-size (default: 20)

H2

Template name: h2

Appropriate translator name: h2

Properties:

  • file (if your DB file has name 'c:/db/stats.h2.db', provide ' c:/db/stats' here, i.e. omit the '.h2.db'  extension)
  • driver (default: com.h2database.h2)
  • user-name (default: sa)
  • password (default: empty)
  • driver (default: h2)

  • driver-class (default: org.h2.Driver )

  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql

  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Hive

Template name: hive2

Appropriate translator name: hive

Properties:

  • host (default: localhost)
  • port (default: 10000)
  • db (default: default)
  • user-name
  • password (default: empty)
  • driver (hive for hive-jdbc-1.2.1, oldhive for hive-jdbc-1.1.0; default: hive)
  • driver-class (default: org.apache.hive.jdbc.HiveDriver)

  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql
  • check-valid-connection-sql (default: show databases)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

IBM DB2

Template name: db2

Appropriate translator name: db2

Properties:

  • host (default: localhost)
  • port (default: 50000)
  • db
  • user-name
  • password (default: empty)
  • driver (default: db2)
  • driver-class (default: com.ibm.db2.jcc.DB2Driver)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql
  • check-valid-connection-sql (default: VALUES 1)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

IBM DB2 for iSeries (AS/400)

Template name: db2as400

Appropriate translator name: db2as400

Properties:

  • host (default: localhost)
  • port (default: 446)
  • db
  • user-name
  • password (default: empty)
  • driver (default: db2as400)
  • driver-class (default: com.ibm.as400.access.AS400JDBCDriver)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql
  • check-valid-connection-sql (default: VALUES 1)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

IBM DB2 for zSeries (zOS)

Template name: db2zos

Appropriate translator name: db2zos

Properties:

  • host (default: localhost)
  • port (default: 4019)
  • db
  • user-name
  • password (default: empty)
  • driver (default: db2zos)
  • driver-class (default: com.ibm.db2.jcc.DB2Driver)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql
  • check-valid-connection-sql (default: VALUES 1)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

IBM Informix

Template name: informix

Appropriate translator name: informix

Properties:

  • host (default: localhost)
  • port 
  • db (required; if absent: No database name provided)
  • server-name (required)
  • user-name
  • password 
  • driver (default: informix)
  • driver-class (default: com.informix.jdbc.IfxDriver)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql
  • jta (must be set to FALSE while connecting to Informix unlogged database, i.e. database that cannot use transactions
  • min-pool-size (default: 2)
  • max-pool-size (default: 6)

IBM Netezza

Template name: netezza

Appropriate translator name: netezza

Properties:

  • host (default: localhost)
  • port (default: 5480)
  • db (default: empty)
  • user-name
  • password
  • driver (default: netezza)
  • driver-class (default: org.netezza.Driver)
  • new-connection-sql
  • jdbc-properties (arbitrary extra properties)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Ingres

Template name: ingres

Appropriate translator name: ingres or ingres93

Properties:

  • host (default: localhost)
  • port (default: 21071)
  • db
  • user-name
  • password (default: empty )
  • driver (default: ingres)
  • driver-class (default: com.ingres.jdbc.IngresDriver)

  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql

  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

InterSystems Caché

Template name: intersystemscache

Appropriate translator name: intersystems-cache

Properties:

  • host (default: localhost)
  • port (default: 1972)
  • db
  • user-name
  • password (default: empty )
  • driver (default: cache)
  • driver-class (default: com.intersys.jdbc.CacheDriver)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql

  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

JDBC

Template name: jdbc

Appropriate translator name: jdbc-ansi or jdbc-simple

Properties:

  • connection-url (JDBC connection URL)
  • user-name
  • password (default: empty )
  • driver
  • driver-class
  • new-connection-sql

  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

kdb+

Template name: kdb

Appropriate translator name: kdb

Properties:

  • host (default: localhost)
  • port (default: 5000)
  • user-name
  • password
  • new-connection-sql
  • jdbc-properties (arbitrary extra properties)
  • driver (default: kdb)
  • driver-class (default: jdbc)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

ksqlDB

Template name: ksqldb

Appropriate translator name: ksqldb

Properties:

  • host (default: localhost)
  • port (default: )
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

LDAP

Template name: ldap

Appropriate translator name: ldap

Properties:

  • host (default: localhost)
  • port (default: 389)
  • user-dn (format: cn=???,ou=???,dc=???)
  • password
  • timeout (in milliseconds; default: -1)
  • min-pool-size (default: 0)
  • max-pool-size (default: 20)

MemSQL

See SingleStore

Microsoft Dataverse

Template name: dataverse_jdbc

Appropriate translator name: jdbc-ansi

Properties:

  • jdbc-properties (required)
  • OAuthClientId (required)
  • OAuthClientSecret (required)
  • OrganizationURL (required, set via jdbc-properties)
  • AuthScheme (required, set via jdbc-properties)
  • persist_oauthaccesstoken (set via jdbc-properties)

Microsoft SQL Server

Template name: mssql

Appropriate translator name: sqlserver

Properties:

  • host (default: localhost)
  • port (no default; for connection to a Microsoft SQL Server with dynamic port allocation configured, instanceName needs to be provided via jdbc-properties)
  • db
  • user-name
  • password (default: empty)
  • encrypt (default: FALSE; enables connection encryption and has the same possible string value as the corresponding SQL Server JDBC driver property: TRUE, FALSE, and STRICT)
  • driver (default: com.microsoft.sqlserver)
  • driver-class (default: com.microsoft.sqlserver.jdbc.SQLServerDriver)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql (default: SET ANSI_WARNINGS OFF)
  • check-valid-connection-sql (default: select 1)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Microsoft SQL Server jTDS

Template name: mssql_jtds

Appropriate translator name: sqlserver

Properties:

  • host (default: localhost)
  • port (default: 1433)
  • db
  • user-name
  • password (default: empty )
  • driver (default: jtds)
  • driver-class (default: net.sourceforge.jtds.jdbc.Driver)
  • ParentDirectory (required)

  • AllowParentPaths (TRUE|FALSE; default: TRUE)

  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql (default: SET ANSI_WARNINGS OFF)
  • check-valid-connection-sql (default: select 1)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

MongoDB

Template name: mongodb

Appropriate translator name: mongodb

Properties:

  • server (default: localhost)
  • port (default: 27017)
  • database
  • login (optional)
  • password (optional if no login specified)
  • authSource (optional)
  • readMode
  • options
  • min-pool-size (default: 0)
  • max-pool-size (default: 100)

MySQL

Template name: mysql

Appropriate translator name: mysql or mysql5

Properties:

  • host (default: localhost)
  • port (default: 3306)
  • serverId (default: empty)

  • db
  • user
  • password (default: empty)
  • driver (default: mysql)
  • driver-class (default: com.mysql.cj.jdbc.Driver)
  • ssl (TRUE|FALSE)

  • jdbcProperties (arbitrary extra properties; default: empty)
  • useL2Buffer

  • new-connection-sql (default: set SESSION sql_mode = 'ANSI')

  • check-valid-connection-sql (default: select 1)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

MySQL CDC

Template name: mysqlcdc

Appropriate translator name: mysqlcdc

Properties:

  • host (default: localhost)
  • port (default: 3306)
  • serverId
  • user
  • password
  • jdbcProperties (arbitrary extra properties)
  • useL2Buffer
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Neo4j

Template name: neo4j

Appropriate translator name: neo4j

Properties:

  • host (default: localhost)
  • port (default: 7687)
  • user-name
  • password
  • driver (default: neo4j)
  • driver-class (default: org.neo4j.jdbc.bolt.BoltDriver)
  • new-connection-sql

  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

ODBC

Template name: odbc

Appropriate translator name: jdbc-ansi or jdbc-simple

Properties:

  • DSN
  • user-name
  • password (default: empty )
  • charset
  • driver (default: odbc)

  • driver-class (default: sun.jdbc.odbc.JdbcOdbcDriver)
  • new-connection-sql
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

OLAP

Mondrian OLAP

Template name: olap_mondrian

Appropriate translator name: olap

Properties:

  • db-config (format: DataSource=java:foodmart;Catalog=FoodMart;Catalog=deploy/mondrian.war/WEB-INF/queries/FoodMart.xml)
  • user-name
  • password (default: empty )
  • driver (default: olap)
  • driver-class (default: mondrian.olap4j.MondrianOlap4jDriver)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

XMLA OLAP

Template name: olap_xmla

Appropriate translator name: olap

Properties:

  • host (default: localhost)
  • port (default: 8080)
  • path (default: /mondrian/xmla)
  • db-config (format: DataSource=java:foodmart;Catalog=FoodMart)
  • user-name
  • password (default: empty )
  • driver (default: olap)
  • driver-class (default: org.olap4j.driver.xmla.XmlaOlap4jDriver)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql

  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Oracle ADWC

Template name: oracleadwc

Appropriate translator name: oracle

Properties:

  • tns 
  • user-name
  • password 
  • driver (default: oraclev7)
  • driver-class (default: oracle.v7.jdbc.OracleDriver)
  • wallet
  • tns_admin (default: "dvserver/standalone/deployments/wallets/wallet_<tns>/; the path should end with '/')
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql (default: alter session set nls_sort = BINARY)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

tns_admin available since v4.0.7

Oracle Database

Template name: oracle

Appropriate translator name: oracle

Properties:

  • host (default: localhost)
  • port (default: 1521)
  • db
  • sid
  • service-name (db, or sid, or service-name must be specified)
  • user-name
  • password (default: empty )
  • driver (default: oracle)
  • driver-class (default: oracle.jdbc.OracleDriver)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql (default: alter session set nls_sort = BINARY)
  • check-valid-connection-sql (default: SELECT 1 FROM DUAL)
  • validate-on-match (default: FALSE; when set to TRUE, database connection is validated every time it is checked out from the connection pool)
  • valid-connection-checker-class-name
  • background-validation (default: TRUE; when set to TRUE, database connection is validated at frequent intervals; background-validation-millis should be > 0)
  • background-validation-millis (default: 60000)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Oracle NetSuite

Template name: netsuite

Appropriate translator name: netsuite

Properties: 

  • serverHost (default:  odbcserver.sandbox.netsuite.com)
  • port (default: 1708)
  • serverDataSource (default:  NetSuite.com )
  • encrypted (default: 1)
  • accountID (required)
  • roleID (default: 3)
  • user-name (required)
  • password (default: empty)
  • driver (default: netsuite)
  • driver-class (default: com.netsuite.jdbc.openaccess.OpenAccessDriver)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql
  • check-valid-connection-sql (default: select 0)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Parquet

Template name: parquet

Appropriate translator name: parquet

Properties:  

  • path (FS path)
  • min-pool-size (default: 0)
  • max-pool-size (default: 20)

Pavi

Template name: pavi

Appropriate translator name: pavi

Properties:

  • EndPoint (default: http://pav3.cdyne.com/i/PavInternationalService.svc/VerifyAddressInternational)
  • min-pool-size (default: 0)
  • max-pool-size (default: 20)

PostgreSQL

Template name: postgresql

Appropriate translator name: postgresql

Properties:

  • host (default: localhost)
  • port (default: 5432)
  • db
  • user-name
  • password (default: empty )
  • driver (default: org.postgresql)
  • driver-class (default: org.postgresql.Driver)
  • ssl (TRUE|FALSE; default: FALSE)
  • sslmode (can be set to one of the SSL modes available in PostgreSQL such as requireverify-ca, etc. Value is used and validated only if SSL is enabled; default: require)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql

  • check-valid-connection-sql (default: select 0)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Redis

Template name: redis

Appropriate translator name: redis

Properties: 

  • host (default: localhost)
  • port (default: 1433)
  • db
  • password
  • min-pool-size (default: 0)
  • max-pool-size (default: 100)

Rserve

Template name: rserve

Appropriate translator name: rserve

Properties:

  • host (default: localhost)
  • port (default: 6311)
  • user
  • password
  • min-pool-size (default: 0)
  • max-pool-size (default: 20)

s3

Template name: s3

Appropriate translator name: ufile

Properties:

  • keyId (required)
  • secretKey (required)
  • bucketName (required)
  • region (optional)
  • prefix

  • grantee

  • permission

  • partSize

  • multipartUpload

  • numberOfThreads

  • decompressCompressedFiles

  • min-pool-size (default: 0)
  • max-pool-size (default: 100)

Salesforce

Salesforce

Template name: salesforce

Appropriate translator name: salesforce

Properties:

  • url (default: https://login.salesforce.com/services/Soap/u/34.0)
  • user-name
  • password
  • securityToken

  • connectTimeout (connection timeout in milliseconds; default: 120000)
  • requestTimeout (request timeout in milliseconds; default: 240000)
  • ClientId

  • ClientSecret

  • RefreshToken

  • AccessTokenEndpoint (default: https://login.salesforce.com/services/oauth2/token)

  • defaultTokenRefreshTimeout (default: 28800)
  • proxyURL (optional)
  • min-pool-size (default: 0)
  • max-pool-size (default: 20)

Salesforce41

Template name: salesforce41

Appropriate translator name: salesforce41

Properties:

  • url (default: https://login.salesforce.com/services/Soap/u/45.0)
  • user-name
  • password
  • securityToken
  • connectTimeout (connection timeout in milliseconds; default: 120000)
  • requestTimeout (request timeout in milliseconds; default: 240000)
  • ClientId

  • ClientSecret

  • RefreshToken

  • AccessTokenEndpoint (default: https://login.salesforce.com/services/oauth2/token)

  • defaultTokenRefreshTimeout (default: 28800)

  • proxyURL (optional)
  • min-pool-size (default: 0)
  • max-pool-size (default: 20)

SAP Advantage Database Server

Template name: sapads

Appropriate translator name: sapads

Properties:

  • host (default: localhost)
  • port (default: 6262)
  • catalog (required; if absent: No catalog provided)
  • jdbc-properties (arbitrary extra properties)
  • user-name
  • password
  • driver (default: sapads)
  • driver-class (default: com.extendedsystems.jdbc.advantage.ADSDriver)
  • new-connection-sql
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

SAP ASE (Sybase)

Template name: sybaseiq

Appropriate translator name: sybase

Properties:

  • host (default: localhost)
  • port (default: 2638)
  • db (default: empty)
  • user-name
  • password
  • driver (default: jconnect)
  • driver-class (default: com.sybase.jdbc4.jdbc.SybDriver)
  • jdbc-properties (arbitrary extra properties)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

SAP HANA

Template name: hana

Appropriate translator name: hana

Properties:

  • host (default: localhost)
  • port (default: 39013)
  • db (default: empty)
  • user-name
  • password
  • driver (default: hana)
  • driver-class (default: com.sap.db.jdbc.Driver)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

jdbc-properties connection property is available since v4.0.7

SAS

Template name: sas

Appropriate translator name: jdbc-ansi

Properties:

  • user-name (mandatory)
  • password (optional)
  • host (default: localhost)
  • port (default: 5010)
  • app-name (required; if absent: No app name provided)
  • jdbc-properties (optional)
  • driver (default: sas)
  • driver-class (default: com.sas.net.sharenet.ShareNetDriver)
  • new-connection-sql (optional)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

SCP

Template name: scp

Appropriate translator name: ufile

Properties:

  • host (default: localhost)
  • port (default: 22)
  • user
  • remoteDirectory
  • privatekey

  • decompressCompressedFiles

  • password
  • min-pool-size (default: 0)
  • max-pool-size (default: 100)

SFTP

Template name: sftp

Appropriate translator name: ufile

Properties:

  • host (default: localhost)
  • port (default: 22)
  • user
  • remoteDirectory

  • privatekey

  • decompressCompressedFiles

  • proxy

  • proxyHost

  • proxyPort

  • proxyPassword

  • config (default: StrictHostKeyChecking=no; used for SSH configuration)
  • min-pool-size (default: 0)
  • max-pool-size (default: 100)

config connection property available since v4.0.8

SingleStore (formerly MemSQL)

Template name: memsql

Appropriate translator name: memsql

Properties:  

  • host (default: localhost)
  • port (default: 3306)
  • db
  • user-name
  • password (default: empty )
  • driver (default: mysql)
  • driver-class (default: com.mysql.cj.jdbc.Driver)
  • ssl (TRUE|FALSE)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql (default: set SESSION sql_mode = 'ANSI')

  • check-valid-connection-sql (default: select 1)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Snowflake

Template name: snowflake

Appropriate translator name: snowflake

Properties:

  • host (default: localhost)
  • db (required; if absent: No database name provided)
  • schema
  • warehouse (required; if absent: No warehouse name provided)
  • CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX (default: TRUE)
  • application (default: DataVirtuality_DV)
  • user-name
  • password
  • driver (default: snowflake)
  • driver-class (default: net.snowflake.client.jdbc.SnowflakeDriver)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql
  • check-valid-connection-sql (default: select 0)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Teradata

Template name: teradata

Appropriate translator name: teradata

Properties:

  • host (default: localhost)
  • db (required)
  • user-name
  • password (default: empty )
  • driver (default: teradata)
  • driver-class (default: com.teradata.jdbc.TeraDriver)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql
  • check-valid-connection-sql (default: select 0)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Trino

Template name: trino

Appropriate translator name: trino

Properties:

  • host(default: localhost)

  • port (default: 8080)

  • catalog (default: empty)

  • db(default: empty)

  • jdbc-properties (arbitrary extra properties)

  • user-name(default: empty)

  • password(default: empty)

Trino connector available since v4.7

Twitter

Template name: twitter

Appropriate translator name: ws

Properties:

  • EndPoint
  • SecurityType
  • AuthUserName
  • AuthPassword
  • AccessToken
  • RefreshToken
  • ClientId
  • ClientSecret
  • AccessTokenEndpoint
  • ConfigFile
  • ConfigName
  • RequestTimeout
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

ufile

Template name: ufile

Apprpriate translator name: u file

Properties:

  • ParentDirectory (required)
  • AllowParentPaths (default: TRUE)

  • decompressCompressedFiles (default: FALSE)

  • min-pool-size (default: 0)
  • max-pool-size (default: 20)

Vertica

Template name: vertica

Appropriate translator name: vertica

Properties:

  • host (default: localhost)
  • port (default: 5433)
  • db (required; if absent: No database name provided)
  • user-name
  • password (default: empty )
  • driver (default: vertica)
  • driver-class (default: com.vertica.jdbc.Driver)
  • ssl (TRUE|FALSE)
  • jdbc-properties (arbitrary extra properties)
  • new-connection-sql
  • new-connection-sql
  • check-valid-connection-sql (default: select 0)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

WS

Template name: ws

Appropriate translator name: ws

Properties:

  • EndPoint (URL for HTTP, Service Endpoint for SOAP)
  • SecurityType (web service security used; possible values: NoneHTTPBasicWSSecurity, OAuth2,NTLM; optional)
  • AuthUserName (optional)
  • AuthPassword (optional)
  • AuthDomain (optional)
  • AuthHost (optional)
  • AccessToken

  • RefreshToken

  • ClientId

  • ClientSecret

  • scope (used in case of OAuth2 ClientId/ClientSecret authorization to obtain an access token)
  • cloudAgentWsHost(target host name as seen from inside the agent network)
  • cloudAgentWsPath(optional)(specific endpoint path to mount the data source to)
  • AccessTokenEndpoint

  • TranslatorName

  • ConfigFile (CXF client configuration file or URL; optional)
  • ConfigName (local name of the QName of the port configuration to use with this connection; default namespace URI: http://teiid.org; optional)
  • ConnectTimeout (optional)
  • RequestTimeout (optional)
  • redirectUri

  • authCode

  • decompressCompressedFiles (if set to TRUE, a gzipped response of invokeHttp will be decompressed on the fly; default: FALSE)
  • expirationTimeMilliseconds
  • min-pool-size (default: 0)
  • max-pool-size (default: 20)
  • TenantId

NTLM security type available since v4.1

AuthDomain and AuthHost connection properties available since v4.1 

cloudAgentWsHost and cloudAgentWsPath connection properties available since v4.7

scope parameter is available since v4.7


Do not create CLI templates with names containing the minus sign because it is used as a divider when generating internal connection names.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.