About calling COM component _ MySQL in a SQL-SERVER

Source: Internet
Author: User
If you are interested in calling the IDispatch-based COM component in SQL server, you can query the usage of storage procedures such as sp_OACreate, sp_OAMethod, and sp_OADestroy in SQL help. The following is some SQL code in a small project of text message alarm. the alarm information is inserted into SQL-server through various software, then the component is called through the trigger, and the short SQL Server is sent.

SQL server can call the COM component based on IDispatch

If you are interested, you can check the usage of storage procedures such as sp_OACreate, sp_OAMethod, and sp_OADestroy in SQL help.

The following is some SQL code in a small project of text message alarm. the alarm information is inserted into SQL-server through various software, and then the component is called through the trigger, and sends a text message to the specified mobile phone to enable automatic alarm.

// Test the database trigger

Alter trigger message_Trigger1

ON dbo. message

For insert/*, UPDATE, DELETE */

AS

/* If update (column_name )...*/

Begin

Declare @ PhoneNum nvarchar (50)

Declare @ Content nvarchar (140)

Declare @ MessageId nvarchar (70)

Declare @ index int

Declare @ hr int

Declare @ object int

Select @ PhoneNum = phone_num, @ Content = Content, @ MessageId = message_id from inserted

Select @ index = 1

/* Call COM to send a text message */

Begin

EXEC @ hr = sp_OACreate '{26850d424862c-44ff-9232-282937f2ca4b}', @ object OUT

If @ hr = 0

Begin

Exec @ hr = sp_OAMethod @ object, 'sendmsg ', NULL, @ Content, @ PhoneNum, @ index, @ MessageId

Exec sp_OADestroy @ object

End

End

End

The code here can be said to be okay, but it can also be said to be a big problem.

The key lies in the SendMsg method of the component. why? I can introduce several issues that I have encountered.

There are two main points

1st: whether the COM component is an in-process component, and whether the internal code of the component is strong enough

2nd: create and destroy components and component methods as quickly as possible

Let me explain the above two points.

If the COM component is an in-process component, it means that the component is loaded by SQL-server. if the code is not robust enough, it will be suspended and crashed due to the component itself, it will directly affect the entire SQL-server, so the situation is very serious. this kind of error is enough to happen once, if it happens to blow up on the customer's face ......

The solution is as follows: first, make sure that your component code is strong enough to be no longer strong. In addition, try to prevent components from being in the process. if the component is already a DLL, it should be handed over to COM Catalog for management. This solves the SQL server issue caused by component crashes and hangs.

Second, you need to make the creation, method call, and destruction of your components so fast that they can be completed almost instantly. Otherwise, if a massive amount of data comes in and your trigger cannot be reflected, your data may be rolled back, and the data is very important, then you will be waiting for the Fire to L 5555 ~~~~~

If you have to use this method, you need to complete the above 2 points, and it is best to enable the COM object pool. If the method takes a long time to return and cannot be optimized, remove the methods and execute these actions in another program so that the COM method can return immediately.

All in all, in a word, in the system integration, if a few developers did not discuss the interface, and are using a SQL-SERVER database, then the operation of such data, it seems that using this method is the best remedy.

I personally feel a little arrogant, although very sharp, but it will inevitably hurt myself

Gentle poison 2005-12-30

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.