RE: Interesting error thrown on SQL with text field that contains a date -- JAVA400-L

CCSID 65535. JT400 pulls the record description from the table on the i. CCSID 37 would be varChar, CCSID 65535 is varBinary.

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx [mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
Sent: Wednesday, July 18, 2012 12:37 PM
To: Java Programming on and around the IBM i
Subject: Re: Interesting error thrown on SQL with text field that contains a 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(Delegati
ngPreparedStatement.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.

--
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.