Back to Pratical Moose

Back to piratical Moose;

You may remember a post a few days ago where I was starting the first of my practical Database::Driver::DBI tests against an Oracle DB I happen to have handy. I ran into problems right away as I was getting this generated SQL;


NSERT INTO people ( city, country_id, first_name,
last_name, postal_code, street, user_id )
VALUES( ?, ?, ?, ?, ?, ?, ? )

as the the container I was using was not being cleaned up so I was getting elements that where part of the 'address' view. My last few post cleaned up that problem and now when I run my test I get;;

not ok 1 - Create New User
# Failed test 'Create New User'
# at 10_crud_basic.t line 20.
not ok 2 - One row effected
# Failed test 'One row effected'
# at 10_crud_basic.t line 21.

and looking at the returned results class I see the generated SQL;

INSERT INTO people ( first_name, last_name, user_id ) VALUES( ?, ?, ? )

and I also can have a look at the error coming back from the DBI handle;

DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into ("HR"."PEOPLE"."ID") (DBD
ERROR: OCIStmtExecute) [for Statement "INSERT INTO people ( first_name, last_name, user_id )
VALUES( ?, ?, ? )" with ParamValues: :p1=\'James\', :p2=\'Marceia\', :p3=\'marceiaj\'] at …

That actully looks better than before as I have the container all cleaned up with only elements that have the view 'people' but what I an missing is the 'id' as indicated by the 'cannot insert NULL' error.

Looking at the defintion hash I use from the Person.pm class;


my $da = Database::Accessor->new({view =>{name=>"people"},
elements=>[{name =>'id',
identity =>{'DBI::db'=>{'ORACLE' => {
name => 'NEXTVAL',
view => 'people_seq'}
}} },
{name=>'first_name'},
...

I have that 'identity' key present but it does no like that 'ORACLE' as the secondary key. Checking my Driver::DBI code this is what I use to check that secondary key;

if (exists(
$identity->{ $self->DB_Class }->{ $self->dbh()->{Driver}->{Name} }
)){
my $new_field = Database::Accessor::Element->new($identity->{ $self->DB_Class }->{ $self->dbh()->{Driver}->{Name}} );
unshift(@params,$new_field);
unshift(@field_sql,$self->_field_sql($field));

}
}

I just added a warn before the if to peek and seek what '$self->dbh()->{Driver}->{Name}' is and I get


DBH Driver name=Oracle at D:...
Just a little typo in my ' Xtest::DA::Person;' class quickly fixed;

-- identity =>{'DBI::db'=>{'ORACLE'  => {
++ identity =>{'DBI::db'=>{'Oracle'  => {
and when I run that I get;

ok 1 - Create New User
ok 2 - One row effected
and looking at my SQL I see I have;

INSERT INTO people ( id, first_name, last_name, user_id ) 
         VALUES( people_seq.NEXTVAL, ?, ?, ? )
and on SQLPlus I check the DB and I see that I have

+----+-----------+------------+----------+
| ID | LAST_NAME | FIRST_NAME | USER_ID  |
+----+-----------+------------+----------+
|  5 | Marceia   | James      | marceiaj |
+----+-----------+------------+----------+
Present so things are looking up.

Now to figure out a test plan for this.

pose.jpg

Leave a comment

About byterock

user-pic Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations