Model.get(id) is not working

[tiagomelo]

Hi,

I'm using DataMapper and dm-oracle-adapter. I have a table at Oracle like this:

 ID_ICON              NOT NULL NUMBER(3)
 ICON_PT              VARCHAR2(200)
 STATUS                NUMBER(1)

ID_ICON is the Primary Key. So, I have a model like this:

class Icon

  include DataMapper::Resource

  storage_names[:default] = "icon"

  property :id, Serial, :field => 'id_icon'

  property :icon_pt, String
  property :icon_es, String
  property :icon_en, String
  property :status, Integer

end

Supose that I have a registry at this table with ID 1. When I try to find it, like this:

@icon = Icon.get(1)

It simply can't find that record! But the record is there:

Icon.all
 => [#<Icon @id=1 @icon_pt="a" @icon_es="b" @icon_en="c" @status=2>] 

This is the query that the server is doing:

SQL (3.920ms)  SELECT "ID_ICON", "ICON_PT", "ICON_ES", "ICON_EN", "STATUS" FROM "ICON" WHERE ("ID_ICON" = :1) AND rownum <= :2

What Am I missing? Thank you in advance.

[snusnu]

Not being familiar with oracle, this is just a guess: Have you tried specifying the key property not as a Serial (that assumes an autogenerated value) but as an Integer?

property :id, Integer, :key => true

The query it generated seems really weird to me btw ...

[tiagomelo]

Well, I've tried exactly the way you said and I'm still not able to get that record =/

2011/3/30 snusnu < reply@reply.github.com>

Not being familiar with oracle, this is just a guess: Have you tried specifying the key property not as a Serial (that assumes an autogenerated value) but as an Integer?

property :id, Integer, :key => true

The query it generated seems really weird to me btw ...

Reply to this email directly or view it on GitHub: https://github.com/datamapper/dm-oracle-adapter/issues/2#comment_935075

Tiago Melo Desenvolvedor de Software Twitter: http://twitter.com/tiago_melo

"The weak can never forgive. Forgiveness is the attribute of the strong." - Mahatma Gandhi

[tiagomelo]

Well, I've created a test table with a sequence and a trigger to autoincrement it's primary key (that's the way to have "autoincrement" working at Oracle).

Creating the table:

create table test(id number primary key, name varchar2(30));

Next, creating the sequence:

CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

Finally, creating the trigger:

create or replace trigger test_trigger
before insert
on test
referencing new as new
for each row
begin
  select test_sequence.nextval into :new.id from dual;
end;
 /

So, I've created the model class:

class TestTable
  include DataMapper::Resource
  storage_names[:default] = 'test'
  property :id, Serial # I'm using "Serial" here since this field is autoincremented by trigger
  property :name, String
end

Now, trying to play with it at Rails Console:

$ rails c
:001 > TestTable.all
 => [] 
:002 > TestTable.create(:name => "Iron Maiden")
 => #<TestTable @id=1 @name="Iron Maiden">
:003 > TestTable.get(1)
 => nil

Any clue??

[tiagomelo]

OK, I've figure out. It should be a bug at version 1.1.0.

Turns out that I had to come back to version 1.0.0 of all DM's gems:

At GemFile:

DM_VERSION = '~> 1.0.0'

# DataMapper related gems

gem 'dm-rails', DM_VERSION
gem 'dm-migrations', DM_VERSION
gem 'dm-types', DM_VERSION
gem 'dm-validations', DM_VERSION
gem 'dm-constraints', DM_VERSION
gem 'dm-transactions', DM_VERSION
gem 'dm-aggregates', DM_VERSION
gem 'dm-timestamps', DM_VERSION
gem 'dm-observer', DM_VERSION
gem 'dm-core', DM_VERSION
gem 'dm-do-adapter', DM_VERSION
gem 'dm-active_model', DM_VERSION

Everything is working.

[myabc]

I can confirm I am able to reproduce this bug (and its affecting an app that needs to go into production soon).

Model.first(:id => id_val) ist a workaround, of course.

Looks like an issue with sequences / rownum, although I need to investigate further to see exactly what's happening.

[Bence]

I can confirm this bug: I can reliably reproduce on our production environment. Model.first(:id => id_val) retrieves the record, while Model.get(id_val) doesn't.

[myabc]

A quick and very dirty workaround is to monkey-patch to remove/comment out 4 lines from `lib/dm-oracle-adapter/adapter.rb (lines 148-151):

          #if use_simple_rownum_limit
          #  statement << " AND rownum <= ?"
          #  bind_values << limit
          #end

This works for me. I haven't yet worked on a more robust solution. I would need to consult with @rsim about what the implications are removing this logic would be.

[myabc]

/cc @pietia Any thoughts on this?

[pietia]

I can confirm that works (Alex's solution) but haven't digged into this problem.

[myabc]

@tiagomelo @Bence can you provide me with the version of data_objects/ do_oracle you are using? JRuby or MRI?

[Bence]

Thanks for the workaround!

I'm on MRI with data_objects/do_oracle v0.10.6.

[tillsc]

I can confirm this and I've fixed it in #8.

[tillsc]

This should be done with #8 so it can be closed now