Has_many :foreign_key association fails

Rails
1

hello list,

class Advertiser < ActiveRecord::Base
set_table_name “INSERENT”
set_primary_key “INSNR”

has_many :priv_properties,
:class_name => “PrivProperty”,
:foreign_key => “MAKNR”
end

produces wrong sql selelect.
so the searched value is NULL

ins = Advertiser.find(100050)

select * from objpriv where objpriv.maknr = NULL;
it should be
select * from objpriv where objpriv.maknr = 100050;

so
ins.quoted_id gets NULL

does activerecord handle the id wrong?

should i add ticket on dev.rubyonrails.org?

best regards

2

module ActiveRecord
module ConnectionAdapters # :nodoc:
module Quoting
# Quotes the column value to help prevent
# {SQL injection attacks}[Wikipedia, the free encyclopedia
SQL_injection].
def quote(value, column = nil)
# records are quoted as their primary key
return value.quoted_id if value.respond_to?(:quoted_id)

    case value
      when String, ActiveSupport::Multibyte::Chars
        value = value.to_s
        if column && column.type == :binary &&

column.class.respond_to?(:string_to_binary)

“#{quoted_string_prefix}‘#{quote_string(column.class.string_to_binary(value))}’”

’ (for ruby-mode)

        elsif column && [:integer, :float].include?(column.type)
          value = column.type == :integer ? value.to_i :

value.to_f
value.to_s
else
“#{quoted_string_prefix}‘#{quote_string(value)}’” #
’ (for ruby-mode)
end
when NilClass then “NULL”
when TrueClass then (column && column.type
== :integer ? ‘1’ : quoted_true)
when FalseClass then (column && column.type
== :integer ? ‘0’ : quoted_false)
when Float, Fixnum, Bignum then value.to_s
# BigDecimals need to be output in a non-normalized form and
quoted.
when BigDecimal then value.to_s(‘F’)
else
if value.acts_like?(:date) || value.acts_like?(:time)
“‘#{quoted_date(value)}’”
else

“#{quoted_string_prefix}‘#{quote_string(value.to_yaml)}’”
end
end
end
…

so i saw the method quote and the case select which returns “NULL” if
value is a NilClass
but why its NilClass?

hmm

3

On 12 Oct 2007, at 10:53, inriz wrote:

end

produces wrong sql selelect.
so the searched value is NULL

ins = Advertiser.find(100050)

select * from objpriv where objpriv.maknr = NULL;
it should be
select * from objpriv where objpriv.maknr = 100050;

Given what you’ve told AR, I would expect the sql generated to be
select * from INSERENT where INSNR = 100050; since you’ve asked AR to
find you the instance of Advertiser with primary key value 100050 and
you’ve told it that the table name is INSERENT and the primary key INSNR

Are you sure you’ve pasted the right bit of sql?

Fred

4

On 12 Oct 2007, at 11:20, inriz wrote:

       :class_name => "PrivProperty",
       :foreign_key => "MAKNR"

end

so set_table_name and set_primary_key must have a :hash? in my book,
agile webdevelopment from dhh, he wrote a normal string?
Strings should work absolutely fine. Not sure why you’re talking
about hashes.

i wonder, why the its a floating point number.
basicly the behavior is rigth :wink: but this, floating point number seam
to be scary?

I can’t guess without seeing the code calling this.

Fred

5

dear frederick,

im a step forward. so i changed the my model see below:

class Advertiser < ActiveRecord::Base
set_table_name :inserent
set_primary_key :insnr

has_many :priv_properties,
:class_name => “PrivProperty”,
:foreign_key => “MAKNR”
end

so set_table_name and set_primary_key must have a :hash? in my book,
agile webdevelopment from dhh, he wrote a normal string?
but it works if i call ins.id
there is a none nil result :wink:
so columns_hash expected a string an the code converts a hash? to_s ?

def column_for_attribute(name)
self.class.columns_hash[name.to_s]
end

now im quite happy but the association always doesnt work.

so AR creates the following sql

SELECT * FROM OBJPRIV WHERE (OBJPRIV.MAKNR = 100050.0)

i wonder, why the its a floating point number.
basicly the behavior is rigth :wink: but this, floating point number seam
to be scary?

many thanks

6

On 12 Oct 2007, at 13:50, inriz wrote:

so ins.id returns the bigdecimal and the sql statement will be filled
with the right value in the condition area
the only thing i changed, was the options under set_primary_key from
string “INSNR” to a hash :insnr
(is it the right spelling?)

You mean symbol, not hash. I’ve no idea why that would make a difference

between 100050 100050.0
for an integer column its no difference but it isnt proper for my
eyes :wink:

so for the current integration of the legacy tables, the bigdecimal
column should be a clear integer …
ins.id.to_i returns the right value for the condition

additionally, if i scaffold the INSERENT table the ids also are
floating point numbers
why?

What are the types of the database columns. If you’ve actually
declared them as big decimals what rails is doing seems reasonable,
if not what you want.

Fred

7

ok lets speak some code snippet

##################

version 1 start

class Advertiser < ActiveRecord::Base
set_table_name “INSERENT”
set_primary_key “INSNR”

has_many :priv_properties,
:class_name => “PrivProperty”,
:foreign_key => “MAKNR”
end

ruby script/console:

ins = Advertiser.find(100050)
… display resultset

ins.id
=> nil

ins.priv_properties
=> []

ins.priv_properties produces the follow sql statement
SELECT * FROM OBJPRIV WHERE (OBJPRIV.MAKNR = NULL)

so ins.id is a NilClass and the quote method returns NULL

ende

##################

##################

version 2 start

class Advertiser < ActiveRecord::Base
set_table_name “INSERENT”
set_primary_key :insnr

has_many :priv_properties,
:class_name => “PrivProperty”,
:foreign_key => “MAKNR”
end

ruby script/console:

ins = Advertiser.find(100050)
… display resultset

ins.id
=> #BigDecimal:3fd2c8c,‘0.10005E6’,8(12)

ins.priv_properties
=> []

ins.priv_properties produces the follow sql statement
SELECT * FROM OBJPRIV WHERE (OBJPRIV.MAKNR = 100050.0)

so ins.id returns the bigdecimal and the sql statement will be filled
with the right value in the condition area
the only thing i changed, was the options under set_primary_key from
string “INSNR” to a hash :insnr
(is it the right spelling?)

ende

##################

the next problem is the bigdecimal typed id
standard rails created database schematas will return only the value
of id without any kind of
type information and so on

in this case, the id is mapped to “INSNR” of my legacy table and that
produces => #BigDecimal:3fd2c8c,‘0.10005E6’,8(12)
its okay, but the foreign_key is a char(10) so its a difference
between 100050 100050.0
for an integer column its no difference but it isnt proper for my
eyes :wink:

so for the current integration of the legacy tables, the bigdecimal
column should be a clear integer …
ins.id.to_i returns the right value for the condition

additionally, if i scaffold the INSERENT table the ids also are
floating point numbers
why?

many thanks to you

best regards

8

On 12 Okt., 15:14, Frederick C. [email protected]
wrote:

its okay, but the foreign_key is a char(10) so its a difference
why?

What are the types of the database columns. If you’ve actually
declared them as big decimals what rails is doing seems reasonable,
if not what you want.

Fred

yes, symbol was the right name :wink:
the column maknr is a char(10) in the table objpriv
fortunately, our databases, firebird, support sql statements
like select * from objpriv where maknr = 100050 without apostrophe
of a char(10)
select * from objpriv where maknr = 100050.0

on an integer column 100050 and 100050.0 are same
but on a char column its not the same
so thats my problem

9

so another breakpoint

the database, were using firebird, has to types
integer and bigint

the ruby firebird adapter uses bigint and bigint is mapped
via bigdecimal

so the behavior, using bigdecimal is defined by the adapter
and the floating point number is right

there are many things to get involved
the fireruby adapter is old and have many bugs with firebird 2.0