RSQLite load extension

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 at gmail.com> wrote:

From: Gabor Grothendieck <ggrothendieck at gmail.com>
Subject: Re: [R-sig-DB] RSQLite load extension
To: tech at wildintellect.com
Cc: r-sig-db at 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 at 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