Model validation with SQL Server

Rails
1

Hi,

I have a model that looks like this:

class Assessment < ActiveRecord::Base
…
set_table_name “SCG_Assessment”
set_primary_key “ID_”
validates_presence_of :Name, :Description, :Version
validates_length_of :Name, :maximum => 50
…
end

My setup is windows server 2003 R2, RoR 1.1.6, and SQL Server 2005.
When I intentionally enter a string that is greater than 50 characters,
the write to the database is still attempted, and the user sees an ugly
error like this:

ActiveRecord::StatementInvalid in AssessmentsController#create

DBI::DatabaseError: Execute
OLE error code:80040E57 in Microsoft OLE DB Provider for SQL Server
String or binary data would be truncated.
HRESULT error code:0x80020009
Exception occurred.: INSERT INTO SHAKERCG_Assessment ([Name],
[Version], [State], [Customer_ID_FK], [Description]) VALUES(‘this is my
new assessment with a really long name.’, ‘1’, NULL, 1, ‘hello’)

RAILS_ROOT: ./script/…/config/…
Application Trace | Framework Trace | Full Trace

c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/abstract_adapter.rb:120:in
log' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/sqlserver_adapter.rb:279:ininsert’
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:1739:in
create_without_callbacks' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/callbacks.rb:265:increate_without_timestamps’
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/timestamp.rb:30:in
create' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:1718:increate_or_update_without_callbacks’
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/callbacks.rb:253:in
create_or_update' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:1392:insave_without_validation’
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/validations.rb:736:in
save_without_transactions' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/transactions.rb:126:insave’
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/abstract/database_statements.rb:51:in
transaction' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/transactions.rb:91:intransaction’
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/transactions.rb:118:in
transaction' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/transactions.rb:126:insave’
#{RAILS_ROOT}/app/controllers/assessments_controller.rb:35:in `create’

I want the user to see the flash[:notice] message in a

element
that I have created in the page template. Instead, they are directed to
this type of page. Because this is a legacy schema, the primary key and
table names must all be overriden. I don’t know this is happening
because of SQL Server, or because of not being able to use the RoR
naming conventions.

I also tried wrapping the save call around a begin/rescue block, hoping
to catch the exception (changing to a .save! vs. .save), but that didn’t
seem to help either.

Any suggestions?
-Kevin

2

On Sun, 2007-01-28 at 16:57 +0100, Kevin T. wrote:

…
end

Are you sure you don’t want :maximum => 49? Your test string was
exactly 50 characters long, so according to :maximum => 50 it’s a valid
length.

My setup is windows server 2003 R2, RoR 1.1.6, and SQL Server 2005.

God save you :slight_smile:

–
Matthew B. :: 607 227 0871
Resume & Portfolio @ http://madhatted.com

3

Matthew B. wrote:

Are you sure you don’t want :maximum => 49? Your test string was
exactly 50 characters long, so according to :maximum => 50 it’s a valid
length.

I didn’t realize my input string was 50 characters exactly. I’m
guessing that the varchar(50) in MSSQL also counts a NULL character,
which is why the insert fails with exactly 50 characters.

When I enter a string that is longer than 50, I get an exception as I
would expect. I’m also adding in the error_messages_for construct to my
views, so that errors are displayed more completely. I’ll have to go
around and subtract one from all my lengths, but other than that things
are working as expected.

My setup is windows server 2003 R2, RoR 1.1.6, and SQL Server 2005.

God save you :slight_smile:

Yes, I would much rather have a Linux/MySQL setup, but this is what the
customer wants. oh well.

Thanks again for your help.

4

Kevin T. wrote:

class Assessment < ActiveRecord::Base
…
set_table_name “SCG_Assessment”
set_primary_key “ID_”
validates_presence_of :Name, :Description, :Version
validates_length_of :Name, :maximum => 50
…
end

I want the user to see the flash[:notice] message in a

element
that I have created in the page template. Instead, they are directed to
this type of page. Because this is a legacy schema, the primary key and
table names must all be overriden. I don’t know this is happening
because of SQL Server, or because of not being able to use the RoR
naming conventions.

Kevin,

Hmmm. Can you post your rescue block? I can’t think of why something
along the lines of

begin
obj.save!
rescue
return render(:orig.page)
end

wouldn’t work for you?

Wes