MS Sql get calls return 200, but null response

A place for developers to advertise their TheTVDB.com enabled app and get help from other developers with the API.
Post Reply
TerrenceT76
Posts: 5
Joined: Tue Apr 24, 2018 12:58 pm

Sat Sep 22, 2018 7:13 am

Gahhh, I am going nuts. I have read everything I can find about how to call an api with get and pass a token in the header to return data from sql. I Think I have it all correct and even get a 200 response, but null null text and body. I searched the board, but I could not find anyone having this issue (so it must be me). I just have a tiny implementation with only the people in my house using my page. Any help would be greatly appreciated...

Code: Select all

declare @TVDB_Token as Nvarchar(MAX) 
EXECUTE GetTVDBOauthToken @TVDB_Token OUTPUT;  
Declare @authHeader as  NVARCHAR(max) = 'Bearer ' + @TVDB_Token --Verified this is correct through swagger

DECLARE @obj AS INT
DECLARE @Uri AS NVARCHAR(MAX)
DECLARE @ResponseText NVARCHAR(MAX)
DECLARE @ResponseBody NVARCHAR(MAX)
DECLARE @status NVARCHAR(32)
DECLARE @statusText NVARCHAR(256)
 [code]
SET @Uri = 'https://api.thetvdb.com/series/281534/episodes'
EXEC sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @obj OUT
EXEC sp_OAMethod @obj, 'open', NULL, 'GET', @Uri, false

--Headers I am passing
EXEC sp_OAMethod @obj, 'setRequestHeader', NULL, 'accept-language', 'en' --tried with 'en','en-US' and 'en-US,en;q=0.9'
EXEC sp_OAMethod @obj, 'setRequestHeader', NULL, 'Authorization', @authHeader;
EXEC sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/json; charset=utf-8' --tried with only 'application/json'

--Send the request
EXEC sp_OAMethod @obj, 'send'

--Get response and show it
EXEC sp_OAGetProperty @obj, 'status', @status OUT;
EXEC sp_OAGetProperty @obj, 'statusText', @statusText OUT;
EXEC sp_OAGetProperty @obj, 'ResponseText', @ResponseText OUTPUT
exec sp_OAMethod @obj, 'ResponseBody', @ResponseBody OUTPUT

select 'Status: ' + @status + ' (' + @statusText + ')' --Returns Status: 200 (OK)
SELECT @ResponseText [responseText] --Returns NULL
SELECT @ResponseBody [responseBody] --Returns NULL

EXEC sp_OADestroy @obj
[/code]
TerrenceT76
Posts: 5
Joined: Tue Apr 24, 2018 12:58 pm

Wed Sep 26, 2018 3:47 am

[taking attendance] Bueller?... Bueller?... Bueller? snicker snicker
TerrenceT76
Posts: 5
Joined: Tue Apr 24, 2018 12:58 pm

Thu Sep 27, 2018 6:43 am

Okay, I tinkered for hours and finally found the issue. the call to episodes returns every episode from a given series in one page. For the series I selected that is 66,573 characters. MSSql has a limit of 4000, so it is overrun and returns null. This is a pain since there is not a way to search for a specific episode until you have the episode number and you cant get the episode number without returning all episodes for a series. I am going to have to use something else to return this GIGANTIC json and process it...
Post Reply