Working with CLOB/BLOB data types - old Talend issue

Six Stars

Working with CLOB/BLOB data types - old Talend issue

Hello,
In my scenarion I use tOracleSP used to call oracle function. One of the returned parameter is type of CLOB on the database side. When I use type String for saving CLOB value into it, it is ok, but the variable is empty. But based on information bellow I schould use Object instead of String data type. In that case (Object) error occurs:
_________________________________________________________________________________________________________________________
Starting job Testovaci_Vygeneruj_FDAVKU at 12:08 09/11/2010.
connecting to socket on port 3546
connected
connecting to socket on port 4546
connected
Exception in component tOracleSP_1
java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3433)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:125)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:283)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:363)
at fnkv_ip.testovaci_vygeneruj_fdavku_0_1.Testovaci_Vygeneruj_FDAVKU.tFixedFlowInput_1Process(Testovaci_Vygeneruj_FDAVKU.java:3373)
at fnkv_ip.testovaci_vygeneruj_fdavku_0_1.Testovaci_Vygeneruj_FDAVKU.tOracleConnection_1Process(Testovaci_Vygeneruj_FDAVKU.java:3812)
at fnkv_ip.testovaci_vygeneruj_fdavku_0_1.Testovaci_Vygeneruj_FDAVKU.runJobInTOS(Testovaci_Vygeneruj_FDAVKU.java:4458)
0|5D335BE1F6FB955683BF7125C5618E9B|FN KRÁLOVSKÉ VINOHRADY ZEMAN Marek, MUDr.|Fakultní nemocnice|ZPMV ?R Pobo?ka PRAHA a ST?EDNÍ ?ECHY|U Výstavi?t? 287 / 17|Praha 7 - Hole?ovice|170 00|200062||
at fnkv_ip.testovaci_vygeneruj_fdavku_0_1.Testovaci_Vygeneruj_FDAVKU.main(Testovaci_Vygeneruj_FDAVKU.java:3866)
disconnected
disconnected
Job Testovaci_Vygeneruj_FDAVKU ended at 12:08 09/11/2010.
_________________________________________________________________________________________________________________________
Also the strange look of stack output appears always. There are output mixed with exception messages.
I found similar topics over the forum, but there is no solution of these issues:
http://www.talendforge.org/forum/viewtopic.php?id=3952
http://www.talendforge.org/forum/viewtopic.php?pid=31742#p31742
http://www.talendforge.org/forum/viewtopic.php?id=1418
http://talendforge.org/forum/viewtopic.php?pid=5449#p5449
I am able to find in menu Window -> Preferences -> Talend -> Specific Settings -> Metadata of TalendType the record "mapping_Oracle.xml". Based on information from this file I found something strange:
In case of Java language there is defined following statement:
<language name="java">
<talendToDbTypes>
<talendType type="id_Object">
<dbType type="BLOB" default="true"/>
<dbType type="CLOB"/>
<dbType type="BFILE"/>
<dbType type="XMLTYPE"/>
<dbType type="ROWID"/>
<dbType type="UROWID"/>
</talendType>
</talendToDbTypes>
<dbToTalendTypes>
<dbType type="CLOB">
<talendType type="id_Object" default="true" />
</dbType>
</dbToTalendTypes>
So the mappings is O.K. CLOB schould be retrieved from database as Object java type. I cannot say if default java Object type is able to keep this type of data. Is it?
What I though about the possible solution:
Call database function/procedure inside of Talend routine and when dealing with CLOBs database type use "java.sql.Clob". Something like following code:
public static String CLOBToString(){
Class.forName("oracle.jdbc.driver.OracleDriver");
//
// or
// DriverManager.registerDriver
// (new oracle.jdbc.driver.OracleDriver());
String url = "jdbcSmiley Surprisedracle:thin:@//server.local:1521/prod";
// jdbcSmiley Surprisedracle:thin:@//hostSmiley Tongueort/service
// or
// String url = "jdbcSmiley Surprisedracle:thin:@server.local:1521Smiley Tonguerodsid";
// jdbcSmiley Surprisedracle:thin:@hostSmiley TongueortSmiley FrustratedID
//
// SID - System ID of the Oracle server database instance.
// By default, Oracle Database 10g Express Edition
// creates one database instance called XE.
// ex : String url = "jdbcSmiley Surprisedracle:thin:@myhost:1521:xe";
Connection conn = DriverManager.getConnection(url,"scott","tiger");
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
// Select LOB locator into standard result set.
ResultSet rs = stmt.executeQuery ("SELECT blob_col, clob_col FROM lob_table");
while (rs.next())
{
// Get LOB locators into Java wrapper classes.
java.sql.Blob blob = (java.sql.Blob)rs.getObject(1);
java.sql.Clob clob = (java.sql.Clob)rs.getObject(2);
(...process...)
}

OR
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{? = call ReturnCLOB()}");
ocs.registerOutParameter(1, OracleTypes.CLOB);
ocs.execute();
oracle.sql.CLOB clob = ocs.getCLOB(1);
There is also question about using java.sql or oracle.sql type library?
As soon as I wasn't able to see any working solution about working with CLOBs neither BLOBs in Talend, I thank you for any response.
Six Stars

Re: Working with CLOB/BLOB data types - old Talend issue

Well,
I spent again some time with seeking the solution for component tOracleSP (even other components) work with CLOB, but without success. Then I wrote small piece of code which is able to write returned CLOB data to file. I will of course edit it to make it able work as Talend routine with some In/Out parameters.
Here is the current working code in Eclipse (you need Oracle JDBC drivers which provides advanced functions agains standard JDBC which can be downloaded here http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html), as soon as I have some working example for Talend, I will post it here too:
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
public class testhash {
public static void main(String[] args) throws IOException,
NoSuchAlgorithmException, SQLException {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//
// or
// DriverManager.registerDriver
// (new oracle.jdbc.driver.OracleDriver());
String url = "jdbcSmiley Surprisedracle:thin:@//172.28.140.110:1521/XE";
Connection conn;
conn = DriverManager.getConnection(url, "fnkvip", "oracle");
if (conn != null) {
System.out.println(" connection good");
} else {
System.out.println(" connection failed");
}
conn.setAutoCommit(false);
// This is calling my oracle packaged function
OracleCallableStatement ocs = (OracleCallableStatement) conn
//? = call PACKAGE_NAME.FUNCTION_NAME(parameter1,parameter2,...parameterN)
.prepareCall("{? = call ROZHRANI_S_POJ.VRAT_XML_FDAVKU(?,?,?,?,?,?,?,?,?,?)}");
// Set up parameters, each char '?' in prepareCall has its index number, based on this
// , I will work with these as input or output parameters

// Set up input parameters
ocs.setString(2, "5D335BE1F6FB955683BF7125C5618E9B");
ocs.setString(3, "FN KRÁLOVSKÉ VINOHRADY ZEMAN Marek, MUDr.");
ocs.setString(4, "Fakultní nemocnice");
ocs.setString(5, "ZPMV ?R Pobo?ka PRAHA a ST?EDNÍ ?ECHY");
ocs.setString(6, "U Výstavi?t? 287 / 17");
ocs.setString(7, "Praha 7 - Hole?ovice");
ocs.setString(8, "170 00");

// Output parameters
// Each function on Oracle database has its return value, and this is it
ocs.registerOutParameter(1, OracleTypes.NUMBER);
// Return of entity primary key
ocs.registerOutParameter(9, OracleTypes.NUMBER);
// and here finally come 2 CLOBS parameters out
ocs.registerOutParameter(10, OracleTypes.CLOB);
ocs.registerOutParameter(11, OracleTypes.CLOB);
// Execute the statement
ocs.execute();

oracle.sql.NUMBER numberPkFaktura = ocs.getNUMBER(9);
oracle.sql.CLOB clobFDAVKA = ocs.getCLOB(10);
// For testing I use only the last returned parameter
oracle.sql.CLOB clobXMLFDAVKA = ocs.getCLOB(11);

// And now I want to write returned CLOB data for example into a file
BufferedWriter outFile = new BufferedWriter(new FileWriter("E://FDAVKA.out"));
java.io.BufferedReader in = new java.io.BufferedReader(clobXMLFDAVKA.getCharacterStream());
char[] buffer = new char;
int bytes_read;
StringBuffer stringBuffer = new StringBuffer();
while ((bytes_read = in.read(buffer)) != -1) {
stringBuffer.append(new String(buffer, 0, bytes_read));
}
outFile.write("" + stringBuffer);
outFile.close();
System.out.println(stringBuffer.toString());
}
}
Best regards,
archenroot
Six Stars

Re: Working with CLOB/BLOB data types - old Talend issue

I created bug report at http://talendforge.org/bugs/view.php?id=16994 related to this issue
archenroot

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Downloads and Trials

Test drive Talend's enterprise products.

Downloads