REST API Post Integration from SQL | IFS Community
Question

REST API Post Integration from SQL


Badge +1

Looking for a solution to call a POST REST API from Microsoft SQL. We are able to use the GET successfully, but the POST is giving us issues. Below is an example of trying to issue a part to a Maintenance Material Requisition. We get the correct response Status: 201 (Created), and JSON Response text...but it appears we’re missing a “COMMIT” of sorts.

Any suggestions?

 

DECLARE @authHeader NVARCHAR(64);
DECLARE @contentType NVARCHAR(64);
DECLARE @postData NVARCHAR(2000);
DECLARE @responseText NVARCHAR(2000);
DECLARE @responseXML NVARCHAR(2000);
DECLARE @ret INT;
DECLARE @status NVARCHAR(32);
DECLARE @statusText NVARCHAR(32);
DECLARE @token INT;
DECLARE @url NVARCHAR(256);
DECLARE @Authorization NVARCHAR(200);

--set your post params
SET @authHeader = 'Basic sampledata';
SET @contentType = 'application/json;charset=utf-8'
SET @postData = '{
  "ParentObjkey": "27AAD84E36F344E0995DB764FC5F13A4",
  "ObjCreatedBy": "",
  "Rental": true,
  "ToDefaultWarehouse": true,
  "SpareId": "",
  "PartDescription": "",
  "SpareContract": "",
  "QuantityLeft": 3,
  "Owner": "",
  "OwnerName": "",
  "PartOwnership": "CompanyOwned",
  "ConditionCode": "",
  "InventoryUom": "",
  "CatchUom": "",
  "ProjectId": "",
  "ProjectDescription": "",
  "ActivitySeq": 1,
  "MaintMaterialOrderNo": 6,
  "LineItemNo": 1,
  "ToLocationNo": "",
  "SupplyCode": "",
  "ProjectInventory": "",
  "StandardInventory": "",
  "OwningCustomerNo": "",
  "ConsignmentStock": "",
  "WoExecutedBy": "",
  "UserWarehouse": "All",
  "HasDefaultWarehouse": "",
  "ConnectedLocationsAvailable": "",
  "MaxQuantityLeft": 3
}'
SET @url = 'https://sampledata/int/ifsapplications/projection/v1/ManualIssueAssistantHandling.svc/ManualIssueVirtualSet'

-- 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';
IF @ret <> 0 EXEC sp_OAGetErrorInfo @token

--set a custom header Authorization is the header key and VALUE is the value in the header
--EXEC sp_OAMethod @token, 'SetRequestHeader', NULL, 'Authorization', 'VALUE'

EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authorization', @authHeader;
IF @ret <> 0 EXEC sp_OAGetErrorInfo @token
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
IF @ret <> 0 EXEC sp_OAGetErrorInfo @token

EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData;
IF @ret <> 0 EXEC sp_OAGetErrorInfo @token

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

 

Apps10 UPD14


0 replies

Be the first to reply!

Reply