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 = "")
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
.
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/
r sql multiple user specified variables
You should prepare the string first.
I'm not 100% confident with R
Just make sure all the space between keywords are there.
id1 = "(1,2,3)"
id2 = "(8,9,10)"
strQuery = paste("SELECT * FROM dbo.my_table ",
"WHERE COLUMN1 in " , id1,
" AND COLUMN2 in " , id2, sep = " ")
Then:
example <- sqlQuery(myDB , strQuery)
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.
Fetching SQL data in R with parameters
You are using the selector parameter incorrectly, as you wrote it selector it's interpreted as a literal. You can use sprintf to create the sql string passing parameters:
getData <- function(selector){
sql <- sprintf("SELECT * FROM match_player_home WHERE match_id = %s", selector)
rs = dbSendQuery(con, sql)
}
The string "%s" inside sprintf will be substitute by the value of selector.
Passing characters as argument into db function in R
As others say, you can use paste
or paste0
to construct the query. However, the sprintf
function can also do the trick. I think this is slightly more easy to read as you avoid mixed single and double quotation marks.
I.e. do something like the following in your function:
query <- sprintf("SELECT lbl, Date, dot
FROM
tblData t
WHERE t.lbl= '%s'
AND t.Date < '%s'
ORDER BY t.Date desc", x, y)
sqlQuery(con, query)
Related Topics
How to Specify the Size/Layout of a Single Plot to Match a Certain Grid in R
Predict Out of Sample on Fixed Effects Model
R Xts: .001 Millisecond in Index
Likert Plot Showing Percentage Values
Calculate Percentage for Each Time Series Observations Per Group in R
What Exactly Does Complete in Mice Do
R Ggplot2: Using Stat_Summary (Mean) and Logarithmic Scale
How to Compare Two Factors with Different Levels
How to Replace Lower/Upper Triangular Elements of a Matrix
How to Adapt a Latex Beamer Theme to Apply It in an Rmarkdown::Beamer_Presentation
Tidy Data.Frame with Repeated Column Names
How to Print Double Quotes (") in R
Month Language in the As.Date Function
Shortcut Using Lm() in R for Formula
Regression with Heteroskedasticity Corrected Standard Errors