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.
errors
logical: if true halt and display error, else return -1.
as.is
see sqlGetResults.
special
logical. If true, return only the column(s) needed to
specify a row uniquely. Depending on the database, there might be none.
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).
literal
logical: wildcards may be interpreted in
schema and sqtable: if so this may
suppress such interpretation.
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.
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.
If sqtable contains . and neither catalog nor
schema is supplied, an attempt is made to interpret
qualifier.table as table table in
schema 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
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.
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.)
The column names are not constant across ODBC versions so the
data should be accessed by column number.
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(VS.85).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.
For the numeric values returned by sqlColumns(special=TRUE) see
http://msdn.microsoft.com/en-us/library/ms714602(VS.85).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).