D3/3: execute: SELECT cat FROM MRR1_09_marked_csv_lines_id
DBMI-SQLite driver error:
Error in sqlite3_step():
unknown error
ERROR: Error while executing: 'SELECT cat FROM MRR1_09_marked_csv_lines_id
'
But within "sqlite3", the statement works:
sqlite3 $HOME/grassdata/patGB1/fra_sqlite/sqlite.db
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT cat,count(length) FROM MRR1_09_marked_csv_lines_id
GROUP BY length;
23|23
39|16
90|29
...
sqlite>
But I thought that both did essentially the same job? Apparently not... I wonder
if we could trap "SELECT" in db.execute and point the user to db.select.
?
But I thought that both did essentially the same job? Apparently not...
No, db.select is only for selection and db.execute is for any other db manipulations (update, delete, insert, create table, etc). They use different functions to interact with the DB. Don't know what Radim's reasoning was behind this, but I guess he had a good one :-).
I wonder
if we could trap "SELECT" in db.execute and point the user to db.select.
?
That's not as easy as it sounds, since you might have SELECT clauses in UPDATE and other db.execute clauses (e.g. INSERT INTO mytable SELECT x, y, z FROM othertable). So the trapping would have to be a bit more sophisticated, at least looking out for a SELECT at the beginning of the statement. But definitely possible. Probably best done right before the
ret = db_execute_immediate(driver, &stmt);
at
line 74 of db/base/execute.c (grass6)
or
line 75 of /db/db.execute/main.c (grass_trunk)
But I thought that both did essentially the same job?
nope.
Apparently not... I wonder if we could trap "SELECT" in
db.execute and point the user to db.select.
?
I recall that Radim gave a good explanation about why they had
to be separated; probably to be found in the ML archives near to
the time when this was added to the db.execute man page:
"""
NOTES
db.execute only executes SQL statements and does not return any data. If you need
data returned from the database, use db.select.
"""
>> s/db.execute/db.select
>
> Cool.
>
> But I thought that both did essentially the same job? Apparently not...
No, db.select is only for selection and db.execute is for any other db
manipulations (update, delete, insert, create table, etc). They use
different functions to interact with the DB. Don't know what Radim's
reasoning was behind this, but I guess he had a good one :-).
A select command returns data; the others return only a status
indication. This is typically reflected in the underlying API (libpq,
sqlite, etc) having different interfaces for queries (select
statements) and other statements.
I wonder
if we could trap "SELECT" in db.execute and point the user to db.select.
?
That's not as easy as it sounds, since you might have SELECT clauses in
UPDATE and other db.execute clauses (e.g. INSERT INTO mytable SELECT x,
y, z FROM othertable). So the trapping would have to be a bit more
sophisticated, at least looking out for a SELECT at the beginning of the
statement.
Even that would not work as some RDMS (e.g. PostgreSQL) allow a syntax such as
SELECT * INTO table2 FROM table1
(equivalent to CREATE TABLE table2 AS SELECT * FROM table1)
which is a db.execute statement...
Not sure that there is a better solution than the hint in the NOTES section of the man page Hamish pointed to.