Page 1 of 1
Query Results as Text
Posted: Mon 31 Dec 2012 22:37
by testiclesoup
I want to create a free SQL form in my application. It would be used for trouble shooting problems remotely. Basically an isql type window inside my application. I am currently using a MSQuery object to post the query. I can of course, put the results into a dbgrid or something similar, but I prefer to simply see the results as text if possible. Most likely dumped into a TMemo type component. Is there a simple way to do that?
Re: Query Results as Text
Posted: Wed 02 Jan 2013 09:31
by AlexP
Hello,
To output data from DataSet in Memo, you can use loops on all records and fields, for example:
Code: Select all
var
i: integer;
s: String;
begin
MSQuery1.Open;
While Not MSQuery1.Eof do
begin
s := '';
for i := 0 to MSQuery1.FieldCount - 1 do
s := s + ' ' + MSQuery1.Fields[i].AsString;
Memo1.Lines.Add(s);
MSQuery1.Next;
end;
end;
Re: Query Results as Text
Posted: Wed 02 Jan 2013 15:15
by testiclesoup
My issue with this is the formatting. I guess I really don't know what tools like isql do internally. I only know the output is displayed as text that lines up properly in vertical columns. I don't think this solution would do that.I already do something similar but into a grid component so I can resize the columns. Plus, if I issue a command that has a print statement, the print output has to be retrieved via the connection object and I have no idea of the timing between print output and column information.
Re: Query Results as Text
Posted: Thu 03 Jan 2013 09:48
by AlexP
hello,
Please describe your problem in greater detail and we'll try to suggest you a solution for it.
Re: Query Results as Text
Posted: Fri 04 Jan 2013 20:53
by testiclesoup
The issue is this...
When I query a small table in our database, when I use your method I get something like this:
alert Alert.1.0.exe
pics PICS.2.9.29.310.exe
shopview ShopView.2.1.9.exe
There are two columns returned of varying lengths. What I would prefer to see is something like this:
program_name executable_name
------------ -------------------
alert Alert.1.0.exe
pics PICS.2.9.29.310.exe
shopview ShopView.2.1.9.exe
Note the columns are labeled and justified. This is more what I see with isql type tools. Without a great deal of programming or scanning through the results twice to find the greatest width of columns, is there a simple way to see this information?
Thanks,
Kevin
Re: Query Results as Text
Posted: Fri 04 Jan 2013 20:55
by testiclesoup
The tabs did not expand in my previous post. I didn't realize there were tab characters. What the second set of results should have looked like is this:
program_name executable_name
------------ -------------------
alert Alert.1.0.exe
pics PICS.2.9.29.310.exe
shopview ShopView.2.1.9.exe
Sorry for any confusion this may have caused.
Kevin
Re: Query Results as Text
Posted: Fri 04 Jan 2013 20:58
by testiclesoup
Well, your system keeps reformatting the post and removing extra space. The second set of results SHOULD be shown with the columns left justified.
Sorry, but I don't know how to do it
Re: Query Results as Text
Posted: Tue 08 Jan 2013 16:07
by AndreyZ
You can use the code like this:
Code: Select all
var
i, sz: integer;
s: String;
begin
MSQuery1.Open;
while Not MSQuery1.Eof do begin
s := '';
for i := 0 to MSQuery1.FieldCount - 1 do begin
sz := MSQuery1.Fields[i].Size;
if sz = 0 then
sz := 4;
s := s + ' ' + MSQuery1.Fields[i].AsString + ' ' + DupeString(' ', sz - length(MSQuery1.Fields[i].AsString)) + '|';
end;
Memo1.Lines.Add(s);
MSQuery1.Next;
end;
Memo1.Lines.SaveToFile('filename');
end;
The text in the file will be left-justified. If you want to obtain any other kind of text formatting, you should code it yourself.
Re: Query Results as Text
Posted: Tue 08 Jan 2013 21:17
by testiclesoup
I'll try that thanks. It is a step in the right direction. I suppose I should handle Info message like print statements separately. However, if I do, I think I would lose sequencing as in a stored proc that returns a set of rows, a print statement then another set of rows might not come out in that order. I'll play with that but would appreciate any suggestions you might have in that direction.
Thanks a lot for your help.
Regards,
Kevin
Re: Query Results as Text
Posted: Wed 09 Jan 2013 14:23
by AndreyZ
To handle PRINT statements, you can use the TMSConnection.OnInfoMessage event handler. Here is a code example:
Code: Select all
procedure TForm1.Button1Click(Sender: TObject);
begin
MSStoredProc1.StoredProcName := 'testprint'; // testprint is a stored procedure that executes PRINT statements
MSStoredProc1.Execute;
end;
procedure TForm1.MSConnection1InfoMessage(Sender: TObject; E: EMSError);
begin
ShowMessage(E.Message);
end;
Note that to run this code, you should add the OLEDBAccess unit to the USES clause of your unit.