Re: insert statement using default values

I think I didn't give you guys all the information in my first message. So to clarify that: I have the following table ( for example ): create table t1 ( col1 serial, col2 char(10), col3 integer ); Under Mysql if I run insert into t1 values (); will insert the all default values for that table, I dont mind what the default values are. Under Sybase if I run insert into t1 values ( DEFAULT ) ; will insert all the default values, again I don't mind what the defaul values are. Under informix if I run insert into t1 values ( ); won't work, it returns syntax error so I need to specify at least 1 of the columns. insert into t1 ( col1 ) values ( 0 ); -- this works fine and insertsthe serial values and nulls for other columns. insert into t1 ( col2 ) values ( 1 ); -- this works fine and insertsthe next serial value available and 1 for the integer column and null for the char column. I don't mind if the table has a definition for default values or not, because if you haven't defined a default the engine will insert a null value for that column. Back in the main point it was that the following sql statement doesn't work under informix: insert into table values (); I was looking for some kind of trick to do it, but the only way to do it is specifying at least one column. So I will modify the hibernate dialect to avoid this. I know it is not a problem, and every database vendor has a different way to do it. As Jonathan said before it is in his list of nice to have and it would be nice to have it. Thanks for your help dryburghj@yahoo.com (scottishpoet) wrote in message news:<81714288.0407200433.3d26fa30@posting.google.com>... > Esteban, what does : > > "the following syntax works for mysql > insert into table_name values ()"> > insert into the table? How is "table_name" defined?> What result are you looking for? One row with a default value for > every column? > > if so the closest I think I can get is : > > > CREATE TABLE test3 > (col1 INTEGER DEFAULT 10, > col2 CHAR(10) DEFAULT "UNASSIGNED" > col3 SERIAL);> > INSERT INTO test3(col3) VALUES (0);> > > This will insert 1 row, generate a unique serial number and then > defau lt the other columns. > > Thanks > > johnd > > > Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<Y60Lc.8842$Qu5.3572@newsread2.news.pas.earthlink.net>... > > 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. > > > > >>and the following works for sybase > > >>insert into table_name values ( DEFAULT );> > > > And that should only work if there's one column. > > > > >>under informix I received a syntax error. > > >>I wonder if there is a way to do it. > > >>What I 've got is: > > >> > > >>create table "informix".test3 > > >> ( > > >> col1 integer, > > >> col2 char(10) > > >> ); > > >>revoke all on "informix".test3 from "public";> > > > > > > > > You will want to define what the default values are for each column where > > > you will allow a default. For example: > > > > > > CREATE TABLE test3 > > > (col1 INTEGER DEFAULT 10, > > > col2 CHAR(10) DEFAULT "UNASSIGNED");> > > > > > > > >>insert into test3 ( col1 ) values ( 1 );> > >>will insert 1 for col1 and null for col2 > > > > > > > > > The INSERT that you've shown would now insert a 1 for col1 and "UNASSIGNED" > > > for col2. > > > > > > You could also issue a statement such as: > > > > > > INSERT INTO test3 (col2) VALUES ("ABC");> > > > > > and col1 would contain a 10 and col2 would contain "ABC". > > > > > > > > >>but > > >>insert into test3 values ( );> > >> > > >>returns syntax error. > > >>Any help would be great, > > > > > > > > > Until just now, I've never tried to insert a row with all default values. > > > In a few simple tests, I haven't discovered how to do it - assuming it is > > > even possible, and the documentation that I've read does not give any > > > indication that it is possible. I'm curious, though, why you would want to > > > do such a thing; you could end up with multiple identical rows. Of course, > > > without a unique index, you could still end up there, but.... > > > > 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);> > > > 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).

Esteban Casuscelli wrote: > I think I didn't give you guys all the information in my first > message. So to clarify that: > > I have the following table ( for example ): > > create table t1 ( col1 serial, col2 char(10), col3 integer );> > Under Mysql if I run insert into t1 values (); will insert the all > default values for that table, I dont mind what the default values > are. > > Under Sybase if I run insert into t1 values ( DEFAULT ) ; will insert > all the default values, again I don't mind what the defaul values are. > > Under informix if I run insert into t1 values ( ); won't work, it > returns syntax error so I need to specify at least 1 of the columns. > > insert into t1 ( col1 ) values ( 0 ); -- this works fine and inserts> the serial values and nulls for other columns. > > insert into t1 ( col2 ) values ( 1 ); -- this works fine and inserts> the next serial value available and 1 for the integer column and null > for the char column. > > I don't mind if the table has a definition for default values or not, > because if you haven't defined a default the engine will insert a null > value for that column. Thanks - that is clear. > Back in the main point it was that the following sql statement doesn't > work under informix: > > insert into table values ();> > I was looking for some kind of trick to do it, but the only way to do > it is specifying at least one column. So I will modify the hibernate > dialect to avoid this. > I know it is not a problem, and every database vendor has a different > way to do it. > > As Jonathan said before it is in his list of nice to have and it > would be nice to have it. Actually, no - that statement was not in my list of nice-to-haves (sorry). What is in there is the ability use DEFAULT as a token in the VALUES list, like you can currently use NULL, and it would insert the relevant default for the corresponding column, just as the default would be inserted if you omitted to supply both the column from the (possibly implicit) list of columns and the list of values. Hence: INSERT INTO t1 VALUES(DEFAULT, "Navigation", DEFAULT) would insert the next available serial value into col1, "Navigation" (a boring non-default value) into col2 and the defined default (possibly null, possibly zero, possibly something else) into col3. The tricky part is making that work sensibly with a prepared statement: INSERT INTO t1 VALUES(?,?,?); How do you tell it that the value you're providing is the default? Probably by a type SQLDEFAULT - needs consideration. And being on my list of nice-to-have is no guarantee of anything other than the fact that I somewhat agree with the concept - or some variation of your concept. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/