byterock

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.