topic Re: how to retrieve byteArray/blob from Data base? in Talend Studio

how to retrieve byteArray/blob from Data base?

Anonymous — Thu, 31 May 2012 07:35:23 GMT

Hi All,
Using Talend job , I need to retrieve images from data base to local system. Images are stored in DB as byte array.
Could any 1 please guide me on this.

Thanks In Advance
Chaya

Re: how to retrieve byteArray/blob from Data base?

Anonymous — Thu, 31 May 2012 07:46:37 GMT

Hi Chaya
Which DB do you use? What's the data type of the image column?
I guess maybe you have to write a custom routine which can retrieve binary from DB into file.
Regards,
Pedro

Re: how to retrieve byteArray/blob from Data base?

Anonymous — Thu, 31 May 2012 08:52:37 GMT

Thanks Pedro,
I m using postgres, column data type is bytea.
I have referred, one of your post , where in you have given, "BlobDemoRoutine" to load images from local system to Data base by using "ByteArrayFromFile" method.
I want to do exactly reverse of this. If I play around with fileOutPutSteam and byte array in java routine that should get me the images back.. am I rite?

Re: how to retrieve byteArray/blob from Data base?

janhess — Thu, 31 May 2012 08:54:02 GMT

You probably need base64 encode/decode.
See https://community.talend.com/t5/Design-and-Development/resolved-Send-file-to-WebService/td-p/106745.
I've also posted a number of other solutions for images.

Re: how to retrieve byteArray/blob from Data base?

Anonymous — Thu, 31 May 2012 09:21:56 GMT

package routines;
public class Write {
	
	 
	public static void ByteArrayToFile(byte[] input, String filepath) {
	  try 
	       {   
	            java.io.File fp = new java.io.File(filepath);
	            java.io.FileOutputStream fos = new java.io.FileOutputStream(fp);
	            fos.write(input);
	            fos.close();
	       }
	       catch(Exception err)
	       {
	           err.printStackTrace();
	           
	       }
	}
}

Create a job tPostgresqlInput--main-->tJavaRow.
Regards,
Pedro

Re: how to retrieve byteArray/blob from Data base?

Anonymous — Thu, 31 May 2012 10:54:27 GMT

Thanks a lot Pedro & janhess.
Wel Pedro,
I just did exactly what ever you said. Job runs with out any error.. & also a .png is created in the specified path
But When I try to open the image in the specified path, I cant see the image, its shows as error message content is corrupted or damaged..
Am I doing something wrong. Please correct me.

Thanks
Chaya

Re: how to retrieve byteArray/blob from Data base?

Anonymous — Thu, 31 May 2012 10:57:43 GMT

Hi Chaya
Are you sure that the suffix of this image is png?
Maybe it is jpg or other file.
Regards,
Pedro

Re: how to retrieve byteArray/blob from Data base?

Anonymous — Thu, 31 May 2012 11:00:08 GMT

Hey Pedro,
Yes I m sure, The file is .PNG only
I reconfirmed also, by uploading 1 .png to DB and again trying to download the same.

Re: how to retrieve byteArray/blob from Data base?

janhess — Thu, 31 May 2012 11:09:56 GMT

I think you can read it in as an object type and map this to an object typ output in a tMap. No coding required.

Re: how to retrieve byteArray/blob from Data base?

Anonymous — Thu, 31 May 2012 11:13:34 GMT

Hi Chaya
I test it and it works fine.
Can you save a new image into DB?
We need to make sure the data in DB is correct.
Regards,
Pedro

Re: how to retrieve byteArray/blob from Data base?

Anonymous — Thu, 31 May 2012 11:28:05 GMT

No Luck Pedro,
I tried uploading new image to DB (even different formats) & retried to download but no luck. Issue remains the same.
Should I mail You the complete job or upload the screen shots ?

Re: how to retrieve byteArray/blob from Data base?

Anonymous — Thu, 31 May 2012 11:30:28 GMT

I think you can read it in as an object type and map this to an object typ output in a tMap. No coding required.

Hi janhess,
Thanks.. Could You Please elaborate ur approach, though i read it like an object, how can I write that object with out coding... please explain

Re: how to retrieve byteArray/blob from Data base?

janhess — Thu, 31 May 2012 11:56:01 GMT

Sorry just map a byte array input to a byte array output.

Re: how to retrieve byteArray/blob from Data base?

Anonymous — Thu, 31 May 2012 12:16:56 GMT

Thanks janhess ..
Still, I continue to face the same issue, Please fine the attachment. In which I have uploaded the image which I m trying to read from DB and also the job flow.
Thanks for ur time..

Re: how to retrieve byteArray/blob from Data base?

Anonymous — Thu, 31 May 2012 12:33:41 GMT

Hi Team,
Some where I m going wrong, either reading or may be while uploading to DB itself.
So, please look in to the attachments for the way I am uploading my image to DB.
& I have already posted the way I m reading it. So, please let me know in case if i m wrong.
Routine Code:
public static byte[] ByteArrayFromFile(String filepath) {
try
{
java.io.File file=new java.io.File(filepath);
java.io.FileInputStream fis = new java.io.FileInputStream(file);
int fileLength = (int) file.length();
byte[] incoming_file_data = new byte;
fis.read(incoming_file_data, 0, fileLength );
fis.close();
return incoming_file_data;
}
catch(Exception err)
{
err.printStackTrace();
return null;
}
}

Please help me on this.,
Thanks

Re: how to retrieve byteArray/blob from Data base?

janhess — Thu, 31 May 2012 15:22:24 GMT

You didn't specify a field length in your output. Should be the same as input.
I use this to import images to a database.
The rule in the tMap is ImageUtil.getByte(row6.abs_path) where abs_path is the path to the image file
package routines;
import java.io.FileInputStream;
import java.io.File;
import java.io.FileNotFoundException;
public class ImageUtil {
public static byte[] getByte(String filepath) {
byte[] b=null;

try {
FileInputStream fis = new FileInputStream(filepath);
b=new byte;
fis.read(b);
} catch (Exception e) {
e.printStackTrace();
}
return b;
}
}