Free the text

[markb]

So I’m in the process of liberating my data on a shoestring budget
with SpatiaLite, all test connections to my database (through
Christian Werner’s ODBC driver) are going great, but then I run into
major difficulties returning ADODB RecordSet’s in ArcMap VBA on any
Text field whatsoever (Integer fields are ok). The instant I include
Text columns in the Select statement field list or Where clause, I end
up with a falsely empty recordset (it even chokes on SELECT *...).
Getting data into the db with Connection.Excute is no problem (DSN or
Driver based connections). Reading data via linked tables or pass-
through queries in MS Access is no problem. I’ve tried various
versions of ADO (currently on 2.8) and the SQLite ODBC driver
(currently on 0.79). I've tried all manor of cursor and lock types.
This is where I say, "What in the what what?". It is an ArcMap 9.3
and Access 2003 shop, I can’t change that, but I can spatially enable
Access and greatly 'open-up' ArcMap. Any insights are very welcome.
Please be kind, as I'm quite green. I'm running XP sp2.

Is anyone connecting with a free & current OLE DB provider instead
that can happen to load the libspatialite-2.dll successfully? BTW, it
would be best if I could stick with ADO, since I or someone else may
need to re-point to another db in the future. But if necessary, I
could easily be open to using something like dhSQLite (http://
www.thecommon.net/2.html) for its other advantages (speed, etc.), if
it could load the SpatiaLite dll. I use the dhSortedDictionary
faithfully.

Thanks everyone for any help you can provide.

[a.fu...@lqt.it]

Hi,

this one looks like an ADODB specific problem, more than
an SQLite / SpatiaLite problem ...
I find very puzzling you got problems with TEXT values; they
are anyway a plain, standard SQLite type ... SpatiaLite on its
own does absolutely nothing of specific for TEXT values,
simply relying on basic SQLite features.

I suppose this ADODB issue will show with a pure SQLite DB
as well (i.e. no SpatiaLite extended stuff, no geometries etc; only
integer, float and text values); is this true ?
let me know, please ...

the only two suppositions I can advance are the following ones:

1.SQLite's TEXT can store an arbitrary length string, but
the standard SQL CHAR or VARCHAR type is usually
constrained to a maximum 256 bytes length; are you
sure none of your text values exceeds this limit ?

2.SQLite's TEXT is UTF-8 encoded [at least, I'll expect this
on a clean, well organized DB]; this is standard for Linux,
but for Windows XP the expected encoding is the locale one,
such as CP1252 [depending on your nation/culture/location].
An encoding mismatch surely can cause problems, if it's
not handled the right way [an assumption I can absolutely
grant you for Microsoft-related software :-) ].

bye, Sandro

[markb]

Sandro,

Thanks for the quick reply. I completely agree that this "looks like
an ADODB specific problem", it's good to have some confirmation. That
was why I was wondering about what technology people are connecting
with, that also supports loading your library. I should have
mentioned that this is an issue regardless of the SpatiaLite extension
being loaded or not, I definitely tested that, sorry about not
mentioning it (no cause for alarm there). This essentially empty
tester db was built with your GUI tool (good job btw), and only has
minimal Integer & Text fields at the moment (so no text field with
greater than about 30 characters right now). All characters read from
your GUI Tool (set to CP1252) appear the same in MS ACC, and look the
same prior to being inserted from ArcMap VBA via a Connection.Execute
"INSERT INTO…" statement. I'm also having the same problem with the
'test-2.2.sqlite' db you so kindly provided. Encoding was certainly
something I was aware of as a potential cause, but frankly I'm quite
ignorant as far as all that goes (I really don't know much more than
your tutorial on that subject). Grunt, me in US, machine is vanilla
flavored.

Are there any serious limitations using your 2.2 library with SQLite
version 3.4.1 (which is used by dhSQLite)?


Everyone,

Can the VBE have character encoding different from its 'containing'
ArcMap or Access GUI? Do my results "rule-out" any possible problems
with the ODBC driver in your opinion? Is this just an ADO bug? Or,
is my ADO install simply broke? Is anyone willing to replicate this
problem, so I'm not in the world all alone? Maybe I'll try to post
this over at the Nabble SQLite forum. After all I realize it is only
tangentially related to SpatiaLite, although it certainly presents a
usability issue as far as a great many like me are concerned (this is
my gateway to a better place beyond the house that MS & ESRI built).
If its not obvious, programming isn't exactly my problem domain. But
it certainly demonstrates a break-through for a utility such as this
to reach a lowly VBA scripter (though having much wider aspirations of
course). The ultimate way-at-the-end-user (like me) can even see how
cool & useful this stuff is.

Again, as always, thanks so much to everybody for all the help.

[a.fu...@lqt.it]

Hi Mark,

just a quick response to your "environmental" questions:

- As anybody can notice, SpatiaLite is an "open source"
project; this is not a synonym for "no-cost", but is
more like a pervasive ethic / culture / philosophy
- The open source community tends to develop and release
high-quality software, respecting complete user's freedom,
and thus making the source code publicly available
- this is exactly the opposite of "closed source" aka
proprietary software, which is based on copyrights,
fees and royalties, denying any user's freedom, and
keeping their source code undisclosed.

All the software you are using combined with Sqlite/Spatialite
is "closed source"; Windows, VBA and ArcMap are typical
proprietary undisclosed software

This IS NOT the optimal environment to deploy Sqlite/SpatiaLite;
as a rule of the thumb, open source software is intended to
cooperate with other open source sw, not with "closed source" sw
In other words, nothing forbids you to use a mix of open source
and proprietary sw, but you can't expect the open source community
can help you to resolve problems arising from undisclosed software.

The dhSQLite too seems to be "free=no-cost", but I see no
sources at all, so this too isn't open source.
BTW, dhSQLite is based on SQLite v.3.4.0; this one is a
VERY OLD sqlite's release [June 2007]; SpatiaLite is
based on SQLite v.3.6.3 [September 2008]. I don't think
this can be related to your TEXT issue, but really this
is a very obsolescent sqlite's version.

Some open source alternatives I can suggest you:
- use Python or C, not VBA
- use Quantum Gis, not the ESRI stuff

----------------------------------------------------
a final suggestion: you can try to submit your TEXT
issue to the dhSQLite developer; probably they can
give you some useful hint.

bye, Sandro

[Keith]

Markb,

I'm able to connect to a Sqlite 3 database inside ArcMap 9.2, XP sp3
with Christian Werner’s ODBC driver - SQLite3 ODBC Driver. I also
inserted more than 255 characters in a Text field and queried the
table. I'm not using any ArcGIS objects, just plain ADO connection and
recordset objects. I did not use the Spatialite dll.

Keith

[Mark Bradford]

Keith,

Could you post a code snippet? Thank you very much for testing this out for me.

[Adam Estrada]

Mark,
Install the ODBC software for sqlite and make the connection in your control panel. Then, connect to that data source through arcgis. I think that Keith was explaining that he was not using spatialite in this case.

Adam

-----Original Message-----
From: "Mark Bradford" <brad.m...@gmail.com>

Date: Thu, 9 Oct 2008 15:35:20
To: <spatiali...@googlegroups.com>
Subject: [SpatiaLite-Users] Re: Free the text

[Mark Bradford]

I think I mentioned it, but the result is the same regardless of
whether or not I include "LoadExt=libspatialite-2.dll;" in the
connection string or not. And I've definitely installed the ODBC
driver correctly, since I'm writing to it via ADO just fine (connected
via DSN or Driver strings). I've also just verified the same problem
via DAO as well (writes fine, but text read error). Maybe my MDAC
install is corrupt? I've also replicated all this with Sandro's test
db and one I made from the SQLite cml without loading SpatiaLite. I
can't rule out user error, that was why I was looking for a code
snippet as to rule out my own ignorance. Keith 'seemed' to be doing
precisely what I require, but can get done.

This SQL statement returns a populated recordset in ADO & DAO (both
fields are INTEGER's):
"SELECT EditSessionID, MaxSaveSequence FROM SessionInfo" with or
without an ending ";"

This one returns an empty recordset in ADO and an inaccessible UserID
field in DAO (UserID is TEXT):
"SELECT EditSessionID, UserID FROM SessionInfo" with or without an ending ";"

[Keith]

I wrote this initially as a Vbscript. You can test it outside of
ArcMap, just change debug.print to wscript.echo.

Sub query_sqlite()

Set objRS = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")

Const adUseClient = 3
Const adOpenStatic = 3
Const adLockOptimistic = 3

objConn.Open "DRIVER=SQLite3 ODBC Driver;Database=E:\keith\sqlite
\sqlitespy\World.db3;"

strQuery = "Select * FROM City"

With objRS
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open strQuery, objConn
End With

' List All Fields
For Each f In objRS.Fields
Debug.Print f.Name
Next

If objRS.RecordCount > 0 Then
objRS.movefirst
Do Until objRS.EOF
Debug.Print objRS("Name")
objRS.movenext
Loop
End If
End Sub

[markb]

Thanks Keith for the code snippet, ultimately very helpful since my
prior ADO (and DAO) experience hasn't covered ODBC connections. I've
mostly just manipulated "current db" recordsets within Access
frontends. My newbie problem was getting the right CursorLocation
with allowable cursor and lock types. It appears that adUseServer
only works with a forward-only cursor. I certainly learned a ton of
useful stuff trying to ascertain the underlying cause from the
misleading error codes and behavior though (much more familiar with
ADO now). Thanks everybody for your help, and sorry for the slow
response.

[Keith]

Mark,

I cannot load libspatialite-2.dll from my connection string with the
ODBC driver. I'm getting an error like "cannot load module".
I tried the full path to libspatialit-2.dll, placing the dlls in c:
\windows\system32, ... no luck
The libspatialite-2.dll will not register - regsvr32, which is
probably ok.
I've changed my PATH variable to point to my libspatialite folder as
well, no luck
libspatialite-2.dll will manually load from Sqlite3 command
line, .load libspatialite-2.dll

I'd appreciate any help.

objConn.Open "DRIVER=SQLite3 ODBC Driver;Database=E:\SpatiaLite
\test.db;Loadext=libspatialite-2.dll"

Keith

[Mark Bradford]

Keith,
Boiled down, here's what's working for me during my initial testing:

{frmEditWatch}
Private Sub cmdInitEvents_Click()
...
Call WriterEvents.InitEvents("Provider=MSDASQL.1;Driver={SQLite3
ODBC Driver};" & _
"Database=C:\MADb\testDb_LF.sqlite;" & _
"StepAPI=0;SyncPragma=;NoTXN=0;Timeout=;" & _

"ShortNames=0;LongNames=0;NoCreat=0;NoWCHAR=0;" & _
"LoadExt=libspatialite-2.dll;", strGUID)
...

{clsLockWriter}
Public Function InitEvents(ByVal ConnectionStr As String, _
ByVal MXDSessionGUID As String) As Long
...
adoCon.Open ConnectionStr
...

I copied the dll's in the spatialite-2.2-win-libs/dll folder to
C:\WINDOWS\system32, and didn't register any of them. Specifying the
Provider for ODBC connections isn't required, since it is the default
provider. The curly brackets aren't necessary either. You are
missing an ending semicolon, not sure about that? The other parms
should be optional? Have you tried the SQLite SQL function
'load_extension()'? You can use it within a Select statement. My
guess is that you need the other dll dependencies(?) located along
with the 'libspatialite-2.dll'. Let us know if you get the SQLite
ODBC Driver to successfully load 'libspatialite-2.dll' from a
different location (not sys32). Good luck.

[Keith]

Thanks Mark, that worked. I copied all 7 dll's to the System32
folder. It would be nice if Spatialite used an Environment Variable.
I have almost 2000 dll's in the System32 folder. This is a great tool.

Keith

[Adam Estrada]

I am having the same problem loading the dll through adobe live cycle. I will try this today and report back what I find. I think that the so called "optional" params are actually required but I'm not completely sure about that. Have a great weekend...

Adam

-----Original Message-----
From: Keith <lewis....@gmail.com>

Date: Fri, 24 Oct 2008 18:20:36
To: SpatiaLite Users<spatiali...@googlegroups.com>

Subject: [SpatiaLite-Users] Re: Free the text