Hi Oliver,
Can't answer your broader question, but I suspect the reason you are
seeing "No query Unable to fetch row" - error code: "" is because you
are not checking for an error after the prepare. Hopefully, you will
then the expected 'no such function' error and can act on it accordingly.
Regards,
Tony
On 25/10/2022 4:58 am, Till Oliver Knoll wrote:
Dear all,
What is the least complicated way to enable the math functions that
come with SQLite, when it is compiled with the
-DSQLITE_ENABLE_MATH_FUNCTIONS switch?
My empirical experiments with the binary Qt 5.15.2 (open source)
release (but also some tests with the lastest Qt 6.4 binary release,
for either Windows or macOS) suggest that the SQLite plugin that ships
with Qt does not enable those "built in mathematical functions", as
described here:
https://www.sqlite.org/lang_mathfunc.html
So do I:
* Recompile the Qt SQLite plugin, with the desired
-DSQLITE_ENABLE_MATH_FUNCTIONS flag?
o Disadvantages (?):
+ Need to download Qt source code
+ Probably complicates the build process... (I have "build
actions" on github.com that currently "only" need to
install the binary Qt distribution)
+ Feels a bit "overkill"
* Try to get the "SQLite DB handle" and add my own sin(), cos() and
power() functions?
o As described here:
https://forum.qt.io/topic/71210/sqlite-user-define-functions/3
+ Essentially "QSqlDatabase::driver->handle()", some voodoo
casting and using sqlite3_create_function()
o Advantages:
+ Probably can be achieved using the "stock" SQLite plugin
that ships with Qt, "programatically"
o Disadvantages (?):
+ Still need to get hold of some SQLite header (for
sqlite_create_function etc.)
+ Fragile? What if the header mismatches (in the future)
with the shipped Qt SQLite plugin?
# Feels "hacky"...
* Any other "trick" to add those "math extensions" to an existing Qt
SQLite plugin?
The second option seems more attractive than the first, but
nevertheless a bit "fragile" (and I have to add functions that would
essentially "already be in SQLite" (if enabled & compiled)), so feels
like "re-inventing the wheel"...
Some more details: specifically I am trying to execute a query like:
*select power(l.latitude - 48.0, 2) + power((l.longitude - 8.0) *
cos(radians( 48.0 )), 2) dist ****from location l;*
(Eventually I want to find locations given by their latitude and
longitude that are within a certain distance of my given location:
https://jonisalonen.com/2014/computing-distance-between-coordinates-can-be-simple-and-fast/)
However when compiled into my Qt 5.15.2 application with some current
"test code" like:.
QSqlQueryquery1;
query1.setForwardOnly(true);
query1.prepare(
"selectpower(l.latitude-48.0,2)+power((l.longitude-8.0)*cos(radians(48.0)),2)dist"
"from location l;"
);
constboolsuccess=query1.exec();
if(success){
...
}else{
qDebug()<<"SQL error:"<<query1.lastError().text()<<"- error
code:"<<query1.lastError().nativeErrorCode();
}
I get:
SQL error: "No query Unable to fetch row" - error code: ""
When executing the equivalent query in the Qt example app "Qt SQL
Browser" (this time compiled with Qt 6.4) (which provides a Movies and
Names table):
select power(l.latitude - 48.0, 2) + power((l.longitude - 8.0) *
cos(radians( 48.0 )), 2) dist from movies l;
then I get a more meaningful (and expected) error message:
"no such function: power Unable to exectute statement"
Conclusion: "No math support in the shipped SQLite plugin, neither in
Qt 5.15.2 (Windows nor macOS) nor Qt 6.4 (again tried both Windows and
macOS).
Now when I execute my first query in the app "DB Browser for SQLite
(https://sqlitebrowser.org/), against my own database schema (with a
"location" table) again:
select power(l.latitude - 48.0, 2) + power((l.longitude - 8.0) *
cos(radians( 48.0 )), 2) dist from location l;
I intially also get "no such function: power". However DB Browser for
SQLite provides the possibility to add "extensions", such as (on macOS);
/Applications/DB Browser for SQLite.app/Contents/Extensions/math.dylib
(To be registered via Preferences or loaded via Tool / Load Extension...)
Now with this "math" extension loaded (I assume this is some DB
Browser for SQLite specific plugin / extension) my query works as
expected (even with the somewhat older SQLite 3.35.x). So this goes to
illustrate that my query is not completely wrong (syntactically)...
So any ideas how to "enable math support" in either the shipped Qt
SQLite plugin, and/or how to (re-)compile that plugin with the least
effort, or any other tricks how to make use of "sin, cos, power and
friends" in SQLite are very welcome. Interestingly I did not find any
such questions in the Qt interest forums, and some stackoverflow
answers seem indeed to suggest to use sqlite3_create_function()
Thanks,
Oliver
_______________________________________________
Interest mailing list
Interest@qt-project.org
https://lists.qt-project.org/listinfo/interest
_______________________________________________
Interest mailing list
Interest@qt-project.org
https://lists.qt-project.org/listinfo/interest
