rowguid uniqueidentifier column - default value causes error on create

[stewbawka]

After using the sql server replication wizard on my tables, which added in the rowguid column of type 'uniqueidentifier', I am now getting an error when trying to save a new record. The error is:

ActiveRecord::StatementInvalid in MallsController#create

ODBC::Error: 37000 (8169) [unixODBC][FreeTDS][SQL Server]Conversion failed when converting from a character string to uniqueidentifier.: INSERT INTO [malls]([name], [created_at], [updated_at], [rowguid]) VALUES('ewqrweq', '2010-06-28 18:18:27.139', '2010-06-28 18:18:27.139', 'newsequentialid(')

partial stack trace:

/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract_adapter.rb:221:in log' /usr/local/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.6/lib/active_record/connection_adapters/sqlserver_adapter.rb:916:indo_execute'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.6/lib/active_record/connection_adapters/sqlserver_adapter.rb:389:in execute' /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/database_statements.rb:259:ininsert_sql'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.6/lib/active_record/connection_adapters/sqlserver_adapter.rb:903:in insert_sql' /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/database_statements.rb:44:ininsert_without_query_dirty'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/query_cache.rb:19:in insert' /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/base.rb:2949:increate_without_timestamps'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/timestamp.rb:53:in create_without_callbacks' /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/callbacks.rb:266:increate'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/base.rb:2915:in create_or_update_without_callbacks' /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/callbacks.rb:250:increate_or_update'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/base.rb:2573:in save_without_validation' /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/validations.rb:1090:insave_without_dirty'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/dirty.rb:79:in save_without_transactions' /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/transactions.rb:229:insend'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/transactions.rb:229:in with_transaction_returning_status' /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/database_statements.rb:136:intransaction'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/transactions.rb:182:in `transaction_without_cache_transaction'

[metaskills]

The adapter "should" handle GUIDs gracefully. If not it would be easy to add a test case and do so. Before we get there tho, your stack trace give me pause. Quite some time ago I remember the schema reflection method #column_definitions had a patch that would allow it to recognize/ignore function names (for instance to set a date) in a column default. A long time ago the adapter mistook that function for a string value.

Are you certain that the GUID function is correctly setup? Just making sure. Are you interested too in creating a tested patch?

[stewbawka]

i'm not too sure what to tell you regarding the ignoring of function names.. that is with adapter 2.3.6.

the field was added by the sql server replication wizard. I can only assume that it set the default value for the rowguid to use the correct guid function.

I would be happy to help out in patching some code to test a fix.

[metaskills]

What version of SQL Server are you on?

[stewbawka]

2008

[stewbawka]

10.50.1600 to be specific

[metaskills]

Issue 42, nice :)

I'm wiring up some tests now. Some using NEWID() and some using NEWSEQUENTIALID(). Will get back soon.

[metaskills]

Take a look at this commit and let me know what you think. Unfortunately AR does not yet allow default functions to be included "unquoted" in the INSERT statement, so you have to do a callback on the model to set it before create. Test has an example of this. You can even see the two helpers for the two functions that get guid back.

[metaskills]

Commit:
http://github.com/rails-sqlserver/2000-2005-adapter/commit/aa8be6380a279db32015f853fc56f7900f7753d8

[metaskills]

Closing this out.

[bairdrt]

I'm a PITA today - the commit link on this is broken. Could we get a new link to these test cases so we can see how this should be working?

Thanks a ton!

[metaskills]

Project was renamed. Here it is.

http://github.com/rails-sqlserver/activerecord-sqlserver-adapter/commit/aa8be6380a279db32015f853fc56f7900f7753d8