Thread: Converting Other DBes to Postgres & JDBC

Converting Other DBes to Postgres & JDBC

From
Dmitry Iv Popov
Date:
Greetings,

would you be so kind to give me an advice: how can I connect/convert
Visual FoxPro  database to  PostgreSQL and read data from it from my
Java applet (i.e. is there JDBC public driver)? Thanks a lot!

--
  BEST WISHES, Dmitry
  Webmaster, Auric Vision Ltd.
  http://www.auricvision.com/



Re: [GENERAL] Converting Other DBes to Postgres & JDBC

From
Ulf Mehlig
Date:
Dmitry Iv Popov <mitlas@auricvision.com> wrote:

> how can I connect/convert Visual FoxPro database to PostgreSQL and
> read data from it from my Java applet (i.e. is there JDBC public
> driver)?

What do you mean by connect? Are you planning to access a PostgreSQL
server over a network or do you just want to experiment with another
database system?

There is a JDBC driver "delivered" with the sources of PostgreSQL 6.4
(../postgresql-v6.4/src/interfaces/jdbc/). There should be the
possibility to transfer data from one database system to another via
two JDBC driver connections, but I've never tried that (it should be
possible with all that XxxMetaData stuff to write a general data
transfer program, if there are suitable JDBC drivers, shouldn't it?).

I don't know anything about FoxPro's export facilities, but I think
you can produce a text file for data of every table, with single
fields separated by a certain delimiter. You can modify the text files
afterwards with perl, awk etc. to make small changes (CSV's commas or
`;'-s to TABs etc). Be carefull: NULL values aren't always exported in
a form that is recognizable afterwards (but you can change all NULL
values in your data to some strange text or number that don't occur in
the "real" data and reconvert it after having imported them to the new
database).

You should look for a FoxPro utility to copy the logical structure
(table definition and so on) of your database into a SQL script;
otherwise, you have to reproduce the tables by hand, writing SQL
"CREATE TABLE" scripts.

For importing data in ASCII text format you can use the COPY-command
of PostgreSQL (see man page). Standard format is TAB-separated ASCII
with \N for NULL values. If you are converting from a DOS source, I
would recommend (under Linux and probably other Unixes) to do a

   recode ibmpc:latin1 < original.txt > import.txt

before importing the data with COPY to change line endings and
character codes that are non-ASCII.

Hope it helps!
Ulf

--
======================================================================
 %%%%%            Ulf Mehlig              <umehlig@zmt.uni-bremen.de>
   %%%%!%%%       Projekt "MADAM"         <umehlig@uni-bremen.de>
%%%% %!% %%%%     ----------------------------------------------------
 ---| %%%         MADAM:  MAngrove    |  Center for Tropical Marine
    ||--%!%              Dynamics     |  Biology
    ||                  And           |  Fahrenheitstrasse 1
 _ /||\_/\_            Management     |
/  /    \  \ ~~~~~~~~~~~~~~~~~        |  28359 Bremen/Germany
  ~~~~~~~~~~~~~~~~~~~~

Re: [GENERAL] Converting Other DBes to Postgres & JDBC

From
Peter T Mount
Date:
On Mon, 23 Nov 1998, Ulf Mehlig wrote:

> Dmitry Iv Popov <mitlas@auricvision.com> wrote:
>
> > how can I connect/convert Visual FoxPro database to PostgreSQL and
> > read data from it from my Java applet (i.e. is there JDBC public
> > driver)?
>
> What do you mean by connect? Are you planning to access a PostgreSQL
> server over a network or do you just want to experiment with another
> database system?
>
> There is a JDBC driver "delivered" with the sources of PostgreSQL 6.4
> (../postgresql-v6.4/src/interfaces/jdbc/).

Don't forget I have precompiled drivers on my web site.

> There should be the possibility to transfer data from one database
> system to another via two JDBC driver connections, but I've never tried
> that (it should be possible with all that XxxMetaData stuff to write a
> general data transfer program, if there are suitable JDBC drivers,
> shouldn't it?).

In theory, it should be. The problem is that there are not many drivers
that implement the full API.


--
       Peter T Mount peter@retep.org.uk
      Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
 Java PDF Generator: http://www.retep.org.uk/pdf