Re: Interesting error thrown on SQL with text field that containsadate

Hi Pete,

First, I sent my response at 2:48 PM EDT, but in my mail it didn't show
up 'til 11:29 PM? Don't know why.

My mention of the date format business was in the nature of trying to
remove possible bug sources. I don't like those sorts of things in
production because they amount to on-the-fly patches that might not work
elsewhere. As I mentioned, the anomaly in handling there brought up
similarities.

I think Dan may have something regarding the CCSID. You can see in
other places, like here:

http://www.iprodeveloper.com/forums/aft/148085

that people have unexpectedly seen CCSID 65535 rear its ugly head.
Incidentally, my comment:

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

had exactly to do with CCSID 65535 handling, and the conversation was at
COMMON in the '90's.

However, I don't agree that "your select statement works fine as a dynamic, but fails with the exact same values as a prepared statement" is expected behavior. That may well be what is happening, but I would consider it a bug. I would expect instead that the CCSID 65535 issue would show up in both instances. Some JTOpen folk used to comment here from time to time; be nice to hear from the horse's mouth.

You might try a

CAST('xxx' AS CHAR(20) CCSID 37)

or similar to try to eliminate the CCSID 65535 element altogether.

I'll hope this gets there in less than 8 1/2 hours. ;-)


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: Thursday, July 19, 2012 1:44 AM
To: Java Programming on and around the IBM i
Subject: Re: Interesting error thrown on SQL with text field that
containsadate

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