Problems with CLOB and JDBC

[ingo.klose]

Hi all,

We have a problem to read data from a CLOB field and I hope someone can tell me what part of the source code we could change to solve the problem.

We have the following case. We use a generic JDBC connection that connects via a VJDBC driver to an Oracle DB. When we try to read a CLOB field, we only get the class reference and not the actual content as string a string value. 

Funny enough it works, when using the generic JDBC connection with an Oracle driver. It also works when using the same VJDBC-driver in a different tool, such as DB Visualizer. We guess the problem must be hidden somewhere in the PDI implementation for generic JDBC connections with unknown JDBC drivers.

Do you guys have an idea where we can look to fix the problem?

Thanks and best regards,

Ingo

[samatar hassan]

Hi ingo I remember we had an oracle db years ago and we use getclobval to return the content of xml type. What is missing in kettle is to handle xml type as a native type ..like int, string... Envoyé depuis Yahoo Mail pour Android

---

From: ingo.klose <ingo....@googlemail.com>;
To: <kettle-d...@googlegroups.com>;
Subject: [kettle-developers] Problems with CLOB and JDBC
Sent: Thu, Jul 31, 2014 2:14:49 PM

-- You received this message because you are subscribed to the Google Groups "kettle-developers" group. To unsubscribe from this group and stop receiving emails from it, send an email to kettle-develop...@googlegroups.com. To post to this group, send email to kettle-d...@googlegroups.com. Visit this group at http://groups.google.com/group/kettle-developers. For more options, visit https://groups.google.com/d/optout.

[Matt Casters]

Hi Ingo,

Since we have pluggable data types now you want to look at:

ValueMetaBase.getValueFromSQLType():

        case java.sql.Types.CLOB:

          valtype = ValueMetaInterface.TYPE_STRING;

          length = DatabaseMeta.CLOB_LENGTH;

          isClob = true;

          break;

This detects a CLOB as a String.

Where it might go wrong is in getValueFromResultSet(): where we do a simple resultSet.getString(index+1);

While I'm 100% sure this used to work fine for Oracle they might have changed their minds in the mean time meaning we might have to put logic in there to read the String through an InputStream.

No idea why the Generic driver works and the Oracle doesn't.  Perhaps it's related to OracleDatabaseMeta:

  @Override

  public int getMaxVARCHARLength() {

    return 2000;

  }

Good luck!
Matt

--

Matt Casters <mcas...@pentaho.org>
Chief Data Integration, Kettle founder, Author of Pentaho Kettle Solutions (Wiley)
Fonteinstraat 70 - 9400 OKEGEM - Belgium - Cell : +32 486 97 29 37

Pentaho  -  Powerful Analytics Made Easy

[ingo.klose]

Hi guys,

Thanks a lot for the suggestions. I'll let you know if we could solve the problem.

Best regards,

Ingo