1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

  2. Anuncie Aqui ! Entre em contato fdantas@4each.com.br

Send requests to rest API from SQL Server and check http code returned

Discussão em 'Outras Linguagens' iniciado por Stack, Fevereiro 3, 2021.

  1. Stack

    Stack Membro Participativo

    I'm attempting to send json data to a rest API, ideally from SQL Server 2016. I have come across the following code (from https://gist.github.com/theorigin/fa3c58406ff7b4565ca2) that uses sp_OAMethod and have tested it using webhook.site so I know it sends, but I can't see the http response code from SQL which is what I need to view... the API in question sends a 400 response along with an error message if there is an issue with a submission, or a 200 if everything was correct.

    The SQL code returns a NULL column under all scenarios. At present I have a PHP application that I have written that does all this for me, except it is not automated. The application runs through csv files and sends json one line at a time to the endpoint. Before I alter it to look at query results rather than csv files, and before I start looking at adding this to a cron job (or perhaps a scheduled task in Windows?), I have been wondering if what I am trying to achieve is possible using SQL Server, which is where all my data is stored. This may be a round peg into a square hole though and if so then I will discount this method.

    DECLARE @Object AS INT;
    DECLARE @ResponseText AS VARCHAR(8000);
    DECLARE @Body AS VARCHAR(8000) =
    '{
    "name": "John",
    "age": 30,
    }'

    EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
    EXEC sp_OAMethod @Object, 'open', NULL, 'post','https://webhook.site/1234', 'false'

    EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
    EXEC sp_OAMethod @Object, 'send', null, @body

    EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
    SELECT @ResponseText

    EXEC sp_OADestroy @Object


    Ideally I will see '200' or '400' as a response for each request and I can parse these after the stored procedure has run.

    Any help or suggestions are very welcome. Thanks

    Continue reading...

Compartilhe esta Página