Using DBIx::Class with linked databases in Oracle

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Using DBIx::Class with linked databases in Oracle

Amelia Ireland-2
I'm querying an Oracle database with a number of linked tables and have not yet been able to access the linked tables using DBIx::Class.

I generated the schema for both databases using DBIC::Schema::Loader separately as DBIC::Schema::Loader doesn't follow links.

I'm now trying to translate a nested select query into a DBIx::Class data structure and wrestle with the linked DB issue.

SELECT name, id 
FROM local_table
WHERE alt_table_id
IN (
    SELECT  link_id
    FROM linked_table\@linked_db
)

Is there any way to get DBICSL to acknowledge the linked tables when creating the schema, and if not, how can I use DBIx::Class to perform the nested select query? 

Any help would be gratefully received.

Thank you!

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: Using DBIx::Class with linked databases in Oracle

Amelia Ireland-2
To answer my own question here, I was getting weird SQL generated when the table name had the link in it; "linked_table\@linked_db" was corrupted into "lnkdtbl@lnkddb_M38DWUJS" (or something similar). I tried removing the "@linked_db" part from the DBIx::Class table name, and miraculously, it worked! It seems that although DBIx::Class::Schema::Loader doesn't process linked tables, DBIx::Class is able to use them transparently. Great!



On 19 June 2015 at 17:29, Amelia Ireland <[hidden email]> wrote:
I'm querying an Oracle database with a number of linked tables and have not yet been able to access the linked tables using DBIx::Class.

I generated the schema for both databases using DBIC::Schema::Loader separately as DBIC::Schema::Loader doesn't follow links.

I'm now trying to translate a nested select query into a DBIx::Class data structure and wrestle with the linked DB issue.

SELECT name, id 
FROM local_table
WHERE alt_table_id
IN (
    SELECT  link_id
    FROM linked_table\@linked_db
)

Is there any way to get DBICSL to acknowledge the linked tables when creating the schema, and if not, how can I use DBIx::Class to perform the nested select query? 

Any help would be gratefully received.

Thank you!


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...