Image files into BLOB

One Star

Image files into BLOB

Hello,
I have a list of jpg files I need to put in BLOB fields.
The name of the file corresponds to the ID of the datas to which the images owned. So I need to get the name of the jpg file, to compare it to the ID in the database and then to set the jpg file contains for the ID in the database.
I have written this job, but I have the following error (see attached files).
I think it's coming from the settings 'File Name' of the tFileInputDelimited_3.
Is the way of my job correct or am I totally wrong?
How can I do that?
Thanks
I use TOS 3.0.4
One Star

Re: Image files into BLOB

I think there should be a "iterate" link between the tFileList and the tFileInputDelimited_3.
Then, the tFileInputDelimited is not a good idea to put the content of an image file in a BLOB : the image file contains binary data whereas tFileInputDelimited is reading text data.
You'll probably have to create your own tFileInputBinary component.
One Star

Re: Image files into BLOB

Hello,
regarding that post http://talendforge.org/forum/viewtopic.php?id=1753, I'm not totally wrong. I agree that it will not works using the tFileInputDelimited_3.
But I don't know how to get in the same job (transaction) the name of the file to join it with the ID of PER_PERSONNE and the binary contain of the file. I must have twice in the input of the tMap_3.
If I put a "iterate" link between the tFileList and the tFileInputDelimited_3 I can't then put a link from the tMap_1 to tMap2 ou tMap_3.
I have the same problem If I put a link between the tFileProperties_2 and the tFileInputDelimited_3
Could somebody help me?
Thanks
Community Manager

Re: Image files into BLOB

Hello mamouroux
From the error mesage, I see the error come from tFileInputDelimited_1, but I don't see this component in your job.
Best regards

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

Re: Image files into BLOB

Hello,
Sorry, I have found how to do, but now I have an other error:
Exception in component tOracleOutput_1
java.sql.SQLException: Type de colonne non valide
This error has been already explained in that topic,
http://talendforge.org/forum/viewtopic.php?id=1543
but I doesn't know how to use setBlob instead of setObject.
I can't change anything in the code.
Please could you explian me how to do that?
Thanks
Community Manager

Re: Image files into BLOB

Hello
But I don't know how to create one. And, is it possible to call it from the tMap ?

Go go Repository-->Code-->Routine and right click on 'Routine', then select 'create a new routine'.
For example, if you create a new routine call:
Forum6799 and there is a method in it: ByteArrayFromFile
  package routines;
public class Forum6799 {
public static Object ByteArrayFromFile(String filePath) {
try {
java.io.FileInputStream fis = new java.io.FileInputStream(filePath);
byte[] incoming_file_data = new byte;
fis.read(incoming_file_data);
fis.close();
return incoming_file_data;
} catch (Exception err) {
err.printStackTrace();
return null;
}
}
}

Assuming the job looks like:
tFileInputDelimited--row1--tMap--tMysqlOutput
On tMap, set the talend type as Object and db type as Blob, call the routine on expression:
Forum6799.ByteArrayFromFile(row1.filePath)
Let me know if you have any questions!!
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star pac
One Star

Re: Image files into BLOB

Hello, thx for the reply !
but now I have a java.lang.ClassCastException: [B cannot be cast to oracle.sql.BLOB
Community Manager

Re: Image files into BLOB

Hello
Please upload some screenshots of your job, so I can see where do you make a mistake.
Best regards

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

Re: Image files into BLOB

btw, I use TOS 3.1.1 and I previously this problem : http://www.talendforge.org/forum/viewtopic.php?id=6367 so I tried the solutions descrbied there and also the solution of http://www.talendforge.org/bugs/view.php?id=7556 (http://talendforge.org/trac/tos/changeset/25260)
One Star pac
One Star

Re: Image files into BLOB

Here are screenshots of the job and inside the tMap

In the tMap I have hardcoded the path of the pdf to insert as a blob for test purpose.
Thx for your help
One Star pac
One Star

Re: Image files into BLOB

I have some news :
I have to modify the file TOS-All-r24830-V3.1.1\plugins\org.talend.designer.components.localprovider_3.1.1.r24830\components\templates\db_output_bulk.skeleton around line 755 because it uses setBLOB which isnt valid
If try to just rename it to setBlob then the generated code is :
pstmt_tOracleOutput_1.setBlob(39,(oracle.sql.BLOB) dict_info.DICTFR_PIECE_JOINTE);

then i got this problem :
Exception in component tOracleOutput_1
java.lang.ClassCastException: ) dict_info.DICTFR_PIECE_JOINTE),((byte[])dict_info.DICTFR_PIECE_JOINTE).length);

and I got this error :
Exception in component tOracleOutput_1
java.sql.SQLException: Connexion interrompue
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)
at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:3127)
at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:3042)
at dict.test_0_1.test.tFileList_1Process(test.java:3734)
at dict.test_0_1.test.tOracleConnection_1Process(test.java:369)
at dict.test_0_1.test.runJobInTOS(test.java:6458)
at dict.test_0_1.test.main(test.java:6359)

but this time the row is inserted in oracle and i'm able to get the pdf with oracle sqldevelopper.
Community Manager

Re: Image files into BLOB

Hello
java.sql.SQLException: Connexion interrompue

The previous connection haven't been closed when you iterate the next file and create a new connection. Generally, you are required to use a tOracleCommit to close the connection after the job.
Add a tOracleCommit after tOracleOutput,
.........tOracleOutput--OnComponentOK-->tOracleCommit
Best regards

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

Re: Image files into BLOB

thx a lot, it now works !