Replace MSSQL by PostgreSQL ?

Pieter Grimmerink wrote:

I have a windows application using a MSSQL (the free 2gig version) database.
When the application is first started, I have to select 'sql server' and
then I need to
give the sql setup (servername, user+pass) in an ODBC datasource setup
dialog.

Now I am wondering, would there be a chance I could use PostgreSQL on my
linux
server, with an ODBC windows driver, and let this application create and use
a postgresql database instead, without having to make changes to the
application
itself?

Or do I have a way to positive idea about ODBC interfaces...

Right now I tried to get this working, I installed postgresql 7.1, and the
postgresql ODBC
driver for windows, but I can't even import database tables from postgresql
in MSAccess.
But this could very well be because the highest version I can select in the
driver is the 6.4/6.5
protocol.

The only way to convince people to move from MSSQL to PostgreSQL would be
when they don't notice the difference ;-)

Should I go on with this, or don't I have a chance?

You could try, it isn't difficult nor time consuming to test it. The
only thing is that the SQL syntax of MS SQL differs from that of
PostgreSQL, so if you can't change the app I don't give you much chance.

Success,

Nils

Pieter Grimmerink wrote:

I have a windows application using a MSSQL (the free 2gig version) database.
When the application is first started, I have to select 'sql server' and
then I need to
give the sql setup (servername, user+pass) in an ODBC datasource setup
dialog.

Now I am wondering, would there be a chance I could use PostgreSQL on my
linux
server, with an ODBC windows driver, and let this application create and use
a postgresql database instead, without having to make changes to the
application
itself?

Or do I have a way to positive idea about ODBC interfaces...

Right now I tried to get this working, I installed postgresql 7.1, and the
postgresql ODBC
driver for windows, but I can't even import database tables from postgresql
in MSAccess.
But this could very well be because the highest version I can select in the
driver is the 6.4/6.5
protocol.

The only way to convince people to move from MSSQL to PostgreSQL would be
when they don't notice the difference ;-)

Should I go on with this, or don't I have a chance?

Best regards,

Pieter

Hi

First, try to get the latest driver. It shows support for 7.x. You can
find it at http://odbc.postgresql.org/

Next, you need to configure the datasource. By default it is read-only.
And there are some other issues for how it is supposed to interpret
TEXT-types and the like.
http://www.sevainc.com/Access/PostgreSQL_ODBC_Configuration.html

These links are taken from
http://techdocs.postgresql.org/oresources.php

Best regards
Per-Olof Pettersson

Hi, Pieter,
there is no problem with ODBC, but
you have to move your database architecture to PostgreSQL.
There are some problems with the the lack of SQL compatibleness between MS &
PG (of course - no standard).

MS use getdate() to get current_timestamp etc.

I stopped porting because I have table 'name' on MS, but on PG is it
impossible.

regards,

Jan Pruner

On Mon, 28 May 2001, Pruner Jan wrote:

Hi, Pieter,
there is no problem with ODBC, but
you have to move your database architecture to PostgreSQL.
There are some problems with the the lack of SQL compatibleness between MS &
PG (of course - no standard).

MS use getdate() to get current_timestamp etc.

I stopped porting because I have table 'name' on MS, but on PG is it
impossible.

Hi,

The reason is PG's built in data type 'name'. As your
databases superuser you may drop this data type, then you can
create tables named 'name' :-)

If you don't need type 'name' do

DROP TYPE name;

Then you can do

CREATE table name (...);

...

--
Dipl-Ing. Martin Jacobs * Windsbach * Germany
Registered Linux User #87175, http://counter.li.org/

100.179370@germanynet.de (Martin Jacobs) writes:

If you don't need type 'name' do
DROP TYPE name;

This would be an exceedingly bad idea, since 'name' is used throughout
the system catalogs.

It might work to rename the type (eg, "update pg_type set typname =
'pgname' where typname = 'name'). Haven't tried that to see what
sorts of problems it might have. Would definitely recommend doing
any experimentation of this sort in a scratch database ;-)

regards, tom lane

On Sat, 16 Jun 2001, Tom Lane wrote:

100.179370@germanynet.de (Martin Jacobs) writes:

If you don't need type 'name' do
DROP TYPE name;

This would be an exceedingly bad idea, since 'name' is used throughout
the system catalogs.

Ash on my head! You'r right.

It might work to rename the type (eg, "update pg_type set typname =
'pgname' where typname = 'name'). Haven't tried that to see what
sorts of problems it might have. Would definitely recommend doing
any experimentation of this sort in a scratch database ;-)

I've done such experiment. It does not work, sorry for the
noise. :-(

...

I can understand Pruner, it's a bit disappointing to have
'name' as table name blocked by PG internals, and other rather
natural table names too. Is there really no solution?

Martin

--
Dipl-Ing. Martin Jacobs * Windsbach * Germany
Registered Linux User #87175, http://counter.li.org/

100.179370@germanynet.de (Martin Jacobs) writes:

On Sat, 16 Jun 2001, Tom Lane wrote:

It might work to rename the type (eg, "update pg_type set typname =
'pgname' where typname = 'name'). Haven't tried that to see what
sorts of problems it might have. Would definitely recommend doing
any experimentation of this sort in a scratch database ;-)

I've done such experiment. It does not work, sorry for the
noise. :-(

No? What goes wrong?

I can understand Pruner, it's a bit disappointing to have
'name' as table name blocked by PG internals, and other rather
natural table names too. Is there really no solution?

Once we implement schemas (hopefully Real Soon Now), I'd expect the
built-in type names to be part of the system schema, where they'd not
prevent you from creating new table + type names in your own schema.
Of course, you will still not like what happens after you create a
table named "text", say ... but as long as you're sufficiently careful
about qualifying table names and type names it seems like it should
work.

regards, tom lane

On Mon, 18 Jun 2001, Tom Lane wrote:

100.179370@germanynet.de (Martin Jacobs) writes:

On Sat, 16 Jun 2001, Tom Lane wrote:

It might work to rename the type (eg, "update pg_type set typname =
'pgname' where typname = 'name'). Haven't tried that to see what
sorts of problems it might have. Would definitely recommend doing
any experimentation of this sort in a scratch database ;-)

I've done such experiment. It does not work, sorry for the
noise. :-(

No? What goes wrong?

My PG environment is still 7.03. I did a pg_dump of an
existing small database, consisting of a couple of tables,
sequences and more. I removed the data import from the dump
file, created a new test database, renamed type 'name' to
'pg_name' as you suggested and tried to import the dumpfile
into the new database with

psql -e test < dumpfile

and get the error message

ERROR: Unable to locate type name 'name' in catalog

The first line after the connect command is

CREATE SEQUENCE "anbieter_lieferant_seq" start 61 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;

In the follow up there are more error messages due to the
first error.

My conclusion: there is no simple way to name a table like a
type name.

Right?

I can understand Pruner, it's a bit disappointing to have
'name' as table name blocked by PG internals, and other rather
natural table names too. Is there really no solution?

Once we implement schemas (hopefully Real Soon Now), I'd expect the
built-in type names to be part of the system schema, where they'd not
prevent you from creating new table + type names in your own schema.
Of course, you will still not like what happens after you create a
table named "text", say ... but as long as you're sufficiently careful
about qualifying table names and type names it seems like it should
work.
...

Sounds promising, so let's look forward.

Regards

Martin

--
Dipl-Ing. Martin Jacobs * Windsbach * Germany
Registered Linux User #87175, http://counter.li.org/