记一次sql server 2005访问http接口，并解析json的过程
JSON解析官方网站：https://www.red-gate.com/simple-talk/sql/t-sql-programming/consuming-json-strings-in-sql-server/
Sql脚本：
--开启Sql Server 通讯配置-- sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ole Automation Procedures'; GO
调用 declare @Json varchar(2000); exec IPCC_WEBSERVICE_PACKID 'ddd',@Json output declare @温度_temp varchar(30); //parseJson用来解析json字符串 Select * from parseJSON(@Json);
Create PROCEDURE [dbo].[IPCC_WEBSERVICE_PACKID](@parametersStr VARCHAR(255),@outText VARCHAR(2000) OUT) AS BEGIN DECLARE @returnText VARCHAR(2000) DECLARE @status int DECLARE @urlStr VARCHAR(255) SET @urlStr = 'http://www.weather.com.cn/data/sk/101200901.html'; EXEC P_GET_HttpRequestData @urlStr, @status OUTPUT, @returnText OUTPUT; SET @outText = @returnText; --print @outText ; ---打印 END;
P_GET_HttpRequestData：
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* 参数说明? @URL=http请求地址 @status=状态代? @returnText=返回? @object=对象令牌 @errSrc=错误源编? DECLARE @returnText VARCHAR(2000) DECLARE @status int DECLARE @urlStr VARCHAR(255) SET @urlStr = 'http://127.0.0.1:8086/query?chunked=true&db=yunhedb&epoch=ns&q=select+%2A+from+TAGDATA+where+TAGCODE%3D%27MZY_KF1%3A12LI-111004L01C%27+AND+TIME%3E%272019-01-11+00%3A00%3A00%27+AND+TIME%3C%272019-01-11+00%3A10%3A00%27'; EXEC P_GET_HttpRequestData @urlStr,'Basic YWRtaW46YWRtaW4=', @status OUTPUT, @returnText OUTPUT; print @returnText; DECLARE @Json VARCHAR(2000) set @Json = '{"results":[{"statement_id":0,"series":[{"name":"TAGDATA","columns":["time","CONF","DATATYPE","LASTTIME","TAGCODE","VALUEF","VALUES"],"values":[[1547164860000000000,100,"FLOAT","2019-01-11 00:01:51","MZY_KF1:12LI-111004L01C",49.79543,""],[1547164920000000000,100,"FLOAT","2019-01-11 00:02:54","MZY_KF1:12LI-111004L01C",49.51171,""],[1547164980000000000,100,"FLOAT","2019-01-11 00:03:57","MZY_KF1:12LI-111004L01C",51.73619,""],[1547165040000000000,100,"FLOAT","2019-01-11 00:04:57","MZY_KF1:12LI-111004L01C",50.52501,""],[1547165100000000000,100,"FLOAT","2019-01-11 00:05:57","MZY_KF1:12LI-111004L01C",48.77499,""],[1547165160000000000,100,"FLOAT","2019-01-11 00:06:57","MZY_KF1:12LI-111004L01C",49.93849,""],[1547165220000000000,100,"FLOAT","2019-01-11 00:07:48","MZY_KF1:12LI-111004L01C",50.86118,""],[1547165280000000000,100,"FLOAT","2019-01-11 00:08:57","MZY_KF1:12LI-111004L01C",52.8973,""],[1547165340000000000,100,"FLOAT","2019-01-11 00:09:57","MZY_KF1:12LI-111004L01C",47.21809,""]]}]}]}' --parseJson用来解析json字符串 Select * from parseJSON(@Json); */ ALTER PROCEDURE [dbo].[P_GET_HttpRequestData]( @URL varchar(500), @auth VARCHAR(2000),--'Basic YWRtaW46YWRtaW4=' @status int=0 OUT, @returnText varchar(8000)='' OUT ) AS BEGIN DECLARE @object int, @errSrc int /*初始化对*/ EXEC @status = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0', @object OUT IF @status <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT RETURN END /*创建链接*/ EXEC @status= SP_OAMethod @object,'open',NULL,'GET',@URL IF @status <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT RETURN END EXEC @status=SP_OAMethod @object,'setRequestHeader','Content-Type','application/x-www-form-urlencoded' if(@auth != null or @auth!='') begin--有密码 EXEC SP_OAMethod @object,'setRequestHeader',NULL,'Authorization',@auth; end /*发起请求*/ EXEC @status= SP_OAMethod @object,'send',NULL IF @status <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT RETURN END /*获取返回*/ EXEC @status= SP_OAGetProperty @object,'responseText',@returnText OUT IF @status <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT RETURN END END;
parseJSON：
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[parseJSON]( @JSON NVARCHAR(MAX)) RETURNS @hierarchy TABLE ( element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */ sequenceNo [int] NULL, /* the place in the sequence for the element */ parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */ Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */ NAME NVARCHAR(2000),/* the name of the object */ StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */ ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/ ) AS BEGIN DECLARE @FirstObject INT, --the index of the first open bracket found in the JSON string @OpenDelimiter INT,--the index of the next open bracket found in the JSON string @NextOpenDelimiter INT,--the index of subsequent open bracket found in the JSON string @NextCloseDelimiter INT,--the index of subsequent close bracket found in the JSON string @Type NVARCHAR(10),--whether it denotes an object or an array @NextCloseDelimiterChar CHAR(1),--either a '}' or a ']' @Contents NVARCHAR(MAX), --the unparsed contents of the bracketed expression @Start INT, --index of the start of the token that you are parsing @end INT,--index of the end of the token that you are parsing @param INT,--the parameter at the end of the next Object/Array token @EndOfName INT,--the index of the start of the parameter at end of Object/Array token @token NVARCHAR(200),--either a string or object @value NVARCHAR(MAX), -- the value as a string @SequenceNo int, -- the sequence number within a list @name NVARCHAR(200), --the name as a string @parent_ID INT,--the next parent ID to allocate @lenJSON INT,--the current length of the JSON String @characters NCHAR(36),--used to convert hex to decimal @result BIGINT,--the value of the hex symbol being parsed @index SMALLINT,--used for parsing the hex value @Escape INT --the index of the next escape character DECLARE @Strings TABLE /* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped' in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in the JSON string by tokens representing the string */ ( String_ID INT IDENTITY(1, 1), StringValue NVARCHAR(MAX) ) SELECT--initialise the characters to convert hex to ascii @characters='0123456789abcdefghijklmnopqrstuvwxyz', @SequenceNo=0, --set the sequence no. to something sensible. /* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */ @parent_ID=0; WHILE 1=1 --forever until there is nothing more to do BEGIN SELECT @start=PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin);--next delimited string IF @start=0 BREAK --no more so drop through the WHILE loop IF SUBSTRING(@json, @start+1, 1)='"' BEGIN --Delimited Name SET @start=@Start+1; SET @end=PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start) collate SQL_Latin1_General_CP850_Bin); END IF @end=0 --no end delimiter to last string BREAK --no more SELECT @token=SUBSTRING(@json, @start+1, @end-1) --now put in the escaped control characters SELECT @token=REPLACE(@token, FROMString, TOString) FROM (SELECT '\"' AS FromString, '"' AS ToString UNION ALL SELECT '\\', '\' UNION ALL SELECT '\/', '/' UNION ALL SELECT '\b', CHAR(08) UNION ALL SELECT '\f', CHAR(12) UNION ALL SELECT '\n', CHAR(10) UNION ALL SELECT '\r', CHAR(13) UNION ALL SELECT '\t', CHAR(09) ) substitutions SELECT @result=0, @escape=1 --Begin to take out any hex escape codes WHILE @escape>0 BEGIN SELECT @index=0, --find the next hex escape sequence @escape=PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token collate SQL_Latin1_General_CP850_Bin) IF @escape>0 --if there is one BEGIN WHILE @index<4 --there are always four digits to a \x sequence BEGIN SELECT --determine its value @result=@result+POWER(16, @index) *(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1), @characters)-1), @index=@index+1 ; END -- and replace the hex sequence by its unicode value SELECT @token=STUFF(@token, @escape, 6, NCHAR(@result)) END END --now store the string away INSERT INTO @Strings (StringValue) SELECT @token -- and replace the string with a token SELECT @JSON=STUFF(@json, @start, @end+1, '@string'+CONVERT(NVARCHAR(5), @@identity)) END -- all strings are now removed. Now we find the first leaf. WHILE 1=1 --forever until there is nothing more to do BEGIN SELECT @parent_ID=@parent_ID+1 --find the first object or list by looking for the open bracket SELECT @FirstObject=PATINDEX('%[{[[]%', @json collate SQL_Latin1_General_CP850_Bin)--object or array IF @FirstObject = 0 BREAK IF (SUBSTRING(@json, @FirstObject, 1)='{') SELECT @NextCloseDelimiterChar='}', @type='object' ELSE SELECT @NextCloseDelimiterChar=']', @type='array' SELECT @OpenDelimiter=@firstObject WHILE 1=1 --find the innermost object or list... BEGIN SELECT @lenJSON=LEN(@JSON+'|')-1 --find the matching close-delimiter proceeding after the open-delimiter SELECT @NextCloseDelimiter=CHARINDEX(@NextCloseDelimiterChar, @json, @OpenDelimiter+1) --is there an intervening open-delimiter of either type SELECT @NextOpenDelimiter=PATINDEX('%[{[[]%', RIGHT(@json, @lenJSON-@OpenDelimiter)collate SQL_Latin1_General_CP850_Bin)--object IF @NextOpenDelimiter=0 BREAK SELECT @NextOpenDelimiter=@NextOpenDelimiter+@OpenDelimiter IF @NextCloseDelimiter<@NextOpenDelimiter BREAK IF SUBSTRING(@json, @NextOpenDelimiter, 1)='{' SELECT @NextCloseDelimiterChar='}', @type='object' ELSE SELECT @NextCloseDelimiterChar=']', @type='array' SELECT @OpenDelimiter=@NextOpenDelimiter END ---and parse out the list or name/value pairs SELECT @contents=SUBSTRING(@json, @OpenDelimiter+1, @NextCloseDelimiter-@OpenDelimiter-1) SELECT @JSON=STUFF(@json, @OpenDelimiter, @NextCloseDelimiter-@OpenDelimiter+1, '@'+@type+CONVERT(NVARCHAR(5), @parent_ID)) WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin))<>0 BEGIN IF @Type='Object' --it will be a 0-n list containing a string followed by a string, number,boolean, or null BEGIN SELECT @SequenceNo=0,@end=CHARINDEX(':', ' '+@contents)--if there is anything, it will be a string-based name. SELECT @start=PATINDEX('%[^A-Za-z@][@]%', ' '+@contents collate SQL_Latin1_General_CP850_Bin)--AAAAAAAA SELECT @token=SUBSTRING(' '+@contents, @start+1, @End-@Start-1), @endofname=PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin), @param=RIGHT(@token, LEN(@token)-@endofname+1) SELECT @token=LEFT(@token, @endofname-1), @Contents=RIGHT(' '+@contents, LEN(' '+@contents+'|')-@end-1) SELECT @name=stringvalue FROM @strings WHERE string_id=@param --fetch the name END ELSE SELECT @Name=null,@SequenceNo=@SequenceNo+1 SELECT @end=CHARINDEX(',', @contents)-- a string-token, object-token, list-token, number,boolean, or null IF @end=0 SELECT @end=PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @Contents+' ' collate SQL_Latin1_General_CP850_Bin) +1 SELECT @start=PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' '+@contents collate SQL_Latin1_General_CP850_Bin) --select @start,@end, LEN(@contents+'|'), @contents SELECT @Value=RTRIM(SUBSTRING(@contents, @start, @End-@Start)), @Contents=RIGHT(@contents+' ', LEN(@contents+'|')-@end) IF SUBSTRING(@value, 1, 7)='@object' INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType) SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 8, 5), SUBSTRING(@value, 8, 5), 'object' ELSE IF SUBSTRING(@value, 1, 6)='@array' INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType) SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 7, 5), SUBSTRING(@value, 7, 5), 'array' ELSE IF SUBSTRING(@value, 1, 7)='@string' INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, ValueType) SELECT @name, @SequenceNo, @parent_ID, stringvalue, 'string' FROM @strings WHERE string_id=SUBSTRING(@value, 8, 5) ELSE IF @value IN ('true', 'false') INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, ValueType) SELECT @name, @SequenceNo, @parent_ID, @value, 'boolean' ELSE IF @value='null' INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, ValueType) SELECT @name, @SequenceNo, @parent_ID, @value, 'null' ELSE IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin)>0 INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, ValueType) SELECT @name, @SequenceNo, @parent_ID, @value, 'real' ELSE INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, ValueType) SELECT @name, @SequenceNo, @parent_ID, @value, 'int' if @Contents=' ' Select @SequenceNo=0 END END INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType) SELECT '-',1, NULL, '', @parent_id-1, @type -- RETURN END GO
调用HTTP的SQL函数：
ALTER FUNCTION FN_HTTP_POST( @URL VARCHAR(256), @AUTH VARCHAR(256),--'Basic YWRtaW46YWRtaW4=' @DATA VARCHAR(2000), @REQ_H_ACCEPT VARCHAR(256), @REQ_H_CONTENT_TYPE VARCHAR(256)) RETURNS VARCHAR(5000) AS BEGIN /* --HTTP POST 请求 --[测试] -- --DROP FUNCTION FN_HTTP_POST; --调用函数 SELECT dbo.FN_HTTP_POST('http://127.0.0.1:8086/query?chunked=true&db=yunhedb&epoch=ns&q=select+%2A+from+TAGDATA+where+TAGCODE%3D%27MZY_KF1%3A12LI-111004L01C%27+AND+TIME%3E%272019-01-11+00%3A00%3A00%27+AND+TIME%3C%272019-01-11+00%3A10%3A00%27','Basic YWRtaW46YWRtaW4=','{"data1":"test","data2":"123456"}','application/json','application/json'); --转换JSON DECLARE @Json VARCHAR(8000) set @Json = '{"results":[{"statement_id":0,"series":[{"name":"TAGDATA","columns":["time","CONF","DATATYPE","LASTTIME","TAGCODE","VALUEF","VALUES"],"values":[[1547164860000000000,100,"FLOAT","2019-01-11 00:01:51","MZY_KF1:12LI-111004L01C",49.79543,""],[1547164920000000000,100,"FLOAT","2019-01-11 00:02:54","MZY_KF1:12LI-111004L01C",49.51171,""],[1547164980000000000,100,"FLOAT","2019-01-11 00:03:57","MZY_KF1:12LI-111004L01C",51.73619,""],[1547165040000000000,100,"FLOAT","2019-01-11 00:04:57","MZY_KF1:12LI-111004L01C",50.52501,""],[1547165100000000000,100,"FLOAT","2019-01-11 00:05:57","MZY_KF1:12LI-111004L01C",48.77499,""],[1547165160000000000,100,"FLOAT","2019-01-11 00:06:57","MZY_KF1:12LI-111004L01C",49.93849,""],[1547165220000000000,100,"FLOAT","2019-01-11 00:07:48","MZY_KF1:12LI-111004L01C",50.86118,""],[1547165280000000000,100,"FLOAT","2019-01-11 00:08:57","MZY_KF1:12LI-111004L01C",52.8973,""],[1547165340000000000,100,"FLOAT","2019-01-11 00:09:57","MZY_KF1:12LI-111004L01C",47.21809,""]]}]}]}' --parseJson用来解析json字符串 Select * from parseJSON(@Json); */ DECLARE @object int, @returnStatus int, @returnText varchar(5000), @errMsg varchar(2000), @httpStatus varchar(20); /* 初始化 */ EXEC @returnStatus = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0',@object OUT; IF @returnStatus <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT; RETURN ('初始化对象失败，' + @errMsg + ISNULL(@returnText,'')); END /*创建链接*/ EXEC @returnStatus= SP_OAMethod @object,'open',NULL,'post',@URL,'false'; IF @returnStatus <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT; RETURN ('创建连接失败，' + @errMsg + ISNULL(@returnText, '')); END EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Accept',@REQ_H_ACCEPT; EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Content-Type',@REQ_H_CONTENT_TYPE; EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Content-Length','1000000'; if(@auth != null or @auth!='') begin--有密码 EXEC SP_OAMethod @object,'setRequestHeader',NULL,'Authorization',@auth; end /*发起请求*/ EXEC @returnStatus= SP_OAMethod @object,'send',NULL,@DATA; IF @returnStatus <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT; RETURN ('发起请求失败，' + @errMsg + ISNULL(@returnText, '')); END /*获取HTTP状态代码*/ EXEC @returnStatus = SP_OAGetProperty @Object, 'Status', @httpStatus OUT; IF @returnStatus <> 0 BEGIN EXEC sp_OAGetErrorInfo @Object, @errMsg OUT, @returnText OUT; RETURN ('获取HTTP状态代码失败，' + @errMsg + ISNULL(@returnText, '')); END IF @httpStatus <> 200 BEGIN RETURN ('访问错误，HTTP状态代码:' + @httpStatus); END /*获取返回信息*/ EXEC @returnStatus= SP_OAGetProperty @object,'responseText',@returnText OUT; IF @returnStatus <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT; RETURN ('获取返回信息失败，' + @errMsg + ISNULL(@returnText, '')); END RETURN @returnText; END ; GO ----------------------------------------------------- ALTER FUNCTION FN_HTTP_GET( @URL VARCHAR(256), @AUTH VARCHAR(256),--'Basic YWRtaW46YWRtaW4=' @DATA VARCHAR(2000), @REQ_H_ACCEPT VARCHAR(256), @REQ_H_CONTENT_TYPE VARCHAR(256) ) RETURNS VARCHAR(5000) AS BEGIN DECLARE @object int, @returnStatus int, @returnText varchar(5000), @errMsg varchar(2000), @httpStatus varchar(20) ; /* 初始化 */ EXEC @returnStatus = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0',@object OUT; IF @returnStatus <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT; RETURN ('初始化对象失败，' + @errMsg + ISNULL(@returnText,'')); END /*创建链接*/ EXEC @returnStatus= SP_OAMethod @object,'open',NULL,'get',@URL,'false'; IF @returnStatus <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT; RETURN ('创建连接失败，' + @errMsg + ISNULL(@returnText, '')); END EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Accept',@REQ_H_ACCEPT; EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Content-Type',@REQ_H_CONTENT_TYPE; EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Content-Length','1000000'; if(@auth != null or @auth!='') begin--有密码 EXEC SP_OAMethod @object,'setRequestHeader',NULL,'Authorization',@auth; end /*发起请求*/ EXEC @returnStatus= SP_OAMethod @object,'send',NULL,@DATA; IF @returnStatus <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT; RETURN ('发起请求失败，' + @errMsg + ISNULL(@returnText, '')); END /*获取HTTP状态代码*/ EXEC @returnStatus = SP_OAGetProperty @Object, 'Status', @httpStatus OUT; IF @returnStatus <> 0 BEGIN EXEC sp_OAGetErrorInfo @Object, @errMsg OUT, @returnText OUT; RETURN ('获取HTTP状态代码失败，' + @errMsg + ISNULL(@returnText, '')); END IF @httpStatus <> 200 BEGIN RETURN ('访问错误，HTTP状态代码:' + @httpStatus); END /*获取返回信息*/ EXEC @returnStatus= SP_OAGetProperty @object,'responseText',@returnText OUT; IF @returnStatus <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT; RETURN ('获取返回信息失败，' + @errMsg + ISNULL(@returnText, '')); END RETURN @returnText; END ; GO -------------------------------------------------------- ALTER FUNCTION FN_HTTP_PUT( @URL VARCHAR(256), @AUTH VARCHAR(256),--'Basic YWRtaW46YWRtaW4=' @DATA VARCHAR(2000), @REQ_H_ACCEPT VARCHAR(256), @REQ_H_CONTENT_TYPE VARCHAR(256) ) RETURNS VARCHAR(5000) AS BEGIN DECLARE @object int, @returnStatus int, @returnText varchar(5000), @errMsg varchar(2000), @httpStatus varchar(20) ; /* 初始化 */ EXEC @returnStatus = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0',@object OUT; IF @returnStatus <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT; RETURN ('初始化对象失败，' + @errMsg + ISNULL(@returnText,'')); END /*创建链接*/ EXEC @returnStatus= SP_OAMethod @object,'open',NULL,'put',@URL,'false'; IF @returnStatus <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT; RETURN ('创建连接失败，' + @errMsg + ISNULL(@returnText, '')); END EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Accept',@REQ_H_ACCEPT; EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Content-Type',@REQ_H_CONTENT_TYPE; EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Content-Length','1000000'; if(@auth != null or @auth!='') begin--有密码 EXEC SP_OAMethod @object,'setRequestHeader',NULL,'Authorization',@auth; end /*发起请求*/ EXEC @returnStatus= SP_OAMethod @object,'send',NULL,@DATA; IF @returnStatus <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT; RETURN ('发起请求失败，' + @errMsg + ISNULL(@returnText, '')); END /*获取HTTP状态代码*/ EXEC @returnStatus = SP_OAGetProperty @Object, 'Status', @httpStatus OUT; IF @returnStatus <> 0 BEGIN EXEC sp_OAGetErrorInfo @Object, @errMsg OUT, @returnText OUT; RETURN ('获取HTTP状态代码失败，' + @errMsg + ISNULL(@returnText, '')); END IF @httpStatus <> 200 BEGIN RETURN ('访问错误，HTTP状态代码:' + @httpStatus); END /*获取返回信息*/ EXEC @returnStatus= SP_OAGetProperty @object,'responseText',@returnText OUT; IF @returnStatus <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT; RETURN ('获取返回信息失败，' + @errMsg + ISNULL(@returnText, '')); END RETURN @returnText; END ; GO
