[fpc-pascal] EDatabaseError on SELECT

[Michael Van Canneyt]

On Wed, 10 Feb 2016, Felipe Monteiro de Carvalho wrote:

> Hello,
>
> I'm posting here because this seams to be an issue with using sqldb,
> my SQL seams to be ok.
>
> I create a table and add a row to it, but when trying to read that
> same row using SELECT I get EDatabase error with message "Syntax error
> near SELECT".

That means the engine throws an error, this is not SQLDB throwing an error.

>
> I create the Table and add a row to it like this:
>
>  FSQLConn := TSQLite3Connection.Create(nil);
>  FSQLTran := TSQLTransaction.Create(nil);
>  FQuery := TSQLQuery.Create(nil);
>  FQuery.DataBase := FSQLConn;
>  FQuery.Transaction := FSQLTran;
> .........
>  lTableName := GetTableName(AListNr, dtkHeader);
>  lSQL := Format('CREATE TABLE "%s"( "ID" INTEGER,'+
>    ' "Block_NrOfItems" INTEGER, "Block_ParentList" INTEGER,'+
>    ' "Block_IndexIn_ParentList" INTEGER, "Block_Kind" INTEGER,'+
>    ' "Block_Name" TEXT);', [lTableName]);
>  FSQLConn.ExecuteDirect(lSQL);
>
>  lSQL := Format('INSERT INTO %s (ID, Block_NrOfItems, Block_ParentList,'+
>    ' Block_IndexIn_ParentList, Block_Kind, Block_Name)'+
>    ' VALUES (%u, %u, %u, %s, %u, ''%s'')',
>    [lTableName, UINT32_ID_SINGLE_ROW_TABLE,
>     AData.NrOfItems, AData.ParentList,
>     IntToStr(AData.IndexIn_ParentList),
>     AData.Kind, AData.Name]);
>  FSQLConn.ExecuteDirect(lSQL);
>  FSQLTran.Commit;
>
> Here is the SELECT with error:
>
>  lTableName := GetTableName(AListNr, dtkHeader);
>  lSQL := Format('SELECT * FROM "%s" WHERE ID=%u', [lTableName,
> UINT32_ID_SINGLE_ROW_TABLE]);
>  FQuery.SQL.Add(lSQL);

Did you try clearing SQL first ?

Michael.