Solved

SQL Messages from a Executing Stored Procedure in Delphi

Posted on 2004-09-08
3
1,100 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

734 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