Having an issue with default values on active record objects

[Kenneth Ortmann]

My database tables have default values on them.

I've tracked the issue I am having down to this:

client = TinyTds::Client.new username: "**", password: "**", host: "**", database: "**"

client.active?

sql = "EXEC sp_executesql N' SELECT DISTINCT columns.TABLE_NAME AS table_name, columns.COLUMN_NAME AS name, columns.DATA_TYPE AS type, columns.COLUMN_DEFAULT AS default_value, columns.NUMERIC_SCALE AS numeric_scale, columns.NUMERIC_PRECISION AS numeric_precision, columns.ordinal_position, CASE WHEN columns.DATA_TYPE IN (''nchar'',''nvarchar'') THEN columns.CHARACTER_MAXIMUM_LENGTH ELSE COL_LENGTH(''''+columns.TABLE_SCHEMA+''.''+columns.TABLE_NAME, columns.COLUMN_NAME) END AS [length], CASE WHEN columns.IS_NULLABLE = ''YES'' THEN 1 ELSE NULL END AS [is_nullable], CASE WHEN KCU.COLUMN_NAME IS NOT NULL AND TC.CONSTRAINT_TYPE = N''PRIMARY KEY'' THEN 1 ELSE NULL END AS [is_primary], c.is_identity AS [is_identity] FROM INFORMATION_SCHEMA.COLUMNS columns LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC ON TC.TABLE_NAME = columns.TABLE_NAME AND TC.CONSTRAINT_TYPE = N''PRIMARY KEY'' LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU ON KCU.COLUMN_NAME = columns.COLUMN_NAME AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND KCU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG AND KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA INNER JOIN .sys.schemas AS s ON s.name = columns.TABLE_SCHEMA AND s.schema_id = s.schema_id INNER JOIN .sys.objects AS o ON s.schema_id = o.schema_id AND o.is_ms_shipped = 0 AND o.type IN (''U'', ''V'') AND o.name = columns.TABLE_NAME INNER JOIN .sys.columns AS c ON o.object_id = c.object_id AND c.name = columns.COLUMN_NAME WHERE columns.TABLE_NAME = @0 AND columns.TABLE_SCHEMA = @1 ORDER BY columns.ordinal_position ', N'@0 nvarchar(max), @1 nvarchar(max)', @0 = N'registrations', @1 = N'IonicImCloud'"

results = client.execute(sql)

results.each{|x| puts x};nil

results:

{"table_name"=>"registrations", "name"=>"id", "type"=>"int", "default_value"=>nil, "numeric_scale"=>0, "numeric_precision"=>10, "ordinal_position"=>1, "length"=>4, "is_nullable"=>nil, "is_primary"=>1, "is_identity"=>true}

{"table_name"=>"registrations", "name"=>"username", "type"=>"nvarchar", "default_value"=>nil, "numeric_scale"=>nil, "numeric_precision"=>nil, "ordinal_position"=>2, "length"=>255, "is_nullable"=>1, "is_primary"=>nil, "is_identity"=>false}

{"table_name"=>"registrations", "name"=>"device_id", "type"=>"nvarchar", "default_value"=>nil, "numeric_scale"=>nil, "numeric_precision"=>nil, "ordinal_position"=>3, "length"=>255, "is_nullable"=>1, "is_primary"=>nil, "is_identity"=>false}

{"table_name"=>"registrations", "name"=>"tls_csr", "type"=>"nvarchar", "default_value"=>nil, "numeric_scale"=>nil, "numeric_precision"=>nil, "ordinal_position"=>4, "length"=>-1, "is_nullable"=>1, "is_primary"=>nil, "is_identity"=>false}

{"table_name"=>"registrations", "name"=>"media_csr", "type"=>"nvarchar", "default_value"=>nil, "numeric_scale"=>nil, "numeric_precision"=>nil, "ordinal_position"=>5, "length"=>-1, "is_nullable"=>1, "is_primary"=>nil, "is_identity"=>false}

{"table_name"=>"registrations", "name"=>"random_id", "type"=>"nvarchar", "default_value"=>nil, "numeric_scale"=>nil, "numeric_precision"=>nil, "ordinal_position"=>6, "length"=>255, "is_nullable"=>1, "is_primary"=>nil, "is_identity"=>false}

{"table_name"=>"registrations", "name"=>"status", "type"=>"nvarchar", "default_value"=>nil, "numeric_scale"=>nil, "numeric_precision"=>nil, "ordinal_position"=>7, "length"=>255, "is_nullable"=>1, "is_primary"=>nil, "is_identity"=>false}

Multiple of those columns have default values.

If I take the straight sql and run it on my sqlserver, the result set contains default values.

I don't know where the issue is, but since TinyTds is mainly compiled code, I am out of the scope of ruby and unable to trace this down any further.

[Ken Collins]

Hey Kenneth!

Can you hop into our Gitter room (https://gitter.im/rails-sqlserver/activerecord-sqlserver-adapter) and chat today. I think it offers more bandwidth and lazy replies vs the Google Group which I would like to deprecate.

Also, when you head there, let's talk about 1 single column and focus on that specific column first. It would help ID your issue faster by not having to scan thru the large examples you posted.

 - Cheers,

    Ken