Skip to main content

SQL Data Source

The SQL data source allows for flexible importing of data stored in a variety of SQL databases. The database URL has to be in the form of a JDBC connection string:

database-type://hostname/db-name

The exact format of the connection string depends on the DBMS in use and is explained for the supported databases below. Note that not all databases require a password, depending on their configuration.

The following database drivers (JDBC) can be used for the importer configuration. Please refer to the external documentation of the specific driver for detailed usage examples. If additional JDBC drivers are required, they can be loaded dynamically.

Supported database systems​

Oracle:​

  • URL Schema (Simplified): oracle:<drivertype>:<user>/<password>@<database>
  • URL Example (Basic): oracle:thin:sys/password@myhost:1521:orcl
  • Supported versions: Up to Oracle 12c

For the configuration of an SQL import data source, the fields for username and password can be used, while <user> / <password> can be omitted. If you want to provide a specific role for authorizing the given user, you can use <user> as <role> in the user field of the connection editor. Stackoverflow has some additional information on connection strings for Oracle.

PostGreSQL​

  • URL Schema (Simplified): postgresql://<host>:<port>/<database>
  • URL Example (Basic): postgresql://mydbhost:5740/mydb
  • Supported databases: Postgres 8.2 and higher

IBM DB2​

  • URL Schema (Simplified): db2://<server>:<port>/<databaseName>
  • URL Example (Basic): db2://sysmvs1.stl.ibm.com:5021/STLEC1

MySQL​

  • URL Schema (Simplified): mysql://<host>:<port>/<databaseName>?<propertyName1=propertyValue1>&<propertyName2=propertyValue2>...
  • URL Example (Basic): mysql://localhost:3306/mydb
  • Supported databases: 5.5, 5.6, 5.7, 8.0

SocketTimeOut

You can modify the timeout of the connection via the JBCD connection string parameter socketTimeout. You can set it to 0 in order to deactivate the timeout. A full connection string could then look like this:

JDBC Connection String

jdbc:mysql://hostname/database?socketTimeout=0

MariaDB​

  • URL Schema (Simplified): mariadb:<replication:|failover:|sequential:|aurora:>//<host>:<portnumber>/<databaseName>?<key1>=<value1>&<key2>=<value2>
  • URL Example (Basic): mariadb://localhost:3306/mydb
  • Supported databases: 5.5, 10.0, 10.1, 10.2, 10.3

MSSQL​

  • URL Schema (Simplified): sqlserver://<serverName>\<instanceName>:<portNumber>;<property=value>;<property=value>
  • URL Example (Basic): sqlserver://localhost;databaseName=myDB;user=MyUser;password=MyPassword

AD-Authentication vs. MSSQL-Authentication

This does not work for Windows AD-user accounts but requires a dedicated user at the SQL server.

PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException

Starting from Txture 30, the built-in MSSQL JDBC driver started validating TLS certificates when connecting to MSSQL servers. If you do not want to store the signing CA certificate in the txture_home, you can alternatively turn off the certificate validation:

jdbc:mssql://hostname/database?trustServerCertificate=true;encrypt=true

H2​

  • URL Schema (Simplified): h2:tcp://<host>/<filepath>
  • URL Example (Basic): h2:tcp://localhost/~/test

SQLite​

  • URL Schema (Simplified): sqlite:<filepath>
  • URL Example (Basic): sqlite:/path/to/database.db
  • URL Example on Windows (Basic): sqlite:C:/path/to/database.db