byterock

Just prove it worked day here in the Moose-pen

So yesterday I left off with my SQL looking correct but my query failing on the DBD::DBM so to-day I though I better just do a quick postette just to prove to my many readers that my code actually works.

So I all did was write up this quick little script

#!perl

use DBI;

use Data::Dumper;

use Database::Accessor;

my $dbh = DBI->connect("DBI:Oracle:",

        "a user id" ,

        "a password");


my $new_da = Database::Accessor->new({view=>{name=>'locations'},

                                          elements=>[{name=>'location_id'},

                                                     {name=>'street_address'},

                                                     {name=>'postal_code'},

                                                     {name=>'city'},

                                                     {name=>'state_province'},

                                                     {name=>'country_id'}],});

my $other_user = Database::Accessor->new({view=>{name=>'locations'},

                                          elements=>[{name=>'city'}],

                                          conditions=>{left  =>{ name  => 'city',

                                view  => 'locations'},

                      right =>{ value => 'Toronto'}}});

$new_da->add_condition({left  =>{ name  => 'city',

                               },

                      right =>{ value => $other_user}

                    });

$new_da->retrieve($dbh);

warn('result='.Dumper($new_da))

which should just mimic what I had in my last post I just changed he view and element names to ones I know I had data for on my local Oracle DB. When I gave this a run I got this error;

DBD::Oracle::db prepare failed: ORA-00936: missing expression (DBD ERROR: error

 possibly near <*> indicator at char 163 in \'SELECT locations.location_id,

 locations.street_address, locations.postal_code, locations.city,

 locations.state_province, locations.country_id FROM locations WHERE <*>.city = ( 

SELECT locations.city FROM locations WHERE locations.city = :p1 )\') …

So I guess in my add_condition I will need to add in the view name as well to that left part of the preticate. So I added that in;

$new_da->add_condition({left  =>{ name => 'city',

++                                                     view => 'locations'

                               },

                      right =>{ value => $other_user}

                    });

and it worked as I get no error in my result class and only this;

   'set' => [ [ '1800','147 Spadina Ave','M5V 2L7','Toronto','Ontario', 'CA' ]],

in the set. Which is the only record on the DB with a city name of 'Toronto. Case proven.

Now I think I will have to have modify my tests so I can include an option to test with another DB, but before that I will have to look into that view problem on a condition.