NYCPHP Meetup

[Ben Sgro]

Hello All,

So, here's the issue I'm stuck against:

We are currently developing on Linux and then deploying to Test, Staging 
and Production on Windows.

We are using the Zend framework, with the PDO driver. (config.ini: 
database.adapter = "pdo_mssql").

The original issue is that when storing XML fields the pdo driver was 
failing to select the "unicode" data from MSSQL.
Here's the error:

Warning: PDOStatement::execute()
       [function.PDOStatement-execute]: message: Unicode data in a 
Unicode-only collation or ntext data
          cannot be sent to clients using DB-Library (such as ISQL) or 
ODBC version 3.7 or earlier. (severity 16)

To fix this, we store the XML in a MSSQL TEXT field. Well, it turns out, 
our client doesn't like that ... they want the ability
to xpath the data.

We returned the field to 'XML' and created a 'view' in MSSQL that cast 
the XML to TEXT. So, now my application selects from the Views,
which works great. However, since the field is now computed (via the 
cast) I am unable to INSERT/UPDATE into it.

I thought, well maybe I can have the selects (fetchAll(), fetchRow()) 
access the VIEW and the Updates/Inserts (insert(), update()) access the 
base table (w/the XML field).
So, I could provide my own implementation to the Zend DB Table Abstract 
insert() and update() methods. Instead of having, say protected $_name = 
'whatever', my models could have another variable named $_viewName = 
'viewWhatever';

However, after looking into that, it doesn't seem too simple. Those 
methods actually pass in a db object, not a table name (like fetchAll() 
and fetchRow()). So programmaticaly, it appears easier to override the 
implementation of fetchAll() and fetchRow().

Lets pretend we can't do that. Another option would be to use Stored 
Procedures. But since my application is already written with ORM/Zend DB 
Table Abstract...that would be a real PITA.

Our client also won't let us provide THEM a view...so keep the fields as 
TEXT for our application and have a VIEW that cast from TEXT to XML (not 
sure if you can even do this) so then they can XPATH to their hearts 
content.

So, I decided that there must be an better driver to use ...  is there?

Does anyone have a solution ?

One of my coworkers just suggested having two fields, TEXT and XML, and 
using a SQLSERVER trigger to keep them up to date.

..Thanks ya'll!!!!!

- Ben