Solved

SQL Messages from a Executing Stored Procedure in Delphi

Posted on 2004-09-08
3
1,156 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

Industry Leaders: 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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month4 days, 21 hours left to enroll

635 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