問題的提出：
一般我們要根據(jù)數(shù)據(jù)庫的紀(jì)錄變化時，進(jìn)行某種操作。我們習(xí)慣的操作方式是在程序中不停的查詢表，判斷是否有新紀(jì)錄。這樣耗費的資源就很高,，如何提高這種效 率，我想在表中創(chuàng)建觸發(fā)器，在觸發(fā)器中調(diào)用外部動態(tài)連接庫通過消息或事件通知應(yīng)用程序就可實現(xiàn),。而master的存儲過程中最好能調(diào)用外部的動態(tài)連接庫， 我們在觸發(fā)器中調(diào)用master的存儲過程即可,。
下載源代碼 大小:14K
說明:VC6需要安裝較新的Platform SDK才能順利編譯本代碼,，VC.Net可以直接編譯本代碼。另外還需要連接Opends60.lib
為了使沒有較新Platform SDK的朋友也能編譯本例子,，已經(jīng)將VC.Net中的Srv.h和Opends60.lib放到壓縮包中
程序?qū)崿F(xiàn)：
我們來實現(xiàn)一個存儲過程中調(diào)用外部的dll(storeproc.dll)的函數(shù)SetFileName和addLine,。
存儲過程如下(需放到master庫中)：
CREATE PROCEDURE sp_testdll AS
exec sp_addextendedproc 'SetFileName', 'storeproc.dll' --聲明函數(shù)
exec sp_addextendedproc 'addLine', 'storeproc.dll'
declare @szFileName varchar(200)
declare @szText varchar(200)
declare @rt int
Select @szFileName = 'c:/welcome.txt'
EXEC @rt = SetFileName @szFileName --調(diào)用SetFileName函數(shù)，參數(shù)為--szFileName;
if @rt = 0
begin
select @szText = 'welcome 01'
Exec @rt = addLine @szText --調(diào)用addLine
select @szText = 'welcome 02'
Exec @rt = addLine @szText
end
exec sp_dropextendedproc 'SetFileName'
exec sp_dropextendedproc 'addLine'
dbcc SetFileName(free)
dbcc addLine(free)
動態(tài)連接庫的實現(xiàn)：這種動態(tài)連接庫和普通的有所不同。該動態(tài)連接庫要放入SQL的執(zhí)行目錄下,，或直接放到Window的System32目錄下,并重起SQL-Server
#include <windows.h>
#include <srv.h> //要加入這個.h文件
#define XP_NOERROR 0
#define XP_ERROR 1
#ifndef _DEBUG
#define _DEBUG
#endif
char szFileName[MAX_PATH+1];
void WriteInfo(const char * str);
extern "C" SRVRETCODE WINAPI SetFileName(SRV_PROC* pSrvProc)
{
WriteInfo("SetFileName start");
int paramCount = srv_rpcparams(pSrvProc);
if (paramCount != 1){
WriteInfo("Param Err start");
return XP_ERROR;
}
BYTE bType;
unsigned long cbMaxLen;
unsigned long cbActualLen;
BOOL fNull;
int ret = srv_paraminfo(pSrvProc, 1, &bType, &cbMaxLen, &cbActualLen,
NULL, &fNull);
if (cbActualLen){
ZeroMemory(szFileName, MAX_PATH+1);
memcpy(szFileName, srv_paramdata(pSrvProc, 1), cbActualLen);
WriteInfo("Set filename ok");
return (XP_NOERROR);
}
else {
WriteInfo("Set filename param failed");
return XP_ERROR;
}
}
extern "C" SRVRETCODE WINAPI addLine(SRV_PROC* pSrvProc)
{
WriteInfo("addline start");
int paramCount = srv_rpcparams(pSrvProc);
if (paramCount != 1){
WriteInfo("addline param err");
return XP_ERROR;
}
BYTE bType;
unsigned long cbMaxLen;
unsigned long cbActualLen;
BOOL fNull;
bool rt = false;
int ret = srv_paraminfo(pSrvProc, 1, &bType, &cbMaxLen, &cbActualLen,
NULL, &fNull);
if (cbActualLen){
int n;
char srt[3] = {0x0d, 0x0a, 0};
char * c = new char[cbActualLen + 3];
if (!c)return XP_ERROR;
ZeroMemory(c, cbActualLen + 3);
memcpy(c, srv_paramdata(pSrvProc, 1), cbActualLen);
memcpy(c+cbActualLen, srt, 3);
HANDLE hf = CreateFile(szFileName, GENERIC_WRITE, FILE_SHARE_WRITE|FILE_SHARE_READ, NULL,
OPEN_ALWAYS, 0, NULL);
if (hf == INVALID_HANDLE_VALUE){
WriteInfo("addline create file err ");
delete []c;
return XP_ERROR;
}
WriteInfo("addline create file ok ");
DWORD dwWt;
n = strlen(c);
SetFilePointer(hf, 0, NULL, FILE_END);
if (WriteFile(hf, c, n, &dwWt, NULL) && dwWt == n)
{
WriteInfo("addline write file ok ");
rt = true;
}
delete []c;
CloseHandle(hf);
}
return rt ? XP_NOERROR:XP_ERROR;
}
inline void WriteInfo(const char * str){
#ifdef _DEBUG
char srt[3] = {0x0d, 0x0a, 0};
HANDLE hf = CreateFile("c://storeproc.log", GENERIC_WRITE, FILE_SHARE_WRITE|FILE_SHARE_READ, NULL,
OPEN_ALWAYS, 0, NULL);
if (hf != INVALID_HANDLE_VALUE){
SetFilePointer(hf, 0, NULL, FILE_END);
DWORD dwWt;
WriteFile(hf, str, strlen(str), &dwWt, NULL);
WriteFile(hf, srt, strlen(srt), &dwWt, NULL);
CloseHandle(hf);
}
else {
MessageBox(NULL, "Write info err", "Message", MB_OK|MB_ICONINFORMATION);
}
#endif
}
BOOL WINAPI DllMain(HINSTANCE hinstDLL,DWORD fdwReason,LPVOID lpReserved)
{
return TRUE;
}
編譯完成后,，把動態(tài)鏈接庫放到WINNT/System32目錄下,啟動SQL Server。我們可以打開SQL Server Query Analyzer調(diào)用存儲過程sp_testdll以測試其運行是否正確,。
具體可參考SQL-Server的在線幫助,。
筆者環(huán)境：win2000 professional + SQL-Server7.0(2000也可)
VC6.0+SP5+Platform SDK 20001.8
VC知識庫測試環(huán)境：win2000 professional + SQL-Server 7.0 + VC.Net |
