SQL queries in R with RJDBC crash if column or table doesn't exist

Question

After creating a simple connection conn to an Oracle database, I write a query like the below:

dbGetQuery(conn, "select * from CUSTOMERS where rownum <= 10")

Since the table CUSTOMERS exists, this returns a set of rows.

However, if I write the below query and accidentally type CSUTOMERS, (the U and S are switched), I get an error that actually CLOSES R or RStudio. I like to use R interactively, which means that I'll inevitably make typos and reference tables or columns that don't exist. RJDBC is completely unusable if it crashes your R session because you make a typo in SQL.

Below is the code and resultant error.

dbGetQuery(conn, "select * from CSUTOMERS where rownum <= 10")

2016-04-04 20:03:27.475 R[3292:355239] Apple AWT Internal Exception : java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
    at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:873)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
    at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1491)
    at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
2016-04-04 20:03:27.475 R[3292:355239] *** Terminating app due to uncaught exception 'JavaNativeException', reason: 'java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
'
*** First throw call stack:
(
    0   CoreFoundation                      0x00007fff998c803c __exceptionPreprocess + 172
    1   libobjc.A.dylib                     0x00007fff9065976e objc_exception_throw + 43
    2   CoreFoundation                      0x00007fff998c7bd9 -[NSException raise] + 9
    3   JavaNativeFoundation                0x000000010cc4fc3f JNFCallStaticVoidMethod + 213
    4   libawt.jnilib                       0x00000001157a614d setBusy + 53
    5   CoreFoundation                      0x00007fff997e4127 __CFRUNLOOP_IS_CALLING_OUT_TO_AN_OBSERVER_CALLBACK_FUNCTION__ + 23
    6   CoreFoundation                      0x00007fff997e4080 __CFRunLoopDoObservers + 368
    7   CoreFoundation                      0x00007fff997d62ba __CFRunLoopRun + 1178
    8   CoreFoundation                      0x00007fff997d5bd8 CFRunLoopRunSpecific + 296
    9   libtcl8.6.dylib                     0x000000010c10c6a7 Tcl_WaitForEvent + 293
    10  libtcl8.6.dylib                     0x000000010c0c2742 Tcl_DoOneEvent + 357
    11  tcltk.so                            0x000000010c01a6ca TclSpinLoop + 26
    12  libR.dylib                          0x0000000106700188 R_ToplevelExec + 456
    13  tcltk.so                            0x000000010c01a697 TclHandler + 55
    14  libR.dylib                          0x000000010689e277 R_ProcessEvents + 55
    15  libR.dylib                          0x000000010673f49d R_CheckUserInterrupt + 77
    16  libR.dylib                          0x0000000106745062 Rf_eval + 66
    17  libR.dylib                          0x0000000106735b9b Rf_findFun + 331
    18  libR.dylib                          0x000000010674cb93 bcEval + 29491
    19  libR.dylib                          0x00000001067451a6 Rf_eval + 390
    20  libR.dylib                          0x000000010679bcb8 Rf_applyClosure + 1400
    21  libR.dylib                          0x0000000106756999 bcEval + 69945
    22  libR.dylib                          0x00000001067451a6 Rf_eval + 390
    23  libR.dylib                          0x000000010679bcb8 Rf_applyClosure + 1400
    24  libR.dylib                          0x0000000106745537 Rf_eval + 1303
    25  libR.dylib                          0x000000010679ebe3 do_begin + 451
    26  libR.dylib                          0x000000010674562e Rf_eval + 1550
    27  libR.dylib                          0x000000010674562e Rf_eval + 1550
    28  libR.dylib                          0x000000010679ebe3 do_begin + 451
    29  libR.dylib                          0x000000010674562e Rf_eval + 1550
    30  libR.dylib                          0x000000010679cf2e R_execClosure + 926
    31  libR.dylib                          0x000000010679cb25 R_execMethod + 613
    32  methods.so                          0x00000001088946cd R_dispatchGeneric + 2173
    33  libR.dylib                          0x00000001067e1cda do_standardGeneric + 634
    34  libR.dylib                          0x0000000106757d4d bcEval + 74989
    35  libR.dylib                          0x00000001067451a6 Rf_eval + 390
    36  libR.dylib                          0x000000010679bcb8 Rf_applyClosure + 1400
    37  libR.dylib                          0x0000000106756999 bcEval + 69945
    38  libR.dylib                          0x00000001067451a6 Rf_eval + 390
    39  libR.dylib                          0x000000010679cf2e R_execClosure + 926
    40  libR.dylib                          0x000000010679cb25 R_execMethod + 613
    41  methods.so                          0x00000001088946cd R_dispatchGeneric + 2173
    42  libR.dylib                          0x00000001067e1cda do_standardGeneric + 634
    43  libR.dylib                          0x00000001067456a8 Rf_eval + 1672
    44  libR.dylib                          0x000000010679bcb8 Rf_applyClosure + 1400
    45  libR.dylib                          0x0000000106745537 Rf_eval + 1303
    46  libR.dylib                          0x000000010679f752 do_set + 1858
    47  libR.dylib                          0x000000010674562e Rf_eval + 1550
    48  libR.dylib                          0x000000010679ebe3 do_begin + 451
    49  libR.dylib                          0x000000010674562e Rf_eval + 1550
    50  libR.dylib                          0x000000010679bcb8 Rf_applyClosure + 1400
    51  libR.dylib                          0x0000000106745537 Rf_eval + 1303
    52  libR.dylib                          0x000000010679f752 do_set + 1858
    53  libR.dylib                          0x000000010674562e Rf_eval + 1550
    54  libR.dylib                          0x000000010679ebe3 do_begin + 451
    55  libR.dylib                          0x000000010674562e Rf_eval + 1550
    56  libR.dylib                          0x000000010674562e Rf_eval + 1550
    57  libR.dylib                          0x000000010679ebe3 do_begin + 451
    58  libR.dylib                          0x000000010674562e Rf_eval + 1550
    59  libR.dylib                          0x000000010679bcb8 Rf_applyClosure + 1400
    60  libR.dylib                          0x0000000106745537 Rf_eval + 1303
    61  libR.dylib                          0x000000010679ebe3 do_begin + 451
    62  libR.dylib                          0x000000010674562e Rf_eval + 1550
    63  libR.dylib                          0x000000010674562e Rf_eval + 1550
    64  libR.dylib                          0x000000010674562e Rf_eval + 1550
    65  libR.dylib                          0x000000010679ebe3 do_begin + 451
    66  libR.dylib                          0x000000010674562e Rf_eval + 1550
    67  libR.dylib                          0x000000010679bcb8 Rf_applyClosure + 1400
    68  libR.dylib                          0x0000000106745537 Rf_eval + 1303
    69  libR.dylib                          0x000000010679ebe3 do_begin + 451
    70  libR.dylib                          0x000000010674562e Rf_eval + 1550
    71  libR.dylib                          0x000000010679cf2e R_execClosure + 926
    72  libR.dylib                          0x000000010679cb25 R_execMethod + 613
    73  methods.so                          0x00000001088946cd R_dispatchGeneric + 2173
    74  libR.dylib                          0x00000001067e1cda do_standardGeneric + 634
    75  libR.dylib                          0x00000001067456a8 Rf_eval + 1672
    76  libR.dylib                          0x000000010679f752 do_set + 1858
    77  libR.dylib                          0x000000010674562e Rf_eval + 1550
    78  libR.dylib                          0x000000010679ebe3 do_begin + 451
    79  libR.dylib                          0x000000010674562e Rf_eval + 1550
    80  libR.dylib                          0x000000010679bcb8 Rf_applyClosure + 1400
    81  libR.dylib                          0x0000000106745537 Rf_eval + 1303
    82  libR.dylib                          0x000000010679f752 do_set + 1858
    83  libR.dylib                          0x000000010674562e Rf_eval + 1550
    84  libR.dylib                          0x000000010679ebe3 do_begin + 451
    85  libR.dylib                          0x000000010674562e Rf_eval + 1550
    86  libR.dylib                          0x000000010679cf2e R_execClosure + 926
    87  libR.dylib                          0x000000010679cb25 R_execMethod + 613
    88  methods.so                          0x00000001088946cd R_dispatchGeneric + 2173
    89  libR.dylib                          0x00000001067e1cda do_standardGeneric + 634
    90  libR.dylib                          0x00000001067456a8 Rf_eval + 1672
    91  libR.dylib                          0x000000010679bcb8 Rf_applyClosure + 1400
    92  libR.dylib                          0x0000000106745537 Rf_eval + 1303
    93  libR.dylib                          0x00000001067cfae3 Rf_ReplIteration + 675
    94  libR.dylib                          0x00000001067d0fe5 R_ReplConsole + 149
    95  libR.dylib                          0x00000001067d0f19 run_Rmainloop + 73
    96  R                                   0x000000010669af3b main + 27
    97  libdyld.dylib                       0x00007fff95d9d5c9 start + 1
    98  ???                                 0x0000000000000001 0x0 + 1
)
libc++abi.dylib: terminating with uncaught exception of type JNFException
Abort trap: 6

It really seems problematic. I've never used RJDBC myself, as I only need to interface with MySQL dbs, but if it dies with "uncaught exception", that is pretty dangerous to use on any serious program. Have you checked if other methods have the same problem? Such as using dbSendQuery instead of dbGetQuery? Finally, it seems that the culprit is not RJDBC, but the oracle jdbc driver. Is there a possibility to use another JDBC driver as the connector? — andrechalom, Apr 05 '16 at 01:05
@andrechalom This SQLException is not a bug or problem in the JDBC driver. A JDBC driver is expected to throw an exception if the query has an error (in this case a table or view that doesn't exist), so using another driver (if there was one) is not going to fix this. — Mark Rotteveel, Apr 05 '16 at 07:51
Yes, I understand that the Java driver should throw an exception. The problem is: the R code should handle the exception gracefully instead of freaking out and dying. I haven't yet looked at the internals of RJDBC, so I don't know whose to blame for this mess; but I will probably have time for this still today. — andrechalom, Apr 05 '16 at 15:43

SQL queries in R with RJDBC crash if column or table doesn't exist

0 Answers0