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
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
|
R version 2.9.1 Patched (2009-07-08 r48919)
Copyright (C) 2009 The R Foundation for Statistical Computing
ISBN 3-900051-07-0
R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.
R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.
Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.
> ##-*- R -*-
> library(RODBC)
> library(MASS)
> USArrests[1,2] <- NA
> hills <- hills[1:15,]
> row.names(hills)[12] <- "Dollar ('$')"
>
> channel <- odbcConnect("sqlite3")
> if(!inherits(channel, "RODBC")) q("no")
> sqlTables(channel)
[1] TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
<0 rows> (or 0-length row.names)
> sqlDrop(channel, "USArrests", errors = FALSE)
> sqlSave(channel, USArrests, addPK = TRUE)
> sqlTables(channel)
TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
1 <NA> <NA> USArrests TABLE <NA>
> sqlColumns(channel, "USArrests")
TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME
1 USArrests rownames 12 varchar(255)
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 10 0 0 <NA>
2 53 54 10 0 1 <NA>
3 9 10 10 0 1 <NA>
4 9 10 10 0 1 <NA>
5 53 54 10 0 1 <NA>
COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION
1 NULL 12 NA 16384 1
2 NULL 8 NA 16384 2
3 NULL 4 NA 16384 3
4 NULL 4 NA 16384 4
5 NULL 8 NA 16384 5
IS_NULLABLE
1 NO
2 YES
3 YES
4 YES
5 YES
> sqlColumns(channel, "USArrests", special=TRUE)
SCOPE COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH
1 2 rownames 12 varchar(255) 0 255
DECIMAL_DIGITS PSEUDO_COLUMN NULLABLE
1 255 1 1
> sqlPrimaryKeys(channel, "USArrests")
TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME KEY_SEQ
1 USArrests rownames 1
PK_NAME
1 sqlite_autoindex_USArrests_1
> sqlFetch(channel, "USArrests")
Murder Assault UrbanPop Rape
Alabama 13.2 NA 58 21.2
Alaska 10.0 263 48 44.5
Arizona 8.1 294 80 31.0
Arkansas 8.8 190 50 19.5
California 9.0 276 91 40.6
Colorado 7.9 204 78 38.7
Connecticut 3.3 110 77 11.1
Delaware 5.9 238 72 15.8
Florida 15.4 335 80 31.9
Georgia 17.4 211 60 25.8
Hawaii 5.3 46 83 20.2
Idaho 2.6 120 54 14.2
Illinois 10.4 249 83 24.0
Indiana 7.2 113 65 21.0
Iowa 2.2 56 57 11.3
Kansas 6.0 115 66 18.0
Kentucky 9.7 109 52 16.3
Louisiana 15.4 249 66 22.2
Maine 2.1 83 51 7.8
Maryland 11.3 300 67 27.8
Massachusetts 4.4 149 85 16.3
Michigan 12.1 255 74 35.1
Minnesota 2.7 72 66 14.9
Mississippi 16.1 259 44 17.1
Missouri 9.0 178 70 28.2
Montana 6.0 109 53 16.4
Nebraska 4.3 102 62 16.5
Nevada 12.2 252 81 46.0
New Hampshire 2.1 57 56 9.5
New Jersey 7.4 159 89 18.8
New Mexico 11.4 285 70 32.1
New York 11.1 254 86 26.1
North Carolina 13.0 337 45 16.1
North Dakota 0.8 45 44 7.3
Ohio 7.3 120 75 21.4
Oklahoma 6.6 151 68 20.0
Oregon 4.9 159 67 29.3
Pennsylvania 6.3 106 72 14.9
Rhode Island 3.4 174 87 8.3
South Carolina 14.4 279 48 22.5
South Dakota 3.8 86 45 12.8
Tennessee 13.2 188 59 26.9
Texas 12.7 201 80 25.5
Utah 3.2 120 80 22.9
Vermont 2.2 48 32 11.2
Virginia 8.5 156 63 20.7
Washington 4.0 145 73 26.2
West Virginia 5.7 81 39 9.3
Wisconsin 2.6 53 66 10.8
Wyoming 6.8 161 60 15.6
> sqlQuery(channel, "select rownames, Murder from USArrests where Rape > 30 order by Murder")
rownames Murder
1 Colorado 7.9
2 Arizona 8.1
3 California 9.0
4 Alaska 10.0
5 New Mexico 11.4
6 Michigan 12.1
7 Nevada 12.2
8 Florida 15.4
> foo <- USArrests[1:3, 2, drop = FALSE]
> foo[1,1] <- 236
> sqlUpdate(channel, foo, "USArrests")
> sqlFetch(channel, "USArrests", max = 5)
Murder Assault UrbanPop Rape
Alabama 13.2 236 58 21.2
Alaska 10.0 263 48 44.5
Arizona 8.1 294 80 31.0
Arkansas 8.8 190 50 19.5
California 9.0 276 91 40.6
> sqlFetchMore(channel, max = 8)
Murder Assault UrbanPop Rape
Colorado 7.9 204 78 38.7
Connecticut 3.3 110 77 11.1
Delaware 5.9 238 72 15.8
Florida 15.4 335 80 31.9
Georgia 17.4 211 60 25.8
Hawaii 5.3 46 83 20.2
Idaho 2.6 120 54 14.2
Illinois 10.4 249 83 24.0
> sqlDrop(channel, "USArrests")
>
> Btest <- Atest <-
+ data.frame(x = c(paste(1:100, collapse="+"), letters[2:4]), rn=1:4)
> Btest[,1] <- Atest[c(4,1:3),1]
> sqlDrop(channel, "Atest", errors = FALSE)
> colspec <- list(character="varchar(1000)", double="double",
+ integer="integer", logical="varchar(5)")
> sqlSave(channel, Atest, typeInfo = colspec)
> sqlColumns(channel, "Atest")[, 1:6]
TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME
1 Atest rownames -1 varchar(1000)
2 Atest x -1 varchar(1000)
3 Atest rn 4 integer
> sqlFetch(channel, "Atest")
x
1 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
2 b
3 c
4 d
rn
1 1
2 2
3 3
4 4
> sqlUpdate(channel, Btest, "Atest", index = "rn")
> sqlFetch(channel, "Atest")
x
1 d
2 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
3 b
4 c
rn
1 1
2 2
3 3
4 4
> sqlDrop(channel, "Atest")
> varspec <- "varchar(1000)"; names(varspec) <- "x"
> sqlSave(channel, Atest, varTypes = varspec)
> sqlColumns(channel, "Atest")[, 1:6]
TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME
1 Atest rownames 12 varchar(255)
2 Atest x -1 varchar(1000)
3 Atest rn 4 integer
> sqlFetch(channel, "Atest")
x
1 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
2 b
3 c
4 d
rn
1 1
2 2
3 3
4 4
> sqlDrop(channel, "Atest")
>
> dates <- as.character(seq(as.Date("2004-01-01"), by="week", length=10))
> times <- paste(1:10, "05", "00", sep=":")
> dt <- as.POSIXct(paste(dates, times))
> Dtest <- data.frame(dates, times, dt, logi=c(TRUE, NA, FALSE, FALSE, FALSE))
> varspec <- c("date", "time", "timestamp", "varchar(5)")
> names(varspec) <- names(Dtest)
> sqlDrop(channel, "Dtest", errors = FALSE)
> sqlSave(channel, Dtest, varTypes = varspec)
> sqlColumns(channel, "Dtest")
TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME
1 Dtest rownames 12 varchar(255)
2 Dtest dates 91 date
3 Dtest times 92 time
4 Dtest dt 93 timestamp
5 Dtest logi 12 varchar(5)
COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS NUM_PREC_RADIX NULLABLE REMARKS
1 255 255 10 0 1 <NA>
2 0 10 10 0 1 <NA>
3 0 8 10 0 1 <NA>
4 0 32 10 0 1 <NA>
5 5 5 10 0 1 <NA>
COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION
1 NULL 12 NA 16384 1
2 NULL 91 NA 16384 2
3 NULL 92 NA 16384 3
4 NULL 93 NA 16384 4
5 NULL 12 NA 16384 5
IS_NULLABLE
1 YES
2 YES
3 YES
4 YES
5 YES
> sqlFetch(channel, "Dtest")
dates times dt logi
1 2004-01-01 1:05:00 2004-01-01 01:05:00 TRUE
2 2004-01-08 2:05:00 2004-01-08 02:05:00 NA
3 2004-01-15 3:05:00 2004-01-15 03:05:00 FALSE
4 2004-01-22 4:05:00 2004-01-22 04:05:00 FALSE
5 2004-01-29 5:05:00 2004-01-29 05:05:00 FALSE
6 2004-02-05 6:05:00 2004-02-05 06:05:00 TRUE
7 2004-02-12 7:05:00 2004-02-12 07:05:00 NA
8 2004-02-19 8:05:00 2004-02-19 08:05:00 FALSE
9 2004-02-26 9:05:00 2004-02-26 09:05:00 FALSE
10 2004-03-04 10:05:00 2004-03-04 10:05:00 FALSE
> sqlDrop(channel, "Dtest", errors = FALSE)
> sqlSave(channel, Dtest, varTypes = varspec, fast = FALSE)
> sqlColumns(channel, "Dtest")
TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME
1 Dtest rownames 12 varchar(255)
2 Dtest dates 91 date
3 Dtest times 92 time
4 Dtest dt 93 timestamp
5 Dtest logi 12 varchar(5)
COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS NUM_PREC_RADIX NULLABLE REMARKS
1 255 255 10 0 1 <NA>
2 0 10 10 0 1 <NA>
3 0 8 10 0 1 <NA>
4 0 32 10 0 1 <NA>
5 5 5 10 0 1 <NA>
COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION
1 NULL 12 NA 16384 1
2 NULL 91 NA 16384 2
3 NULL 92 NA 16384 3
4 NULL 93 NA 16384 4
5 NULL 12 NA 16384 5
IS_NULLABLE
1 YES
2 YES
3 YES
4 YES
5 YES
> sqlFetch(channel, "Dtest")
dates times dt logi
1 2004-01-01 1:05:00 2004-01-01 01:05:00 TRUE
2 2004-01-08 2:05:00 2004-01-08 02:05:00 NA
3 2004-01-15 3:05:00 2004-01-15 03:05:00 FALSE
4 2004-01-22 4:05:00 2004-01-22 04:05:00 FALSE
5 2004-01-29 5:05:00 2004-01-29 05:05:00 FALSE
6 2004-02-05 6:05:00 2004-02-05 06:05:00 TRUE
7 2004-02-12 7:05:00 2004-02-12 07:05:00 NA
8 2004-02-19 8:05:00 2004-02-19 08:05:00 FALSE
9 2004-02-26 9:05:00 2004-02-26 09:05:00 FALSE
10 2004-03-04 10:05:00 2004-03-04 10:05:00 FALSE
> sqlDrop(channel, "Dtest")
>
> sqlDrop(channel, "hills test", errors = FALSE)
> sqlSave(channel, hills, "hills test", verbose=TRUE)
Query: CREATE TABLE "hills test" ("rownames" varchar(255), "dist" double, "climb" integer, "time" double)
Query: INSERT INTO "hills test" ( "rownames", "dist", "climb", "time" ) VALUES ( ?,?,?,? )
Binding: 'rownames' DataType 12, ColSize 255
Binding: 'dist' DataType 8, ColSize 53
Binding: 'climb' DataType 4, ColSize 9
Binding: 'time' DataType 8, ColSize 53
Parameters:
no: 1: rownames Greenmantle/***/no: 2: dist 2.5/***/no: 3: climb 650/***/no: 4: time 16.083/***/
no: 1: rownames Carnethy/***/no: 2: dist 6/***/no: 3: climb 2500/***/no: 4: time 48.35/***/
no: 1: rownames Craig Dunain/***/no: 2: dist 6/***/no: 3: climb 900/***/no: 4: time 33.65/***/
no: 1: rownames Ben Rha/***/no: 2: dist 7.5/***/no: 3: climb 800/***/no: 4: time 45.6/***/
no: 1: rownames Ben Lomond/***/no: 2: dist 8/***/no: 3: climb 3070/***/no: 4: time 62.267/***/
no: 1: rownames Goatfell/***/no: 2: dist 8/***/no: 3: climb 2866/***/no: 4: time 73.217/***/
no: 1: rownames Bens of Jura/***/no: 2: dist 16/***/no: 3: climb 7500/***/no: 4: time 204.617/***/
no: 1: rownames Cairnpapple/***/no: 2: dist 6/***/no: 3: climb 800/***/no: 4: time 36.367/***/
no: 1: rownames Scolty/***/no: 2: dist 5/***/no: 3: climb 800/***/no: 4: time 29.75/***/
no: 1: rownames Traprain/***/no: 2: dist 6/***/no: 3: climb 650/***/no: 4: time 39.75/***/
no: 1: rownames Lairig Ghru/***/no: 2: dist 28/***/no: 3: climb 2100/***/no: 4: time 192.667/***/
no: 1: rownames Dollar ('$')/***/no: 2: dist 5/***/no: 3: climb 2000/***/no: 4: time 43.05/***/
no: 1: rownames Lomonds/***/no: 2: dist 9.5/***/no: 3: climb 2200/***/no: 4: time 65/***/
no: 1: rownames Cairn Table/***/no: 2: dist 6/***/no: 3: climb 500/***/no: 4: time 44.133/***/
no: 1: rownames Eildon Two/***/no: 2: dist 4.5/***/no: 3: climb 1500/***/no: 4: time 26.933/***/
> sqlUpdate(channel, hills[11:15,], "hills test", verbose=TRUE, fast=TRUE)
Query: UPDATE "hills test" SET "dist"=?, "climb"=?, "time"=? WHERE "rownames"=?
Binding: 'dist' DataType 8, ColSize 53
Binding: 'climb' DataType 4, ColSize 9
Binding: 'time' DataType 8, ColSize 53
Binding: 'rownames' DataType 12, ColSize 255
Parameters:
no: 1: dist 28/***/no: 2: climb 2100/***/no: 3: time 192.667/***/no: 4: rownames Lairig Ghru/***/
no: 1: dist 5/***/no: 2: climb 2000/***/no: 3: time 43.05/***/no: 4: rownames Dollar ('$')/***/
no: 1: dist 9.5/***/no: 2: climb 2200/***/no: 3: time 65/***/no: 4: rownames Lomonds/***/
no: 1: dist 6/***/no: 2: climb 500/***/no: 3: time 44.133/***/no: 4: rownames Cairn Table/***/
no: 1: dist 4.5/***/no: 2: climb 1500/***/no: 3: time 26.933/***/no: 4: rownames Eildon Two/***/
> sqlFetch(channel, "hills test")
dist climb time
Greenmantle 2.5 650 16.083
Carnethy 6.0 2500 48.350
Craig Dunain 6.0 900 33.650
Ben Rha 7.5 800 45.600
Ben Lomond 8.0 3070 62.267
Goatfell 8.0 2866 73.217
Bens of Jura 16.0 7500 204.617
Cairnpapple 6.0 800 36.367
Scolty 5.0 800 29.750
Traprain 6.0 650 39.750
Lairig Ghru 28.0 2100 192.667
Dollar ('$') 5.0 2000 43.050
Lomonds 9.5 2200 65.000
Cairn Table 6.0 500 44.133
Eildon Two 4.5 1500 26.933
> sqlDrop(channel, "hills test")
> sqlSave(channel, hills, "hills test", verbose=TRUE, fast=FALSE)
Query: CREATE TABLE "hills test" ("rownames" varchar(255), "dist" double, "climb" integer, "time" double)
Query: INSERT INTO "hills test" ( "rownames", "dist", "climb", "time" ) VALUES ( 'Greenmantle', 2.5, 650, 16.083 )
Query: INSERT INTO "hills test" ( "rownames", "dist", "climb", "time" ) VALUES ( 'Carnethy', 6.0, 2500, 48.350 )
Query: INSERT INTO "hills test" ( "rownames", "dist", "climb", "time" ) VALUES ( 'Craig Dunain', 6.0, 900, 33.650 )
Query: INSERT INTO "hills test" ( "rownames", "dist", "climb", "time" ) VALUES ( 'Ben Rha', 7.5, 800, 45.600 )
Query: INSERT INTO "hills test" ( "rownames", "dist", "climb", "time" ) VALUES ( 'Ben Lomond', 8.0, 3070, 62.267 )
Query: INSERT INTO "hills test" ( "rownames", "dist", "climb", "time" ) VALUES ( 'Goatfell', 8.0, 2866, 73.217 )
Query: INSERT INTO "hills test" ( "rownames", "dist", "climb", "time" ) VALUES ( 'Bens of Jura', 16.0, 7500, 204.617 )
Query: INSERT INTO "hills test" ( "rownames", "dist", "climb", "time" ) VALUES ( 'Cairnpapple', 6.0, 800, 36.367 )
Query: INSERT INTO "hills test" ( "rownames", "dist", "climb", "time" ) VALUES ( 'Scolty', 5.0, 800, 29.750 )
Query: INSERT INTO "hills test" ( "rownames", "dist", "climb", "time" ) VALUES ( 'Traprain', 6.0, 650, 39.750 )
Query: INSERT INTO "hills test" ( "rownames", "dist", "climb", "time" ) VALUES ( 'Lairig Ghru', 28.0, 2100, 192.667 )
Query: INSERT INTO "hills test" ( "rownames", "dist", "climb", "time" ) VALUES ( 'Dollar (''$'')', 5.0, 2000, 43.050 )
Query: INSERT INTO "hills test" ( "rownames", "dist", "climb", "time" ) VALUES ( 'Lomonds', 9.5, 2200, 65.000 )
Query: INSERT INTO "hills test" ( "rownames", "dist", "climb", "time" ) VALUES ( 'Cairn Table', 6.0, 500, 44.133 )
Query: INSERT INTO "hills test" ( "rownames", "dist", "climb", "time" ) VALUES ( 'Eildon Two', 4.5, 1500, 26.933 )
> sqlUpdate(channel, hills[11:15,], "hills test", verbose=TRUE, fast=FALSE)
Query: UPDATE "hills test" SET "dist"=28.0, "climb"=2100, "time"=192.667 WHERE "rownames"='Lairig Ghru'
Query: UPDATE "hills test" SET "dist"= 5.0, "climb"=2000, "time"= 43.050 WHERE "rownames"='Dollar (''$'')'
Query: UPDATE "hills test" SET "dist"= 9.5, "climb"=2200, "time"= 65.000 WHERE "rownames"='Lomonds'
Query: UPDATE "hills test" SET "dist"= 6.0, "climb"= 500, "time"= 44.133 WHERE "rownames"='Cairn Table'
Query: UPDATE "hills test" SET "dist"= 4.5, "climb"=1500, "time"= 26.933 WHERE "rownames"='Eildon Two'
> sqlDrop(channel, "hills test")
>
> sqlTables(channel, catalog = "", schema = "", tableName = "", tableType = "%")
TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
1 TABLE <NA>
2 VIEW <NA>
>
> close(channel)
>
|
