topic Working with CLOB/BLOB data types - old Talend issue in Talend Studio

Working with CLOB/BLOB data types - old Talend issue

Anonymous — Sat, 16 Nov 2024 13:12:48 GMT

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:
https://community.talend.com/t5/Design-and-Development/CLOB-transfer-into-String-VARCHAR2/td-p/83308
http://www.talendforge.org/forum/viewtopic.php?pid=31742#p31742
https://community.talend.com/t5/Design-and-Development/Problem-with-un-CLOB-oracle/td-p/64557
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 = "jdbc racle:thin:@//server.local:1521/prod";
// jdbc racle:thin:@//host ort/service
// or
// String url = "jdbc racle:thin:@server.local:1521 rodsid";
// jdbc racle:thin:@host ort ID
//
// 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 = "jdbc racle: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.

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

Anonymous — Tue, 09 Nov 2010 14:03:38 GMT

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 = "jdbc racle: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

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

Anonymous — Wed, 10 Nov 2010 10:17:34 GMT

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