JDBC_QUERY

JDBC_QUERY allows reading data from relational databases (implemented using Java Database Connectivity (JDBC) framework):

JDBC_QUERY(uri:uri_str)
JDBC_QUERY(uri:uri_str, query:query_str)
JDBC_QUERY(uri:uri_str, query:query_str, username:uname_str, password:pswd_str)
where:
  • uri_str - is Jdbc connection string. The format of string depends on the particular Jdbc driver of the database.
  • query_str - SQL SELECT statement.
  • uname_str - username of database user. Optional.
  • pswd_str - password of database user. Optional.

Note that you need to enable respective Jdbc drivers in configuration.

Example: executing a select statement in an SQLite database

1
JDBC_QUERY(uri:'jdbc:sqlite:/tmp/example.db',query:'SELECT * FROM mytable WHERE id=1');

JDBC Drivers

See details of driver installation in the Admin Manual Database connectivity drivers section.

Protecting the Confidentiality of Credentials

To protect the confidentiality of database access credentials, the query scripts containing JDBC connection strings should be kept in user private directory (i.e under /user/).

When such query scripts are meant to be shared between users, SpectX recommends that admins declare the stream in a separate script file which can be called by each analysis script. The stream declaration must be placed under /system/ directory or protected by Data Access Control from viewing while allowing execution.

Example:

The script performing JSDBC_QUERY, saved as: /system/employeeTable.sx:

1
2
3
JDBC_QUERY(uri:'jdbc:postgresql://127.0.0.1/test',
           query:'SELECT * FROM employees WHERE id=1',
           username:'postgres', password:'test');

Analysis script at user directory calls it:

1
@[/system/employeeTable.sx] | select(name, id);