ActiveRecord destroy_all throws StatementInvalid

Question

I am using ActiveRecord with oracle adapter on Ruby on Rails. I am getting a StatementInvalid Exception when trying to delete a row.

Here is how my table looks like : room_user_table

room | user
1010 | a
1010 | b
1011 | a
1011 | c
1011 | d

My ruby ActiveRecord class:

class RoomUserTable < ActiveRecord:Base
    self.table_name = 'room_user_table'
end

Now I want to delete the 2nd row for example, so I am issuing

RoomUserTable.destroy_all(:room => 1010, :user => 'b')

But this is throwing ActiveRecord::StatementInvalid Exception

OCIError: ORA-01741: illegal zero-length identifier: DELETE FROM "ROOM_USER_TABLE" WHERE "ROOM_USER_TABLE"."" = :a1

Any help would be much appreciated.

My test_controller.rb

class TestController < ActionController::Base
    def test
       RoomUserTable.destroy_all(:room => 1010, :user => 'b')
    end
end

RoomUserTable.where(:room => 1010, :user => 'b').first.destroy — Debadatt, Jul 18 '13 at 10:18
This is throwing the same exception `ORA-01741: illegal zero-length identifier` — gnuger, Jul 18 '13 at 10:24

score 1 · Accepted Answer · answered Jul 18 '13 at 12:01

Your RoomUserTable doesn't have a primary key, which is causing it to run the query that you have in your question WHERE "ROOM_USER_TABLE"."" = ... which in turn is causing Oracle to throw a wobbly. Rails models need to have primary keys.

The table looks like a join table therefore you don't need to create a model for it or query it directly at all. You can use a has_and_belongs_to_many relationship between User and Room and specify the join table.

class Room < ActiveRecord::Base
  has_and_belongs_to_many :users, join_table: :room_user_table
end

class User < ActiveRecord::Base
  has_and_belongs_to_many :rooms, join_table: :room_user_table
end

Or similar.

Edit - having said that you have a, b etc in your user column, so I've no idea what that table is, but the problem is still the same, you don't have a primary key.

Actually room_user_table had a composite primary key (user, room) which doesn't have a build-in support in ror. I am trying to install http://compositekeys.rubyforge.org/ to overcome this — gnuger, Jul 18 '13 at 12:27
For some reason, compositekeys plugin didn't worked for me. So I created an additional ID column as primary key set to auto_increment following http://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle Now everything works fine. Thanks Mike — gnuger, Jul 18 '13 at 21:00

Mike Szyndel · Answer 2 · 2013-07-18T10:21:58.827

0

To delete a simgle record you should do

RoomUserTable.where(:room => 1010, :user => 'b').first.destroy

The reason I suggest that is because it is safer than destroy_all, but makes sense only when you are trying to remove a single record.

The best would be to find a record by id of course.

This also should work, but given your problem I'm not sure.

RoomUserTable.where(:room => 1010, :user => 'b').destroy_all

edited Jul 18 '13 at 10:21

answered Jul 18 '13 at 10:03

Mike Szyndel

10,461
10
47
63

I tried that too I got `ArgumentError` : `wrong number of arguments (0 for 1)` I not sure why am I getting that – gnuger Jul 18 '13 at 10:07
Maybe try with old hash notation? Can you paste traceback of this ArgumentError somewhere (pastie.org)? – Mike Szyndel Jul 18 '13 at 10:10
ah, I'm a moron! See this guys comment http://stackoverflow.com/questions/17720312/activerecord-destroy-all-throws-statementinvalid/17720371?noredirect=1#comment25828655_17720312 – Mike Szyndel Jul 18 '13 at 10:20
`RoomUserTable.where(:room => 1010, :user => 'b').destroy_all` is also throwing the same Exception `ORA-01741: illegal zero-length identifier` – gnuger Jul 18 '13 at 10:28
require 'RoomUserTable' class TestController < ActionController::Base def test ... end end – gnuger Jul 18 '13 at 10:33
Why do you do the require? It is a AR class, not a lib! Remove require form the first line. – Mike Szyndel Jul 18 '13 at 10:35
Other ActiveRecord methods are working fine like find(), where(), delete() etc. Only destroy() is causing trouble – gnuger Jul 18 '13 at 10:38
That doesn't mean you should have this require! – Mike Szyndel Jul 18 '13 at 10:39
I have removed the 'require' but still getting same error, but anyway thanks for pointing this out – gnuger Jul 18 '13 at 10:57

score 0 · Answer 3 · answered Jul 18 '13 at 10:23

0

Try this

RoomUserTable.where(:room => 1010, :user => 'b').first.destroy

answered Jul 18 '13 at 10:23

Debadatt

5,935
4
27
40

1

This is throwing the same exception `ORA-01741: illegal zero-length identifier` – gnuger Jul 18 '13 at 10:29

ActiveRecord destroy_all throws StatementInvalid

3 Answers3