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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
the java code is now in cvs
simply check out the mdbtools-java module to obtain the latest code
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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*
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
The latest code can be downloaded from:
https://sourceforge.net/tracker/index.php?func=detail&aid=960527&group_id=2294&atid=102294
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
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
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.
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.
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)
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.
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*
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 ?
"found a non fully qualified column" happens with a query like "select mycolumn from mytable".
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.