1context("dbSendQuery") 2 3test_that("attempting to change schema with pending rows generates warning", { 4 con <- dbConnect(SQLite()) 5 on.exit(dbDisconnect(con)) 6 7 df <- data.frame(a = letters, b = LETTERS, c = 1:26, stringsAsFactors = FALSE) 8 dbWriteTable(con, "t1", df) 9 10 rs <- dbSendQuery(con, "SELECT * FROM t1") 11 row1 <- fetch(rs, n = 1) 12 expect_equal(row1, df[1, ]) 13 14 expect_warning(dbSendQuery(con, "CREATE TABLE t2 (x text, y integer)"), 15 "pending rows") 16}) 17 18 19test_that("simple position binding works", { 20 con <- dbConnect(SQLite(), ":memory:") 21 dbWriteTable(con, "t1", data.frame(x = 1, y = 2)) 22 23 dbGetPreparedQuery(con, "INSERT INTO t1 VALUES (?, ?)", 24 bind.data = data.frame(x = 2, y = 1)) 25 26 expect_equal(dbReadTable(con, "t1")$x, c(1, 2)) 27}) 28 29test_that("simple named binding works", { 30 con <- dbConnect(SQLite(), ":memory:") 31 dbWriteTable(con, "t1", data.frame(x = 1, y = 2)) 32 33 dbGetPreparedQuery(con, "INSERT INTO t1 VALUES (:x, :y)", 34 bind.data = data.frame(y = 1, x = 2)) 35 36 expect_equal(dbReadTable(con, "t1")$x, c(1, 2)) 37}) 38 39test_that("named binding errors if missing name", { 40 con <- dbConnect(SQLite(), ":memory:") 41 dbWriteTable(con, "t1", data.frame(x = 1, y = 2)) 42 43 expect_error( 44 dbGetPreparedQuery(con, "INSERT INTO t1 VALUES (:x, :y)", 45 bind.data = data.frame(y = 1)), 46 "incomplete data binding" 47 ) 48}) 49 50bind_select_setup <- function() { 51 con <- dbConnect(SQLite()) 52 df <- data.frame(id = letters[1:5], 53 x = 1:5, 54 y = c(1L, 1L, 2L, 2L, 3L), 55 stringsAsFactors = FALSE) 56 57 dbWriteTable(con, "t1", df, row.names = FALSE) 58 con 59} 60 61test_that("one row per bound select", { 62 con <- bind_select_setup() 63 64 got <- dbGetPreparedQuery(con, "select * from t1 where id = ?", 65 data.frame(id = c("e", "a", "c"))) 66 67 expect_equal(got$id, c("e", "a", "c")) 68}) 69 70test_that("failed matches are silently dropped", { 71 con <- bind_select_setup() 72 sql <- "SELECT * FROM t1 WHERE id = ?" 73 74 df1 <- dbGetPreparedQuery(con, sql, data.frame(id = "X")) 75 expect_equal(nrow(df1), 0) 76 expect_equal(names(df1), c("id", "x", "y")) 77 78 df2 <- dbGetPreparedQuery(con, sql, data.frame(id = c("X", "Y"))) 79 expect_equal(nrow(df2), 0) 80 expect_equal(names(df2), c("id", "x", "y")) 81 82 df3 <- dbGetPreparedQuery(con, sql, data.frame(id = c("X", "a", "Y"))) 83 expect_equal(nrow(df3), 1) 84 expect_equal(names(df3), c("id", "x", "y")) 85}) 86 87test_that("NA matches NULL", { 88 con <- bind_select_setup() 89 dbGetQuery(con, "INSERT INTO t1 VALUES ('x', NULL, NULL)") 90 91 got <- dbGetPreparedQuery(con, "SELECT id FROM t1 WHERE y IS :y", 92 data.frame(y = NA_integer_)) 93 94 expect_equal(got$id, "x") 95}) 96