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

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);
  FQuery.Open; // <- EDatabaseError

I tested with sqlite3 command line and the same statement issues no syntax error ...

Any ideas???

lTableName = Header_0
UINT32_ID_SINGLE_ROW_TABLE=0

thanks,

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

Did you try clearing SQL first ?

Michael.

Michael Van Canneyt wrote

Did you try clearing SQL first ?

What do you mean?

Result in sqlite3 console:

sqlite> SELECT * FROM "Header_0" WHERE ID=0;
0|0|0|0|18|

You used sql.add. Did you do sql.clear first ?

Otherwise you'll be sending 2 sql statements as 1.

Michael.

Michael Van Canneyt wrote

You used sql.add. Did you do sql.clear first ? Otherwise you'll be sending 2 sql statements as 1.

ok, that fixed the issue, thanks!

Did you output the iSQL string to a console or a text file or even a message box to verify that what you're passing in is actually what you expect?

If the engine is coming back with a problem "Near Select" then there is something wrong either before the string, or after Select. GetTableName may be returning something the query isn't expecting.

On Wed, Feb 10, 2016 at 10:00 AM, Felipe Monteiro de Carvalho <

Nevermind... Ninja'd.

Hello,

Taking advantage that we are already talking about SQL .... how to upload the contents of a blob field?

I tried using a SELECT to get the field +
TBlobField(FQuery.FieldByName('Data')).LoadFromStream but it raises an exception that "Dataset is not in insert or edit mode"

I see that I could write the data in hexadecimal in the SQL query, but that would be slow...

So what is the proper way to use TBlobField.LoadFromStream?

Or should I use params + an SQL statement with question mark:

lSQL := Format('UPDATE %s SET ? WHERE ID=''%d''', [lTableName, AItemNr]);

?

thanks,

Both ways are possible.

Qry.sql.text:='your select';
Qry.Open;
// Navigate to record you want to modify
Qry.Edit; // Go to edit mode.
TBlobField(FQuery.FieldByName('Data')).LoadFromStream(YourStream))
Qry.Post; // Save changes

Or

Qry.Sql.Text:='update tablename set (yourfield=:yourfield) where ID=something';
Qry.ParamByName('yourfield').LoadFromStream(YourStream)
Qry.ExecSQL;

Michael.