Call IIQ APIs From SQL Server Stored Procedures

@mikegamble - We’ve used Stored Procedures to call IIQ API’s before but I’m told by my team that it was a pain so we eventually moved everything over to Powershell.

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

@mikegamble - Since we converted all of our scripts over to PowerShell we don’t have the exact scripts that we used with IIQ, however the method is similar to this if this helps.

--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);

@mikegamble - Do you happen to be a Microsoft district by chance?