Storing Results from a query into Variables & Arrays.

[Baldur2630]

If I execute a query i.e. Nsql := ' SELECT COUNT(DISTINCT types.typeid) FROM types;';
How can I store the result into a Variable?

If I have a number of Items in a Table as varchar(45) and I execute a query such as ' SELECT types.myType FROM types;'; How can I store all the results into an Array[0..100] of ANSIString? (there will always be less than 100 - no higher than 99)

[marsupilami]

Hello Baldur2630,

just use an SQL query for the first one. Ie:

Code: Select all

var
  MyCount: Integer;
begin
...
  Query.Close
  Query.SQL.Text := 'SELECT COUNT(DISTINCT types.typeid) as COUNTX FROM types';
  Query.Open
  try
    MyCount := Query.FieldByName('COUNTX').AsInteger;
  finally
    Query.Close
  end;
...
end;

and for the Second one try something similar to this:

Code: Select all

var
  x: Integer;
  Values: Array of String;
begin
  ...
  Query.Open;
  SetLength(Values, Query.RecordCount)
  x := 0;
  Query.First;
  while (x <= High(Values)) and (not Query.Eof) do begin
    Values[x] := Query.FieldByName('FIELDNAME').AsString;
  end;
  ..
end;

Best regards,

Jan

[Baldur2630]

Thanks for the snippets but . . .

Snippet 1. Tells me that there is no Field COUNTX. This is my Table

Fsql:='CREATE TABLE types ( ' +
' typeid int(10) NOT NULL AUTO_INCREMENT,' +
' name varchar(60) NULL DEFAULT NULL,' +
' recipes_recipeid INT(10) NOT NULL,' +
' PRIMARY KEY (typeid),' +
' UNIQUE INDEX iname (name ASC),' +
' INDEX fk_types_recipes_idx (recipes_recipeid ASC)' +
' )ENGINE=INNODB DEFAULT CHARSET=latin1; ';

Snippet 2. Empty Query. What should I use as a query or would a stored procedure be better.?

I'm a complete 'newbie' when it comes to working with MySQL databases, give me something nice and simple like Novell eDirectory any day!

Can you please be a bit more explicit. Once I've succeeded. It stays there. my memory is still pretty good.

[marsupilami]

Hello Baldur2630,

hmmm - the SQL-Code for the first snippet was just a guess. I don't have a MySQL-Server here, so I cannot easily test for that. The COUNTX was just used by me as the alias name for the field where the Server returns the count.
If you execute the query from your first post
SELECT COUNT(DISTINCT types.typeid) FROM types;
-> how does the Server name the field where it returns the result of the count function? Use that name in the FieldByName-Method in my codesnipet. Or replace
MyCount := Query.FieldByName('COUNTX').AsInteger;
with
MyCount := Query.Fields[0].AsInteger;
But in my opinion the latter is bad code. Also you might want to read up on the Documentation of TQuery for the Method FieldByName.

As for the second snippet: I enhanced it by adding the query from your first post:

Code: Select all

var
  x: Integer;
  Values: Array of String;
begin
  ...
  Query.Close;
  Query.SQL.Text := 'SELECT types.myType FROM types';
  Query.Open;
  SetLength(Values, Query.RecordCount)
  x := 0;
  Query.First;
  while (x <= High(Values)) and (not Query.Eof) do begin
    Values[x] := Query.FieldByName('FIELDNAME').AsString;
  end;
  ..
end;

I hope this makes things more clear. In any case I suggest that you test your SQL-Queries in an admin tool like phpMyAdmin or a similar tool because both of them seem somewhat ugly to me- But then I am not a professional for MySQLs SQL...

Best regards,

Jan