r-mysql: use r variable to fetch a column from database

Question

Can I pass an r variable into a function for selecting a column from the database so that different columns are selected every time the variable in r changes:

father <- 'father'
myfun(father)

Function:

myfun <- function (parent)
{
query <-  paste("SELECT '$parent' from table1 where EXTRACT(YEAR FROM dob) 
between '",date1,"' and '",date2,"'",sep='')

connect1 <<- dbGetQuery (con, query )
connect1
}

@mt1022 We would also need to make sure that there are spaces between SELECT and parent. So maybe make the separator a single space. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 24, 2017 at 13:27
@TimBiegeleisen and Prasinus, sorry I did not notice that the default sep was changed. Just copied it from OP's code. — mt1022
– mt1022, Commented Dec 24, 2017 at 13:38

Tim Biegeleisen · Accepted Answer · 2017-12-24 13:22:16Z

1

It is not possible to have a table column be a parameter in a prepared statement. But I don't see anything stopping you from trying something along the lines of the following:

parent <- "father"
query <- paste0("SELECT ", parent, " FROM table1 WHERE YEAR(dob) BETWEEN '", date1, "' AND '", date2, "'", sep='')

answered Dec 24, 2017 at 13:22

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

r-mysql: use r variable to fetch a column from database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related