Query Results as Text

[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?

[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;

[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.

[AlexP]

hello,

Please describe your problem in greater detail and we'll try to suggest you a solution for it.

[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

[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

[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

[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.

[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

[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.