RODBC sqlQuery() 在应该返回 varchar(MAX) 时返回 varchar(255) [英] RODBC sqlQuery() returns varchar(255) when it should return varchar(MAX)

问题描述

我正在使用 RODBC 包来查询数据库中的文本列.该数据库建立在 Microsoft SQL Server 2008 R2 之上.SQL中列的数据类型为nvarchar(max).

I am using the RODBC package to query a text column from a database. The database is built on Microsoft SQL Server 2008 R2. The data type of the column in SQL is nvarchar(max).

但是，当我运行时:

# Set up ODBC connection to CCWEB5 production server
# Note: default database is set to "CCSalary"
ccweb5.prod <- odbcConnect("ccweb5")

# Read in some job ad text
job.text <- sqlQuery(ccweb5.prod,"
  SELECT TOP 100
    ja.JobTitle,
    ja.JobText as 'JobText',
    LEN(ja.JobText) as 'JobTextLength'
  FROM JobStore.dbo.JobAd as ja (NOLOCK)
")

在 SQL 中，我期望(对于第一行):

Within SQL, I am expecting (for the top row):

JobTitle                     JobText              JobTextLength
IT Field Service Technician  <text goes here...>  2742

但是，当我这样做时:nchar(as.character(job.text[1,2]))

返回:255.

所以我的问题是，是什么导致了这种截断，我该如何避免它?谢谢！！

So my question is, what is causing this truncation and how do I avoid it? Thanks!!

推荐答案

好的，看来我找到了解决方法.经过更多的谷歌搜索，我发现:

OK, so it seems that I have found a work-around to this. After some more Google'ing, I found that:

SQL Native Client ODBC 驱动程序需要考虑的一件事是 VARCHAR(MAX) 没有固定大小，并且 ODBC 驱动程序通过返回最大列大小 0 来表示这一点.这可能会造成混淆您的应用程序如果它不检查 0 作为特殊情况.见本文底部:http://msdn.microsoft.com/en-us/library/ms130896.aspx 但是一般来说我我的任何 .NET 应用程序都没有看到这种情况发生在 ADO.NET 中正确处理.

One thing to consider with the SQL Native Client ODBC driver is that VARCHAR(MAX) has does not have fixed size and the ODBC driver represents this by returning a max column size of 0. This can confuse your application if it doesn't check for 0 as a special case. See the bottom section of this article: http://msdn.microsoft.com/en-us/library/ms130896.aspx But in general I have not seen this happen with any of my .NET applications as it is handled properly in ADO.NET.

来源:http://bytes.com/topic/sql-server/answers/808461-cannot-read-varchar-max

因此，就我而言，以下方法奏效了:

So, in my case, the following did the trick:

job.text <- sqlQuery(ccweb5.prod,"
  SELECT DISTINCT TOP 100
    ja.JobTitle,
    [JobText] = CAST(ja.JobText AS varchar(8000)), -- note the data-type re-cast
    [JobTextLength] = LEN(ja.JobText)
  FROM JobStore.dbo.JobAd as ja (NOLOCK)
")

这样 nchar(as.character(job.text[1,2])) 现在返回 2742(应该的).

Such that nchar(as.character(job.text[1,2])) now returns 2742 (as it should).

我在 StackOverflow 上没有看到任何类似的问题，所以我就不提了.希望这对某人有所帮助！

I didn't see any similar questions on StackOverflow so I'll leave this up. Hope this helps somebody!

这篇关于RODBC sqlQuery() 在应该返回 varchar(MAX) 时返回 varchar(255)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！