4

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'
Leonardo Herrera
  • 8,388
  • 5
  • 36
  • 66
ed tru
  • 101
  • 1
  • 2
  • 7
  • 3
    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 Mar 30 '12 at 14:11
  • 1
    @TLama UniQuery1: TUniQuery, s/he uses the UniDAC components from http://www.devart.com/unidac/ –  Mar 30 '12 at 14:17
  • 1
    Don't forget to accept the answers that resolve your questions [`this way`](http://meta.stackexchange.com/a/5235/179541). It will help the future visitors to recognize the right solution. Thanks and enjoy StackOverflow! – TLama Mar 31 '12 at 00:26

1 Answers1

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.

  • 4
    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. – Andriy M Mar 30 '12 at 14:22
  • 1
    @Andriy is right. the `UniQuery1.FindFirst` is also not needed. – kobik Mar 30 '12 at 14:25
  • 2
    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;` ? – TLama Mar 30 '12 at 14:25
  • 1
    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. – kobik Mar 30 '12 at 15:29
  • @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 –  Mar 30 '12 at 15:46
  • @DorinDuminica, that was just a generic observation. +1 – kobik Mar 30 '12 at 15:51