Executing a Business Rule via the Infor CRM SData API from T-SQL

I had the need to call a business rule in Infor CRM from a stored procedure in SQL. This turned out to be a relatively easy task, although you want to make sure you think through what you’re doing for this to not introduce performance problems in SQL. Keep in mind, sometimes SData requests can take a bit to execute, not to mention that you’re calling this via HTTP and doing it all via OLE automation using MSXML2.ServerXMLHttp. With all that in mind, here’s how it’s done.

First, you need to make sure that OLE Automation Procedures in SQL have been enabled. You can do that with the following:

sp_configure 'show advanced options', 1  
go
reconfigure
go
sp_configure 'Ole Automation Procedures', 1
go
reconfigure
go

With that turned on, you can now use things like sp_OACreate, etc, which we’ll need to do the HTTP Posts to SData. In this example, my business rule is for the ERPShipTo entity and does not take any additional parameters.

declare @obj int 
declare @url varchar(200) 
declare @body varchar(8000)
declare @response varchar(8000)
declare @username varchar(20), @password varchar(20), @auth varchar(100) 

-- my business rule is for the ERPShipTo entity and 
-- is named CreateUpdateShipToAccount
set @url = 'http://localhost:3333/sdata/slx/dynamic/-/erpShipTos/$service/CreateUpdateShipToAccount?format=json'

-- set username & password 
set @username = 'admin'
set @password = ''

-- create base 64 auth string for request
set @auth = 'Basic ' + (select cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'varchar(max)') Base64Enc from (select cast(@username + ':' + @password as varbinary(max)) as bin) as encodedString)

-- this is the payload for the rule
set @body = '{ "request": { "ERPShipToId": "Q6UJ9A00VI77" } }'

-- create the OLE object for hte request 
exec sp_OACreate 'MSXML2.ServerXMLHttp', @obj out 
exec sp_OAMethod @obj, 'open', null, 'POST', @url, false 
exec sp_OAMethod @obj, 'setRequestHeader', null, 'Authorization', @auth
exec sp_OAMethod @obj, 'setRequestHeader', null, 'Content-Type', 'application/json'
exec sp_OAMethod @obj, 'send', null, @body
exec sp_OAGetProperty @obj, 'responseText', @response out 
exec sp_OADestroy @obj 

-- view response
select @response

That’s it. If you’re not sure what parameters are needed for your business rule request, you can open this in a browser to see what parameters it is expecting (of course, replace the entity and rule names):

http://localhost:3333/sdata/slx/dynamic/-/erpShipTos/$service/CreateUpdateShipToAccount/$template?format=json

Also, you can see the rules for an entity by opening the following (replace the entity name with your entity):

http://localhost:3333/sdata/slx/dynamic/-/erpShipTos/$service?format=json