One Star

[resolved] File into BLOB

I have a delimited file which I use to generate rows. I go thru a replace and a tMap process. At that point, I need to grab a file off of the filesystem based on a field value. That file is then to be a BLOB in the given row. I've got everything running well except I cannot find a clean way to get the file into the BLOB column. Any help or suggestions would be appreciated.
1 ACCEPTED SOLUTION

Accepted Solutions
Employee

Re: [resolved] File into BLOB

Here is a simple example converting this simple delimited file with a reference to another file into a BLOB
Samuel;Johnson;35;C:/temp/Samuel_Johnson.txt
David;Palmer;43;C:/temp/David_Palmer.txt

Note the tMap conversion in the first capture.
Regards,
8 REPLIES
Community Manager

Re: [resolved] File into BLOB

Hi
Set the datatype as 'Object' for BLOB column when define the schema in tMap.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] File into BLOB

I do have that column as a BLOB. How do I load a file off of the disk into an OBJECT column in the row?
Employee

Re: [resolved] File into BLOB

Here is a simple example converting this simple delimited file with a reference to another file into a BLOB
Samuel;Johnson;35;C:/temp/Samuel_Johnson.txt
David;Palmer;43;C:/temp/David_Palmer.txt

Note the tMap conversion in the first capture.
Regards,
One Star

Re: [resolved] File into BLOB

This results in
java.sql.SQLException: Unable to convert between java.io.File and JAVA_OBJECT.
Any thoughts? I can do this in Java by copying byte arrays around but it is very slow.
Community Manager

Re: [resolved] File into BLOB

Hi
java.sql.SQLException: Unable to convert between java.io.File and JAVA_OBJECT.

Which version of TOS did you use? Can you succeed in create a simple job as mhirt did?
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] File into BLOB

I solved this by creating by creating a small routine. I can then call it as a function in the tMap
public static Object ByteArrayFromFile(java.io.File file) {
try
{
java.io.FileInputStream fis = new java.io.FileInputStream(file);
int fileLength = (int) file.length();
byte[] incoming_file_data = new byte; // allocate byte array of right size
fis.read(incoming_file_data, 0, fileLength ); // read into byte array
fis.close();
return incoming_file_data;
}
catch(Exception err)
{
err.printStackTrace();
return null;
}

Works well in 2.2.2 and now 2.2.3
One Star

Re: [resolved] File into BLOB

Hi All,
i am struggling with the process of inserting image in sql server using talend. I have even gone through the process mentioned help.talend.com.
As i am new to talend so i dont have much idea. can anybody help me with detailed process of doing so.
Thanks & Regards
Four Stars

Re: [resolved] File into BLOB

Hi,

 

I've solved it by adding a tJavaRow with following content, before tMap:

 

java.io.File file = new java.io.File(((String)globalMap.get("tFileInputRaw_1_FILENAME_PATH")));
java.io.FileInputStream fis = new java.io.FileInputStream(file);
int fileLength = (int) file.length();
byte[] incoming_file_data = new byte[fileLength]; // allocate byte array of right size
fis.read(incoming_file_data, 0, fileLength ); // read into byte array
fis.close();
output_row.content = incoming_file_data;

I attach sample job screenshot.

 

1) tFileList loops through files to load

2) tFileInputRaw reads each file as Object

3) tJavaRow converts Object to byte[]

4) tMap retrieves byte[] 

5) tOracleOutput stores byte[] column as BLOB DB internal type

 

Hope it helps!