I am trying to write a prepared statement with dbSendQuery. My issue is that the data frame of inputs are converted to numeric values, but two of the three inputs are dates. This results in the following error message:
Warning: Error in postgresqlExecStatement: RS-DBI driver: (could not Retrieve the result : ERROR: invalid input syntax for type timestamp: "17624" )
My code is as follows:
query = dbSendQuery(con,"
SELECT
***AL LOT OF TABLES AND JOINS***
WHERE
users_terminals.user_user_id = $1 and
planning_stops.planned_arrival >= $2 and
planning_stops.planned_arrival <= $3"
,
data.frame(user$users_users_id,
datefrom,
dateto))
tmp = dbFetch(query)
dbClearResult(query)
The numeric value of datefrom is 17624, so this make me think that $2 is replaced by as.numeric(datefrom) when I run the command. Also, user$users_users_id is a numeric value and I do not get an error for that one. Probably the whole data frame is converted to numeric.
I have created a workaround, but it is not an optimal situation and I would like to understand what happens here. The workaround I created is:
query = dbSendQuery(con,"
SELECT
***AL LOT OF TABLES AND JOINS***
WHERE
users_terminals.user_user_id = $1 and
EXTRACT(day from planning_stops.planned_arrival - '1970-01-01') >= $2 and
EXTRACT(day from planning_stops.planned_arrival - '1970-01-01') <= $3"
,
data.frame(user$users_users_id,
datefrom,
dateto))
tmp = dbFetch(query)
dbClearResult(query)
Could anyone help me out here? The workaround works for now, but it does not seem to be optimal.
Thanks.
UPDATE
I have read something about sqlInterpolate, so I thought let's give it a try. However, I still receive an error message:
Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : ERROR: operator does not exist: timestamp without time zone >= integer LINE 57: ... planning_stops.planned_arrival >= 2018-04... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. )
My code is now:
query = sqlInterpolate(con,"
SELECT
***AL LOT OF TABLE AND JOINS***
WHERE
users_terminals.user_user_id = ?id1 and
planning_stops.planned_arrival >= ?date1 and
planning_stops.planned_arrival <= ?date2"
,
id1 = user$users_users_id,
date1 = datefrom,
date2 = dateto)
tmp = dbGetQuery(con,
query)
Still not working though.. It seems sqlInterpolate converts the inputs to integer.