Ruby on Rails and MSSQL Server: trying to insert rows with newid() default values.

Question

(this question was originally posted in another forum a few months ago, without getting any answer ... there was no SO at these times!)

My first steps in RoR. I baught one of these books talking about Ruby, and Ruby On Rails. Examples are running correctly on MySQL, so I decided to rewrite them for MS-SQL as a good exercice (and as MS-SQL is our standard database).. and it turned into a bad dream:

I have this table with 2 fields

Tbl_People
----------
id_People        primary key, newid() default value,
namePeople       string

I have an ODBC connection to the database. I can list the records, but ... I cannot make any inserts in the table. The INSERT string generated by Ruby is the following:

INSERT INTO Tbl_People (id_People, namePeople) VALUES('newid','GRONDIER, Philippe')

And will be refused because the string 'newid' cannot be inserted in the uniqueindentifier/primary key field id_People. Logically, the error returned is:

DBI::DatabaseError: 37000 (8169) [Microsoft] [ODBC SQL Server Driver][SQL Server]Conversion failed when converting from a character string to uniqueidentifier

It seems to be so obviously a big and basic problem that I feel I missed something usually written in bold on each RoR training document such as 'Don't try INSERTs through RoR','uniqueIdentifier is Chinese to RoR', or 'RoR doesn't work with MS SQL Server'...

Any idea?

Answer 1

I'm not a Ruby guy, so just keep that in mind. Most of this is just lost in the MySQL->MSSQL translation IMO.

So, your id is of type "uniqueidentifier". One of two things should happen:

a) you should pass in NEWID() as the first parameter. Not the string 'newid', but the function NEWID()

b) don't pass in the first param. Since there is a default value, just insert into the NamePeople column.

Depending on usage, I'd only use uniqueidentifier primary keys if you are dealing with distributed data, and stick to good old INT IDENTITY columns for most key references.

Answer 2

Check out this post, different db type, but essentially the same issue, how to conceal defaulted fields from RoR so an invalid insert isn't generated.

http://inodes.org/blog/category/programming/