Sqlserver調用api

標籤：als   urlencode   config   webapi   使用   sub   roc   app   declare   

雖然使用sqlserver去調用服務介面的情況比較少，但也可以去瞭解下對應的使用方式

一、首先要開啟組件的配置

sp_configure ‘show advanced options‘, 1;GORECONFIGURE;GOsp_configure ‘Ole Automation Procedures‘, 1;GORECONFIGURE;GOEXEC sp_configure ‘Ole Automation Procedures‘;GO

二、調用webservice

1、調用webservice的時候建議使用fiddler去擷取一下發送資料過程用contenttype的類型以及調用介面的資料

2、使用sqlserver調用對應的介面以及結果

declare @ServiceUrl as varchar(1000) set @ServiceUrl = ‘http://localhost:19930/LoginWebService.asmx/Login‘DECLARE @data varchar(max);set @data=‘username=8&password=7‘                  Declare @Object as IntDeclare @ResponseText AS  varchar(1000)   ;      Exec sp_OACreate ‘Msxml2.ServerXMLHTTP.3.0‘, @Object OUT;Exec sp_OAMethod @Object, ‘open‘, NULL, ‘POST‘,@ServiceUrl,‘false‘Exec sp_OAMethod @Object, ‘setRequestHeader‘, NULL, ‘Content-Type‘,‘application/x-www-form-urlencoded‘Exec sp_OAMethod @Object, ‘send‘, NULL, @data --發送資料Exec sp_OAMethod @Object, ‘responseText‘, @ResponseText OUTPUTEXEC sp_OAGetErrorInfo @Object --異常輸出Select  @ResponseText Exec sp_OADestroy @ObjectGO

二、調用webapi、兩者調用的方式基本如出一轍

1、同樣使用fiddler擷取介面調用資訊（因為該介面是GET就不需要看所傳的參數）

2、介面調用以及結果

GET操作

declare @ServiceUrl as varchar(1000) set @ServiceUrl = ‘http://xxxxx.com/beijing/139/1000000/TaxInfo?token=6d83d2adcff64594bd68614b6ae9e1c8‘DECLARE @data varchar(max);set @data=‘‘                  Declare @Object as IntDeclare @ResponseText AS  varchar(8000)   ;      Exec sp_OACreate ‘Msxml2.ServerXMLHTTP.3.0‘, @Object OUT;Exec sp_OAMethod @Object, ‘open‘, NULL, ‘GET‘,@ServiceUrl,‘false‘Exec sp_OAMethod @Object, ‘send‘, NULL, @data --發送資料Exec sp_OAMethod @Object, ‘responseText‘, @ResponseText OUTPUTEXEC sp_OAGetErrorInfo @Object --異常輸出Select  @ResponseText Exec sp_OADestroy @ObjectGO

　

POST操作

declare @ServiceUrl as varchar(1000) set @ServiceUrl = ‘http://xxxx.com/Feedback/Estimate‘DECLARE @data varchar(max);--發送資料set @data=‘CityName=SubmitSystemName=%E7%99%BE%E5%BA%A6%E5%8F%8D%E9%A6%88&OriginID=2d90660c-436c-4e12-bfa6-e849a06b2c51&Price=10000&IsAccurate=False&PriceType=1&UserKeyId=a669e4ec7bdc47a7b6c2c334ebe1a50c&signature=X8p3lIZT0Ba3LeiC6irm3%2FMnlE8%3D&time=1452735047291‘                   Declare @Object as IntDeclare @ResponseText AS  varchar(8000)   ;      Exec sp_OACreate ‘Msxml2.ServerXMLHTTP.3.0‘, @Object OUT;Exec sp_OAMethod @Object, ‘open‘, NULL, ‘POST‘,@ServiceUrl,‘false‘Exec sp_OAMethod @Object, ‘setRequestHeader‘, NULL, ‘Content-Type‘,‘application/x-www-form-urlencoded‘Exec sp_OAMethod @Object, ‘send‘, NULL, @data --發送資料Exec sp_OAMethod @Object, ‘responseText‘, @ResponseText OUTPUTEXEC sp_OAGetErrorInfo @Object --異常輸出Select  @ResponseText Exec sp_OADestroy @ObjectGO

　　

 

Sqlserver調用api