SQL Query output to a variable in Delphi?

Question

I Would like to know how Can I put the SQL Query result into a variable.

I'm aware of this

integerVariable := UniQuery1.RecordCount;

but this?

integerVariable := SELECT COUNT(*) FROM Orders WHERE Amount='1000'

Ehm, what ? I think you mean something like UniQuery1.Fields[0].AsInteger if you are on the first row of your dataset, but we don't even know what type is the UniQuery1. — TLama
– TLama, Commented Mar 30, 2012 at 14:11
@TLama UniQuery1: TUniQuery, s/he uses the UniDAC components from devart.com/unidac — user497849
– user497849, Commented Mar 30, 2012 at 14:17
Don't forget to accept the answers that resolve your questions this way. It will help the future visitors to recognize the right solution. Thanks and enjoy StackOverflow! — TLama
– TLama, Commented Mar 31, 2012 at 0:26

score 7 · Accepted Answer · 2012-03-30 14:34:40Z

7

what you need to do is first "execute" the sql, then check for result, if result is present, then store it in a variable, here's what I mean:

procedure ...;
var
  LCount: Integer;
begin
  LCount := 0;
  //
  // note that I am doubling the single quote to escape it
  //
  // set the query
  UniQuery1.SQL.Text := 'SELECT COUNT(*) FROM Orders WHERE Amount=''1000'';';
  //
  // "execute" it
  //
  UniQuery1.Open;
  //
  // SELECT COUNT(*) will return 1 record with 1 field
  // most likely the field name is 'count' <= lower case
  // but we are sure that there should be only 1 field so we 
  // access it by Fields[Index].As[TYPE]
  //
  LCount := UniQuery1.Fields[0].AsInteger;
  ShowMessageFmt('Total count of orders with Amount = 1000: %d', [LCount]);
end;

EDIT: thank you for point out that "COUNT" will always have a return.

edited Mar 30, 2012 at 14:34

answered Mar 30, 2012 at 14:15

user497849

Sign up to request clarification or add additional context in comments.

6 Comments

Andriy M Over a year ago

Obviously, this particular query will always return a result, moreover, it will return a value (as opposed to NULL), because COUNT() always returns a value, never a NULL. So, the not IsEmpty test can be omitted in this case.

kobik Over a year ago

@Andriy is right. the UniQuery1.FindFirst is also not needed.

TLama Over a year ago

I don't know TUniQuery but isn't the dataset already on the first row after performing TUniQuery.Open ? Wouldn't be enough something like UniQuery1.Open; LCount := UniQuery1.Fields[0].AsInteger; ?

kobik Over a year ago

The field name is not count. to specify a name use select count(*) as as RecordCount and use UniQuery1.FiledByName('RecordCount').AsInteger. also better to use Parameters in your SQL.Text instead of doubling the single quote.

user497849 Over a year ago

@kobik I wrote "most likely the field name is 'count'", in Postgres the field name would be "count" if no alias defined, don't know about sql server

|

Collectives™ on Stack Overflow

SQL Query output to a variable in Delphi?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related