1

The error messages I get from RPostgreSQL contain nonsense characters where there should be some informative text. Here are two examples:

Example 1: Querying a non-existent table

> library("RPostgreSQL")
Loading required package: DBI
>  drv <- dbDriver("PostgreSQL")
>  connection <- dbConnect(
+      drv,
+      dbname="postgres",
+      host=db_host,
+      port=5432,
+      user=.pguser,
+      password=.pgpass
+      )
>
> results <- dbGetQuery(connection, "SELECT * FROM t1")
Error in postgresqlExecStatement(conn, statement, ...) :
  RS-DBI driver: (�W��J)
Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
  Could not create execute: SELECT * FROM t1 LIMIT 20

Example 2: Attempting to connect to a database with invalid credentials.

> library("RPostgreSQL")
Loading required package: DBI
>  drv <- dbDriver("PostgreSQL")
> connection <- dbConnect(
+      drv,
+      dbname="postgres",
+      host=db_host,
+      port=5432,
+      user=.pguser,
+      password=.pgpass
+      )
Error in postgresqlNewConnection(drv, ...) : RS-DBI driver: (8�)

I would like to see an error message instead of RS-DBI driver: (�W��J) or RS-DBI driver: (8�). What is going on here? How can I see the complete error message?

Molly
  • 13,240
  • 4
  • 44
  • 45
  • 1
    What encoding are you using? Is it English, Latin, or some other language? Is the encoding on your computer, in R, RStudio, and this database the same? For example, to test my theory, I changed the encoding in R to `ru_RU.KOI8-R` (non-UTF-8 formatting) and intentionally made an erroneous query with `POSTgreSQL`. The error message was gibberish. When I changed it to `ru_RU.UTF-8` (a Russian and UTF-compliant alternative), my messages were no longer gibberish. Before: `��������������:` and after: `Предупреждение:` (That just means _warning_.) – Kat Dec 09 '22 at 04:56
  • That's a good thought but I checked and the encodings are all English. – Molly Dec 12 '22 at 18:49
  • Are they English and UTF-8 (I'm not sure if it can there is an English that isn't UTF-8, but I'd hate to assume!) How about the database and server? How about the computer? Is it all the same encoding? – Kat Dec 12 '22 at 22:17
  • What OS is your device? I'm guessing it's Windows? Can you let me know about your OS, your server version, and your version of the r package `RPostgreSQL`? – Kat Dec 12 '22 at 22:23
  • The setup is a little complicated, R and Postgres are running on Linux (Redhat 8.7) servers but I access them using BitVise (using a terminal to run R and Rscript) or Chrome (for RStudio) running on Windows 10. The character encoding for Postgres, Linux and R is en_US.UTF-8. The version of RPostgeSQL is 0.7-3. PostgreSQL is version 14.6. R is version 4.2.2. The charter encoding for Windows is iso-8859-1 and for BitVise it is UTF-8. Thank you for your help! – Molly Dec 13 '22 at 18:30
  • I tried to find a method to recreate your situation, but it's tough! I did read that RPostgresSQL doesn't work with SSL, but mine is via SSL. I'm curious if that's strictly a Windows issue. If you wouldn't mind, try using the package `RPostgres` instead of `RPostgresSQL` and see if that works. `library(DBI); library(RPostgres); connection <- dbConnect(RPostgres::Postgres(), user = .pguser, password = .pgpass, dbname = "postgres", host = db_host)` If that doesn't work, add the argument `sslmode = "require"` to `dbConnect` (I didn't need to set that, though.) – Kat Dec 13 '22 at 23:40
  • I tried `RPostgres` and it does work. I was hoping to get `RPostgreSQL` working but `RPostgres` might be the way to go. Thank you again for your help! – Molly Dec 14 '22 at 22:55

0 Answers0