NYCPHP Meetup

[Ben Sgro]

Hello Justin,

The problem does not go away on Windows:

SQLSTATE[HY000]: General error: 10007 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. [10007] (severity 5) [(null)]

- Ben

Justin Dearing wrote:
> 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
>>
>>     
> _______________________________________________
> 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
>
>