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:
For 11.8 and lower:
Here is a sample output that will be returned for a manually modified SQL 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:
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.
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 libraryThis 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" >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.