Arkadaşlar Merhaba,
Bu yazımda sizlere SQL Server üzerinde T-SQL ile nasıl web service işlemleri yapabileceğimizi anlatacağım.
Kimi zaman bazı entegrasyon işlerinde web service ile ilgili işlemlerimiz için C# üzerinde kod yazmak gerekir ve hem zaman alır hem de can sıkıcı bir durum ortaya çıkar.
Bunun yerine bu işi T-SQL ile de kolayca çözebiliriz. İster web servis üzerinden veri alabilir istersek de veri gönderebiliriz.
Bununla ilgili olarak Vishal Seth adlı arkadaş bir stored procedure geliştirmiş. Ancak bu kodu direk çalıştırdığımızda bir takım hatalar veriyordu ve kodu düzeltmek durumunda kaldık. Şu an için SQL Server 2008 R2’de çalışıyor. Ancak SQL Server 2014 üzerinde çalışmıyor. SQL Server 2014 üzerinde sorunu çözdüğümüzde ilerleyen günlerde sizlerle burada paylaşacağım.
Kodu ilk defa çalıştırdığınızda izinlerle ilgili bir takım hatalar alabilirsiniz. İzinleri verdikten sonra kod çalışacaktır.
Aşağıdaki T-SQL kodu ile Stored Procedure’nüzü oluşturabilirsiniz.
[sourcecode language="sql" title="Stored Procedure for Web Service"]
CREATE Proc [dbo].[ebs_GetWebServiceXML]
(
@URI varchar(2000) = '',
@methodName varchar(50) = 'POST',
@requestBody varchar(max),
@SoapAction varchar(255),
@UserName varchar(255)='',
@Password varchar(255)='',
@resultXML XML output
)
as
declare @envelope nvarchar(max), @responseText varchar(100), @Result int
IF @methodName = ''
BEGIN
select FailPoint = 'Method Name must be set'
return
END
set @responseText = 'FAILED'
DECLARE @objectID int
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255)
EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'Create failed',
MedthodName = @methodName
goto destroy
return
END
-- open the destination URI with Specified method
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'Open failed',
MedthodName = @methodName
goto destroy
return
END
--EXEC sp_OASetProperty @objectID, 'setTimeouts',resolveTimeout,connectTimeout,sendTimeout,receiveTimeout
EXEC sp_OASetProperty @objectID, 'setTimeouts','5000000','5000000','5000000','5000000'
-- set request headers
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'SetRequestHeader failed',
MedthodName = @methodName
goto destroy
return
END
-- set soap action
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'SetRequestHeader failed',
MedthodName = @methodName
goto destroy
return
END
declare @len int
set @len = len(@requestBody)
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'SetRequestHeader failed',
MedthodName = @methodName
goto destroy
return
END
-- send the request
EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'Send failed',
MedthodName = @methodName
goto destroy
return
END
declare @statusText varchar(1000), @status varchar(1000)
-- Get status text
exec sp_OAGetProperty @objectID, 'StatusText', @statusText out
exec sp_OAGetProperty @objectID, 'Status', @status out
IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml
CREATE TABLE #xml ( yourXML XML )
INSERT #xml ( yourXML )
EXEC @Result = sp_OAGetProperty @objectID, 'responseXML.xml'--, @resultXML OUT
SELECT @resultXML=yourXML
FROM #xml
destroy:
exec sp_OADestroy @objectID
[/sourcecode]
Peki kodu nasıl kullanacağız? Bununla ilgili olarak ben örnek bir web service hazırladım.
Şimdi burada Web Service’deki GetData metodunu kullanarak verileri çekeceğiz ve SQL Server’da tabloda göstereceğiz. Öncelikle web service’e bağlanıp verileri almamız gerekiyor. Bunun için yukarıda yazmış olduğumuz stored procedure’i kullanacağız. Stored Procedure’ı çalıştırınca XML olarak bize cevap dönecek.
[sourcecode language="sql" title="Stored Procedure'in Kullanımı"]
declare
@URI varchar(2000) = 'http://10.1.1.241/webservice.asmx',
@methodName varchar(50) = 'POST',
@requestBody varchar(8000) = '<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<GetData xmlns="http://10.1.1.241" />
</soap:Body>
</soap:Envelope>',
@SoapAction varchar(255)='http://10.1.1.241/GetData',
@UserName nvarchar(100)='UserName',
@Password nvarchar(100)='PassWord',
@XMLresult XML,
@XMLnew XML
exec ebs_GetWebServiceXML @URI,@methodName,@requestBody,@SoapAction,@UserName,@Password,@XMLresult output
[/sourcecode]
Gördüğünüz gibi output olarak yani cevap olarak bize xml bir sonuç dönüyor.
Peki bu XML sonucu nasıl sql tablosu’nda göstereceğiz? Aşağıdaki örnek kod ile XML’i tablo olarak gösterebilirsiniz.
[sourcecode language="sql" title="XML to SQL Table"]
SELECT
T.N.value('HISSE[1]','nvarchar(5)') as 'HISSE',
T.N.value('TARIH[1]','date') as 'TARIH',
T.N.value('FIYAT[1]','float') as 'FIYAT'
FROM @XMLresult.nodes('//Data') as T(N)
[/sourcecode]
Tebrikler, başardınız 🙂 İyi çalışmalar…


