Delete with conditon problem

[iancoullie]

Hi All
Im using 7.2.4-stable with Delphi Berlin starter 10.1
Code:
DM.ZConnection1.Connected := true;
DM.InfoSQL.SQL.Clear;
DM.InfoSQL.SQL.Add('DELETE FROM aInfo WHERE SUPPLIER_NAME = ''Lovato''');
DM.InfoSQL.ExecSQL;
// DM.InfoSQL.Active := true;

If i leave out the WHERE condition the delete works and emptys the table

with the WHERE condition I get the following error
SQL Error: SQL logic error or missing database.

if I run the 'DELETE FROM aInfo WHERE SUPPLIER_NAME = 'Lovato'
in SQLStudio it works

Ive tried the DM.InfoSQL.Active := true; and get the same error
DM.InfoSQL connection is set in its properties to DM.ZConnection1
All other values on the connection and Query component are default.

Thanks in advance for any advice

[marsupilami]

Hello

Which database do you use? Currently I am guessing SQLite. If it is SQLite it might make sense to try Zeos 7.3 SVN because it has better error reporting for SQLite than Zeos 7.2. You can also try Zeos 7.2 from SVN and set the parameter ExtendedErrorMessage to 1 or Yes or true to get the extended error information.

[iancoullie]

Hi marsupilami

Thanks for that. Yes its SQLite. Ill upgrade and see what happens. Much appreciated.

[iancoullie]

Hi marsupilami

The extended error message is

SQL Error: Error: SQL logic error or missing database

Message: no such collation sequence: SYSTEMNOCASE.

The table was generated by a Mormot server with this Collation. Is there a way to add support for this collation

Thanks

[marsupilami]

Hello iancoullie,

duh - it seems that Mormot has registered its own collation with that database: https://www.sqlite.org/faq.html#q18. So you probably will have to register it from your Zeos application as well or use Mormot for accessing the data.

I am not sure if we currently expose the required functions for registering custom collations with SQLite I have to check that. SYSTEMNOCASE uses a Mormot specific function. See Mormot SynSQLite3.pas

Best regards,

Jan

[iancoullie]

Thanks Jan

I use the zeos component in a tiny app that updates data daily from a variety of XLS spreadsheets.
The app was previously connected to a firebird database and i am changing it to SQLite.
The irony is that the error only occurs with a delete with condition. The full delete works and the insert works.

Ill weigh up my options.

Thanks for your help

[iancoullie]

Hi Jan

Problem solved. I dropped the table and recreated without the collation.
As these are small tables and i have not noticed a speed drop when connecting with the mormot server.

Thanks very much