[Dbix-class] New DBIx::Class oracle user

[Hartmaier Alexander]

Hi Ben!

I'm using a base class for all table classes:

package NAC::Model::DBIC::Table::Parent::All;
use strict;
use warnings;
use base qw/DBIx::Class/;

__PACKAGE__->load_components(qw/HTMLWidget WebForm Core/);

sub schemaname {
    return 'nacadm';
}

1;

An that’s how a table class looks like:

package NAC::Model::DBIC::Table::Device;
use strict;
use warnings;
use base 'NAC::Model::DBIC::Table::Parent::All';

__PACKAGE__->table(__PACKAGE__->schemaname . '.device');

# primary key column(s)
__PACKAGE__->add_columns(
    id_device   => {
        sequence            => __PACKAGE__->schemaname . '.seq_device',
        is_auto_increment   => 1,
    },
);

# other columns
__PACKAGE__->add_columns(qw/
    fk_monitor
    scan
    ping
    fk_device_type
    fk_division
    fk_location
    fk_collector
    name
    alias
    snmplocation
    hostid
    fk_device_service_contract
    service_contract_id
    service_contract_enddate
    ipaddr
    snmpport
    snmpread
    serialnumber
    memory
    flashmem
    chassismodel
/);

# set the primary key
__PACKAGE__->set_primary_key(qw/
    id_device
/);

I hope this helps starting with DBIC!

-Alex

PS: Don't even think about using Class::DBI these days!


> -----Original Message-----
> From: Ben Hitz [mailto:hitz at genome.stanford.edu]
> Sent: Thursday, June 21, 2007 1:51 AM
> To: dbix-class at lists.rawmode.org
> Subject: [Dbix-class] New DBIx::Class oracle user
> 
> Hi -
> 
> We have a well-developed Oracle database but are refactoring our per
> API for it.  So we are checking out Class::DBI and DBIx::Class (at
> Matt's prompting).
> 
> I did get the DBIx::Class::Schema::Loader to work, BUT I had to put
> in a little hack to use "$db_schema.$tablename" instead of $tablename
> in
> DBIx::Schema::Loader::DBI::Oracle.
> 
> I ran my first test on the new class, and it works great - BUT only
> if I log in as user $db_schema (in Oracle, users and schemas are
> synonymous).   In our current API (and older, raw 'bag o DBI SQL
> code") we basically hard code in $schema.$table.
> 
> If you log in as a user you get :
> DBIx::Class::ResultSet::search(): DBI Exception: DBD::Oracle::db
> prepare_cached failed: ORA-00942: table or view does not exist (DBD
> ERROR: error possibly near <*> indicator at char 247 in 'SELECT
> me.reference_no, me.source, me.status, me.pdf_status, me.dbxref_id,
> me.citation, me.year, me.pubmed, me.date_published, me.date_revised,
> me.issue, me.page, me.volume, me.title, me.journal_no, me.book_no,
> me.date_created, me.created_by FROM <*>reference me WHERE ( pubmed
> = :p1 )') [for Statement "SELECT me.reference_no, me.source,
> me.status, me.pdf_status, me.dbxref_id, me.citation, me.year,
> me.pubmed, me.date_published, me.date_revised, me.issue, me.page,
> me.volume, me.title, me.journal_no, me.book_no, me.date_created,
> me.created_by FROM reference me WHERE ( pubmed = ? )"] at ./test.pl
> line 10
> 
> (no, my user name is not "me")
> 
> It's very hard to google for this sort of info because the nouns are
> all "schema" "user" "database" name.
> 
> I think there is a simple solution to this, but I am a little stuck.
> Some options:
> 1) can I pass a schema name indepedent of my user name to DBI
> connection string?
> 2) is there some field I can set in the DBIx::Schema or in the
> ResultSources themselves?
> 3) Was there some other good way to run DBIx::Class::Schema::Loader
> (without the hack) that I missed?  Another hack to add?
> 
> Thanks for your time.
> 
> Ben
> --
> Ben Hitz
> Senior Scientific Programmer ** Saccharomyces Genome Database ** GO
> Consortium
> Stanford University ** hitz at genome.stanford.edu
> 
> 
> 
> 
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive: http://www.mail-archive.com/dbix-
> class at lists.rawmode.org/

*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
T-Systems Austria GesmbH   Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
Notice: This e-mail contains information that is confidential and may be privileged.
If you are not the intended recipient, please notify the sender and then delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*