0
I am using sql server 2016 to consume webservice by Procedure store. I have a JSON webservice returning the following:
[{"nome":"Rebecca","email":"[email protected]","ra":"12345"},{"nome":"Caroline","email":"[email protected]","ra":"23456"},{"nome":"Vanessa","email":"[email protected]","ra":"99999"}]
I can consume it by passing a variable. If I leave my webservice to receive a parameter and pass it I can return the student’s name and email. But if I leave my webservice without the need to receive parameter and remove the parameter passage from the Procedure and run, my Procedure returns null. What I need to change in my code to get back the full list of students that my webservice exposes?
Follow my code with passing parameters that returns the corresponding AR user:
create PROCEDURE webservice_parametros (@RA as varchar (5))
AS
BEGIN
DECLARE @OBJ INT;
DECLARE @URL VARCHAR(200);
DECLARE @RESPONSE VARCHAR(8000);
SET @URL = 'http://dominio:8080/v1/alunos/' + @RA
EXEC SP_OACREATE 'MSXML2.ServerXMLHttp', @OBJ out
EXEC SP_OAMETHOD @OBJ, 'OPEN', NULL, 'GET', @URL, FALSE
EXEC SP_OAMETHOD @OBJ, 'SEND'
exec SP_OAGETPROPERTY @OBJ, 'responseText', @RESPONSE out
EXEC SP_OADESTROY @OBJ
SELECT JSON_VALUE(@RESPONSE, '$.nome') as nome,
JSON_VALUE(@RESPONSE, '$.email') as email
END
execute webservice_parametros '12345'
Edited
My code that returns null to a service that does not need parameters
create PROCEDURE webservice
AS
BEGIN
DECLARE @OBJ INT;
DECLARE @URL VARCHAR(200);
DECLARE @RESPONSE VARCHAR(8000);
SET @URL = 'http://dominio:8080/v1/alunos/'
EXEC SP_OACREATE 'MSXML2.ServerXMLHttp', @OBJ out
EXEC SP_OAMETHOD @OBJ, 'OPEN', NULL, 'GET', @URL, FALSE
EXEC SP_OAMETHOD @OBJ, 'SEND'
exec SP_OAGETPROPERTY @OBJ, 'responseText', @RESPONSE out
EXEC SP_OADESTROY @OBJ
SELECT JSON_VALUE(@RESPONSE, '$.nome') as nome,
JSON_VALUE(@RESPONSE, '$.email') as email
END
execute webservice
Edited
Here a public web service that tested and returned null in the same way.
http://ws.sistemaindustria.org.br/api-basi/v1/entidades/3/departamentos.json
You have this service in a public way so we can test the access?
– Sorack
@Sorack is not only internal at the moment because it is in development yet. But it is open, has no authentication and when I call it in the browser returns the result passed in question.
– VictorP
@Marconi what I really need is to return to the complete list without passing any parameters. I put parameters only to test the return same and if it is necessary for another webservice. But if you can do both things in the same proc, better.
– VictorP
Not knowing the correct URL and if it really returns some result gets very difficult to help
– Sorack
@Sorack I published in the question the result of the webservice. I already know that returns and tested it with the passing of parameters. What I need is to know what to change in the code to display the complete list without having to pass any parameter.
– VictorP
Apparently your problem is not in the call but in the service that is not prepared to return without parameters
– Sorack
I have the two services. To receive passage of parameter and without passage of parameter.
– VictorP
I put in question a webservice with the output exactly the same as mine that I tested and gave the same result:
NULL– VictorP