Forum: Ruby on Rails Strange HABTM behavior against SQL Server (AR 1.14.4)

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Bb4bdf2b184027bc38d4fb529770cde5?d=identicon&s=25 Wes Gamble (weyus)
on 2006-12-29 01:49
All,

Win XP
SQL Server 2000
Rails 1.1.6 (AR 1.14.4)

I have two objects (Job and Warning) in a HABTM relationship.  Below are
the pertinent sections of the model files and the schema for the join
table.

job.rb:
class Job < ActiveRecord::Base
  has_and_belongs_to_many :warnings, :foreign_key =>
'JobReferenceNumber'
  set_table_name  :JobData
  set_primary_key :JobReferenceNumber

warning.rb:
class Warning < ActiveRecord::Base
  has_and_belongs_to_many :jobs, :association_foreign_key =>
'JobReferenceNumber'

jobs_warnings table migration:
create_table :jobs_warnings do |t|
  t.column :JobReferenceNumber, :integer, :null => false
  t.column :warning_id, :integer, :null => false
end

What I find in both my app. and ruby script/console is the following:

If I run the following three statements against a saved Job object j
with JobReferenceNumber 550, and an _empty_ jobs_warnings table:

j.warnings << Warning.find(3)
j.warnings << Warning.find(2)
j.warnings << Warning.find(1)

I end up seeing rows in the jobs_warnings table with

id: 3
JobReferenceNumber: 550
warning_id: 3

id: 2
JobReferenceNumber: 550
warning_id: 2

id: 1
JobReferenceNumber: 550
warning_id: 1

This is wrong.  The id value is equal to the warning_id value.  The id
columns in jobs_warnings should start at 1 since id is an identity
column.  And, since there are three distinct warnings, the 4th insert
fails with a duplicate key failure.

Here is a piece of the development log from running my app:
SQL (0.080000)   SET IDENTITY_INSERT jobs_warnings ON
SQL (0.080000)   SELECT @@IDENTITY AS Ident
SQL (0.080000)   INSERT INTO jobs_warnings ([JobReferenceNumber], [id],
[warning_id]) VALUES (551, 3, 3)

I believe the id from the warnings table is being selected and then used
as the id value for the join table.  Is this a bug in the SQL Server
driver when using HABTM relationships with non-standard table names?

Does anyone know of any problems with HABTM against SQL Server that
would explain these erroneous join table INSERTS?

Thanks,
Wes
Bf95852638e764fcabbd716e089615ac?d=identicon&s=25 Steve Keener (muddawg)
on 2006-12-29 04:31
Wes Gamble wrote:
> Does anyone know of any problems with HABTM against SQL Server that
> would explain these erroneous join table INSERTS?

Yup...I ran into the exact same thing a while back.  It's a "Feature" or
so I was told.  I was also told I was wrong in my approach to solving my
issue.  I didn't agree so I removed the ID field from the join table
(You don't really need it.) and my stuff works fine.  Your mileage may
vary.

Enjoy!

MudDawg
Bb4bdf2b184027bc38d4fb529770cde5?d=identicon&s=25 Wes Gamble (weyus)
on 2006-12-29 06:12
Steve Keener wrote:
> Yup...I ran into the exact same thing a while back.  It's a "Feature" or
> so I was told.  I was also told I was wrong in my approach to solving my
> issue.  I didn't agree so I removed the ID field from the join table
> (You don't really need it.) and my stuff works fine.  Your mileage may
> vary.
>

Steve,

Thanks for this.  Something strange - in the case I outlined above, the
"first" of the two joined tables (table "JobData" for class "job") is a
legacy table, and the second follows the Rails convention.  I have
another pair of tables where the "first" table uses the "normal" Rails
convention and the second one is the legacy table, and that works just
fine.

I have also noticed that for some reason, you need to use strings
instead of symbols for the values of :foreign_key and
:association_foreign_key.  I wonder if that's SQL Server specific as
well.

Glad to know I'm not crazy.

Wes
Bb4bdf2b184027bc38d4fb529770cde5?d=identicon&s=25 Wes Gamble (weyus)
on 2006-12-29 07:50
I decided to go the has_many :though route, so I created a join model
named JobsWarning and then set up the relationships like so:

Job (job.rb):
  has_many :jobs_warnings, :foreign_key => :JobReferenceNumber,
:dependent => :delete_all
  has_many :warnings, :through => :jobs_warnings

Warning (warning.rb):
  has_many :jobs_warnings
  has_many :jobs, :through => :jobs_warnings

JobsWarning (jobs_warning.rb):
  belongs_to :job, :foreign_key => :JobReferenceNumber
  belongs_to :warning

Given that my job object is saved,  when I do job.warnings <<
Warning.find(x), I expect that records will automatically be saved (like
they are for HABTM).  But these join records are not automatically
saved.

Is this correct?

Thanks,
Wes
Bb4bdf2b184027bc38d4fb529770cde5?d=identicon&s=25 Wes Gamble (weyus)
on 2006-12-29 08:25
Well, this is more explicit, but it seems to work (code is in job.rb):

self.jobs_warnings.clear
self.jobs_warnings.create(:JobReferenceNumber =>
self.JobReferenceNumber,
                          :warning_id =>
Warning.find_by_title("Javascript").id)

whereas self.jobs << Warning.find(x) simply adds the warning object to
the collection in memory.

Wes
This topic is locked and can not be replied to.