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

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

CREATE PROCEDURE [dbo].[UpdateAccessToken]	--更新访问票据
AS
BEGIN
	declare @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 Int
	DECLARE @hr1 int
	DECLARE @hr2 int
	Declare @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 @object
		IF @hr2 <> 0
			BEGIN
			   EXEC sp_OAGetErrorInfo @object,@source OUT, @description OUT
			END	
   END TRY	
   BEGIN CATCH
		IF @hr1 = 0
			BEGIN
				EXEC sp_OADestroy @object
			END		
   END CATCH
    DECLARE @TokenValue NVARCHAR(1024)
	DECLARE @TokenExpireIn INT
	DECLARE @TokenOwner NVARCHAR(50)
	SET @TokenOwner='WXMicroApp'

	SELECT @TokenValue=dbo.fn_parsejson(@ResponseText,'access_token')
	SELECT @TokenExpireIn=dbo.fn_parsejson(@ResponseText,'expires_in')

	DECLARE @count INT
	SELECT @count=COUNT(*) FROM dbo.TWXAccessToken WHERE TokenOwner=@TokenOwner
	IF @count>0
		BEGIN
			UPDATE dbo.TWXAccessToken SET TokenValue=@TokenValue,TokenExpireIn=@TokenExpireIn,TokenUpdateTime=GETDATE() WHERE TokenOwner=@TokenOwner
		END
	ELSE
		BEGIN
			INSERT INTO dbo.TWXAccessToken(TokenGuid,TokenOwner,TokenValue,TokenExpireIn,TokenUpdateTime)
			VALUES(NEWID(),@TokenOwner,@TokenValue,@TokenExpireIn,GETDATE())
		END
	Select @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      
BEGIN
DECLARE @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)
BEGIN
IF(@tmprow IS NOT NULL)
BEGIN
SET @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)
BEGIN
IF(@i = 0) 
BEGIN
SET @jsonkey = @tmpval
END
IF(@i = 1)
BEGIN
SET @jsonvalue = @tmpval
END


SET @i = @i + 1


FETCH NEXT FROM @str_cur into @tmpval-- 内层游标下移一行
END

CLOSE @str_cur-- 关闭内层游标
DEALLOCATE @str_cur -- 释放内层游标


IF(@jsonkey = @p_key)
BEGIN
SET @rtnVal = @jsonvalue
END
END


FETCH NEXT FROM @json_cur INTO @tmprow-- 内层游标结束后，外层游标下移一行
END


CLOSE @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))
AS
BEGIN
DECLARE @idx INT
DECLARE @len INT
SELECT @len = LEN(@p_split), @idx = CHARINDEX(@p_split, @p_str, 1)
WHILE(@idx >= 1)
BEGIN
INSERT 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)
END

if(@p_str <> '') INSERT INTO @tab SELECT @p_str
RETURN

END;

有了这些函数后，我们就可以从获取到的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自动更新的具体实现过程。

转载请注明出处。