Solved

SQL Messages from a Executing Stored Procedure in Delphi

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

17 Experts available now in Live!

Get 1:1 Help Now