Unable to CREATE or DROP temporary tables in SAP HANA when using Spotfire information links – Revvity Signals

Date Posted: 2019-05-09
Product: TIBCO Spotfire®

Problem:

Unable to CREATE or DROP temporary tables in SAP HANA when using Spotfire information links

Solution:

Sometimes with specific database types (e.g., SAP HANA) you will not be able to open an information link and the following errors will be seen in the Spotfire Server logs.

ERROR 2015-02-25T11:25:35,279-0600 [*pool-3-thread-5, ENT\xyz, #4661*] ds.sql.SQLQuerySession: Error running DROP TABLE SFTMP88513545014

com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [259] (at 11): invalid table name: SFTMP88513545014: line 1 col 12 (at pos 11)

at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:334)

at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateDatabaseException(SQLExceptionSapDB.java:165)

--------------------------------------------------------------------------------------------

ERROR 2015-03-18T13:12:40,158-0500 [*pool-3-thread-32, ENT\xyz, #26298*] ws.dat.AbstractOperator: Start job failed.

com.spotfire.ws.dat.OperatorException: com.spotfire.ws.im.IMException: Failed to execute query: SAP DBTech JDBC: [257] (at 8): sql syntax error: incorrect syntax near "TEMPORARY": line 1 col 8 (at pos 8)

Cause:

The error is due to the fact that the temporary table has not been created because of incorrect syntax. As a result, it complains about a non-existent table while it tries to drop it. A temporary table is used when executing an SQL query, where the total size of a condition list is larger than a particular threshold value. The default threshold is 10000.

To resolve the issue follow the steps below.

1. Open the configuration tool for the installed TIBCO Spotfire Server.
2. Enter the password when prompted.
3. Go to the configuration tab.
4. Choose Data-Source Templates.
5. Mark the SAP HANA template so that you can see its contents.
6. Edit the data source template XML with the following tag:

create-temp-table-command

The create-temp-table-command are the SQL commands for creating a temporary table. This is used to store filter values (when more than condition?list?threshold) and to store result from sub-queries. The syntax may vary between databases. $name$ is a placeholder for the table name. $column_list$ is a placeholder for a column list on the format (name type, name type, ...).

Default:

CREATE TEMPORARY TABLE $name$ $column_list$

Change this XML to what is expected by the specific database.

Example for SAP HANA: Since SAP HANA use local temporary table instead of a normal table, following lines are required.

<temp-table-name-pattern>#$$name$$</temp-table-name-pattern>
<create-temp-table-command>CREATE LOCAL TEMPORARY TABLE $$name$$ $$column_list$$;</create-temp-table-command>

Note: Users authenticating to the Data Source need the rights to create the temporary tables.

7. Save configuration to a database.
8. Restart the TIBCO Spotfire Server.

This will resolve the issues on any new data source elements created using this template. To ensure that already existing data source elements are updated, you need to go into Information Designer and edit and re-save all existing data source elements that uses this template.

Related articles