Wednesday, January 20, 2010

Base64 Encoding Binary Files

Several months ago I posted a handful of methods for base64 encoding strings. At that time I suggested that the next important step was base64 encoding binary files. This represents a challenge because PeopleCode does not offer methods for reading binary files. The following PeopleCode uses the Java API and the Apache Commons Codec library to show how to base64 encode a binary file. To run this example, download the commons-codec library and place the jar file in your local %PS_HOME\class directory. Next, create an App Engine with a PeopleCode step and insert the following PeopleCode:

Local JavaObject &f_in = CreateJavaObject("java.io.FileInputStream", "c:\temp\binaryfile.gif");
Local JavaObject &coder_in = CreateJavaObject("org.apache.commons.codec.binary.Base64InputStream", &f_in, True);
Local JavaObject &reader = CreateJavaObject("java.io.BufferedReader", CreateJavaObject("java.io.InputStreamReader", &coder_in));

Local string &b64Data = "";
Local any &line;

While True
&line = &reader.readLine();
If (&line <> Null) Then
&b64Data = &b64Data | &line | Char(13) | Char(10);
Else
Break;
End-If;

End-While;

Local File &b64File = GetFile("c:\temp\base64_encoded.txt", "A", "A", %FilePath_Absolute);
&b64File.WriteLine(&b64Data);
&b64File.Close();

The Java objects at the top of this listing read binary data from a file and transform that data into a base64 text listing, which is then stored in the variable &b64Data. For demonstration purposes, I wrote the contents of &b64Data to a file. In real life, you would add this contents to a CDATA node prior to sending a message through the Integration Broker.

Before running this code, replace c:\temp\binaryfile.gif with the full path to a real binary file on your workstation. After adding the PeopleCode listed above, disable restart and run your App Engine. When the App Engine completes, look in your local c:\temp directory for a file named base64_encoded.txt.

78 comments:

Pressure said...

Hi Jim,
Just wanted to know if there is a way to load this Binary file into a BLOB object in database directly. Not using given add attachment or anything but the way you have done, I mean how to get that bianry content as a binary string ? and can I insert that binary string in database directly then ? If you can help me on this that will be great. this way i can create a mass upload of JPEG files using Peoplecode and not writing PL/SQL upload which can do that.

Jim Marion said...

@Prateek, you could store the base64 results in a CLOB, but considering that base64 encoding creates a file that is dramatically larger than the binary equivalent, I'm not sure this is a good idea.

I haven't tried writing binary data directly to a BLOB using PeopleCode records. I'll have to do some investigating to see how this would work.

If the file is already on your app server, as it would be to base64 encode it, why not use PutAttachment?PutAttachment does require an attachment record definition and a URL definition.

I will look into reading/storing binary data directly in record definitions. I am interested in this as well.

Pressure said...

Thanks Jim, I was just wondering how come I can write binary files using writeRaw but I cant read a doc or jpeg files in peoplecode and load directly into BLOB field in database so that later on I can again print back using writeRaw. If one way is possible other way should be there.

Jim Marion said...

@Prateek, do you mean %Response.WriteBinary? You can definitely do a SQLExec into a variable of type "Any" and then WriteBinary that contents to the browser. Is that what you mean? Now you are looking for a way to acquire binary and write to a table? Just trying to clarify...

Pressure said...

Hi Jim,
I am aware of writing the data to browser using writeBinary( thanks to your post for that). I am looking for just other way round. If I have a jpeg/GIF file on the server and I want to load this into my EMPLOYEE_PHOTO table which has IMAGE field which is nothing but a BLOB (binary large object). I want to read the file as a bianry file and insert that binary content using SQLs. Its possible in Pl/SQL as far as I heard but why not in Peoplecode. why Do we have writeRaw() function in peoplecode to write BLOB objects from database to file why not other way round.
THis way if I can achieve that I would be able to do a mass upload of JPEG files in server to tables using an App engine. Wht do you say ?

Jim Marion said...

Why not use PutAttachment? It does what you described. You write some PeopleCode to insert files into tables. PutAttachment is the method provided by PeopleCode to bulk insert attachments. PutAttachment and GetAttachment are the server side, non UI compliments to the UI based AddAttachment, ViewAttachment, etc. I use PutAttachment and GetAttachment all the time to move files in and out of the database to manipulate/process them on the app server. The added bonus of using PutAttachment when inserting files is that you can later use ViewAttachment and DeleteAttachment from the UI to allow users to work with these files online as well.

Pressure said...

Thanks Jim, I think thats the fastest way to do so. I also thought so , was just wondering if I have Binary I/O in peoplecode to read & write Files

Jim Marion said...

No, unfortunately, there is no binary I/O in PeopleCode. The only way I know of to work with binary data from PeopleCode is through Java.

Pressure said...

Unfortunately I have no idea of Java but thanks to you and your blog. I should say big big thanks to you for helping so many PeoplSoft professionals with your blog.

RGds
Prateek Parasar

Jim Marion said...

@Prateek, Thank you. I am glad to help. Hopefully all of my Java posts give you and other PeopleTools programmers some idea of the power of Java and some ideas on how to use Java with PeopleCode.

Mohammed Hussain said...

Hi Jim,
Thanks for the information provided,As you said in the post i used PutAttachment method to bulk upload photos in to the system. Below is the code i used in my app engine for uploading photos for all active employees.

&RC = CreateRecord(Record.EMPL_PHOTO);

Local Rowset &rs1 = CreateRowset(Record.ZIN_EMPL_TAG_VW); /* SQL View which returns Active employees */

&rcount = &rs1.Fill();

For &I = 1 To &rs1.RowCount

&EMPLID = &rs1(&I).ZIN_EMPL_TAG_VW.EMPLID.Value;

&FILE_NAME = "C:\Upload Photos\" | &EMPLID | ".jpg";

&RC.EMPLID.Value = &EMPLID;


&Result = PutAttachment("record://PSFILE_ATTDET", &EMPLID | ".txt", &FILE_NAME);


SQLExec("SELECT FILE_DATA FROM PSFILE_ATTDET WHERE ATTACHSYSFILENAME = :1 AND VERSION = 1", &EMPLID | ".txt", &Data);

&RC.EMPLOYEE_PHOTO.Value = &Data;
SQLExec("DELETE FROM PSFILE_ATTDET WHERE ATTACHSYSFILENAME = :1 AND VERSION = 1", &EMPLID | ".txt");


&RC.PSIMAGEVER.Value = (Days365(Date3(1999, 12, 31), %Date) * 86400) + (%Time - Time3(0, 0, 0));


&RC.Insert();
&RC.Update();
End-For;

Jim Marion said...

@Mohammed Hussain, If you know the name of the table that has the files you want to copy to the file system, then use the GetAttachment function. Usually the attachment details table (the one with FILE_DATA) is related to a transaction table by ATTACHSYSFILENAME. Find that table also. Then you can iterate over files by emplid, as you are doing and then call GetAttachment using the URL (as you are using), the ATTACHSYSFILENAME (from the transaction table that has ATTACHSYSFILENAME, ATTACHUSERFILE, and EMPLID), and the ATTACHUSERFILE value. ATTACHSYSFILENAME is parameter two to GetAttachment and ATTACHUSERFILE is parameter three.

Pressure said...

I am sending a soap messge from SOAPUI which is this:




AA0001



But in handler I am getting this


AA0001

Not peoplesoft delivered handler for HCM service registry still expects soap message as it is sent, is it a peoplesoft issue or Do we need to do something so that it stops doing this conversion.
Right now I can see it happens all my messages and it removes all the header and soap envelop when message is shown in message monitor.
Any thoughts

Jim Marion said...

@Pressure, did you paste in XML tags or anything? The "what you sent" and "what you get" in your post are identical. I'm not sure what the problem is. Do you need to HTML escape your tags and repost your question?

Pressure said...

before(sent from SoapUI) :
<soapenv:Envelope xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://schemas.xmlsoap.org/ws/2003/03/addressing/" xmlns:xsd="http://www.w3.org/2001/XMLSchema/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance/">
<soapenv:Header/>
<soapenv:Body xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<PERSID>AA0001</PERSID>
</soapenv:Body>
</soapenv:Envelope>

Received at handler(also in monitor:)

<?xml version="1.0"?>
<PERSID xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://schemas.xmlsoap.org/ws/2003/03/addressing/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance/">AA0001</PERSID>

Pressure said...

when I conver HCM registry to IB definition it automatically creates everything including handler but the issue is that handler still expects the message as its written inside the WSDL but when i send the soap message thru SoapUI I receive completely stripped message. So I checked my other custom handler and message which I created and I found out this stripping happens all the time(only thing in my code I always write using getrowset or getelement by tag name and it works.) and I never receive a Soap doc in my handler , this does not happen in outbound as whtver i send is receievd at destination.

Pressure said...

Is there something wrong in my system or way of sending or there is any other way to receive the Soap DOC as it is sent and IB does not play with it.

Jim Marion said...

@Pressure, thank you, that clears it up a bit. Are you using a rowset based message or non rowset?

Pressure said...

HCM registry ones are non rowset based and schema is automatically generated from WSDL given in HCM resgitry for the service.

I still have not found the answer why that soap message changes to XML

Jim Marion said...

@Pressure, I am wondering too. I thought the old SOAP2CI passed the full SOAP message to the handler. The HttpListeningConnector, at that time, wasn't coded to do anything different... hmmm... maybe that is it. Are you using the service listening connector or the HttpListeningConnector?

Pressure said...

I am using ServiceListner connector as I exposed this as webservice . you gave me a point to test it using a Http listening conenctor now. but I have done whtever the service registry says and provided service. we hv decided to report this to oracle as either the issue is with the listener or with their generic handler app package wrriten for these HCM Services which still expects Soap

Pressure said...

How to expose webservice not using ServiceListeningconenctor and rather use HttpListenerconnector, I know I can do without exposing as webservice and just treat as app messaging, but now I am curious if I can use different conenctor to so.

Pressure said...

Thanks Jim. Great news. I have changed the Listner from ServiceListener to HttpListener and it worked like a charm.

How weird. Peoplesoft somewhere should tell me that ServiceListeningconnector does something with my message.

Jim Marion said...

@Pressure, yes, you should open a case with support. If you are using a WSDL with a web service end point, but the delivered code acts like it is processing a full soap message, then there is a problem for development to fix.

If you want to make this change yourself, then I think you just need to change the connector on your node or in your routing connector properties (if you are connecting from PeopleSoft).

If you are posting to PeopleSoft from outside PeopleSoft (like soapui), then you can change the URL in your consumer application. I know the WSDL defines the web service end point as the service connector, but if you need to process the full soap message, then you will want to send to the HttpListeningConnector.

Jim Marion said...

@Pressure, it would be nice if it told you. I forgot it did that. That was one of the purposes for the service listening connector.

Pressure said...

Anyway all is well in the end.We dnt think peoplesoft wants these service operation to be exposed as webservice rather use as app messages over http which will work on http listener conenctor. thanks for spending time on this.
thanks a lot.

Pressure said...

I have got another question. I need to implement a thirda party payment gateway and I am sure that USers needs to be redirected to one URL (we will send the data as well) and then user will key in the card etc dtls and pay the amount and once done it should come back to the same page in Peoplesoft. Its not somethign I have done till now. So basically I know how to redirect to external URLs but how to send the data, I assume HTML forms but dnt know exactly, can you please explain mein how and also how the thrida party can redirect back to the same page and send the information back. This is not something related to IB and not my area but I thought you might know as it will involve Iscript and HTML probably. Thanks in advance.

Jim Marion said...

@Pressure, actually, it may use Integration Broker. The FSCM punchouts are a good example of this. Usually the target service will require a callback URL so it can send the information back to your system.

Pressure said...

Hi Jim,
Thanks for quick response but I dnt have FSCM, i am in campus solutions right now. can you share some example. Once I have seen data being posted using HTML form post and then receiving once the work is done on external website. If you have anything let me know atleast it will give me a starting point.

Jim Marion said...

@Pressure, I'm afraid I don't have a CRM example.

Lazy Donkey said...

Hi Jim,
do you know any way to update component buffer with async to Sync integration. I woud like fire a async - Sync message in component prebuild of integration broker and once we get the response back needs to update the component buffer through AJAX/New technologies.We are in new tools 8.50.
first Question is can we do it. if yes can you help how.

thanks
Lee

Jim Marion said...

@Lee, so asynchronous versus synchronous. Synchronous would be blocking on prebuild. If you make a synchronous request from prebuild, then the prebuild event will not return until the synchronous request returns. This means your users will not see the component displayed until the synchronous request returns. There is no Ajax involved here. If you used asynchronous in prebuild, then you would send a message and include a callback. You would then have to poll for a result because there is no way for the integration broker to update a component buffer. I don't think you want that either.

If you just want to update the page, then add an HTMLArea to the page and make the Ajax service request from the page (either to an iScript or to the Integration Broker HTTPListeningConnector).

If you want to update the component buffer, then do the same as the page example above, but on return of the asynchronous Ajax request, update the local page field and execute the field's onchange event (if it has one). This won't update the component buffer (unless there is FieldChange PeopleCode), but it will update the local page level values. Once the user takes an action that submits the page for processing (FieldChange, Save, click a prompt, whatever), the component buffer will get updated.

I would discourage a synchronous service call from any component event that happens before the component is displayed. Invoking a service at that point will delay displaying the page to the user and make them think your system has poor performance. If it must happen as soon as the page loads, then use JavaScript and Ajax to do it when the page loads. I think this will give you the best experience.

Even though you are on PT 8.50, I don't think you will get any benefit from the new PT 8.50 ajax in your scenario.

If you read my other posts, you know that I would recommend using jQuery to do this. I think jQuery dramatically simplifies the amount of JavaScript you have to write.

Let me know if this makes sense.

Joseph said...

Jim, I am sending a synchronous request to ECM (Enterprise Content Manager, an Oracle Product) using the http target connector. I saw a couple references to an HTTPListeningConnector. I am sending and receiving a SOAP document to download a file. I am having problems decrypting the file. Could my problem be that I am using the PeopleSoftListeningConnector? If so, how do we set up an HTTPListeningConnector?

Jim Marion said...

@Joseph, I would have to look closer to know for sure, but it sounds like you are already using the Http Target Connector. Since it is synchronous, the response will come back on the same channel as the request. If your target node is configured to use HTTPTARGET, then the response will come back over HTTP.

SOAP is text, so if you are receiving binary data, it must be encoded in some way (base64?). I know of ECM, but haven't worked with it. Does the SOAP response contain the file or does it just contain a URL to the file? If it is just a URL/pointer to the file, then you will need some other mechanism to download the file.

Joseph said...

Jim, the file is in the SOAP response and is encoded with base64 (according to the Oracle ECM support).

So far, though, I have not been able to decode and open the file (using Word, or Acrobat, et al). I have tried several ways, but it appears that I have to stream the decoded data into the file, as opposed to decoding to a text string and writing that to the file (file.writeline). I am not a java programmer, so I don't know how this is done with java, although I have tried a couple of the easier java methods like:

Local object &oDecoder = CreateJavaObject("sun.misc.BASE64Decoder");
Local object &dOut = &oDecoder.decodeBuffer(&Fld_Str);
Local object &oDecoded = CreateJavaObject("java.lang.String", &dOut, 0, &dOut.length, "UTF8");
&DecodeResult = &oDecoded.toString();

Please help!!! I have run out of ideas (and quickly running out of time)?

Joseph said...

fyi... in regard to my last post, I have solved the problem of writing a binary file via PeopleCode. In this case, I am getting the file from Oracle's Enterprise Content Manager.

REM &Fld_Str contains the ecrypted binary data (from SOAP Msg)
&Fld_Str = &Rslts_Node.NodeValue;

rem &PrintFlName is the path and filename from the SOAP MSG;
&PrintFlName = &Sav_Dir | &Resp_NameVal;

Local object &oDecoder = CreateJavaObject("sun.misc.BASE64Decoder");
Local object &dOut = &oDecoder.decodeBuffer(&Fld_Str);
Local object &fstream2 = CreateJavaObject("java.io.FileOutputStream", &PrintFlName);

&fstream2.write(&dOut);

&fstream2.close();

Jim Marion said...

@Joseph, Brilliant! Well done. Thank you for sharing.

One comment though... instead of using Local Object, I recommend using Local JavaObject. I don't know if it will change anything, but the return value of a Java method is technically a JavaObject, so it is worth noting that in the declaration.

Rachel S said...

I have problem while attaching file to database. It passed me an error “Error attaching file to database.” I’m using PutAttachment method, standard attachment record definition and a URL definition --> &rtn = PutAttachment(URL.FILEDB, &fname, %This.getFullPath());
Does this error got to do/fix some setting at the server?

Rachel S said...

Missed the greeting :)
hi Jim

Jim Marion said...

@Rachel, I'm trying to think of what could cause this... My only guesses are that either the file path isn't correct, the app server doesn't have access to the file, or the record doesn't exist. Since you are using FILEDB, I'm assuming the table was built from the record. If you haven't already done so, I suggest using the File object to write %This.getFullPath() to a temp/log file. You can also use the File object to try to open the file specified by %This.getFullPath read only and see if you get a file access error. If this doesn't answer the question, then I think you should get someone to look at the code and see it execute in your environment, for example, opening a case with Global Support.

Jim Marion said...

Hi Rachel, :D No problem. Thank you for being polite!

-- Jim

Rachel S said...

Hi Jim.

thanks for the reply. ill try to debug it using the file object.

kane81 said...

Hi Jim,

I'm hoping you can help out here. I want to convert file attachments to base64 from an Oracle database.

From your other post about base64, I thought I could do this as an example

Local String &data1, &data2;

SQLExec("SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(FILE_DATA))) FROM psfile_attdet where attachsysfilename = '4488R2Current.doc' and FILE_SEQ = 0", &data1);
SQLExec("SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(FILE_DATA))) FROM psfile_attdet where attachsysfilename = '4488R2Current.doc' and FILE_SEQ = 2", &data2);

Local File &FILE = GetFile("C:\base64.txt", "w", "a", %FilePath_Absolute);
&FILE.WriteString(&data1);
&FILE.WriteString(&data2);
&FILE.Close();


However I get casting problems with my SQL :(


Any ideas what I need to do to get FILE_DATA to base64?

Is what I want to do going to work?


Actually what I then want to do is decode the base64 and put it into a java byte[] array – not a file.

So

Local JavaObject &documentByteArray = new sun.misc.BASE64Decoder().decodeBuffer(&data1+&data2);

rem Use Java POI to check if word document is password protected;

Local boolean &isProtected = POI.IsWordDocumentPasswordProtected(&documentByteArray);


Thanks for any help

Kane

Jim Marion said...

@Kane,

I'm looking at your code and it seems as though base64 is just an intermediate state so you can get it through the PeopleCode layer, which only supports basic types like string (base64) and not binary data. If the data is in a file attachment table in the format acceptable to the File attachment functions (because it was put there by something like AddAttachment or PutAttachment), then why not just use GetAttachment to copy the file locally? Once you have it in a temp file in your app or process scheduler server, you can use java.io.FileInputStream or one of the other streams to work with the file. I'm sure you know how to get a file into a byte array (read from FileInputStream and write to ByteArrayOutputStream) or maybe you can skip the byte array all together?

It would be nice if you could skip the temporary file and just test the file in the database. In fact, if you have Java installed in your database, then you may be able to do this with a Java stored procedure instead.

washington said...

Hi Jim,

Thank you for the post. The base64 examples works great for files less then 4 MB. However, for files larger then 4 MB I am interested in looking at using MTOM. Do you have any examples of using MTOM within Peoplesoft/Peoplecode?

Thanks,
Washington

Jim Marion said...

@Washington, I don't have any examples. I'm actually not familiar with MTOM. I just looked it up and it looks like it is a W3C recommendation for embedding binary in XML... but not quite. Actually, using pointers to MIME boundaries that contain binary data. I don't seem MIME boundaries as an issue with PeopleSoft, what I do see as an issue is getting the binary data from where ever (database, file, etc) and into an Integration Broker message. I'm not sure how that would work.

Now, with that said, Integration Broker isn't the only way to integrate. On occasion, I've been known to use Axis2 proxies and commons-httpclient from PeopleCode rather than use Integration Broker. If you have a Java based MTOM library, then using it would be pretty simple. If not, then using plain old http through a client like common-httpclient is another alternative. It really depends on whether PeopleSoft is the service or the client. If it is the client, then you are free to do whatever you want. If it is the listener, then... Hmmm... I wonder if a custom ListeningConnector would be appropriate here.

raju said...

Hi Jim,

I have downloaded the Codec 1.5 jar file and copied it to my Application server CLASSPATH ($PS_HOME/appserv/classes). When I try to use the code you provided, I am getting below error.
"Java Exception: java.lang.NoClassDefFoundError: org/apache/commons/codec/binary/Base64InputStream: finding class org.apache.commons.codec.binary.Base64InputStream (2,725) STF_PD_RCOM_WRK.HYPERLINK.FieldChange PCPC:9719 Statement:89

The noted error was encountered while looking for the given class. Possible errors include a misspelling of the class name, or not having the location of the class file in the CLASSPATH."

Please let me know what and where I am making a mistake. Please advice. Appreciate your help.

Thanks,
Sunnyvale

Jim Marion said...

@Raju, Jar files aren't accessible automatically. When the app sever starts, it iterates over all jar files in the directory and adds them to the class path. If restarting your app server doesn't solve the issue, then try placing the file in $PS_HOME/class and then restart the app server.

Raju said...

Hi Jim,

The Solution that you have provided worked perfectly. Sorry for replying late i was engaged in some other assignment.

I have one more hurdle to cross.My question to Oracle was "Will there be any impact if we upgrade Commons-codec-1.3.jar to Commons-codec-1.5.Jar in our PS envr".

Answer:"Since this is upgrade will entail the modification of the original and required JRE (1.6.x) by replacing the Commons-codec-1.3.jar file with the Commons-codec-1.5.Jar file this environment will no longer be certified and supported as it requires the original JRE 1.6.x files. If this implementation is carried out and the PeopleSoft environment requires any troubleshooting for any reason, the change would need to be backed out so that the original and supported JRE is re-established".

So is there a way in peoplesoft to set the class path before calling the java class or to change the CLASSPATH dynamically. So what should be my approach here.

Thaks
Raju.

Jim Marion said...

@Raju, Yes, you can create custom class loaders and dynamically load jars. See the URLClassLoader for more details. Unfortunately, this will likely require you to do all your work in PeopleCode/Java reflection, which is very ugly. An alternative would be to write some custom java that uses the class loader and returns the appropriate values, or objects with strongly typed return types instead of "Object." Your custom Java won't violate any support agreements.

Raju said...

Hi Jim,

Thanks for your valuable suggestion.
When i am going through people books under the Java class i found that When PeopleTools loads the Java Virtual Machine (JVM), PeopleTools builds a class path for the JVM from the following elements. And there will be a specific search order. First it looks into PS_HOME\class then PS_HOME\appserv\classes and later For each directory listed in the "Add To CLASSPATH" parameter of the psappsrv.cfg configuration file.(where we can keep the custom jar files).

So is it bad to keep this code.1.5.jar in a custom path and add it to the "Add To CLASSPATH" parameter. Please suggest. When i tried this it is working fine.

Thanks
Satya.

Jim Marion said...

@Raju, using Add to Classpath is actually the best way. Well done.

Michael said...

I keep getting the following error when attempting to run this code in 2-tier Application Designer.




Java Exception: java.lang.NoClassDefFoundError: org/apache/commons/codec/binary/Base64InputStream: finding class org.apache.commons.codec.binary.Base64InputStream (2,725) ZFS_SAFESERV.MAIN.GBL.default.1900-01-01.Step04.OnExecute PCPC:287
Statement:2

Message Set Number: 2


Message Number: 725


Message Reason: Java Exception: %1: finding class %2 (2,725)



I have copied the .jar files to pshome/class and had our administrators reboot the application/web/batch servers and it still can't locate the class.



Also, I've seen the PS_CLASSPATH environment variable may need to be modified, however I cannot find any information on where this value can be set. Can someone help? Thanks!

Jim Marion said...

@Michael, 2 tier means it isn't running through your app server, and is using your local App Designer's PS_HOME. Make sure you have the jar files in your local peopletools PS_HOME as well.

Michael said...

Thanks for your help Jim. I was able to successfully run this when going through App Server. I was just testing it out using an App Engine in 2-tier, but it won't be used that way when I get to production.


I have another question regarding Base64 decoding. We have a 3rd-party document management system that is sending a Base64-encoded string via a web-service response. I am able to retrieve this value, decode it, and save it to a TIF file, however is there a way to decode this stream and output the TIF to the user via %Response.Write (or something like this) without actually storing a file on the App Server? We would prefer not to physically store the TIF file and then have to go back and clean it up. Thanks!


FYI...I was fortunate enough to win a copy of your PeopleSoft PeopleTools Tips & Techniques book at a session you presented at Collaborate11. It is an excellent book and has some very useful information.

Jim Marion said...

@Michael, I'm glad you are putting the book to good use.

You can use the Response.WriteBinary (not documented) method to write binary data to the browser. I have only used this method with blob data from database fields (either SQLExec or SQL cursor). If you can get your binary data into a variable and write it with Response.WriteBinary, then you are good to go.

Sachin Jhawar said...

Hi Jim ,

Can you guide on how to send a file as an attachment using integration broker in people soft i am not finding any useful document on the same.
If possible can you provide some sample code or walkthrough.

Jim Marion said...

@Sachin, XML doesn't allow binary content. Integration Broker deals with XML (and plain text, but with an XML wrapper). The only way I know of to send binary data (attachment) through integration broker is by Base64 encoding the binary data. That is the point of this post. Use the code here to base64 encode your attachment, and then insert the base64 string into your XML payload.

Sachin Jhawar said...

Thanks Jim for your prompt response.

Timothy Lee said...

Hi Jim,

Our People system is going to receive a pdf file from the third party site through a pushbutton on the PeopleSoft page. The problem is that we want to save the pdf file in our UNIX server so that we want to merge the downloaded PDF's into one PDF file.

I cannot use ViewURL or ViewContentURL because the functions display the pdf file in the browser window instead of saving the file on the UNIX server.

Do you have idea how to redirect (or pipe) the pdf to a file (or binary data) on the UNIX server when the website is called from the PeopleSoft page through the pushbutton?

The website url is like this:

https://www.test.com/pdf?id=12345&user=joe&passwd=opensesame.

I tried use CopyAttachments or %IntBroker.ConnectorRequest but I haven't had any success so far.

Thanks in advance for your input.

Jim Marion said...

@Timothy, GetAttachment and PutAttachment are the server side versions of ViewAttachment and ViewXXUrl... but the GetAttachment and PutAttachment deal with FTP or database storage locations and only move files between the app server and those storage locations. I am not aware of specific PeopleCode functions for accessing binary HTTP. The integration broker methods were designed for text based integration, not binary.

If I were to accomplish this task, I would use PeopleCode's support for the JavaObject variable type to make the HTTP request and save it locally. If you have access to your app server's lib directory, then the easiest way is to download the commons-httpclient and write your own Java to make the HTTP request and save the file. Then you can create an instance of the JavaObject through your FieldChange PeopleCode.

If writing Java or downloading a library is not an option, you can still do this with Java, but it will be a lot more work. In this scenario you would create instances of Java objects using CreateJavaObject and then work with the low level HTTP connection directly to write the binary response to a FileOutputStream. This scenario is more difficult because PeopleSoft doesn't support casting or overloaded constructors. Doing anything complex often requires using Java reflection. Both this blog and my book contain many examples of using Java with PeopleCode.

Viswanathareddy K said...

Hi JIm,

Good Morning.

We have an requirement of interacting from peoplesoft finance system to third party webserver to get the files in base64 format.

Request you to please let me know how to convert these base64 format data received through response message , back to original file or BLOB to insert into peoplesoft records.

Thanks
Viswa.

Jim Marion said...

@viswanathareddy, if the received base64 data is binary, then you will need to use Java as shown in this blog post to write the binary streams to file, and then import them using PutAttachment. If the incoming data is base64 encoded text, then you can use any one of the mechanisms shown here to convert the base64 to plain text, and then SQLExec, etc it into your database.

Viswanathareddy K said...

Hi Jim,

Many thanks for your time in reply.

We are using SQL server so we are facing the challnegs in converting base64 encoded text to binary as there is no UTL functions provided in sqlserver like in oracle data base.

and also request you to please provide me the java files which can be used to convert (common codec) base64 binary back to original file.

Thanks
Viswanathareddy K.

Jim Marion said...

@viswa, The commons codec Java is available for download from the link in the code above. Decoding is very similar to encoding, except that you will be writing to a Java file object inside your read loop. Your Base64InputStream constructor will use "false" instead of "true" and will use something like the java.io.ByteArrayInputStream.

First you will create a Java string from your PeopleCode base64 input string (see the fourth line of my Regular Expressions example). Then you will create a new ByteArrayInputStream... something like this:

Local JavaObject &f_in = CreateJavaObject("java.io.ByteArrayInputStream", &j_string.getBytes());

In line 3 of the example in this post, instead of creating a BufferedReader, you will create a FileOutputStream (or BufferedOutputStream), passing in the name of your file as the constructor. The rest is the loop, and for the loop you will read from &coder_in (which should probably be renamed &decoder_in) and write to the FileOutputStream.

Sachin Jhawar said...

Hi Viswa,

May be you can try this as well , this works in microsoft sql server, store your base64 in a record and then use below casting function to convert it into binary data . kindly note the below cast function is case sensitive and hence u have to use %NoUpperCase metasql.


%NoUpperCase
SELECT ATTACHSYSFILENAME
,FILE_SEQ
,VERSION
, CAST(N'' AS xml).value('xs:base64Binary(xs:hexBinary(sql:column("A.FILE_DATA")))'
, 'varbinary(max)') AS sql_handle_base64
FROM PSFILE_ATTDET A


Thanks
Sachin Kumar Jhawar

Jim Marion said...

@Sachin thank you. That is very helpful. So the idea here would be to save the incoming base64 data in a row in a table (SQLExec), and then SQLExec again as an insert/select that converts from base64 to binary. For inserting into long character fields, you can SQLExec, but be sure to use the %Text Meta-SQL. In fact, you may be able to use %Text with the CAST to convert directly from b64 into bin without a staging table.

Although, Sachin, the fact that you are selecting from PSFILE_ATTDET tells me that you are base64 encoding binary data. Can you post the reverse: decoding base64 into binary?

Sachin Jhawar said...

Yes Jim the sql example was indeed used in context of converting base64 from binary, but i modified the data type ,so that same can be used , if you notice in the below sql ,i have declared the result as varbinary(max) , for binary to base64 conversation this would be varchar(max) . so for our scenario it can be something ilke as described in b

a)
%NoUpperCase
SELECT ATTACHSYSFILENAME
,FILE_SEQ
,VERSION
, CAST(N'' AS xml).value('xs:base64Binary(xs:hexBinary(sql:column("A.FILE_DATA")))'
, 'varbinary(max)') AS sql_handle_base64
FROM PSFILE_ATTDET A

b) %NoUpperCase
insert into PS_Target table
select CAST(N'' AS xml).value('xs:base64Binary(xs:hexBinary(sql:column("A.")))'

, 'varbinary(max)') AS sql_handle_binary
FROM PS_Source_table A

let me know if this works ! or i have to come up with actual sql and syntaxes !!

Jim Marion said...

@Sachin, thank you again.

Jim Marion said...

@washington, PeopleTools 8.52 supports MTOM. The PeopleBook entry Sending and Receiving Binary Files contains code samples and documentation for sending and receiving binary files using MTOM. I'm wondering if I can use this new feature for UCM/document management system file transfers and mobile image uploads (expense receipt photos?).

Dan said...

Jim,

I want to do the opposite - I encode a excel workbook on my laptop, and format a SOAP request in VB to send the encoded payload to IB. In my app package, how can I decode back to binary and put to a server path?

I can get the message, grab the encoded text into a string or array - now I want to decode it and somehow do a put_attatchement.


- Dan

Jim Marion said...

@Dan, take a look at this post Base64 Encoding Binary Files in PT 8.52. If you are using PT 8.52 or higher, you can use the File.WriteBase64StringToBinary method to write a base64 string to a binary file. If not, then the next easiest way is to use the Commons Codec library shown here, but changes the in's to out: FileOutputStream, Base64OutputStream, etc.

Viswa said...

Hi Jim/Sachin

created a staging table with field as long to hold base64 resposne and i used below sql to convert it to hexbinary as you mentioned.

%NoUpperCase
SELECT ATTACHSYSFILENAME
,FILE_SEQ
,VERSION
, CAST(N'' AS xml).value('xs:base64Binary(xs:hexBinary(sql:column("A.FILE_DATA")))'
, 'varbinary(max)') AS sql_handle_base64
FROM PSFILE_ATTDET A

But iam getting the below error

Return: 8602 - [Microsoft][ODBC SQL Server Driver][SQL Server]XQuery: SQL type 'text' is not supported in XQuery. (SQLSTATE 37000) 6739

Instead of column is i use
decalre @str varchar(20);
set @str = 'VEVTVDE=';
and
SELECT CAST(N'' AS xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(max)') AS sql_handle
FROM PSINSTALLATION

iam getting the hexbinary value.

Please help me with the column type xquery.

Viswa said...

Hi Jim,Sachin

If we use sql:column option to convert base64 to hexbinary , its not handling the text/image sql types.

%NoUpperCase
SELECT ATTACHSYSFILENAME
,FILE_SEQ
,VERSION
, CAST(N'' AS xml).value('xs:base64Binary(xs:hexBinary(sql:column("A.FILE_DATA")))'
, 'varbinary(max)') AS sql_handle_base64
FROM PSFILE_ATTDET A

So converted base64 stream to hexbinary by using below sql.

&Hex = "NOUPPERCASE SELECT CAST(N'' AS XML).value('xs:base64Binary(" | Char(34) | &Base64_String| Char(34) | ")' , 'VARBINARY(MAX)')"; and then inserted into fileattachment records after proper handling of file seqnum.

But with this appraoch if base64_string exceeds 32700 its giving error saying that maximum lengthe of sql statment exceeded.

Thanks
Viswa.

CHETAN A G said...

Hi Jim,

I have a requirement to display the company logo in the email generated using generic templates and value to this generic templates need to be sent via SQL/Query?

Please let me know if you come across this kind of scenario or do have any idea how can we display image in mail?

Thank you in advance

Jim Marion said...

@Chetan, I have not. You should be able to accomplish this with HTML based e-mail. I suggest you post your question on the PopleSoft OTN Forum or PeopleTools IT Toolbox group.

Kameswara Rao said...

Hi Jim

I have a requirement to read a File object in a IB XML message coming from external system using Peoplecode. Please let me know the best way we can accomplish this.

Kamesh

Jim Marion said...

@Kamesh, Is this synchronous or asynchronous? The PeopleCode will be the same, but configuration within the Service Operation will be slightly different.

I assume that the XML you will be processing is part of the "payload" delivered with the request, not an actual file sitting on the file server? For transmitted XML, you have a couple of options. I'm not sure there is one way that is easier than another, they are just different. If the incoming message is rowset based, then you can use Message.GetRowset to iterate over the results as if the XML was a component buffer. If it is not rowset based, then you can either use XSL in a routing transform to make it rowset based, or you can use the Message.GetXmlDoc method to get a pointer to the XML document. With a reference to the XML document, you can traverse the nodes or use XPath selections to identify information within the XML document.