The Artima Developer Community
Sponsored Link

Java Answers Forum
Major Problem!!!

16 replies on 2 pages. Most recent reply: Oct 7, 2005 3:08 PM by Sharad Thakur

Welcome Guest
  Sign In

Go back to the topic listing  Back to Topic List Click to reply to this topic  Reply to this Topic Click to search messages in this forum  Search Forum Click for a threaded view of the topic  Threaded View   
Previous Topic   Next Topic
Flat View: This topic has 16 replies on 2 pages [ 1 2 | » ]
pranjal

Posts: 9
Nickname: dutta
Registered: Oct, 2005

Major Problem!!! Posted: Oct 3, 2005 4:56 AM
Reply to this message Reply
Advertisement
I am facing a great problem in connecting and retreiving data from a MSAccess(.mdb) file to PostgresSQL under LINUX platform.I used Jackcess library newly but i was unable to work with it.Could u all please suggest how to use Jackcess and so if u all have any idea under such requirement please get back to me.

Requirement is "How to read data and transfer it from .mdb file to postgressSQL under Linnux using java."

Please help me .Waiting 4 reply .

Thanks in advance.
dutta


Kondwani Mkandawire

Posts: 530
Nickname: spike
Registered: Aug, 2004

Re: Major Problem!!! Posted: Oct 3, 2005 5:27 AM
Reply to this message Reply
Suggestion:

Depending how long you have to complete the task, why don't you
just write a module that takes in a Driver Class Name,
uses SQL and takes in Server Names (the URL). That way
when you're moving it over to any platform or a platform
with any backend database, all you have to do is download
a Postgres JDBC driver (for instance) and configure the
classpath - to point to the Driver.

Could take you a day or could take you several dependent
on how many tables you have etc. Though it would be once
off if you modularize it properly. Unless you go across
the database programatically, checking what tables it
has - as opposed to doing so manually. I had code for
this a while back but that's probably in my laptop, and the
hassle of transferring it to my desktop is unfortunately
something I'm not prepared to do.

Good luck,
Kondwani

pranjal

Posts: 9
Nickname: dutta
Registered: Oct, 2005

Re: Major Problem!!! Posted: Oct 3, 2005 8:56 PM
Reply to this message Reply
hi,
Actually i want a free ware and the drivers are expensive.
So ,please provide me a link for a
"PostgresSQL JDBC driver" under LINUX platform.

Thanks for reply
dutta

Kondwani Mkandawire

Posts: 530
Nickname: spike
Registered: Aug, 2004

Re: Major Problem!!! Posted: Oct 3, 2005 11:48 PM
Reply to this message Reply
> hi,
> Actually i want a free ware and the drivers are
> expensive.

That's not entirely true. As long as they are
JDBC based they are usually free actually.
The postgres-jdbc driver, the mysql-jdbc driver,
even the oracle one (if I'm not mistaken).

> So ,please provide me a link for a
> "PostgresSQL JDBC driver" under LINUX platform.
>

http://jdbc.postgresql.org/
A 10 second Google would have given you that link
though.

Remember you will have to write your own modules
that simply make use of the postgres driver.
I hope you're not under the impression that these
drivers will be your solution.

Good luck,
Spike

pranjal

Posts: 9
Nickname: dutta
Registered: Oct, 2005

Re: Major Problem!!! Posted: Oct 4, 2005 3:54 AM
Reply to this message Reply
hi ,

Are you sure that JDBC-PostgresSQL Driver will work on LINUX platform for retrieving data from .mbd files? How is it possible to connect to .mdb under LINUX using JDBC-PostgresSQL Driver.

Please reply ASAP.
Thanks for ur previous replies.
Dutta

Kondwani Mkandawire

Posts: 530
Nickname: spike
Registered: Aug, 2004

Re: Major Problem!!! Posted: Oct 4, 2005 4:34 AM
Reply to this message Reply
OK I think I din't explain it fully. You will have to suck out
info from your old database using the JDBC-ODBC driver that comes
with Java. Then you port it to your postgresql database using
postgres-JDBC and a few SQL commands.

Picture you create a JFrame similar to this:

**************************************************
* Porting from: <JComboBox> MSAccess *
**************************************************
* Driver: <JComboBox> JdbcOdbcDriver *
**************************************************
* Url From: <JTextField>sub-prot.etc *
**************************************************
* Porting To: <JComboBox> Postgres *
**************************************************
* Driver: <JComboBox> postgres-jdbc *
**************************************************
* Url To: <JTextField> url-to.etc *
**************************************************

Now You create static connections to keep the MSAccess Connection open and accessible
as follows:

MyConnection.getConnection();

so that you never lose it and have to reconnect each time.

At the same token you keep the Connection to the receiving end open in a similar format.
I think default postgres port is 5432 (if I'm not mistaken - well at least on my
machine).

Play around with the modules via Read/Write operations - you may use
ResultSets (to read) and PreparedStatements to write, or via some
other quicker method you can think of.

Now that's the more detailed version of it.

Good luck,
Spike

Kondwani Mkandawire

Posts: 530
Nickname: spike
Registered: Aug, 2004

Re: Major Problem!!! Posted: Oct 4, 2005 5:10 AM
Reply to this message Reply
Example something like this would be the heart and soul of the application:
public void dumpResultSet(ResultSet rs, String table){
   StringBuffer insertStr = new StringBuffer("insert into "+table+"(");
   while(rs.next()){
     ResultSetMetaData rsmd = rs.getMetaData();
     int numCols = rsmd.getColumnCount();
     int i = 0;
     while(i < numCols){
        insertStr.append(rsmd.getColumnName(i)+", ");
     }
     //  then eliminate the last "," at the end of the buffer
     //  - I'm too lazy to go through the StringBuffer API and figure this out
     insertStr.append(")values(");
     i = 0;
     while(i < numCols){
        String colType = rsmd.getColumnTypeName(i);
        if(colTypeName.equals("text");
           insertStr.append("'"+rs.getString()+"',");
        else if(colTypeName.equals("integer"))
           insertStr.append("rs.getInt(),");
        //  Continue with however many types are possible in
        //  normal SQL
     }
     //  When done remove the last char which is will be ","
     //  - I'm too lazy to go through the StringBuffer API and figure this out
     //  Obviouosly getPostgresConnection() is a method that returns the
     //  to connection defined
     insertStr.append(")");
     PreparedStatement pstmt = getPostgresConnection().preparedStatement(insertStr.toString());
     
   }
}
 
//  You have a similar method somewhere that returns the ResultSet from you MSAccess database
 
public ResultSet getMSAccessResultSet(String table){
   //  do neccessary stuff via SQL Select * from table, etc...
   return result;
}

Then create your GUI, and inconjunction with whatever class you want your Controller to be.

Good luck again,
Kondwani

Kondwani Mkandawire

Posts: 530
Nickname: spike
Registered: Aug, 2004

Re: Major Problem!!! Posted: Oct 4, 2005 5:24 AM
Reply to this message Reply
Come to think of it that's the foundation of Code
to a decent project (A Java based Database Porter).

You'll be able to toy with it from all sorts of angles
MSAccess to Postgres, MySql to Postgres, vice-versa.
Oracle to someOtherDatabase, the list is endless.

pranjal

Posts: 9
Nickname: dutta
Registered: Oct, 2005

Re: Major Problem!!! Posted: Oct 4, 2005 8:43 PM
Reply to this message Reply
hi ,

u didnot get my requirement.I have to do everything on a LINUX machine.
I went thru your sample code.That fine for first in WINDOWS and porting on LINUX.My Requirement is to "Fetch data from a table from a .mdb file and then insert this data into PostgresSQL on LINUX".There is no Windows here so that i can port it .

Plz reply ASAP.
Thanks for ur previous replies.
With regards
Pranjal

Kondwani Mkandawire

Posts: 530
Nickname: spike
Registered: Aug, 2004

Re: Major Problem!!! Posted: Oct 4, 2005 10:49 PM
Reply to this message Reply
Let me get this straight, you copied over a .mdb file
to a Linux machine, now you want to stick it into
a Postgres database?

Wow, you're in for a task. Why not stick the file back
on a second machine (Windows based - if you have one
available) have your code run off your Linux machine
and do what I suggested.

Other than that, I have no clue how in the world
you would convert an mdb file to a postgres
underlying equivalent - atleast I can't think
of a Java solution. Try a forum that specializes in
MSAccess. Frankly I don't even think its possible
to do what you're asking in Java, sounds like you
would need low level manipulation on the data, and
will have to know the exact structure of an mdb file.

pranjal

Posts: 9
Nickname: dutta
Registered: Oct, 2005

Re: Major Problem!!! Posted: Oct 4, 2005 11:12 PM
Reply to this message Reply
hi


My Database PostGreSQL and the .mdb file both are on the Linux machine.
I donot want to convert the .mdb file to Postgres equivalent." I want only to fetch the data from the .mdb file and enter this data into PostgreSQL on LINUX platform.

U just help me in reading the .mdb file on LINUX machine.I m using Jackcess1.0 library and JDK1.4.2.

PLZ HELP ME IN READING THE .MDB FILE ON A LINUX MACHINE.

WITH REGARDS
DUTTA

Kondwani Mkandawire

Posts: 530
Nickname: spike
Registered: Aug, 2004

Re: Major Problem!!! Posted: Oct 5, 2005 1:54 AM
Reply to this message Reply
I've never used it before but if it is a Library
then the should be an API/Java Doc lingering on
the server of what ever website it is lingering
on. Most come with example usage too.

In any case if Java Doc is not provided, download
the Jar jar -tvf TheJarFile should give you a list
of all the classes in the file that you can use.

javap package.FullClassName will give you all the provided
methods for the class.

If the library was written by a reasonable person with
reasonable method and class names, then this will
help you attain all the info you need to read the .mdb
file.

Nic Passmore

Posts: 2
Nickname: nicjp
Registered: Apr, 2005

Re: Major Problem!!! Posted: Oct 5, 2005 8:22 PM
Reply to this message Reply
One assumes you are tring to use the library located here: http://jackcess.sourceforge.net/. If that is the case, then the author has provided a basic example on that front page, and javadoc here: http://jackcess.sourceforge.net/apidocs/index.html

The FAQ states that the program works with linux, so that shouldnt be a problem. It also notes that the software supports Access 2000 databases only, and not Access 2003.

How about having a look at some of the examples first, and coming back (maybe with code) if you run into problems....

pranjal

Posts: 9
Nickname: dutta
Registered: Oct, 2005

Re: Major Problem!!! Posted: Oct 5, 2005 11:38 PM
Reply to this message Reply
Hi ,
Yes , i want code to read a .mdb file and fetch its
contents on LINUX machine.
I want only the actual data or values from the rows.I don't
want metadata of the tables .I m getting exception in
getting ACTUAL data from the.mdb files.
I would be very gratefull if u send me the code
for"FETCHING OR READING ACTUAL DATA FROM A .MDB FILE".

I m sending u the code and the output list which i m getting
while running this code .Moreover i m using JDK1.4.2
and Jackcess 1.0.

My code which is as below:

import com.healthmarketscience.jackcess.*;
> import java.io.*;
> import java.util.*;
> import java.sql.*;
> import java.nio.*;
>
> class Tester {
> public static void main(String args[]) {
>
> try {
>
> Database d = Database.open(new File("db1.mdb"));
>
> System.out.println ( "--TABLE META DATA---" );
> Set tableNames = d.getTableNames();
> Iterator it = tableNames.iterator();
> int j=0;
> System.out.println ( tableNames.size() );
>
> while( it.hasNext() ) {
> String tableName = it.next().toString();
> System.out.println ( j+":"+tableName );
> Table t = d.getTable( tableName );
> List columns = t.getColumns();
> for (int i=0;i Column c = (Column)columns.get(i);
> System.out.println ( i+"|"+c.getName() );
> }
>
> System.out.println ( "--TABLE ROW VALUES---" );
> Iterator iter = columns.iterator();
> Map row;
> int rowCount=0;
> while ((rowCount++ < Long.MAX_VALUE) && (row =
t.getNextRow()) !=
> null) {
> iter = row.values().iterator();
> while (iter.hasNext()) {
> Object obj = iter.next();
> if (obj instanceof byte[]) {
> byte[] b = (byte[]) obj;
> System.out.print ( "a:"+
ByteUtil.toHexString(ByteBuffer.wrap(b),
> b.length));
> } else {
> System.out.print ( "b:"+ String.valueOf(obj));
> }
> if (iter.hasNext()) {
> System.out.print ( "\t" );
> }
> }
> }
> }
>
> } catch(Exception e) {
> e.printStackTrace();
> }
>
> }
> }

My OUTPUT is as below:
> ----TABLE META DATA---
> 6
> 0 college_admission_form
> java.nio.BufferUnderFlowException
> at java.nio.Buffer.nextGetIndex(Buffer.java:398)
> at
java.nio.Buffer.HeapByteBuffer.get(Heap.java:117)
> at
>
com.healthmarketscience.jackcess.Index$EntryColumn<init>(Index.java )
> at
>
com.healthmarketscience.jackcess.Index$EntryColumn<init>.Index.read
> (Index.java)
> at
>
com.healthmarketscience.jackcess.Table.read<init>(Table.java)
> at
>
com.healthmarketscience.jackcess.Table.readPage<init>(Table.java)
& gt; at
>
com.healthmarketscience.jackcess.Table<init>(Table.java)
> at
>
com.healthmarketscience.jackcess.Table.getTable<init>(Table.java)
& gt; at com.healthmarketscience.jackcess.tester.main


Plz send it ASAP.Very Very Urgent
Thanks
With Regards
Pranjal Dutta



__________________________________

Nic Passmore

Posts: 2
Nickname: nicjp
Registered: Apr, 2005

Re: Major Problem!!! Posted: Oct 6, 2005 4:29 PM
Reply to this message Reply
First up, can I ask why you are not using the current version Jackcess? The current version at SF.net is 1.1.2, which may in fact solve your problem..

Second, what version of Access was used to create the database (mdb) file?

Flat View: This topic has 16 replies on 2 pages [ 1  2 | » ]
Topic: Help with updating graphical objects in my GUI. Previous Topic   Next Topic Topic: Problem with Jackcess 1.0

Sponsored Links



Google
  Web Artima.com   

Copyright © 1996-2019 Artima, Inc. All Rights Reserved. - Privacy Policy - Terms of Use