[R-sig-DB] RSQLite load extension

[Gabor Grothendieck]

sqldf uses libspatiate-1.dll which is from 2.3.1 stable.  I think you
are using the development version.

You might want to use the stable version until you have it working
since there are too many unknowns otherwise.

The main dll seems to load all the others so you its unnecessary to do
that yourself.  For example, try moving the other dll files so that it
cannot find them.  You will get an error when you try to load the main
dll showing that its trying to load the others.

On Mon, Feb 22, 2010 at 6:15 AM, Nick Baumberger <nbaumberger using yahoo.com> wrote:
> 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
>>
>
> _______________________________________________
> 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
>