unable to find an inherited method for function ‘dbExecute’ for signature ‘\"character\", \"character\"’" about redshifttools HOT 5 CLOSED

this is my session info

session_info()
Session info --------------------------------------------------------------------------------------------------------------------------
setting value
version R version 3.5.1 (2018-07-02)
system x86_64, mingw32
ui RStudio (1.1.453)
language (EN)
collate English_Australia.1252
tz Australia/Perth
date 2018-10-25

Packages ------------------------------------------------------------------------------------------------------------------------------
package * version date source
assertthat 0.2.0 2017-04-11 CRAN (R 3.5.0)
aws.s3 * 0.3.20 2018-08-24 Github (cloudyr/aws.s3@3d2b2e1)
aws.signature 0.4.4 2018-07-27 CRAN (R 3.5.1)
base * 3.5.1 2018-07-02 local
base64enc 0.1-3 2015-07-28 CRAN (R 3.5.0)
bindr 0.1.1 2018-03-13 CRAN (R 3.5.0)
bindrcpp 0.2.2 2018-03-29 CRAN (R 3.5.0)
bit 1.1-14 2018-05-29 CRAN (R 3.5.0)
bit64 0.9-7 2017-05-08 CRAN (R 3.5.0)
bitops * 1.0-6 2013-08-17 CRAN (R 3.5.0)
blob 1.1.1 2018-03-25 CRAN (R 3.5.1)
compiler 3.5.1 2018-07-02 local
crayon 1.3.4 2017-09-16 CRAN (R 3.5.0)
curl 3.2 2018-03-28 CRAN (R 3.5.0)
data.table * 1.11.4 2018-05-27 CRAN (R 3.5.1)
datasets * 3.5.1 2018-07-02 local
DBI * 1.0.0 2018-05-02 CRAN (R 3.5.0)
devtools * 1.13.6 2018-06-27 CRAN (R 3.5.1)
digest 0.6.15 2018-01-28 CRAN (R 3.5.1)
dplyr * 0.7.6 2018-06-29 CRAN (R 3.5.1)
glue 1.3.0 2018-07-17 CRAN (R 3.5.1)
graphics * 3.5.1 2018-07-02 local
grDevices * 3.5.1 2018-07-02 local
hms 0.4.2 2018-03-10 CRAN (R 3.5.0)
httr 1.3.1 2017-08-20 CRAN (R 3.5.0)
jsonlite 1.5 2017-06-01 CRAN (R 3.5.0)
magrittr 1.5 2014-11-22 CRAN (R 3.5.0)
memoise 1.1.0 2017-04-21 CRAN (R 3.5.0)
methods * 3.5.1 2018-07-02 local
mime 0.5 2016-07-07 CRAN (R 3.5.0)
pillar 1.3.0 2018-07-14 CRAN (R 3.5.1)
pkgconfig 2.0.2 2018-08-16 CRAN (R 3.5.1)
plyr 1.8.4 2016-06-08 CRAN (R 3.5.1)
purrr 0.2.5 2018-05-29 CRAN (R 3.5.0)
R6 2.2.2 2017-06-17 CRAN (R 3.5.0)
Rcpp 0.12.18 2018-07-23 CRAN (R 3.5.1)
RCurl * 1.95-4.11 2018-07-15 CRAN (R 3.5.1)
redshiftTools * 0.3.900 2018-08-14 Github (8b7d295)
rjson * 0.2.20 2018-06-08 CRAN (R 3.5.0)
rlang 0.2.2 2018-08-16 CRAN (R 3.5.1)
RPostgres * 1.1.1 2018-05-06 CRAN (R 3.5.1)
RPostgreSQL * 0.6-2 2017-06-24 CRAN (R 3.5.0)
RSiteCatalyst * 1.4.15 2018-04-22 CRAN (R 3.5.1)
rstudioapi 0.7 2017-09-07 CRAN (R 3.5.0)
stats * 3.5.1 2018-07-02 local
stringi 1.1.7 2018-03-12 CRAN (R 3.5.0)
stringr 1.3.1 2018-05-10 CRAN (R 3.5.0)
tibble 1.4.2 2018-01-22 CRAN (R 3.5.0)
tidyselect 0.2.4 2018-02-26 CRAN (R 3.5.0)
tools 3.5.1 2018-07-02 local
utils * 3.5.1 2018-07-02 local
withr 2.1.2 2018-03-15 CRAN (R 3.5.0)
xml2 1.2.0 2018-01-24 CRAN (R 3.5.0)
zendeskR * 0.4 2014-02-21 CRAN (R 3.5.1)

from redshifttools.

hi there,
after further investigation i realised that I was using a different connection for this table.
the difference was that i was including tcpkeepalive parameters for the test table. Whereas, the other tables were not using the tcpkeepalive connection:

write_conn <- dbConnect(RPostgres::Postgres()
, dbname="blahblah"
, host='blahblah'
, port='xxx'
, user='xxxx'
, password='xxxxx'
, keepalives = 1
, keepalives_idle = 30
, keepalives_interval = 30
)

I assume this is the culprit. I guess my question is, is there a way to include keeptcpalive parameters for writing back to Redshift? sometimes data.table can be very very large and i would have to split my file to 100-200 files to write, which takes a long enough time for the connection to be timedout.

thanks.

from redshifttools.

That's strange @ankhnesmerira, i'm updating a lot of data and i've not had many timeouts, are you sure you need to specify those parameters?

Anyway, i'm not doing anything special, just querying with dbGetQuery and dbExecute, can you check if that connection is supporting dbExecute correctly with simple queries? It'd be weird that adding the keepalive would break executing statements

from redshifttools.

hi there,
thanks so much for your prompt response. the query works well for smaller data sizes, and times out for larger ones. I have a feeling though that it's got something to do with the firewall setting, as this problem didn't exist before before.
I'm able to work my way around it at the moment by dividing data into smaller pieces. I'll get back to you if any further progress on the issue.

Thanks again for your time and help.

from redshifttools.

Ok glad to hear you found a workaround, let me know if you need to revisit this error.

from redshifttools.