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
--
This is the Java Programming on and around the IBM i (JAVA400-L) mailing list To post a message email: JAVA400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/java400-l.
midrange.com
