Data Sources: Validation Query

If you happen to receive the following error:

An error occurred while retrieving the data for the chart InsertChartName : The data source (ID = InsertDataSourceID) for this operation is currently unavailable. Contact your iDashboards System Administrator for assistance.

The issue could be that the data source has gone offline due to limited activity. A Data Source can go offline occasionally while the iDashboards server remains online. When a certain data source is not available a red exclamation mark appears right next to that data source in iDashboards Admin or Data Hub through Data Sources.

Validation Query

To correct this issue you will want to place a Validation Query in the Edit a Data Source window. Validation queries are used to assist iDashboards in making sure a connection to a data source is valid. It is defined in the Admin manual as:

If a Data Source is known to go offline occasionally, while the iDashboards server remains online, then a value for this property should be provided. It should be a short, simple SQL SELECT statement that is valid for the data source and will always return at least one row. Whenever a connection is fetched from the connection pool, this query will be run to determine if the connection is still active. If the query fails, the connection will be considered "dead" and removed from the pool. Because this query will be run often, it should be one that is known to run quickly to minimize the performance cost. Oracle databases often include a special system table named "dual" that is ideal for this purpose; in such cases, the statement "SELECT * FROM DUAL" may be used.  

You can define a validation query for a data source by logging into iDashboards Admin select Data Sources  then Edit the Data Source in question and define a query in the Validation Query box.  

This validation query will run whenever a connection is fetched from the connection pool to determine if a data source connection is still active. If the query fails, the connection will be considered dead and removed from the pool.  

Common Validation Queries:

MS SQL Server - Select GETDATE()

Oracle - SELECT * FROM DUAL

MySQL - SELECT CURDATE()

AS400/DB2 - SELECT current date FROM sysibm.sysdummy1

MS Access - SELECT Date()

Sybase - SELECT 1 or ​SELECT SYSDATE