Hi,
the following syntax works for mysql:

insert into table_name values () ;

and the following works for sybase

insert into table_name values ( DEFAULT );

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";

insert into test3 ( col1 ) values ( 1 );
will insert 1 for col1 and null for col2

but
insert into test3 values ( );

returns syntax error.

Any help would be great,

thanks
esteban.-

Esteban Casuscelli wrote:
> the following syntax works for mysql:
> insert into table_name values () ;
>
> and the following works for sybase
> insert into table_name values ( DEFAULT );
>
> 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....

--
June Hunt

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).

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Thanks June,

Just to let you know if you don't use the DEFAULT clause the default
will be a
null value. So if you use the default clause or not you will have
default values for each column.

The problem is that I couldn't find a way to insert all default values
for a table. Same thing works for mysql and sybase....
Under informix at least you need to specify one column to obtain
default values with the other columns.

Yes you are right, you could still end up with more than one row with
all NULL values and doesn't make sense.

I 'm running some hibernate testings and one of them insert all
default values to a table. That 's why I m doing this.

thank you for your help
esteban.-

"June C. Hunt" <june_c_hunt@hotmail.com> wrote in message news:<2m2ilhFhvpg3U1@uni-berlin.de>...
> Esteban Casuscelli wrote:
> > the following syntax works for mysql:
> > insert into table_name values () ;
> >
> > and the following works for sybase
> > insert into table_name values ( DEFAULT );
> >
> > 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....

Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<Y60Lc.8842$Qu5.3572@newsread2.news.pas.earth link.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.

No, the table has 4 columns ( serail , varchar, integer, integer ) and
it works.
>
> >>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 :-)
>
> >>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).

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.

thank you for you help,
esteban.-

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
default the other columns.

Thanks

johnd


Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<Y60Lc.8842$Qu5.3572@newsread2.news.pas.earth link.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:
> Just to let you know if you don't use the DEFAULT clause the default
> will be a null value. So if you use the default clause or not you will
have
> default values for each column.
>
> The problem is that I couldn't find a way to insert all default values
> for a table. Same thing works for mysql and sybase....
> Under informix at least you need to specify one column to obtain
> default values with the other columns.
>
> Yes you are right, you could still end up with more than one row with
> all NULL values and doesn't make sense. [remainder snipped]

Oh - you *wanted* a row of all NULL values? Sorry, I misunderstood what you
were trying to accomplish. So, with the following original example:

insert into test3 ( col1 ) values ( 1 );
will insert 1 for col1 and null for col2

It is col1 that is the problem, not col2? As you have found, you must
specify at least one column... How about:

insert into test3 (col1) values (NULL);

Not the syntax you are used to, but it seems that it would give you the
results you seek... Unless, of course, the major point of your original
question goes to syntax rather than results.

--
June Hunt

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/

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.

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.earth link.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/