Multiple Items From a SQL Select string

[BillKilgore]

Hello everyone,

I’ve been trying to extract some data from an Access DB then I want to insert it into an Excel
File for further analysis (Length, Width, BoardFeet, ProcessTime) rather than write Functions for each view.
The SQL statement works as far as it goes and inserting data from an array into EXCEL shouldn’t be a problem. I was able to, ‘SELECT COUNT’ with a variant of it.
My problem is getting the data from the SQL string into an array of some kind (dynamic, variant, etc). I seem to recall having to creat a temporary table or something and reading from that.
My code looks like this. I can get one tuple out but no more than that. It’s been a long time since my SQL class and I’m out in the field so I haven’t got access to much documentation. Also, I'm running with Vista on a Dell so there's also no Delphi 'Help' function.

With adoqSomeQuery
Close;
SQL.Clear;
SQL.Add('SELECT * FROM Logs WHERE Specie + QuotedStr(SpecieDesignator));
RetrieveLogData := FieldByName('LogNum').AsString;
Active := True;
Open;
SQL.Clear;
Close;


The 'RetrieveLogData' statement was only to see if anything comes out.


Any help would really be appreciated at this point.

Thanks all, Bill Kilgore

 

[Opieo]

Now, the SQL string might have just been typed on the fly, and this could already be correct in your program, but try structuring the line this:

SQL.Add ([blue]'SELECT * FROM Logs WHERE Specie = '[/blue]
                     + QuotedStr(SpecieDesignator) + [blue]';'[/blue];

Also, I am not sure how much of a difference it makes as I have never actually tested it the other way, but I always issue the SQL.Clear statement after the Close.

Hope this helps some.

~
Chuck Norris is the reason Waldo is hiding.

 

[DjangMan]

Typically I would do something like this:

With adoqSomeQuery do
begin
  Close;
  SQL.Clear;
  SQL.Add('SELECT * FROM Logs WHERE Specie = '+ QuotedStr(SpecieDesignator));
  Open;
  while not(EOF) do
  begin
    ExportRecord(adoqSomeQuery);
    next;
  end;
  Close;
end;

Then you write a procedure called ExportRecord which takes a TADOQuery as a parameter and export the record your pointed at. You don't need a temp table as you already have the table in your adoqSomeQuery:

Something like:

sOutString := '';
for i := 0 to aADOQuery.Fields.Count -1 do
begin
  sOutString := sOutstring + QuotedStr(aADOQuery.fields[i].AsString)+',';
end;
sOutstring := copy(sOutstring,1,length(sOutstring)-1); //chop last comma
//then send sOutstring to your export file

There are lots of ways to do this.

http://www.scalabium.com/

has import/export components and you should be able to find code on the Internet to assist as well.

 

[lespaul]

My problem is getting the data from the SQL string into an array of some kind (dynamic, variant, etc).

you mean you want to loop through the recordset and get information about each record the query returned? I haven't used ADO queries much, but I think they are slightly different then the query component I use, but basically something like this (I also corrected some of your code, you need to open or make the query active before you try to get the "fieldbyname" and you needed some begin and end around your with):

[navy][i]// for automatic syntax highlighting see faq102-6487 
[/i][/navy]With adoqSomeQuery
[b]begin[/b]
Close;
SQL.Clear;
SQL.Add([teal]'SELECT * FROM Logs WHERE Specie ='[/teal] + QuotedStr(SpecieDesignator));
Open;
[b]while[/b] [b]not[/b] eof [b]do[/b]
[b]begin[/b]
RetrieveLogData := FieldByName([teal]'LogNum'[/teal]).AsString;
ShowMessage(RetrieveLogData);
Next;
[b]end[/b];
SQL.Clear;
Close;
[b]end[/b];

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[BillKilgore]

Hello Once Again,

I hope everyone who contributed sees this response. Thank you people so much for getting me out of that mess. I was unaware of using the ADOTquery in the way that DjangMan indicated so I tried it. It’s really slick. Creating a table would have been totally redundant.
I also used Lespaul’s method, as I often have before, in an additional retrieve.
I utilized both methods so that I’ll have functioning examples of each for future reference.
The SQL example I included in my note was indeed incorrect. I can only attribute the typo to the lateness of the hour after a long day.

So thanks again, Bill Kilgore

 

[DjangMan]

As it so happens I came across this code last night. I haven't tried it but it may be of use:

http://www.koders.com/delphi/fidFAFDAD753E42FBEA2FCC2887688366275171836C.aspx?s=delphi#L2

or

http://tinyurl.com/3cnquh