AW: O$: Null Fields

Rudolf Bargholz bargholz at onlinetravel.ch
Wed Apr 22 10:08:53 EDT 2009


Hi Cliff,

Just spent a couple of days thinking I was very smart and attempting to implement what you describe below. I went through all our schemas and assigned an additional property to the schema column in order to set a DEFAULT value, only to find out in the end if you explicitly insert a NULL into a column (at least with DB2), then DB2 will insert the NULL, irrespective of the value you supply as a default. Only if you do not supply the column in your insert statement will the default value be inserted.

In your example below, could you try:

insert into test (id, foo, bar) values (default, cast(NULL as char(1)), 99);

and have a look what you get returned from the select? In DB2, to my dismay, it was NULL and not the expected empty value. Could have kicked myself following this white elephant and wasting so much time without testing thoroughly first. A

Do Row.$definefromsqlclass('MySchema')
Do Row.$insert()

till thus not insert any DEFAULT values, but rather NULL in all of the specified columns.

Regards

Rudolf Bargholz

-----Ursprüngliche Nachricht-----
Von: omnisdev-en-bounces at lists.omnis-dev.com [mailto:omnisdev-en-bounces at lists.omnis-dev.com] Im Auftrag von CLIFFORD ILKAY
Gesendet: Dienstag, 21. April 2009 19:53
An: OmnisDev List - English
Betreff: Re: O$: Null Fields

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Christine Penner wrote:
> We use Postgres.

Use the Force, Christine. :) If you want to avoid NULLs, why not set
default values for those columns in which you don't want NULLs? The
following illustrates.

christine=# create table test (id serial primary key, foo varchar not
null default 'BLUB', bar integer default 0);
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for
serial column "test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
Time: 36.697 ms
christine=# insert into test default values;
INSERT 12523162 1
Time: 13.921 ms
christine=# insert into test (id, foo, bar) values (default, 'Hello',
default);
INSERT 12523163 1
Time: 9.422 ms
christine=# insert into test (id, foo, bar) values (default, default, 99);
INSERT 12523164 1
Time: 5.471 ms
christine=# select * from test;
 id |  foo  | bar
- ----+-------+-----
  1 | BLUB  |   0
  2 | Hello |   0
  3 | BLUB  |  99
(3 rows)

Time: 0.507 ms

See <http://www.postgresql.org/docs/8.3/interactive/sql-insert.html>.
- --
Regards,

Clifford Ilkay
Dinamis
1419-3266 Yonge St.
Toronto, ON
Canada  M4N 3P6

<http://dinamis.com>
+1 416-410-3326
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJ7gfolUaSlL+/juwRAsIWAJ0bxeNn5Xs373DgxbzOOwJBTwLSygCeOGh9
AZxxxwVm1TFAuxR73wvNB6I=
=XNyV
-----END PGP SIGNATURE-----
_____________________________________________________________
Manage your list subscriptions at http://lists.omnis-dev.com



More information about the omnisdev-en mailing list