[QGIS-Developer] Unexpected results from GPKG table query using executeSql()

[Alessandro Pasotti]

Hi Raymond,

I'll have a look but keep in mind that for GPKG the implementation uses
GDAL/OGR to execute the queries, there might be some fid/id handling that
messes up with the returned fields.



On Fri, Apr 1, 2022 at 8:28 AM Raymond Nijssen <r.nijssen at terglobo.nl>
wrote:

> For a workaround I found that selecting this way:
>
> SELECT
>    edit_id + 0 AS edit_id,
>    timestamp,
>    name
> FROM
>    ...
>
> The +0 seem to makes QGIS (or GDAL?) unaware of edit_id being the PK
> field and it does return it. Works both in 3.10 and 3.25.
>
> Raymond
>
>
>
> On 31-03-2022 18:15, Alessandro Pasotti wrote:
> > Hi Raymond,
> >
> > your code looks good.
> >
> > Can you provide a test file?
> >
> > There are some test in core here:
> >
> https://github.com/qgis/QGIS/blob/master/tests/src/python/test_qgsproviderconnection_ogr_gpkg.py
> > <
> https://github.com/qgis/QGIS/blob/master/tests/src/python/test_qgsproviderconnection_ogr_gpkg.py
> >
> >
> >
> > On Thu, Mar 31, 2022 at 6:09 PM Raymond Nijssen via QGIS-Developer
> > <qgis-developer at lists.osgeo.org <mailto:qgis-developer at lists.osgeo.org>>
>
> > wrote:
> >
> >     Hi devs,
> >
> >     I'm working on a plugin that connects to a custom table (without
> >     geometry) in a GPKG. When I'm sending queries to it, I keep on
> getting
> >     unexpected results. Somehow the id field is missing, sometimes it
> >     appears twice (!). And this seems to be different between QGIS 3.10
> and
> >     3.25. (I think it changed since 3.22).
> >
> >     Here is a piece of my code:
> >
> >
> >     fn = '/path/to/test.gpkg'
> >     md = QgsProviderRegistry.instance().providerMetadata('ogr')
> >     conn = md.createConnection(fn, {})
> >
> >     q = 'select * from test_table;'
> >     qr = conn.executeSql(q)
> >     print(qr) # No id field in result
> >
> >
> >
> >     Before diving into the QGIS code I'd like to check if this is the
> >     way to
> >     go. Or should i use another way to query that GPKG (SQLite) db?
> >
> >
> >     Kind regards,
> >     Raymond
> >     _______________________________________________
> >     QGIS-Developer mailing list
> >     QGIS-Developer at lists.osgeo.org <mailto:
> QGIS-Developer at lists.osgeo.org>
> >     List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
> >     <https://lists.osgeo.org/mailman/listinfo/qgis-developer>
> >     Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
> >     <https://lists.osgeo.org/mailman/listinfo/qgis-developer>
> >
> >
> >
> > --
> > Alessandro Pasotti
> > QCooperative: www.qcooperative.net <https://www.qcooperative.net>
> > ItOpen: www.itopen.it <http://www.itopen.it>
>


-- 
Alessandro Pasotti
QCooperative:  www.qcooperative.net
ItOpen:   www.itopen.it
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20220401/ca682339/attachment-0001.html>