Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Reprex of trying to write data to Dolt with RMariaDB
library(sys)
library(RMariaDB)
library(DBI)
library(withr)
# Clean up from previous session
unlink("doltdb", recursive = TRUE)
try(dbDisconnect(conn), silent = TRUE)
# Running latest Dolt Compiled from source, create dolt db and start server
dir.create("doltdb", showWarnings = FALSE)
with_dir("doltdb", exec_wait("~/go/bin/dolt", "init"))
#> Successfully initialized dolt data repository.
#> [1] 0
dolt_server_pid <- with_dir("doltdb", sys::exec_background(
"~/go/bin/dolt", c("sql-server", "--port 3333", "--host 127.0.0.1", "--user user", "--password pwd")
))
Sys.sleep(1) # Wait for startup to connect
conn <- dbConnect(RMariaDB::MariaDB(), host = "127.0.0.1", port = 3333,
username = "user", password = "pwd", dbname = "doltdb")
# Create the table from the data frame but do not add data, this works fine
dbCreateTable(conn, "mtcars", mtcars)
dbReadTable(conn, "mtcars")
#> [1] mpg cyl disp hp drat wt qsec vs am gear carb
#> <0 rows> (or 0-length row.names)
# Adding Data to the table doesn't work
dbAppendTable(conn, "mtcars", mtcars)
#> [1] 0
dbReadTable(conn, "mtcars")
#> [1] mpg cyl disp hp drat wt qsec vs am gear carb
#> <0 rows> (or 0-length row.names)
# Try to add data using low-level commands
statement <- enc2utf8(new("SQL", .Data = "INSERT INTO `mtcars`\n (`mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`)\nVALUES\n (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"))
params <- unname(as.list(mtcars))
rs <- new("MariaDBResult",
sql = statement,
ptr = RMariaDB:::result_create(conn@ptr, statement, is_statement = TRUE),
bigint = conn@bigint,
conn = conn)
# No rows affected both before and after binding data to the the result
rs
#> <MariaDBResult>
#> SQL INSERT INTO `mtcars`
#> (`mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`)
#> VALUES
#> (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
#> ROWS Fetched: 0 [incomplete]
#> Changed: NA
dbGetRowsAffected(rs)
#> [1] NA
if (!is.null(params)) {
dbBind(rs, params)
}
rs
#> <MariaDBResult>
#> SQL INSERT INTO `mtcars`
#> (`mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`)
#> VALUES
#> (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
#> ROWS Fetched: 0 [complete]
#> Changed: 0
dbGetRowsAffected(rs)
#> [1] 0
tools::pskill(dolt_server_pid)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment