db.select - reading multiline SQL statements from file fails

[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.

[mlennert]

Replying to 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).

[mlennert]

Replying to mlennert:

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: "\"

Sorry, the '\n' was not well placed. It should have been

echo "SELECT count(*) FROM boundary_county WHERE Z_MEAN > 38;\n SELECT count(*) FROM boundary_county;" | db.select input=- database=GRASS/GRASSDATA/NC_spm_temporal_workshop/PERMANENT/sqlite/sqlite.db 
count(*)
333

So, no error, but the query on the second line does not get executed...

[mmetz]

Replying to mlennert:

Replying to mlennert:

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: "\"

Sorry, the '\n' was not well placed. It should have been

echo "SELECT count(*) FROM boundary_county WHERE Z_MEAN > 38;\n SELECT count(*) FROM boundary_county;" | db.select input=- database=GRASS/GRASSDATA/NC_spm_temporal_workshop/PERMANENT/sqlite/sqlite.db 
count(*)
333

So, no error, but the query on the second line does not get executed...

You need to use echo -e (enable interpretation of backslash escapes).

About the main issue, we could introduce a new flag, e.g. -m, to allow multiline statements: only a semicolon, not a newline would mark the end of a statement.

[neteler]

Ticket retargeted after milestone closed

[neteler]

Ticket retargeted after milestone closed

[martinl]

Bump milestone to 7.4.4

[neteler]

Ticket retargeted after milestone closed