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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
|
\name{RODBCtables}
\alias{sqlPrimaryKeys}
\alias{sqlColumns}
\alias{sqlDrop}
\alias{sqlClear}
\title{Operations on Tables in ODBC databases}
\description{
Operations on tables in ODBC databases.
}
\usage{
sqlClear(channel, sqtable, errors = TRUE)
sqlDrop(channel, sqtable, errors = TRUE)
sqlColumns(channel, sqtable, errors = FALSE, as.is = TRUE,
special = FALSE)
sqlPrimaryKeys(channel, sqtable, errors = FALSE, as.is = TRUE)
}
\arguments{
\item{channel}{connection object as returned by \code{\link{odbcConnect}}.}
\item{sqtable}{character: a database table name accessible from the
connected dsn.}
\item{errors}{if TRUE halt and display error, else return -1}
\item{as.is}{as in \code{\link{sqlGetResults}}.}
\item{special}{return only the column(s) needed to specify a row
uniquely. Depending on the database, there might be none.}
}
\details{
\code{sqlClear} deletes the content of the table \code{sqtable}. No
confirmation is requested.
\code{sqlDrop} removes the table \code{sqtable}. No confirmation
is requested.
\code{sqlColumns} and \code{sqlPrimaryKeys} return information as data
frames. The column names are not constant across ODBC versions so the
data should be accessed by column number. The argument \code{special}
to \code{sqlColumns} returns the columns needed to specify a row
uniquely. This is intended to form the basis of a WHERE clause for
updates (see \code{\link{sqlUpdate}}).
}
\value{
A data frame on success, or character/numeric on error depending on the
\code{errors} parameter. 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 DBMS, an error code results.)
}
\seealso{
\code{\link{odbcConnect}}, \code{\link{sqlQuery}}, \code{\link{sqlFetch}},
\code{\link{sqlSave}}, \code{\link{sqlTables}}, \code{\link{odbcGetInfo}}
}
\author{
Michael Lapsley and Brian Ripley
}
\examples{
\dontrun{## example results from MySQL
channel <- odbcConnect("test")
sqlDrop(channel, "USArrests", errors = FALSE) # precautionary
sqlSave(channel, USArrests)
sqlColumns(channel, "USArrests")
sqlColumns(channel, "USArrests", special = TRUE)
sqlPrimaryKeys(channel, "USArrests")
sqlColumns(channel, "USArrests")
## Table_cat Table_schema Table_name Column_name Data_type Type_name
## 1 USArrests rownames 12 varchar
## 2 USArrests murder 8 double
## 3 USArrests assault 4 integer
## 4 USArrests urbanpop 4 integer
## 5 USArrests rape 8 double
## Column_size Buffer_length Decimal_digits Num_prec_radix Nullable Remarks
## 1 255 255 <NA> <NA> 0
## 2 22 8 31 10 1
## 3 11 4 0 10 1
## 4 11 4 0 10 1
## 5 22 8 31 10 1
sqlColumns(channel, "USArrests", special = TRUE)
## Scope Column_name Data_type Type_name Precision Length Scale
## 1 2 rownames 12 varchar 0 0 0
## Pseudo_column
## 1 1
sqlPrimaryKeys(channel, "USArrests")
## Table_qualifer Table_owner Table_name Column_name Key_seq Pk_name
## 1 <NA> <NA> USArrests rownames 1 PRIMARY
sqlClear(channel, "USArrests")
sqlDrop(channel, "USArrests")
close(channel)
}}
\keyword{IO}
\keyword{database}
|