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
_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
