mdbtools is being ported to java

2004-05-02
2013-05-01
1 2 > >> (Page 1 of 2)
  • Why?
    I needed a way to read an access database from java and found the java -> jdbc -> odbc -> mdbtools.odbc - mdbtools core   to be too much of a hassle, both for setting up and runtime. 

    Instead I opted for a 100% java solution and started to port mdbtools to 100% java.  This allows for simple setup (ie just ensure the jar file is on the classpath) and development (no new api) and rumtime (if mdbtools crashes all you have to do is catch the exception)

    How was this done?
    My approach is to simply port the c code with minimal changes and then run the programs in util to ensure the ported code works, and then to implement a jdbc driver that uses the ported code.

    Status:

    Right now i got the mdb-tables file ported to java and enough of the libmdb code to enable mdb-tables to work.  (ie from java I can get a list of table names)  I also have a jdbc driver that accepts the query:
    list tables
    and hands back the table names. 

    Code:
    As an example: to get all the table names from an access database simply do:
          Class.forName("mdbtools.jdbc.Driver");
          Connection conn = DriverManager.getConnection("jdbc:mdbtools:" + filename);
          Statement stmt = conn.createStatement();
          ResultSet rset = stmt.executeQuery("list tables");
          while (rset.next())
            System.out.println(rset.getString(1));

    What's next:
      Next I'm going to work on getting mdb-schema ported and allow the jdbc driver to accept:
    desc table_name
    and pass back the schema for the table

    After that I'll get mdb-export to work.  At this point a java program can open an access database and dump out all of the data.

    Version:
    I'm using the 0.5 version that i downloaded last week.
    Once I get the mdb-export to work I'll see about synching to head

    Help out?
    If you would like the driver to further test the port just let me know

     
    • To contact me send a message to: calvinrsmith@hotmail.com and put java-mdbtools is the subject

       
    • mdb_schema is ported
      I can now list and describe tables from pure java

       
    • mdb-export is now ported

      it is now possible using pure java to:

      open an access database
      iterate over all the tables
      dump the schema
      dump the data

      Next:
        What's next you say?  Well the way I see it there is the sql engine and the GUI stuff. 

      SQL:
        I'll get a simple sql engine up that can parse: select ... from ... 

      GUI:
        I don't really want to port the GUI as that will just create yet another java frontend to a databsae.  Instead I'll work on getting the JDBC driver up to speed and test it against squirrel (http://squirrel-sql.sourceforge.net) 
         This gives an excellent UI to acces without having to write a UI.  Plust it tests out the JDBC driver

       
    • Status update:

      Using only the JDBC api I can:
      open the database
      list all the table
      dump the schema
      dump the data

      The sql engine can only do: select column_list from table

      Squirrel can be used to load the database, and list the tables.  It can't list the columns or the data.  not sure why.  i'm looking into this next.

      The code is now ready for prime time testing for someone that only needs to dump the data (say for example to insert into another database)

       
    • listing the tables and dumping the schema uses the JDBC meta data API's instead of idea in the post of accepting non sql quiries (list tables,desc table)

       
    • Hello.

      Maybe you should try to integrate it with the Apache POI project (jakarta.apache.org/poi). They have a very good support for Excel documents and are adding support to Word documents, also they have a very good support for loading/saving documents in the base format used in Word and Excel.
      I don't think that MDB files are in the same base format as Word and Excel, but manipulating MDB files can be part of the same suite, and POI can ve a central repository to get Java support for all office files.
      By the way your work will be very usefull to the comunity. Thanks.

      Rivas.

       
    • Rivas,
        The POI project really is about OLE documents of which acess is not.

       
    • I can now use Squirrel to obtain a list of tables and from each table I can get the schema and data.
      I now have one less reason to run acess

      :)

      What's next?
         Not sure, This now does what I needed so it's ready for other people to grab and start testing.  I'm hoping I can get cvs write access so I can check this code in.
         If your using my code and find it helpfull please let me know.

       
    • status update:

      6/1/2004  modified sql parser to accept: select function (ie count, max, min...) from table
      6/3/2004  modified sql engine to execute: select count(*) from table
      6/5/2004  modified sql engine to support count, max min
      6/5/2004  modfied sql parser to accept where field = value
      6/5/2004  modified sql engine to support where field = value

       
    • the java code is now in cvs
      simply check out the mdbtools-java module to obtain the latest code

       

    • Anonymous
      2004-07-09

      Any plans to support write access as well?
      I am thinking of exporting data from oracle to access on a linux box using jdbc, so people can download and view data without a complex database system.
      Anyway, great work you are doing.

       
    • Right now i'm not looking for write support, instead i'm focussing on usable read support

       
    • Nathan Egge
      Nathan Egge
      2004-08-02

      This is exactly what I was looking for.  However after downloading the source from CVS and building the jar file, the following select statement:

        SELECT * FROM users

      would give me this error:

        java.sql.SQLException: only equation is implemented
              at mdbtools.jdbc.Engine.executeWhere(Unknown Source)
              at mdbtools.jdbc.Engine.executeSelect(Unknown Source)
              at mdbtools.jdbc.Engine.execute(Unknown Source)
              at mdbtools.jdbc.MDBStatement.executeQuery(Unknown Source)

      I know that this library is in development, but any help would be appreciated.

       
    • James Stansell
      James Stansell
      2004-10-08

      The problem is in Engine.java - executeWhere only knows how to do "equation" matching, and a null "where" object isn't that type.

      I'm preparing to upload a patch that fixes it (and also a resultSetMetaData problem.)

      -james.

       
    • James Stansell
      James Stansell
      2004-10-08

      See https://sourceforge.net/tracker/index.php?func=detail&aid=1043280&group_id=2294&atid=302294 for the "no where clause" patch.

      For some reason squirrel-sql on windows isn't able to connect, even though mdbtools.jdbc.Test works just fine.  I haven't figured it out yet. (Win/2k, squirrel-sql 1.2beta6, java 1.4.2_05-b04)

       
    • James Stansell
      James Stansell
      2004-10-10

      I got squirrel-sql working fairly well on Linux (Debian i386 - both Blackdown 1.3.1 and Sun 1.4.2).  I'll try to upload a patch in the next few days.  (found a few issues in squirrel I guess I should report, too.)

      I'll have access to Windows again during the week; hopefully my changes will work fine there, too.

      I created an mdb file that I can read the table and column names, but actually reading the data (including a count(*)) produces an array-index out-of-bounds exception.  I think I used File -> New -> MS Access Application, then opened it with Access 2002 and created a table with 3 or 4 columns, including an auto-increment ID, a Text and an Integer.  Exporting the table into an MDB created with the ODBC manager lets me read the data without problems.  Can anyone reproduce the error based on these simple notes?

      TODO:
      - make work with isql-viewer
      - make work with OpenOffice.org
      - make work with Octopus ETL tool

      Regards,
      -james.

       
    • James Stansell
      James Stansell
      2004-10-11

      Still no luck with Squirrel-sql (either the stable or dev versions) on Win/2K.  Very perplexing.

      I think the bug I mentioned above is caused by letting MSAccess add a primary-key ID column to the table.  The name is showing up as the first column but the definition and data show up in the last column.  Hmm... since I can read the data in this case, maybe it's really a different bug.  I'll log a report for this one at least.

       
    • I've commited your patch: [ 1043280 ] mdbtools-jdbc: no where clause
        Also i've commited a package called: mdbtools.jdbc2   This new package is a new driver that completly replaces mdbtools.jdbc and uses the package mdbtools.dbengine for sql execution.  This new driver should do everything the old driver did and also adds support for order by and constraints like (x = 1 and x = 2)
        I tested this new driver against open office (on linux) and was able to make a connection, get the list of tables, but when I click the table I get back the correct number of columns for the table but the data in the first column is repeated for each column.  but a great start

       
    • just did a quick test with isql viewer and it didn't work, need more methods implemented :( *anyone want to fix it? *

      I'm going to leave the driver as it's 'good enough for now' and will refactor the sql engine to use an exection tree which will simplify the engine, then i'll be able to implement join :) *yeah*

       
    • James Stansell
      James Stansell
      2004-10-13

      The "repeated first column" problem happens in executeQuery where selectedColumnNames is being built.  Needs to use columns.get(j).

      The "found a non fully qualified column" exception is happening there too, when you don't select *.  The  parser doesn't return FQColumn objects for "select col from ...".  It apparently does for "select table.col from ..." but then the selectedColumnNames is not being populated.

       
    • thanks!  openoffice.org now works

      when do you see: found a non fully qualified column  ?

       
    • James Stansell
      James Stansell
      2004-10-14

      "found a non fully qualified column" happens with a query like "select mycolumn from mytable".

       
    • James Stansell
      James Stansell
      2004-10-14

      I have uploaded patch 1046857 with miscellaneous improvements for the original jdbc driver.  I'm not sure yet if the new driver could benefit from any of them.

       
1 2 > >> (Page 1 of 2)