The Dell Boomi database connector only allows parameters of 3 types, Character, Number and Date/Time. Behind the scenes Boomi then determines the more specific type to use in the call. I am not sure what logic is used to make this determination and their support team did not either, but it is not based on the parameter types of the stored procedure.

Since stored procedures in postgres are identified by the name, parameters and parameter types if Boomi gets the parameter type wrong it will fail. Since there is no option for a Boolean Parameter Type in Boomi stored procedures that contain Boolean parameters always fail. I have also run into situations where Boomi specifies a Number type different than the one required by my stored procedure, which creates the same problem.

There are two ways around this. Change the type of the parameter and deal with it in the stored procedure code, not an ideal solution, especially if you are calling these stored procedures form multiple platforms, or CAST the parameters in the Boomi Database Profile to be of the correct type.

To demonstrate how you would set this up let’s use the example of a stored procedure called upsert that upserts records based on one character and one boolean parameter and returns the id of the record.

1. Create a new Database Profile and under Options set the Execution Type to Read
2. In the Data Elements section set the type to Select
3. In the SQL Script box input “SELECT upsert( ?, CAST(? AS BOOLEAN))”
4. In the Fields section add a field called “returnValue” and set the Data Type to be the type of your return
5. In the parameters section add two parameters with the Character Data Type with Field Names matching the field names in your stored procedure.