f



how do you update a java.sql.date type in an ms access database

when i update a row in an access database the date field does not
update to the correct date it brings up some funny date which i dont
know where it comes from. Below is the method i  use to update. maybe
the date types are not compatable?

thanks in advance,

 public static void bookIN(int custID, int rNumber,java.sql.Date
bkInDate ){

         try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            String filename = "hotelDB.mdb";
            String database = "jdbc:odbc:Driver={Microsoft Access
Driver (*.mdb)};DBQ=";
            database+= filename.trim() + ";DriverID=22;READONLY=true}";

            Connection con = DriverManager.getConnection( database
,"","");

            int yes = 1;
            Statement s = con.createStatement();

            s.executeUpdate("update Rooms set CustomerId
="+custID+",BookINDate ="+ bkInDate +",Occupied = "+yes+" where RoomNo
= "+rNumber+" ");


            s.close();
            con.close();
         }
             catch (Exception err) {
               System.out.println("ERROR: " + err);
            }
      }

0
busanil (1)
11/9/2005 8:15:32 PM
comp.lang.java.help 13339 articles. 0 followers. Post Follow

4 Replies
1323 Views

Similar Articles

[PageSpeed] 56

"chose" wrote...

> when i update a row in an access database the date field
> does not update to the correct date it brings up some
> funny date which i dont know where it comes from.

It comes from db's attempt to parse an invalid date-string...

In Access/Jet, it needs to be enclosed within #:s, and with the date parts 
in a specific order...

Another thing is that java.sql.Date only contains the date part, whereas the 
DateTime data type in Access/Jet is more like a java.sql.Timestamp.

> Below is the method i  use to update. maybe
> the date types are not compatable?

Well, partly, but you're not using the date AS a date in the SQL statement, 
but as a String. This will in most cases lead to errors, as the parsing of 
such is left to the database, not the driver...

> s.executeUpdate("update Rooms set CustomerId=" + custID
>   + ", BookINDate = " + bkInDate + ", Occupied = " + yes
>   + " where RoomNo = " + rNumber + " ");


It's really bad practice to concatenate the string for an SQL statement in 
that way, because of differences between different dbs. Instead you should 
use a parameterized statement.

Use java.sql.Timestamp instead of java.sql.Date.
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Timestamp.html
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Date.html

Read up on how to use a PreparedStatement, and it will solve your problem.

http://java.sun.com/j2se/1.5.0/docs/api/java/sql/PreparedStatement.html

// Bjorn A


0
bjorn_abelli (300)
11/9/2005 8:49:33 PM
On 9 Nov 2005 12:15:32 -0800, "chose" <busanil@gmail.com> wrote,
quoted or indirectly quoted someone who said :

>when i update a row in an access database the date field does not
>update to the correct date it brings up some funny date which i dont
>know where it comes from.

note that there are two date classes, java.util.Date  and
java.sql.Date.
-- 
Canadian Mind Products, Roedy Green.
http://mindprod.com Java custom programming, consulting and coaching.
0
11/10/2005 12:52:04 AM
"Roedy Green" wrote...
> "chose" wrote:

>> when i update a row in an access database the date field
>> does not update to the correct date it brings up some
>> funny date which i dont know where it comes from.
>
> note that there are two date classes, java.util.Date  and
> java.sql.Date.

If you had written "Date classes" instead of "date classes" I would agree... 
;-)

But there are actually *more* than two date classes.

In connection to JDBC, there are *four* "date"-classes to deal with.

- There's our usual java.util.Date.

Then there are the three classes derived from java.util.Date

- java.sql.Date *derives* from java.util.Date,
  but "truncates" the "time part".

- java.sql.Time *derives* from java.util.Date,
  but "truncates" the "date part".

- java.sql.Timestamp *derives* from java.util.Date,
  but extends it with an attribute for nanoseconds.

I'm not sure of the reasons behind Suns decision to name java.util.Date to 
only "Date", as it can cause much confusion, as in *many* other environments 
a "Date type" is just the date. I've met many developers that were confused 
when they couldn't find a java.util.Time class... ;-)

// Bjorn A


0
bjorn_abelli (300)
11/10/2005 11:53:52 AM
Bjorn Abelli wrote:

> I'm not sure of the reasons behind Suns decision to name java.util.Date to 
> only "Date", as it can cause much confusion, as in *many* other environments 
> a "Date type" is just the date. 

Like in unix shells - bash for example? Guess what "time" and "date" do for you 
there. ;-)
0
igoplan (47)
11/10/2005 12:35:22 PM
Reply: