SQL Server API error selecting from system view information_schema.schemata

REST API
1

I have created a service that connects to a sql server DB. I get the following error trying any of the API’s though

{
“error”: [
{
“error_detail”: {
“SQLSTATE”: “42S02”,
“driver_name”: “mysql”,
“driver_error_code”: 208,
“driver_error_message”: “[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name ‘information_schema.schemata’.”
},
“message”: “CDbCommand failed to execute the SQL statement: SQLSTATE[42S02]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name ‘information_schema.schemata’.”,
“code”: 500
}
]
}

I can run the query “select * from INFORMATION_SCHEMA.SCHEMATA” on the server to get details from the system view but cannot run “select * from information_schema.schemata”

Looking at the error log I don’t know why the driver_name is “mysql” ?

Is there a way around this?

2

We have seen this before when SQL Server is configured to be case-sensitive. This is not the average or expected config, so we had not allowed for it. The 1.x version of the API doesn’t expect that the actual thing that exists in the DB is INFORMATION_SCHEMA.SCHEMATA and that it cannot be addressed in lowercase.

This is resolved in 2.0. If you would like to enforce uppercase in 1.x you will need to edit

<install path>/vendor/dreamfactory/yii/framework/db/schema/mssql/CMssqlSchema.php

There should be two instances, one near line 491 and one near 896, which you may edit to use uppercase.

3

I do need to work with case sensitive sql server. I have made the code changes recommended and works a treat.

Thanks.

1 Like