[GRASS-dev] [GRASS GIS] #3397: db.select - reading multiline SQL statements from file fails

[GRASS GIS]

#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>