8 Replies Latest reply on Jan 12, 2018 3:25 PM by finiha

    How do I display an image in a report from a foreign key?

    finiha

      How do I display an image in a report from a foreign key?

       

      I made this table referencing the images on DEMO_PRODUCT_INFO (the table in sample database application)

       

      CREATE TABLE  "CHILD_OF_DEMO_PRODUCT_INFO" 
         (    "ID" NUMBER, 
          "FKEY" NUMBER, 
          "DESCRIPTION" VARCHAR2(400), 
           CONSTRAINT "CHILD_OF_DEMO_PRODUCT_INFO_PK" PRIMARY KEY ("ID")
        USING INDEX  ENABLE
         )
      /
      ALTER TABLE  "CHILD_OF_DEMO_PRODUCT_INFO" ADD CONSTRAINT "CHILD_OF_DEMO_PRODUCT_INFO_FK" FOREIGN KEY ("FKEY")
            REFERENCES  "DEMO_PRODUCT_INFO" ("PRODUCT_ID") ENABLE
      /
      

       

      then I made a report with this sql

      select ID,
             FKEY,
             DESCRIPTION
        from CHILD_OF_DEMO_PRODUCT_INFO
      

       

      and I formatted the FKEY column

       

      this way

       

      but the report produces this error

       

      I read this oracle page, but it does not explains it

      https://docs.oracle.com/database/apex-5.1/HTMDB/understanding-blob-support-in-forms-and-reports.htm#HTMDB29659

        • 1. Re: How do I display an image in a report from a foreign key?
          finiha

          I made the application in

          Workspace: whatisworkspace

          user: everybody

          pass: everybody

          • 2. Re: How do I display an image in a report from a foreign key?
            Jitendra

            Hi finiha,

             

            I modified your report query in page 1,

            please check it is showing the image.

             

             

            Regards,

            Jitendra

            1 person found this helpful
            • 3. Re: How do I display an image in a report from a foreign key?
              finiha

              Thank you.

               

              But why it does works?

               

              Does the query needs to explicitly query the id of the table having the BLOB? (and not just the same number from the other table foreign key)

               

              Where is it explained?

              • 4. Re: How do I display an image in a report from a foreign key?
                fac586

                finiha wrote:

                 

                How do I display an image in a report from a foreign key?

                 

                I made this table referencing the images on DEMO_PRODUCT_INFO (the table in sample database application)

                1. CREATETABLE"CHILD_OF_DEMO_PRODUCT_INFO"
                2. ("ID"NUMBER,
                3. "FKEY"NUMBER,
                4. "DESCRIPTION"VARCHAR2(400),
                5. CONSTRAINT"CHILD_OF_DEMO_PRODUCT_INFO_PK"PRIMARYKEY("ID")
                6. USINGINDEXENABLE
                7. )
                8. /
                9. ALTERTABLE"CHILD_OF_DEMO_PRODUCT_INFO"ADDCONSTRAINT"CHILD_OF_DEMO_PRODUCT_INFO_FK"FOREIGNKEY("FKEY")
                10. REFERENCES"DEMO_PRODUCT_INFO"("PRODUCT_ID")ENABLE
                11. /

                then I made a report with this sql

                1. selectID,
                2. FKEY,
                3. DESCRIPTION
                4. fromCHILD_OF_DEMO_PRODUCT_INFO

                and I formatted the FKEY column

                 

                this way

                 

                but the report produces this error

                 

                I read this oracle page, but it does not explains it

                https://docs.oracle.com/database/apex-5.1/HTMDB/understanding-blob-support-in-forms-and-reports.htm#HTMDB29659

                It does:

                To facilitate the inclusion of a download link in a report, the report includes the selection of the length of the BLOB (for example, dbms_lob.getlength(RESUME)). If the length is 0, the BLOB is NULL and no download link is displayed.

                Your report query does not do this. FKEY is a reference to a row in the DEMO_PRODUCT_INFO table. It is not a reference to the image itself, nor does it provide the required BLOB length information.

                 

                The FKEY value would be used to join CHILD_OF_DEMO_PRODUCT_INFO to DEMO_PRODUCT_INFO in order to be able to access the image column in the master table:

                 

                select
                    c.id
                  , c.fkey
                  , c.description
                  , dbms_lob.getlength(pi.product_image) img
                  , pi.product_id
                from
                    child_of_demo_product_info c
                      join demo_product_info pi
                        on c.fkey = pi.product_id
                

                 

                BLOB format support also requires the primary key value of the image row to be included in the query results, or an ORA-01403 error will occur.

                 

                See working example on page 586 of the demo application.

                • 5. Re: How do I display an image in a report from a foreign key?
                  fac586

                  finiha wrote:

                   

                  But why it does works?

                   

                  Does the query needs to explicitly query the id of the table having the BLOB?

                  Yes. The report query projection must include a column returning the size of the image, and a column with the same names/aliases and values as the PK column(s) in the image source table, as referenced in the Primary Key Column 1/2 properties of the report image column.

                  (and not just the same number from the other table foreign key)

                  Only possible if the column name/alias is the same as the PK column in the other table. This suggests confusion on your part as to the fundamentals of relational databases. The FK column is a reference to a row in another table. It is not a pointer or object reference that provides direct access to the original. To access the data in the other table, it must be included in the query via a join.

                  Where is it explained?

                  In the linked documentation:

                  To facilitate the inclusion of a download link in a report, the report includes the selection of the length of the BLOB (for example, dbms_lob.getlength(RESUME)). If the length is 0, the BLOB is NULL and no download link is displayed.

                  • 6. Re: How do I display an image in a report from a foreign key?
                    fac586

                    Jitendra wrote:

                     

                    I modified your report query in page 1,

                    Do not modify the original components of a demonstration app. This obscures the original problem and creates confusion for the OP and others who are offering assistance. Always create a copy of the problem page/region/component and work on this.

                    • 7. Re: How do I display an image in a report from a foreign key?
                      finiha

                      fac586 escribió:

                       

                      Where is it explained?

                       

                      In the linked documentation:

                      To facilitate the inclusion of a download link in a report, the report includes the selection of the length of the BLOB (for example, dbms_lob.getlength(RESUME)). If the length is 0, the BLOB is NULL and no download link is displayed.

                      It should be reworded?

                       

                      To say that the report includes something is not the same as saying that the programmer needs to provide it. It gives the oposite impression.

                      I would assume that if the reports gets the BLOB image, then it can run the dbms_lob.getlength function.

                       

                      But what I was asking is were is explained how to make the query. For example that the ID of the images table needs to be queried.

                      • 8. Re: How do I display an image in a report from a foreign key?
                        finiha

                        fac586 escribió:

                         

                         

                        1. select
                        2.     c.id
                        3.     ,c.fkey
                        4.     ,c.description
                        5.     ,dbms_lob.getlength(pi.product_image) img
                        6.     ,pi.product_id
                        7. from
                        8.     child_of_demo_product_info c
                        9.     joindemo_product_info pi
                        10. on    c.fkey = pi.product_id

                         

                         

                        Is more complex than that.

                        I had been experimenting, and it looks like the image column ('img') should be queried EXACTLY before the image ID, or it doesn't works.

                         

                        For example, for some reason, this doesn't works:

                         

                        select
                            c.id
                            ,c.fkey
                            ,c.description
                            ,pi.product_id
                            ,dbms_lob.getlength(pi.product_image) img
                        from
                            child_of_demo_product_info c
                            joindemo_product_info pi
                            on    c.fkey = pi.product_id