How to perform socket operations in database trigger

Source: Internet
Author: User
Tags sql server query
After you execute the insert, update, and delete operations, you can use triggers to capture them. I use socket to upload the data that I want to change to another machine. How do I perform the socket operation in the database trigger?
Compile a DLL. This dll is responsible for transferring changed data to another machine using socket, and then calling this DLL in the trigger. The call method and DLL example are here:
Call an external dynamic connection library in a stored procedure (ms SQL Server7.0/2000 environment)
Author: ac952_z_cn
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 size: 14 KB

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 a friend who does not have a newer Platform SDK to compile this example, he has put srv. h and opends60.lib in vc.net 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 01
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 re-run SQL-server # include <windows. h>
# Include <srv. h> // Add this. h file.

# 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_pai_write | file_pai_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_1__write | file_1__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;
}

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

VC knowledge base test environment: Win2000 professional + SQL-Server 7.0 + vc.net

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.