Hi all, I'm very much a newb to Rose::DB::Object, but I gotta admit it's pretty cool stuff. I'm writing a perl API into an already existing schema that our company uses Java to interface with. So far, RDBO has made a huge undertaking much easier. I've hit my first snag though:
We have SKU's, and each row in the SKU table might or might not have a corresponding entry in the INVENTORY table. The gotcha here is that the INVENTORY table lives in a different schema than does the SKU table. Now, I can get at the inventory "indirectly" by manually creating a new Inventory object, but I can't get at it using the relationship in SKU. The problem is that RDBO is using the schema name from My::SKU to query the associated rows in INVENTORY. I'll be the first to admit that I'm pretty green to all this, so it's probably an error in my code, but I've tinkered with different things for a few hours now and can't seem to get it right. I'm using Oracle, and here's my test case: -----------BEGIN CODE-------------- #!/usr/bin/perl -w use strict; package My::DB; { use base qw(Rose::DB); __PACKAGE__->use_private_registry; __PACKAGE__->default_domain('pts'); __PACKAGE__->default_type('main'); __PACKAGE__->register_db( domain => 'pts', type => 'main', driver => 'Oracle', database => 'pts', schema => 'b_main', host => 'ptsodb', username => 'justine', password => 'xxxxx', connect_options => { ReadOnly => 1, AutoCommit => 0, LongTruncOk => 1, }, ); __PACKAGE__->register_db( domain => 'pts', type => 'custom', driver => 'Oracle', database => 'pts', schema => 'b_custom', host => 'ptsodb', username => 'justine', password => 'xxxxx', connect_options => { ReadOnly => 1, AutoCommit => 0, LongTruncOk => 1, }, ); } package My::Inventory; { use base qw(Rose::DB::Object); __PACKAGE__->meta->setup( table => 'inventory', columns => [ sku_code => { type => 'varchar', length => 128, not_null => 1, }, qty => { type => 'integer', not_null => 1, }, backorder_qty => { type => 'integer', not_null => 1, }, ], primary_key_columns => [ qw(sku_code) ], ); our $DB; sub init_db { $DB ||= My::DB->new(type => 'custom'); } } package My::SKU; { use base qw(Rose::DB::Object); __PACKAGE__->meta->setup( table => 'sku', columns => [ sku_id => { type => 'integer', not_null => 1, }, version => { type => 'integer', not_null => 1, }, sku_code => { type => 'varchar', not_null => 1, length => 128, }, ], primary_key_columns => [ qw(sku_id version) ], relationships => [ inventory => { type => 'many to one', class => 'My::Inventory', required => 0, column_map => { sku_code => 'sku_code', }, }, ], ); our $DB; sub init_db { $DB ||= My::DB->new; } } package main; { my $sku = My::SKU->new(sku_id => '1689949372551661', version => 1)->load; print "SKU Code: ".$sku->sku_code."\n"; my $inv = My::Inventory->new(sku_code => $sku->sku_code)->load; print "Indirect Inventory: ".$inv->qty."\n"; print "Inventory: ".$sku->inventory->qty."\n"; } -----------END CODE-------------- Running the above generates the following output+error: -----------BEGIN OUTPUT-------------- SKU Code: GFT00010 Indirect Inventory: 1000 DBD::Oracle::db prepare_cached failed: ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 48 in 'SELECT sku_code, qty, backorder_qty FROM b_main.<*>inventory WHERE sku_code = :p1') [for Statement "SELECT sku_code, qty, backorder_qty FROM b_main.inventory WHERE sku_code = ?"] at /usr/lib/perl5/site_perl/5.8.8/Rose/DB/Object.pm line 375. load() - DBD::Oracle::db prepare_cached failed: ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 48 in 'SELECT sku_code, qty, backorder_qty FROM b_main.<*>inventory WHERE sku_code = :p1') [for Statement "SELECT sku_code, qty, backorder_qty FROM b_main.inventory WHERE sku_code = ?"] at /usr/lib/perl5/site_perl/5.8.8/Rose/DB/Object.pm line 375. at /usr/lib/perl5/site_perl/5.8.8/Rose/DB/Object/MakeMethods/Generic.pm line 2331 -----------END OUTPUT-------------- You can see that the SKU in question has 1000 qty in the INVENTORY table via the "indirect" way, but for some reason the SELECT statement generated by RDBO when using the relationship is querying the schema b_main instead of b_custom. I am unable to determine if it's using the wrong schema name on the right connection, or not creating the new connection at all. Any tips are greatly appreciated. Justin ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object