June 18, 2019 at 10:07 pm
Hello,
We use a service that provides an API for pulling our real-time data. I'm trying to retrieve that data into a table so I can build some functionality in-house consuming that data. I found a few script examples but I can't find complete documentation on the stored procedures that are referenced.
The first call is returning the first value called the wssid they say is to be used in future calls (although their documentation doesn't have any reference to use it). The next call is supposed to give me an authentication cookie that I'm supposed to send in subsequent calls (in the RequestHeader?) so the service knows I'm authenticated. All its returning right now is a web page instead of the cookies. Since I can't find any documentation on sp_OAGetProperty, I can't figure out how to get the cookies that are returned.
ALTER procedure sp_GetRequestRESTfulAPI
AS
BEGIN
DECLARE @ResponseTable TABLE (StatusCode NVARCHAR(32),
StatusText NVARCHAR(32),
ResponseText NVARCHAR(MAX),
SpErrorMessage VARCHAR(MAX));
DECLARE @ResponseText TABLE (Content NVARCHAR(MAX));
DECLARE @Ret INT;
DECLARE @Status NVARCHAR(32);
DECLARE @StatusText NVARCHAR(32);
DECLARE @spErrorMessages NVARCHAR(4000);
DECLARE @Token INT;
-- Open the connection
EXEC @Ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @Token OUT;
IF @Ret <> 0
BEGIN
INSERT INTO @ResponseTable (spErrorMessage)
VALUES ('Unable to open HTTP connection');
SELECT *
FROM @ResponseTable;
RETURN;
END;
-- Send the WSSID request.
EXEC @Ret = sp_OAMethod @Token, 'OPEN', NULL, 'GET', 'https://merchant.qless.com/qless/api/v1/wssid', 'FALSE';
EXEC @Ret = sp_OAMethod @Token, 'SEND', NULL, NULL;
-- Handle the response.
EXEC @Ret = sp_OAGetProperty @Token, 'Status', @Status OUT;
EXEC @Ret = sp_OAGetProperty @Token, 'StatusText', @StatusText OUT;
DELETE FROM @ResponseText;
INSERT INTO @ResponseText (Content)
EXEC sp_OAGetProperty @Token, 'ResponseText';
INSERT INTO @ResponseTable (StatusCode,
StatusText,
ResponseText,
SpErrorMessage)
VALUES (@Status,
@StatusText,
(SELECT Content FROM @ResponseText),
'WSSID');
-- Send the authentication request.
EXEC @Ret = sp_OAMethod @Token, 'OPEN', NULL, 'POST', 'https://merchant.qless.com/qless/authenticator?principal=[my username here]&credentials=[my password here]&remember=true', 'FALSE';
EXEC @Ret = sp_OAMethod @Token, 'setRequestHeader', NULL, 'Content-type', 'text/xml; charset=utf-8';
EXEC @Ret = sp_OAMethod @Token, 'SEND', NULL, NULL;
-- Handle the response.
EXEC @Ret = sp_OAGetProperty @Token, 'Status', @Status OUT;
EXEC @Ret = sp_OAGetProperty @Token, 'StatusText', @StatusText OUT;
DELETE FROM @ResponseText;
INSERT INTO @ResponseText (Content)
EXEC sp_OAGetProperty @Token, 'ResponseText';
INSERT INTO @ResponseTable (StatusCode,
StatusText,
ResponseText,
SpErrorMessage)
VALUES (@Status,
@StatusText,
(SELECT Content FROM @ResponseText),
'Authentication Reply');
-- Get the authorization info.
EXEC @Ret = sp_OAMethod @Token, 'OPEN', NULL, 'GET', 'https://merchant.qless.com/qless/api/v1/employee/authInfo', 'FALSE';
--EXEC @Ret = sp_OAMethod @Token, 'setRequestHeader', NULL, 'Content-type', 'application/x-www-form-urlencoded';
EXEC @Ret = sp_OAMethod @Token, 'SEND', NULL, NULL;
-- Handle the response.
EXEC @Ret = sp_OAGetProperty @Token, 'Status', @Status OUT;
EXEC @Ret = sp_OAGetProperty @Token, 'StatusText', @StatusText OUT;
DELETE FROM @ResponseText;
INSERT INTO @ResponseText (Content)
EXEC sp_OAGetProperty @Token, 'ResponseText';
INSERT INTO @ResponseTable (StatusCode,
StatusText,
ResponseText,
SpErrorMessage)
VALUES (@Status,
@StatusText,
(SELECT Content FROM @ResponseText),
'Authentication Info');
-- Get the employee info.
EXEC @Ret = sp_OAMethod @Token, 'OPEN', NULL, 'GET', 'https://merchant.qless.com/qless/api/v1/employee/employee', 'FALSE';
--EXEC @Ret = sp_OAMethod @Token, 'setRequestHeader', NULL, 'Content-type', 'multipart/form-data';
EXEC @Ret = sp_OAMethod @Token, 'SEND', NULL, NULL;
-- Handle the response.
EXEC @Ret = sp_OAGetProperty @Token, 'Status', @Status OUT;
EXEC @Ret = sp_OAGetProperty @Token, 'StatusText', @StatusText OUT;
DELETE FROM @ResponseText;
INSERT INTO @ResponseText (Content)
EXEC sp_OAGetProperty @Token, 'ResponseText';
INSERT INTO @ResponseTable (StatusCode,
StatusText,
ResponseText,
SpErrorMessage)
VALUES (@Status,
@StatusText,
(SELECT Content FROM @ResponseText),
'Employee Info');
-- Get the locations.
EXEC @Ret = sp_OAMethod @Token, 'OPEN', NULL, 'GET', 'https://merchant.qless.com/qless/api/v1/employee/locations', 'FALSE';
--EXEC @Ret = sp_OAMethod @Token, 'setRequestHeader', NULL, 'Content-type', 'multipart/form-data';
EXEC @Ret = sp_OAMethod @Token, 'SEND', NULL, NULL;
-- Handle the response.
EXEC @Ret = sp_OAGetProperty @Token, 'Status', @Status OUT;
EXEC @Ret = sp_OAGetProperty @Token, 'StatusText', @StatusText OUT;
DELETE FROM @ResponseText;
INSERT INTO @ResponseText (Content)
EXEC sp_OAGetProperty @Token, 'ResponseText';
INSERT INTO @ResponseTable (StatusCode,
StatusText,
ResponseText,
SpErrorMessage)
VALUES (@Status,
@StatusText,
(SELECT Content FROM @ResponseText),
'Locations');
-- Close the connection
EXEC @Ret = sp_OADestroy @Token;
IF @Ret <> 0
BEGIN
INSERT INTO @ResponseTable (spErrorMessage)
VALUES ('Unable to close HTTP connection');
SELECT *
FROM @ResponseTable;
RETURN;
END;
SELECT *
FROM @ResponseTable;
END
This is what the vendor provides for documentation on the authenticator:
/qless/authenticator
POST
Authenticates the user's userid and password and returns an Authentication Cookie (named "au") and an Identity Cookie (named "i"), which can be used together to authenticate future calls. The Authenticator will send a 302 redirect to the QLess Queue Manager UI upon a successful login because it is also used by the web UI. For the purpose of calling the web services, all we are interested in is retrieving the cookies, which will be included in the HTTP response headers.
Example request: https://merchant.qless.com/qless/authenticator
Example POST data: principal=myuserid&credentials=secret&remember=true
Example response (excerpt):
Set-Cookie: au=IgYVB...=; Path=/
Set-Cookie: i=ayKtm+...=; Path=/
How do I get these cookies and how to I add them to subsequent requests?
June 19, 2019 at 10:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply