[CDBI] Sequence failure using Class::DBI::Sweet

Victor Churchill victor at qonnectis.com
Tue Dec 4 18:11:41 GMT 2007


I have an Oracle table 'readings' which has a PK generated with a
sequence and a mandatory 'insert_date' timestamp.  My program can
insert rows into the table when it uses Class::DBI::Oracle but fails
when it uses Class::DBI::Sweet.

The Oracle error is 'ORA-00923: FROM keyword not found where
expected'. I put on a TraceLevel=>3 and at the time of insertion where
Class::DBI::Oracle shows
    -> prepare_cached for DBD::Oracle::db
(DBIx::ContextualFetch::db=HASH(0x......)~0x......'SELECT
rea_seq.NEXTVAL from DUAL

the corresponding trace for Class::DBI::Sweet is
    -> prepare_cached for DBD::Oracle::db
(DBIx::ContextualFetch::db=HASH(0x......)~0x......SELECT NEXTVAL
('rea_seq')   [... yes that's all ...]

Relevant parts of code:

package QG::QGDB;
use strict;
use warnings;
use diagnostics;

##use base 'Class::DBI::Sweet';    # this is the only difference
use base 'Class::DBI::Oracle';
...
QG::QGDB->set_db('Main',
                 $dsn,
                 $dbuser,
                 $dbpassword,
                 {AutoCommit=>1,
                  TraceLevel=>3},
                 );
1;
#####################
package QG::Reading;
#####################
use strict;
use base 'QG::QGDB';

QG::Reading->table('readings');
QG::Reading->columns(Primary => qw/rea_id/);
QG::Reading->columns(Essential => qw/
rea_reading rea_reading_date rea_reading_type_code rea_alm_id
/);
QG::Reading->columns(Others => qw/rea_flow_period/);
QG::Reading->has_a(rea_alm_id => 'QG::ALM');

# when we create a new reading we need to get the sequence number for the ID
# c/o http://wiki.class_dbi.com/wiki/Setting_default_values
# Do Not Delete The Blank Line !!
#####
# However! ...
# __PACKAGE__ -> set_sql(MakeNewObj => <<"");
# INSERT INTO __TABLE__ (rea_id,rea_ins_date,%s)
#  VALUES(REA_SEQ.NEXTVAL,SYSDATE, %s)
# ... did not work ... 'can't get last insert id'
# instead...
__PACKAGE__->sequence('rea_seq');

#If the table has a single primary key column and that column value is
#not defined in %data, insert() will assume it is to be generated. If a
#sequence() has been specified for this Class, it will use that.
#http://search.cpan.org/~tmtm/Class-DBI/lib/Class/DBI.pm#sequence_%2F_auto_increment
# but we DO still want the override for sysdate! ...
__PACKAGE__ -> set_sql(MakeNewObj => <<"");
INSERT INTO __TABLE__ (rea_ins_date,%s) VALUES(SYSDATE, %s)

1;

... followed by ...

# remember the ID value is created inside the class
my %hash = (rea_reading=>$reading,
            rea_reading_date => $date,
            rea_reading_type_code => $type,
            rea_alm_id => $ALM);
my $newReading = QG::Reading -> create(\%hash);

It doesn't seem to make a difference if the column rea_ins_date is
added to the QG::Reading->columns(Essential=>...)  list, the same
error occurs. Likewise if QG::Reading -> create is replaced with
QG::Reading -> insert - same again.

I can probably get by with CDBI::O rather than CDBI::S (it's a fairly
simple application), but it would be good to know if there's something
else I should have done.

Thanks for your attention,

Victor Churchill



More information about the ClassDBI mailing list