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):
12 comments:
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.
@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.
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
@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
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.
@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.
Jim, what do you do when some employee photos are over 7275 bytes? For example, some of our employee photos are as big as 507894. Thanks!
@Jim I have tried to change de file extension to jpg in Johann but it doesn't work. I don't know if it's because of the charset or if there is a configuration in the server to accept to write jpg images. With .gif files there is no problem.
@leaf, any reason to have such high resolution images? If you are taking this approach, you have to honor the maximum length. To do that, just add more columns with appropriate substr values... or just use the image field type in PS Query that will give you a URL fragment for the image. Then you can just reference it directly.
Hi Jim..
I am trying to display Image of the Item URL , store in the Item master..
can i use the same logic in PSQUERY... to convert URL in image and instead of displaying URL i can display Image?
or what are my options?
Hi Jim,
Suppose the images lived in a database external to PS. What are good options for displaying the images in PS? Would we need to move the data to our PS database first or are there ways to display the images without storing the data locally?
- Would it be bad practice to create a database link and use the same SQL above to get the base64 string?
- I believe we can make calls to a middleware that could use a direct database connection to get the base64 string and return it to PS.
- I have looked a little bit at MTOM for receiving data through integration broker. But from what I understand, only asynchronous operations are supported.
I'm just not sure about the performance of any of the approaches above.
If you just want to display the image, is it possible to craft a URL to the system that owns the image? If so, that would be the easiest. If you need to proxy the image into PeopleSoft, then all of the options you mentioned would work as well. I would stay away from MTOM for this. I don't think it is necessary.
Post a Comment