CODEWITHSUNDEEP
sqlrrodbc
Bojan
Bojan

Reputation: 11

RODBCext query parameters

I have sql query in R of this type:

    query <- "SELECT a.* FROM 
               ( (SELECT x,y,z FROM t1,t2,t3 where c1 = ? and c2 = ?) as b
                 LEFT JOIN
                 (SELECT x1,y1,z1 FROM t4, t5, t6 where c3 = ? and c4 = ?) as c
                  ON b.x = c.x1 ) as a"

I'm using RODBCext to connect and query database. I do it like this:

    library(RODBCext)
    conHandle <- odbcConnect("dsn", uid="uid", pwd = "pwd", believeNRows = FALSE)
    parameters <- data.frame(v1,v2,v3,v4) #values for parameters
    response <- sqlExecute(conHandle, query, parameters, fetch = TRUE)

I receive this error:

    Error in sqlExecute(conn, upit, parameters, fetch = TRUE) :
    [RODBCext] Error: Number of parameters in query do not match number of columns in data

Any help about this ??

Upvotes: 1

Views: 1157

Answers (1)

James Vance
James Vance

Reputation: 177

Answering this for anyone is looking after the fact:

According to the github for the rodbcext package: this error throws because number of params (number of question marks in your sql query) does not match the number of columns in the parameters dataframe. The mistake made was that wrapping a vector in data.frame results in n rows, not n columns.

if(nparams != LENGTH(data))
{
  error(_("Number of parameters does not match number of columns in provided data"));
  FreeHandleResources(thisHandle);
  return 100;
}

Upvotes: 1

Related Questions