MS SQL Server 上的 ODBC 查询仅在 PHP PDO (FreeTDS) 中返回前 255 个字符
我目前正在尝试从 SQL Server 数据库视图中提取一些数据，我们对 Linux Web 服务器的访问受到限制。
我们不需要编辑数据，只需将其显示在网页中即可。
一切看起来都很好，直到我们尝试输出并仅获取文本字段的前 255 个字符。
有谁知道这是否是通过 PHP::PDO 使用 FreeTDS 的问题或者它是否应该正常工作？我看到其他人也有类似的问题，但似乎没有太多答案。
我使用它作为 MS SQL 数据库的连接字符串：
$dbConn = new PDO("odbc:Driver=FreeTDS;DSN=OURDSN;UID=WWWUser;PWD=ourpassword");
I'm currently trying to pull some data from a SQL Server database view that we have restricted access to from our Linux web server.
We don't need to edit the data just display it in a webpage.
It all looks fine until we try to output and only get the first 255 characters of a text field.
Does anyone know if this is a problem with using FreeTDS through PHP::PDO or if it should work fine? I've seen other people out there having similar problems, but there don't seem to be many answers.
I'm using this as the connection string for the MS SQL db:
$dbConn = new PDO("odbc:Driver=FreeTDS;DSN=OURDSN;UID=WWWUser;PWD=ourpassword");
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
根据 FreeTDS 用户指南，问题似乎是 FreeTDS 只能处理
varchar与 SQL Server 通信时最多 255 个字符“由于协议定义固有的限制”。任何大于此值的数据都必须是text数据类型。您可以通过相应地修改架构或在查询期间转换数据类型来解决该问题，如下所示：
According to the FreeTDS User Guide, the issue seems to be that FreeTDS can only handle
varcharup to 255 characters when talking to SQL Server "due to limitations inherent in the protocol definition". Anything bigger than that needs to be data typetext.You can resolve the issue either by modifying your schema accordingly, or converting the data type during your query, like this:
您可以增加 FreeTDS 使用的 /etc/odbc.ini 文件中文本字段的大小。
您还可以尝试使用 PHP 低级 odbc 例程来确保您可以获得该级别的数据检索，然后恢复使用 PDO。
You can increase the size of text fields in the /etc/odbc.ini file used by FreeTDS.
You can also try using the PHP low level odbc routines to make sure that you can get that level of data retrieval, then work back up to using PDO.
默认情况下，FreeTDS 使用协议版本 4.2 如果将协议升级到 7.0，则可以检索超过 255 个字节的 varchar。您可以使用“CAST” hack，也可以更改列 col varchar(max)。
varchar(max) 是与 varchar 完全不同的列类型（DATA_TYPE 2005 vs 12），将通过 freetds 4.2 进行流式传输，不会进行截断。
为什么不升级到版本7？因为UTF-8不能用较新的协议存储在varchar中。 SQL Server 将以 UCS-2（如 UTF-16）传输所有协议信息，并在保存之前将数据转换为表或列排序规则。但是，这需要您在 UTF8 数据前加上 N 前缀。 INSERT into tbl (txt) values (N'hello world')
为什么不 CAST？ CAST AS TEXT 与 MySQL 不兼容（需要执行 CAST AS CHAR）。
坚持协议 4.2 并将 varchar 定义为 varchar(max) 让您可以编写最兼容的 SQL。
FreeTDS, by default, uses protocol version 4.2 If you up the protocol to 7.0 you can retrieve more than 255 bytes of a varchar. You can use the "CAST" hack, or you can ALTER COLUMN col varchar(max).
varchar(max) is a completely different column type than varchar (DATA_TYPE 2005 vs 12) and will be streamed over freetds 4.2 w/o truncating.
Why not upgrade to version 7? Because UTF-8 cannot be stored in varchar with newer protocols. SQL Server will transmit ALL protocol information in UCS-2 (like UTF-16) and convert your data into the table or column collation before saving. But, this requires you to prefix UTF8 data with N. INSERT into tbl (txt) values (N'hello world')
Why not CAST? CAST AS TEXT is not compatible with MySQL (need to do CAST AS CHAR).
Staying on protocol 4.2 and defining your varchars as varchar(max) let's you write the most compatible SQL.
关于这个问题还有一个事实。截至 2015 年，返回 XML 类型的值会导致前 256 字符被干净地返回。然而，XML 的其余大部分将作为明显的随机垃圾返回，偶尔会出现一些清晰的文本片段。事实上，如果我不得不猜测的话，查询会返回 256 之后所有字符的随机内存块。
在我的特定情况下，我生成 XML（使用多个 FOR XML 嵌套查询）以发送到网站进行显示。在这种情况下，我找到的解决方案是使用 CAST hack，将数据转换为 varchar(max)。
因此请记住：如果您在查询结果中看到一个由 256 个清晰字符组成的块，后跟随机垃圾，则它可能是以 XML 类型而不是 varchar(max) 类型返回的 XML 值。
注意：这可能仅适用于动态生成 XML 的情况。
One more factoid for this issue. As of 2015, returning a value of type XML results in the first 256 characters being returned cleanly. However, most of the rest of the XML will be returned as apparently random garbage, with an occasional clear fragment of text. In fact, if I had to guess, the query returns a random block of memory for all characters after 256.
In my specific case, I was generating XML (using multiple FOR XML nested queries) to send to a website for display. In this case, the solution I found was to use the CAST hack, casting the data to varchar(max).
So remember: If you see a block of 256 clear characters followed by random garbage in your query results, it is probably an XML value being returned as an XML type instead of a varchar(max) type.
CAVEAT: This may only apply if the XML is dynamically generated.