topic Re: JSON based stored process works fine in SAS stpweb app but returns NO data while using API call in SAS Programming

JSON based stored process works fine in SAS stpweb app but returns NO data while using API call

saspert — Thu, 07 Sep 2017 20:02:07 GMT

Hello,

I have a stored process web app hosted on a AWS server. My json based stored process works fine on the stored process web app. But when I make an API call using postman/soapui, I get a 200 OK message but no data back.

https://myserver.compute-1.amazonaws.com:8343/SASBIWS/rest/storedProcesses/Products/xyz/scr2json

is the endpoint I am using.

This is the header information I am passing-

Accept: application/json

Content-Type: application/x-www-form-urlencoded

When using SOAPUI, I give the username/password on the request properties. Before that I create a new REST request using the above end point and just click on it

If I am using postman, I plug in the above URL, the header information noted above and also the username and password.

data _null_;
old = stpsrv_header("Content-type","application/json");
run;

options symbolgen mlogic mprint mprintnest merror ;

libname _webout xml ;
libname abcd '/mnt/data/SP';

%let survey_key=501;
proc sql;
create table ctgry_trgt_scrn as
select target_id, category_id, target_name, category_name
from abcd.survey_target_category
where survey_key=input("&survey_key.",4.);
run;

options nosyntaxcheck;

proc json out=_webout pretty nosastags;
write open object;
write values "rows";
write open array;
export ctgry_trgt_scrn;
write close;
write close;
run;

Re: JSON based stored process works fine in SAS stpweb app but returns NO data while using API call

BrunoMueller — Fri, 08 Sep 2017 06:44:25 GMT

Have you defined data targets in the Metadata of the Stored Process.

By default call a STP as Web Service will return the output parameters defined as a JSON structure.

To access other data, you will need to define a data target and use a different end point to call your STP as Web Service.

You will find a quick introduction on using a Stored Process as a Web Service on this page

https://www.sas.com/de_de/training/home/services/webinar-lunchtime.html

Search for "Stored Processes als Web-/REST-Service aufrufen"

You can download the slides and sample STP and SAS program code.

The slides are in German, I hope this gets you going.

Re: JSON based stored process works fine in SAS stpweb app but returns NO data while using API call

saspert — Fri, 08 Sep 2017 16:00:17 GMT

@BrunoMueller this is a great suggestion! So I did add the entry into the stored process and also the additional endpoint to the web service -

https://myserver.compute-1.amazonaws.com/SASBIWS/json/storedProcesses/Products/myclient/scr2json/dataTargets/myjson

I have the header inside the stored process set to

data _null_;
/*    old = stpsrv_header("Content-type","application/json");*/
/*     old = stpsrv_header("Accept","application/json");*/
  new=stpsrv_header("Content-type","application/x-www-form-urlencoded");
/*old=stpsrv_header("Content-type","application/json");*/

/* abc=stpsrv_header("Content-type","application/x-www-form-urlencoded");*/
run;

and the soapui header to this -

Accept application/json

Content-type application/x-www-form-urlencoded

but now I get >HTTP Status 500 - Request processing failed; nested exception is java.lang.NullPointerException error. I using a GET method using SOAPUI

Please point out if I need to change the header in my code and/or soapui request.

Re: JSON based stored process works fine in SAS stpweb app but returns NO data while using API call

BrunoMueller — Fri, 08 Sep 2017 17:05:17 GMT

In my example

I first write out the JSON to a temp file and use the DATA Step to stream it back to the appropriate fileref as defined in the data targets of the stored process metadata. Like so:

filename xjson temp;

proc json out=xjson pretty;
   export &tableName;
run;

data _null_;
  infile xjson;
  input;
  file myjson;
  put _infile_;
run;

filename xjson clear;

I do use the %STPBEGIN and %STPEND macros.

I do not set the application header

You can download the sample STP and Proc HTTP code to call the Web Service from the web site I mentioned earlier

also note, that if your SAS code has an error, you get the code 500, also check the Stored Process Server Log

Re: JSON based stored process works fine in SAS stpweb app but returns NO data while using API call

saspert — Mon, 11 Sep 2017 19:41:09 GMT

Many thanks for your timely help!