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
