I am working on a program that has to send data to Microsoft SQL Server through R. Since SQL Server doesn't have any INSERT OR IGNORE INTO like PostgreSQL, I have to check if the value is already in the table. However, I don't know if I'm doing it right. How do I send multiple parameters (or the same parameter twice) into SQL Server using dbBind?
Here is my code:
statement <- "IF NOT EXISTS (SELECT * FROM dbo.nodes WHERE node_id=?) INSERT INTO dbo.nodes (node_id) VALUES (?)"
insertnew <- dbSendQuery(conn, statement)
test_p1 <- list(5, 6)
test_p2 <- list(test_p1, test_p1)
dbBind(insertnew, params=test_p2)
When I run it it gives me the following error:
Error in result_bind(res@ptr, params, batch_rows) :
RAW() can only be applied to a 'raw', not a 'double'
Using RStudio's Show Traceback feature, it shows this:
result_bind(res@ptr, params, batch_rows)
.local(res, params, ...)
dbBind(insertnew, params = test_p1)
Does anybody know what I'm doing wrong here? If the parameters should be something other than a list of lists, what should they be? Is there a way to name parameters to use twice in the same statement? What is RAW() and what is it trying to apply it to?
dbo.nodes is a table with one column, node_id NVARCHAR(1000) NOT NULL PRIMARY KEY.
conn is a DBIConnection object created earlier using dbConnect(odbc::odbc(), ...). It works for executing other statements so there shouldn't be a problem there.
Adding the argument batch_rows=1 to dbBind doesn't change anything (not that I understand what that is supposed to do).
Thanks in advance for the help!
test_p1 = c(5, 6)
test_p2 = unname(data.frame(I(test_p1), I(test_p1)))
In case this helps someone looking this up later, changing it from a list of lists to a data frame made from vectors worked.