To extend SQL Server functionality with COM Auto Service

The T-SQL programming language of SQL Server is powerful in data storage and recovery, but is less functional in terms of interaction with systems outside of the SQL Server database. However, we can overcome this limitation with SQL Server's built-in COM automation environment, which enables users to automate COM objects in stored procedures.

With 7 extended stored procedures in SQL Server 7.0 and SQL Server 6.5, you can extend SQL Server functionality through off-the-shelf COM objects, such as your own or office. SQL Server also provides an error-handling mechanism that can write error messages to the SQL Agent log. With COM automation, you can also set SQL Server 6.5 with Microsoft's Exchange Server, Index Server, and other software that can be controlled by COM automation operations to introduce an object automation environment. It was originally called OLE. The name of the object operation changed over time, but the automatic operating environment in SQL Server 7.0 was unchanged compared to SQL Server 6.5, so Microsoft's documentation still referred to this feature as an OLE operation rather than a COM operation, in the lookup SQL This is especially true when the server online brochure (BOL) is in use. Let's talk about how to use SQL Server's COM automated operation stored procedures and how COM automated operations can help us solve real-world programming problems.

Details of COM operations

Table 1 lists the 7 extended stored procedures for COM operations in SQL Server. When automating a COM object, you need to first establish an instance of a COM object by calling sp_OACreate, and then complete the tasks that need to be done through a series of sp_OAGetProperty, sp_OASetProperty, and sp_OAMethod calls , you also need to call sp_OADestroy to release the object after you complete the operation on the COM object. When studying each stored procedure in detail, note two important questions.

First, you must provide all the parameters of the call, because the automatic action feature does not support well-known parameters, and if you cannot use a detailed parameter, you need to pass a null as a placeholder, and second, each call returns an HRESULT of the integer type, which is 0 if the call succeeds. Later, we'll discuss how to handle the return value as non.

Stored Procedure Description

sp_OACreate establish an instance of an automatic action object

sp_OADestroy frees an instance of an object

sp_OAGetErrorInfo Get error description information from the HRESULT returned by other processes

sp_OAGetProperty store An object's properties in a result set or in a local variable

sp_OASetProperty change the value of an object property

sp_OAMethod the method of executing the object, passing parameters to the method, and getting the return value

sp_OAStop shutdown SQL Server's automatic operating environment

Table 1:sql Server COM auto-operation stored Procedures

The COM operation must begin with the call to the sp_OACreate stored procedure, as shown in the following syntax format:

sp_OACreate ProgID | CLSID, Objecttoken out. Put,

The first parameter is the program ID (progid━━ an application name. A string in the form of a class name, for example:

Excel.Application,) or a class ID (Clsid━━ the globally unique ID of a nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn form) that identifies the COM object you want to create an instance of.

Where possible, I recommend using the ProgID parameter because it is easy to input and memory. You will find that only a very small number of the objects that you want to automate are not rogid, and if you occasionally encounter such objects, you will only use the CLSID. The second variable, Objecttoken, is also an integer variable, with the tag of an object pointing to the handle and pointer to an object created by SQL Server, and we need to use this returned object tag to determine the object in the subsequent automatic operation of the object. The final context variable is optional, and you can force the created object to use some sort of automatic mechanism. If the value is 1, the object is required to be in an ActiveX DLL file, the value is 4, the object is required in the ActiveX EXE server, and if the default value is 5, any automatic action can be used. Here we recommend using the default option without providing a proper value for the context parameter. The following command, called Op_oacreate, creates an instance of Microsoft's Excel program:

Declare @Object int

Declare @RetVal int

Exec @RetVal =sp_oacreate ' Excel.Application ',

@Object OUTPUT

After you create an object, you need to get some of its properties. To get these properties, you can use the following syntax to tune

sp_OAGetProperty:

sp_OAGetProperty objecttoken, propertyname[, PropertyValue OUTPUT] [, the value of the first parameter objecttoken is the value returned by the sp_OACreate, Parameter PropertyName is the property we want to get.

There are several options in getting this value, if the property is a single value, it can be stored in a variable or as a result set of a single line or field, and if the property value is a one-dimensional or two-dimensional array, it must be a result set; If the value of the property is more than a two-dimensional array, the SP _oagetproperty cannot return its value, an error occurs. To return a result set, simply do not specify the value of the PropertyValue parameter (if you want it to have a value to use the index argument, NULL is assigned to it.)

Otherwise, you should assign propertyvalue a value of the appropriate type, and be sure to mark the parameter as output. If you are accessing a property that is a collection, you need to use the index parameter to specify a specific number in the collection. If an object's property is another object, the object should be stored in an integer variable, and sp_OAGetProperty returns an OBJECT tag, but not the same as the sp_OACreate return. We can use this object tag to automate any objects returned by the stored procedure. The following command calls sp_OAGetProperty to deposit a property value named DefaultFilePath into the variable @dfp:

Exec sp_oagetproperty @Object, ' DefaultFilePath ',

@DFP OUTPUT

You can change the property values of an object by using the sp_OASetProperty stored procedure in the following format:

sp_OASetProperty Objecttoken, Propertyname,newvalue [, index]

The first parameter objecttoken is returned by sp_OACreate, and the parameter PropertyName is the property name of the object to be changed, and the NewValue parameter is the new variable that you want to assign to the property, either a variable or a literal value. If you set an attribute value as an object of a collection, you can use the optional Index argument to specify a specific location for the collection. The following command calls sp_OASetProperty to set the property named FixedDecimalPlaces to 6:exec sp_OASetProperty @Object, ' fixeddecimalplaces ', 6

You can call the sp_OAMethod stored procedure to execute an object using the following syntax:

sp_OAMethod objecttoken, MethodName [, ReturnValue OUTPUT] [,

sp_OAMethod is the most flexible, and thus the most complex, automatic stored procedure, and we can even call a property with it like calling a method, and we can get a return value, and of course we can use sp_OAGetProperty to accomplish this task. The first parameter of the stored procedure Objecttoken is the object token returned by sp_OACreate, and the parameter methodname is the name of the method that you want to execute, if the method has a return value, The next parameter returnvalue should be a variable of the appropriate type that contains the return value of the method, or null as a placeholder if the return value is a one-dimensional or two-dimensional array, and the procedure returns a result set. The stored procedure cannot return an array of more than two dimensions as the result set, in which case SQL Server will be faulted. If the method does not have a return type.

If you call a method that requires parameters, you need to supply them when you call sp_OAMethod. If the method allows parameters to be supplied sequentially, each parameter is listed in the order in which it is required, with commas separating each parameter, or a variable or literal variable as a parameter. If you need to use well-known parameters, SQL Server also provides the appropriate mechanism to simply use the @ variable name = variable value in the form of the required variables can be listed. Note that the variable name is not treated as a local variable because of the @ prefix, and SQL Server resolves the @ when the stored procedure sp_OAMethod is invoked, so even if there is a parameter named hostname in the method being invoked, You can still use local variables with the name @hostname.

Here are two examples of calling sp_OAMethod. The first example calls a method named CentimetersToPoints, which accepts only one argument provided in the @cmval variable, and the returned value is stored in the variable @retval. The second example calls a method named MailLogon, which accepts three optional variables, in this case, by accepting two variables by name, setting name to String "MyUserName", and setting password to a string:

Exec sp_OAMethod @Object, ' centimeterstopoints ', @RetVal OUTPUT, @CMVal

Exec sp_OAMethod @Object, ' MailLogon ', NULL, @Name = ' MyUserName ',

After you no longer use an object, you need to invoke the stored procedure sp_OADestroy the sp_OADestroy of the object by invoking the following syntax Objecttoken

Invoking the sp_OADestroy stored procedure frees the object specified by the parameter objecttoken, while also releasing the memory and other resources used by the object. Here is a command to invoke sp_OADestroy:

Exec sp_OADestroy @Object

Note that the data types in T-SQL are not one by one corresponding to other programming languages, and can be faulted when calling a method that requires a particular data type. The Data Type Conversion toolbar can bring SQL Server data.

Error handling

As mentioned earlier, if a call to a stored procedure succeeds, an HRESULT value of 0 is returned, and the other HRESULT value means that an error has occurred. To determine a Non-zero HRESULT value, you can pass the HRESULT value:

sp_OAGetErrorInfo [Objecttoken] [, source output] [, description output]

The first parameter objecttoken is the object token returned by sp_OACreate.

The following four parameters return an error message. Source is the application or library that generated this error message, description is the description of the error, and if there is a Help file, the HelpFile is the path to the Help file. These three parameters are both signed or unsigned character data, and sp_OAGetErrorInfo intercepts the returned value based on the size of the variable being defined. The last parameter helpid is the index number of the specific error in the Help file. The following command invokes sp_OAGetErrorInfo to obtain more detailed information about an error:

Declare @Source varchar (MB), @Description varchar (255), @HelpFile

Exec sp_OAGetErrorInfo @Object, @Source output, @Description output,

The SQL Server online manual also provides an example of a sp_displayoaerrorinfo stored procedure that can invoke sp_OAGetErrorInfo to organize the returned value into a formatted string to write the information to the log file.

For more information about Sp_displayoaerrorinfo, see the sidebar, in addition, invoking the sp_OAStop stored procedure can turn off SQL Server's COM automation environment without any parameters. Turning off an automated operating environment is not required in most cases, the automatic operating environment is automatically turned on the first time the sp_OACreate is invoked, and the automatic operating environment shuts down automatically when SQL Server shuts down. If a stored procedure is automating an object while another procedure calls sp_OAStop, we do not recommend calling sp_OAStop in the program, which can be invoked only through a query window when debugging a process that is not running.

Using COM automation in real-world work

Now that we've learned how to use every COM automated stored procedure, let's talk about a comprehensive example of how to apply them. Listing 1 is a process named Sp_openwordifcoprocavailable, in which we created an instance of Microsoft Word with sp_OACreate and then used the SP_ Oagetproperty to get the MathCoprocessorAvailable property of Word, if sp_OAGetProperty returns 1, sp_ Openwordifcoprocavailable returns the object tag of the Word object to the calling procedure;

Sp_openwordifcoprocavailable closes word and returns 0. In order to save the layout, we only call the error handling process once, in the actual application, you should call the automatic operation of the stored procedure after the error-handling process. Note that in order to automate Word, you should install Word on the machine where SQL Server is installed.

Listing 1: An example of how Word is automatically manipulated

Create Procedure sp_openwordifcoprocavailable as



return 0

End

Exec @hr = sp_OAGetProperty @Object, ' mathcoprocessoravailable ', @RetVal









return 0

End

Return @Object

If you need to automate the use of a COM object written in Visual Basic, it is fairly easy to debug its interoperability with SQL Server. We need to install Visual Basic on the machine running SQL Server, load the COM project in the Visual Basic Editor, set some breakpoints, and then compile and run the COM object. When a stored procedure automates the object, when you run to a breakpoint, the editor automatically switches to debug mode, and we can debug the COM object as you would any other Visual Basic program. If you want to implement more control over the debugging process, you can use the T-SQL Debugger for VB plug-in, which performs the save in a step-by-step manner.

Also, what else can we do to apply COM automation in SQL Server? Here's a real example of how I used SQL Server's powerful COM automation functionality. Not long ago, I need to use one SQL Server stored procedure to communicate through named pipes, while SQL Server does not provide a mechanism for programmatically opening and using Named pipes, I have a VB example and library that can communicate using named pipes, so I make this library file a class , and an ActiveX DLL file is created, and the DLL is automatically manipulated from the stored procedure.

Another time, I need to copy some files and database tables. With SQL Server replication, this data can easily be replicated, but it is much harder to copy files, and the NT directory synchronization function is weak and does not meet the requirements. Although I can also save the copy command to a character variable, and then pass the variable to xp_cmdshell, I will encounter the limit of the length of the command. More inconvenient, if there was an error in the copy process, I couldn't easily judge where the error occurred, so I wrote an ActiveX DLL and handled it automatically to work on the copy of the file.

Again, I need to perform a link between SQL Server 6.5 and Index Server 2.0 before completing the query task, if Windows indexing Services and SQL Server with ADO are used 7.0, it's very easy to do this, but it's much more difficult if you're not using these products.

First, you need to write an ActiveX DLL that executes the Index Server query object Ixsso.dll, automate it, get information from the Index Server directory, and return the information to the stored procedure in a single method.

The data is then saved to a temporary table and then connected to it. COM automatic operation once again helped me solve the problem. Performing COM automation in a stored procedure can almost make us do whatever we want to do. COM automation in SQL Server 2000 does not change, so code written in this way can still be used in the future.