小程序开发系列(八)利用SQL实现access_token的自动通知

在ms sql服务器中，利用维护计划，可以自动执行作业。如果我们将access_token的更新定义为作业，在间隔若时间进行更新，那么理论上，SQL服务器中就实现了access_token的自动更新。按着这样的思路，第1步要解决的就是SQL的http请求。我们来看实现代码

CREATE PROCEDURE [dbo].[UpdateAccessToken]--更新访问票据ASBEGINdeclare @ServiceUrl as varchar(1000) declare @UrlAddress varchar(500)--该储存过程，需要开通以下两个设定，才可正常运行.--设定1--sp_configure 'show advanced options', 1;--GO--RECONFIGURE;--GO--设定2--sp_configure 'Ole Automation Procedures', 1;--GO--RECONFIGURE;--GO--WebService地址：以http开头--SELECT @UrlAddress    set @UrlAddress='https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid=APPID&secret=SECRET'set @ServiceUrl = @UrlAddress;                  Declare @Object as IntDECLARE @hr1 intDECLARE @hr2 intDeclare @ResponseText as VARCHAR(8000)    DECLARE @source varchar(255)    DECLARE @description varchar(255)              BEGIN TRY    --Exec @hr1 =sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;Exec @hr1=sp_OACreate 'MSXML2.ServerXMLHttp', @Object OUT;Exec sp_OAMethod @Object, 'open', NULL, 'get',@ServiceUrl,'false'Exec sp_OAMethod @Object, 'send'Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT--insert into test.dbo.t1 values(newid(),getdate())EXEC @hr2 = sp_OADestroy @objectIF @hr2 <> 0BEGIN   EXEC sp_OAGetErrorInfo @object,@source OUT, @description OUTEND   END TRY   BEGIN CATCHIF @hr1 = 0BEGINEXEC sp_OADestroy @objectEND   END CATCH    DECLARE @TokenValue NVARCHAR(1024)DECLARE @TokenExpireIn INTDECLARE @TokenOwner NVARCHAR(50)SET @TokenOwner='WXMicroApp'SELECT @TokenValue=dbo.fn_parsejson(@ResponseText,'access_token')SELECT @TokenExpireIn=dbo.fn_parsejson(@ResponseText,'expires_in')DECLARE @count INTSELECT @count=COUNT(*) FROM dbo.TWXAccessToken WHERE TokenOwner=@TokenOwnerIF @count>0BEGINUPDATE dbo.TWXAccessToken SET TokenValue=@TokenValue,TokenExpireIn=@TokenExpireIn,TokenUpdateTime=GETDATE() WHERE TokenOwner=@TokenOwnerENDELSEBEGININSERT INTO dbo.TWXAccessToken(TokenGuid,TokenOwner,TokenValue,TokenExpireIn,TokenUpdateTime)VALUES(NEWID(),@TokenOwner,@TokenValue,@TokenExpireIn,GETDATE())ENDSelect @ResponseText AS Response,@source AS ErrorSrc, @description AS ErrorDescription   END

利用MSXML2.XMLHTTP可以实现SQL的HTTP访问，其实就是OLE。有了HTTP访问之后，只要按官方的API说明，将URL附上相应的appid和secret就可以获取access_token了。

需要注意：要使用MSXML2.XMLHTTP必须开启相应的配置，上面的代码中的【设定1】和【设定2】有说明。相应的配置如果没有开启，将会导致请求失败。

由于请求后获取的数据是JSON数据，所以为了能够从JSON数据中分离出access_token，我们特别定义了相应的函数dbo.fn_parsejson，代码如下

---------------------------------  解析JSON字符串  -----------------------------------p_jsonstr json字符串--p_key 键--返回p_key对应的值ALTER FUNCTION [dbo].[fn_parsejson](@p_jsonstr VARCHAR(8000),                                 @p_key VARCHAR(200)) RETURNS VARCHAR(3000)AS      BEGINDECLARE @rtnVal VARCHAR(3000);  DECLARE @i INT;  DECLARE @jsonkey VARCHAR(200);  DECLARE @jsonvalue VARCHAR(1000);  DECLARE @json VARCHAR(8000);  DECLARE @tmprow VARCHAR(2000);  DECLARE @tmpval VARCHAR(2000);    IF(@p_jsonstr IS NOT NULL)BEGIN     SET @json = REPLACE(@p_jsonstr, '{', '');     SET @json = REPLACE(@json, '}', '');     SET @json = REPLACE(@json, '"', '');DECLARE @json_cur CURSOR;  -- 声明外层游标SET @json_cur = CURSOR FOR SELECT tid FROM fn_split(@json, ',');OPEN @json_cur-- 打开游标（外层游标）FETCH NEXT FROM @json_cur INTO @tmprow-- 提取外层游标行WHILE(@@FETCH_STATUS = 0)BEGINIF(@tmprow IS NOT NULL)BEGINSET @i = 0;SET @jsonkey = '';SET @jsonvalue = '';DECLARE @str_cur CURSOR;-- 声明内层游标SET @str_cur = CURSOR FOR SELECT tid FROM fn_split(@tmprow, ':');--第二次拆分后的游标（内层游标）OPEN @str_cur  -- 打开游标FETCH NEXT FROM @str_cur INTO @tmpval-- 提取内层游标行WHILE(@@FETCH_STATUS = 0)BEGINIF(@i = 0) BEGINSET @jsonkey = @tmpvalENDIF(@i = 1)BEGINSET @jsonvalue = @tmpvalENDSET @i = @i + 1FETCH NEXT FROM @str_cur into @tmpval-- 内层游标下移一行ENDCLOSE @str_cur-- 关闭内层游标DEALLOCATE @str_cur -- 释放内层游标IF(@jsonkey = @p_key)BEGINSET @rtnVal = @jsonvalueENDENDFETCH NEXT FROM @json_cur INTO @tmprow-- 内层游标结束后，外层游标下移一行ENDCLOSE @json_cur-- 关闭外层游标DEALLOCATE @json_cur-- 释放外层游标END     RETURN @rtnVal  END --------------------------

在该函数中用到了字符串分离的函数dbo.fn_split，其代码如下

--------------------------------------------------------           解析字符串函数                --------------------------------------------------------ALTER FUNCTION [dbo].[fn_split](@p_str VARCHAR(8000), @p_split VARCHAR(10))RETURNS @tab TABLE(tid VARCHAR(2000))ASBEGINDECLARE @idx INTDECLARE @len INTSELECT @len = LEN(@p_split), @idx = CHARINDEX(@p_split, @p_str, 1)WHILE(@idx >= 1)BEGININSERT INTO @tab SELECT LEFT(@p_str, @idx - 1)SELECT @p_str = RIGHT(@p_str, LEN(@p_str) - @idx - @len + 1), @idx = CHARINDEX(@p_split, @p_str, 1)ENDif(@p_str <> '') INSERT INTO @tab SELECT @p_strRETURNEND;

有了这些函数后，我们就可以从获取到的json串中分离出【访问票据】access_token和【有效期】expires_in。为了便于使用，我们将数据存到了一个表中，表见下图

表中的TokenValue就是access_token，TokenExpireIn就是expires_in，同时定义了更新的时间TokenUpdateTime、唯一标识TokenGuid，为了适应更多的公众号、小程序的票据，特别定义了【所有者】TokenOwner，TokenOwner的值由用户自行定义，比如WXMicroApp。

我们执行一下存储过程dbo.UpdateAccessToken，表中记录了相应的数据。

为了让这个过程可以自动，我们在维护计划中添加UpdateAccessToken，如下图

在UpdateAccessToken中添加一个子计划，同时在左侧的【工具箱】中拖入【T-SQL】，然后双击【T-SQL】输入执行存储过程的代码

然后在维护计划的子计划中设置更新的时间间隔，比如隔1小时。

以上就是SQL的access_token自动更新的具体实现过程。

转载请注明出处。