0

I'm having an issue with certain JSON responses every time I make a GET call to MercadoLibre API. So far what I have found is that when response is too large, I get a null value, but i was able to solve it. This is my code:

SET TEXTSIZE 2147483647
DECLARE @url VARCHAR(8000) = 'https://api.mercadolibre.com/products/MLA18648076'
DECLARE @token as int;
DECLARE @respuesta as VARCHAR(8000);
DECLARE @ret INT;
DECLARE @respuesTabla table(respuestaTxt nvarchar(max))

EXEC sp_OACreate 'MSXML2.XMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'send'
insert into @respuesTabla (respuestaTxt)
EXEC @ret = sp_OAMethod @token, 'responseText'
select @respuesta = respuestaTxt from @respuesTabla

select @respuesta

EXEC sp_OADestroy @token

Now, response looks ok but if i try to open with OPENJSON i get the error that Json format isnt ok.

BUT,if i download the response to txt and use OPENROWSET i actually can read the json file. So...what's the problem?

Juan Pablo Humani
  • 1
  • 2
  • This seems like the same question as yours https://stackoverflow.com/questions/52588498/ssms-and-sp-oamethod-is-there-a-data-type-greater-than-varchar8000 – Craig Feb 02 '23 at 00:19
  • 1
    Use of the `sp_OA*` procs has been a code smell pretty much since SQL Server 2005. They compromise the performance and reliability of your SQL Server because the ActiveX objects they interact with are often not threadsafe and cause resource leaks if they aren't managed exactly correctly (which you don't seem to be doing here). Interactions with APIs really should be handled by external processes that connect to SQL Server to get/update data. If you absolutely must do this inside SQL Server then please look into SQL CLR functions and procedures which are managed code written in .NET Framework. – AlwaysLearning Feb 02 '23 at 00:55
  • Ok i'll edit with new updates – Juan Pablo Humani Feb 02 '23 at 00:57
  • According to your answer, what would be the easiest way to interact with APIs? – Juan Pablo Humani Feb 02 '23 at 01:03

1 Answers1

0

Change DECLARE @respuesta as VARCHAR(8000) to DECLARE @respuesta as NVARCHAR(MAX)

But yes, don't use SQL Server for this. You can create simple java or python or even powershell script that calls some URL and fetches products

siggemannen
  • 3,884
  • 2
  • 6
  • 24