Alternative needed for PDO connection when using MSSQL via dblib

Question

I need to connect to a remote MSSQL server using PHP. I've successfully connected using the dblib, but it has some pretty "nasty" limitations.

I discovered it is unable to fetch column rows that are in the ntext format, there is a simple workaround for this, but it limits the output (converting the ntext to nvarchar via the query), I then noticed it also has problems fetching the rows in decimal format (or so it seems, unfortunately the General error 4004 thrown for everything isn't very helpful).

There is just too much hacking required for my liking, is there a better way to approach this, maybe a different db library I should look into for my connection?

It's worth noting that I've used mssql_* with great success, but it is obviously not an ideal approach and I wish to keep using PDO.

Things worth noting;

I'm running on Debian, and I know Microsoft has released drivers for ODBC, but their driver downloads are bugged and thus don't work (at least not on non-RHEL systems it seems)

If you change your tds version to 8.0 does it make any difference? `tds version = 8.0` — Benny Hill, Feb 27 '13 at 16:33
Here is how I've set up a connection from [Ubuntu to MSSQL](http://stackoverflow.com/questions/13066716/freetds-not-using-its-config/13069820#13069820) - does any of that help you? — Benny Hill, Feb 27 '13 at 16:51
That was actually the resource I used to try and make it work with ODBC over dblib earlier today. — Clorith, Feb 27 '13 at 16:55
Sorry, I'm afraid that's the extent of my knowledge about connecting to a MSSQL server from *nix. I've not heard about the problems you are having but I'm also not a developer who is working with the MSSQL databases in our shop. — Benny Hill, Feb 27 '13 at 17:14
Your connection string should look like this: `$con = new PDO("dblib:dbname=$db_name;host=$host", $username, $password);` — Benny Hill, Feb 27 '13 at 20:18
My connection works if I use dblib, problem is dblib lacks support for a lot of column types which are used in my database, so it is virtually useless to me. — Clorith, Feb 28 '13 at 08:04
test from command line using following, substitute as necessary `TDSDUMPCONFIG=stdout TDSDUMP=stdout tsql -S -U sa -p 1443 -P `. Post results — shorif2000, Mar 19 '13 at 16:31

Francis Avila · Answer 1 · 2013-02-27T14:33:38.223

2

Do not use the dblib/mssql: driver.

If you are on windows, use the sqlsrv: driver. (You have to download some DLLs--instructions linked.)

If you are on a unix, use the PDO odbc: driver, preferably the unixODBC flavor. On Debian and Ubuntu you can get this driver with a simple apt-get install php5-odbc.

edited Feb 27 '13 at 14:33

answered Feb 27 '13 at 13:59

Francis Avila

29,546
6
54
93

I don't have this driver, and from what I've been told it's a Windows driver, I should probably have mentioned I am on Debian. – Clorith Feb 27 '13 at 14:00
I would love to, but the Microsoft ODBC drivers are the only ones I've found, and they are only for RHEL (tried running on debian, didn't work) – Clorith Feb 27 '13 at 14:20
What? Just apt-get install [php5-odbc](http://packages.debian.org/squeeze/php5-odbc). It's a PDO driver, uses unixODBC under the hood. Not from Microsoft. – Francis Avila Feb 27 '13 at 14:30
I thought so as well, but when doing so this is the outcome; `Connection failed: SQLSTATE[IM002] SQLDriverConnect: 0 [unixODBC][Driver Manager]Data source name not found, and no default driver specified` Connecting using the string $db = new PDO( 'odbc:dbname=MyDB;host=DBHost', 'Username', 'Password' ); – Clorith Feb 27 '13 at 14:32
This is a configuration issue--your dsn is incorrect. You need to either configure a datasource or use a [full connection string](http://www.connectionstrings.com/sql-server#microsoft-sql-server-odbc-driver). You're a bit beyond the scope of the question now and my expertise, but this is really just a matter of getting the right DSN string. [Some more ideas here](http://www.php.net/manual/en/ref.pdo-odbc.connection.php) – Francis Avila Feb 27 '13 at 14:37
@Strifariz, it looks like you probably need something like `odbc:Driver={SQL Server};Server=DBHost;Database=MyDB` – Francis Avila Feb 27 '13 at 14:46
I've had a look, unfortunately the SQL Server and SQL Native Client options for ad river are only available on windows (I've tested them there though, and they work like a charm). My current string is `$db = new PDO( 'odbc:Driver={freetds};Server=' . $host . ';Database=' . $db . ', $username, $password );` which unfortunately also doesn't work, but it hopefully helps to show what is being attempted – Clorith Feb 27 '13 at 18:58
The `Driver` field in FreeTDS is a reference to an entry in an [odbcinst.ini file](http://www.freetds.org/userguide/dsnless.htm). Do you have that? You also may need to specify a protocol version? See [this troubleshooting guide](http://www.freetds.org/userguide/odbcdiagnose.htm). – Francis Avila Feb 27 '13 at 19:24
I do have "freetds" as an entry in my odbcinst.ini file. The troubleshooting guide didn't cover my problem as ODBC appears to be set up correctly. odbcinst.ini: http://pastebin.com/5B6Th9Ab - odbc.ini: http://pastebin.com/yRzARDFA – Clorith Feb 28 '13 at 07:34

score 0 · Answer 2 · answered Mar 22 '13 at 21:07

When I execute:

TDSDUMPCONFIG=stdout TDSDUMP=stdout tsql -S <name in TDS.conf> -U '<domain>\<username>' -p 1443 -P <password>

I get this:

log.c:196:Starting log file for FreeTDS 0.91
        on 2013-03-22 15:55:50 with debug flags 0x4fff.
config.c:185:Getting connection information for [XXX].
config.c:189:Attempting to read conf files.
config.c:354:... $FREETDSCONF not set.  Trying $FREETDS/etc.
config.c:367:... $FREETDS not set.  Trying $HOME.
config.c:293:Could not open '/home/hillb/.freetds.conf' ((.freetds.conf)).
config.c:297:Found conf file '/etc/freetds/freetds.conf' (default).
config.c:483:Looking for section global.
config.c:540:   Found section global.
config.c:543:Got a match.
config.c:565:   text size = '64512'
config.c:540:   Found section egserver50.
config.c:540:   Found section egserver70.
config.c:540:   Found section XXX.
config.c:554:   Reached EOF
config.c:483:Looking for section XXX.
config.c:540:   Found section global.
config.c:540:   Found section egserver50.
config.c:540:   Found section egserver70.
config.c:540:   Found section XXX.
config.c:543:Got a match.
config.c:565:   host = 'XXX'
config.c:595:Found host entry XXX.
config.c:599:IP addr is xxx.xxx.xxx.xxx.
config.c:565:   port = '1433'
config.c:565:   tds version = '8.0'
config.c:788:Setting tds version to 8.0 (0x701) from $TDSVER.
config.c:554:   Reached EOF
config.c:301:Success: [XXX] defined in /etc/freetds/freetds.conf.
config.c:714:Setting 'dump_file' to 'stdout' from $TDSDUMP.
config.c:224:Final connection parameters:
config.c:225:            server_name = XXX
config.c:226:       server_host_name = XXX
config.c:227:                ip_addr = xxx.xxx.xxx.xxx
config.c:228:          instance_name =
config.c:229:                   port = 1433
config.c:230:          major_version = 7
config.c:231:          minor_version = 1
config.c:232:             block_size = 0
config.c:233:               language = us_english
config.c:234:         server_charset =
config.c:235:        connect_timeout = 0
config.c:236:       client_host_name = x
config.c:237:         client_charset = UTF-8
config.c:238:               app_name = TSQL
config.c:239:              user_name = <domain>\<username>
config.c:242:                library = TDS-Library
config.c:243:              bulk_copy = 0
config.c:244:      suppress_language = 0
config.c:245:          encrypt level = 0
config.c:246:          query_timeout = 0
config.c:249:               database =
config.c:250:              dump_file = stdout
config.c:251:            debug_flags = 0
config.c:252:              text_size = 64512
config.c:253:           broken_dates = 0
config.c:254:     emul_little_endian = 0
config.c:255:      server_realm_name =
log.c:196:Starting log file for FreeTDS 0.91
        on 2013-03-22 15:55:50 with debug flags 0x4fff.
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
iconv.c:330:tds_iconv_open(0x98cb9e8, UTF-8)
iconv.c:187:local name for ISO-8859-1 is ISO-8859-1
iconv.c:187:local name for UTF-8 is UTF-8
iconv.c:187:local name for UCS-2LE is UCS-2LE
iconv.c:187:local name for UCS-2BE is UCS-2BE
iconv.c:349:setting up conversions for client charset "UTF-8"
iconv.c:351:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
iconv.c:394:tds_iconv_open: done
net.c:205:Connecting to xxx.xxx.xxx.xxx port 1433 (TDS version 7.1)
net.c:270:tds_open_socket: connect(2) returned "Operation now in progress"
net.c:310:tds_open_socket() succeeded
util.c:156:Changed query state from DEAD to IDLE
net.c:741:Sending packet

[hex dumps redacted]

token.c:555:processing result tokens.  marker is  fd(DONE)
token.c:2339:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
token.c:2355:tds_process_end() state set to TDS_IDLE
util.c:156:Changed query state from READING to IDLE
token.c:2370:                rows_affected = 0
util.c:104:logic error: cannot change query state from IDLE to PENDING
token.c:540:tds_process_tokens(0x98cb9e8, 0xbf82fb48, 0xbf82fb4c, 0x100)
token.c:543:tds_process_tokens() state is COMPLETED
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"

What exactly are you looking for?

Alternative needed for PDO connection when using MSSQL via dblib

2 Answers2