0

I'm attempting to write a PHP script which will use PDO to retrieve XML data stored in an MS SQL database, then parse the XML data using SimpleXML.

The problem is that retrieving the XML data results in the following error message:

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)

I'm type casting to retrieve the XML data, which is stored as text in the database. I don't necessarily need to have this returned as real XML. Retrieving it as a string will be fine, since I'm using SimpleXML to parse the data anyway.

However- if I attempt to retrieve this data as text, without type casting, the string of XML is always cut short. Then SimpleXML spits out a long list of errors like:

Premature end of data in tag ABC line 123

expected '>'

The the string length of the returned XML maxed out at 4999. If I type cast the data to XML, then convert to VARCHAR, like so:

SELECT CONVERT(VARCHAR(MAX), CAST(XML_DATA AS xml))

I'm then able to squeeze a few more characters out of the string - maxing out at a string length of 5085.

The casting and converting was just a wild guess. The only thing that it has proven is that I don't know where these max lengths are coming from, or what they mean.

How can I retrieve the XML data in its entirety? What's causing the cut-off? The PHP/PDO, or the MS SQL database?

Any suggestions?

Additional information: The database is read-only. The server is running PHP Version 5.2.17. The following PDO drivers are installed: sqlite, sqlsrv, mssql, mysql, odbc; PDO Driver for MSSQL DB-lib: MSSQL_70. The XML is from Amazon MWS.

Community
  • 1
  • 1
Jason
  • 329
  • 3
  • 11
  • Did you check these answers about a similar problem with pdo and mysql? Maybe you can understand the issue and see if it's the same with mssql: http://stackoverflow.com/a/11663623/1606729 and http://stackoverflow.com/a/10455228/1606729 – koopajah Dec 06 '12 at 23:49
  • No luck. I get this error message after setting PDO::ATTR_EMULATE_PREPARES to false: "SQLSTATE[IM001]: Driver does not support this function: driver does not support setting attributes" I'm using PDO::query and not PDO::prepare. Would that make a difference? – Jason Dec 07 '12 at 16:26
  • I don't really know, just found this linked question and hoped it would help you :) – koopajah Dec 07 '12 at 16:27
  • Thanks for your suggestions! I assume there's a simple solution out there. After all, I'm just trying to retrieve information from a database. It should be the same as retrieving a lengthy blog post, or whathaveyou. But I don't know enough about MS SQL to understand such caveats, or even enough to phrase a good Google search. – Jason Dec 07 '12 at 18:11

2 Answers2

0

For everyone who stumbles across this question the fix I found was quite easy. PHP.ini by default truncates queried text fields to 4096 bytes.

Two ways to remedy:

  1. In a query update your text limit

mssql_query("SET TEXTSIZE 2147483647");

  1. In php.ini permanently update text size (max size shown below)

mssql.textlimit = 2147483647

mssql.textsize = 2147483647

Source SQL Server PHP and Truncating

Vinny M
  • 762
  • 4
  • 14
-1

The answer here helped me solve the same problem with unbuntu 16.4, php7.2, MSSQL using the FreeTDS solution

https://stackoverflow.com/a/38064487/2096951

Matalina
  • 127
  • 2
  • 11