How to find the manually modified SQL query used by a particular Information Link in TIBCO Spotfire

Product:TIBCO Spotfire Server
Versions:All

Summary:
This article helps you determine the manually modified SQL query used by an Information Link by querying the Spotfire application database.

Details:
When creating an Information Link in the Information Designer, the actual SQL query is automatically generated by Spotfire based on the included columns, filters, joins, etc. There is also the option to manually edit the SQL using the "SQL..." button. See Editing the SQL of an Information Link for more details.  This article demonstrates a way to query the TIBCO Spotfire Server application database for the custom SQL used for a particular Information Link. This can be useful as a way to programmatically extract the SQL query for Information Links in the library without manually opening each Information Link in the Information Designer.
 

Resolution:
Here is an example SQL query (for Microsoft SQL Server) that can be executed against the TIBCO Spotfire Server database to get the details of what manually modified query an Information Link is using:

For Spotfire versions 12.0 and higher:

SELECT 
    libitems.ITEM_ID,
    libitems.TITLE,
    itemtype.LABEL,
    CONVERT(varchar(max), DECOMPRESS(data.DATA), 0) as ildata
FROM [LIB_ITEMS] libitems
JOIN [LIB_ITEM_TYPES] itemtype on libitems.ITEM_TYPE=itemtype.[TYPE_ID]
JOIN [LIB_DATA] data on libitems.DATA_ID=data.DATA_ID
WHERE libitems.ITEM_ID='94134318-1116-48f0-a250-b74e41a68f1c' -- Update with the GUID of the desired Information Link from your library

This assumes library compression is enabled, which is the default. If compression is disabled, then the DECOMPRESS() function can be removed as it is not required.

For 11.8 and lower:

SELECT 
    libitems.ITEM_ID,
    libitems.TITLE,itemtype.LABEL, 
    CONVERT(varchar(max), data.DATA, 0) as ildata
FROM [LIB_ITEMS] libitems
join [LIB_ITEM_TYPES] itemtype
on libitems.ITEM_TYPE=itemtype.[TYPE_ID]
join [LIB_DATA] data 
on libitems.ITEM_ID=data.ITEM_ID
where libitems.ITEM_ID='94134318-1116-48f0-a250-b74e41a68f1c' -- Update with the GUID of the desired Information Link from your library

Here is a sample output that will be returned for a manually modified SQL query:

<query><elements><column-spec><column guid="ba8a86d1-0359-47eb-9a0d-ea59ed448eb8" /><properties><property><key>Spotfire.Group</key><value>0</value></property></properties></column-spec><column-spec><column guid="efa14163-942b-4373-858c-d365a3f572ec" /><properties><property><key>Spotfire.Group</key><value>0</value></property></properties></column-spec></elements><user-modified-queries><user-modified-query xmlns:im="http://schemas.spotfire.com/ws/2004/12/im.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="im:SQLQuery" data-source="7d9daad8-d08f-468b-8a08-4bdcb67f424d"><sql>SELECT
   B1."ASSISTS" AS "ASSISTS",
   B1."AT_BATS" AS "ATBATS"
FROM
   "spotfire_demodata"."dbo"."BASEBALL" B1
WHERE
    B1."ASSISTS" &gt;127
</sql></user-modified-query></user-modified-queries></query>

Please note that if the Information Link uses the default SQL (the SQL query has not been manually modified) then it will not be possible to get the exact query from the database since that query is programmatically generated and not explicitly stored in the database. This method will only work if the default SQL is modified. Here is an example result of the above query for an Information Link which has not had its SQL manually modified and is still using the default query:

<query><elements><column-spec><column guid="1a3bceca-4dfa-4dc9-ae46-0e5d65f7502a" /><properties><property><key>Spotfire.Group</key><value>0</value></property></properties></column-spec><column-spec><column guid="40239874-38dc-44be-b67a-5512691869e6" /><properties><property><key>Spotfire.Group</key><value>0</value></property></properties></column-spec><column-spec><column guid="0eebec1f-b5c8-48fc-8b2c-e204c22a6328" /><properties><property><key>Spotfire.Group</key><value>0</value></property></properties></column-spec></elements></query>

Disclaimer: The content of this article is for informational purposes only. The subject material may change in any new versions with no notice and there is no responsibility by TIBCO to maintain or support future access to this internal application content. Modification of any internal application content is not recommended and can lead to an unsupported configuration.  It is not intended to be used "As Is" in a Production environment. Always test in a Development environment.