PostgreSQL JDBC driver adds an "@" symbol to an information link's auto-generated SQL query, causing the information link to fail with a syntax error.

Created
October 08, 2021 23:28

Date Posted: 2017-12-12
Product: TIBCO Spotfire®

Problem:

Solution:

When using PostgreSQL JDBC driver version 8.4.x to build information links in Spotfire, the information link's auto-generated SQL query will have the "@" symbol (also called the "at" sign) added into its FROM clause. The following is an example of such a SQL query:

SELECT *

FROM

"VeloDB"."processed"@"emp" n1

WHERE

Symptoms:
The presence of the "@" symbol will cause the information link to fail. When executed, the information link will display the following error:

InformationModelException at Spotfire.Dxp.Data:

Failed to execute query: ERROR: syntax error at or near "@".

Cause:
This issue is a known problem when using version 8.4 of the PostgreSQL JDBC driver.

There are two methods to address this behavior.

1). Manually modify the information link's default SQL query, by replacing the "@" (the "at sign") with "." (a period), so that the information link's modified SQL query, for the example shown above, looks like this:

SELECT *

FROM

"VeloDB"."processed"."emp" n1

WHERE

2). Use a later version of the PostgreSQL JDBC driver, such as version 9.x. PostgreSQL JDBC drivers can be downloaded from the following URL:

https://jdbc.postgresql.org/download.html