1 2 Previous Next 15 Replies Latest reply: Nov 13, 2012 3:32 AM by 623708 RSS

    Show image on report

    garcol
      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
          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
            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
              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
                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
                  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
                    >
                    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
                      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
                        Do I have to put the credentials here? Or by private mail?
                        • 9. Re: Show image on report
                          fac586
                          garcol wrote:
                          Do I have to put the credentials here? Or by private mail?
                          Post them here.
                          • 10. Re: Show image on report
                            garcol
                            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
                              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
                                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
                                  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
                                    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