× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.




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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.