importing into postgres from access

nsrona <svngo@earthlink.net> wrote:

I currently have postgres on a Linux 6.2 machine & I can access all of its
information from any windows machine using the proper odbc driver.

Now I want to go in the other direction - Is there a way for my postgres
server on the linux box to pull down information from a windows client that
is using an Access Database?

I'm relatively new to postgres *and* I'm not sure if I understand your
question right:

You want "postgres" to connect to another DB server, and populate a
postgres table from a table in that other DB server ???

If that's the case, I'm pretty sure it's not possible. PostgreSQL is a DB
server, not a client, and actually I believe this is true for most (all
?) DB server: They can act as server and server only, not as client.

Now, one thing you might want to look into, and again assumning I
understood the question right, is to look at perl and DBI. What this would
allow you to do is run a perl script on the Linux box that connects to
both servers (postgres on Linux, and whichever on Windows), and populate
one table from one with data from a table on the other server. IF you know
a litle perl, this is trivial to do.

Yves.
----
Yves Dorfsman dorfsmay@cuug.ab.ca
http://www.cuug.ab.ca/~dorfsmay

You can in fact access an Access database from Linux.

Just install the Universal ODBC stuff from OpenLink and point it at a
DSN on an NT box. (Don't know about W95/98 but should work.)

You can the use PERL/DBI etc to manipulate the data directly.

If, however, you actually want PostgreSQL to process the Access tables
then forget it.

Cheers,
Stephen.

Yves Dorfsman <x@x.com> wrote:

nsrona <svngo@earthlink.net> wrote:

I currently have postgres on a Linux 6.2 machine & I can access all of its
information from any windows machine using the proper odbc driver.

Now I want to go in the other direction - Is there a way for my postgres
server on the linux box to pull down information from a windows client that
is using an Access Database?

I'm relatively new to postgres *and* I'm not sure if I understand your
question right:

You want "postgres" to connect to another DB server, and populate a
postgres table from a table in that other DB server ???

If that's the case, I'm pretty sure it's not possible. PostgreSQL is a DB
server, not a client, and actually I believe this is true for most (all
?) DB server: They can act as server and server only, not as client.

Now, one thing you might want to look into, and again assumning I
understood the question right, is to look at perl and DBI. What this would
allow you to do is run a perl script on the Linux box that connects to
both servers (postgres on Linux, and whichever on Windows), and populate
one table from one with data from a table on the other server. IF you know
a litle perl, this is trivial to do.

Yves.
----
Yves Dorfsman dorfsmay@cuug.ab.ca
http://www.cuug.ab.ca/~dorfsmay

========================================================================
Stephen Davies Consulting scldad@sdc.com.au
Adelaide, South Australia. Voice: 08-8177 1595
Computing & Network solutions. Fax: 08-8177 0133

At 16:31 11/07/00 +0930, Stephen Davies wrote:

You can in fact access an Access database from Linux.

Just install the Universal ODBC stuff from OpenLink

URL please, neither openlink.com nor openlink.org seem to be the right places.

Thanks

Steve

--
thorNET - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax: 01454 854412
http://www.thornet.co.uk

At 16:31 11/07/00 +0930, Stephen Davies wrote:

You can in fact access an Access database from Linux.

Just install the Universal ODBC stuff from OpenLink

URL please, neither openlink.com nor openlink.org seem to be the right

places.

Thanks

Steve

http://www.openlinksw.com

Alex Bolenok

Alex Bolenok wrote:

At 16:31 11/07/00 +0930, Stephen Davies wrote:

You can in fact access an Access database from Linux.

Just install the Universal ODBC stuff from OpenLink

URL please, neither openlink.com nor openlink.org seem to be the right

places.

Thanks

Steve

http://www.openlinksw.com

Alex Bolenok

You can use the Windows ODBC driver of Postgresql and the use access to
export your table to PG via ODBC. Maybe the Upsize wizard will even work...

Cheers.
Franck

Hello,

I'm just new on this list and hope that I don't ask a FAQ (if so please
lead me to the correct document).

I have to migrate a smaller Database (about 20 tables, fully normalized,
no table more than 1000 lines) for a web-project from MS SQL server
to PostgreSQL.

Each table has an unique ID which I plan to port to a SEQUENCE.
Because there were some deleted rows the current database I'm affraid
that the PostgreSQL tables will not be filled correctly because
the IDs are refered to in other tables and are not possible to
change. I wonder how to assure the referential integrity of the
tables.
Are there any mechanisms in PostgreSQl to assure that only those
numbers could be included into a table which are present as ID in
a certain row and how to do that if it is possible?

Furthermore I have some stored procedures. Do I have to expect
many problems when converting to PostgreSQL?

If you answer my questions please keep in mind that I consider
myself as a beginner in SQL and databases and as quite experienced in
handling Linux.

Kind regards

Andreas.

Hello,

I'm just new on this list and hope that I don't ask a FAQ (if so please
lead me to the correct document).

I have to migrate a smaller Database (about 20 tables, fully normalized,
no table more than 1000 lines) for a web-project from MS SQL server
to PostgreSQL.

Each table has an unique ID which I plan to port to a SEQUENCE.
Because there were some deleted rows the current database I'm affraid
that the PostgreSQL tables will not be filled correctly because
the IDs are refered to in other tables and are not possible to
change. I wonder how to assure the referential integrity of the
tables.
Are there any mechanisms in PostgreSQl to assure that only those
numbers could be included into a table which are present as ID in
a certain row and how to do that if it is possible?

Furthermore I have some stored procedures. Do I have to expect
many problems when converting to PostgreSQL?

If you answer my questions please keep in mind that I consider
myself as a beginner in SQL and databases and as quite experienced in
handling Linux.

Kind regards

Andreas.

There is a great replication mechanism in MS SQL. You should set up your MS
SQL server as Distributor and Publisher, set up a snapshot replication, add
a pull ODBC Subscriber with DSN of your PostgreSQL server, and start the
Replication Agent (see MS SQL Server documentation for details). All your
indices, constraints etc. that PostgreSQL supports will be ported there.
Data will be bulk copied into PostgreSQL database first, and constraints
will be applied later, so all your data will be just the same it was on MS
SQL Server.

As for unique ID's, there are two types of ones in MS SQL: identity and
GUID's (called uniqueidentifiers in MS SQL). If you use identity (that is
just an autoincrement integer field), you will have no problems with porting
it, but you will have to create manually a PostgreSQL generator for each
table, set its value to SELECT MAX(id) FROM table, and add a default of
SELECT NEXTVAL('generator_name') to the id field of the table. GUID's are
converted into CHAR(36) type, and you will have to write an SQL function
that would generate a GUID and set default value of your GUID field to this
function. I heard that Linux library with the GUID generation function exist
somewhere, but I may mistake.

Triggers and stored procedures are to be completely rewritten, because
languages that MS SQL and PostgreSQL use are very different.

Alex Bolenok.

Thanks for your hint. I tried hardly to follow your suggestion but really
failed to set up the DSN right. (It might be that I use not the right
terms in the following because I use a German Win-NT :-(.)
When I tried to activate a subscriber for the distributor and publisher
and try to install a "New Subscriber" I get a list of 5 possibilities:

FoodMart (MS Access-Driver)
LocalServer (SQL Server)
MQUIS (SQL Server)
Visual FoxPro Database
Visual FoxPro Tables

I used the ODBC-Datasource-Administrator to insert the PostgreSQL-ODBC
driver but I couldn't manage it to be included in the list above. (In

fact

it worked for MS-Access so I think the ODBC driver works in general

because

I was able to read and write into the PostgreSQL tables with Access.)
What kind of DSN I have to configure which way. There are
User-DSN (in German Benutzer-DSN)
System-DSN
File-DSN (in German Datei-DSN)
which is the right one (I configured all of them for read/write access
hopefully) and how to do the exact configuration.

You should use System DSN (so that SQL Server, that runs under either
LOCALSYSTEM or .\SQLExecutive account, can see it).

Add a DSN, then configure a Subscriber (use ODBC subscriber). It must appear
in your subscribers list. (I cannot repoduce all the actions needed, because
I have no MS SQL Server installed handy, but it _did_ work on my Windows
2000 Server / MS SQL Server 7.0 (full version) / PostgreSQL 6.5.4 / RedHat
6.1 configuration).

If you fail to set up an ODBC Subscriber, you may still try to set up an OLE
DB Subsriber. First create a linked server (use OLE DB for ODBC) and insert
either DSN or provider string into the corresponding field. Then if you
select this linked server in the Enterprise Manager, you will see the tables
that reside in the database you selected. It means that the linked server is
set up properly. Then you should create a new OLE DB Subscriber and when
asked select the linked server you have created. It must work too.

BTW, I experienced some problems with transactional replications to
PostgreSQL 6.5.4 on both OLE DB Subscriber (not all type definitions were
created correctly) and ODBC Subscriber (replication failed to repeat the
transations if the connection was broken once). Hope it is fixed in 7.0.

In how fare are SQL stored procedures different?

Quite.

All the triggers are fired FOR EACH STATEMENT in MS SQL (as for now,
PostgreSQL does not support it).

PostgreSQL does not support nested transactions, cursors in stored
procedures (use FOR loops instead of cursors), etc.

You cannot get system names from variables in PostgreSQL (so if you use
something like CREATE TABLE @mytable in your triggers or procedures, you
will have to write an external procedure to achieve this result).

PostgreSQL does not support output variables in functions, and uses RETURN
result, that is commonly used to report an error in MS SQL procedures.

In general, SQL of PostgreSQL is MUCH poorer that one of MS SQL (it does not
support OUTER JOINS, you cannot perfom runtime formed queries, etc.).

And so on, and so on...

So, even your procedures are simple, you will have to rewrite them all
manually anyway, at least to fit in PostgreSQL variable naming convention
($1 for the first variable, etc.)

Alex Bolenok.

On Tue, 18 Jul 2000, Alex Bolenok wrote:

You should use System DSN (so that SQL Server, that runs under either
LOCALSYSTEM or .\SQLExecutive account, can see it).

This is the trick to get the PostgreSQL driver into the list.
Thanks for the hint.

Add a DSN, then configure a Subscriber (use ODBC subscriber). It must appear
in your subscribers list. (I cannot repoduce all the actions needed, because
I have no MS SQL Server installed handy, but it _did_ work on my Windows
2000 Server / MS SQL Server 7.0 (full version) / PostgreSQL 6.5.4 / RedHat
6.1 configuration).

Is there any possibility to test the connection to the driver. I've got
it included into the list of subscribers but after the configuration
or after setting the properties I get the following error message:

"SQL Server Enterprise Manager konnte nicht �berpr�fen, ob 'PostgreSQL'
ein g�ltiger Datenquellenname ist.
M�chten Sie ihn trotzdem als Abonnenten aktivieren?
Fehler 21076: [SQL-DMO] Die angegebene DSN-Informationen sind falsch
oder unvollst�ndig."
Trial of a translation:
"SQL Server Enterprise Manager couldn't check, if 'PostgreSQL'
is a valid data source name.
Do you want it to be a subscriber anyway?
Error 21076: [SQL-DMO] The DSN-informations are invalid or not
complete."

When creating the DSN I just unchecked the "Readonly"-fields and
filled in the appropriate login user information.
If I restart the subscriber properties menu the PostgreSQL entry
vanished (for sure because of the error).

If you fail to set up an ODBC Subscriber, you may still try to set up an OLE
DB Subsriber. First create a linked server (use OLE DB for ODBC) and insert
either DSN or provider string into the corresponding field. Then if you

Sorry but I havn't found a way to set up such a beast. Could you explain
shortly how to do that or give a pointer to the related description?

Thanks for your detailed help

Andreas.

Is there any possibility to test the connection to the driver. I've got
it included into the list of subscribers but after the configuration
or after setting the properties I get the following error message:

"SQL Server Enterprise Manager konnte nicht �berpr�fen, ob 'PostgreSQL'
ein g�ltiger Datenquellenname ist.
M�chten Sie ihn trotzdem als Abonnenten aktivieren?
Fehler 21076: [SQL-DMO] Die angegebene DSN-Informationen sind falsch
oder unvollst�ndig."
Trial of a translation:
"SQL Server Enterprise Manager couldn't check, if 'PostgreSQL'
is a valid data source name.
Do you want it to be a subscriber anyway?
Error 21076: [SQL-DMO] The DSN-informations are invalid or not
complete."

When creating the DSN I just unchecked the "Readonly"-fields and
filled in the appropriate login user information.
If I restart the subscriber properties menu the PostgreSQL entry
vanished (for sure because of the error).

You should also fill in the ServerName and Database fields. Do not fill in
Username and Password, because these fields will be overriden by MS SQL
Server when setting up the Subscriber. And, surely, uncheck ReadOnly. You
may also need to play with BoolAsChar, TextAsLongVarChar etc. options if you
will experience difficulties with type translation.

Sorry but I havn't found a way to set up such a beast. Could you explain
shortly how to do that or give a pointer to the related description?

See MS SQL Server Books Online, topic "Linked servers, adding"

Alex Bolenok.

On Tue, 18 Jul 2000, Alex Bolenok wrote:

If you fail to set up an ODBC Subscriber, you may still try to set up an OLE
DB Subsriber. First create a linked server (use OLE DB for ODBC) and insert
either DSN or provider string into the corresponding field. Then if you
select this linked server in the Enterprise Manager, you will see the tables
that reside in the database you selected. It means that the linked server is
set up properly. Then you should create a new OLE DB Subscriber and when
asked select the linked server you have created. It must work too.

Back from holidays I did a further try to cope with your suggestion.
I really didn't managed to install the ODBC Subscriber for the
replication mechanism. Fortunately I managed to create a linked server
and finally I could see the tables of the PostgreSQL database.

Unfortunately my stupid German documentation dosn't give me any hint
how to create a OLE DB subscriber. (Does anybody know if I could
get a English one from MS once bought a German for free? - I suppose
not.) Could anybody tell me which damn menu I have to pull to get
such a beast installed. It's a shame to ask such questions here but
I really get upset yesterday when trying to fiddle around with this
beast :-( - sorry.

Kind regards

Andreas.