SQL Anywhere database - Column Defaults

Author

Column Defaults

Jim Blackburn

2005-04-26, 8:23 pm

Hi,

ASA 9.02.3044

I have a table with two timestamp columns that have a current
timestamp default. When I do an insert that has a null in the values
the default is ignored.

Insert table1 (timein,timeout) values (null,null) ;

Is this new behavior or have I been missing something for the last 8
years :).

Thanks.

Greg Fenton

2005-04-26, 8:23 pm

Jim Blackburn wrote:
>
> Is this new behavior or have I been missing something for the last 8
> years :).
>

I hate to say it, but you've been missing it.

A DEFAULT clause only takes effect when no value or the keyword DEFAULT
is provided as a value for a column in an INSERT clause (or UPDATE for
some special DEFAULTs). NULL is a *value*, so no default would fire if
provided.

Defaults fire in the event of:

Insert table1 (timein,timeout) values (DEFAULT,DEFAULT);

But the ASA documentation states that it is advisabel to omit columns
altogether from an INSERT statement if you only plan to assign them the
DEFAULT value.

Hope that helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Reg Domaratzki \(iAnywhere Solutions\)

2005-04-27, 11:23 am

I tried this back at v5.5.05 build 2509 and at v902 build 3118 and NULL
values were used for both versions, which is what I would have expected.

create table t1 ( pkey integer primary key, c1 timestamp default timestamp,
c2 timestamp default current timestamp );
insert into t1 values ( 10, NULL, NULL );
insert into t1 values ( 20, NULL, DEFAULT );
insert into t1 (pkey, c1) values ( 21, NULL );
insert into t1 values ( 30, DEFAULT, NULL );
insert into t1 (pkey, c2) values ( 31, NULL );
insert into t1 values ( 40, DEFAULT, DEFAULT );
insert into t1 (pkey) values ( 41 );

select * from t1 :

10,(NULL),(NULL)
20,(NULL),'2005-04-27 12:10:50.328'
21,(NULL),'2005-04-27 12:10:50.328'
30,'2005-04-27 12:10:50.328',(NULL)
31,'2005-04-27 12:10:50.328',(NULL)
40,'2005-04-27 12:10:50.328','2005-04-27 12:10:50.328'
41,'2005-04-27 12:10:50.328','2005-04-27 12:10:50.328'

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Jim Blackburn" < jamesb@round2consult
ing.com> wrote in message
news:hvft61t54srofpa
eo2d6i3hj5ootf65vip@
4ax.com...
> Hi,
>
> ASA 9.02.3044
>
> I have a table with two timestamp columns that have a current
> timestamp default. When I do an insert that has a null in the values
> the default is ignored.
>
> Insert table1 (timein,timeout) values (null,null) ;
>
> Is this new behavior or have I been missing something for the last 8
> years :).
>
> Thanks.

Jim Blackburn

2005-04-27, 1:23 pm

Thanks Reg and Greg. Funny how a small thing like raise its ugly
head. My data handling classes are designed to not send null or empty
values but I had a bug in the way dates where handled. Fixed now.

On 27 Apr 2005 09:16:53 -0700, "Reg Domaratzki \(iAnywhere
Solutions\)" < Spam_bad_rdomarat@ia
nywhere.com> wrote:

>I tried this back at v5.5.05 build 2509 and at v902 build 3118 and NULL
>values were used for both versions, which is what I would have expected.
>
>create table t1 ( pkey integer primary key, c1 timestamp default timestamp,
>c2 timestamp default current timestamp );
>insert into t1 values ( 10, NULL, NULL );
>insert into t1 values ( 20, NULL, DEFAULT );
>insert into t1 (pkey, c1) values ( 21, NULL );
>insert into t1 values ( 30, DEFAULT, NULL );
>insert into t1 (pkey, c2) values ( 31, NULL );
>insert into t1 values ( 40, DEFAULT, DEFAULT );
>insert into t1 (pkey) values ( 41 );
>
>select * from t1 :
>
>10,(NULL),(NULL)
>20,(NULL),'2005-04-27 12:10:50.328'
>21,(NULL),'2005-04-27 12:10:50.328'
>30,'2005-04-27 12:10:50.328',(NULL)
>31,'2005-04-27 12:10:50.328',(NULL)
>40,'2005-04-27 12:10:50.328','2005-04-27 12:10:50.328'
>41,'2005-04-27 12:10:50.328','2005-04-27 12:10:50.328'

Sponsored Links