Re: insert statement using default values

Esteban Casuscelli wrote: > Jonathan Leffler <jleffler@earthlink.net> wrote >>June C. Hunt wrote: >>>Esteban Casuscelli wrote: >>>>the following syntax works for mysql: >>>>insert into table_name values () ;>> >>Well, that should only work if there are zero columns in the table. > > No, the table has 4 columns (serial, varchar, integer, integer) and > it works. OK; let's put it this way - that is a totally non-standard extension to SQL that works on MySQL and no-one in their right mind would expect it to work on any other server. So, who else supports it? And I stand by my observation that any table that has defaults on all columns has no serious use -- show me how it could be useful? 'Something unidentified exists' is what the row says - with the serial column, you get 'something unidentified exists and you can call it by the serial value allocated by the server'. Hardly a rivettingly useful piece of information. So, how does MySQL let you insert defaults into 3 of the 4 columns but a meaningful value into the other one? >>>>and the following works for sybase >>>>insert into table_name values ( DEFAULT );>> >>And that should only work if there's one column. > > No, the table has 4 colums :-) OK - so you Sybase has a different non-standard extension to SQL that works on it (and MS SQL Server?). The other points I raised in connection with MySQL (meaninglessness, and how do you insert all defaults except one value) apply here too. >>>>under informix I received a syntax error. [...much snippage...] >> >>There isn't a way to do it in Informix yet. Sybase has it - that's >>useful to know. DB2 has it. More ammunition - it was on my list of >>nice to have's. >> >>INSERT INTO SomeTable(Col1, Col2, ..., ColN) >> VALUES(Val1, DEFAULT, ..., DEFAULT); This is more or less what DB2 supports - any deviations are accidental (my mistake). >>Note that no rational database design would permit every column to be >>defaulted. Note that there are implications for loaders like >>DB-Import (and, to a lesser extent, unloaders). > > Ok, I think there isn't a way to do it and I understand that this > doesn't make sense from a relational database point of view. > > I think i will need to change the hibernate dialect to fix this. Yes... -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/