MSAccess, rs.next() and SELECT DISTINCT

[mine0926]

Hi, I am trying to load unique dates base on data in MSAccess. Everything is fine except when I load it, when I get its last row it gives me 12 (meaning I have 12 total result), but on the third line (3rd rs.next()) it give me error message. I try to query it in MSAccess application itself and it only return 2 result.
TABLE Sales' Data

|----------------------------------------|
| OR_Number | Date_Sale |    Cashier     |
|----------------------------------------|
|   0001    | 9-26-2010 | foo            |
|   0002    | 9-26-2010 | foo            |
|   0003    | 9-26-2010 | foo            |
|   0004    | 9-26-2010 | foo            |
|   0005    | 9-26-2010 | foo            |
|   0006    | 9-26-2010 | foo            |
|   0007    | 9-26-2010 | foo            |
|   0008    | 9-27-2010 | bar            |
|   0009    | 9-27-2010 | bar            |
|   0010    | 9-27-2010 | bar            |
|   0011    | 9-27-2010 | bar            |
|   0012    | 9-27-2010 | bar            |
|----------------------------------------|

CODE

    public ArrayList<Object> loadAvailableDates(String path)
    {
        System.out.println(path);
        ArrayList<Object> avDates = new ArrayList<Object>();
        //Create Connections of transactions
        Connection con = null;

        try
        {
            System.out.println("Starting...; CLASS=TransactionTables.java; "
                    + "METHOD=loadAvailableDates()");

            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            con = DriverManager.getConnection("jdbc:odbc:Driver="
                    + "{Microsoft Access Driver (*.mdb)};"
                    + "DBQ=" + path + "SalesTable.mdb");
            System.out.println("CONNECTED - Temporary Sales");

            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            [b]//This query should return 2 rows only since I use "DISTINCT"[/b]
            ResultSet rs = st.executeQuery("SELECT DISTINCT CDATE(Date_Sale) "
                    + "FROM Sales_Table");
            [b]//I add this line to know how many rows it return[/b]
            rs.last();
            int totalRow = rs.getRow();
            System.out.println("EXPECTED ROW: " + totalRow);
            int curRow = 1;

            rs.beforeFirst();
            while(rs.next())
            {   System.out.println("Currently in row: " + curRow);
                avDates.add(rs.getObject(1)); [b]//this it line 62[/b]
                curRow++;
            }
            System.out.println("LOADED - Temporary Sales");
            rs.close();
            con.close();
            System.out.println("CLOSED - Temporary Sales");
        }
        catch(ClassNotFoundException cnfEx)
        {
        }
        catch(SQLException sqlEx)
        {   sqlEx.printStackTrace();
        }
        finally
        {
            try
            {
                if(con != null){con.close();   }
            }
            catch(SQLException sqlEx)
            {   sqlEx.printStackTrace();
            }
        }

        return avDates;
    }

OUTPUT and ERROR MESSAGE

Z:\UpdateOnUsb\Files\
Starting...; CLASS=TransactionTables.java; METHOD=loadAvailableDates()
CONNECTED - Temporary Sales
EXPECTED ROW: 24
Currently in row: 1
Currently in row: 2
Currently in row: 3
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
        at sun.jdbc.odbc.JdbcOdbc.SQLGetDataStringTimestamp(JdbcOdbc.java:4210)
        at sun.jdbc.odbc.JdbcOdbcResultSet.getDataStringTimestamp(JdbcOdbcResultSet.java:5805)
        at sun.jdbc.odbc.JdbcOdbcResultSet.getTimestamp(JdbcOdbcResultSet.java:1052)
        at sun.jdbc.odbc.JdbcOdbcResultSet.getObject(JdbcOdbcResultSet.java:1724)
        [b]at FlashDriveUpdate.TransactionTables.loadAvailableDates(TransactionTables.java:62)[/b]

Hope someone can pointout what is my error.
Thanks in advance.

[Tolls]

What happens if you don't do last() and simply cycle round the resultset?

Also what happens if you just use a normal resultset (don't set any parameters in createStatement()) and no last()?

I suspect it's the limitations of the driver/access when it comes to scrollable cursors.

[masijade]

Seeing as how the Bridge does not support them.

[Tolls]

I didn't think it did, but didn't know if there was another driver that could be used.

The answer to most Access questions does seem to be "you can't do that" though...:)

[mine0926]

What happens if you don't do last() and simply cycle round the resultset?
Also what happens if you just use a normal resultset (don't set any parameters in createStatement()) and no last()?

It give me same error message.

I suspect it's the limitations of the driver/access when it comes to scrollable cursors.

I guess the answer here I cant do it. Maybe, java and MSAccess is not reaaly a good combination. :(

Thanks for your replies.



Anyone that can help me here?

[masijade]

Maybe MSAccess is just not a very good DB (and there is no maybe about that).

Edit: And, maybe, MS should produce a JDBC driver for it if they want it to be seen as a db to be used with Java and not depend on the JDBC-ODBC Bridge, or at least produce a good JDBC-ODBC Bridge (since ODBC is their creation), and there is also no maybe about those.

[mine0926]

Maybe MSAccess is just not a very good DB (and there is no maybe about that).

Edit: And, maybe, MS should produce a JDBC driver for it if they want it to be seen as a db to be used with Java and not depend on the JDBC-ODBC Bridge, or at least produce a good JDBC-ODBC Bridge (since ODBC is their creation), and there is also no maybe about those.

Hahaha :)

and huhuhu for me. I use MSAccess on my past projects, when I was learning programming, but I am using VB6.0 that time.
Now, I am trying to convert it to Java. You think I should not continue this conversion of my past projects? These
projects do lots of copy-paste of database.

[mine0926]

On method loadAvailableDates(String path) I remove DISTINCT keyword. I load all the dates and use HashMap to enumerate duplicate dates.

[Tolls]

Considering you have available, for free, MySQL, Derby (which comes with the JDK), SQLLite, along with free versions of SQLServer and Oracle...using Access makes little sense (beyond the "I've used it before").

[masijade]

Well, truth be told, I don't find SQLLite much better than Access (at least as far as SQL standards go), and if your going to include that you just well include SleepyCat (a further develop of the old Berkley DB). Both are still better than Access (not the original Berkley DB, I mean, as that was nothing more than a glorified hashtable ;-) ).

[Tolls]

Have to say I've never used it. The name just popped into my head, and (as you've shown) if you miss one out then people start suggesting "what about...".
:)

So my aim to preempt that failed spectactularly!

My suggestion initially would be to start with Derby (since it's there in the JDK) to get used to proper SQL, after Access. Then move onto one of MySQL, or the freebie SQL Server or Oracle...but only bother with those if you plan on doing this in anger. If you;re doing this to just get the idea of what a JDBC app looks like then Derby is more than sufficient, and it has a proper driver and all.

[mine0926]

masijade and Tolls - Thank you for the help and suggestions. After I read your replies I decided to convert these past projects using Java and MySQL. I am just worrying for the processes of copy-paste. I have read in some website that MySQL is not ideal if the database will be copied because it is created to work in network and uses log file. But I was thinking for a solution such as creating a .txt file and that will be the file to be copied and send to its branches. Just want this to be discussed.

Thanks again. :)

[Andri_JT]

The SQL SELECT DISTINCT Statement In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.
The DISTINCT keyword can be used to return only distinct (different) values.

I think because of the DISCTINT DATE FIELD, only contains TWO?

[mine0926]

Yeah, it should return ONLY TWO row, and that is what I am expecting to be returned from rs.getRow() but in the output it returns
12 row which is obviously not correct. If I use other database such as MySql it works perfect. So I guess masijade and Tolls are
correct, that the problem is with the Odbc driver.

:)