DB2 and generated always

[prev] [thread] [next] [Date index for 2005/03/10]

Hello Class::DBI folk:

I am trying to use Class::DBI 0.96 and Class::DBI::DB2 0.16 with a table that h\
as a DB2 auto generated primary key:

create table foo
(
  foo_id INTEGER generated always as identity (start with 1) primary key not nu\
ll,
  name varchar(16),
  phone varchar(16)
)

I see that Class::DBI makes efforts to support such things, but perhaps not
Class::DBI::DB2.  To insert new rows, the syntax DB2 wants is:

insert into foo (name, phone) values ('Ben', '212-555-1212')

or

insert into foo values (default, 'Ben', '212-555-1212')

and then, to get the autogenerated id back:

values identity_val_local()

I've setup my columns like so:

Foo->columns (Primary => qw /foo_id/);    # Line 1
Foo->columns (Others => qw /name phone/); # Line 2

yet this call:

$foo = Foo->create ({name => 'Ben', phone => '212-555-1212'});

results in DBD::DB2 throwing an error because Class::DBI tries to execute:

INSERT INTO foo (name, foo_id, phone) VALUES (?, ?, ?)

Is there any documentation about this?  Should I be using sequences instead
of "generated always"?  If I don't tell CDBI about any primary key (by
omitting Line 1, above, and adding foo_id to Line 2), I can
create, but then I can't search or retrieve: running

Foo->search (foo_id => 1)

causes

SELECT FROM foo where foo_id = 1

Thanks for Class::DBI, it is great!

-Ben