Error in as.character(query) : cannot coerce type ‘closure’ to vector of type ‘character’

I was trying to load some data from the oracle database, During the proceeding every line of code was fine until I try to run the last line: df<- sqlQuery(sql_connection,sql_statement,stringsAsFactors=0), an error in the title occurred.

So here is the basic structure of my code:

require(RODBC)
require(lubridate)
require(stringr)
require(dplyr)
require(tidyr)

#server for N2 purge attribute
target <- "xxxx"
user   <- "xxxx"
pass   <- "xxxx"


#print("Working on lot history...")


sql_string <- paste0("select
xxxx
from
xxxx
where
xxxx

")

sql_statement <- sprintf

#sql_statement <- sprintf(sql_string, BackDate, DidList[m])
sql_connection <- odbcConnect(dsn = target, uid = user, pwd = pass,believeNRows=FALSE)

df<- sqlQuery(sql_connection,sql_statement,stringsAsFactors=0)

since I’m new to R, I don’t feel I can tell where is the cause of the error. If someone can help me out with this I’d be much appreciated it.

Answer

In this line, you assign the base function sprintf to sql_statement:

sql_statement <- sprintf

You then pass it to sqlQuery() as the second argument (query):

df<- sqlQuery(sql_connection,sql_statement,stringsAsFactors=0)

The function expects query to be a character, so you get the error that R can’t coerce a ‘closure’ (essentially another name for a function) to a character.

I assume you were trying to imitate this commented-out line:

#sql_statement <- sprintf(sql_string, BackDate, DidList[m])

But it’s not necessary if all you want to do is run the query you just wrote. So if we just tidy up your code so sql_string is passed straight to sqlQuery(), it should work as intended, though I can’t actually test it because we don’t have access to your database:

require(RODBC)
require(lubridate)
require(stringr)
require(dplyr)
require(tidyr)

#server for N2 purge attribute
target <- "xxxx"
user   <- "xxxx"
pass   <- "xxxx"

#print("Working on lot history...")

sql_string <- paste0("select
xxxx
from
xxxx
where
xxxx
")

sql_connection <- odbcConnect(dsn = target, uid = user, pwd = pass,believeNRows=FALSE)
df<- sqlQuery(sql_connection,sql_string,stringsAsFactors=0)