#3397: db.select - reading multiline SQL statements from file fails
--------------------------+-----------------------------------
Reporter: maitl | Owner: grass-dev@…
Type: defect | Status: new
Priority: normal | Milestone: 7.4.0
Component: Database | Version: 7.2.1
Resolution: | Keywords: db.select, db.execute
CPU: Unspecified | Platform: All
--------------------------+-----------------------------------
Changes (by mlennert):
* milestone: 7.2.2 => 7.4.0
Comment:
Replying to [ticket:3397 maitl]:
> It is convenient to store longer SQL statements into file.
>
> If each statement is not exactly one line in input file, then db.select
ends with error.
>
This is due to the fact that db.select reads the input file line by line,
and then uses all text before a semi-colon as the SQL statement.
This means that with a file test.sql that contains
{{{
SELECT count(*) FROM boundary_county WHERE Z_MEAN > 38;
SELECT count(*) FROM boundary_county;
}}}
you get
{{{
count(*)
333
count(*)
926
}}}
while if the file contains
{{{
SELECT count(*) FROM boundary_county WHERE Z_MEAN > 38; SELECT count(*)
FROM boundary_county;
}}}
you only get
{{{
count(*)
333
}}}
The same happens when you do this:
{{{
echo "SELECT count(*) FROM boundary_county WHERE Z_MEAN > 38; SELECT
count(*) FROM boundary_county;" | db.select input=-
}}}
and I cannot integrate a newline ('\n') character:
{{{
echo "SELECT count(*) FROM boundary_county WHERE Z_MEAN > 38\n; SELECT
count(*) FROM boundary_county;" | db.select input=-
DBMI-SQLite erreur de pilote :
Error in sqlite3_prepare():
SELECT count(*) FROM boundary_county WHERE Z_MEAN > 38\n; SELECT count(*)
FROM boundary_county;
unrecognized token: "\"
}}}
A rewrite of this to more flexibly parse the SQL, be it with one statement
over several lines, or several statements on one line would be nice, but I
would consider this too invasive to go into 7.2.2.
So, I propose that for 7.2.2 we just amend the manual. I've taken the
liberty to add one sentence about that in trunk and release72 (r71389 and
r71390).
--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3397#comment:2>
GRASS GIS <https://grass.osgeo.org>