Pass R variable to RODBC's sqlQuery?
Build the string you intend to pass. So instead of
example <- sqlQuery(myDB,"SELECT * FROM dbo.my_table_fn (x)")
do
example <- sqlQuery(myDB, paste("SELECT * FROM dbo.my_table_fn (",
x, ")", sep=""))
which will fill in the value of x
.
Pass R variable to RODBC's sqlQuery with multiple entries?
Look into the collapse
argument in the paste()
documentation. Try replacing b
with paste(b, collapse = ", ")
, as shown below.
Edit As Joshua points out, sqlQuery returns a data.frame, not a vector. So, instead of paste(b, collapse = ", ")
, you could use paste(b[[1]], collapse = ", ")
.
library(RODBC)
channel <- odbcConnect("test")
b <- sqlQuery(channel,
"select top 1 Noinscr
FROM table
where PrixVente > 100
order by datevente desc")
sqlQuery(channel,
## note paste(b[[1]], collapse = ", ") in line below
paste("insert into TestTable (UniqueID) Values (", paste(b[[1]], collapse = ", "),")", sep = "")
Parameterize SQL Queries using R RODBC
You can create a input variable at the start and pass it to your query.
For example:
# Change your FY here
input_FY <- 2016
dbhandle <- odbcDriverConnect('driver={SQL Server};server=SQLSERVER;database=MYDATABASE;trusted_connection=true')
degrees <- sqlQuery(dbhandle, paste0("
select Inst, ID, DegreeDate, Degree
from DEGREE
where FY = ('", input_FY, "')
group by Inst, ID, DegreeDate, Degree
order by Inst, ID, DegreeDate, Degree"),
as.is=TRUE)
So for any complicated queries you can still pass the same input_FY
variable or any other variable that you have declared at the start of code for a quick/easy update.
Run SQL script from R with variables defined in R
There are several options, but my preferred is "bound parameters".
If, for instance, your 'Query.sql'
looks something like
select ...
from MyTable
where CreatedDateTime > ?
The ?
is a place-holder for a binding.
Then you can do
con <- dbConnect(...) # from DBI
df = dbGetQuery(con, statement = read_file('Query.sql'), params = list(dt))
With more parameters, add more ?
s and more objects to the list
, as in
qry <- "select ... where a > ? and b < ?"
newdat <- dbGetQuery(con, qry, params = list(var1, var2))
If you need a SQL IN
clause, it gets a little dicey, since it doesn't bind things precisely like we want.
candidate_values <- c(2020, 1997, 1996, 1901)
qry <- paste("select ... where a > ? and b in (", paste(rep("?", length(candidate_values)), collapse=","), ")")
qry
# [1] "select ... where a > ? and b in ( ?,?,?,? )"
df <- dbGetQuery(con, qry, params = c(list(avar), as.list(candidate_values)))
R RODBC putting list of numbers into an IN() statement
You're not paste
ing your query together correctly.
If you run the paste
statement in isolation, you'll see that you get a vector of length 5000, so sqlQuery
is only executing the first one of those, corresponding to the first element in samRowNum
.
What you want to do is something more like this:
paste("select * FROM db where row_id in (",
paste(sampRowNum,collapse = ","),")", sep="")
Just as an added note (and since I've had to do stuff like this a lot...) constructing sql queries with an IN
clause with strings is a bit more of a nuisance, since you have to tack on all the single quotes:
vec <- letters[1:5]
paste("SELECT * FROM db WHERE col IN ('",
paste(vec,collapse = "','"),"')",sep = "")
[1] "SELECT * FROM db WHERE col IN ('a','b','c','d','e')"
If you do this a lot, you'll end up writing a little function that does that pasting of character vectors for you.
As always, this kind of SQL string manipulation is Not Good if you are dealing with user inputs (e.g. in a web app), due to SQL injection attacks. In my particular situation this isn't much of a concern, but in general people will prefer parametrized queries if you don't have much control over the input values.
Pass R Vector to Sql query
The new dbplyr
package has the best answer for this. It allows any R object to be used, and automatically converts it to SQL
https://db.rstudio.com/dplyr/
How to pass function parameters in sql query in R?
You could try with paste
:
func <- function(x){
sqlQuery(dbhandle, paste('select attr from myTable where cond =', x))
}
RODBC using EXEC @variable query
Essentially, you are building a dynamic query in TSQL using procedural semantics. But R is a procedural language. Hence, consider looping through databases to build concatenated data frame (counterpart of UNION
).
library(RODBC)
sql <- "select a.colA, b.colB, c.colC
from dbo.tableA as a
inner join dbo.tableB as b on b.x = a.x
inner join dbo.tableC as c on c.y = b.y
where col1 is not null"
get_db_data <- function(db) {
# DYNAMICAL DATABASE CONNECTION
conn <- odbcDriverConnect(paste0("Driver={SQL Server};",
"Server=myserver;Database=", db,
";Trusted_connection=Yes"))
df <- sqlQuery(conn, sql)
odbcClose(conn)
return(df)
}
# BUILD LIST OF DFs FROM DIFFERENT DBs
df_list <- lapply(c("proj1", "proj2", "proj3"), get_db_data)
# EQUIVALENT TO UNION QUERY
final_df <- unique(do.call(rbind, df_list))
RODBC command 'sqlQuery' has problems with table variables in t-SQL
Try toggling NOCOUNT
as below:
old_qry <- "
DECLARE @tbl_IDs TABLE
(
Country nvarchar(30),
CID nvarchar(5),
PriceID int,
WindID int
)
INSERT INTO @tbl_IDs
VALUES
('Germany', 'DE', 112000001, 256000002);
SELECT * FROM @tbl_Ids
"
##
new_qry <- "
SET NOCOUNT ON;
DECLARE @tbl_IDs TABLE
(
Country nvarchar(30),
CID nvarchar(5),
PriceID int,
WindID int
);
INSERT INTO @tbl_IDs
VALUES
('Germany', 'DE', 112000001, 256000002);
SET NOCOUNT OFF;
SELECT * FROM @tbl_Ids
"
R> sqlQuery(tcon, gsub("\\n", " ", old_qry))
#character(0)
R> sqlQuery(tcon, gsub("\\n", " ", new_qry))
# Country CID PriceID WindID
#1 Germany DE 112000001 256000002
Basically you want to SET NOCOUNT ON
at the beginning of your code, and SET NOCOUNT OFF
just before the final SELECT
statement.
Related Topics
SQL Server Insert If Not Exists
Query For Array Elements Inside Json Type
How to Do Insert into a Table Records Extracted from Another Table
Ordering by Specific Field Value First
How to Update Selected Rows With Values from a CSV File in Postgres
Error, String or Binary Data Would Be Truncated When Trying to Insert
How to Use Script Variables in Psql
MySQL Trigger After Update Only If Row Has Changed
Remove Identity from a Column in a Table
Including Parameters in Openquery
How to Implement Limit With SQL Server
Search For "Whole Word Match" in MySQL
What Is the String Concatenation Operator in Oracle
Remove Trailing Zeros from Decimal in SQL Server