[SOLVED] Execute a Library Report within a FEX

Hi Nick,

could you please be more specific what you mean by "executing a saved library report".

I guess you mean the following:

You have lets say a HTML report stored as a BLOP in the report library, and you would now like to include this one into the fex'es output? Is that right?

For the a.m. task I could up with one solution which would should work with text data reports (like HTML).

If you know some ID of the report in the library a-priori (which I guess you do, you could not include it otherwise) - you could extract the blob from the report library db into the a alpha hold file (in WF TEMPDIR) and then just include it using

 
-HTMLFORM BEGIN
!IBI.FIL.MYHOLD;
-HTMLFORM END

Have not tried this myself but I believe it should work.

However, for binary files (like PDFs or Excels) this solution is likely not to work. You might need to use external software here to "embed" a library's report.

Cheers Linne

WebFOCUS 7.7.03

Posts: 67 | Registered: January 05, 2011

Ignored post by linnex posted

Silver Member

posted

Thank you for your help...

quote:
could you please be more specific what you mean by "executing a saved library report".

I would like to retrieve the report from the library, and display to the user.

quote:
you could extract the blob from the report library db into the a alpha hold file (in WF TEMPDIR) and then just include it using

That is a very interesting idea. Any idea how to extract the BLOB from the database? For instance which synonym file to I connect to?

With this information I will also continue my research.

Thanks again.

Posts: 37 | Registered: January 11, 2008

Ignored post by Ublguy posted

Silver Member

posted

Looks like I need to try sql pass thru to retrieve it.

I will give that a shot.

Posts: 37 | Registered: January 11, 2008

Ignored post by Ublguy posted

Silver Member

posted

I am not having any luck extracting the report from the blob...any tips?

Posts: 37 | Registered: January 11, 2008

Ignored post by Ublguy posted

linnex

Gold member

posted

Hi Nick,

at what point do you currently stuck.

Is it the actual selection of a blob or the problem of getting a join / select etc. inside the RCaster databases.

Could you also tell us on which platform you wfocus database runs on. Ora, MS-Sql?

Hopefully, we can find a solution then.

Linne

WebFOCUS 7.7.03

Posts: 67 | Registered: January 05, 2011

Ignored post by linnex posted

linnex

Gold member

posted

Hi again,

I tried something and this might be able to help you:

I am using an ORA 11g Database with a test table containing a blob column called bdata (one row only)

WebFOCUS code:

ENGINE SQLORA SET DEFAULT_CONNECTION MyDB
SQL SQLORA
 select btoc(bdata) "mytext" from TEST_BLOB_LOAD;
TABLE FILE SQLOUT
PRINT 
     mytext
ON TABLE HOLD AS HTMP FORMAT ALPHA
END
-RUN
-HTMLFORM BEGIN
!IBI.FIL.HTMP;
-HTMLFORM END

You also require the conversion function from BLOB to CLOB (I called BTOC):

found here

  
create or replace function BTOC(B BLOB) 
return clob is 
  c clob;
  n number;
begin 
  if (b is null) then 
    return null;
  end if;
  if (length(b)=0) then
    return empty_clob(); 
  end if;
  dbms_lob.createtemporary(c,true);
  n:=1;
  while (n+32767<=length(b)) loop
    dbms_lob.writeappend(c,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(b,32767,n)));
    n:=n+32767;
  end loop;
  dbms_lob.writeappend(c,length(b)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b)-n+1,n)));
  return c;
end;
/

My result was the HTML page contained in the BLOB. Unfortunately I also got 2 extra undesired characters at the end of the page - I do not yet know where they come from.

Yet I hope this helps.

Linne

WebFOCUS 7.7.03

Posts: 67 | Registered: January 05, 2011

Ignored post by linnex posted

Silver Member

posted

Linne, thank you very much for the very detailed example. I bet it will help many people including myself.

thank you again.

Posts: 37 | Registered: January 11, 2008

Ignored post by Ublguy posted