Forum: Ruby on Rails model validation with SQL Server

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.
Da734ede01dc6e5bace38f5fbc0fc4f6?d=identicon&s=25 Kevin Tambascio (ktambascio)
on 2007-01-28 16:57
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:in
`insert'
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:in
`create_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:in
`create_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:in
`save_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:in
`save'
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:in
`transaction'
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:in
`save'
#{RAILS_ROOT}/app/controllers/assessments_controller.rb:35:in `create'

I want the user to see the flash[:notice] message in a <div> 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
39b36b2be47228f8619d61ea7a607a25?d=identicon&s=25 Matthew Beale (mixonic)
on 2007-01-28 17:32
(Received via mailing list)
On Sun, 2007-01-28 at 16:57 +0100, Kevin Tambascio 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 :-)

--
Matthew Beale :: 607 227 0871
Resume & Portfolio @ http://madhatted.com
Bb4bdf2b184027bc38d4fb529770cde5?d=identicon&s=25 Wes Gamble (weyus)
on 2007-01-28 17:57
Kevin Tambascio 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 <div> 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
Da734ede01dc6e5bace38f5fbc0fc4f6?d=identicon&s=25 Kevin Tambascio (ktambascio)
on 2007-01-28 18:42
Matthew Beale 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 :-)

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

Thanks again for your help.
This topic is locked and can not be replied to.