13

I am querying a database in R using RJDBC. The queries are built up from data which is read in from a file. These queries can get very long, and can potentially include non existent columns (resulting in an error).

Below is a simplified example, it takes the file as input and the runs 2 queries generated from the file.

table     column
drinks    cost
drinks    sugar
drinks    volume
food      cost

SELECT column, cost, sugar FROM drinks;
SELECT cost FROM food;

Because these queries can get very long, any errors from the database are often truncated before the useful information. One of my current errors reads:

ERROR [2018-05-16 16:53:07] Error processing table data_baseline_biosamples for DAR-2018-00008 original error message: Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ", : Unable to retrieve JDBC result set for SELECT ed.studyid, {very long list of columns} ,ct.nmr_xl_vldl_pl,ct.nmr_xl_

Because the database error includes the entire query before the key information, the truncation removes valuable information for solving the problem.

In this case the error message probably ends with something like this:

(line 1, Table 'data_biosamples' owned by 'littlefeltfangs' does not contain column 'sample_source'.)

How to I record the full error message sent by the database or otherwise extract the final part of that message?

I am capturing the error in a tryCatch and passing the error into a log file using futile.logger. The total error length when truncated is 8219 characters, with 8190 of those appearing to be from the database.

littlefeltfangs
  • 396
  • 2
  • 17
  • 1
    Since R seems to have a "hard coded" max length you could catch the error within the SQL code, extract the error message an returning e. g. as a result set with one cell. Which database are you using? – R Yoda May 19 '18 at 08:47
  • 1
    Ingres is beyond my skills but `Inquire_SQL` seems to deliver the error message of the last executed SQL statement (http://docs.actian.com/ingres/11.0/index.html#page/OpenSQLRef%2FError_Checking_Using_Inquire_Statements.htm%23 and http://docs.actian.com/ingres/11.0/index.html#page/OpenSQLRef%2FError_Checking_Using_Inquire_Statements.htm%23) and an example code can be found here: http://docs.actian.com/ingres/11.0/index.html#page/OpenSQLRef%2FINQUIRE_SQL_Example.htm%23 – R Yoda May 21 '18 at 17:12

2 Answers2

14

It's not RJDBC that's cutting off the error message.

See ?stop:

Errors will be truncated to getOption("warning.length") characters, default 1000.

So you can set the option:

stop(paste(rep(letters, 50L), collapse = ''))
options(warning.length = 2000L)
stop(paste(rep(letters, 50L), collapse = ''))

You'll notice the truncation in the first message, but no the second.

For my own helper functions catching errors from RDJBC, I use something like:

result = tryCatch(<some DB operation>, error = identity)

Then do regular expressions on result$message to test for various common errors & produce a friendlier error message.

Not mentioned in ?stop is that warning.length can only be in a fairly narrow range of values. To explore this I ran the following code:

old = options('warning.length')
can = vapply(1:16000L, function(ii) {
  !is.error(tryCatch(options(warning.length=ii), error=identity))
}, logical(1L))
options(old)

png('~/Desktop/warning_valid.png')
plot(can, las = 1L, ylab = 'Valid option value?',
     main = 'Valid option values for `warning.length`',
     type = 's', lwd = 3L, log = 'x')
first = which.max(can)
switches = c(first, first + which.min(can[first:length(can)] - 1L))
abline(v = switches, lty = 2L, col = 'red', lwd = 2L)
axis(side = 1L, at = switches, las = 2L, cex = .5)
dev.off()

A plot showing valid values of the 'warning.length' option. the plot starts at 0 (invalid) and rises to 1 (valid) at Index=100, where it stays before dropping back to 0 at around 8172.

I'm not sure about the minimum value of 100, but 8172 is 8192 - 20 (20 is enough characters to include [... truncated]). Here is the place in the R source where these values are hard-coded in, and here is the place where the buffer size of 8192 is defined.

FWIW, in my own error-parsing helper function (built for querying PrestoDB), I have this line:

core_msg = gsub('.*(Query failed.*)\\)\\s*$', '\\1', result$message)

This is catered to the error messages that come out of PrestoDB, so you'll have to customize it yourself, but the idea is to clip out that part of your error message which is just regurgitating the query itself.

Alternatively, of course you can split result$message into two bits which are less than 8172 characters and print them out separately.

MichaelChirico
  • 33,841
  • 14
  • 113
  • 198
0

While not a solution for the general case, the solution to my specific case was to move from using the RJDBC package to the odbc package (not the RODBC package). Both are based on DBI, which means that switching should be as simple as installing an ODBC driver and replacing your dbConnect parameters. Error messages produced by the odbc package do not include the original query, so do not run into the truncation issue I was struggling with.

For comparison, this is the complete set of changes I've needed to make:

Original:

request_settings[['db_con']]<-dbConnect(global_settings$ingresJDBC,url="jdbc:ingres://localhost:IJ7/myvnode::mydatabase;")

New:

request_settings[['db_con']]<-dbConnect(odbc::odbc(),driver="Ingres",server="myvnode",database="mydatabase")

The error messages are much more compact. E.g.,

Error in new_result(connection@ptr, statement): nanodbc/nanodbc.cpp:1344: 42501: [Actian][Ingres ODBC Driver][Ingres]line 1, Table 'mytable' owned by 'littlefeltfangs' does not contain column 'mycolumn'.

The documentation for the odbc package (what there is of it) can be found here.

littlefeltfangs
  • 396
  • 2
  • 17