Solved

How to open a socket connection based on an insert TRIGGER?

Posted on 2007-03-19
14
242 Views
Last Modified: 2013-11-30
Hello,

Would it be possible to open a socket connection and send few data based on an insert TRIGGER?
My client would basically like to send the ID of  a product via socket connection when a new product is inserted into the SQL database.
DATABASE:SQL server 8 Database


Thanks for your help
Arnold
0
Comment
Question by:arnololo1
  • 6
  • 4
  • 2
14 Comments
 
LVL 23

Expert Comment

by:Christopher Kile
Comment Utility
There aren't any socket I/O primitives in Transact-SQL.  For SQL Server 2000, you'll need to use the OLE interface to access a COM object that handles socket I/O (I THINK WSOCK32.DLL is such an object, but you'll need to research it).  In SS2005, you can link in .NET assemblies, so you would use the NetClient interface, I would expect.
0
 

Author Comment

by:arnololo1
Comment Utility
Thanks for your answer,  your idea sounds good. Could you give me more detail on how to use the OLE interface to access a COM object that handles socket I/O with SQL. I am pretty familiar with sql server, store procedure, but I have no idea on how to do what you described.
The first thing would be, How do you access the DLL from  a  TRIGGER?

Any sample code would be great.

Thanks
0
 
LVL 23

Expert Comment

by:Christopher Kile
Comment Utility
As it turns out, I can't find any COM DLL's that encapsulate the Windows Socket API.  There may be some out there somewhere, I just don't know where they are.  Your only other option is to write an extended stored procedure in C/C++ (which is the required interface for dealing directly with the WinSock libraries) or to write a COM object yourself (either in C/C++ or, if you're truly brave, in VB6) that will encaspulate the WinSock API.  Do you have any experience with Windows Sockets?
0
 

Author Comment

by:arnololo1
Comment Utility
I have absolutely no experience with Windows Sockets and I have also never written a store procedure in c/c++ .  And I do really not see myself writting a COM object.
So if you have any code regarding the store procedure in C/C++ to do what I need, I would greatly apreciate.

Thanks
0
 
LVL 23

Expert Comment

by:Christopher Kile
Comment Utility
I don't.  My suspicion is that you'll have to go buy it somewhere.  Also, if you have no experience with Sockets, you're gonna have a steep learning curve.  Sockets are no longer in common use because of this, and because other protocols are simply easier to use.

Now, have you ever written an extended stored procedure?  Do you know C/C++?

If your answer to the last two questions is No, you have some serious shopping to do, because it is highly unlikely that you're gonna find what you want for free.  Sorry to be the bearer of bad news, and I hope someone makes a liar of me for your sake, but if I were you I'd be thinking of some way to talk my client out of this.
0
 

Author Comment

by:arnololo1
Comment Utility
Thanks for thos explanation.
I know C/C++  but not enough to write something to open a socket connection.

As far as buying a  component, that would be fine, but I could not find any.

My client want to use the socket connection because as soon as a new product is entered is the sql database, he needs to print a label from his ERP (QAD). His ERP uses a prelude database, the ODBC driver that exists to connect both database creates a lots of problem of connection. Therefore, the client would like me to open a socket connection to send the ID of the new product to his ERP. On his side he would have an open socket that would only "listen".

If you have any other idea of how we could do that, please let me know.
Thanks
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 23

Expert Comment

by:Christopher Kile
Comment Utility
Does the printing of the label truly need to be instantaneous??  Because if not, it would be MUCH easier to send him a file using DTS which he could import and use to generate the label list.

Failing that, I'd take another look at that ODBC driver, which you could use to do a remote database update.
0
 

Author Comment

by:arnololo1
Comment Utility
The printing does not need to be instantaneous but at least within few seconds of the insert.
Is it possible to use a DTS within a TRIGGER?
Can the DTS create a text file?
Whe you say send a file using DTS, do you mean FTP the file or is there other way to send the file?

Thanks very much for your help
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
Comment Utility
>>Is it possible to use a DTS within a TRIGGER?<<
Yes, but not recommended.
>>Can the DTS create a text file? <<
Yes.  Use the FileSystemObject in an ActiveX Script Task.
0
 
LVL 23

Assisted Solution

by:Christopher Kile
Christopher Kile earned 250 total points
Comment Utility
Wait a minute....

acperkins,

is there any impediment to accessing a COM object using the OLE_* procedures within a trigger?

arnololo1,

do you have Visual Studio 2003?  It just occurred to me that the "socket" you want to open isn't called a socket in .NET - it's called a TcpClient.  

Ask your client if you can open a TCP port on his machine and shove through a set of bytes over without any surrounding protocol such as HTTP.  If so, building a COM object in VS 2003 using VB is pretty easy (all it takes is adding a GUID to the assembly definition, creating the DLL, then registering the DLL, while making sure that every public access method uses COM-compatible types).  Your COM object will have one method that accepts your product ID.  It will then create a TcpClient, connect to the host and port of your customer's server, then the TcpClient.GetStream() method will provide a read-write data stream to that host and port.  Write the data, close the stream, close the port, message is sent.

Naturally, the devil is in the details; however, those details can be worked out if you have VS 2003.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>is there any impediment to accessing a COM object using the OLE_* procedures within a trigger?<<
There is an impediment to running any time consuming action in a Trigger.  Simply Triggers should not be used for that. Period.

But don't take my word for it:
How can I execute a DTS package from a trigger
http://www.sqldts.com/219.aspx
0
 
LVL 23

Expert Comment

by:Christopher Kile
Comment Utility
>There is an impediment to running any time >consuming action in a Trigger.  Simply Triggers >should not be used for that. Period.

Very true.

arnololo1,

Is the insertion of a new Product ID being done in a stored procedure?  If so, you should move the transmission of the ProductID to your customer into the stored procedure.  If you're not using a stored procedure, you should consider building one.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now