0

I am developing a simple desktop Java application to retrieve newly added records from MS Access database and put those in to Mysql and I am gonna make this as a scheduled task.

I have a JButton and when i click that button, the records those created today in MS Access has to be fetched and display in to my JTable, this is the purpose.

The problem I am facing is trying to retrieve records based on current date from MS Access. I am using Java8 and using "ucanaccesss" driver for establishing connection between my application and MS Access. I get date mismatched errors. 

try {                                         
            // TODO add your handling code here:

            SimpleDateFormat objSDF = new SimpleDateFormat("dd-mm-yyyy");
            java.util.Date dt_1 = objSDF.parse("06-12-2019");
            //String date6 = objSDF.format(dt_1);

            try {

                Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");

                String msAccDB = "C:/Users/t1/Dropbox/P2002/p2002.mdb";
                String dbURL = "jdbc:ucanaccess://" + msAccDB;
                DefaultTableModel model1 = new DefaultTableModel();
                jTable1.setModel(model1);
                model1.setColumnIdentifiers(new Object[]{"ID", "Datum", "Summe"});
                Connection li = DriverManager.getConnection(dbURL);

                PreparedStatement pstm = li.prepareStatement("SELECT ID,Datum,Summe FROM Bes WHERE Datum = #"+dt_1+"#");

                ResultSet Rs = pstm.executeQuery();

                while (Rs.next()) {
                    model1.addRow(new Object[]{Rs.getInt(1), Rs.getString(2), Rs.getDouble(3)});
                }
                li.close();
            } catch (ClassNotFoundException cnfe) {
                System.out.println(cnfe);
            } catch (SQLException sqle) {
                System.out.println(sqle);
            }

        } catch (ParseException ex) {
        Logger.getLogger(GetRecords.class.getName()).log(Level.SEVERE, null,ex);
    }***
Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
Thiv
  • 9
  • 4
  • I recommend you don’t use `SimpleDateFormat` and `Date`. Those classes are poorly designed and long outdated, the former in particular notoriously troublesome. Instead use `LocalDate` and `DateTimeFormatter`, both from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). – Ole V.V. Dec 20 '19 at 20:34
  • You may find partial answers in [SimpleDateFormat ignoring month when parsing](https://stackoverflow.com/questions/3056703/simpledateformat-ignoring-month-when-parsing) and/or [java date problem in parsing](https://stackoverflow.com/questions/4966652/java-date-problem-in-parsing). You should also use a `?` placeholder in your query string and transfer the parameter using `pstm.setObject(1, yourLocalDate);`. I would expect this to solve your problem. – Ole V.V. Dec 20 '19 at 20:38
  • Further related question: [Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2](https://stackoverflow.com/questions/43039614/insert-fetch-java-time-localdate-objects-to-from-an-sql-database-such-as-h2). – Ole V.V. Dec 20 '19 at 20:40
  • Hi, thanks for the reply.... i tried to use the placeholder value as ?. but still i get error as " net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.1 incompatible data type in conversion " – Thiv Dec 21 '19 at 15:42
  • 2
    A possible explanation is that your UCanAccess version doesn’t yet support JDBC version 4.2. That^s the version that specifies that you can pass a `LocalDate` (and other types from java.time). I’m wondering a bit though, I thought that all drivers were JDBC 4.2 compliant by now. – Ole V.V. Dec 21 '19 at 15:49
  • Any other way to retrieve newly created records(Crrent day) from MS Access?? – Thiv Dec 21 '19 at 16:03
  • If `Datum` has type `date` in Access and `pstm.setObject(1, yourLocalDate);` gives the exception you quoted, then you must use `pstm.setDate(1, Date.valueOf(yourLocalDate));` where `Date` is `java.sql.Date`. – Ole V.V. Dec 21 '19 at 16:15
  • But it works for only for one date which hasn't got time in the date tab in MS Access. – Thiv Dec 21 '19 at 16:32
  • I believe that you had better edit your question and tell us more precisely what the datatype in MS Access is and what your data looks like there. – Ole V.V. Dec 21 '19 at 16:46
  • Date column is Date/Time type.......looks like "27/03/2017 17:14:57" – Thiv Dec 21 '19 at 17:24
  • But the query works for " 07/12/2019 " – Thiv Dec 21 '19 at 17:25
  • 1
    UCanAccess 4.0.4 does not support `java.time`. I have submitted the required changes to [the UCanAccess repository](https://sourceforge.net/p/ucanaccess/git-code/) as version 5.0, but they have not been released yet. – Gord Thompson Dec 21 '19 at 20:16
  • Thanks, @GordThompson. Would Thiv then need to pass a `Timestamp` to retrieve the row where the `Datum` is 27/03/2017 17:14:57, for example? I would expect that to work. And I would still use either a `LocalDateTime` or an `Instant` and `Timestamp.valueOf(myLocalDateTime)` or `Timestamp.from(myInstant)` for conversion to `Timestamp`. – Ole V.V. Dec 22 '19 at 10:51
  • 1
    @OleV.V. - Yes, that would probably be the best approach for now. – Gord Thompson Dec 22 '19 at 12:44
  • Hi, thanks for the comment, but I was trying to fetch the records which crated on that date, so I need to ignore the time when each record created, so if i pass the the Timestamp then, it will search for that particular time too??? Am i right about it??? – Thiv Dec 27 '19 at 12:52
  • 1
    @OleV.V. - UCanAccess 5.0.0 was released today. It supports `java.time.LocalDateTime`. – Gord Thompson Jan 08 '20 at 18:36

0 Answers0