Posted by Jerry Stuckle on 10/02/06 02:28

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
==================

[Back to original message]