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

["Joe Sam Shirah" <joe_sam@xxxxxxxxxxxxx>]

   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