sqlColumns(RODBC)
sqlColumns()所属R语言包：RODBC
Query Column Structure in ODBC Tables
查询列在ODBC表的结构
译者：生物统计家园网 机器人LoveR
描述----------Description----------
Enquire about the column structure of tables on an ODBC database connection.
查询有关该列的表结构上的ODBC数据库连接。
用法----------Usage----------
sqlColumns(channel, sqtable, errors = FALSE, as.is = TRUE,
special = FALSE, catalog = NULL, schema = NULL,
literal = FALSE)
sqlPrimaryKeys(channel, sqtable, errors = FALSE, as.is = TRUE,
catalog = NULL, schema = NULL)
参数----------Arguments----------
参数：channel
connection object as returned by odbcConnect.
连接对象返回的odbcConnect。
参数：sqtable
character string: a database table (or view or similar) name accessible from the connected DSN. If wildcards are allowed (only for sqlColumns(special=FALSE)), results for all matching tables.
字符串：从连接的DSN访问一个数据库表（或视图或类似）的名称。如果允许使用通配符（仅适用于sqlColumns(special=FALSE)），结果为所有匹配的表。
参数：errors
logical: if true halt and display error, else return -1.
逻辑：如果真正的停止并显示错误，否则返回-1。
参数：as.is
see sqlGetResults.
看到sqlGetResults。
参数：special
logical. If true, return only the column(s) needed to specify a row uniquely. Depending on the database, there might be none.
逻辑。如果为true，只返回列（S）唯一指定的行。根据在数据库中，有可能是没有的。
参数：catalog, schema
NULL or character: additional information on where to locate the table: see sqlTables for driver-specific details. Wildcards may be supported in schema for sqlColumns(special=FALSE).
NULL或字符：在哪里可以找到表的其他信息：见sqlTables驱动程序的具体细节。通配符可支持schemasqlColumns(special=FALSE)。
参数：literal
logical: wildcards may be interpreted in schema and sqtable: if so this may suppress such interpretation.
符合逻辑的：，通配符可以解释schema和sqtable：如果是这样，这可能会抑制这样的解释。
Details
详细信息----------Details----------
The argument special = TRUE to sqlColumns returns the column(s) needed to specify a row uniquely. This is intended to form the basis of an SQL WHERE clause for update queries (see sqlUpdate), and what (if anything) it does is DBMS-specific. On many DBMSs it will return the primary keys if present: on others it will return a pseudo-column such as ROWID (Oracle) or _ROWID_ (SQLite), either always (Oracle) or if there is no primary key.
参数special = TRUE到sqlColumns唯一指定的行返回的列（S）。这样做的目的的基础上形成的一个SQL WHERE子句更新查询（见sqlUpdate），什么（如果有的话）它是特定的DBMS。在许多数据库管理系统，它会返回主键（如果存在）：在别人身上它会返回一个伪列，如ROWID（Oracle）或_ROWID_（SQLite的），总是（Oracle）或有没有主键。
Primary keys are implemented in some DBMSs and drivers. A table can have a single column designated as a primary key or, in some cases, multiple columns. Primary keys should not be nullable (that is, cannot contain missing values). They can be specified as part of a CREATE TABLE statement or added by a ALTER TABLE statement.
实现主键在某些DBMS和驱动程序。一个表只能有一个主键或在某些情况下，多列指定为单个列。主键不能为空（即，不能包含缺少值）。他们可以被指定为一个CREATE TABLE语句的一部分或添加一个ALTER TABLE声明。
In principle specifying catalog should select an alternative database in MySQL or an attached database in SQLite, but neither works with current drivers.
原则，指定catalog应该选择一个不同的数据库在MySQL或附加的数据库SQLite中的，但也与当前的驱动程序。
If sqtable contains . and neither catalog nor schema is supplied, an attempt is made to interpret <VAR>qualifier</VAR>.<VAR>table</VAR> as table <VAR>table</VAR> in schema <VAR>qualifier</VAR> (and for MySQL "schema" means "database", but the current drivers fail to interpret catalog=, so this does not yet work). (This can be suppressed by opening the connection with interpretDot = FALSE.) This has been tested successfully on PostgreSQL, SQL Server, Oracle, DB2 and Mimer.
如果sqtable包含.也不catalog，也不schema提供，试图解释<VAR>qualifier</VAR>.<VAR>table</VAR>表<VAR>table</VAR>的架构<VAR>qualifier</VAR>（和MySQL的架构的意思是数据库，但当前的驱动程序无法解释catalog=，所以这不，但工作）。 （这可以抑制由打开连接与interpretDot = FALSE。）已经测试成功，在PostgreSQL中，SQL Server，Oracle，DB2和Mimer。
Whether wildcards are accepted for sqtable and schema in sqlColumns(special = FALSE) depends on the driver and may be changed by the value of literal. For example, the PostgreSQL driver tested allowed wildcards in schema only if literal = FALSE and never in sqtable, whereas two MySQL drivers both failed to match a database when catalog was supplied and always allowed wildcards in sqtable even if literal = TRUE.
是否接受通配符sqtable和schemasqlColumns(special = FALSE)取决于驱动程序，可以改变的价值literal。例如，PostgreSQL驱动程序测试schema只有literal = FALSE，从来没有在sqtable，而两个MySQL驱动程序不匹配数据库catalog提供和通配符始终允许使用通配符在sqtable即使literal = TRUE的。
值----------Value----------
A data frame on success. If no data is returned, either a zero-row data frame or an error. (For example, if there are no primary keys or special column(s) in this table an empty data frame is returned, but if primary keys are not supported by the ODBC driver or DBMS, an error code results.)
一个数据框成功。如果没有数据，则返回一个零行的数据框或错误。 （例如，如果没有在此表中的主键或特殊的列（S）一个空的数据框返回，但如果主键是不支持的ODBC驱动程序或数据库管理系统，一个错误代码的结果。）
The column names are not constant across ODBC versions so the data should be accessed by column number.
列名不恒定的ODBC版本，这样的数据可以被访问的列数。
For sqlPrimaryKeys and sqlColumns(special=FALSE) the first four columns give the catalog, schema, table and column names (where applicable). For sqlPrimaryKeys the next two columns are the column sequence number (starting with 1) and name of the primary key: drivers can define further columns. For sqlColumns(special=FALSE) there are 18 columnns: see http://msdn.microsoft.com/en-us/library/ms711683%28VS.85%29.aspx. Those beyond the first 6 shown in the examples give the "ordinal position" (column 17) and further characteristics of the column type: see sqlTypeInfo.
对于sqlPrimaryKeys和sqlColumns(special=FALSE)的前四列目录，架构，表名和列名（如适用）的。对于sqlPrimaryKeys在接下来的两列的列顺序号（从1开始）和名称的主键：驱动程序可以进一步定义列。对于sqlColumns(special=FALSE)有18 columnns的：http://msdn.microsoft.com/en-us/library/ms711683％28VS.85％29.aspx。实施例中所示的那些以外的第一6给序号位置“（第17栏）和进一步的特性列类型：看到sqlTypeInfo。
For the numeric values returned by sqlColumns(special=TRUE) see http://msdn.microsoft.com/en-us/library/ms714602%28VS.85%29.aspx: the scope should always be 2 (the session) since that is the scope requested in the call. For the PSEUDO_COLUMN column, the possible values are 0 (unknown), 1 (no) and 2 (yes).
对于数字sqlColumns(special=TRUE)http://msdn.microsoft.com/en-us/library/ms714602％28VS.85％29.aspx的范围应始终2（返回值会话），因为这是在调用请求的范围。 PSEUDO_COLUMN列，可能的值是0（未知），1（NO）和2（是）。
（作者）----------Author(s)----------
Michael Lapsley and Brian Ripley
参见----------See Also----------
odbcConnect, sqlQuery, sqlFetch, sqlSave, sqlTables, odbcGetInfo
odbcConnect，sqlQuery，sqlFetch，sqlSave，sqlTables，odbcGetInfo
实例----------Examples----------
## Not run: ## example results from MySQL[＃不运行：＃＃例如从MySQL]
> channel <- odbcConnect("test")
> sqlDrop(channel, "USArrests", errors = FALSE) # precautionary[预防]
> sqlSave(channel, USArrests, addPK = TRUE)
> sqlColumns(channel, "USArrests")
TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME
1 ripley <NA> USArrests rownames 12 varchar
2 ripley <NA> USArrests Murder 8 double
3 ripley <NA> USArrests Assault 4 integer
4 ripley <NA> USArrests UrbanPop 4 integer
5 ripley <NA> USArrests Rape 8 double
... 12 more columns
> sqlColumns(channel, "USArrests", special = TRUE)
SCOPE COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH
1 2 rownames 12 varchar 255 255
DECIMAL_DIGITS PSEUDO_COLUMN
1 NA 1
> sqlPrimaryKeys(channel, "USArrests")
TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME
1 <NA> <NA> USArrests rownames 1 PRIMARY
> sqlDrop(channel, "USArrests")
> close(channel)
转载请注明:出自 生物统计家园网(http://www.biostatistic.net)。
注：
注1：为了方便大家学习，本文档为生物统计家园网机器人LoveR翻译而成，仅供个人R语言学习参考使用，生物统计家园保留版权。
注2：由于是机器人自动翻译，难免有不准确之处，使用时仔细对照中、英文内容进行反复理解，可以帮助R语言的学习。
注3：如遇到不准确之处，请在本贴的后面进行回帖，我们会逐渐进行修订。
|
发表于 2012-9-27 22:49:11
