Jump to content
We've recently updated our Privacy Statement, available here. ×

Need metadata query for datasource for Spotfire V14.0


Sirisha Kurella
Go to solution Solved by Peter McKinnis,

Recommended Posts

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:

image.png.bcbfcc9c479588a9ddb4c3a923699b6b.png

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

Link to comment
Share on other sites

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. 
 

Link to comment
Share on other sites

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.

image.png.35c1cbb89e5d0f7c6ed954d74a4390df.png

Link to comment
Share on other sites

  • Solution

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 

Link to comment
Share on other sites

@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))

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

 

image.thumb.png.477ab4922b9aba37fbda8ca0850cddf0.png

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

image.thumb.png.13e5dcde87f0dfdea331a1ac8f835aa0.png

Link to comment
Share on other sites

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,

image.png.fafdb70ab4d9e44a29137909797196e3.png

 

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

image.thumb.png.b50df55aabc8942fecdcbf3ce02200ba.png

Link to comment
Share on other sites

1 hour ago, Sirisha Kurella said:

Hi Scott,

Thanks for your response.

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

image.thumb.png.13e5dcde87f0dfdea331a1ac8f835aa0.png

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


 

Link to comment
Share on other sites

  • 2 weeks later...

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...