sqlQuery(RODBC)
sqlQuery()所属R语言包：RODBC
Query an ODBC Database
查询一个ODBC数据库
译者：生物统计家园网 机器人LoveR
描述----------Description----------
Submit an SQL query to an ODBC database, and retrieve the results.
提交一个SQL查询到ODBC数据库，并检索结果。
用法----------Usage----------
sqlQuery(channel, query, errors = TRUE, ..., rows_at_time)
sqlGetResults(channel, as.is = FALSE, errors = FALSE,
max = 0, buffsize = 1000,
nullstring = NA_character_, na.strings = "NA",
believeNRows = TRUE, dec = getOption("dec"),
stringsAsFactors = default.stringsAsFactors())
参数----------Arguments----------
参数：channel
connection handle as returned by odbcConnect.
返回的连接句柄odbcConnect。
参数：query
any valid SQL statement.
任何有效的SQL语句。
参数：errors
logical: if true halt and display error, else return -1.
逻辑：如果真正的停止并显示错误，否则返回-1。
参数：...
additional arguments to be passed to sqlGetResults.
额外的参数传递给sqlGetResults。
参数：rows_at_time
The number of rows to fetch at a time, between 1 and 1024. See "Details".
一次获取的行数，在1到1024之间。请参阅“详细信息”。
参数：as.is
which (if any) columns returned as character should be converted to another type? Allowed values are as for read.table. See "Details".
（如果有的话）返回的字符列转换为另一种类型的？允许值为read.table。请参阅“详细信息”。
参数：max
limit on the number of rows to fetch, with 0 indicating no limit.
要抓取的行数的限制，0表示没有限制。
参数：buffsize
an initial guess at the number of rows, used if max = 0 and believeNRows == FALSE.
初始猜测的行数，，如果max = 0和believeNRows == FALSE。
参数：nullstring
character string to be used when reading SQL_NULL_DATA character items from the database.
字符串时要使用从数据库读取SQL_NULL_DATA字符资料。
参数：na.strings
character vector of strings to be mapped to NA when reading character data.
被映射到NA当读取字符数据的字符串的字符向量。
参数：believeNRows
logical. Is the number of rows returned by the ODBC connection believable? This might have been set to false when the channel was opened, and if so that setting cannot be overridden.
逻辑。通过ODBC连接可信返回的行数吗？这可能已被设置为false，当通道被打开了，如果这样，设置不能被覆盖。
参数：dec
The character for the decimal place to be assumed when converting character columns to numeric.
小数位时假定字符的字符列转换为数字。
参数：stringsAsFactors
logical: should columns returned as character and not excluded by as.is and not converted to anything else be converted to factors?
逻辑：应列返回的字符，而不是排除的as.is“，而不是转化为别的转换的因素？
Details
详细信息----------Details----------
sqlQuery is the workhorse function of RODBC. It sends the SQL statement query to the server, using connection channel returned by odbcConnect, and retrieves (some or all of) the results via sqlGetResults.
sqlQuery是主力的RODBC功能。它发出的SQL语句query到服务器，使用连接channel返回odbcConnect，检索（部分或全部）的结果，通过sqlGetResults的。
The term "query" includes any valid SQL statement including table creation, alteration, updates etc as well as SELECTs. The sqlQuery command is a convenience wrapper that first calls odbcQuery and then sqlGetResults. If finer-grained control is needed, for example over the number of rows fetched, additional arguments can be passed to sqlQuery or the underlying functions called directly.
“查询”包括任何有效的SQL语句，包括表的创建，更改，更新等以及SELECT的。 sqlQuery命令是一个方便的包装，首先调用odbcQuery然后sqlGetResults。如果细粒度的控制是必要的，例如，在读取的行数，额外的参数可以传递给sqlQuery或底层函数直接调用。
sqlGetResults is a mid-level function. It is called after a call to sqlQuery or odbcQuery to retrieve waiting results into a data frame. Its main use is with max set to non-zero when it will retrieve the result set in batches with repeated calls. This is useful for very large result sets which can be subjected to intermediate processing.
sqlGetResults是一个中等水平的功能。这就是所谓的在通话结束后sqlQuery或odbcQuery检索到一个数据框等待结果。它的主要用途是max设置为非零时，分批通过重复调用将检索结果。可以经过中间处理的结果集非常大，这是非常有用的。
Where possible sqlGetResults transfers data in binary form: this happens for columns of (ODBC) SQL types double, real, integer and smallint, and for binary SQL types (which are transferred as lists of raw vectors, given class "ODBC_binary"). All other SQL data types are converted to character strings by the ODBC interface.
凡可能sqlGetResults传输数据以二进制形式：发生这种情况（ODBC）SQL类型的列double，real，integer和smallint，并为二进制SQL类型（转移作为原料向量的列表，给定的类"ODBC_binary"“）。所有其他的SQL数据类型转换为字符串的ODBC接口。
This paragraph applies only to SQL data types which are returned by ODBC as character vectors. If when creating the connection (see odbcConnect) DBMSencoding was set to a non-empty value, the character strings are re-encoded. Then if as.is is true for a column, it is returned as a character vector. Otherwise (where detected) date, datetime and timestamp values are converted to the "Date" or "POSIXct" class. (Some drivers seem to confuse times with dates, so times may get converted too. Also, some DBMSs (e.g. Oracle's) idea of date is a date-time.) Remaining cases are converted by R using type.convert. When character data are to be converted to numeric data, the setting of options("dec") is used to map the character used by the ODBC driver in setting decimal points—this is set to a locale-specific value when RODBC is initialized if it is not already set.
本款仅适用于SQL数据类型返回的ODBC特征向量。如果创建连接时（见odbcConnect)DBMSencoding被设置为一个非空值，字符串重新编码。as.is如果是真正的一列，它返回作为一个字符向量，否则（如检测）date，datetime和timestamp值被转换为"Date"或"POSIXct"类。（有些司机似乎混淆时间与日期，所以可能会转换。此外，某些DBMS（如Oracle）的想法date是一个日期时间）。剩余的情况下，用type.convert。字符数据转换要转换为数字数据，设置options("dec")使用的字符映射所使用的ODBC驱动程序中设置小数点，这是一个区域设置特定的值设置为RODBC初始化它是不是已经设置。
Using buffsize will yield a marginal increase in speed if set to no less than the maximum number of rows when believeNRows = FALSE. (If set too small it can result in unnecessarily high memory use as the buffers will need to be expanded.)
使用buffsize将产生轻微上升的速度，如果设置为不低于最大的行数时believeNRows = FALSE。 （如果设置过小，可能会导致不必要的高内存使用的缓冲区将需要扩展）。
Modern drivers should work (and work faster, especially if communicating with a remote machine) with rows_at_time = 100, the usual default, or more. (However, some drivers may mis-fetch multiple rows, in which case set rows_at_time = 1 when creating the connection.) However, if max is specified then this may fetch too many rows and hence it could be reduced (but then this setting applies to all subsequent fetches from that result set). Another circumstance in which you might want to reduce rows_at_time is if there are large character columns in the result set: with the default value up to 6Mb of buffer for each such column could be allocated to store intermediate results.
现代的驱动程序应该正常工作（工作得更快，尤其是当与远程计算机通信）rows_at_time = 100，通常默认情况下，或更多。 （但是，一些司机可能误取多行，在这种情况下，设置rows_at_time = 1创建连接时。）但是，如果max指定，那么这可能取行数太多，因此它可以减少（但此设置适用于所有后续的获取结果集）。另一种情况中，您可能要减少rows_at_time的是，如果有大量的字符列在结果集中的默认值高达6MB的缓冲区为每个这样的列被分配到存储中间结果。
值----------Value----------
On success, a data frame (possibly with 0 rows) or character string. On error, if errors = TRUE a character vector of error message(s), otherwise an invisible integer error code -1 (general, call odbcGetErrMsg for details) or -2 (no data, which may not be an error as some SQL statements do return no data).
成功时，一个数据框（可能是0行）或字符串。发生错误时，如果errors = TRUE的错误消息（S），否则一个无形的整数错误代码-1（一般情况下，调用“odbcGetErrMsg）-2（字符向量没有数据，这可能是一个错误，因为一些SQL语句不返回任何数据）。
（作者）----------Author(s)----------
Michael Lapsley and Brian Ripley
参见----------See Also----------
odbcConnect, sqlFetch, sqlSave, sqlTables, odbcQuery
odbcConnect，sqlFetch，sqlSave，sqlTables，odbcQuery
实例----------Examples----------
## Not run: [＃不运行：]
channel <- odbcConnect("test")
sqlSave(channel, USArrests, rownames = "State", verbose = TRUE)
# options(dec=".") # optional, if DBMS is not locale-aware or set to ASCII[选项（十二月=“。”）＃可选的，如果DBMS不知道或语言环境设置为ASCII]
## note case of State, Murder, Rape are DBMS-dependent,[＃注意状态的情况下，谋杀，强奸是依赖于DBMS，]
## and some drivers need column and table names double-quoted.[＃和一些驱动程序需要双引号引用的列名和表名。]
sqlQuery(channel, paste("select State, Murder from USArrests",
"where Rape > 30 order by Murder"))
close(channel)
转载请注明:出自 生物统计家园网(http://www.biostatistic.net)。
注：
注1：为了方便大家学习，本文档为生物统计家园网机器人LoveR翻译而成，仅供个人R语言学习参考使用，生物统计家园保留版权。
注2：由于是机器人自动翻译，难免有不准确之处，使用时仔细对照中、英文内容进行反复理解，可以帮助R语言的学习。
注3：如遇到不准确之处，请在本贴的后面进行回帖，我们会逐渐进行修订。
|
发表于 2012-9-27 22:49:42
