[Interest] Use SQLite math functions (-DSQLITE_ENABLE_MATH_FUNCTIONS) like sin and cos?

[Tony Rietwyk]

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 at qt-project.org
> https://lists.qt-project.org/listinfo/interest
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.qt-project.org/pipermail/interest/attachments/20221025/2469c11b/attachment.htm>