Results 1 to 14 of 14
  1. #1
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    4

    Default MSAccess, rs.next() and SELECT DISTINCT

    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
    Java Code:
    |----------------------------------------|
    | 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
    Java 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
    Java Code:
    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.
    Last edited by mine0926; 01-20-2011 at 01:14 AM.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    19

    Default

    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.

  3. #3
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

    Seeing as how the Bridge does not support them.

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    19

    Default

    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...:)

  5. #5
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    4

    Default

    Quote Originally Posted by Tolls View Post
    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.

    Quote Originally Posted by Tolls View Post
    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?

  6. #6
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

    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.

  7. #7
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    4

    Default

    Quote Originally Posted by masijade View Post
    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.

  8. #8
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    4

    Default

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

  9. #9
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    19

    Default

    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").

  10. #10
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

    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 ;-) ).

  11. #11
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    19

    Default

    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.

  12. #12
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    4

    Default

    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. :)

  13. #13
    Andri_JT is offline Member
    Join Date
    Jan 2011
    Posts
    10
    Rep Power
    0

    Default

    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?

  14. #14
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    4

    Default

    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.

    :)

Similar Threads

  1. how to handle data over two distinct socket connection
    By krishforever in forum New To Java
    Replies: 7
    Last Post: 02-03-2011, 10:57 AM
  2. Replies: 9
    Last Post: 08-25-2010, 07:31 AM
  3. How to connect to msaccess?
    By azzaiel in forum New To Java
    Replies: 1
    Last Post: 06-28-2009, 02:19 AM
  4. How to retain value in struts 2 using <s:select></s:select> tag
    By SaiPrasad@Sella in forum Web Frameworks
    Replies: 0
    Last Post: 02-09-2009, 07:23 AM
  5. MSAccess and hibernate
    By bbq in forum JDBC
    Replies: 3
    Last Post: 04-30-2008, 04:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •