четверг, 11 августа 2011 г.

Microsoft SQL Server CLR stored procedure and Java web-service

Simple JAX-WS web-service with method:

public String getOSVersion(String S)
    {
        return System.getProperty("os.version");
    }

работающий под 7-й IBM WebSphere.

Как обратиться к нему из MSSQL?
Довольно просто:

1) Создаем в VS C# database project
2) Создаем Web reference (Project->Add->Web reference) к WSDL:
http://127.0.0.1:9080/EAService2/ImageService/WEB-INF/wsdl/ImageService.wsdl
Присваиваем имя ImageService
3) Код CLR функции:

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString getOSVersion(SqlString s)
    {
        ImageService service = new ImageService();
        String res = service.getOSVersion(s.ToString());
        return new SqlString(res);
    }
};
4) Project-Properties->Build events->post-build:
"C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force "$(TargetPath)"

5) Build :-)

6) SQL Install scripts:

-- clear
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'FS' and name = 'getOSVersion')
drop function getOSVersion
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'ImageService.XmlSerializers.dll')
DROP ASSEMBLY [ImageService.XmlSerializers.dll]
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'ImageService')
DROP ASSEMBLY [ImageService]
GO

-- install
CREATE ASSEMBLY [ImageService] from 'C:\Work\SQL\SqlServerWSTest.dll' WITH PERMISSION_SET = UNSAFE 
GO
CREATE ASSEMBLY [ImageService.XmlSerializers.dll] from 'C:\Work\SQL\SqlServerWSTest.XmlSerializers.dll' WITH PERMISSION_SET = UNSAFE 
GO 
create function getOSVersion (@s nvarchar(100))
returns nvarchar(100)
AS EXTERNAL NAME ImageService.UserDefinedFunctions.[getOSVersion]
GO
Test:
select dbo.getOSVersion('123')
Result:
6.1 build 7600

Комментариев нет:

Отправить комментарий