Is anyone running Rails 2.x against a MS SQL Server DB?

Rails
1

I am unable to get ActiveRecord session support to work under 2.x
against a SQL Server database. I’m starting to wonder if anyone is
running 2.x against SQL Server?

Is anyone running under the following configuration:

Rails 2.x
SQL Server 2000 DB (using AR adapter in ODBC mode)
AR store for ActionController session store.

I’d like to chat with you if you are.

Thanks,
Wes

2

hey Wes,
I will be running against a sql server 2000 or 2005, but I am still in
development. So I will keep an eye out for any information that you
find out.

Thanks,

Rob

On Mar 14, 2:36 pm, Wes G. [email protected]

3

Well, I’ve done some digging into the guts of the active record store
(actionpack-2.0.2\lib\action_controller\session\active_record_store.rb).

I’ve verified the following:

  1. Nothing is being saved to my session table in the DB.

  2. The update method is being called, which in turn attempts to call
    @session.save. Nothing happens when the save method is called
    (whichever method it is), and so my session record is not being saved.

  3. The standard save method in AR::Base is not being called. I am
    unable to figure out where the “save” method that’s being called
    actually lives.

  4. The session object is a CGI::Session::ActiveRecordStore::Session
    class and it is a descendent of AR::Base.

(Is there a way to figure out which method is being called
programmatically, without manually analyzing the inheritance tree of
classes and/or modules?)

So far, this evidence is internally consistent. If AR::Base.save is not
being called on this object, then the session won’t be saved.

But the question is what happens in this method in
active_record_store.rb:

def update
if @session
ActiveRecord::Base.silence { @session.save }
end
end

?

What is @session.save calling? BTW, I’ve already gone through the
exercise of removing the ActiveRecord::Base.silence block and doing
@session.save! instead in order to see an exception get raised. I see
nothing when I do that.

It’s as though the save method is empty somewhere and does nothing.

Thanks for any help,
Wes

4

I develop against MS SQL Server 2000 db’s all the time.

My company has a whole bunch of legacy db’s that are SQL Server 2000
db’s. So far, I have not had a problem with the SQL Server AR
adapter, with FreeTDS, in ODBC mode with Rails 2.x.

But, then again, we don’t do database session store. We use the Rails
2.x default cookie based session store. So, you may have uncovered a
bug. However, that seems unlikely. The SQL Server AR adapter was
around before Rails 2.x. And, prior to Rails 2.x, I was using db
session store.

The problems I do run into are not really with AR or SQL Server. But,
rather, I have to deal with a lot of legacy/non-Rails-esque database
designs. Things like non-surrogate PK’s, composite PK’s, weird FK
relationships, using PK’s that contain periods and “@'s” . . . just
about every weird thing you can think of . . . I’ve come across.

The good thing is that so far, I have been able find workarounds for
all these situations.

If you can be a little bit more specific with you problem, maybe post
some error messages or something, I would be happy to help you out.

On Mar 14, 4:36 pm, Wes G. [email protected]

5

Here is some script/console output. We see that the session is in fact
an ActiveRecord object, hooked up to the sessions table, but that saving
the session appears to do nothing. No data end up in the database, and
notice how session.new_record? is still true after the save.

Thanks,
Wes

C:\eclipse\workspace\eSimplyOnlineRails>ruby script/console
Loading development environment (Rails 2.0.2)

session = CGI::Session::ActiveRecordStore::Session.new
=> #<CGI::Session::ActiveRecordStore::Session id: nil, session_id: nil,
data: ni
l, updated_at: nil>

session.is_a?(ActiveRecord::Base)
=> true

session.class.table_name
=> “sessions”

session.new_record?
=> true

session[:test_key] = 3
=> 3

session.save
=> []

session.save!
=> []

session.new_record?
=> true

session[:test_key]
=> 3

6

On Sat, Mar 15, 2008 at 9:33 AM, Wes G.
[email protected] wrote:

Well, I’ve done some digging into the guts of the active record store
(actionpack-2.0.2\lib\action_controller\session\active_record_store.rb).

It’s as though the save method is empty somewhere and does nothing.

I used (don’t any more) MS Sql Server using session store.

Double check your table permissions on the SQL Server, maybe something
is funky here.

Try going into the console and “Session.new” set some param and save
it, see if it works.

Try using save! instead of save in the console test to raise an
exception on failed save.

What does your test.log say?

Give us more details :slight_smile:

Mikel

7

Here are the script/console actions:

C:\eclipse\workspace\eSimplyOnlineRails>ruby script/console
Loading development environment (Rails 2.0.2)

session = CGI::Session::ActiveRecordStore::Session.new
=> #<CGI::Session::ActiveRecordStore::Session id: nil, session_id: nil,
data: ni
l, created_at: nil, updated_at: nil>

session.new_record?
=> true

session[:test_key] = 3
=> 3

session.save
=> []

session.save!
=> []

and here are the entries in the development.log file (note that all of
this is output on the instantiation of the session, nothing gets written
to the log after that).

e[4;36;1mCGI::Session::ActiveRecordStore::Session Columns
(0.000000)e[0m e[0;1m
SELECT
cols.COLUMN_NAME as ColName,
cols.COLUMN_DEFAULT as DefaultValue,
cols.NUMERIC_SCALE as numeric_scale,
cols.NUMERIC_PRECISION as numeric_precision,
cols.DATA_TYPE as ColType,
cols.IS_NULLABLE As IsNullable,
COL_LENGTH(cols.TABLE_NAME, cols.COLUMN_NAME) as Length,
COLUMNPROPERTY(OBJECT_ID(cols.TABLE_NAME), cols.COLUMN_NAME,
‘IsIdentity’) as IsIdentity,
cols.NUMERIC_SCALE as Scale,
cols.ORDINAL_POSITION as Position
FROM INFORMATION_SCHEMA.COLUMNS cols
WHERE cols.TABLE_NAME = ‘sessions’
ORDER BY cols.ORDINAL_POSITION
e[0m
MemCache Get (0.020000) parse_tree_refresh_times
MemCache Get (0.000000) list_data_refresh_times
e[4;36;1mCGI::Session::ActiveRecordStore::Session Columns
(0.000000)e[0m e[0;1m
SELECT
cols.COLUMN_NAME as ColName,
cols.COLUMN_DEFAULT as DefaultValue,
cols.NUMERIC_SCALE as numeric_scale,
cols.NUMERIC_PRECISION as numeric_precision,
cols.DATA_TYPE as ColType,
cols.IS_NULLABLE As IsNullable,
COL_LENGTH(cols.TABLE_NAME, cols.COLUMN_NAME) as Length,
COLUMNPROPERTY(OBJECT_ID(cols.TABLE_NAME), cols.COLUMN_NAME,
‘IsIdentity’) as IsIdentity,
cols.NUMERIC_SCALE as Scale,
cols.ORDINAL_POSITION as Position
FROM INFORMATION_SCHEMA.COLUMNS cols
WHERE cols.TABLE_NAME = ‘sessions’
ORDER BY cols.ORDINAL_POSITION
e[0m
MemCache Get (0.010000) parse_tree_refresh_times
MemCache Get (0.000000) list_data_refresh_times
e[4;36;1mCGI::Session::ActiveRecordStore::Session Columns
(0.010000)e[0m e[0;1m
SELECT
cols.COLUMN_NAME as ColName,
cols.COLUMN_DEFAULT as DefaultValue,
cols.NUMERIC_SCALE as numeric_scale,
cols.NUMERIC_PRECISION as numeric_precision,
cols.DATA_TYPE as ColType,
cols.IS_NULLABLE As IsNullable,
COL_LENGTH(cols.TABLE_NAME, cols.COLUMN_NAME) as Length,
COLUMNPROPERTY(OBJECT_ID(cols.TABLE_NAME), cols.COLUMN_NAME,
‘IsIdentity’) as IsIdentity,
cols.NUMERIC_SCALE as Scale,
cols.ORDINAL_POSITION as Position
FROM INFORMATION_SCHEMA.COLUMNS cols
WHERE cols.TABLE_NAME = ‘sessions’
ORDER BY cols.ORDINAL_POSITION
e[0m

Wes

8

On Sat, Mar 15, 2008 at 5:30 PM, Wes G.
[email protected] wrote:

Here is some script/console output. We see that the session is in fact
an ActiveRecord object, hooked up to the sessions table, but that saving
the session appears to do nothing. No data end up in the database, and
notice how session.new_record? is still true after the save.

Weird,

What does your test / development log say about these actions?

Mikel

9

and here are the entries in the development.log file (note that all of
this is output on the instantiation of the session, nothing gets written
to the log after that).

On windows I have sometimes had that the server won’t write to the log
file if it gets too full sometimes and definately won’t if you have
say another console or server open.

Close all your servers and consoles that could be writing to
development.log. Delete or move the development.log and try again and
see if there is any more output…

Mikel

10

I’m running 2.x against 2005, and all seems to be working fine. I’ve
ended up modifying the connection adapter to suit my needs though.

I dont think you can set session in the console you’ll need to do it
from a controller i.e.

class TestController < ApplicationController
def index
session[:test_key] = 3
render :text => “setting test key to 3”
end

def read
render :text => session[:test_key]
end

end

This works as expected. Using the edge sql server adapter.

11

Some more information:

In an attempt to move my application configuration out of the picture,
I generated a brand-new Rails project in 2.0.2 against SQL Server using
ODBC mode for the adapter.

(database.yml below)

development:
adapter: sqlserver
mode: odbc
username: reference
password: reference
dsn: reference

I created the schema_info table, and generated the sessions migration
using rake db:sessions:create, and ran it. So there are simply 2 tables
in my database, schema_info, and sessions. My environment.rb has

config.action_controller.session_store = :active_record_store

set.

Here is the transcript of a script/console session that attempts to save
a session.

C:\eclipse\workspace\Rails_2.0.2_Reference>ruby script/console
Loading development environment (Rails 2.0.2)

session = CGI::Session::ActiveRecordStore::Session.new
=> #<CGI::Session::ActiveRecordStore::Session id: nil, session_id: nil,
data: ni
l, created_at: nil, updated_at: nil>

session[:test_key] =3
=> 3

session.save!
ActiveRecord::RecordNotSaved: ActiveRecord::RecordNotSaved
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record
/base.rb:1980:in save_without_validation!' from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record /validations.rb:944:insave_without_transactions!’
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record
/transactions.rb:112:in save!' from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record /connection_adapters/abstract/database_statements.rb:66:intransaction’
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record
/transactions.rb:80:in transaction' from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record /transactions.rb:100:intransaction’
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record
/transactions.rb:112:in save!' from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record /transactions.rb:120:inrollback_active_record_state!’
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record
/transactions.rb:112:in `save!’
from (irb):3

The reason this appears to fail is that in this method in callbacks.rb,
the call to “callback(:before_save)” returns false.
create_or_update_with_callbacks then returns false to
AR::Base.save_without_validation! which then raises the RecordNotSaved
exception

def create_or_update_with_callbacks #:nodoc:
  return false if callback(:before_save) == false
  result = create_or_update_without_callbacks
  callback(:after_save)
  result
end

While this may be useful, it is not totally related to my original
issue, since I am unable to verify that the save method in AR::Base is
being called when I attempt to save the session in my application. What
I’ve just described above in a default app. is definitely going through
the expected AR::Base.save code.

Having said that though, I feel like this cast aspersions on whether the
AR session store works in SQL Server at all.

Can someone generate a brand-new project, and see if they can get a
session to save using the AR store against SQL Server via ODBC?

Thanks,
Wes

12

Luke P. wrote:

I’m running 2.x against 2005, and all seems to be working fine. I’ve
ended up modifying the connection adapter to suit my needs though.

Just to clarify that - In my project I’m using a modified sql server
adapter.

I created a new test project, using rails 2.0.2 and the edge sql
server adapter. I added that controller to get/set the session and it
all worked properly.

13

Luke,

I see the same results in a fresh project using 2.0.2 and the 1.0 SQL
server adapter.

Thanks for the test code.

I’ll post the problem with my other app. when I figure it out.

Wes

14

My problem appears to be related to the use of the object_transactions
plugin (see: http://code.bitsweat.net/svn/object_transactions/), which
in turn, relies on the transaction-simple gem.

Removing the object-transactions plugin allows my session to save
correctly.

I haven’t figured out the exact reason yet, but I will and will post
when I have it.

If anyone understands the guts of the object_transactions plugin and/or
the transaction-simple gem and would like to chime in, I’m all ears.

It’s incredibly frustrating to have moved to the object_transactions
plugin as an an effectively mandated part of the upgrade to Rails 2.0,
and then have it totally not work with the ActiveRecord session store.

My faith in being able to upgrade a Rails app. and even hope for plugins
to continue to work is extremely shaken.

Wes

15

Just one more thing to add some evidence to my claim.

If you install the object_transactions plugin (and by association, the
transaction-simple plugin) into a new Rails app. like the one that
Luke mentioned above, you will see the same behavior - your AR session
store will not be saved to/from anymore.

I am going to start a new post about this potential bug.

Wes

16

I think the final answer is here:

http://www.ruby-forum.com/topic/146569

Wes