在預存程序中調用外部的動態串連庫(MS SQL Server7.0/2000環境)

問題的提出：
一般我們要根據資料庫的紀錄變化時，進行某種操作。我們習慣的操作方式是在程式中不停的查詢表，判斷是否有新紀錄。這樣耗費的資源就很高，如何提高這種效率，我想在表中建立觸發器，在觸發器中調用外部動態串連庫通過訊息或事件通知應用程式就可實現。而master的預存程序中最好能調用外部的動態串連庫，我們在觸發器中調用master的預存程序即可。

下載原始碼 大小:14Khttp://www.vckbase.com/vckbase/vckbase12/vc/nonctrls/database_07/StoreProc.zip

說明:VC6需要安裝較新的Platform SDK才能順利編譯本代碼，VC.Net可以直接編譯本代碼。另外還需要串連Opends60.lib
為了使沒有較新Platform SDK的朋友也能編譯本例子，已經將VC.Net中的Srv.h和Opends60.lib放到壓縮包中

程式實現：
我們來實現一個預存程序中調用外部的dll(storeproc.dll)的函數SetFileName和addLine。

預存程序如下(需放到master庫中)： CREATE PROCEDURE sp_testdll AS

exec sp_addextendedproc 'SetFileName', 'storeproc.dll'    --聲明函數
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    --調用SetFileName函數，參數為--szFileName;
if @rt = 0
begin
select @szText = 'welcome 01'
Exec @rt = addLine @szText                    --調用addLine
select @szText = 'welcome 02'
Exec @rt = addLine @szText

end
exec sp_dropextendedproc 'SetFileName'
exec sp_dropextendedproc 'addLine'

dbcc SetFileName(free)
dbcc addLine(free)

動態串連庫的實現：這種動態串連庫和普通的有所不同。該動態串連庫要放入SQL的執行目錄下，或直接放到Window的System32目錄下,並重起SQL-Server

 

#include <windows.h><br />#include <srv.h> //要加入這個.h檔案<br />#define XP_NOERROR 0<br />#define XP_ERROR 1<br />#ifndef _DEBUG<br />#define _DEBUG<br />#endif<br />char szFileName[MAX_PATH+1];<br />void WriteInfo(const char * str);<br />extern "C" SRVRETCODE WINAPI SetFileName(SRV_PROC* pSrvProc)<br />{<br /> WriteInfo("SetFileName start");<br /> int paramCount = srv_rpcparams(pSrvProc);<br /> if (paramCount != 1){<br /> WriteInfo("Param Err start");<br /> return XP_ERROR;<br /> }<br /> BYTE bType;<br /> unsigned long cbMaxLen;<br /> unsigned long cbActualLen;<br /> BOOL fNull;<br /> int ret = srv_paraminfo(pSrvProc, 1, &bType, &cbMaxLen, &cbActualLen,<br /> NULL, &fNull);<br /> if (cbActualLen){<br /> ZeroMemory(szFileName, MAX_PATH+1);<br /> memcpy(szFileName, srv_paramdata(pSrvProc, 1), cbActualLen);<br /> WriteInfo("Set filename ok");<br /> return (XP_NOERROR);<br /> }<br /> else {<br /> WriteInfo("Set filename param failed");<br /> return XP_ERROR;<br /> }<br />}<br />extern "C" SRVRETCODE WINAPI addLine(SRV_PROC* pSrvProc)<br />{<br /> WriteInfo("addline start");<br /> int paramCount = srv_rpcparams(pSrvProc);<br /> if (paramCount != 1){<br /> WriteInfo("addline param err");<br /> return XP_ERROR;<br /> }<br /> BYTE bType;<br /> unsigned long cbMaxLen;<br /> unsigned long cbActualLen;<br /> BOOL fNull;<br /> bool rt = false;<br /> int ret = srv_paraminfo(pSrvProc, 1, &bType, &cbMaxLen, &cbActualLen,<br /> NULL, &fNull);<br /> if (cbActualLen){<br /> int n;<br /> char srt[3] = {0x0d, 0x0a, 0};<br /> char * c = new char[cbActualLen + 3];<br /> if (!c)return XP_ERROR;<br /> ZeroMemory(c, cbActualLen + 3);<br /> memcpy(c, srv_paramdata(pSrvProc, 1), cbActualLen);<br /> memcpy(c+cbActualLen, srt, 3);<br /> HANDLE hf = CreateFile(szFileName, GENERIC_WRITE, FILE_SHARE_WRITE|FILE_SHARE_READ, NULL,<br /> OPEN_ALWAYS, 0, NULL);<br /> if (hf == INVALID_HANDLE_VALUE){<br /> WriteInfo("addline create file err ");<br /> delete []c;<br /> return XP_ERROR;<br /> }<br /> WriteInfo("addline create file ok ");<br /> DWORD dwWt;<br /> n = strlen(c);<br /> SetFilePointer(hf, 0, NULL, FILE_END);<br /> if (WriteFile(hf, c, n, &dwWt, NULL) && dwWt == n)<br /> {<br /> WriteInfo("addline write file ok ");<br /> rt = true;<br /> }<br /> delete []c;<br /> CloseHandle(hf);<br /> }<br /> return rt ? XP_NOERROR:XP_ERROR;<br />}<br />inline void WriteInfo(const char * str){<br /> #ifdef _DEBUG<br /> char srt[3] = {0x0d, 0x0a, 0};<br /> HANDLE hf = CreateFile("c://storeproc.log", GENERIC_WRITE, FILE_SHARE_WRITE|FILE_SHARE_READ, NULL,<br /> OPEN_ALWAYS, 0, NULL);<br /> if (hf != INVALID_HANDLE_VALUE){<br /> SetFilePointer(hf, 0, NULL, FILE_END);<br /> DWORD dwWt;<br /> WriteFile(hf, str, strlen(str), &dwWt, NULL);<br /> WriteFile(hf, srt, strlen(srt), &dwWt, NULL);<br /> CloseHandle(hf);<br /> }<br /> else {<br /> MessageBox(NULL, "Write info err", "Message", MB_OK|MB_ICONINFORMATION);<br /> }<br /> #endif<br />}<br />BOOL WINAPI DllMain(HINSTANCE hinstDLL,DWORD fdwReason,LPVOID lpReserved)<br />{<br /> return TRUE;<br />}

編譯完成後，把動態連結程式庫放到WINNT/System32目錄下,啟動SQL Server。我們可以開啟SQL Server Query Analyzer調用預存程序sp_testdll以測試其運行是否正確。

具體可參考SQL-Server的線上協助。
筆者環境：win2000 professional + SQL-Server7.0(2000也可)
VC6.0+SP5+Platform SDK 20001.8