I have some problems using the "Execute SQL" algorithm (in General Vector Tool) in the Processing Toolbox. No problem when I've got only 1 input table, but when I try to select 2 tables as input data I get an error: virtual: Referenced table input2 in query not found!

For background information my SQL expression (works fine with add virtual layers):

select makepoint((st_line_locate_point(p.geometry, a.geometry)*st_length(a.geometry)) /*:real*/,"Z" /*:real*/,2154) as geometry
from 'input1' as a, 'input2' as p
  • I can't find the tool 'execute SQL' anywhere in the Vector menu... – DPSSpatial Nov 10 '16 at 17:16
  • 1
    @DPSSpatial It's in Toolbox -> GDAL/OGR -> [OGR] Miscellaneous -> Execute SQL. At least in 2.14 and 2.16 it is. – Phil G Nov 10 '16 at 17:31
  • when making use of toolbox -> qgis algorithms -> vector general tools -> execute sql then I can select multiple layers and use all of them in the sql-expression – PieterB Nov 16 '16 at 8:20
  • @PieterB did you use input1 and input2 for calling your first and second entries ? or 'input1' and 'input2' ? – sylvain badey Nov 16 '16 at 8:53
  • 1
    You have to refer to the name of the layer. Not input1, input2, ... eg: select makepoint((st_line_locate_point(p.geometry, a.geometry)*st_length(a.geometry)) /*:real*/,"Z" /*:real*/,2154) as geometry from layerA as a, layerB as p – PieterB Nov 16 '16 at 9:09

This problem is due to a bug in the "ExecuteSQL.py" script, where an index wasn't being incremented (see the bug report here). That bug report says the issue has been resolved, so I'm not sure why it's still happening on my machine but the fix itself was easy enough to do by hand, following the fix shown here.

Basically it involves adding the following line to "ExecuteSQL.py" after line 106:

        layerIdx += 1

I just made the change, tried it and it worked.

  • I added the line in ExecuteSQL.py in QGIS 2.18.28, but an python error appears when loading Qgis layerIdx += 1 IndentationError: unexpected indent – Leehan Mar 4 '19 at 8:49
  • Looks like you don't have the correct number of spaces at the front of your line. This matters because indentation is how Python keeps track of nesting and loops etc. – Don Mar 4 '19 at 15:30
  • so obvious...Thanks. But the issue is before that : in the graphic modeler ExecuteSQL can't use a layer previously generated with points layer from table. It is a part of a bug that is not fixed so far according to your link (?). – Leehan Mar 5 '19 at 12:53
  • @Leehan for the Graphical Modeler you might want to look at this question. The bug report I referenced was reopened about three months ago, because of problems with the Graphical Modeler. I just tried using ExecuteSQL: it works from the Processing Toolbox, but not within the Graphical Modeler, so maybe these problems require different fixes. – Don Mar 5 '19 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.