[R-sig-DB] RSQLite load extension

[Nick Baumberger]

Hi there,

this response brings me a bit further but there is still missing some piece in the puzzle. I can now load the 'main' dll (libspatialite-2.dll), and RSQLite is finding the function stubs - however they don't do anything - I guess because the underlying dll's are yet not loaded. And funnily I can't load these dll's (libgeos-3-0-0.dll, libgeos_c-1.dll, libproj-0.dll) in the same manner as libspatialite-2.dll. (They are in the same directory which is on the path.)

So what I get on Windows XP using R 2.9.1 is


library(SQLiteMap)
library(RSQLite)
dbDisconnect(con)
sqli.db <- system.file("sqlimaps/sids.db3", package="SQLiteMap")
drv <- dbDriver("SQLite")
con <- dbConnect(drv, dbname = sqli.db, loadable.extensions = TRUE)
s <- sprintf("select load_extension('%s')","libspatialite-2.dll")
dbGetQuery(con,s)

sql <- 'select isempty(sids.geom) from sids where gid <= 10'
dbGetQuery(con,sql)
  isempty(sids.geom)
1                  -1
2                  -1
..

sql <- 'select centroid(sids.geom) from sids where gid <= 10'
dbGetQuery(con,sql)
  centroid(sids.geom)
1                 <NA>
2                 <NA>
..

.. nice that I don't get an error, but the result is a bit empty ! This I get when I try to load the other dll's ..

sql <- 'SELECT load_extension("libgeos-3-0-0.dll")'
dbGetQuery(con, sql)
Fehler in sqliteFetch(rs, n = -1, ...) : 
  RSQLite driver: (RS_SQLite_fetch: failed first step: The specified procedure could not be found.

unfortunately the links in Gabor's mail don't help the R users
any idea ??

regards
Nick







--- On Sun, 2/21/10, Gabor Grothendieck <ggrothendieck using gmail.com> wrote:

> From: Gabor Grothendieck <ggrothendieck using gmail.com>
> Subject: Re: [R-sig-DB] RSQLite load extension
> To: tech using wildintellect.com
> Cc: r-sig-db using stat.math.ethz.ch
> Date: Sunday, February 21, 2010, 12:21 AM
> sqldf in the sqldf package
> automatically loads spatialite if its in
> its path.   That feature has only been
> tested on Windows but it works
> at least there.  Here is the source code that is used
> in sqldf -- the
> spatialite feature has only been tested on Windows and I
> have omitted
> the part here  that checks whether spatialite is
> actually present or
> not:
> 
>    m <- dbDriver("SQLite")
>    connection <- dbConnect(m, dbname =
> dbname, loadable.extensions = TRUE)
>    s <- sprintf("select
> load_extension('%s')", dll)
>    dbGetQuery(connection, s)
> 
> References:
> example of using spatialite from sqldf:
> http://code.google.com/p/sqldf/#Example_14._Use_of_spatialite_library_functions
> 
> important bug to be aware of in spatialite 2.3.1:
> https://groups.google.com/group/spatialite-users/msg/182f1f629c922607
> 
> sqldf home page:
> http://sqldf.googlecode.com
> 
> On Sat, Feb 20, 2010 at 11:20 PM, Alex Mandel
> <tech_dev using wildintellect.com>
> wrote:
> > Does anyone have a working example of loading an
> SQLite extension.
> > (There was a similar email in Nov but no answer)
> >
> > I've tried the following, and it doesn't throw any
> errors during the
> > load, but none of the extension functions work those
> throw errors.
> >
> > library(RSQLite)
> > m <- dbDriver("SQLite")
> > con <- dbConnect(m, dbname =
> "test.db",loadable.extensions = TRUE)
> > sql <- "SELECT load_extension('libspatialite.so')"
> > rs <- dbSendQuery(con, sql)
> > dbGetException(con)
> > dbClearResult(rs)
> >
> > sql <- "SELECT id, AsText(geom) as points FROM
> locations Limit 10;"
> > dtest <- dbGetQuery(con, sql)
> > Error in sqliteExecStatement(con, statement,
> bind.data) :
> >  RS-DBI driver: (error in statement: no such
> function: AsText)
> >
> > This query works fine via other SQLite interfaces.
> >
> > Thanks,
> > Alex
> >
> > _______________________________________________
> > R-sig-DB mailing list -- R Special Interest Group
> > R-sig-DB using stat.math.ethz.ch
> > https://stat.ethz.ch/mailman/listinfo/r-sig-db
> >
> 
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>