Anyone in here Call IIQ APIs From SQL Server Stored Procedures?
+1
Thanks for your reply! Does your team have a script that actually worked with IIQ, similar to this not_IIQ_API example?
DECLARE @URL NVARCHAR(MAX) = 'http://localhost:8091/api/v1/employees/getemployees';
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
@URL,
'False'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
IF((Select @ResponseText) <> '')
BEGIN
DECLARE @json NVARCHAR(MAX) = (Select @ResponseText)
SELECT *
FROM OPENJSON(@json)
WITH (
EmployeeName NVARCHAR(30) '$.employeeName',
Title NVARCHAR(50) '$.title',
BirthDate NVARCHAR(50) '$.birthDate',
HireDate NVARCHAR(50) '$.hireDate',
Address NVARCHAR(50) '$.address',
City NVARCHAR(50) '$.city',
Region NVARCHAR(50) '$.region',
PostalCode NVARCHAR(50) '$.postalCode',
Country NVARCHAR(50) '$.country',
HomePhone NVARCHAR(50) '$.homePhone'
);
END
ELSE
BEGIN
DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';
Print @ErroMsg;
END
Exec sp_OADestroy @Object--CREATE JSON
SET @authHeader = 'ADD AUTH HEADER';
SET @contentType = 'application/json';
SET @postData = '{
"@type": "MessageCard",
"@context": "http://schema.org/extensions",
"themeColor": "92C83E",
"summary": "SUMMARY CHANGE ME",
"sections": [{
"activityTitle": "TITLE CHANGE ME",
"activitySubtitle": "SUBTITLE CHANGE ME",
"activityImage": "",
"facts": [{
"name": "changes: ",
"value": "''"
}],
"markdown": "true"
}],
"Text" : "''"
}'
print @postdata
--Infinite Campus Log
--SET @url = 'your url'
SET @url = 'your url'
-- Open the connection.
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);
-- Send the request.
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authentication', @authHeader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', 'application/json';
EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData;
-- Handle the response.
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;
-- Show the response.
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;
-- Close the connection.
EXEC @ret = sp_OADestroy @token;
IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
