Re: Interesting error thrown on SQL with text field that containsa date

Thanks. I am a little uneasy with the "date format=iso;time format=iso;" approach only because I don't know if I have any code that is currently relying on another format (by accident), but I might try it and see what happens. Most of my formatting of dates is to/from strings only because most of this code results in either HTML or JSON which is text anyway. But it is worth I try.

I actually think that maybe the issue is on the DB2 side of things because the column is the result of a view that does some substringing and concatenation. DB2 may be creating something other than a varChar from the result.....

Dan Kimmel mentioned CCSID on the column. How do I see what the CCSID is on the column? All I see is varChar when I use interactive SQL and prompt for the field.

Pete Helgren
Value Added Software, Inc
www.petesworkshop.com
GIAC Secure Software Programmer-Java

On 7/18/2012 12:48 PM, Joe Sam Shirah wrote:

Hi Pete,

That's pretty much what I thought would happen. The
NumberFormatException here:

java.sql.SQLException: Data type mismatch. (class
java.lang.NumberFormatException)

tells you that it thinks it is dealing with a number. I'd say that's
bugland, especially since you say it works as an ordinary statement, as
opposed to a prepared statement. BTW, to be sure, did you try the regular
statement in code, as opposed to interactive SQL? Just wondering if it's a
bug in PreparedStatement or throughout JTOpen JDBC. I would also try
greater than and less than, using character value comparison, in place of
BETWEEN to see if that worked.

Something that did strike me is that your date string is in ISO format.
To me that's a good thing, *but* for some reason unknown to me, JTOpen and
the Toolbox have never ( unless recently changed ) followed the JDBC
java.sql.Date.toString() specification. See:

http://docs.oracle.com/javase/7/docs/api/java/sql/Date.html#toString()

then:

http://www-01.ibm.com/support/docview.wss?uid=nas125a815068ae8792586256aaa0054f5fb

which says:

"The Toolbox JDBC driver returns string conversions formatted as specified
in the operating system job options. Applications not written specifically
for the System i™ system may not expect this format...

Applications that require ISO formats or JDBC-defined string conversions
must set the toolbox connection properties that control the date and time
format to ISO. For example, the following URL sets date and time to use ISO
formats (the separator properties are not used when ISO format is
specified):"

"jdbc:as400://RCHASSLH;date format=iso;time format=iso;"


So, crazy as it sounds, I would give specifying the date format as above
a try, just to see if there was any impact. *Something* is making it think
ps1.setString(2, "2012-06-06"); is a number, and if that "fixes" it, you
would at least know the bug area.

Beats me why they would never change that, but I saw something similar
in the i18n area long ago and they wouldn't budge.

Hope I haven't sent you to far afield, but the similarities struck me.


Joe Sam

Joe Sam Shirah - www.conceptgo.com (904) 302-6870
conceptGO - Consulting/Development/Outsourcing
Java Filter Forum: www.ibm.com/developerworks/java
Just the JDBC FAQs: www.jguru.com/faq/JDBC
Going International? www.jguru.com/faq/I18N
Que Java400? www.jguru.com/faq/Java400



-----Original Message-----
From: Pete Helgren
Sent: Wednesday, July 18, 2012 1:37 PM
To: Java Programming on and around the IBM i
Subject: Re: Interesting error thrown on SQL with text field that containsa
date

That just moves the Exception to the first setString. It *thinks* that
parameter 2 is a varBinary for some reason. I am not familiar enough
with the driver to figure out how it evaluates the parameter types but I
am walking through the code (and there is a lot of it in the jt400
driver....)

It's interesting but also a complete show stopper. I *could*
dynamically build the SQL string temporarily but it would open the code
up to SQL injection over the long haul.

Pete Helgren
Value Added Software, Inc
www.petesworkshop.com
GIAC Secure Software Programmer-Java

On 7/18/2012 8:34 AM, Dan Kimmel wrote:

Try setting 2, 3, 4 instead of 1, 2, 3. 1 is the return value parameter. I
don't know why it should think you have a return value, but apparently it
does.

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
Sent: Wednesday, July 18, 2012 1:22 AM
To: Java Programming on and around the IBM i
Subject: Interesting error thrown on SQL with text field that contains a
date

I have a table that carries date values as strings in varchar columns.
A select statement like this:

select * from vwPunches v,tbltclocs l
where v.clockname = l.clockname and location = 'GARAGE' and srtDate
between '2012-06-06' and '2012-06-21'

Works fine. If I parameterize it with a statement like this:

String sql = select * from vwPunches v,tbltclocs l where v.clockname =
l.clockname and location = ? and srtDate between ? and ?

And then execute it like so:

ps1 = conn.prepareStatement(sqlSelectPunchErrors);
ps1.setString(1, "GARAGE");
ps1.setString(2, "2012-06-06");
ps1.setString(3, "2012-06-21");

The second setString ( ps1.setString(2, "2012-06-06");) throws this error:

java.sql.SQLException: Data type mismatch. (class
java.lang.NumberFormatException)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:521)
at
com.ibm.as400.access.SQLVarcharForBitData.set(SQLVarcharForBitData.java:100)
at
com.ibm.as400.access.AS400JDBCPreparedStatement.setValue(AS400JDBCPreparedStatement.java:2817)
at
com.ibm.as400.access.AS400JDBCPreparedStatement.setString(AS400JDBCPreparedStatement.java:2470)
at
org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:132)

srtDate is defined as a varChar(10) not a number.....

Seems like the JT400 driver is attempting to convert the date to/from
bit data (I think). The column using the between operator is a varChar
and the parameter values are Strings. I don't see how that is causing the
Exception to be thrown.

A bug perhaps?

--
Pete Helgren
Value Added Software, Inc
www.petesworkshop.com
GIAC Secure Software Programmer-Java