在Delphi中使用ADOConnection查看“打印”语句的输出 [英] View output of 'print' statements using ADOConnection in Delphi
问题描述
我的某些MS SQL存储过程使用打印命令生成消息。在使用TADOConnection连接到MS SQL的Delphi 2007应用程序中,如何查看这些打印命令的输出?
Some of my MS SQL stored procedures produce messages using the 'print' command. In my Delphi 2007 application, which connects to MS SQL using TADOConnection, how can I view the output of those 'print' commands?
关键要求:
1 )我不能多次运行查询;它可能正在更新事物。
2)即使返回数据集,我也需要查看打印结果。
Key requirements: 1) I can't run the query more than once; it might be updating things. 2) I need to see the 'print' results even if datasets are returned.
推荐答案
这很有趣一个...
ADOConnection的OnInfoMessage事件起作用,但详细信息中包含Devil!
That was an interesting one...
The OnInfoMessage event from the ADOConnection works but the Devil is in the details!
要点:
使用CursorLocation = clUseServer而不是默认的clUseClient。
使用Open,而不是将ExecProc与ADOStoredProc一起使用。
使用NextRecordset从当前值中获取以下内容,但请确保检查您是否已打开。
在存储过程中使用SET NOCOUNT = ON。
Main points:
use CursorLocation = clUseServer instead of the default clUseClient.
use Open and not ExecProc with your ADOStoredProc.
use NextRecordset from the current one to get the following, but be sure to check you have one open.
use SET NOCOUNT = ON in your stored procedure.
SQL端:您的存储过程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FG_TEST]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[FG_TEST]
GO
-- =============================================
-- Author: François
-- Description: test multi ADO with info
-- =============================================
CREATE PROCEDURE FG_TEST
AS
BEGIN
-- SET NOCOUNT ON absolutely NEEDED
SET NOCOUNT ON;
PRINT '*** start ***'
SELECT 'one' as Set1Field1
PRINT '*** done once ***'
SELECT 'two' as Set2Field2
PRINT '*** done again ***'
SELECT 'three' as Set3Field3
PRINT '***finish ***'
END
GO
Delphi端:
创建一个新的VCL表单应用程序。
在表单中放置一个备忘录和一个按钮。
Delphi side:
Create a new VCL Forms Application.
Put a Memo and a Button in your Form.
复制以下文本,更改目录和数据源并将其粘贴到您的表单上
Copy the following text, change the Catalog and Data Source and Paste it onto your Form
object ADOConnection1: TADOConnection
ConnectionString =
'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security In' +
'fo=False;Initial Catalog=xxxYOURxxxDBxxx;Data Source=xxxYOURxxxSERVERxxx'
CursorLocation = clUseServer
LoginPrompt = False
Provider = 'SQLOLEDB.1'
OnInfoMessage = ADOConnection1InfoMessage
Left = 24
Top = 216
end
object ADOStoredProc1: TADOStoredProc
Connection = ADOConnection1
CursorLocation = clUseServer
ProcedureName = 'FG_TEST;1'
Parameters = <>
Left = 24
Top = 264
end
在
In the OnInfoMessage of the ADOConnection put
Memo1.Lines.Add(Error.Description);
对于ButtonClick,粘贴此代码
For the ButtonClick, paste this code
procedure TForm1.Button1Click(Sender: TObject);
const
adStateOpen = $00000001; // or defined in ADOInt
var
I: Integer;
ARecordSet: _Recordset;
begin
Memo1.Lines.Add('==========================');
ADOStoredProc1.Open; // not ExecProc !!!!!
ARecordSet := ADOStoredProc1.Recordset;
while Assigned(ARecordSet) do
begin
// do whatever with current RecordSet
while not ADOStoredProc1.Eof do
begin
Memo1.Lines.Add(ADOStoredProc1.Fields[0].FieldName + ': ' + ADOStoredProc1.Fields[0].Value);
ADOStoredProc1.Next;
end;
// switch to subsequent RecordSet if any
ARecordSet := ADOStoredProc1.NextRecordset(I);
if Assigned(ARecordSet) and ((ARecordSet.State and adStateOpen) <> 0) then
ADOStoredProc1.Recordset := ARecordSet
else
Break;
end;
ADOStoredProc1.Close;
end;
这篇关于在Delphi中使用ADOConnection查看“打印”语句的输出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!