dcsimg
Results 1 to 4 of 4

Thread: Questions about the sp_OAMethod parameter varchar(8000)

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2016
    Location
    China
    Posts
    186

    Questions about the sp_OAMethod parameter varchar(8000)

    Code:
    alter  PROC PrUrlSendRequest(@Url VARCHAR(8000) = '',@PostData VARCHAR(10) = '' ,@ResponseText VARCHAR(8000) = '' OUTPUT)AS
        /*存储过程发起URL请求,启用 Ole Automation Procedures 选项
    	exec sp_configure 'show advanced options',1;
    	go
    	reconfigure;
    	go
    	sp_configure 'Ole Automation Procedures',1;
    	go
    	reconfigure;
    	go
    	*/
    	SET NOCOUNT ON 
        DECLARE @ErrMsg VARCHAR(5000)
        DECLARE @Object INT,@status INT,@returnTextErr VARCHAR(8000),@HttpStatus VARCHAR(200),@HttpMethod VARCHAR(10) = 'GET'
    
        IF ISNULL(@PostData, '') <> ''
            SET @HttpMethod = 'POST'
    	/*初始化对*/
        EXEC @status = sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT
        IF @status <> 0
            BEGIN  
                EXEC sp_OAGetErrorInfo @Object, @ErrMsg OUT, @returnTextErr OUT
                SET @ErrMsg = '初始化对象失败,' + @ErrMsg + ISNULL(@returnTextErr, '')
                RAISERROR(@ErrMsg,16,-1)           
                RETURN 1
            END
    	/*创建链接*/
        EXEC @status = sp_OAMethod @Object, 'open', NULL, @HttpMethod, @Url,'false'
        IF @status <> 0
            BEGIN  
                EXEC sp_OAGetErrorInfo @Object, @ErrMsg OUT, @returnTextErr OUT
                SET @ErrMsg = '创建连接失败,' + @ErrMsg + ISNULL(@returnTextErr, '')
                RAISERROR(@ErrMsg,16,-1)           
                RETURN 2
            END  
    
        IF @HttpMethod = 'POST'
            BEGIN
                --EXEC @status = sp_OAMethod @Object, 'setRequestHeader', NULL,'Content-Type', 'application/x-www-form-urlencoded'
                EXEC @status = sp_OAMethod @Object, 'setRequestHeader', NULL,'Content-Type', 'application/json; charset=UTF-8'
            END
        ELSE
            BEGIN
                --EXEC @status = sp_OAMethod @Object, 'setRequestHeader', NULL,'Content-Type', 'text/xml; charset=gb2312'   
                EXEC @status = sp_OAMethod @Object, 'setRequestHeader', NULL,'Content-Type', 'application/json; charset=UTF-8'
            END
    
        IF @status <> 0
            BEGIN  
                EXEC sp_OAGetErrorInfo @Object, @ErrMsg OUT, @returnTextErr OUT
                SET @ErrMsg = '设置RequestHeader属性失败,' + @ErrMsg + ISNULL(@returnTextErr, '')
                RAISERROR(@ErrMsg,16,-1)           
                RETURN 2
            END  
    
        EXEC @status = sp_OAMethod @Object, 'send', NULL, @PostData 
    
        IF @status <> 0
            BEGIN  
                EXEC sp_OAGetErrorInfo @Object, @ErrMsg OUT, @returnTextErr OUT 
                SET @ErrMsg = '发送请求头失败,' + @ErrMsg + ISNULL(@returnTextErr, '')
                RAISERROR(@ErrMsg,16,-1)           
                RETURN 3
            END 
    
        EXEC @status = sys.sp_OAGetProperty @Object, 'Status', @HttpStatus OUT; 
    
        IF @status <> 0
            BEGIN
                EXEC sp_OAGetErrorInfo @Object, @ErrMsg OUT, @returnTextErr OUT 
                SET @ErrMsg = '读取[Status]属性值失败,' + @ErrMsg + ISNULL(@returnTextErr,'')
                RAISERROR(@ErrMsg,16,-1)           
                RETURN 3
            END
    
        IF @HttpStatus <> 200
            BEGIN
                SET @ErrMsg ='访问错误,http状态代码,'+@HttpStatus+''
                RAISERROR(@ErrMsg,16,1);
                RETURN -6;
            END
    
        --问题在这里:@ResponseText 不支持超过8000字符
    	declare @json varchar(max)
    	EXEC @status = sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
    	--返回查询结果
    	select  @ResponseText
    
        IF @status <> 0
            BEGIN  
                EXEC sp_OAGetErrorInfo @Object, @ErrMsg OUT, @returnTextErr OUT
                SET @ErrMsg = '获取回复报文失败:' + ISNULL(@ErrMsg, '') + ','+ ISNULL(@returnTextErr, '')
                RAISERROR(@ErrMsg,16,-1)           
                RETURN 4
            END 
    
        EXEC @status = sp_OADestroy @Object
        IF @status <> 0
            BEGIN  
                EXEC sp_OAGetErrorInfo @Object, @ErrMsg OUT, @returnTextErr OUT
                SET @ErrMsg = '释放资源对象,' + @ErrMsg + ISNULL(@returnTextErr, '')
                RAISERROR(@ErrMsg,16,-1)           
                RETURN 5
            END
        RETURN 0
    GO

    declare @url as Varchar(2000) ,@ResponseText as Varchar(8000)

    set @url='http://cache.video.iqiyi.com/jp/avlist/202861101/1/?callback=jsonp9'
    exec PrUrlSendRequest @url ,'',@ResponseText OUTPUT


    The following test will occur because there are too many results returned. "The source data specified for this string, binary column, or parameter is too long."
    Can anyone help solve this problem?
    QQ: 289778005

  2. #2
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,543

    Re: Questions about the sp_OAMethod parameter varchar(8000)

    Try setting "declare @url as NVarchar(2000)" , if this does not work try with Nvarchar(MAX) or Varchar(MAX) but I guess it should be nvarchar if you are passing data in Chinese.
    Slow as hell.

  3. #3
    Frenzied Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    1,411

    Re: Questions about the sp_OAMethod parameter varchar(8000)

    Try this

    Code:
        --问题在这里:@ResponseText 不支持超过8000字符
        DECLARE @json TABLE (Result NVARCHAR(MAX))
        INSERT  @json(Result)
        EXEC    @status = dbo.sp_OAGetProperty @Object, 'responseText'
        SELECT  @ResponseText = Result FROM @json
    cheers,
    </wqw>

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    May 2016
    Location
    China
    Posts
    186

    Resolved Re: Questions about the sp_OAMethod parameter varchar(8000)

    Quote Originally Posted by wqweto View Post
    Try this

    Code:
        --问题在这里:@ResponseText 不支持超过8000字符
        DECLARE @json TABLE (Result NVARCHAR(MAX))
        INSERT  @json(Result)
        EXEC    @status = dbo.sp_OAGetProperty @Object, 'responseText'
        SELECT  @ResponseText = Result FROM @json
    cheers,
    </wqw>
    Thank you so much for wqweto! Give me a good solution. After testing, it works perfectly well. Thank you again for wqweto.
    QQ: 289778005

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width