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

This works to do what you're asking

create view rjsflow.test as
select cast(substring(PROCESSID, 1, 7) as varchar(20) CCSID 37)as x
from rjsflow.processmaster

Yours should be something like

create view asaapfiles/vwPunches as
select
(row_number()
over(partition by xrefempid
order by strdate,xrefempid,strtime) + 1) /2
as setNbr
, a.*,
cast(substr(strdate,7,4)||'-'||substr(strdate,1,5) as char(10) CCSID 37) as srtdate
from tblpunches a

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx [mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
Sent: Thursday, July 19, 2012 11:29 AM
To: Java Programming on and around the IBM i
Subject: Re: Interesting error thrown on SQL with text field that containsa date

Ding,ding, ding...a winner! DSPFFD shows that the SRTDATE column is the ONLY field that has a CCSID of 65535. So how do I fix it? Sounds like I need to coerce the column to be CCSID 37. Do I need to change the "create view"? It currently looks like this:

create view asaapfiles/vwPunches as
select
(row_number()
over(partition by xrefempid
order by strdate,xrefempid,strtime) + 1) /2
as setNbr
, a.*,
substr(strdate,7,4)||'-'||substr(strdate,1,5) as srtdate
from tblpunches a

Pete Helgren
Value Added Software, Inc
www.petesworkshop.com
GIAC Secure Software Programmer-Java

On 7/19/2012 8:23 AM, Dan Kimmel wrote:

If you can, go to a green screen and DSPFFD. The CCSIDs show for every column. If you have the system CCSID set to 37 (or whatever it is for your locale), all the columns will usually default to 37. If everything is still at 65535, the columns will default to 65535. 65535 means no translation at all, ie, binary. I've had this problem with MAPICS tables. MAPICS has all the translation built into their programs and they override all the columns to 65535. We have to explicitly CAST any columns we retrieve from a MAPICS table to get them translated.

As you said the dates were stored in character fields, I think Sam is off-base with his notes on date translation. It won't happen unless the columns are typed as date or timestamp. "date format=iso;time format=iso;" will have no effect on columns that are char or varchar.

Dynamic sql does a whole lot less type checking. For the most part the entire statement is simply passed to the database server as a string for interpretation there. Prepared statements, on the other hand, have to "leave room" for the value of the appropriate type to be inserted. So your select statement works fine as a dynamic, but fails with the exact same values as a prepared statement.

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
Sent: Thursday, July 19, 2012 12:45 AM
To: Java Programming on and around the IBM i
Subject: Re: Interesting error thrown on SQL with text field that
containsa date

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=nas125a815068ae87925862
5
6aaa0054f5fb

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(tm) 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(Delega
t
ingPreparedStatement.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.