Delphi XE MySQL数据库操作类 MySQLHelper - 雪碧

本邮件内容由第三方提供，如果您不想继续收到该邮件，可 点此退订 。

Delphi XE MySQL数据库操作类 MySQLHelper - 雪碧  阅读原文»

注: 无需odbc配置

1 {*
2 * MySQL Helper v1.0
3 * 2015.6.19
4 * 说明:
5 * 这是一个操作MySQL的类,该类必须和libmysql.dll,dbxmys.dll两个文件一起使用.
6 * 安装:
7 * 将dll拷贝到C:\Windows\System32下和项目目录下,发行的时候放到exe目录下即可.
8 * 使用:
9 * //使用insert,update,delete语句时请使用:TMySQLHelper.ExecSQL();返回受影响行数Integer;
10 * //使用select语句时请使用TMySQLHelper.Query();返回数据集TSQLQuery;
11 * 测试:
12 * WIN7 SP1 X86 , MySQL 5.6.17 , Delphi XE 测试通过.
13 * ==========================================
14 * var
15 * MySQLHelper : TMySQLHelper;
16 * begin
17 * MySQLHelper := TMySQLHelper.Create;
18 * MySQLHelper.User_name := 'root';
19 * MySQLHelper.Password := 'root';
20 * MySQLHelper.Database := 'Test';
21 * ShowMessage('影响行数:'+IntToStr(MySQLHelper.ExecSQL('INSERT INTO test(name)values(''FangJun'')')));
22 * MySQLHelper.Free;
23 * end;
24 * ==========================================
25 * var
26 * MySQLHelper : TMySQLHelper;
27 * SQLQuery : TSQLQuery;
28 * begin
29 * MySQLHelper := TMySQLHelper.Create;
30 * MySQLHelper.User_name := 'root';
31 * MySQLHelper.Password := 'root';
32 * MySQLHelper.Database := 'Test';
33 * SQLQuery := TSQLQuery.Create(nil);
34 * SQLQuery := MySQLHelper.Query('select * from test');
35 * while not SQLQuery.Eof do
36 * begin
37 * ShowMessage('姓名:'+VarToStr(SQLQuery.FieldValues['name']);
38 * SQLQuery.Next;
39 * end;
40 * MySQLHelper.MySQLClose;
41 * MySQLHelper.Free;
42 * end;
43 * ==========================================
44 }
45 unit MySQLHelper;
46
47 interface
48
49 uses
50 SysUtils,StdCtrls,Classes,Variants,DB,SqlExpr,DBXMySQL;
51
52 type
53 TMySQLHelper = class(TObject)
54 private
55 _PORT : Integer;
56 _HOST : string;
57 _DATABASE : string;
58 _USER_NAME : string;
59 _PASSWORD : string;
60 _SERVERCHARSET : string;
61 _SQLQuery : TSQLQuery;
62 _SQLConnection : TSQLConnection;
63
64 procedure Set_PORT(const Value: Integer);
65 procedure Set_HOST(const Value: string);
66 procedure Set_DATABASE (const Value: string);
67 procedure Set_USER_NAME(const Value: string);
68 procedure Set_PASSWORD (const Value: string);
69 procedure Set_SERVERCHARSET(const Value: string);
70 function MySQLConnection:TSQLConnection;
71
72 public
73 constructor Create; overload;
74 property Post:Integer write Set_PORT;
75 property Host:string write Set_HOST;
76 property Database:string write Set_DATABASE;
77 property User_name:string write Set_USER_NAME;
78 property Password:string write Set_PASSWORD;
79 property ServerCharSet:string write Set_SERVERCHARSET;
80
81 function ExecSQL(const SQL:string):Integer;
82 function Query(const SQL:string):TSQLQuery;
83

SQL Server定时自动抓取耗时SQL并归档数据脚本分享 - 桦仔  阅读原文»

SQL Server定时自动抓取耗时SQL并归档数据脚本分享

第一步建库

USE [master]
GO

CREATE DATABASE [MonitorElapsedHighSQL]
GO

第二步创建sp_who3存储过程

-- http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3

USE [MonitorElapsedHighSQL]
GO

CREATE PROCEDURE [dbo].[sp_who3]

AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
SPID = er.session_id
,BlkBy = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END
,ElapsedMS = er.total_elapsed_time
,CPU = er.cpu_time
,IOReads = er.logical_reads + er.reads
,IOWrites = er.writes
,Executions = ec.execution_count
,CommandType = er.command
,LastWaitType = er.last_wait_type
,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
,SQLStatement =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
)
,STATUS = ses.STATUS
,[Login] = ses.login_name
,Host = ses.host_name
,DBName = DB_Name(er.database_id)
,StartTime = er.start_time
,Protocol = con.net_transport
,transaction_isolation =
CASE ses.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'Read Uncommitted'
WHEN 2 THEN 'Read Committed'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END
,ConnectionWrites = con.num_writes
,ConnectionReads = con.num_reads
,ClientAddress = con.client_net_address
,Authentication = con.auth_scheme
,DatetimeSnapshot = GETDATE()
,plan_handle = er.plan_handle
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
OUTER APPLY sys

阅读更多内容