Talend community forum / Working with CLOB/BLOB data types - old Talend issue

archenroot · 2010-11-09 12:30:43

Tags: [Blob, bug, clob, database, error management, object, oracle, string]

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.

[statistics] connecting to socket on port 3546
[statistics] connected
[trace] connecting to socket on port 4546
[trace] 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)
[statistics] disconnected
[trace] disconnected
Job Testovaci_Vygeneruj_FDAVKU ended at 12:08 09/11/2010. [exit code=1]
_________________________________________________________________________________________________________________________

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/viewto … 742#p31742
http://www.talendforge.org/forum/viewtopic.php?id=1418
http://talendforge.org/forum/viewtopic. … 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:oracle:thin:@//server.local:1521/prod";
// jdbc:oracle:thin:@//host:port/service
// or
// String url = "jdbc:oracle:thin:@server.local:1521:prodsid";
// jdbc:oracle:thin:@host:port:SID
//
// 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:oracle: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.

archenroot · 2010-11-09 15:03:38

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/datab … 91264.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:oracle: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[1];
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

archenroot · 2010-11-10 11:17:34

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

archenroot

Announcement

Unanswered posts

#1 2010-11-09 12:30:43

Working with CLOB/BLOB data types - old Talend issue

#2 2010-11-09 15:03:38

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

#3 2010-11-10 11:17:34

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

Board footer