Non-recognized Ping command sent to TIBCO Spotfire Advanced Data Services (ADS), which returns the error: 'Incorrect syntax near "SELECT", found "1".' – Revvity Signals

Date Posted: 2018-01-10
Product: TIBCO Spotfire®

Problem:

Non-recognized Ping command sent to TIBCO Spotfire Advanced Data Services (ADS), which returns the error: 'Incorrect syntax near "SELECT", found "1".'

Solution:

Description:
TIBCO Spotfire Server sends a default Ping command that is not recognized by TIBCO Spotfire Advanced Data Services, causing it to fail. The error message logged to the Spotfire Server server.log file is 'Incorrect syntax near "SELECT", found "1".'

Symptoms:
A Spotfire Information Services data source (for use with information links) that connects to TIBCO Spotfire Advanced Data Services (ADS) via JDBC does not work as expected. Looking into Spotfire Server's "server.log" file, in debug mode, shows an error similar to the one shown below:

DEBUG 2014-09-15T17:22:22,822+0800 [username, #19812] ds.sql.JDBCDataSourceManager: Retrieving a connection from a connection pool ADS
DEBUG 2014-09-15T17:22:22,822+0800 [username, #19812] util.sql.PoolingDataSource: Stale connection: failure executing the ping query
java.sql.SQLException: An exception occurred when executing the following query: "SELECT 1". Cause: Unable to parse query text: Incorrect syntax near "SELECT", found "1". On line 1, column 8.
    [parser-2904201]
Cause: Incorrect syntax near "SELECT", found "1"
com.compositesw.cdms.services.parser.ParserException: Unable to parse query text: Incorrect syntax near "SELECT", found "1". On line 1, column 8.
    [parser-2904201]
   at com.compositesw.cdms.services.parser.ParserImpl.parseSql(ParserImpl.java:172)
   at com.compositesw.cdms.services.parser.ParserImpl.parseSql(ParserImpl.java:120)
   at com.compositesw.server.qe.QueryEngine.a(SourceFile:1070)
   at com.compositesw.server.qe.QueryEngine.a(SourceFile:183)
   at com.compositesw.server.qe.QueryEngine.execute(SourceFile:142)
   at com.compositesw.server.qe.QESqlRequest.execute(SourceFile:144)
   at com.compositesw.server.request.HookSqlRequest.execute(HookSqlRequest.java:64)
   at com.compositesw.server.dbchannel.CsExec.exec(CsExec.java:122)
   at com.compositesw.server.dbchannel.CsExec.exec(CsExec.java:74)
   at com.compositesw.server.dbchannel.ServerCommand.exec(ServerCommand.java:644)
   at com.compositesw.server.dbchannel.ServerCommand.dispatchCommand(ServerCommand.java:166)
   at com.compositesw.server.dbchannel.ServerCommand.run(ServerCommand.java:82)
   at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
   at java.lang.Thread.run(Thread.java:662)
Caused by: line 1:8: Incorrect syntax near "SELECT", found "1"
   at com.compositesw.cdms.services.parser.SqlParser.createException(SqlParser.java:77)
   at com.compositesw.cdms.services.parser.SqlParser.createException(SqlParser.java:52)
   at com.compositesw.cdms.services.parser.SqlParser.query_specification(SqlParser.java:2654)
   at com.compositesw.cdms.services.parser.SqlParser.simple_table(SqlParser.java:2512)
   at com.compositesw.cdms.services.parser.SqlParser.non_join_query_primary(SqlParser.java:2476)
   at com.compositesw.cdms.services.parser.SqlParser.non_join_query_term(SqlParser.java:2327)
   at com.compositesw.cdms.services.parser.SqlParser.non_join_query_expression(SqlParser.java:1785)
   at com.compositesw.cdms.services.parser.SqlParser.query_expression(SqlParser.java:1248)
   at com.compositesw.cdms.services.parser.SqlParser.direct_select_statement_multiple_rows(SqlParser.java:854)
   at com.compositesw.cdms.services.parser.SqlParser.direct_sql_data_statement(SqlParser.java:773)
   at com.compositesw.cdms.services.parser.SqlParser.directly_executable_statement(SqlParser.java:261)
   at com.compositesw.cdms.services.parser.SqlParser.start_rule(SqlParser.java:194)
   at com.compositesw.cdms.services.parser.ParserImpl.parseSql(ParserImpl.java:159)
   ... 14 more

Cause:
This issue is caused by the TIBCO Spotfire Server sending the default PING command to the database server when it creates a connection. This will happen unless anything else is specified in the Spotfire Information Services data source template for that type of database. To resolve this issue, there are two steps required:

Update the data source template to specify the correct Ping syntax.
Update the data source element to ensure that it uses the correct syntax specified in the data source template.

Updating the data source template

Note: These steps walk through the process of updating the data source template in TIBCO Spotfire Server 5.0 and later.

Open the configuration tool by going to the Start Menu, choosing TIBCO Spotfire Server X.X, and pressing 'Configure TIBCO Spotfire Server'.
Enter the Tool password when prompted.
Go to the tab labeled "Configurations", and choose "Data source templates".
Here, mark the one labeled "TIBCO Spotfire Advanced Data Services", and you should see that you are able to edit its contents.
It can also be named "TIBCO Spotfire Application Data Services", which is an older name for ADS.
Inside the tag for </jdbc-type-settings> add <ping-command>SELECT 1 FROM /services/databases/system/all_domains</ping-command>.

Example:

<ping-command>SELECT 1 FROM /services/databases/system/all_domains</ping-command>
</jdbc-type-settings>
Press "Save configuration..." and choose to save it in the database.
Write a comment describing your changes, and execute the save.
Restart TIBCO Spotfire Server as a Windows service or Linux service.

Updating the data source element

Log into this Spotfire Server instance from the TIBCO Spotfire Analyst/Professional desktop client.
Select "Tools > Information Designer" from the main menu.
Locate your affected data source element, and edit it.
Change its "max number of connections" parameter to any amount, then change it back.
Press "Save".

This should now be using the updated data source template, and the PING command should execute correctly.