Calling COM objects in SQL

Object
Creating an OLE object instance in SQL Server sometimes reminds us of the COM object in the system while we are executing the stored procedure in the database. At this point we can take the SQL system stored procedure sp_OACreate, the call to this stored procedure to have certain permissions, only members of the sysadmin fixed server role can execute sp_oacreate.

Grammar
sp_OACreate ProgID, | Clsid
Objecttoken OUTPUT
[, Context]

Parameters
ProgID

is the program identifier (ProgID) of the OLE object to create. This string describes the class of the OLE object in the form of the following:

' OLEComponent.Object '

OLEComponent is the name of the component of the OLE Automation server, object is the name of the OLE. The specified OLE object must be valid and must support the IDispatch interface.

For example, SQLDMO. SQL Server is the ProgID of SQL-DMO SQL Server objects. The SQL-DMO component name is valid for the Sqldmo,sqlserver object and, like all SQL-DMO objects, the SQL Server object supports Idispatch.

Clsid

is the class identifier (CLSID) of the OLE object to be created. This string describes the class of the OLE object in the form of the following:

' {nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn} '

The specified OLE object must be valid and must support the IDispatch interface.

For example, {00026ba1-0000-0000-c000-000000000046} is the CLSID of a SQL-DMO SQL Server object.

Objecttoken OUTPUT

Is the returned object token and must be a local variable with a data type of int. The object token is used to identify the created OLE object and will be used when other OLE Automation stored procedures are invoked.

Context

Specifies the execution context in which the newly created OLE object will run. If specified, this value must be one of the following values:

1 = in-process (. dll) OLE server only
4 = local (. exe) OLE server only
5 = Both the in-process OLE server and the local OLE server can be

If not specified, its default value is 5. This value is passed as a dwclscontext parameter when the CoCreateInstance is invoked.

If an in-process OLE server is allowed (by using the context value 1 or 5 or without specifying a context value), the server will have access to the memory and other resources owned by SQL Server. In-process OLE servers can corrupt SQL Server memory or resources and cause unpredictable results, such as SQL Server access violations.

When the context value is specified as 4 o'clock, the local OLE server cannot access any SQL Server resources, and therefore cannot destroy the SQL Server's memory or resources.



Indicates that the parameters for this stored procedure are specified by position, not by name.


Return code value
0 (successful) or not 0 digits (failure), is an integer value of the HRESULT returned by the OLE Automation object.

Let's take a look at a concrete example, in which we'll call a COM component of our own, which is the function of providing a read-write serial number.

CREATE PROCEDURE [dbo]. [SP_MYCHECKSN]
@ENCRYPTION nvarchar (255) out, @type int out
As
DECLARE @object int
DECLARE @hr int
DECLARE @property varchar (255)
DECLARE @return varchar (255)
DECLARE @src varchar (255), @desc varchar (255)

--Create a COM object
EXEC @hr = sp_OACreate ' securitysn.csecurity ', @object out
IF @hr <> 0
BEGIN
Set @ENCRYPTION = ' Err '
Return
End

--calling methods
declare @aa nvarchar (255)--Final return value
EXEC @hr = sp_OAMethod @object, ' getdisksn ', @aa out
IF @hr <> 0
BEGIN
Set @ENCRYPTION = ' Err '
Return
End
Else
Begin
Set @Type = 8--Indicates registered read mode
End

---------------------
If @aa = '
Begin
--Read it again.
EXEC @hr = sp_OAMethod @object, ' Getsnbyapi ', @aa out
If @hr <>0
Begin
Set @ENCRYPTION = ' Err '
Return
End
Else
Begin
Set @Type = 5--Indicates which way to read the registration number
End
End

--Destroy This object
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
Set @ENCRYPTION = ' Err '
Return
End
Set @ENCRYPTION = @aa
Go


To prevent users from modifying the code of a stored procedure. We can encrypt this stored procedure. But encrypted stored procedures can also be deciphered.