The SQLDbToFile connector allows you to run an SQL query at regular time intervals and retrieve the results in the form of CSV files. The execution period is chosen from the Scan Mode defined in the Engine part of OIBus.
Connexion to a SQL database
Before being able to make a request, you must indicate the database on which OIBus should connect to. To do this, you must complete the following elements:
- Host: the address of the SQL server
- Port: SQL server port
- Database: the name of the database to connect to
- SQL Driver: the database technology used
- User / Password / Domain: the username and password used for authentication. The domain is optional: this field is useful for example when the user wishes to connect to an Active Directory domain
- Microsoft SQL Server: If necessary, a domain to identify the user can be specified
- Oracle: This driver requires the local installation of an Oracle Instant Client. Basic or Basic Light package are enough but minimal supported version is 126.96.36.199.0. The installation procedure of the Instant Client is described at the end of the mentionned download page.
- MySQL / MariaDB
- SQLite: This driver only uses the path of a local .db file where the data are stored.
The SQL query must then be completed in the Query field. It is possible to use the @LastCompletedDate variable in a “WHERE” clause to retrieve data with a timestamp greater than the date of the last query.
@LastCompletedDate initially takes the date of the first execution of the query. When the results are retrieved by OIBus, the connector retrieves the most recent timestamp among those results and updates @LastCompletedDate with this value. The next time the query is executed with @LastCompletedDate, the query will therefore retrieve the values since the last most recent recorded timestamp.
As some requests may be relatively long, it is possible to enter longer connection and request timeouts (in ms).
Finally, some parameters allow you to configure the CSV files in which the results will be saved:
- Delimiter: the delimiter used by the CSV
- Date format: the date format
- Time column: the name of the column containing the timestamp
- Timezone: the time zone in which to write the timestamps
The name of the file can respond to a particular pattern allowing a unique name to be associated with each request. This is possible with the @date variable. For example firstname.lastname@example.org will generate a file whose name can be sql-2020-01-01 00:00:00.csv.