Hello
I'm looking to get data from the REST DataService via a local Stored Procedure.
I have already changed the web.config to allow the < symbol:
From: <httpRuntime maxRequestLength="20480" />
otherwise I got the result:
<title>A potentially dangerous Request.Path value was detected from the client (="...e/Execute/<LocalProcedureReque...").</
As I understand it we need a procedure in TR_LOCAL_PROCEDURES and we use the reference number from this in a REST client application. I'm using "REST Console" and "Advanced Rest Client Application" for Chrome.
I'm able to get info from other REST methods, so connectivity isn't the issue.
I have tried my own:
https://tessituratestapi.rsc.org.uk/TEST/DataService/Execute/<LocalProcedureRequest><Parameters>@startDate='2004-11-16',@endDate='2099-12-30',@keyword=4</Parameters><ProcedureId>7512</ProcedureId></LocalProcedureRequest>
and have used the example to POST the following
https://tessituratestapi.rsc.org.uk/TEST/DataService/Execute/<LocalProcedureRequest><Parameters>@name=abc&@age=5&@postalCode=75001</Parameters><ProcedureId>123</ProcedureId></LocalProcedureRequest>
and the result I get is a 404 endpoint not found. (ID 123 doesn't exist, but see below)
https://drive.google.com/file/d/0B4vgogd9f1zxaEJhQ3BoQjd2dlk/edit?usp=sharing
I've even tried taking everything away except the ProcedureID and escaping every <, > and =
Below is what the server received from RequestResoponse.log:
----------------------------------------------
2013-11-16 20:34:29.1294|Request : [POST] At [/TEST/DataService/Execute/<LocalProcedureRequest><Parameters>@name=abc&@age=5&@postalCode=75001</Parameters><ProcedureId>123</ProcedureId></LocalProcedureRequest>] With headers [Connection : keep-alive , Content-Length : 0 , Content-Type : application/x-www-form-urlencoded , Accept : */* , Accept-Encoding : gzip,deflate,sdch , Accept-Language : en,en-GB;q=0.8 , Authorization : Basic d2V2YW5zOmFkbWluOnJzY2E2OTc2YjolNDc2MGZlWQ== , Cookie : __utma=180062119.928214813.1362355627.1372459730.1377725808.5; __utmz=180062119.1362355627.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); _ga=GA1.3.928214813.1362355627 , Host : tessituratestapi.rsc.org.uk , User-Agent : Mozilla/5.0 (Windows NT 5.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36 , Origin : chrome-extension://hgmloofddffdnphfgcellkdfbfbjeloo ]
Request Content Details : [ Type : application/x-www-form-urlencoded, Length : 0, Encoding : System.Text.UTF8Encoding ]
Request User Details : [ Host Name : 192.168.5.1, Address : 192.168.5.1, Agent : Mozilla/5.0 (Windows NT 5.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36 ]
2013-11-16 20:44:27.7232|Request : [POST] At [/TEST/DataService/Execute/<LocalProcedureRequest><Parameters>@startDate='2004-11-16',@endDate='2099-12-30',@keyword=4</Parameters><ProcedureId>7512</ProcedureId></LocalProcedureRequest>] With headers [Connection : keep-alive , Content-Length : 0 , Content-Type : application/x-www-form-urlencoded , Accept : */* , Accept-Encoding : gzip,deflate,sdch , Accept-Language : en,en-GB;q=0.8 , Authorization : Basic d2V2YW5zOmFkbWluOnJzY2E2OTc2YjolNDc2MGZlWQ== , Cookie : __utma=180062119.928214813.1362355627.1372459730.1377725808.5; __utmz=180062119.1362355627.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); _ga=GA1.3.928214813.1362355627 , Host : tessituratestapi.rsc.org.uk , User-Agent : Mozilla/5.0 (Windows NT 5.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36 , Origin : chrome-extension://hgmloofddffdnphfgcellkdfbfbjeloo ]
I'm assuming the process would lookup the StoredProcedure name from the ID, then attempt to run it with whatever parameters are given. I'm doing a trace, but cannot see this reaching the database, but I can the following getting to the database via trace:
EXEC AP_SET_SESSION_CONTEXT @uid='wevans', @ug='admin', @location='RSCA6975N'
I must be doing something wrong, but can't see what.
If you've got any DataService/Execute/ stored procedures working, did you have any issues retrieving data.
Thanks
--Wayne
Hi Wayne,
It's tough to know what exactly might be happening, but it looks from your screenshot as though you're trying to send the POST data through the URL which is not going to work. You'll want to put
<LocalProcedureRequest> <Parameters>@startDate='2004-11-16'&@endDate='2099-12-30'&@keyword=4</Parameters <ProcedureId>7512</ProcedureId></LocalProcedureRequest>
Into the request body that you send to https://tessituratestapi.rsc.org.uk/TEST/DataService/Execute/
If that's not the issue, sometimes I've found that replacing & with & in the <Parameters> will also help solve any problems.
Hope that helps!
Thanks Paul
that does make sense. I was getting blinded to making it work the same way as the other services which can work in the url (looking up constituent details)
I'll give this a try and put my web config back to the original safe method
Wayne
Thanks Paul. That pointed me in the right direction
Finally got it and was really simple... once I'd figured it out
Advance Rest Client Screenshot
The contents of the output are unimportant, just no error messages and I have data.
If anyone else is struggling, and searches this, hopefully this will help. If you've got it working already, skip this, you know this already.
Switch NLOG rules to "Debug" on TEST. Open todays DataService log and RequestResponse.log in notepad++ (it alerts you something has changed and prompts you to reload it)
My Service URL:
https://tessituratestapi.rsc.org.uk/TEST/DataService/Execute/
My Headers:Authorization: Basic d2V2YsnippedZlWQ==
My RAW Payload (body):
<LocalProcedureRequest><Parameters>@startDate=2004-11-16&@endDate=2015-01-01&@keyword=4</Parameters><ProcedureId>7512</ProcedureId></LocalProcedureRequest>
The errors I got after moving the data to the raw body were (entered for forum searching purposes later) :
2013-11-17 10:10:31.6838|Error|There was an error executing the Local SP. Error Message : Procedure or function 'LWP_GET_PERFORMANCES_KEYWORDS' expects parameter '@startDate', which was not supplied.
2013-11-17 10:09:20.4187|Error|There was an error deserializing the object of type Tessitura.DataService.Client.LocalProcedureRequest. Unexpected end of file. Following elements are not closed: Parameters, LocalProcedureRequest. Line 1, position 124.
Gotchas here were the & symbols between parameters. A single parameter worked, but this SP required multiple parameters. When running this from this client, I needed to escape the character with & not forward slash amp (/&) mentioned in the REST help.