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
|
##-*- R -*-
library(RODBC)
library(MASS)
USArrests[1,2] <- NA
hills <- hills[1:15,]
row.names(hills)[12] <- "Dollar ('$')"
set.seed(1)
options(digits.secs = 3)
channel <- odbcConnect("testpg")
if(!inherits(channel, "RODBC")) q("no")
odbcGetInfo(channel)
sqlTables(channel)
sqlDrop(channel, "USArrests", errors = FALSE)
sqlSave(channel, USArrests, rownames = "state", addPK = TRUE)
sqlTables(channel)
sqlColumns(channel, "USArrests")[, 1:6]
sqlColumns(channel, "USArrests", special = TRUE)
sqlPrimaryKeys(channel, "USArrests")
sqlFetch(channel, "USArrests", rownames = "state")
sqlQuery(channel, "select state, murder from USArrests where rape > 30 order by murder")
foo <- cbind(state=row.names(USArrests), USArrests)[1:3, c(1,3)]
foo[1,2] <- 236
sqlUpdate(channel, foo, "USArrests")
sqlFetch(channel, "USArrests", rownames = "state", max = 5)
sqlFetchMore(channel, rownames = "state", max = 8)
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="text", double="double",
integer="integer", logical="varchar(5)")
sqlSave(channel, Atest, typeInfo = colspec)
sqlColumns(channel, "Atest")[, 1:6]
sqlFetch(channel, "Atest")
sqlUpdate(channel, Btest, "Atest", index = "rn")
sqlFetch(channel, "Atest")
sqlDrop(channel, "Atest")
varspec <- "text"; names(varspec) <- "x"
sqlSave(channel, Atest, varTypes = varspec)
sqlColumns(channel, "Atest")[, 1:6]
sqlFetch(channel, "Atest")
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")[, 1:6]
sqlFetch(channel, "Dtest")
sqlDrop(channel, "Dtest")
## needs fast = FALSE for fractional seconds.
dt <- dt + round(runif(10), 3)
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, fast = FALSE)
sqlColumns(channel, "Dtest")[, 1:6]
sqlFetch(channel, "Dtest")
sqlDrop(channel, "Dtest")
sqlDrop(channel, "hills test", errors = FALSE)
sqlSave(channel, hills, "hills test", verbose=TRUE)
sqlUpdate(channel, hills[11:15,], "hills test", verbose=TRUE, fast=TRUE)
sqlFetch(channel, "hills test")
sqlDrop(channel, "hills test")
sqlSave(channel, hills, "hills test", verbose=TRUE, fast=FALSE)
sqlUpdate(channel, hills[11:15,], "hills test", verbose=TRUE, fast=FALSE)
sqlDrop(channel, "hills test")
# sqlQuery(channel, "create schema test2")
sqlTables(channel, catalog="", schema="", tableName="", tableType="%")
sqlTables(channel, catalog="%", schema="", tableName="")
sqlTables(channel, catalog="", schema="%", tableName="")
close(channel)
|
