0

I have a query that is related to this topic: https://developer.jboss.org/thread/277610

Prior to reaching the comma separated values stage, the values are actually stored as a blob. There is a function fetchBlobtoString(Blob, string, VARIADIC start_end integer) returns String that actually takes the blob input and then converts to comma separated values as seen on the post.

The issue with this is string is limited to 4000 characters, hence it will decimate the data and not all values show up. What would be the best way to extract the values that are double and convert it to rows similar to the post.

Would converting it in to an object instead of string improve performance using following function as an example:

fetchElementValueFromBlob(protobufBlob Blob, origName string) returns object

I have tried iterating items in blob using getItem function, add to temp table, but its slow and I get following error If i go more that 15-20 iterations:

Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 petrelDS: TEIID60000 javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/petrelDS SQLState: 50000 ErrorCode: 30504

BEGIN
  DECLARE integer VARIABLES.counter = 0;
  DECLARE integer VARIABLES.pts = 100;
  WHILE (VARIABLES.counter < VARIABLES.pts)
   BEGIN
    select wellbore_uwi,getItem(fetchBlob(data, 'md'),VARIABLES.counter) INTO TEMP from DirectionalSurvey where wellbore_uwi='1234567890'; 
    VARIABLES.counter = (VARIABLES.counter + 1);
   END
SELECT TEMP.wb_uwi,TEMP.depth  FROM TEMP;
END

If I remove the getItem() function, the error goes away.

ASAFDAR
  • 19
  • 1
  • 7
  • looks like you exhausting the connections, thus the error after 15 in getItem. So you are reading one value each time and in while loop and adding each one to temp table? Why not convert the blob to clob then use TextTable in single scoop? – Ramesh Reddy Mar 17 '20 at 04:00
  • I got the following error when trying to convert the blob to clob: Remote org.teiid.api.exception.query.QueryResolverException: TEIID30071 The conversion from blob to clob is not allowed. – ASAFDAR Mar 17 '20 at 04:18
  • What would be the appropriate encoding in this case. Using UTF-8 encoding I get following error: Error: TEIID30179 Text parse error: Could not read data in Unknown – ASAFDAR Mar 17 '20 at 05:55
  • Can you read as CLOB as begin with? Since BLOB is considered binary there will be no implicit conversion, I would have to check if even `convert(myblob, CLOB)` works as in the explicit case. Encoding depends on your data. If it is String yes you can use UTF-8. I say print out content to make sure first as in debug step then if that looks right then feed to TextTable – Ramesh Reddy Mar 17 '20 at 12:33
  • SOLVED: Converted Blob to Object to Object Array and then used exec arrayiterate function: fetchElementValueFromBlob: Function that fetches the complete array object for the given element in the given blob `select ds.wellbore_uwi,depth.* from DirectionalSurvey as ds, TABLE (exec arrayiterate(CONVERT(fetchElementValueFromBlob(ds.data, 'md'),object[])))as depth;` – ASAFDAR Mar 17 '20 at 13:23
  • I ran in to another last issue, how can I keep the array values in sync. I am getting like this: Well-01 survey_01 6 10 0 0 Well-01 survey_01 6 10 0 0.96 It seems each column is repeating when doing a cross join. the double values should not repeat. `select ds.wellbore_uwi,depthTable.*,inclinationTable.* from DirectionalSurvey as ds cross join (TABLE (exec arrayiterate(CONVERT(fetchElementValueFromBlob(ds.data, 'md'),object[]))) as depthTable cross join TABLE (exec arrayiterate(CONVERT(fetchElementValueFromBlob(ds.data, 'inclination'),object[])))as inclinationTable) ;` – ASAFDAR Mar 17 '20 at 16:53
  • You mean the second cross join is duplicating the data? change the second cross join to left outer join and see if that works for you. – Ramesh Reddy Mar 17 '20 at 20:24
  • Second Cross join is making second column duplicate values. For left outer join I would need to join on a primary key or common column between the two. there is no common column between the two. in the TEXTTABLE there is option "id for ordinality" and join the two on that. but cross joining two tables without id seems difficult – ASAFDAR Mar 17 '20 at 20:34
  • Left Join on row number will work I believe – ASAFDAR Mar 17 '20 at 23:46
  • Try `join on 1 = 1` that should pass the SQL parsing – Ramesh Reddy Mar 18 '20 at 16:04
  • @RameshReddy Thanks for your response. Unfortunately that did not work. I only the use of row_number() here. Which is related to my other question that I have posted: https://stackoverflow.com/questions/60741244/assigning-row-number-without-any-order – ASAFDAR Mar 18 '20 at 18:19

0 Answers0