Обсуждение: generic insert into table

please CC me, I am on digest
---------------------------------
I have the following code from an application that is 'mysql_centric'. I
want to make it generic across all databases, if it's possible,
especially postgres :-)

mysql version:
    INSERT INTO calendar_setting SET setting='colorEvent',value='#C2DCD5';

There is no data in this table at this time.
Isn't this the same as:
    INSERT INTO calendar_setting( 'colorEvent' ) VALUES ( '#C2DCD5');

would this work on all db's?

On 7-6-2004 23:29, Dennis Gearon wrote:
> please CC me, I am on digest
> ---------------------------------
> I have the following code from an application that is 'mysql_centric'. I
> want to make it generic across all databases, if it's possible,
> especially postgres :-)
>
> mysql version:
>    INSERT INTO calendar_setting SET setting='colorEvent',value='#C2DCD5';
>
> There is no data in this table at this time.
> Isn't this the same as:
>    INSERT INTO calendar_setting( 'colorEvent' ) VALUES ( '#C2DCD5');
>
> would this work on all db's?
>

Afaik your first example is equal to:
INSERT INTO calendar_setting( setting, value) VALUES ('colorEvent',
'#C2DCD5');

Your first example isn't really SQL-standard and you'd perhaps better
try using SQL-standard stuff as much as possible on MySQL aswell. The
INSERT ... SET-version of INSERT wouldn't be my version to use.

Best regards,

Arjen

On Mon, Jun 07, 2004 at 02:29:33PM -0700, Dennis Gearon wrote:

> I have the following code from an application that is 'mysql_centric'. I
> want to make it generic across all databases, if it's possible,
> especially postgres :-)
>
> mysql version:
>    INSERT INTO calendar_setting SET setting='colorEvent',value='#C2DCD5';

That would be

INSERT into calendar_setting (setting, value)
VALUES ('colorEvent', '#C2DCD5');

This is the standard syntax, which is not quite the same thing as
saying it will work on all DBs - it will certainly work on both
Postgres and MySQL.

Richard

On Mon, 2004-06-07 at 15:29, Dennis Gearon wrote:
> please CC me, I am on digest
> ---------------------------------
> I have the following code from an application that is 'mysql_centric'. I
> want to make it generic across all databases, if it's possible,
> especially postgres :-)
>
> mysql version:
>     INSERT INTO calendar_setting SET setting='colorEvent',value='#C2DCD5';

For SQL 92, that's not supported syntax.  I don't recall if something
like it got tossed into the latest SQL 03 standard or not.

> There is no data in this table at this time.
> Isn't this the same as:
>     INSERT INTO calendar_setting( 'colorEvent' ) VALUES ( '#C2DCD5');

That's the syntax the SQL 92 spec says to use.

> would this work on all db's?

Probably not.  It's non-standard in the old spec, I don't know about the
new one.  If it got in the newest spec then it could probably get
committed pretty easily.