This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 13, 2012 1:32 AM by 623708 RSS

Show image on report

garcol Newbie
Currently Being Moderated
Hello,

I am struggling with showing images on my reports.

I have table CLUBS with some columns and a blob column names 'AFBEELDING'. I have a report and a form on this table, works fine, I can upload an image and it is displayed on my report.

Now I want to display images on my page SPELERS.
This is the query:
select slr.*
, dbms_lob.getlength(clb.AFBEELDING) AFBEELDING
,  '<img src="my_image_display?p_clb_id='|| NVL (clb.id, 0)|| '" height="'|| 20|| '" />' afbeelding2
from CLM.VEX_SPELERS SLR
, CLM.VEX_TYPEN_SPELER TSR
, clm.vex_clubs clb
where slr.tsr_code = tsr.code
and clb.id = slr.clb_id  
This statement is used on the page that works, so I have put it in this query also
, dbms_lob.getlength(clb.AFBEELDING) AFBEELDING
This statement was an example on this forum (I created a procedure my_image_display):
,  '<img src="my_image_display?p_clb_id='|| NVL (clb.id, 0)|| '" height="'|| 20|| '" />' afbeelding2
Procedure my_image_display:
create or replace procedure "MY_IMAGE_DISPLAY"
(p_clb_id IN vex_clubs.id%type)
is
--   l_mime        VARCHAR2(255);
   l_length      NUMBER;
   l_file_name   VARCHAR2 (2000);
   lob_loc       BLOB;
BEGIN
 
         SELECT clb.afbeelding
         , DBMS_LOB.getlength (clb.afbeelding )
         INTO lob_loc, l_length
         FROM vex_clubs clb
         WHERE id = p_clb_id;
 
--   OWA_UTIL.mime_header (NVL (l_mime, 'application/octet'), FALSE);
   HTP.p ('Content-length: ' || l_length);
   OWA_UTIL.http_header_close;
   WPG_DOCLOAD.download_file (lob_loc);
    
END my_image_display;
In both cases, it is not working.

2 Questions:
- Why is it working on the CLUBS page with dmbs_lob.getlength
- How can I get the images displayed....
  • 1. Re: Show image on report
    fac586 Guru
    Currently Being Moderated
    garcol wrote:

    In both cases, it is not working.

    2 Questions:
    - Why is it working on the CLUBS page with dmbs_lob.getlength
    - How can I get the images displayed....
    <tt>dbms_lob.getlength</tt> in the report query is used in conjunction with an image format mask in the report column attributes.

    This is described in the documentation: About BLOB Support in Forms and Reports and Display Image. There's an OBE tutorial that followed the introduction of declarative BLOB support in 3.1 as well.
  • 2. Re: Show image on report
    garcol Newbie
    Currently Being Moderated
    Thanks, I have taken a look at them, and some other sources also, but I can't just figure out how it works.

    Actually the only thing I want is to display an image that is stored in the CLUBS-table.
    What do I have to put in my select-query to show the image?
  • 3. Re: Show image on report
    fac586 Guru
    Currently Being Moderated
    garcol wrote:
    Thanks, I have taken a look at them, and some other sources also, but I can't just figure out how it works.

    Actually the only thing I want is to display an image that is stored in the CLUBS-table.
    What do I have to put in my select-query to show the image?
    Look at the report where it does work. In the Column Definition for the AFBEELDING image column the Number/Date Format attribute will be set to something like:
    IMAGE:EMP:RESUME:EMPNO::RESUME_MIMETYPE:RESUME_FILENAME:RESUME_LAST_UPDATE::attachment:Resume
    The AFBEELDING column (based on <tt>dbms_lob.getlength(clb.AFBEELDING)</tt>) in the new report needs to have the same format mask (it is displaying the same image). Copy and paste the format mask from the column in the working report to the column in the new report.

    The BLOB Download Format Mask quick pick link below the Number/Date Format attribute pops up a form for creating/editing these BLOB download format masks.
  • 4. Re: Show image on report
    garcol Newbie
    Currently Being Moderated
    Thanks again, I have already done that.

    A screenshot:

    http://img145.imageshack.us/img145/3833/afbeeldingfield.jpg

    Could the join be a problem? In this case I show a list of players, with their club...
    select dbms_lob.getlength(c.AFBEELDING) AFBEELDING
    from PLAYERS p
    , TYPE_PLAYERS t
    , CLUBS c
    where p.tsr_code = t.code
    and c.id               = p.clb_id  
    The result is this 'broken image'- image...

    Edited by: garcol on 12-nov-2012 1:23
  • 5. Re: Show image on report
    fac586 Guru
    Currently Being Moderated
    garcol wrote:
    Thanks again, I have already done that.

    A screenshot:

    http://img145.imageshack.us/img145/3833/afbeeldingfield.jpg

    Could the join be a problem? In this case I show a list of players, with their club...
    No.
    select dbms_lob.getlength(c.AFBEELDING) AFBEELDING
    from PLAYERS p
    , TYPE_PLAYERS t
    , CLUBS c
    where p.tsr_code = t.code
    and c.id               = p.clb_id  
    The result is this 'broken image'- image...
    Change the Content Disposition attribute to Inline.

    I'd also strongly recommend that you add MIME Type and Filename columns to your tables and use them in the application.
  • 6. Re: Show image on report
    garcol Newbie
    Currently Being Moderated
    >
    Change the Content Disposition attribute to Inline.
    >

    Done. But it does not make a difference... I still see the 'broken-image'-image only...

    >
    I'd also strongly recommend that you add MIME Type and Filename columns to your tables and use them in the application.
    >

    Thanks for the recommendation, added MIMETYPE and TYPETYPE to my table, they are now saved when uploading an image to the CLUBS table. I have added them in Blob Column Attributes (see screenshots). It works on the page that shows the images correct, but not on the page were I am working on now...

    New screenshot:
    http://img259.imageshack.us/img259/481/afbeeldingfield2.jpg

    Edited by: garcol on 12-nov-2012 3:16
  • 7. Re: Show image on report
    fac586 Guru
    Currently Being Moderated
    garcol wrote:
    >
    Change the Content Disposition attribute to Inline.
    >

    Done. But it does not make a difference... I still see the 'broken-image'-image only...

    >
    I'd also strongly recommend that you add MIME Type and Filename columns to your tables and use them in the application.
    >

    Thanks for the recommendation, added MIMETYPE and TYPETYPE to my table, they are now saved when uploading an image to the CLUBS table. I have added them in Blob Column Attributes (see screenshots). It works on the page that shows the images correct, but not on the page were I am working on now...

    New screenshot:
    http://img259.imageshack.us/img259/481/afbeeldingfield2.jpg
    From the screenshot I can see that you have this on apex.oracle.com. Suggest you create a guest developer account and post login credentials for the workspace so we can look at this directly.
  • 8. Re: Show image on report
    garcol Newbie
    Currently Being Moderated
    Do I have to put the credentials here? Or by private mail?
  • 9. Re: Show image on report
    fac586 Guru
    Currently Being Moderated
    garcol wrote:
    Do I have to put the credentials here? Or by private mail?
    Post them here.
  • 10. Re: Show image on report
    garcol Newbie
    Currently Being Moderated
    workspace = CLM
    user = g***
    pwd = g****

    page 300 works correct, page 800 is incorrect.

    Edited by: garcol on 12-nov-2012 21:50
  • 11. Re: Show image on report
    garcol Newbie
    Currently Being Moderated
    It works now.

    My query was
     select player.id
    , club.id
    from clubs club
    ,   players player
    Solution is not to use the player.id, but the club.id, the primary key from the table which I select the images from.

    The query is now:
    select club.id
    , player.id player_id
    from clubs club
    ,   players player
    I have locked the guest user for now.

    Thanks a lot!

    P.S. this seems to be a bug in APEX? Should be report it?

    Edited by: garcol on 12-nov-2012 13:50
  • 12. Re: Show image on report
    fac586 Guru
    Currently Being Moderated
    garcol wrote:
    It works now.

    My query was
     select player.id
    , club.id
    from clubs club
    ,   players player
    Solution is not to use the player.id, but the club.id, the primary key from the table which I select the images from.
    Missed that. It's something of an argument for using table-specific column names (i.e. "CLUB_ID" and "PLAYER_ID" rather than multiple "ID" columns on different tables) to avoid ambiguity.
  • 13. Re: Show image on report
    garcol Newbie
    Currently Being Moderated
    No problem.

    But my next challenge is this:

    Now I have a page with matches, a match between two clubs of course (with different images). How should I do this?

    Query:
    select club1.id
    , club2.id id2
    , match.id wsd_id
    , club1.name clb_name_home
    , match.clb_home
    , club2.name clb_naam_out
    , match.clb_out
    , dbms_lob.getlength(club1.AFBEELDING) AFBEELDING
    , dbms_lob.getlength(club2.AFBEELDING) AFBEELDING2
    from  matches match
    ,       clubs      club1
    ,       clubs      club2
    where club1.id     = match.clb_home
    and     club2.id     = match.clb_out
    The column AFBEELDING2 shows the same image as AFBEELDING...
    Maybe this does make sense because AFBEELDING2 has the same properties as AFBEELDING, but I don't know how to solve this. I can't select two club id's... I have to use an alias (id2), but then it is not working...

    Edited by: garcol on 12-nov-2012 23:08
  • 14. Re: Show image on report
    garcol Newbie
    Currently Being Moderated
    Problem solved.

    Solutions is to make a view with both club1.id and club2.id (with mimetype1/filename1 etc.) and AFBEELDING1/AFBEELDING2

    Properties report columns for the images should refer to the AFBEELDING columns in the view. Primary key AFBEELDING1/AFBEELDING2...

    Nice... :)

    Thanks to SWA...
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points