odbc: mysql vs. postgresql

I have a problem writing PHP ODBC scripts that suit both MySQL and
PostgreSQL. So far, the following syntaxes seem to apply to each
database (this is an 'insert' example; the same differences apply to
'select commands):

MySQL: INSERT INTO MyTable (col1, col2) VALUES ("value1","value2")
PGSQL: INSERT INTO "MyTable" (col1, col2) VALUES ('value1','value2')

I'd like to have a single syntax for both databases. Is this possible?
Do I need to modify either database configuration settings? Does each
database (MySQL, PGSQL, MSSQL, Oracle, ...) use its own SQL syntax?

Any advice welcome.

Jacques Lebastard wrote:
>
> I have a problem writing PHP ODBC scripts that suit both MySQL and
> PostgreSQL. So far, the following syntaxes seem to apply to each
> database (this is an 'insert' example; the same differences apply to
> 'select commands):
>
> MySQL: INSERT INTO MyTable (col1, col2) VALUES ("value1","value2")
> PGSQL: INSERT INTO "MyTable" (col1, col2) VALUES ('value1','value2')
>
> I'd like to have a single syntax for both databases. Is this possible?
> Do I need to modify either database configuration settings? Does each
> database (MySQL, PGSQL, MSSQL, Oracle, ...) use its own SQL syntax?
>
> Any advice welcome.

Both MySQL and Postgres support standardized SQL constructs, within the
restrictions of the release of code you're using (i.e. MySQL < 4.1
doesn't support subselects). So as long as you stay with this, you
could be OK.

However, this only applies to data definition language (DDL) statements
such as CREATE TABLE, and data manipulation language (DML) statements
such as SELECT and INSERT. It does not apply to database specific
things like configuration parameters. These are outside of the SQL
language, and anything supported by the database is for your convenience
only and probably not supported by other databases.

However, I question why you'd even use ODBC. If you're using PHP,
Pear::MDB is much faster and more flexible. It also runs well on every
platform, unlike ODBC.

But personally when I need both, I just encapsulate the
database-specific code in PHP classes and select which class I want.
But then all of my DB-specific code is generally in classes anyway.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle a écrit :
> Jacques Lebastard wrote:
>>
>> I have a problem writing PHP ODBC scripts that suit both MySQL and
>> PostgreSQL. So far, the following syntaxes seem to apply to each
>> database (this is an 'insert' example; the same differences apply to
>> 'select commands):
>>
>> MySQL: INSERT INTO MyTable (col1, col2) VALUES ("value1","value2")
>> PGSQL: INSERT INTO "MyTable" (col1, col2) VALUES ('value1','value2')
>>
>> I'd like to have a single syntax for both databases. Is this possible?
>> Do I need to modify either database configuration settings? Does each
>> database (MySQL, PGSQL, MSSQL, Oracle, ...) use its own SQL syntax?
>>
>> Any advice welcome.
>
> Both MySQL and Postgres support standardized SQL constructs, within the
> restrictions of the release of code you're using (i.e. MySQL < 4.1
> doesn't support subselects). So as long as you stay with this, you
> could be OK.
>
> However, this only applies to data definition language (DDL) statements
> such as CREATE TABLE, and data manipulation language (DML) statements
> such as SELECT and INSERT. It does not apply to database specific

If they are supposed to support the same syntax, why does Postgres
require table name with double quotes whereas I get an error using
double quotes when accessing a MySQL DB? Is it a PHP issue, an ODBC
driver issue?

> things like configuration parameters. These are outside of the SQL
> language, and anything supported by the database is for your convenience
> only and probably not supported by other databases.
>
> However, I question why you'd even use ODBC. If you're using PHP,

> Pear::MDB is much faster and more flexible. It also runs well on every

Humm... pear.php.net seems offline... I'll try later.

> platform, unlike ODBC.
>
> But personally when I need both, I just encapsulate the
> database-specific code in PHP classes and select which class I want. But

I do have classes to access either LDAP directories, CVS & RAW files,
ODBC databases and other specific storage media. I thought I could
access all databases using a single ODBC class (instead of using
php_mysql, php_oci8, php_pg, ...). Maybe I was naive..

> then all of my DB-specific code is generally in classes anyway.
>
I'll have a look at the MDB package as soon as the site is reachable.

Thanks for your advice,

Jerry Stuckle wrote:
> Jacques Lebastard wrote:
> >
> > I have a problem writing PHP ODBC scripts that suit both MySQL and
> > PostgreSQL. So far, the following syntaxes seem to apply to each
> > database (this is an 'insert' example; the same differences apply to
> > 'select commands):
> >
> > MySQL: INSERT INTO MyTable (col1, col2) VALUES ("value1","value2")
> > PGSQL: INSERT INTO "MyTable" (col1, col2) VALUES ('value1','value2')
> >

INSERT INTO MyTable (col1, col2) VALUES ('value1','value2')

Should work on both.

>
> However, I question why you'd even use ODBC. If you're using PHP,
> Pear::MDB is much faster and more flexible. It also runs well on every
> platform, unlike ODBC.
>

Alternatively use the dbx_ functions which support several DBMS, or the
adodb class.

C.

Jacques Lebastard wrote:
> Jerry Stuckle a écrit :
>
>> Jacques Lebastard wrote:
>>
>>>
>>> I have a problem writing PHP ODBC scripts that suit both MySQL and
>>> PostgreSQL. So far, the following syntaxes seem to apply to each
>>> database (this is an 'insert' example; the same differences apply to
>>> 'select commands):
>>>
>>> MySQL: INSERT INTO MyTable (col1, col2) VALUES ("value1","value2")
>>> PGSQL: INSERT INTO "MyTable" (col1, col2) VALUES ('value1','value2')
>>>
>>> I'd like to have a single syntax for both databases. Is this possible?
>>> Do I need to modify either database configuration settings? Does each
>>> database (MySQL, PGSQL, MSSQL, Oracle, ...) use its own SQL syntax?
>>>
>>> Any advice welcome.
>>
>>
>> Both MySQL and Postgres support standardized SQL constructs, within
>> the restrictions of the release of code you're using (i.e. MySQL < 4.1
>> doesn't support subselects). So as long as you stay with this, you
>> could be OK.
>>
>> However, this only applies to data definition language (DDL)
>> statements such as CREATE TABLE, and data manipulation language (DML)
>> statements such as SELECT and INSERT. It does not apply to database
>> specific
>
>
> If they are supposed to support the same syntax, why does Postgres
> require table name with double quotes whereas I get an error using
> double quotes when accessing a MySQL DB? Is it a PHP issue, an ODBC
> driver issue?
>

Double quotes are not valid SQL syntax. Not using the ODBC interface
with Postgres, where does it require double quotes?

>> things like configuration parameters. These are outside of the SQL
>> language, and anything supported by the database is for your
>> convenience only and probably not supported by other databases.
>>
>> However, I question why you'd even use ODBC. If you're using PHP,
>
>
>> Pear::MDB is much faster and more flexible. It also runs well on every
>
>
> Humm... pear.php.net seems offline... I'll try later.
>
>> platform, unlike ODBC.
>>
>> But personally when I need both, I just encapsulate the
>> database-specific code in PHP classes and select which class I want. But
>
>
> I do have classes to access either LDAP directories, CVS & RAW files,
> ODBC databases and other specific storage media. I thought I could
> access all databases using a single ODBC class (instead of using
> php_mysql, php_oci8, php_pg, ...). Maybe I was naive..
>

Nope, ODBC never has lived up to its promise of complete transparency.

>> then all of my DB-specific code is generally in classes anyway.
>>
> I'll have a look at the MDB package as soon as the site is reachable.
>
> Thanks for your advice,
>
It's still not perfect, but better than ODBC, IMHO.

But I still just use my own classes. Much faster and not all that hard
to do.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

C. wrote:
>
> INSERT INTO MyTable (col1, col2) VALUES ('value1','value2')
>
> Should work on both.
>

Except on numbers? I read quoting number values in an SQL statement is
not standard (and optional on MySQL).

Larry

larry@portcommodore.com wrote:
> C. wrote:
>
>>INSERT INTO MyTable (col1, col2) VALUES ('value1','value2')
>>
>>Should work on both.
>>
>
>
> Except on numbers? I read quoting number values in an SQL statement is
> not standard (and optional on MySQL).
>
> Larry
>

No. Numbers are not quoted in MySQL, per the SQL standard.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

> I have a problem writing PHP ODBC scripts that suit both MySQL and
> PostgreSQL. So far, the following syntaxes seem to apply to each
> database (this is an 'insert' example; the same differences apply to
> 'select commands):
>
> MySQL: INSERT INTO MyTable (col1, col2) VALUES ("value1","value2")
> PGSQL: INSERT INTO "MyTable" (col1, col2) VALUES ('value1','value2')

You can run the second line in MySQL also - when you are running the
server in ANSI mode.

> I'd like to have a single syntax for both databases. Is this possible?

I think the SQL standard version of the above statements is:

INSERT INTO MyTable(col1, col2) VALUES ('value1','value2')

(Actually, the SQL version is without the columns, but most SQL
databases support the column list)
It would surprise me if PostGress would not understand this statement.

Best regards

Dikkie Dik wrote:
>> I have a problem writing PHP ODBC scripts that suit both MySQL and
>> PostgreSQL. So far, the following syntaxes seem to apply to each
>> database (this is an 'insert' example; the same differences apply to
>> 'select commands):
>>
>> MySQL: INSERT INTO MyTable (col1, col2) VALUES ("value1","value2")
>> PGSQL: INSERT INTO "MyTable" (col1, col2) VALUES ('value1','value2')
>
>
> You can run the second line in MySQL also - when you are running the
> server in ANSI mode.
>
>> I'd like to have a single syntax for both databases. Is this possible?
>
>
> I think the SQL standard version of the above statements is:
>
> INSERT INTO MyTable(col1, col2) VALUES ('value1','value2')
>
> (Actually, the SQL version is without the columns, but most SQL
> databases support the column list)
> It would surprise me if PostGress would not understand this statement.
>
> Best regards

Actually, the SQL standard makes the column list optional. If not
specified, you must provide values for all columns in the order in which
they are defined.

Column and table names, per the standard, are not surrounded by quotes.
Non-numerical values are surrounded by single quotes. Numeric values
also have no quotes.

MySQL extends the standard by optionally allowing back tickeys (`)
around table and column names. This allows you to specify names which
would otherwise be forbidden as they are keywords in MySQL. But for
non-keyword names, the back tickeys are not required (but allowed if used).

The correct MySQL version would be:

INSERT INTO MyTABLE (col1, col2) VALUES ('value1', value2);

Assuming value1 is non-numeric and value2 is numeric.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Sun, 01 Oct 2006 12:02:53 +0200, Jacques Lebastard
<jacques.lebastard@free.fr> wrote in
<451f9254$0$23500$636a55ce@news.free.fr>:

>Jerry Stuckle a écrit :
>> Jacques Lebastard wrote:
>>>
>>> I have a problem writing PHP ODBC scripts that suit both MySQL and
>>> PostgreSQL. So far, the following syntaxes seem to apply to each
>>> database (this is an 'insert' example; the same differences apply to
>>> 'select commands):
>>>
>>> MySQL: INSERT INTO MyTable (col1, col2) VALUES ("value1","value2")
>>> PGSQL: INSERT INTO "MyTable" (col1, col2) VALUES ('value1','value2')

[snip]

>If they are supposed to support the same syntax, why does Postgres
>require table name with double quotes whereas I get an error using
>double quotes when accessing a MySQL DB? Is it a PHP issue, an ODBC
>driver issue?

They are required because the table name was created as a "quoted
identifier" (possibly by your admin tool). See the PostgreSQL FAQ:
<http://www.postgresql.org/docs/faqs.FAQ.html#item4.21>. Also see the
docs on identifiers:
<http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS>.
--
Charles Calvert | Software Design/Development
Celtic Wolf, Inc. | Project Management
http://www.celticwolf.com/ | Technical Writing
(703) 580-0210 | Research