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

Products	Versions
Spotfire Analyst	All Versions

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

 

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: 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.