Sometimes, due to the lack of Functions of SQL Server, we need to use external programs to implement it. Because of its language independence and powerful functions, com becomes the first choice for the interface with SQL Server, in addition, SQL Server itself provides system stored procedures for implementing interfaces with COM. These related system stored procedures are:
Sp_oacreate creates an OLE object execution entity in the SQL Server execution entity.
Sp_oamethod: Call the OLE object Method
Sp_oagetproperty: Get the attribute value of the OLE object
Sp_oasetproperty sets the OLE object property to a new value.
Sp_oageterrorinfo get OLE automation error information
Sp_oastop stops the execution environment of the OLE Automation pre-stored program on the server.
Sp_oadestroy destroys the created OLE object. Knowing the possibility of implementation, how should we implement it? Follow me below:
1. Create com programs in other programming languages (using Delphi as an example)
1. Close all projects, create a new project, save the default unit file unit1.pas as umain. Pas, and save the default project file project1 as ptestcom. Of course, you can also save the file name you want, but remember that the project name set here is the name of the COM to be called later;
2. Choose File> New> other… from the menu ..., Open the new project window, select the ActiveX page, and find the Automation Object (because the OLE Automation server called by SQL Server must support the idispatch interface, so select it ), click "OK" and enter the class name (testobject here) in the pop-up sound export box. If the others remain unchanged, press "OK" to complete the operation;
3. After completing the preceding steps, a database type window named testcom will pop up, on the left side of the window, we will see the testobject class created above and an interface class called itestobject. Select itestobject and click New Method on the toolbar to create a new method, name it testfunc, and then add two input parameters param1 and param2 (type is BSTR, modifier is [in]) and one output parameter paramout (type is variant *, modifier is [out, retval]), return type remains unchanged, and is hresult. Click the refresh button on the toolbar, press save, save it as utestcom. PAS and utestcom. TLB, and close the window;
4. Now, in the utestcom unit file, you will see that a function has been generated:
Function ttestobject. testfunc (const param1, param2: widestring): olevariant;
Begin End;
Add the following code (of course, you can use your own method in this function ):
Result: = param1 + param2; // Add the incoming strings and return
Click Save, run it, and check whether the preceding steps are completed correctly. 2. Call in SQL Server
After the com program has been created and confirmed to be correct, we can use the system stored procedures mentioned above to call the program. For details, refer to the example below. The details are included in the Code, do not repeat it. Create procedure check_userathor -- defines a stored procedure and accepts two parameters.
@ C_id varchar (20 ),
@ C_name varchar (20)
As
Declare @ object int
Declare @ err int
Declare @ return_value varchar (255)
Declare @ SRC varchar (255), @ DESC varchar (255)
/** // * Construct the com instance and call the ptestcom. testobject created above */
Exec @ err = Master .. sp_oacreate "ptestcom. testobject", @ object out If @ err <> 0/** // * token creation failure */
Begin
Exec sp_oageterrorinfo @ object, @ SRC out, @ DESC out
Select hR = convert (varbinary (4), @ ERR), source = @ SRC, description = @ DESC
Return
End
/** // * Using the com method testfunc declared above, @ return_value is the returned value, and @ c_id and @ c_name are the input parameters */
Exec @ err = sp_oamethod @ object, 'testfunc', @ return_value out, @ c_id, @ c_name
If @ err <> 0
Begin
Exec sp_oageterrorinfo @ object, @ SRC out, @ DESC out
Select hR = convert (varbinary (4), @ ERR), source = @ SRC, description = @ DESC
Exec sp_oadestroy @ object
Return
End
Print 'Return result: '+ (@ return_value) -- The returned value is displayed here.
/** // * Release the com instance */
Exec sp_oadestroy @ object
Go Iii. Call
After the above stored procedure is created correctly, we can call it directly. In the example above, if we enter '123456', 'company ', the returned result should be the 123company string. Iv. Inference
The above com program is actually suitable for calling other programs that can communicate with COM. For example, in ASP, you can also directly call the COM component. The method is as simple as it is not described in detail, if you are interested, you can discuss with me.
Postscript
The above example has a reference to an article widely circulated on the Internet. However, the article has been transferred in and out, and it is not so miserable. |
