Skip to main content

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

Hi @nkiphuth,

Looking at the naming convention of your API service and endpoint it looks like you are using an API used by an assistant (wizard) in the UI.

In order to support the step by step functionality in assistants these APIs use a mechanism known as “virtuals”. In this approach data in each step is stored  temporarily in a  “staging” table (i.e. virtual) until the user clicks “Finish” in the UI. This is when the business object is actually created/updated.

In this case you are probably only calling the REST end point of one of these intermediate steps which means the data is only created in the virtual table temporarily. Since the API endpoint corresponding to  the “Finish” button is not called the actual change is not persisted to the DB.

 

There are two options here:

  1. Use an alterative API which is not designed particularly for assistants (simpler and preferred for integration scenarios)
     
  2. Simulate API calls done in the assistant step by step including the “Finish” step from MS-SQL (This can be complicated since you need to follow the flow in the UI and perhaps not the best approach for integrations unless you want to build a similar external interactive UI)

 

 

Thanks Rifki, this is exactly the problem. Your explanation of the “Virtuals” was very informative. We’ve since been able to POST a status change. We have yet to be able to issue a line item to a Material Requisition, but I believe we’re on the right track. 

 

Any chance there’s a way to view all the API calls at once?  VS looking at each set in the API Explorer? I feel like this would be beneficial when trying to find certain calls.

 

Thanks for taking the time to post! 

Reply


Terms & ConditionsCookie settings