2

I am trying to execute a postgres function within R (using Spotfire Terr), however I cant get the syntax to work for dbSendQuery

I have tried both dbGetQuery and dbSendQuery and can't get it to work. I looked for several examples, but the only ones that I have found are those that have "Insert into" or "UPDATE" statements. I haven't been able to find how to execute a function. I saw that it is possible with sql server using sqlExecute (dbcon, "EXEC ....."). Is there something equivalent for Postgres?

so the function does a few insert statements based on 3 parameters that are passed. Here's what I tried:

Name<-'this is a test'
AInteger<-1
BInteger<-2
result<- dbSendQuery(conn, 'select * from "Main"."InsertDataSet"(?,?,?)', list(Name, AInteger,bInteger))

the function takes 3 parameters. In postgres database, I call the function with select * from "Main"."InsertDataSet"('a name',123,124). I figured that it could be called the same way in R...I can't find a diff example, that is otherwise.

I get the following error

TIBCO Spotfire Statistics Services returned an error: 'Error: RS-DBI driver: (could not Retrieve the result : ERROR: syntax error at or near "?" LINE 1: select * from "Main"."InsertDataSet"? ^ ) eval(expr, envir, enclos) eval(expr, envir, enclos) dbSendQuery(conn, "select * from \"Main\".\"InsertDataSet\"?", standardGeneric("dbSendQuery") .standardGeneric("dbSendQuery", structure(function(conn, statement, dbSendQuery(conn, "select * from \"Main\".\"InsertDataSet\"?", postgresqlExecStatement(conn, statement, ...) NULL'. at Spotfire.Dxp.Data.DataFunctions.Executors.RemoteFunctionClient.OnExecuting() at Spotfire.Dxp.Data.DataFunctions.Executors.AbstractFunctionClient.d__31.MoveNext() at Spotfire.Dxp.Data.DataFunctions.Executors.SPlusFunctionExecutor.d__12.MoveNext() at Spotfire.Dxp.Data.DataFunctions.DataFunctionExecutorService.d__3.MoveNext()

yanci
  • 163
  • 1
  • 13

1 Answers1

0

I have never used the R Postgres package, but going by this SO answer I can suggest the following:

sql <- "SELECT * FROM Main.InsertDataSet($1,$2,$3)"
result <- dbSendQuery(conn, sql, c(Name, AInteger,bInteger))
out <- dbFetch(result)
dbClearResult(result)

or more directly

out <- dbGetQuery(conn, sql, c(Name, AInteger,bInteger))
r2evans
  • 141,215
  • 6
  • 77
  • 149
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • i made the change above,but now i am getting an error with my last statement dbDisconnect(conn) Error in postgresqlCloseConnection(conn, ...) : connection has pending rows (close open results set first) Am I suppose to send the results somewhere before closing the connection? – yanci Aug 27 '19 at 16:24
  • OK what is this Postgres function doing? – Tim Biegeleisen Aug 27 '19 at 16:25
  • I used dbGetQuery instead of dbSendQuery and it now works without any errors. Thanks!! – yanci Aug 27 '19 at 16:28
  • @TimBiegeleisen, I assumed a little by editing your question, hope I didn't go too far. – r2evans Aug 27 '19 at 16:41
  • 2
    @r2evans Not at all, I appreciate your help. The OP's original attempt to bind a list seemed off to me, so I tried to dig around. – Tim Biegeleisen Aug 27 '19 at 16:42