Error Handling in R with RSQLite

I use sqlite for an in-process (serverless) data store.  I run a collector process which subscribes to “real-time” market data from IB’s TWS for 20 or so securities, and each security has it’s own sqlite data file where the respective incoming data stream is stored.

The collector process is written in java and it uses sqlite4java as a very lightweight wrapper (no JDBC) around the standard C implementation of the sqlite library.  As an aside, the collector process also uses zeroMQ in a pub/sub pattern to immediately forward market data to other processes which request a particular securities’ data stream.

I chose this architecture because it allows me to run live trading applications in close proximity to research tools without interfering each other.  I prefer to write these research tools in R.

Of course, the back-testing and research tools get their data directly from the sqlite files, and they do this using the RSQLite package.  There are the inevitable locking issues between multiple processes accessing the same sqlite data file, and the application programmer has to account for this.  This is the trade-off when using sqlite in a multiprocess read/write environment.  I’m ok with the trade-off as I find it easier to administer/re-locate/archive the data stores using simple files in my one-guy does everything approach.

There are two points in the R code where I need to account for the locking issues: 1) when calling dbSendQuery(), and 2) fetch().  Each function allows for an error handler to be passed in, but the examples provided in the RSQLite documentation lead to the complete stoppage of the R program.  Here is the example from the documentation:

tryCatch(dbGetQuery(con, "SELECT * FROM tableDoesNotExist"),
         error=function(e) { print("caught") }
        )

I want to recognize the error state, handle it, and successfully return the data anyway. This is the purpose of this post (finally!) – to document successful recovery when encountering locking issues using RSQLite.  Here is the approach I use:


gotResultSet <- FALSE
while(!gotResultSet) {
  resultSetObj <- tryCatch(dbSendQuery(db, sql), 
                           error=function(e) 
                           { 
                             print("Caught RSQLite Error: dbSendQuery() Failed")
                             print(e)
                             return(e)
                           }
                          )
  error <- dbGetException(db) 

  if (error$errorMsg != "OK") {
    ## DEBUG    cat("SQLite returned an error on dbSendQuery().  Wait a short time and try again\n") 
    ## DEBUG    print(error)
    ## OBSERVED:    $errorNum
    ## OBSERVED:    [1] 5
    ## OBSERVED:    
    ## OBSERVED:    $errorMsg
    ## OBSERVED:    [1] "error in statement: database is locked"
    
    ## Do not call dbClearResult(resultSetObj) here or else the following error msg is produced
    ##     Error in function (classes, fdef, mtable)  : 
    ##       unable to find an inherited method for function "dbClearResult", for signature "simpleError"

    Sys.sleep(0.05) # sleep 0.05 seconds
    next
  } 
  gotResultSet <- TRUE 
}

A similar approach is used with fetch(), only here I test for the class of the returned object from tryCatch() (testing for the class could also be used above, and it would insulate the developer should the underlying package change).

gotData <- FALSE
while(!gotData) {
  trades <- tryCatch(fetch(resultSetObj, n=-1), 
                       error=function(e)
                       {
                         print("Caught RSQLite Error: fetch() Failed")
                         print(e)
                         return(e)
                       }
                    )
  error  <- dbGetException(db) 

  ##  DEBUG print(class(trades))
  ##  OBSERVED:    [1] "Caught RSQLite Error: fetch() Failed"
  ##  OBSERVED:    Error in trades[, -c(1, 2)] : incorrect number of dimensions

  if (inherits(trades, "error") ) {
    cat("SQLite returned an error on fetch().  Wait and try again\n") 
    print(error)
    Sys.sleep(0.05)
    next 
  }

  dbClearResult(resultSetObj)
  gotData <- TRUE
}

Getting WordPress to run at 1and1.com

So it should be straight forward to setup a simple wordpress blog at 1and1.com, right?  “Right”.  Well it is actually once you know how to deal with 1and1.com’s default ancient usage of PHP4.4.9 . (The PHP ChangeLog states it was released on 8/7/2008)

Following the simple instructions for people who have shell access, I created a new mysql db, edited the wp-config.php file, and then went to load wp-admin/install.php only to be greeted by the following error message:

Your server is running PHP version 4.4.9 but WordPress 3.4.2 requires at least 5.2.4.

Luckily Gabe Daiz has this covered with his post on running WP at 1and1.com.  Click here to see the expanded article that covers the solution to this problem, and many others as well.

You need to edit/create the .htaccess file in the wp directory to force the use of PHP5 by 1and1.com.  The following two lines do the trick:

AddType x-mapp-php5 .php
AddHandler x-mapp-php5 .php

Thanks to George

I finally decided to start a personal site as a place holder for technical oddities and notes that I run across in my day-to-day life.  I’m thinking of a personal knowledge base in between the great Circus Ponies NoteBook, and a text file I edit with vim – but definitely more information value than I can comfortably stuff into a d.me entry.  Thanks to my long time pal (“hey pal”) George Drapeau for suggesting this idea, ahem, last decade.