JDBC Drivers
datasqill provides free JDBC drivers that can be used freely. These drivers aim to enable the use of the "Transfer between DBs" module for reading data that does not actually reside in a database and for which there are therefore no JDBC drivers. The following drivers are currently implemented:
- JDBC driver for CSV files
- JDBC driver for Excel files
- JDBC driver for JSON files
- JDBC driver for XML files
- JDBC driver for YAML files
- JDBC driver for LDAP servers
- JDBC driver for Salesforce
- JDBC-Treiber for REST-APIs
- JDBC driver for Meta (Facebook/Instagram/Ads)
- JDBC driver for Eloqua
- JDBC driver for SAP
All drivers use an SQL parser, so the SQL language scope for these sources is approximately the same.
For use with datasqill, the drivers also provide metainformation (or system tables). For example, with the file-based JDBC drivers, files in a specific directory can be queried as metadata in the Loop Query in the module and iterated over via the Loop Query. The SQL in the Loop query thus selects from the metadata (or system tables) and the SQL in the action then selects from the source object.
Most of the drivers currently only support reading via SQL SELECT statement but first implementation with INSERT, UPDATE and DELETE are available.
Connection Properties
Properties can be set when opening a driver.
When used with datasqill, these properties are configured in the keyfile.
The keyfile consists of multiple sections separated by pipe symbols. Connection parameters can be entered between the last two pipe symbols. Properties are specified as follows:
connectionOption1:dboptionlist={propertyName1=xx,propertyName2=yy}:connectionOption3
This example defines three connection options (separated by colons).
The second connection option, dboptionlist, defines the driver properties.
In this example, two properties are defined (separated by commas).
Currently, the following property is supported:
| Name | Value | Meaning |
|---|---|---|
| NullToEmptyStrings | empty | If the value of a column is NULL, return an empty string |
| NullToEmptyStrings | null | If the value of a column is an empty string, return NULL |
| NullToEmptyStrings | keep | For NULL return NULL and for an empty string return an empty string |
Properties in the JDBC URL
Some clients pass only the JDBC URL, together with user and password. datasqill drivers then read further properties from a suffix after the first |:
jdbc:rest:https://api.example.com|spec=/path/spec.json,auth=bearer,token=secret
The pairs are separated by commas. A value that contains a comma is wrapped in double quotes. The driver removes the suffix before it opens the connection, so the address stays jdbc:rest:https://api.example.com. A pair that is not key=value is rejected.
A property from the client replaces the same key from the URL when the client value is not empty. An empty password does not remove a password that was given after |. User and password from the client therefore take precedence.
Other JDBC drivers do not read this suffix. In the Simple Client the properties are passed with properties(...), and the URL is left unchanged.