Enquire about the column structure of tables on an ODBC database connection.
1 2 3 4 5 6
connection object as returned by
character string: a database table (or view or similar)
name accessible from the connected DSN. If wildcards are allowed
logical: if true halt and display error, else return
logical. If true, return only the column(s) needed to specify a row uniquely. Depending on the database, there might be none.
logical: wildcards may be interpreted in
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.
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.
In principle specifying
catalog should select an alternative
database in MySQL or an attached database in SQLite, but neither works
with current drivers.
sqtable contains . and neither
schema is supplied, an attempt is made to interpret
qualifier.table as table
qualifier (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
Whether wildcards are accepted for
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
= 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.
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.)
The column names are not constant across ODBC versions so the data should be accessed by column number.
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
Those beyond the first 6 shown in the examples give the
‘ordinal position’ (column 17) and further characteristics of the
column type: see
For the numeric values returned by
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
1 (no) and
Michael Lapsley and Brian Ripley
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
## Not run: ## example results from 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) ## End(Not run)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.