JDBC_TABLE

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

JDBC_TABLE(uri:uri_str)
JDBC_TABLE(uri:uri_str, table:table_name_str)
JDBC_TABLE(uri:uri_str, table:table_name_str, username:uname_str, password:pswd_str)
where:
  • uri_str - is Jdbc connection string. The format of the string depends on the particular Jdbc driver of the database.
  • table_name_str - name of the table. JDBC_TABLE constructs SELECT query retrieving all rows in the table. When omitted then the query is constructed retrieving the list of all tables in all schemas.
  • 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 1: Selecting all rows from table ‘example’ in PostgreSQL database ‘test’

1
2
JDBC_TABLE(uri:'jdbc:postgresql://127.0.0.1/test', table:'example',
           username:'postgres', password:'test');

Example 2: retrieving the list of all tables from an Oracle database

1
2
JDBC_TABLE(uri:'jdbc:oracle:thin:@localhost:1521:ORCLCDB',
           username:'scott', password:'tiger')

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_TABLE, saved as: /system/employeeTable.sx:

1
2
JDBC_TABLE(uri:'jdbc:postgresql://127.0.0.1/test', table:'employees',
           username:'postgres', password:'test');

Analysis script at user directory calls it:

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