SQLite 3 - "SQL logic error or missing database"

[trob]

Hello

I use Zeos 7.1.2 with Delphi XE2 for reach my SQLite 3 database.

I attach the database.

I use the following code on a ZQuery to update an exist table:

Code: Select all

ZQexec.SQL.Text:='UPDATE :tabla SET zarol=:zarolset WHERE zarol=:zarolwhere AND id=:id';
ZQexec.ParamByName('tabla')     .AsString  := tabla;
ZQexec.ParamByName('id')        .AsInteger := id;
ZQexec.ParamByName('zarolset')  .AsInteger := Ord(zarole);
ZQexec.ParamByName('zarolwhere').AsInteger := Ord(Not zarole);
ZQexec.ExecSQL;

I think everything is right, but i get the message

SQL logic error or missing database

when the ExecSQL is fired.

Please help me

[majlumbo]

I think the issue is that you are using a parameter for the table name value. I believe that is not allowed.

[trob]

Oooooouuuuuuhhh. Now it works. Thank you.

But why is tablename not allowed to set by a parameter?

[trob]

One more question:

Why does "LIMIT 1" at the end of the upper statement (with corrected table name part) cause the same error?

[majlumbo]

why is tablename not allowed to set by a parameter?

I'm sure there's a reason, but I don't know of any connection component that allows table name value to be a supplied by a parameter.

Why does "LIMIT 1" at the end of the upper statement (with corrected table name part) cause the same error?

The statement you have listed is an UPDATE SQL statement and executed with .EXECSQL (which does not return a result set). "Limit" is only relevant for SELECT statements, that is opened using the .OPEN or .Active := True; statements.

[trob]

Why does "LIMIT 1" at the end of the upper statement (with corrected table name part) cause the same error?

The statement you have listed is an UPDATE SQL statement and executed with .EXECSQL (which does not return a result set). "Limit" is only relevant for SELECT statements, that is opened using the .OPEN or .Active := True; statements.

The document of the SQLite's UPDATE says i can use LIMIT in UPDATE too.
http://www.sqlite.org/lang_update.html
I would like to say that if it has found the record then it does not need to seek the whole table.

[majlumbo]

Had not seen that before, but none-the-less, if you follow the link you get this

SQLITE_ENABLE_UPDATE_DELETE_LIMIT

This option enables an optional ORDER BY and LIMIT clause on UPDATE and DELETE statements.

If this option is defined, then it must also be defined when using the 'lemon' tool to generate a parse.c file. Because of this, this option may only be used when the library is built from source, not from the amalgamation or from the collection of pre-packaged C files provided for non-Unix like platforms on the website.

so it all depends on whether you compiled the library with the proper define enabled.

Also, SQL is set based. If you have the proper indexes established on your data, then the engine is not continuing to "look" for any matching records. Looks like this option is used only to limit the number of records it will update/delete, not how many records it will find. Again, from the link provided:

If an UPDATE statement has a LIMIT clause, the maximum number of rows that will be updated is found by evaluating the accompanying expression and casting it to an integer value. A negative value is interpreted as "no limit".

If the LIMIT expression evaluates to non-negative value N and the UPDATE statement has an ORDER BY clause, then all rows that would be updated in the absence of the LIMIT clause are sorted according to the ORDER BY and the first N updated. If the UPDATE statement also has an OFFSET clause, then it is similarly evaluated and cast to an integer value. If the OFFSET expression evaluates to a non-negative value M, then the first M rows are skipped and the following N rows updated instead.

If the UPDATE statement has no ORDER BY clause, then all rows that would be updated in the absence of the LIMIT clause are assembled in an arbitrary order before applying the LIMIT and OFFSET clauses to determine which are actually updated.

The ORDER BY clause on an UPDATE statement is used only to determine which rows fall within the LIMIT. The order in which rows are modified is arbitrary and is not influenced by the ORDER BY clause.