odbc to emulate mysql for end programs

On 06/01/2015 12:04 PM, Mimiko wrote:

Hello.

I have several programs which can use mysql,access,oracle DB as a
front-end DB via odbc. Is there a method to emulate or hide the back-end
DB so for program it will be seen as mysql, but real DB will be on
postgres?

On a theoretical level you could probably envision something. On a
practical level, no.

Thank you.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello,

PostgreSQL has a fully standards compliant ODBC driver (See:
https://odbc.postgresql.org/). Any application designed to communicate with
DBMS over ODBC connection should be able to use that driver to communicate
with PostgreSQL. Most applications that interact with databases come with
ODBC drivers pre-installed for the most common databases (MySQL, Microsoft
SQL Server, Oracle, etc.) but allow you to set up an ODBC driver for
another DBMS.

*Will J. Dunn*
*willjdunn.com <http://willjdunn.com&gt;*

On Mon, Jun 1, 2015 at 3:04 PM, Mimiko <vbvbrj@gmail.com> wrote:

On 6/1/2015 12:04 PM, Mimiko wrote:

I have several programs which can use mysql,access,oracle DB as a
front-end DB via odbc. Is there a method to emulate or hide the
back-end DB so for program it will be seen as mysql, but real DB will
be on postgres?

ODBC *is* that method. you just need a database specific ODBC driver,
which William already described.

now, if your code is making use of MySQL specific features, then its not
going to work with Postgres (but it also likely won't work with Oracle
or Access/Jet databases either).

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks for response.

I've tried to connect the application to postgres with odbc. Arised 2
problems:

1) mysql widelly uses case-insensitive naming for
schemas,tables,columns. But postgres use case-sensitive when
doulbe-quoting or lowers the names without quoting. Is there a configure
option to ignore case by default?
2) despite odbc use, at first start the particular application tries to
connect and create tables in database(schema). It kept saying that
database(schema) does not exists, although I've created the schema
exactly as it needs with cases and specified search_path also. May be
this problem is related to case-sensitivenes, as program double-quotes
the schema,table and column names.

On 02.06.2015 01:25, William Dunn wrote:

PostgreSQL has a fully standards compliant ODBC driver (See:
https://odbc.postgresql.org/). Any application designed to communicate
with DBMS over ODBC connection should be able to use that driver to
communicate with PostgreSQL. Most applications that interact with
databases come with ODBC drivers pre-installed for the most common
databases (MySQL, Microsoft SQL Server, Oracle, etc.) but allow you to
set up an ODBC driver for another DBMS.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Mimiko schrieb am 02.06.2015 um 13:16:

1) mysql widelly uses case-insensitive naming for
schemas,tables,columns.

So does Postgres. FOO, foo and Foo are all the same name

But postgres use case-sensitive when doulbe-quoting

Which is what the SQL standard requires (and this was required *long* before MySQL even existed)

or lowers the names without quoting.

Is there a configure option to ignore case by default?

Yes: don't use quoted identifiers.

2) as program double-quotes the schema,table and column names.

Don't use quoted identifiers. Neither in Postgres nor in MySQL (or any other DBMS)

They give you much more trouble than they are worth it (which you have just learned).

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Jun 02, 2015 at 01:31:55PM +0200, Thomas Kellerer wrote:

2) as program double-quotes the schema,table and column names.

Don't use quoted identifiers. Neither in Postgres nor in MySQL (or any other DBMS)

I think a better rule of thumb is either always to use them (and spell
everything correctly) or never to use them. Where you get in trouble
is the case where sometimes identifiers are quoted and sometimes not.
(I find the unquoted use more convenient, and I think it's subject to
fewer surprises like overloaded identifiers where one has an uppercase
in it; but I think that's a matter of taste, and if your system
framework quotes for you automatically then you have no choice but to
stick with that convention always and everywhere.)

This isn't really any different from any other development rule. For
instance, in some environments there are various rules about single
and double quoting. If you have no conventions imposed across all
your developers about when you use which, pretty soon you'll have an
unmaintainable mess. And everyone has their favourite story of
frustration about indentation style or variable naming convention.
This case is no different.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 06/02/2015 04:16 AM, Mimiko wrote:

Thanks for response.

I've tried to connect the application to postgres with odbc. Arised 2
problems:

1) mysql widelly uses case-insensitive naming for
schemas,tables,columns.

Actually that is not true as I found out the hard way. See here for all
the ways you can make that not true:

https://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

But postgres use case-sensitive when
doulbe-quoting or lowers the names without quoting. Is there a configure
option to ignore case by default? > 2) despite odbc use, at first start the particular application tries to
connect and create tables in database(schema). It kept saying that
database(schema) does not exists, although I've created the schema
exactly as it needs with cases and specified search_path also. May be
this problem is related to case-sensitivenes, as program double-quotes
the schema,table and column names.

MySQL != Postgres. You have just started down a tortuous path if your
application is really expecting to talk to a MySQL database.

On 02.06.2015 01:25, William Dunn wrote:

PostgreSQL has a fully standards compliant ODBC driver (See:
https://odbc.postgresql.org/). Any application designed to communicate
with DBMS over ODBC connection should be able to use that driver to
communicate with PostgreSQL. Most applications that interact with
databases come with ODBC drivers pre-installed for the most common
databases (MySQL, Microsoft SQL Server, Oracle, etc.) but allow you to
set up an ODBC driver for another DBMS.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general