Page 1 of 1
Storing Results from a query into Variables & Arrays.
Posted: 30.04.2013, 14:32
by 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)
Posted: 30.04.2013, 16:29
by 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
Posted: 02.05.2013, 17:53
by 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.
Posted: 07.05.2013, 14:50
by 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