Problem with JDBC/EDBC date/time field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ronaldo Vieira - 27 Apr 2005 14:03 GMT

Hi all ! I am with the following problem. I am trying to retrieve some
records in a data base CA-Ingres, using JDBC.EDBC driver.
The code that I am executing is the code below.

StringBuffer sql = new StringBuffer() ;
sql.append("SELECT *') ;
sql.append("FROM myTable ");
sql.append("WHERE myDateTimeField = ? ") ;
sql.append("AND myStringField = ? ") ;
PreparedStatement pstmt = getConnection().prepareStatement(sql.toString());
pstmt.setTimestamp(1,vo.getMyTimeStampValue()) ;
pstmt.setString (2,vo.getMyStringValue()) ;
ResultSet rs ;
rs = pstmt.executeQuery() ;
if (rs.next()) System.out.println("YES !!!");
else System.out.println("NO ...");

The values that are being passed for clausula WHERE are correct. The SQL
statment is :
SELECT * FROM myTable WHERE myDateTimeField = '2005-1-17 16:58:48'
AND myStringField = 'WEEMINAX638NSRQTMUZCIQF5hECGU3Y2WH5H'

and this statment works fine !!!!
Then, what of it has made a mistake with this code?
Please give examples. Thanks in advance, your help is appreciated.

Ronaldo.

Reply to this Message

Michael Leo - 27 Apr 2005 18:25 GMT

Ronaldo,

Try using setDate() instead of setTimestamp(). JDBC dates
are always "fun".

Michael Leo Java, J2EE, BEA WebLogic,
Caribou Lake LLC Oracle, Open Source, Ingres,
mleo@cariboulake.com Real Enterprise Applications

>Hi all ! I am with the following problem. I am trying to retrieve some
>records in a data base CA-Ingres, using JDBC.EDBC driver.
[quoted text clipped - 23 lines]
>
>Ronaldo.

Reply to this Message

Ronaldo Vieira - 28 Apr 2005 13:37 GMT

Hi, Michel, thanks for your answer.
I try that but don´t work ... :(

Thanks in advance.

Ronaldo.

Reply to this Message

Alex D. Baxter - 27 Apr 2005 22:25 GMT

> StringBuffer sql = new StringBuffer() ;
> sql.append("SELECT *') ;
[quoted text clipped - 17 lines]
> Then, what of it has made a mistake with this code?
> Please give examples. Thanks in advance, your help is appreciated.

You don't say exactly what is going wrong, I assume zero rows are being
returned from the resultset - one thing I can think of is timezone
conversion problems (unless the java.sql.Timestamp you are setting as a
parameter was returned from the same database row earlier on, in which
case you should be OK). As far as I have seen, JDBC always ignores the
II_TIMEZONE setting (on client or server) for timestamps (since
internally they are stored as an millisecond offset, this is fair
enough), but you can get timezone conversion (on the Java client side)
when a returned java.sql.Timestamp is converted to string using the
default toString() method (which uses the system default locale/timezone
settings). So:

1. Don't assume because a query works when you are typing it into a
terminal monitor that it will work by JDBC - there are both from-string
and timezone conversions happening in that case

2. Don't assume that the string representation of a java.sql.Timestamp
output by a Java program is exactly the same as the value being passed
to the database.

3. You either have to be very careful with any timezone conversions
going on between time representations, or retrieve your Timestamps from
the database and use that to compare.

HTH,
Alex

Signature

Alex D. Baxter - <mailto:alex-news@oenone.demon.co.uk>
No HTML in News - see <http://www.usenet.org.uk/ukpost.html>
If you read uk.* newsgroups, please read uk.net.news.announce

Reply to this Message

Ronaldo Vieira - 28 Apr 2005 13:36 GMT

Hi Alex, thanks for your answer.
Yes, the problem is zero rows are being returned from the resultset .
Well, I believe too that the problem is a is timezone conversion problem.
The java.sql.Timestamp that I'm setting as parameter is comming from HTML
page, in a brazilian date / time format ( dd/MM/yyyy HH:mm:ss ), and I´m
using
DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
to make a parse in a date/time parameters that I receive form HTML page. I
don´t using date_format in the URL connection, so I think that using the
default date_format.
If this is the problem, how I can solve that ????

Thanks in advance.

Ronaldo.

Reply to this Message

Alex D. Baxter - 28 Apr 2005 22:50 GMT

> Hi Alex, thanks for your answer.
> Yes, the problem is zero rows are being returned from the resultset .
[quoted text clipped - 4 lines]
> DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
> to make a parse in a date/time parameters that I receive form HTML page.

Step 1 is checking what your Java system default timezone is;
TimeZone.getDefault() should give it to you. For the parse, you can
either set the timezone explicitly on the SimpleDateFormat using
setTimeZone(), or you can append a timezone string to the date/time
string (sorry, I don't know what the code for your location would be).
If you still can't compare in a database select successfully, try
getting the raw milliseconds value for the java.util.Date you are
parsing and for what should be the matching java.sql.Timestamp in the
database (i.e. do the parse, call getTime() to get the milliseconds; do
the select, get the row you think matches and call getTime() on that
timestamp - assuming all conversions are done properly the millisecond
value should match, if not then there is some other timezone conversion
going on).

Alex.

Signature

Alex D. Baxter - <mailto:alex-news@oenone.demon.co.uk>
No HTML in News - see <http://www.usenet.org.uk/ukpost.html>
If you read uk.* newsgroups, please read uk.net.news.announce

Reply to this Message