Generator: SQL query

This generator allows the user to type in an SQL query to run against an SQL or a PostgreSQL database. The result of this query is reported in Nexus as a table item. The items in the table are always returned as strings, even if they are numeric in the source database.

Configuration options

The generator works with two different types of databases. Use the pull down menu "Database type" to select between Sqlite and PostgreSQL:

With the first option, the user will need to input the name of the SQLite database file - including the absolute path - in the "SQLite filename:" field.

With the second option, the user will need to input all the necessary information to identify and connect to the PostgreSQL instance. These are:

• Database name: enter the name of the database, including the absolute path
• Hostname: enter the name of the machine where the PostgreSQL instance is running
• Port: enter the port to be used for the connection to the PostgreSQL instance. If empty, the default port number 5432 is used
• Username: enter the username to access the database
• Password: enter the password to access the database

With both options, the user can click on the "Verify database connection..." to have Nexus trying to connect with the database using the information provided.

It Nexus is able to establish a connection, a successful message will appear. If Nexus is not able to establish a connection, an error message will appear.

In the lower part of the GUI the user can enter as a string the SQL query to run against the database:

Note that there is no check in Nexus about the validity or what exactly the SQL query does. It is up to the user to make sure that the query syntax is correct.

Example

In this example we assume the user wants to get some information from an external Sqlite database as part of the data to use in the current report. The Sqlite database is stored in D:\tmp\report_db. The only information the user needs to input are the "Database type" and the location of the database. 

The result of the SQL query is reported as a table (all items are strings):

Being a table, the user can apply all the properties that tables support. 

The SQL query template will pass the table as an item to all its children templates. Note that his table item is not persistent in the Nexus database: it is not stored as an individual item. If you delete the SQL query template, the resulting table will disappear as well.