Thread: DBI connection to multiple database

Howdy

Running PostgreSQL 7.2.1 on RedHat Linux 7.2.

I know this may be a non-Pg question, but here goes:

Is there a way to connect to one database, extract data,
(say, some select query) and then export that data
into another database without having to table the
data into a file of some sort?

For example, let's say I have two databases running on the
same machine.  I have a script that opens a connection to
Pg (at some port number) and execute a sql ... that would
give me the variable (maybe an array?) to review / test / QC.
Then, I would need to open a connection to Pg (at some new
port) and figure out how to test to see if the target table
exists and has the right amount of columns and then
export / insert / copy the previously build array into
that target table.

It sounds straight forward (at least now that I'm thinking
out loud), but I've never done it and wondered if any of
you have done some thing like that.  If so, any tips or
suggestions?

Thanks!

-X

> Is there a way to connect to one database, extract data,
> (say, some select query) and then export that data
> into another database without having to table the
> data into a file of some sort?

I have done this with both PERL and PHP programs.

In general, I think the PERL DBI is a bit easier to use as it will
handle character or text fields with quotes in them without any
programming, whereas in PHP it is necessary to use ereg_replace to
prepare those fields before doing an insert with them.

I think the PERL DBI also does a better job of handling null fields,
for example in dates.
--
Mike Nolan

> Is there a way to connect to one database, extract data,
> (say, some select query) and then export that data
> into another database without having to table the
> data into a file of some sort?
You might want to start learning about the concept of data
types and variables in programming languages (this includes
shells). Also, reading up on how to pipe things from stdout
of one program to stdin of another might help.

If you want to make one database pipe stuff into another
database directly you'd need to learn about the various
approaches summed up under "dblink". One implementation is
found in contrib/. Search the list archive for more on this.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

> --would you happen to have any examples
> --of this in perl?  i'm trying to find a way
> --of inserting a row into a table without having
> --to parse out the many columns (i'd rather
> --use some array and pop / push with each
> --variable).

I've only recently (January) started working with pgsql.  So far most
of these programs have been to extract data from another database,
such as Oracle or MySQL, and insert it into pgsql.

I've generally written a short program to do this, as I have usually
wanted to massage the data somewhat while transferring it.

Also, I'm a 53 year old programming dinosaur more familiar with COBOL
and PL/SQL techniques than with C, so I have never gotten completely
familiar with some advanced programming techniques, so I'm far more
comfortable with providing a list of variable names than with
using -> notation.

It should be possible to write a generalized perl DBI program that can
transfer data from any arbitrarily defined table to another pgsql
instance, perhaps even handle the CREATE TABLE DDL, but I don't know that
I have the patience to write it.

When I had a large table (10 million rows) to transfer from one pgsql
instance to another, I used the dump/restore process.
--
Mike Nolan