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)

Answer 1

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?

Answer 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.