«
»

Zend Framework connection to SQL Server using SqlSrv

This post is part of a series about my experiences building a PHP app for Windows Server 2008 and IIS 7 for the European WinPHP Challenge 2009 which is sponsored by iBuildings, Microsoft and Leaseweb.

I haven't managed to get much time on the challenge this week as I had hoped. Both my sons' birthday parties have taken place along with one son's birthday (the other's is tomorrow).

It turns out that fellow challenger Juozas Kaziukenas also needs a connection between Zend Framework and SQL Server, so we have joined forces to create App_Db_Adapter_SqlSrv and associated classes. Juozas started a project on codeplex at http://zfmssql.codeplex.com/ and has enabled me to commit to the project too.

Juozas committed an initial implementation and so far I've updated the _connect() method to allow for all the parameters supported by sqlserv_connect().

My current application.ini looks like this:

resources.db.adapter = SQLSRV
resources.db.params.adapterNamespace = "App_Db_Adapter"
resources.db.params.host = "RKAWIN2008\SQLEXPRESS"
resources.db.params.username = "rob"
resources.db.params.password = "123456"
resources.db.params.dbname = zf-tutorial
resources.db.params.driver_options.ConnectionPooling = 1
resources.db.params.driver_options.Encrypt = 0
resources.db.params.driver_options.TransactionIsolation = SQLSRV_TXN_READ_COMMITTED

The adapter correctly converts the SQLSRV_TXN_READ_COMMITTED string from the ini file into the constant value required by the sqlsrv_connect() function using the very useful constant() function.

I had a slight hiccup with detection of the table's primary key. Microsoft's documentation is quite clear once you have found what you are looking for, so that was easily fixed.

At this point the adapter is working for my ZF tutorial, though more work needs to be done on it.

This entry was posted on Sunday, 10th May 2009 at 16:30 and is filed under WinPHP Challenge.

10 Responses to “Zend Framework connection to SQL Server using SqlSrv”

  1. 1 Paul Bain
    July 3rd, 2009 at 14:47 #

    I wish I had read this post 7 days ago! I've had so many problems with MS SQL and PHP!

  2. 2 Chris B
    November 24th, 2009 at 19:03 #

    So I'm trying to connect from ZF running on XAMP on OS X to sql server running on a server. Can I use sqlsrv at all on OS? Or does it only work on a windows machine? Thanks if you can help!
    -chris

  3. 3 Rob...
    November 24th, 2009 at 19:58 #

    Hi Chris,

    The Sqlsrv driver is Windows only. I have no idea how to connect to SQL Server from a non-Windows machine. Sorry.

    Rob...

  4. 4 Chris B
    November 24th, 2009 at 20:32 #

    Thanks Rob! That'll save me some time from trying. :)

  5. 5 yannux
    June 29th, 2010 at 09:03 #

    Hi Rob,

    What's about this adapter ? I don't find it on Zend Framework documentation.

  6. 6 Rob...
    June 29th, 2010 at 09:33 #

    yannux:

    http://framework.zend.com/manual/en/zend.db.adapter.html#zend.db.adapter.adapter-notes.sqlsrv

    Regards,

    Rob...

  7. 7 yannux
    June 29th, 2010 at 09:57 #

    Oups, sorry I didn't see it.

    Thanks a lot !

  8. 8 stef
    May 21st, 2011 at 09:34 #

    Old post but still relevant! Very happy I stumbled on this.

  9. 9 prasanth
    May 31st, 2011 at 12:24 #

    Hi,

    I want to connect SQLSERVER (MSSQL) with zend(MVC).

    when I am trying to connect with PDO_MSSQL getting error like
    "The mssql driver is not there"

    Please help me out.

    thanks in advance
    prasath

  10. 10 Rob...
    May 31st, 2011 at 12:49 #

    Prasath,

    Use the SqlSrv driver.

    Regards,

    Rob...

The views expressed in these comments are not the views of the publisher. However, we believe in the rights of others to express their legitimate views and concerns. Any legitimate complaint emailed to rob@akrabat.com will be seriously considered and the post reviewed as desirable and necessary.