[R-sig-DB] RSQLite load extension

[Gabor Grothendieck]

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
>