Delphi & Absolute database : Delete Query

Question

Why is it that my query does not work ?

Form1.ABSQuery1.Close;
Form1.ABSQuery1.SQL.Clear;
Form1.ABSQuery1.SQL.Text:='DELETE FROM LOG WHERE status = ''YES'' and DATE BETWEEN :d1 and :d2';
Form1.ABSQuery1.Params.ParamByName('d1').Value :=cxDateEdit1.Date;
Form1.ABSQuery1.Params.ParamByName('d2').Value :=cxDateEdit2.Date;
Form1.ABSQuery1.ExecSQL;
Form1.ABSTable1.Refresh;

I get this error :

enter image description here

What doesn't work? Does it not delete the record(s) you expect? Do you get an SQL Error? Can you provide more info on what the actual issue is — Jason, Jun 18 '13 at 22:44
What happens if you parenthesize: `(DATE BETWEEN :d1 and :d2)`? — lurker, Jun 18 '13 at 22:59
It could be DATE is being interpreted as a function maybe? Is DATE really the name of one of your columns? If so, rename your DATE field to something else and try again. — Jason, Jun 18 '13 at 23:03
'delete from LOG where status = ''YES'' and (DATE BETWEEN :d1 and :d2)'; - nothing gets deleted ... — user763539, Jun 18 '13 at 23:09

score 1 · Accepted Answer · answered Jun 18 '13 at 23:08

You should be using AsDateTime in your Params setting code.

Form1.ABSQuery1.SQL.Text:='DELETE FROM LOG WHERE status = ''YES'' and DATE BETWEEN :d1 and :d2';
Form1.ABSQuery1.Params.ParamByName('d1').AsDateTime :=cxDateEdit1.Date;
Form1.ABSQuery1.Params.ParamByName('d2').AsDateTime :=cxDateEdit2.Date;
Form1.ABSQuery1.ExecSQL;

Using Value converts the cxDateEdit1.Date to a generic string format for assignment, and that doesn't properly convert it to the YYYY-MM-DD format that most databases (including ABS) expect. Properly using AsDateTime allows the database driver/component to convert to the specific date format the DBMS uses.

Also, is your database field really named DATE? Date is usually a reserved word or function name in most DBMS, and if it is it usually needs to be quoted.

It had me baffled for a while as I, at first, used .asDate. Adding time, it seems , does the trick. — user763539, Jun 18 '13 at 23:21

score 0 · Answer 2 · answered Jun 19 '13 at 04:36

Form1.ABSQuery1.Params.ParamByName('d1').DataType := ftDateTime;
Form1.ABSQuery1.Params.ParamByName('d1').Value :=cxDateEdit1.Date;

You must explicitly specify the data type of the parameter to it had no such problem, and then convert to a string does not need to

Delphi & Absolute database : Delete Query

2 Answers2