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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
|
\name{sqlSave}
\alias{sqlSave}
\alias{sqlUpdate}
\title{Write a Data Frame to a Table in an ODBC Database}
\description{
Write or update a table in an ODBC database.
}
\usage{
sqlSave(channel, dat, tablename = NULL, append = FALSE,
rownames = TRUE, colnames = FALSE,
verbose = FALSE, oldstyle = FALSE,
safer = TRUE, addPK = FALSE, typeInfo, varTypes,
fast = TRUE, test = FALSE, nastring = NULL)
sqlUpdate(channel, dat, tablename = NULL, index = NULL,
verbose = FALSE, test = FALSE,
nastring = NULL, fast = TRUE)
}
\arguments{
\item{channel}{connection handle returned by \code{\link{odbcConnect}}.}
\item{dat}{a data frame.}
\item{tablename}{character: a database table name accessible from
the connected dsn. If missing, the name of \code{dat}.}
\item{index}{character. Name(s) of index column(s) to be used.}
\item{append}{logical. Should data be appended to an existing table?}
\item{rownames}{either logical or character.
If logical, save the row names as the first column \code{rownames}
in the table? If character, the column name under which to save
the rownames.}
\item{colnames}{logical: save column names as the first row of table?}
\item{verbose}{display statements as they are sent to the server?}
\item{oldstyle}{logical. If false, attempt to use \code{sqlTypeInfo}
to choose the types of columns when a table has to be created. If
true, create all columns as \code{varchar(255)}.}
\item{safer}{logical. If true, create a non-existing table but only
allow appends to an existing table. If false, allow \code{sqlSave}
to attempt to delete all the rows of an existing table, or to drop it.}
\item{addPK}{logical. Should rownames (if included) be specified as a
primary key?}
\item{typeInfo}{optional list of DBMS datatypes. Should have elements
named \code{"character"}, \code{"double"} and \code{"integer"}.}
\item{varTypes}{an optional named character vector giving the DBMSs
datatypes to be used for some (or all) of the columns if a table is
to be created.}
\item{fast}{logical. If false, write data a row at a time. If true,
use a parametrized INSERT INTO / UPDATE query to write all the data
in one operation, or \code{SQLBulkOperations} where supported.}
\item{test}{show what would be done, only.}
\item{nastring}{character string to be used for writing \code{NA}s
to the database. The default, \code{NULL}, attempts to write
a missing value as a database null. Only supported for \code{fast=FALSE}.}
}
\details{
\code{sqlSave} saves the data frame \code{dat} in the table
\code{tablename}. If the table exists and has the appropriate
structure it is used, or else it is created anew. If a new table is
created, column names are remapped by removing any characters which
are not alphanumeric or \code{_}, and the types are selected by
consulting arguments \code{varTypes} and \code{typeInfo}, then looking
the driver up in the database used by \code{\link{getSqlTypeInfo}} or
failing that by interrogating \code{\link{sqlTypeInfo}}.
If \code{rownames = TRUE} the first column of the table will be the
row labels with colname \code{rowname}: \code{rownames} can also be a
string giving the desired column name (see example). \code{colnames}
copies the column names into row 1. This is intended for cases where
case conversion alters the original column names and it is desired
that they are retained. Note that there are drawbacks to this
approach: it presupposes that the rows will be returned in correct
order; not always valid. It will also cause numeric rows to be
returned as factors.
Argument \code{addPK = TRUE} causes the rownames to be marked as a
primary key. This is usually a good idea, and may allow updates to be
done. However, some DBMSs (e.g. Access) do not support primary keys,
and some versions of the PostgreSQL ODBC driver have generated
internal memory corruption if this option is used.
\code{sqlUpdate} updates the table where the rows already exist. Data
frame \code{dat} should columns with names that map to (some of) the
columns in the table. It also needs to contain the column(s)
specified by \code{index} which together identify the rows to be
updated. If \code{index = NULL}, the function tries to identify such
rows. First it looks for a primary key in the data frame, then for
the column(s) that the database regards as the optimal for defining a
row uniquely (these are returned by
\code{\link{sqlColumns}(\dots, special=TRUE)}). If
there is no such column the rownames are used provided they are stored
as column \code{"rownames"} in the table.
The value of \code{nastring} is used for all the columns and no
attempt is made to check if the column is nullable. For all but the
simplest applications it will be better to prepare a data frame with
non-null missing values already substituted.
If \code{fast = FALSE} all data is sent as character strings.
If \code{fast = TRUE}, integer and double vectors are sent as types
\code{SQL_C_SLONG} and \code{SQL_C_DOUBLE} respectively. Some drivers
seem to require \code{fast = FALSE} to send other types,
e.g. \code{datetime}. SQLite's approach is to use the data to determine
how it is stored, and this does not work well with \code{fast = TRUE}.
}
\section{Warning}{
\code{sqlSave(safer = FALSE)} uses the 'great white shark'
method of testing tables (bite it and see). The logic will
unceremoniously DROP the table and create it anew with its own choice of
column types in its attempt to find a writable solution.
\code{test = TRUE} will not necessarily predict this behaviour.
Attempting to write indexed columns or writing to pseudo-columns are
less obvious causes of failed writes followed by a DROP. If your table
structure is precious to you back it up.
}
\value{
1 invisibly for success (and failures cause errors).
}
\seealso{
\code{\link{sqlFetch}}, \code{\link{sqlQuery}},
\code{\link{odbcConnect}}, \code{\link{odbcGetInfo}}
}
\author{
Michael Lapsley and Brian Ripley
}
\examples{
\dontrun{
channel <- odbcConnect("test")
sqlSave(channel, USArrests, rownames = "state", addPK=TRUE)
sqlFetch(channel, "USArrests", rownames = "state") # get the lot
foo <- cbind(state=row.names(USArrests), USArrests)[1:3, c(1,3)]
foo[1,2] <- 222
sqlUpdate(channel, foo, "USArrests")
sqlFetch(channel, "USArrests", rownames = "state", max = 5)
sqlDrop(channel, "USArrests")
close(channel)
}}
\keyword{IO}
\keyword{database}
|