column_definitions generates invalid sql

[metaskills]

Reported by Seth Ladd | April 29th, 2009 @ 10:39 PM

Hello,

This may be a configuration issue, as I can't believe the released version has this bug. However, I'll include the diff for reference. Notice the missing period between the db_name and the INFORMATION_SCHEMA

My configuration looks like this:

development: adapter: sqlserver mode: odbc dsn: DRIVER=/opt/local/lib/libtdsodbc.so;TDS_Version=8.0;SERVER=10.0.6.20;DATABASE=dses_production;Port=1433;uid=pandora;pwd=XXXXXXX;

The diff:
http://gist.github.com/104210

Confirmation on correct setup? Can't imagine I'm the only one that is running into this, so tips on correct usage are much appreciated. Thanks

[metaskills]

Yea... this is a major problem and I'm inclined to think there is something special about your setup, else a LOT MORE people would be seeing this, including myself. Can you investigate that a bit more? What table name setting (if any) are you using in a particular AR model? Does this happen everywhere? SQL Server version? Etc etc. The more info the better.

[metaskills]

Reported by chagar | April 7th, 2009 @ 06:12 PM

When the model has set_table_name 'MYDATABASE.dbo.myTable', I get the following error, because the column_definitions method lacks a period in its query, or because unqualify_db_name is getting a database name that lacks a period.

Adding a single period in one place, makes it work.

The relevant line in column_definitions is:
FROM #{db_name}INFORMATION_SCHEMA.COLUMNS columns

Error message:

DBI::DatabaseError: S0002 (208) [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'MYDATABASEINFORMATION_SCHEMA.COLUMNS'.: SELECT 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, CASE WHEN columns.DATA_TYPE IN ('nchar','nvarchar') THEN columns.CHARACTER_MAXIMUM_LENGTH ELSE COL_LENGTH(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 COLUMNPROPERTY(OBJECT_ID(columns.TABLE_NAME), columns.COLUMN_NAME, 'IsIdentity') = 0 THEN NULL ELSE 1 END as is_identity FROM MYDATABASEINFORMATION_SCHEMA.COLUMNS columns WHERE columns.TABLE_NAME = 'myTable' ORDER BY columns.ordinal_position

[sethladd]

sql server 2005, and we're always using the simple table name, like set_table_name 'foo'

[metaskills]

Us too... any idea why this is not working? Can you provide a patch and run the tests against it?

[sethladd]

there's a patch on the original lighthouse issue. take a look at that and let me know.

[metaskills]

I see this, http://gist.github.com/104210

But that does not tell me if there are tests and/or this has been tested with all the unit tests for the adapter/activerecord?

[ReggieBeans]

I get the same error.

I presume the issue is that if a database name isn't specified the SQL code needs to be just:

FROM INFORMATION_SCHEMA.COLUMNS columns

Therefore add a private method:

def db_name_with_period(db_name)

"#{db_name}."

end

And then update the problem line to:

FROM #{db_name_with_period(db_name) if db_name}INFORMATION_SCHEMA.COLUMNS columns

[metaskills]

I fixed this in a few places that should wrap up this ticket.
http://github.com/rails-sqlserver/2000-2005-adapter/commit/b9e786b0435840d91681a3e998b15d67de2e5b59

[ReggieBeans]

That should do it, and a neater alternative to my suggestion :o)