NYCPHP Meetup

[Justin Dearing]

Ben,

Your problems will magically go away if you use php on windows. The
freetds driver that linux uses and I assume your linux odbc driver
either uses or stole code from has issues with certain things like
varchar above 255. Check the freeTDS website for details. The
microsoft odbc driver just works, and it works great.

Also, two columns will increase rowsize. since were talking large
strings and not ints, guids or the like this will be a big issue.


On Wed, Jun 4, 2008 at 11:42 AM, Ben Sgro <ben at projectskyline.com> wrote:
> 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
>