Thread: RE: Re: Replace MSSQL by PostgreSQL ?
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
> -----Original Message-----
> From: Per-Olof Pettersson [mailto:pgsql@peope.net]
> Sent: Monday, May 28, 2001 5:15 AM
> To: Pieter Grimmerink; pgsql-general@postgresql.org
> Subject: [GENERAL] Re: 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?
> >
> > 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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
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/