Need metadata query for datasource for Spotfire V14.0

[Sirisha Kurella]

Hi,

We found the below Spotfire meta data queries article for older versions of Spotfire where we have datasources related query.

https://community.spotfire.com/articles/spotfire/spotfire-metadata-queries2/#wiki-header-3

I was used the same query which is available in this article in the Spotfire V14.0 to get the data source information from DB. But I am getting the below error while trying to retrieve the information from the created view in the new db.

Error:

Please help me here and share the query for V14.0 if have.

[Jo Onraet]

Hi Sirisha,

I'm wondering if you get the same error message in case you put a blank in between * and from (instead of *from).

SELECT * FROM SPF_DATA_SOURCES_V1

[Scott Sutton 2]

We are seeing the same errors -

SELECT
   L1."ITEM_ID" AS "ELEMENTID",   

case when L3."LABEL" = 'column' then    xmlcast(XMLQuery('for $i in /column/source-column[1]  return $i/schema/text()' PASSING BY VALUE XMLTYPE(L2."DATA",871) RETURNING CONTENT) as varchar2(100))
        when  L3."LABEL" = 'join' then xmlcast(XMLQuery('for $i in /join/source-column[1]  return $i/schema/text()' PASSING BY VALUE XMLTYPE(L2."DATA",871) RETURNING CONTENT) as varchar2(100))
        when  L3."LABEL" = 'filter' then xmlcast(XMLQuery('for $i in /filter/source-column[1]  return $i/schema/text()' PASSING BY VALUE XMLTYPE(L2."DATA",871) RETURNING CONTENT) as varchar2(100))
   end  AS "COL01"

from 

FROM
   "SPOTFIRE"."LIB_CURRENT_ITEMS" L1,
   "SPOTFIRE"."LIB_DATA" L2,
   "SPOTFIRE"."LIB_ITEM_TYPES" L3
WHERE
   (L3."TYPE_ID" = L1."ITEM_TYPE")
   AND (L1."ITEM_ID" = L2."DATA_ID")
   AND (L3."DISPLAY_NAME" IN ('column',
   'filter',
   'join'))

InformationModelServiceException at Spotfire.Dxp.Services:
Failed to execute query: ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 1
LPX-00217: invalid character 31 (U+001F)
ORA-06512: at "SYS.XMLTYPE", line 283
ORA-06512: at line 1

https://docs.oracle.com/error-help/db/ora-31011/ (HRESULT: 80131509)

 

Looks like something changed in the XML Type - have not found a good solution yet. 
 

[Sirisha Kurella]

14 hours ago, Jo Onraet said:

Hi Sirisha,

I'm wondering if you get the same error message in case you put a blank in between * and from (instead of *from).

SELECT * FROM SPF_DATA_SOURCES_V1

Hi Jo Onraet,

Yeah i am getting the same error in case i put a blank between * and from.

[Peter McKinnis]

Sirisha,

The error is probably because the library data is compressed since Spotfire 12.0, such that the LIB_DATA needs to be decompressed before the XML can be read.  I don't know the Oracle command to unzip the Lib_data column, but before the column can be treated as XML in the query it will need to be decompressed.

Regards,

Peter 

[Scott Sutton 2]

@Peter McKinnis @Sirisha Kurella try the below - seems to be working for me. may have to update a bit of syntax. also you may need to do the second option if you have lots of aliases and the data is more than 2000 characters
 

dbms_lob.substr(utl_compress.lz_uncompress(L2.DATA),2000,1))

dbms_lob.substr(utl_compress.lz_uncompress(L2.DATA),2000,1)) || dbms_lob.substr(utl_compress.lz_uncompress(L2.DATA),2000,2001))

[Sirisha Kurella]

On 4/19/2024 at 8:04 PM, Peter McKinnis said:

I don't know the Oracle command to unzip the Lib_data column, but before the column can be treated as XML in the query it will need to be decompressed.

Hi Peter,

Thanks for your reply,

Do we need to decompress the table from Oracle db end from DB team or can we do that from sql developer tool. 

Also, please let us know if we decompress the table now, the existing data available in the table will get decompressed or only new data?

[Sirisha Kurella]

On 4/19/2024 at 9:14 PM, Scott Sutton 2 said:

try the below - seems to be working for me. may have to update a bit of syntax. also you may need to do the second option if you have lots of aliases and the data is more than 2000 characters
 

dbms_lob.substr(utl_compress.lz_uncompress(L2.DATA),2000,1))

dbms_lob.substr(utl_compress.lz_uncompress(L2.DATA),2000,1)) || dbms_lob.substr(utl_compress.lz_uncompress(L2.DATA),2000,2001))

Hi Scott,

Thanks for the query,

I am getting the below error when i am running this query. Please check.

 

[Scott Sutton 2]

Maybe we're not seeing the full command, but i think it would need to be setup like below - if that doesn't work, it may not like the concatenate using two bars (||) so try without that

Select
DATA_ID as ID,
dbms_lob.substr(utl_compress.lz_uncompress(DATA),2000,1)) || dbms_lob.substr(utl_compress.lz_uncompress(DATA),2000,2001)) as data_string
from LIB_DATA

 

[Sirisha Kurella]

On 4/24/2024 at 3:27 PM, Scott Sutton 2 said:

Maybe we're not seeing the full command, but i think it would need to be setup like below - if that doesn't work, it may not like the concatenate using two bars (||) so try without that

Select
DATA_ID as ID,
dbms_lob.substr(utl_compress.lz_uncompress(DATA),2000,1)) || dbms_lob.substr(utl_compress.lz_uncompress(DATA),2000,2001)) as data_string
from LIB_DATA

 

Hi Scott,

Thanks for your response.

Now i am getting the below error. can you please check once.

[Sirisha Kurella]

On 4/19/2024 at 8:04 PM, Peter McKinnis said:

Sirisha,

The error is probably because the library data is compressed since Spotfire 12.0, such that the LIB_DATA needs to be decompressed before the XML can be read.  I don't know the Oracle command to unzip the Lib_data column, but before the column can be treated as XML in the query it will need to be decompressed.

Regards,

Peter 

Hi Peter,

we have decompressed the library through command prompt of Spotfire server as below,

 

But still we are getting the same error as below, could you please check and help here.

[Scott Sutton 2]

1 hour ago, Sirisha Kurella said:

Hi Scott,

Thanks for your response.

Now i am getting the below error. can you please check once.

As I mentioned earlier, Try removing the concatenation. I think you have a slightly different setup than we do so you'll probably have to figure out the rest on your own - hoping this gets you on the right track. 

Select
DATA_ID as ID,
dbms_lob.substr(utl_compress.lz_uncompress(DATA),2000,1)) as data_string1,
dbms_lob.substr(utl_compress.lz_uncompress(DATA),2000,2001)) as data_string2
from LIB_DATA

 

[Peter McKinnis]

Sirisha,

@Sirisha Kurella You may have already figured this out, but the compression and decompression of the Spotfire Library occurs over time.  This means that changing the setting on the server to not compress the items does not immediately decompress all the items in the Library.  As mentioned in the Spotfire Library introduction, "If you disable library compression, then compressed items in the library are uncompressed in the background over time."  Basically, it will take time for the LIB_Data column to decompress. 

I also think select statement mentioned in earlier posts is not syntactically correct.   There are too many closing parentheses.  Only one closing parenthesis is needed after the 1 and 2001.  I am also not sure why you need to concatenate.  I would read the entire column in order to get the entire XML.

Select DATA_ID as ID, dbms_lob.substr(utl_compress.lz_uncompress(DATA),2000,1) || dbms_lob.substr(utl_compress.lz_uncompress(DATA),2000,2001) as data_string from LIB_DATA

Regards,

Peter

[Scott Sutton 2]

23 hours ago, Peter McKinnis said:

There are too many closing parentheses.  Only one closing parenthesis is needed after the 1 and 2001. 

Good catch - you are correct. thanks for clarification. 

 

[Sirisha Kurella]

Thank you so much @Peter McKinnisand @Scott Sutton 2 for the support and resolution
The issue got resolved after decompress the table on server.

Edited May 17 by Sirisha Kurella

[marcojanson11]

On 4/25/2024 at 9:20 PM, Sirisha Kurella said:

Hi Peter,

we have decompressed the library through command prompt of Spotfire server as below,

 

But still we are getting the same error as below, could you please check and help here.

Thanks for share good information.