Using Spatialite within C++ - locating .lib and .h files

[ckgoo...@gmail.com]

Hello all,

I want to make use of Spatialite within my C++ application and normally I’d expect to do this by linking a .lib and including a .h file in my project as well as linking to a .dll.  So far I can only locate .dll files.  Do the .lib and .h files exist in a Windows 64-bit distributable?

Kind regards, Chris

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

Hi Chris,

the Windows distribution's DLLs are not intended to be linked in
the most classic way but to be loaded dynamically at run-time.

simply put, your app code only needs to execute a particular
SQL statement immediately after opening a new SQLite connection
to load and activate the SpatiaLite extension.

SELECT load_extension('mod_spatialite');

it works on any known programming language (Java, Python,
PHP etc) and of course it also works on C/C++

for further information:
https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod_spatialite

bye Sandro

[ckgoo...@gmail.com]

Thanks Sandro, I will give that a go.
Best, Chris

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/58f377efe8d36944433af58b1b788f63%40lqt.it.

[ckgoo...@gmail.com]

Hi, after getting distracted with Shapefiles, I'm following up on the email trail below. To load Spatialite in my C++ console app on Windows I was told to execute a statement to load the Spatialite extension:
SELECT load_extension('mod_spatialite');

My main() function is as below but although the call to create the database is successful the call to load the Spatialite extension returns a value of 1 (one).

I downloaded the Windows AMD64 binaries and put the folder on my C: drive and added this path to my environment variable.

Can anyone give me suggestions on what I am doing wrong to get an error code of 1 (generic error)?
sqlite3* m_DB;
int rc = sqlite3_open( "coastlinesSpatial.db", &m_DB);

//Create a call to spatial library to load it
string sql = "SELECT load_extension('mod_spatialite');";
char* messaggeError;
rc = sqlite3_exec(m_DB, sql.c_str(), nullptr, 0, &messaggeError);

Best, Chris
-----Original Message-----
From: spatiali...@googlegroups.com <spatiali...@googlegroups.com> On Behalf Of a.fu...@lqt.it
Sent: Saturday, June 22, 2024 9:33 AM
To: spatiali...@googlegroups.com
Subject: Re: [SpatiaLite-Users] Using Spatialite within C++ - locating .lib and .h files

[Brad Hards]

On Wednesday, 10 July 2024 5:52:08 AM AEST ckgoo...@gmail.com wrote:
> My main() function is as below but although the call to create the database
> is successful the call to load the Spatialite extension returns a value of
> 1 (one).

Probably the module or a dependent DLL is not being found. Possibly the
environment you are running the command in is not using the same PATH. Or
maybe you need to restart to get the env vars.

Brad

[ckgoo...@gmail.com]

Hi, I've confirmed that my mod_spatialite-5.1.0-win-amd64 folder is in my environment path. I've rebooted my machine. Still getting a return code of 1. Is there any way to get more detail on the error? Or does anyone have other thoughts on what I'm doing wrong?
Summary:
Statement to create a database works.
Following statement to load spatialite fails with error 1.
This is in a console c++ app.
Kind regards, Chris

-----Original Message-----
From: spatiali...@googlegroups.com <spatiali...@googlegroups.com> On Behalf Of Brad Hards
Sent: Wednesday, July 10, 2024 2:18 AM
To: spatiali...@googlegroups.com
Subject: Re: [SpatiaLite-Users] Using Spatialite within C++ - locating .lib and .h files

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/12535850.O9o76ZdvQC%40audax.

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

On Tue, 9 Jul 2024 20:52:08 +0100, ckgoo...@gmail.com wrote:
> I downloaded the Windows AMD64 binaries and put the folder on my C:
> drive and added this path to my environment variable.
>
> Can anyone give me suggestions on what I am doing wrong to get an
> error code of 1 (generic error)?
> sqlite3* m_DB;
> int rc = sqlite3_open( "coastlinesSpatial.db", &m_DB);
>
> //Create a call to spatial library to load it
> string sql = "SELECT load_extension('mod_spatialite');";
> char* messaggeError;
> rc = sqlite3_exec(m_DB, sql.c_str(), nullptr, 0, &messaggeError);
>

Hi Chris,

to begin with I have to make some observations about your C++ code;
It seems that you ignore the contents of the messageError string
which could instead give you useful information on the cause of
the error.
even worse: that variable points to a dynamic memory area.
if you don't release it you are causing a memory leak.

short conclusion: you are always required to implement an
appropriate error handler after each call to SQLite,
something like this:

if (rc != SQLITE_OK)
{
fprintf(stderr, "%s\n", messageError);
sqlite3_free(messageError);
}

----------------------------------------

Coming to the main problem: dynamically loading
mod_spatialite implicitly requires loading any other
depending DLL, and they are about a dozen.
naturally they must all be of the same type as the
main app; if this one is 64 bit all the DLLs must
also be 64 bit.
missing just a single DLL causes overall failure.

C/C++ diagnostics are practically useless, because
they only tell you that there is a problem somewhere,
but it doesn't tell you what it really is.

in all these cases it's always useful to use
sqlite3.exe in the same environment so to check
that everything works well.
in case of errors it should give you richer diagnostic
messages making the identification of missing DLLs less
painful.

----------------------------

note #1: it could be a lack of authorization issue; please see:
https://www.sqlite.org/c3ref/enable_load_extension.html

bye Sandro
note #2:

[ckgoo...@gmail.com]

Hi Sandro,
Thanks for your email. I'm just leaving on a vacation and will look at your advice when I get back. I just didn't want you to think I was ignoring you.

Best, Chris

-----Original Message-----
From: spatiali...@googlegroups.com <spatiali...@googlegroups.com> On Behalf Of a.fu...@lqt.it

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/31e5d033ac93e0174ea0cf1049ac5877%40lqt.it.

[ckgoo...@gmail.com]

Hello Sandro,
I've been able to give the advice you gave below a try. And success, it was a permissions problem. When I make a call to:
rc = sqlite3_enable_load_extension(m_DB, 1 );
Then I don't get an error return code. Many thanks.
However, the documentation suggests to use:

" Security warning: It is recommended that extension loading be enabled using the SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION method rather than this interface, so the load_extension() SQL function remains disabled. This will prevent SQL injections from giving attackers access to extension loading capabilities."


So I make call:
int state = 0;
rc = sqlite3_db_config( m_DB, SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, 1, &state);
which returns SQLITE_OK but the subsequent call to load the extension fails. And reading the error message returned is the same as before - no permission.
Can you see where I might be going wrong?

Best, Chris


-----Original Message-----
From: spatiali...@googlegroups.com <spatiali...@googlegroups.com> On Behalf Of a.fu...@lqt.it

Sent: Friday, July 12, 2024 6:22 AM
To: spatiali...@googlegroups.com

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/31e5d033ac93e0174ea0cf1049ac5877%40lqt.it.

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

Hi Chris,

IT security is very important, but it must not
become overly obsessive.

there is subtle difference between the two - API:

sqlite3_enable_load_extension() enables or disables
both the C-API sqlite3_load_extension() and the SQL
function load_extension().

sqlite3_db_config() instead allows to enable only
the C-API sqlite3_load_extension() BUT NOT the SQL
function load_extension().
it's certainly a safer approach, because the SQL
statement load_extension() could open the door
to malicious SQL-injection attacks.

Conclusion: if you want to maintain a very high
level of security then you have to load the
SpatiaLite extension by calling the C-API
sqlite3_load_extension() instead of calling
the SQL statement load_extension().

I personally would avoid an excessively paranoid
security approach and would be happy to use the
simpler C-API sqlite3_enable_load_extension()

bye Sandro

[ckgoo...@gmail.com]

Thanks Sandro. I'll stick with the simpler one that works.

I'm now starting my journey to discover how Spatialite works by reading the tutorial. I am a C++ programmer and I've done SQL in the past so I hope it will be not too difficult. I need to learn how to convert the OSM water polygon shapefiles into Spatialite so I can quickly test if a coordinate is in the sea or not. Other things I will want to do are to convert OSM data into Spatialite and query what admin area I am in, what town or cities are nearby to my user's coordinates and I am sure lots of other things. Quite a long road but I think Spatialite is the right tool for me.

Best, Chris

-----Original Message-----
From: spatiali...@googlegroups.com <spatiali...@googlegroups.com> On Behalf Of a.fu...@lqt.it
Sent: Sunday, July 28, 2024 6:25 PM
To: spatiali...@googlegroups.com
Subject: RE: [SpatiaLite-Users] Using Spatialite within C++ - locating .lib and .h files

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/45640dc823249f97e5c7e5a7ead7a38f%40lqt.it.

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

On Sun, 28 Jul 2024 21:30:17 +0100, ckgoo...@gmail.com wrote:
> Thanks Sandro. I'll stick with the simpler one that works.
>
> I'm now starting my journey to discover how Spatialite works by
> reading the tutorial. I am a C++ programmer and I've done SQL in
> the
> past so I hope it will be not too difficult. I need to learn how to
> convert the OSM water polygon shapefiles into Spatialite so I can
> quickly test if a coordinate is in the sea or not. Other things I
> will
> want to do are to convert OSM data into Spatialite and query what
> admin area I am in, what town or cities are nearby to my user's
> coordinates and I am sure lots of other things. Quite a long road
> but
> I think Spatialite is the right tool for me.
>

Hi Chris,

just a few very general suggestions to make your way easier.

1. force yourself to get into the habit of writing as much
of SQL code and as little C++ as possible

2. ideally the C++ code should be limited to being a thread
that stitches the various SQL queries together

3. Typical tasks to do in C++:
* generating the text of SQL queries
* executing such queries fetching data from the
returned resultset.
* displaying the results on the screen

4. Prepared Statements should be studied very well because
they are a truly powerful tool.
https://www.sqlite.org/c3ref/stmt.html

5. Don't neglect Parametric Queries and the sqlite3_bind_xxx()
C-APIs because they are absolutely fundamental for
efficiently embedding easily reusable SQL queries
in the body of C++ functions.

6. take all the time you need to familiarize yourself with
the over 500 SQL Spatial functions supported by SpatiaLite.
https://www.gaia-gis.it/gaia-sins/spatialite-sql-5.1.0.html

The last and most fundamental of all pieces of advice.
Never give in to the temptation to write your SQL queries
directly inside your C/C++ code, because then debugging
could easily turn out to be a real nightmare.
Instead, get into the healthy habit of testing every single
query in a pure SQL environment, and then embed your SQL code
in C/C++ only after having thoroughly tested and tuned it.

Finally, just a few words on how to obtain fast and snappy
applications.
Writing overly complex SQL queries is always a recipe
for disaster.
Breaking a complex problem into many small, very simple
elementary problems is always the secret of speed.
Learn to intelligently use TEMPORARY TABLES as a parking
area where storing partial results and then assemble
everything together only in the last final step.

Good luck, and above all have fun :-D

bye Sandro