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 Ansys Dynamic Reporting 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:

Image

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.

Image

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

Image

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

Image

It Ansys Dynamic Reporting is able to establish a connection, a successful message will appear. If Ansys Dynamic Reporting 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:

Image

Note that there is no check in Ansys Dynamic Reporting 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.

Image

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

Image

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 Ansys Dynamic Reporting database: it is not stored as an individual item. If you delete the SQL query template, the resulting table will disappear as well.