Solved

SQL Messages from a Executing Stored Procedure in Delphi

Posted on 2004-09-08
3
1,021 Views
Last Modified: 2012-06-21
I have a large Stored Procedure in Microsoft SQL Server 2000 which is fired from a Delphi 7 App.

Is there a way of getting the messages, Like what is displayed in the Microsoft SQL Query Analyzer messages panel, in real time.

Becuase of the long execution time of the procedure it would be great to get the feedback as to the procedure is actually doing something

Cheers
Darren



0
Comment
Question by:dhnkley
3 Comments
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12013180
I am not sure you can implement what you want. One possible solution is to create e temporary table, for e.g.:

CREATE TABLE PROC_TMP_RESULT
(
  MSG VARCHAR(255)
)

from inside your procedure, while it is being executed, you can make some INSERT into this table and read the message from the table...
0
 
LVL 10

Expert Comment

by:Jacco
ID: 12013280
If you use ADO and want to catch "print" statements, you can you the solution I gave here:

http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_20961933.html

The TADOConnectio.OnInfoMessage event.

Regards Jacco
0
 
LVL 13

Accepted Solution

by:
BlackTigerX earned 500 total points
ID: 12018598
The only way I have been able to make this work is by:

drop a connection and a stored procedure, hook them up to the server and stored procedure
set the CursorLocation in both to clUseServer

add an event handler for the ADOConnection OnInfoMessage event

procedure TForm1.ADOConnection1InfoMessage(Connection: TADOConnection;
  const Error: Error; var EventStatus: TEventStatus);
begin
  Memo1.Lines.Add(Error.Description); //this is the print statement of your stored procedure
  EventStatus:=esCancel
end;

add code to execute the procedure

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOStoredProc1.Open
end;

is VERY important to use OPEN instead of ExecProc method, ExecProc will only return the first print statement of your stored procedure

my stored procedure looks like this:

create procedure test
as
print 'starting'
print 'ending'
select null --This line is just necessary so my Delphi code doesn't trow an exception saying that the procedure doesn't return a dataset when I used the Open method

here's the full unit:
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOStoredProc1: TADOStoredProc;
    Memo1: TMemo;
    Button1: TButton;
    procedure ADOConnection1InfoMessage(Connection: TADOConnection;
      const Error: Error; var EventStatus: TEventStatus);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.ADOConnection1InfoMessage(Connection: TADOConnection;
  const Error: Error; var EventStatus: TEventStatus);
begin
  Memo1.Lines.Add(Error.Description);
  EventStatus:=esCancel
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOStoredProc1.Open
end;

end.


and the form Code:

object Form1: TForm1
  Left = 192
  Top = 123
  Width = 363
  Height = 332
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Memo1: TMemo
    Left = 8
    Top = 112
    Width = 313
    Height = 177
    Lines.Strings = (
      'Memo1')
    TabOrder = 0
  end
  object Button1: TButton
    Left = 104
    Top = 72
    Width = 75
    Height = 25
    Caption = 'Button1'
    TabOrder = 1
    OnClick = Button1Click
  end
  object ADOConnection1: TADOConnection
    Connected = True
    ConnectionString =
      'Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initia' +
      'l Catalog=AirGroup;Data Source=acs-db'
    CursorLocation = clUseServer
    LoginPrompt = False
    Provider = 'SQLOLEDB.1'
    OnInfoMessage = ADOConnection1InfoMessage
    Left = 16
    Top = 16
  end
  object ADOStoredProc1: TADOStoredProc
    Connection = ADOConnection1
    CursorLocation = clUseServer
    ProcedureName = 'test;1'
    Parameters = <>
    Left = 16
    Top = 64
  end
end

pretty lame way of doing it... Microsoft ADO has some bugs (they are aware of them) that's why you need to do it this way to get those messages
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now