CallWebService Script Examples: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
No edit summary |
||
| Line 1: | Line 1: | ||
This page contains script examples for the [[ | This page contains script examples for the [[SQL Scripting Commands#--.23CallWebService|CallWebService]] script command. | ||
<pre> | <pre> | ||
Revision as of 20:49, 25 October 2021
This page contains script examples for the CallWebService script command.
(SELECT 'Method', 'GET') UNION ALL (SELECT 'Address', 'http://google.com') UNION ALL (SELECT 'Content-Type', 'application/json') UNION ALL (SELECT 'Accept', '*/*') --#CallWebService PRINT SUBSTRING(@_ResponseText, 1, 50);
When the script is run, entries similar to the following will be shown in the script log:
Execution duration: 0,753 seconds Execution Log: 2015-09-14T13:59:49.2838661+03:00 Notification 85 Script operation: "--#CallWebService" started 2015-09-14T13:59:49.3468813+03:00 Notification 85 Address: http://google.com Method: GET ContentType: application/json Encoding: Unicode (UTF-8) Body content length: 0 Timeout: 60000 ExecuteInClientSide: 0 Additional headers: Accept(3) 2015-09-14T13:59:49.6579900+03:00 Notification 85 Script operation: "--#CallWebService" completed: Result: OK, text length: 53019, status code: 200 2015-09-14T13:59:49.7230130+03:00 Notification 85 <!doctype html><html itemscope="" itemtype="http:/
Reload Model in the Memory
The following script triggers a reload (drop and load) for a QPR ProcessAnalyzer model. The script executes quickly as it does not wait for the model to be loaded, and thus the result of the reloading is not available in the script. The following information needs to be configured in the beginning of the script: QPR ProcessAnalyzer web service url, model id, username and password.
DECLARE @paWSUrl NVARCHAR(MAX);
DECLARE @modelId NVARCHAR(MAX);
DECLARE @username NVARCHAR(MAX);
DECLARE @password NVARCHAR(MAX);
SET @paWSUrl = 'http://localhost/QPRPA/';
SET @modelId = '12345';
SET @username = 'qpr';
SET @password = 'demo';
SELECT @paWSUrl AS paWSUrl, @modelId AS modelId, @username AS username, @password AS password INTO #loginData;
-- Authenticate
(SELECT 'Method', 'POST') UNION ALL
(SELECT 'Address', @paWSUrl + '/MainService.svc/webHttp/Authenticate') UNION ALL
(SELECT 'Body', '{"logOnName":"' + @username + '","password":"' + @password + '","parameters":[]}') UNION ALL
(SELECT 'ContentType', 'application/json') UNION ALL
(SELECT 'Accept', 'application/json')
--#CallWebService
PRINT 'Authentication result: ' + COALESCE(@_ResponseText, '<none>') + ' StatusCode: ' + CAST(@_ResponseStatusCode AS NVARCHAR(MAX)) + ' Success: ' + CAST(@_ResponseSuccess AS NVARCHAR(MAX));
DECLARE @sessionId NVARCHAR(MAX);
DECLARE @pos INT;
-- Extract session id
SET @pos = PATINDEX('%"SessionId","Value":"%', @_ResponseText);
PRINT @pos;
IF @pos > 0
BEGIN
SET @sessionId = SUBSTRING(@_ResponseText, @pos + 21, LEN(@_ResponseText))
SET @pos = PATINDEX('%"%',@sessionId);
IF @pos > 0
BEGIN
SET @sessionId = SUBSTRING(@sessionId, 0, @pos);
PRINT 'Session id: ' + @sessionId;
END;
END;
SELECT @sessionId AS sessionId INTO #SessionData;
DECLARE @paWSUrl NVARCHAR(MAX);
DECLARE @modelId NVARCHAR(MAX);
SELECT @paWSUrl = paWSUrl FROM #loginData;
SELECT @modelId = modelId FROM #loginData;
(SELECT 'Method', 'POST') UNION ALL
(SELECT 'Address', @paWSUrl + '/MainService.svc/webHttp/ResetModelCache') UNION ALL
(SELECT 'Body', '{"sessionId": "' + @sessionId + '","modelId":"' + @modelId + '","parameters": [{"Key":"ReloadModel","Value":"1"}]}') UNION ALL
(SELECT 'ContentType', 'application/json') UNION ALL
(SELECT 'Accept', 'application/json')
--#CallWebService
PRINT 'QueryObjectProperties result: ' + COALESCE(@_ResponseText, '<none>') + ' StatusCode: ' + CAST(@_ResponseStatusCode AS NVARCHAR(MAX)) + ' Success: ' + CAST(@_ResponseSuccess AS NVARCHAR(MAX));
DECLARE @sessionId NVARCHAR(MAX);
DECLARE @paWSUrl NVARCHAR(MAX);
SELECT @sessionId = sessionId FROM #SessionData;
SELECT @paWSUrl = paWSUrl FROM #loginData;
-- LogOff
(SELECT 'Method', 'POST') UNION ALL
(SELECT 'Address', @paWSUrl + '/MainService.svc/webHttp/LogOff') UNION ALL
(SELECT 'Body', '{"sessionId": "' + @sessionId + '"}') UNION ALL
(SELECT 'ContentType', 'application/json') UNION ALL
(SELECT 'ExecuteInClientSide', '1') UNION ALL
(SELECT 'Accept', 'application/json')
--#CallWebService
PRINT 'LogOff result: ' + COALESCE(@_ResponseText, '<none>') + ' StatusCode: ' + CAST(@_ResponseStatusCode AS NVARCHAR(MAX)) + ' Success: ' + CAST(@_ResponseSuccess AS NVARCHAR(MAX));
A ProcessAnalyzer script example to authenticate user, make a simple query into the server and logoff
------------------------------
--Example (Authenticate + QueryObjectProperties + LogOff):
------------------------------
-- Authenticate
(SELECT 'Method', 'POST') UNION ALL
(SELECT 'Address', 'http://<server IP address>/qprpa/MainService.svc/webHttp/Authenticate') UNION ALL
(SELECT 'Body', '{"logOnName":"GlobalAdmin","password":"demo", "parameters":{}}') UNION ALL
(SELECT 'ContentType', 'application/json') UNION ALL
(SELECT 'ExecuteInClientSide', '1') UNION ALL
(SELECT 'Accept', '*/*')
--#CallWebService
PRINT 'Authentication result: ' + COALESCE(@_ResponseText, '<none>') + ' StatusCode: ' + CAST(@_ResponseStatusCode AS NVARCHAR(MAX)) + ' Success: ' + CAST(@_ResponseSuccess AS NVARCHAR(MAX));
DECLARE @sessionId NVARCHAR(MAX);
DECLARE @pos INT;
-- Extract session id
SET @pos = PATINDEX('%""SessionId"",""Value"":""%',@_ResponseText);
IF @pos > 0
BEGIN
SET @sessionId = SUBSTRING(@_ResponseText, @pos + 21, LEN(@_ResponseText))
SET @pos = PATINDEX('%""%',@sessionId);
IF @pos > 0
BEGIN
SET @sessionId = SUBSTRING(@sessionId, 0, @pos)
PRINT 'Session id: ' + @sessionId;
END;
END;
SELECT @sessionId AS SES_ID INTO #SessionData;
-- QueryObjectProperties
(SELECT 'Method', 'POST') UNION ALL
(SELECT 'Address', 'http://<server IP address>/qprpa/QueryObjectProperties') UNION ALL
(SELECT 'Body', '{"sessionId": "' + @sessionId + '","uniqueObjectIds": ["PA.1.1"],"properties": [{ "Name": "name" }, { "Name": "typename" }],"parameters": []}') UNION ALL
(SELECT 'ContentType', 'application/json') UNION ALL
(SELECT 'ExecuteInClientSide', '1') UNION ALL
(SELECT 'Accept', '*/*')
--#CallWebService
PRINT 'QueryObjectProperties result: ' + COALESCE(@_ResponseText, '<none>') + ' StatusCode: ' + CAST(@_ResponseStatusCode AS NVARCHAR(MAX)) + ' Success: ' + CAST(@_ResponseSuccess AS NVARCHAR(MAX));
DECLARE @sessionId NVARCHAR(MAX);
SELECT @sessionId = SES_ID FROM #SessionData;
-- LogOff
(SELECT 'Method', 'POST') UNION ALL
(SELECT 'Address', 'http://<server IP address>/qprpa/LogOff') UNION ALL
(SELECT 'Body', '{"sessionId": "' + @sessionId + '"}') UNION ALL
(SELECT 'ContentType', 'application/json') UNION ALL
(SELECT 'ExecuteInClientSide', '1') UNION ALL
(SELECT 'Accept', '*/*')
--#CallWebService
PRINT 'LogOff result: ' + COALESCE(@_ResponseText, '<none>') + ' StatusCode: ' + CAST(@_ResponseStatusCode AS NVARCHAR(MAX)) + ' Success: ' + CAST(@_ResponseSuccess AS NVARCHAR(MAX));