Return value using alias name

Question

I wrote code like this :

sql1 :='select sum(jumlah) as debet from kasir_kas_transaksi where flag in (1,3) and tanggal = to_date('+quotedstr(after)+','+quotedstr('dd-mm-yyyy')+')';

with zquery1 do begin
  close;
  sql.clear;
  sql.Add(sql1);
  execSQL;
end;
edit1.Text:=zquery1.fieldbyname('debet').asstring;

then an error occured saying field 'debet' does not exist. is there something wrong in the code? I am using delphi 7 and zeos as connection, as well as oracle 11g as db. Thank you!

Answer 1

Use Open, not ExecSQL. ExecSQL is for queries that don't return result sets (INSERT, DELETE, and UPDATE). Open is for queries that return rows. You also don't need to use SQL.Clear; and then SQL.Add(); - you can just set SQL.Text directly instead.

(You should stop using with; that's a different topic, but I left it here because it's your code and not mine.)

with ZQuery1 do
begin
  SQL.Text := sql1;
  Open;
end;
Edit1.Text := ZQuery1.FieldByName('debet').AsString;

And as a note, stop concatenating SQL and use parameters instead. String concatenation with user input leaves you wide-open for SQL injection. Google Little Bobby Tables to find out why that's a bad thing if you don't know already. You can find an example here of writing your code using parameters instead.