Ruby-postgres: can't convert string to integer

janfoeh · March 14, 2006, 2:41pm

Hi everybody,

I’m experiencing trouble when trying to use ruby-postgres with a legacy
database I need access to. To me it seems the problem is the VARCHAR
columns used there as primary keys. On a simple

me = User.find(‘PS12345’)

I get a type error exception with the message ‘can’t convert String into
Integer’. The trace is:

/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/postgresql_adapter.rb:24:in
connect' /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/postgresql_adapter.rb:24:inpostgresql_connection’
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:145:in
connection_without_query_cache=' /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/query_cache.rb:54:inconnection=’
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:106:in
retrieve_connection' /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:20:inconnection’
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/base.rb:801:in
sanitize' /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/base.rb:409:infind’
./script/…/config/…/app/controllers/database_frontend_controller.rb:68:in
show_overview' /usr/local/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/base.rb:853:inperform_action_without_filters’
/usr/local/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/filters.rb:332:in
perform_action_without_benchmark' /usr/local/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/benchmarking.rb:69:inperform_action_without_rescue’
/usr/local/lib/ruby/1.8/benchmark.rb:293:in measure' /usr/local/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/benchmarking.rb:69:inperform_action_without_rescue’
/usr/local/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/rescue.rb:82:in
perform_action' /usr/local/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/base.rb:369:inprocess_without_session_management_support’
/usr/local/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/session_management.rb:116:in
process' /usr/local/lib/ruby/gems/1.8/gems/rails-1.0.0/lib/dispatcher.rb:38:indispatch’
/usr/local/lib/ruby/gems/1.8/gems/rails-1.0.0/lib/webrick_server.rb:117:in
handle_dispatch' /usr/local/lib/ruby/gems/1.8/gems/rails-1.0.0/lib/webrick_server.rb:83:inservice’
/usr/local/lib/ruby/1.8/webrick/httpserver.rb:104:in service' /usr/local/lib/ruby/1.8/webrick/httpserver.rb:65:inrun’
/usr/local/lib/ruby/1.8/webrick/server.rb:173:in start_thread' /usr/local/lib/ruby/1.8/webrick/server.rb:162:instart_thread’
/usr/local/lib/ruby/1.8/webrick/server.rb:95:in start' /usr/local/lib/ruby/1.8/webrick/server.rb:92:instart’
/usr/local/lib/ruby/1.8/webrick/server.rb:23:in start' /usr/local/lib/ruby/1.8/webrick/server.rb:82:instart’
/usr/local/lib/ruby/gems/1.8/gems/rails-1.0.0/lib/webrick_server.rb:69:in
dispatch' /usr/local/lib/ruby/gems/1.8/gems/rails-1.0.0/lib/commands/servers/webrick.rb:59 /usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:21:inrequire’
/usr/local/lib/ruby/gems/1.8/gems/activesupport-1.2.5/lib/active_support/dependencies.rb:214:in
require' /usr/local/lib/ruby/gems/1.8/gems/rails-1.0.0/lib/commands/server.rb:28 /usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:21:inrequire’
/usr/local/lib/ruby/gems/1.8/gems/activesupport-1.2.5/lib/active_support/dependencies.rb:214:in
`require’
script/server:3

My second app-specific database designed from scratch for Rails works
just fine. I’m on PPC OS X 10.4.5 with Ruby 1.8.4, Rails 1.0.0, Postgres
8.1.2 (on Linux) and have tried ruby-postgres 0.7.1 and 20051221, either
built from source or installed as gem.

Any help, hints, debug tips etc would be greatly appreciated. As I
haven’t had too much luck with postgres-pr as well, I’m now somewhat out
of options…

BTW, the problem I ran into when using postgres-pr was timestamps (with
or without timezones) being casted to nil. For example object.lastlogin
being nil, but object.lastlogin_before_type_cast containing the expected
data.

I tried at first to work around this by adding this to my model:

def after_find
self[:lastlogin] = self.lastlogin_before_type_cast
end

which seemed to work; unfortunately, when saving modified objects back
all timestamp fields where set to nil.

So again, thanks in advance for your help in either case …

Cheers,
Jan

janfoeh · March 14, 2006, 5:16pm

Cam pe la 03/14/2006 03:41 PM, Jan F. scrise:

Hi everybody,

I’m experiencing trouble when trying to use ruby-postgres with a legacy
database I need access to. To me it seems the problem is the VARCHAR
columns used there as primary keys. On a simple

me = User.find(‘PS12345’)

Rails expects that the primary key is a integer. I’ve seen in the
changelog a reference about “non-integer primary key support” added, try
looking in the source for ActiveRecord for that.

–
They say money can’t buy happiness? Look at the smile on my face… ear
to ear, baby!

janfoeh · March 14, 2006, 6:46pm

Constantin G. wrote:

Rails expects that the primary key is a integer. I’ve seen in the
changelog a reference about “non-integer primary key support” added, try
looking in the source for ActiveRecord for that.

Thanks Constantin, I wasn’t aware of that. Yet postgres-pr (which I
started development with) somehow happily accepts varchars as primary
keys; I wonder how it manages to work around this Rails requirement?

However, in this case I’d have to switch back to postgres-pr and
concentrate on solving my timestamp issue; maybe somebody has a hint for
me on this?

Thanks again,
Jan

janfoeh · March 15, 2006, 2:25pm

James B. wrote:

Try this (untested):
set_primary_key "user_key_is_varchar_field"

Thanks Jim, but the source of my problem turned out to be something
else: I found a table in my legacy database which does have integer
primary keys and had the same issue there as well, so it had to be
something else.

It turned out to be the connection sharing method I used was at fault
(http://blogs.pragprog.com/cgi-bin/pragdave.cgi/Tech/Ruby/Connections.rdoc)

Since I was wary of putting too much stress on the legacy db server by
opening too many connections, I followed one of the solutions presented
there by creating the connection once in my ‘User’ model with
‘establish_connection’ and re-used it in each of the other models like
this

Purchase.connection = User.connection

Since I removed this code, everything runs smoothly - even with varchar
primary keys. Now I’ll somehow have to find a new way to deal with
connection management, but that’s for a different topic.

Cheers,
Jan

janfoeh · March 14, 2006, 7:36pm

Jan F. wrote:

Hi everybody,

I’m experiencing trouble when trying to use ruby-postgres with a legacy
database I need access to. To me it seems the problem is the VARCHAR
columns used there as primary keys. On a simple

me = User.find(‘PS12345’)

Try this (untested):

Class User < ActiveRecord::Base

over-ride AR default key treatment

set_primary_key "user_key_is_varchar_field"

anything else goes after here

…
end

me = User.find(“PS12345”)

p me.attributes

Regards,
Jim