SqlSrv v2 and long field names
A good proportion of the projects my company undertakes end up on Windows servers with IIS and SQL Server and hence we use the SqlSrv PHP extension from Microsoft. We don’t host any of these projects ourselves and leave it up to the client’s IT department. This is the main reason that we use a database abstraction layer, Zend_Db, in our case as we can swap the underlying database out with less hassle.
A couple of weeks ago, I came across a problem when installing our app onto the client’s server.
It didn’t work.
This was a surprise as we have a few Windows Server VMWare installations on which we had tested and they had worked fine. The most obvious differences were that this server was 32bit and that it was using v2 of the SqlSrv extension. As there were a number of differences from our usual fare to the client’s install, so it took me a while to (a) build a install that exhibited the problem and (b) reduce the problem to something simple.
The error I was getting is this:
exception 'Zend_Db_Statement_Sqlsrv_Exception' with message
'[Microsoft][SQL Server Native Client 10.0]String data, right truncation'
in C:\Websites\project1\library\Zend\Db\Statement\Sqlsrv.php:237
Googling failed me completely!
I eventually narrowed down the SQL statement that caused the problem:
SELECT email_user_include_form_details FROM "forms"
That’s not especially complicated :)
At the point, I yelled for help which came in the form of Brian Swan of Microsoft. There’s quite a few helpful MS guys around nowadays if you keep your ear close to the PHP community, which is really handy. Brian helped prove that I wasn’t going mad and that there really was a problem.
We determined the problem to be that with v2 of the sqlsrv driver, you cannot have a fieldname longer than 30 characters.
Brian assured me that the team are aware of this and it will be fixed in a subsequent update of the driver. Also, it only affects the sqlsrv driver and not the pdo_sqlsrv driver. Hence, there are two workarounds:
1. Shorten your fieldnames to 30 characters or less
2. Use pdo_sqlsrv
Smart readers will already be commenting that there isn’t a Zend_Db_Adapter_Pdo_Sqlsrv and they’d be right, but it isn’t hard to write one :)
Akrabat_Db_Adapter_Pdo_Sqlsrv
I cobbled together an adapter that works for me and have put it on github. As it’s not in the Zend_ namespace, we have to use an additional key in the application.ini file so that it is loaded:
application.ini:
resources.db.adapter = "Pdo_SqlSrv"
resources.db.params.adapterNamespace = "Akrabat_Db_Adapter"
resources.db.params.host = localhostSQLEXPRESS
resources.db.params.username = testuser
resources.db.params.password = testpassword
resources.db.params.dbname = testdatabase
The key difference from a standard Zend_Db adapter is the use of the resources.db.params.adapterNamespace key which tells the system the full name of the class to load.
My limited testing shows that this adapter works with v2 of pdo_sqlsrv which solves my problem with fieldnames that are longer than 30 characters!
Unfortunately, I found out about this too late for Zend Framework 1.11, so I’ll have to look at getting it into Zend Framework 2.0.
I just recovered from the same disaster. Unfortunately it wasn't until through exhaustive debugging and hair-pulling and finally figuring it out on my own, that I was able to Google "sqlsrv column name too long" and got your article.
Why Microsoft doesn't release a quick patch I don't know.
Thanks for the article.
A quick question, I am developing my first app for deployment on MS SQL Server and IIS. It's using Zend Framework and SQL Server 2005.
Why are you using your own Pdo_SqlSrv and not Pdo_Mssql which is included with ZF? Or why use one over the other? Am I going to hit a wall when I deploy?! (Currently developing on Win 7, not Server).
Simon,
Pdo_Mssql uses the old driver that's not support for SQL Server 2003 or above.
Regards,
Rob…
I've been exploring the nether reaches of the Internet and I can't find an answer.
Has anyone noticed, or better yet measured, the performance difference of using PDO_SQLSRV verses raw SQLSRV or the above wrapper?
Charles,
We have our own perf tests as a part of our test suite. While these are not industry benchmarks and such, we feel they are representative of key web application scenarios.
In a nutshell, the PDO driver is at parity with the v1.1 non-PDO driver, the PDO_SQLSRV driver is slightly faster than SQLSRV v2.0 (<2%).
Hope this helps.
Ashay
Program Manager, SQL Server PHP driver
Simon,
See my response to Charles. We couldn't run even 50% of our perf tests against PDO_MSSQL for comparison because it didn't support many features of SQL Server, so we abandoned those results. The situation with PDO_ODBC was slightly better, but not much.
If you are deploying on Windows and you plan to use features/capabilities of SQL 2005 (or later), then I recommend PDO_SQLSRV.
Ashay Chaudhary
Program Manager, SQL Server PHP driver
Hi,
Regarding the Akrabat_Db_Adapter_Pdo_Sqlsrv class:
The limit method does not work properly for tables having rowcount smaller that limit+offset, for example consider this mysql query:
mysql: select * from users order by username asc limit 10, 10
for a table containing 15 rows will return rows from #11 to #15.
using the limit method from this class, the resulting query will be:
SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 20 "user".* FROM "user" ORDER BY "username" asc) AS inner_tbl ORDER BY "username" DESC) AS outer_tbl ORDER BY "username" asc
– this sql query will return rows #6 to #15 from a similar table
– as a fix, the generated sql query should look like this:
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY "username" asc) AS "num", *
FROM
(SELECT "user".* FROM ."user" ) AS inner_table) as outer_table
WHERE "num" BETWEEN 11 AND 20