Hardcode Values within SQL Statements with the Database v2 Connector

The Database v2 connectors allows you to define a SQL statement within most of the operations. With some integrations that are SQL statement dominate, you might choose to hardcode values within the SQL statement instead of setting a default value within a map. This action is done in the Database v2 connector slightly differently than the Database (v1) connector. The Database (v1) connector allowed you to perform a GET action with the following query and it would return data as expected. The example below would return the first column as a ‘hello’ and as a string.

SELECT 
	'hello' 
FROM DUAL

The Database v2 connector relies on knowing the data type for each column (Boomi Doc: Supported Operations and Data Types). So, while ‘hello’ is valid SEQ, the connector will not recognize it because the data type is unknown. To correct this, you’ll need to define the data type within your SQL statement. The example below shows two variations with SQL. The first column does not return data, but it also does not return any errors. The second columns will return data as expected. The cast() function is used to define the data type.

SELECT 
	'hello' hello,                              -- Does not return data
    cast('hello' as varchar2(5)) hello,         -- Does return data 
FROM DUAL	

Article originally posted at Boomi Community.