Monday, August 18, 2014

Accessing Database Photos from Query Access Service

I have been working with the PeopleTools 8.54 REST Query Access Services. I would absolutely LOVE them if they returned JSON instead of XML. With a little help from x2js I am able to work around this "shortcoming." One specific challenge I faced was accessing image data. For example, with PeopleSoft query I can see who has photos in PS_EMPL_PHOTO, but I can't see the actual uploaded photo. With a little help from Oracle and a query expression, however, I can convert the photo blob into base64:

SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(EMPLOYEE_PHOTO))
  FROM PS_EMPL_PHOTO
 WHERE EMPLID = 'KU0003'

The only problem with this approach is that Oracle database has a maximum size limit on data that can be encoded and most of the photos I have seen exceed that maximum. The way I chose to work around this limitation is to substring the blob and encode it in fragments. I create a separate column for each fragment, and then concatenate them together in the REST client. Here is some sample SQL from a PeopleSoft query. Each of the CASE statements is a query expression.

SELECT
    CASE
      WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 1455 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, 1455, 1)))
      WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) <= 1455 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(EMPLOYEE_PHOTO))
    END AS C1,
    CASE
      WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 2910 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, 1455, 1456)))
      WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) <= 2910 AND DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 1455 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) - 1455, 1456)))
    END AS C2,
    CASE
      WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 4365 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, 1455, 2911)))
      WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) <= 4365 AND DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 2910 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) - 2910, 2911)))
    END AS C3,
    CASE
      WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 5820 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, 1455, 4366)))
      WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) <= 5820 AND DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 4365 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) - 4365, 4366)))
    END AS C4,
    CASE
      WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 7275 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, 1455, 5821)))
      WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) <= 7275 AND DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 5820 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) - 5820, 5821)))
    END AS C5
  FROM PS_EMPL_PHOTO
 WHERE EMPLID = 'KUL704'

On the client I use something like this:

var data = data:image/jpeg;base64," + columns.join("");

The end result is something like this (right-click to see base64 data):

Mikko,Jill's Photo

6 comments:

Keith Talbot said...

Nice write-up.

I have written almost identical SQL to get employee photo images into BI Publisher from a PS Query. However, I choose to concatenate all of the base64 segments back into a single Long field in the query rather than rely upon the client, or BI in my case, doing the join.

Jim Marion said...

@Keith, I tried the same thing. It worked great until I trested the largest photo in my database. Then Oracle threw: ORA-01489: result of string concatenation is too long.

Surendra Pathak said...

Jim,

I am trying to convert BLOB datatype to Image(.JPEJ). and wants to put in specific folder using Application engine.

I tried finding answer but I could not find.

Could you please help me.

Surendra

Jim Marion said...

@Surendra, there are a few ways to do this. The best way is to use the File Attachment API. If that isn't possible, then you can find a couple of alternatives here: https://jjmpsj.blogspot.com/search?q=export+attachments

Johann Oppus said...

Hi Jim,

I would like to ask for your assistance in a requirement i'm trying to solve.

The requirements I'm working now is to get the Photos of the students found in EMPL_PHOTO and have it ZIP then output in an appengine.

I was thinking using the GETATTACHMENT and PUTATTACHMENT but the record EMPL_PHOTO is not structured as an attachment record.

I tried using the code below for generating the photos. but it only generate if the file is GIF. Photos found in EMPL_PHOTO is JPG.
===============================
Local File &FILEPhoto;
Local Record &RECPhoto;
Local SQL &SQL;

&RECPhoto = CreateRecord(Record.EMPL_PHOTO);
&SQL = CreateSQL("SELECT * FROM SYSADM.PS_EMPL_PHOTO WHERE EMPLID = :1 ", &Stg.EMPLID.Value);
While &SQL.Fetch(&RECPhoto)


&EmplId = &RECPhoto.EMPLID.Value;
&filename = %FilePath | &EmplId | ".gif";
&FILEPhoto = GetFile(&filename, "w", "a", %FilePath_Absolute);
&FILEPhoto.WriteRaw(&RECPhoto.EMPLOYEE_PHOTO.Value);

End-While;

&FILEPhoto.Close();
===============

If so, I will be very thankful for you to share your ideas on how this will be done.

thanks.

Jim Marion said...

@Johann, why don't you change the file extension to .jpg? The code above looks like it should work with any file type. The problem is just identifying the data's file type. Most photos are .jpg, so that will cover most of them.