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

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
  ~~~~~~~~~~~~~~~~~~~~