sql – How to check if a TQuery returns any results in Delphi?

Question:

I'm trying to find out if a record exists in the database, I run a select query and I want to get the result, I still can't get it …

Database

By running the following query directly against the database:

SELECT T.ID
FROM TABLA T
WHERE ID=3

Is obtained:

No rows returned

Now I try to display a message in Delphi saying that the record does not exist.

In the form I have a TQuery type component called qValidacion correctly connected with Oracle 11g database.

Attempt 1

procedure TfPrueba.ButtonAceptarClick(Sender: TObject);
begin
    qValidacion.Close;
    qValidacion.SQL.Add('SELECT T.ID');
    qValidacion.SQL.Add('FROM TABLA T');
    qValidacion.SQL.Add('WHERE ID=3');
    qValidacion.Open;
    qValidacion.First;
    if qValidacion.IsEmpty then //No se como validar si el Select regresa algo, IsEmpty me muestra los registros existentes
        begin
             ShowMessage('El registro No Existe');
        end;
    qValidacion.SQL.Clear;
end;

Try 2

procedure TfPrueba.ButtonAceptarClick(Sender: TObject);
begin
    qValidacion.Close;
    qValidacion.SQL.Add('SELECT T.ID');
    qValidacion.SQL.Add('FROM TABLA T');
    qValidacion.SQL.Add('WHERE ID=3');
    qValidacion.Open;
    qValidacion.First;
    if (not qValidacion.Eof) then 
        begin
             ShowMessage('El registro No Existe'); //deberia mostrar el mensaje, pero no lo muestra
        end;
    qValidacion.SQL.Clear;
end;

Answer:

The IsEmpty property of TDataSet returns true if the dataset is empty.

In general, with any descendant component of TDataSet this should work:

qValidacion.Close;
qValidacion.SQL.Text := 'select t.ID from tabla t where t.id = 3';
qValidacion.Open;
if (qValidacion.IsEmpty) then
  raise Exception.Create('El registro no existe');

Now, TQuery is a component that is part of the first standard socket layer that came with Delphi, called the Borland Database Engine (BDE), and is marked obsolete many years ago. BDE's oracle driver is probably having issues (or always has, I can't remember), and is bringing up a row with all fields set to null (I remember seeing this behavior with Firebird at some point.

You can do two things:

With BDE and a driver that fetches a row with null values

If this is the case, then check that the retrieved value is not null, for example this would work in the particular case that you raise:

qValidacion.Close;
qValidacion.SQL.Text := 'select t.ID from tabla t where t.id = 3';
qValidacion.Open;
if (qValidacion.Fields[0].IsNull) then
  raise Exception.Create('El registro no existe');

Eye that this solution can not generalize, as there may be a case where a value recover null in a row if it exists in the database, so another option would be to have the same records, for example:

qValidacion.Close;
qValidacion.SQL.Text := 'select count(t.ID) Cuenta from tabla t where t.id = 3';
qValidacion.Open;
if (qValidacion.Fields[0].AsInteger = 0) then
  raise Exception.Create('El registro no existe');

Move to a modern database socket layer

This is the true answer. Even if you use old versions of Delphi, like Delphi 7, you can use DBExpress , or ideally, if you have a modern version of Delphi, the current recommendation is to use FireDac

Post Views: 21
Scroll to Top