I have a bunch of maps in the same mapset with identical table structure
and with similar sounding names. With v.to.db I've uploaded a distance to
each of their 'length' columns.
The DB is SQLite and everything is stored in $MAPSET/sqlite.db.
Now I want to print them all out in a single command.
I tried this, but it doesn't work:
G65> db.select sql="SELECT length FROM table_name LIKE 'track_%'"
DBMI-SQLite driver error:
Error in sqlite3_prepare():SELECT length FROM table_name LIKE 'track_%'
near "'track_%'": syntax error
this works:
db.select sql="SELECT length FROM track_001 \
UNION SELECT length FROM track_002 \
UNION SELECT length FROM track_003"
but I have something like 100 maps and the statement would get too huge.
The DB is SQLite and everything is stored in $MAPSET/sqlite.db.
G65> db.select sql="SELECT length FROM table_name LIKE 'track_%'"
Hamish,
The problem is with SQL syntax; at least with SQLite. As Rick van der Lans
notes in his "The SQL Guide to SQLite", "Because this literal comes after a
LIKE operator and not after a comparison operator, two characters, the
percentage sign and the underscore, have a special meaning. Such a literal
is called a pattern or a mask. In a pattern, the percentage sign stands for
zero, one, or more characters. The underscore stands for exactly one random
character."
Therefore, when you specify the individual name it works, but in the above
you're asking for exactly one character followed by zero or more characters.
> G65> db.select sql="SELECT length FROM table_name LIKE 'track_%'"
DBMI-SQLite driver error:
Error in sqlite3_prepare():SELECT length FROM table_name LIKE 'track_%'
near "'track_%'": syntax error
Rich wrote:
... "Because this literal comes after a LIKE operator and not
after a comparison operator, two characters, the percentage
sign and the underscore, have a special meaning. Such a literal
is called a pattern or a mask. In a pattern, the percentage
sign stands for zero, one, or more characters. The underscore
stands for exactly one random character."
Therefore, when you specify the individual name it
works, but in the above you're asking for exactly one character
followed by zero or more characters.
which is exactly what I want, a wildcard match track_001,
track_002, etc. The underscore is along for the ride in this
case, but escaping chars is a pain in sql and in this case I
know it is not likely to harm.
which is exactly what I want, a wildcard match track_001, track_002, etc.
The underscore is along for the ride in this case, but escaping chars is a
pain in sql and in this case I know it is not likely to harm.
what is the syntax error?
Hamish,
The sql engine is confused with the underscore followed by the percent
sign. They are two different wildcard characters, as are the regex '?' and
'*'.
If you write the statement as "...LIKE 'track%'" the underscore becomes
one of the matched characters and not a pattern matching wildcard.
I've never tried using both wildcards in the same pattern so I may well be
totally off the solution here.
I have a bunch of maps in the same mapset with identical table structure
and with similar sounding names. With v.to.db I've uploaded a distance to
each of their 'length' columns.
The DB is SQLite and everything is stored in $MAPSET/sqlite.db.
Now I want to print them all out in a single command.
I tried this, but it doesn't work:
G65> db.select sql="SELECT length FROM table_name LIKE 'track_%'"
DBMI-SQLite driver error:
Error in sqlite3_prepare():SELECT length FROM table_name LIKE 'track_%'
near "'track_%'": syntax error
this works:
db.select sql="SELECT length FROM track_001 \
UNION SELECT length FROM track_002 \
UNION SELECT length FROM track_003"
but I have something like 100 maps and the statement would get too huge.
Tough
SQL doesn't allow you to perform an "implicit union" on multiple
tables. You need to perform an explicit union, and there isn't any
kind of "wildcard union" construct; IOW, you need an occurence of the
SELECT keyword for each table.
If the statement is too large for db.select's sql= option (or for the
underlying driver), you can use a hierarchical approach, creating
intermediate results from as many tables as can be merged at a time
with "CREATE TABLE AS SELECT ...", then merging the intermediate
results. Or you can use an iterative approach, starting with an empty
table then executing e.g. "INSERT INTO result SELECT length FROM ..."
for each table in turn.
> this works:
>
> db.select sql="SELECT length FROM track_001 \
> UNION SELECT length FROM track_002 \
> UNION SELECT length FROM track_003"
>
> but I have something like 100 maps and the statement
> would get too huge.
Glynn wrote:
Tough
SQL doesn't allow you to perform an "implicit union" on
multiple tables. You need to perform an explicit union,
and there isn't any kind of "wildcard union" construct;
IOW, you need an occurence of the SELECT keyword for each
table.
bummer. oh well, v.patch it is then.
examples found via web searches seemed to indicate that this
was possible.
Rich:
If you write the statement as "...LIKE 'track%'" the underscore
becomes one of the matched characters and not a pattern matching
wildcard.
