morgon has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I am about to finally ditch DBIx::Class, as I spend far too much time figuring out how to make it do what I want it to do (my IQ is simply not up to it), but does by any chance someone know how to generate primary keys from a sequence in DBIx::Class?

I have tried this approach: 

__PACKAGE__->add_columns(  id        => { data_type => 'integer', size
+ => 16, sequence => "stream_seq" },
...

[download]
But when I then try this: 
my $s = $schema->resultset('Stream')->new({});
$s->insert;

[download]
I get an error (sequence STREAM_SEQ_CURRVAL is not yet defined in this session).

The problem seems to be that PK::Auto seems to expect a trigger that populates the primary key column but I would prefer a solution where I don't define a trigger but rather have DBIx::Class generate an insert-statement that uses the sequence directly - is that possible (and if so how)?

And please don't point me to CPAN-modules you have not used yourself...

Many thanks!

Replies are listed 'Best First'.
Re: DBIx::Class and Oracle sequences
by Your Mother (Archbishop) on May 11, 2009 at 22:23 UTC
    Check out DBIx::Class::Storage::DBI::Oracle::Generic. I think you need auto_nextval. I don't have Oracle to test on so this is untested- 
    __PACKAGE__->add_columns(id => { data_type => 'integer',
                                 size => 16,
                                 sequence => "stream_seq",
                                 auto_nextval => 1 },
              ...

    [download]
    (update: fixed bad link.)
[reply]
[d/l]
[select]
      OK, after working with what I provided, I realized the usage of both sequence & auto_nextval caused a double increment. Therefore, remove teh auto_nextval => 1 from above: 
      
__PACKAGE__->add_columns(
   "id",
   {
    data_type => "NUMBER",
    default_value => undef,
    is_nullable => 0,
    size => 126,
    sequence => "seq_application_id"
   }
);

      [download]
[reply]
[d/l]
      once you create your sequence & trigger, use the following: 
      __PACKAGE__->add_columns(
   "id",
   {
    data_type => "NUMBER",
    default_value => undef,
    is_nullable => 0,
    size => 126,
    sequence => "seq_application_id",
    auto_nextval => 1
   }
);

      [download]
[reply]
[d/l]
        new member so if i posted this twice, i apologize. once you create your sequence & trigger, use the following: 
        __PACKAGE__->add_columns(
   "id",
   {
    data_type => "NUMBER",
    default_value => undef,
    is_nullable => 0,
    size => 126,
    sequence => "seq_application_id",
    auto_nextval => 1
   }
);

        [download]
[reply]
[d/l]
Re: DBIx::Class and Oracle sequences
by afoken (Chancellor) on May 11, 2009 at 20:20 UTC

    SQL Code after 5 seconds of searching the WWW:

    INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.nextval, 'BigCompany');

    [download]

    Now you just have to tell DBIx::Class to pass the sequence name and ".nextval" unchanged to the database whenever you insert a new row. I'm too lazy to read the docs on how to do it.

    Alexander

    --
    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
[reply]
[d/l]
      I am sorry - is this posting for real?

      I know how to do it in plain SQL - that was not my question.

      And had you played with DBIx::Class for even a minute you would know that "telling" DBIx::Class is sometimes not so straightforward.

      I'm too lazy to read the docs on how to do it.
      Sure, why not post something entirely useless instead...
[reply]

Back to Seekers of Perl Wisdom