How to get bookmark contents in TIBCO Spotfire

How to get bookmark contents in TIBCO Spotfire

Sometimes when applying an existing bookmark results in an error, there is a need to investigate the bookmark content to identify which column values/filter values/etc have changed over the time and which are causing this error. You can get the bookmark contents from TIBCO Spotfire application database. The following query helps to get the content for any bookmark by converting the blob column data to string. Prerequisite: The bookmark Id should be known. Update the query with the GUID for your desired bookmark.

SELECT ld.item_id, ld.content_type, CONVERT(varchar(max), ld.data, 0) as bookmarkdata FROM lib_data ld WHERE item_id= <bookmark id>

If you need the list of bookmarks in a dxp (including their GUIDs) in addition to the bookmark contents, you can combine the above query and one seen in this article: List all bookmarks in the Spotfire database for a specific analysis

The example GUID provided below is for the specific analysis from which you need to get bookmarks data. Update the query with the GUID for your desired analysis file.

SQL Server:

SELECT  ld.item_id, ld.content_type,CONVERT(varchar(max), ld.data, 0) as bookmarkdata FROM spotfire_db103.lib_data ld 
WHERE item_id in (
SELECT  item_id FROM   spotfire_db103.lib_items li   INNER JOIN   spotfire_db103.lib_item_types lit   ON li.item_type=lit.type_id 
WHERE  li.parent_id=(
SELECT li.item_id FROM spotfire_db103.lib_items li INNER JOIN spotfire_db103.lib_item_types lit  ON li.item_type=lit.type_id
WHERE  li.parent_id='5b0667bb-23a5-4bcf-89e0-8d3d42166a62' and li.title='Bookmarks'
))

Oracle:
Note: Oracle has a limitation of only showing the first 2000 characters from blob conversion. If you need to see complete data, you may need to check on other tricks to convert blob data to string from Oracle Database.

SELECT ld.item_id, ld.content_type,utl_raw.cast_to_varchar2(dbms_lob.substr(ld.data,2000,1)) bookmarkdata FROM spotfire_db103.lib_data ld 
WHERE item_id in (
SELECT  item_id FROM   spotfire_db103.lib_items li   INNER JOIN   spotfire_db103.lib_item_types lit   ON li.item_type=lit.type_id 
WHERE  li.parent_id=(
SELECT li.item_id FROM spotfire_db103.lib_items li INNER JOIN spotfire_db103.lib_item_types lit  ON li.item_type=lit.type_id
WHERE  li.parent_id='5b0667bb-23a5-4bcf-89e0-8d3d42166a62' and li.title='Bookmarks'
))

Once you get results in SQL Server Management Studio or SQL Developer, export/save the results to .txt format.


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.
  KB: 000028817 List all bookmarks in the Spotfire database for a specific analysis

• https://support.tibco.com/s/article/List-all-bookmarks-in-the-Spotfire-database-for-a-specific-analysis

Doc: Bookmarks

• https://docs.tibco.com/pub/sfire-analyst/10.3.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/bm/bm_what_are_bookmarks.htm