MS SQL: Required PDO driver 'sqlsrv' is not installed or loaded properly

#1

Trying to connect Dreamfactory 2.0a (from github) to MS SQL, from Ubuntu 14.04 LTS.

Have added the following extensions to php.ini:
extension=pdo_dblib.so
extension=mssql.so

When I run php -i |grep PDO I see some signs that PDO driver for FreeTDS is working.

PDO
PDO support => enabled
PDO drivers => firebird, dblib, mysql
PDO Driver for FreeTDS/Sybase DB-lib => enabled
PDO_Firebird
PDO Driver for Firebird/InterBase => enabled
PDO Driver for MySQL => enabled

Inside PHP itself, in a test.php, I enumerate PDO sources:

<?php foreach (PDO::getAvailableDrivers() as $driver) { echo $driver.'
'; } ?>

Hereā€™s the output:
firebird
dblib
mysql

All that, and yet, I can not connect to an MS SQL database, in Dreamfactory. I thought if I get PHP set up, Dreamfactory should work with MS sql.

What else do I have to do? Is there some configuration file changes required inside the dreamfactory/platform/config to make MS SQL connections? I get the error that required PDO driver sqlsrv is not installed or loaded properly.

W

#2

Hi Warren.
You should be selecting the driver labeled ā€œMS SQL Server/Sybaseā€ in the dropdown, not the one labeled "MS SQL Server."
The former is for the freetds driver. The latter for sqlsrv driver (which is used only on Windows systems.)

You will also need to change the connection string it generates. Where is says mssql, replace it with dblib. This is a bug that is fixed in development and will be pushed to production soon.

#3

Ok that got me closer. The SQL server Iā€™m trying to connect to has an instance name, something like SQLSERVER.mydomain.com\SQLINSTANCE, so my connection is something like this:

dblib:host=hostname\instancename;dbname=MYDATABASE

If I include the port 1433 part it breaks. I suspect an underlying dblib or FreeTDS issue on Ubuntu. But the above string works, and at least the Table names are populated on the Schema Manager.

But the schema manager is showing this error: Table ā€˜TABLENAMEā€™ does not exist in the database.

If it doesnā€™t exist why is the Table name list populating at all? The fact that it found my tables suggests that the DB connection works, and the tables exist.

api_error.png857x472 22.1 KB

Thereā€™s nothing interesting at all in the .log file, just one line each time I pick a table in the Schema page, like this:

[2015-09-28 20:28:29] local.DEBUG: Resource event: mssql1._table.tablename.get.pre_process

ā€¦ Where tablename is the actual name of the table, like ACTFAXCONTROLQUEUE in the screenshot above.

Iā€™m a bit surprised that a schema failure like the one above doesnā€™t result in some kind of useful logging.

Maybe this could be a bug in the dblib stuff in my Ubuntu 14.04 LTS php 5.5.9-1ubuntu4.12 version?

When I try to view the fields, it seems that DreamFactory is enumerating table names, but not the fields in the tables.

Has anyone else tested SQL Server 2016 CTP 2.3 with FreeTDS?

#4

I am experiencing the same problem on the hosted version.

Jan Joost

#5

Proper formatting of connection string for connecting to sql express with the dblib driver:

dblib:host={IP or hostname}:{port}\{instance name};dbname={database name}
#6

Me too Jan, hope it get sorted otherwise I must revert back to v1.9

#7

Just to clarify, is this an issue in general? E.g., this API call fails:

api/v2/mssql1/tablename

Or is this only an issue using the Data manager tab from within the admin console?