Problem mapping char type to CHAR via JDBC

Posted November 14th, 2006 by mwalker

I am developing an extremely simply DataRush application that loads the contents of a flat file into a Derby database via WriteToJDBC. I am having difficulty writing CHARs to the db.

An excerpt from the outputType of the ReadDelimitedText operator instance I'm using:
ds2RecordType=(record (int 'CUSTOMERID' ) ... (char 'GENDER' ))

An excerpt from my prepareStatement:

CREATE TABLE customers
    (CUSTOMERID INT NOT NULL,
     ...,
     GENDER CHAR(1),
     PRIMARY KEY(CUSTOMERID)
     )

Using this setup, I receive the following error:

    [junit] Testcase: testDs2ToDerby took 3.646 sec
    [junit]     Caused an ERROR
    [junit] Ds2ToDerby
    [junit] com.pervasive.dataflow.dev.DFCompositeException: Ds2ToDerby{Ds2ToDer
by.write.WriteToJDBC_0=java.lang.RuntimeException: SQL Exception writing data}
    [junit] Ds2ToDerby.write.WriteToJDBC_0
    [junit] java.lang.RuntimeException: SQL Exception writing data
    [junit]     at com.pervasive.dataflow.operators.io.jdbc.WriteToJDBCProcess.d
oRun(WriteToJDBCProcess.java:115)
    [junit]     at com.pervasive.dataflow.operators.io.jdbc.WriteToJDBCProcess.r
un(WriteToJDBCProcess.java:63)
    [junit]     at com.pervasive.dataflow.executor.OperatorTask.runTask(Operator
Task.java:192)
    [junit]     at com.pervasive.dataflow.executor.Task.run(Task.java:93)
    [junit] Caused by: java.sql.SQLException: An attempt was made to get a data
value of type 'CHAR' from a data value of type 'java.lang.Character'.
    [junit]     at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLExceptio
n(Unknown Source)
    [junit]     at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown
Source)
    [junit]     at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown
Source)
    [junit]     at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknow
n Source)
    [junit]     at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Un
known Source)
    [junit]     at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Un
known Source)
    [junit]     at org.apache.derby.impl.jdbc.EmbedPreparedStatement.dataTypeCon
version(Unknown Source)
    [junit]     at org.apache.derby.impl.jdbc.EmbedPreparedStatement.setObject(U
nknown Source)
    [junit]     at com.pervasive.dataflow.executor.ObjectInputImpl.setValue(Obje
ctInputImpl.java:343)
    [junit]     at com.pervasive.dataflow.operators.io.jdbc.WriteToJDBCProcess.d
oRun(WriteToJDBCProcess.java:89)
    [junit]     ... 3 more

Simply switching char to string and CHAR(1) to VARCHAR(1) solves the problem.

Is this an issue with the WriteToJDBC operator, or am I doing something wrong here?

Trackback URL for this post:

http://www.pervasivedatarush.com/trackback/60
‹ Boxing scalars in records Oddity with ReadDelimitedText's notion of newlines? ›
On November 16th, 2006 jfalgout said:

The SQL type CHAR does not map to the Java char type. In SQL, CHAR actually represents a fixed length string. Varchar represents a varying length string. The exception is coming from a java.sql class that is attempting to do the mapping and one does not exist.

As you suggested, using a java.lang.String type should solve the problem. The Java string type should work for either CHAR or VARCHAR.