Call an external dynamic connection library in a stored procedure (ms SQL Server7.0/2000 environment)

Question:
Generally, we need to perform some operation based on the database record changes. We are used to constantly query tables in programs to determine whether there are new records. This results in high resource consumption. To improve this efficiency, I want to create a trigger in the table and call the external dynamic Connection Library in the trigger to notify the application through messages or events. In the master's stored procedure, the best way to call the external dynamic connection library is to call the master's stored procedure in the trigger.

Download source code: 14 khttp: // www.vckbase.com/vckbase/vckbase12/vc/nonctrls/database_07/storeproc.zip

Note: vc6 requires the installation of a newer Platform SDK to successfully compile this code. VC. Net can directly compile this code. You also need to connect to opends60.lib
In order to allow friends who do not have a newer Platform SDK to compile this example, the SRV. h and opends60.lib in VC. Net have been put into the compressed package.

Program Implementation:
Let's call the external DLL (storeproc. dll) functions setfilename and addline in a stored procedure.

The stored procedure is as follows (which must be stored in the master database): Create procedure sp_testdll

Exec sp_addextendedproc 'setfilename', 'storeproc. dll '-- declare a function
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 -- call the setfilename function. The parameter is -- szfilename;
If @ RT = 0
Begin
Select @ sztext = 'Welcome 0'
Exec @ RT = addline @ sztext -- call addline
Select @ sztext = 'Welcome 02'
Exec @ RT = addline @ sztext

End
Exec sp_dropextendedproc 'setfilename'
Exec sp_dropextendedproc 'addline'

DBCC setfilename (free)
DBCC addline (free)

Implementation of Dynamic Connection Library: this dynamic Connection Library is different from the common one. The dynamic connection library should be placed in the SQL Execution directory, or directly under the System32 directory of window, and the SQL-Server

 

# Include <windows. h> <br/> # include <srv. h> // Add this. hfile <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 (Pa Ramcount! = 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/> 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 [cbactua Llen + 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_0000_write | file_0000_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_wash_write | file_wash_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/>}

After compilation, place the dynamic link library in the WINNT/system32 directory and start SQL Server. We can enable SQL Server Query analyzer to call the Stored Procedure sp_testdll to test whether it runs correctly.

For more information, see SQL Server online help.
Author environment: Win2000 professional + SQL-Server7.0 (2000 can also)
Vc6.0 + SP5 + platform sdks 20001.8