dbGetQuery in R truncates really long the SQL query (of length 10564 characters)

Question

I have been trying to execute a really long SQL query and get the result into a data frame in R. But the following line throws an error and displays partial query in the console (truncates it)

my_dataframe <- dbGetQuery(conn, my_large_query)

The length of the query is 10564 characters where I use a lot of CTEs. I have removed the string truncation via options menu in R studio but I wonder if there a character limitation in the dbGetQuery function in R?

Any suggestions?

• RDMBS: DB2 (on IBM AS400),
• R Package: DBI (library - RJDBC)

Answer 1

You mentioned changing the 'string truncation', but how about warning.length?

options("warning.length"={integer})

I'd also suggest that you test the query out first in whatever GUI is available for your database. When you verify there's no problem with the query, then run it in R.

When dbGetQuery (assuming the DBI package) throws an sql/database error, the actual content of the error won't be shown until after the full text of the query. In other words, if the query text is being truncated in the R error output, the database error code/text coming will not be visible at all. The issue may be something extremely simple like an unmatched bracket or a missing comma.

Please note whether the error message starts with Error in dbGetQuery (R issue) or Error in .verify.JDBC.result (database issue).