How to create SQL Query in order to avoid complexity and unwanted errors.

Requirement: Sometimes, we come across a scenario where we have to use a DBQuery function in Data Mapping in order to fetch the data from Database and require to create a SQL query using Concat function. Since source fields and string have to be used in the queries using Concat function which makes the query complex and might result in some unwanted errors/ missing brackets, commas, $apos.

As shown below:-

runQueryString: concat( string('insert into PRTC_KEY_ID_REF'),' (', string('PRTC_KEY_ID, PRTC_ID,CLIENT_ID,ROW_UPDATE_DATE'),')', string(' values'), '(',
$apos,string('789'),$apos,',',
$apos,string('789'),$apos,',',
$apos,string('ORCL'),$apos,',',
string('sysdate'), ')' )

runQuery:  java:MapperUtilityClass.executeDbInsertQuery( $mysql ,$runQueryString )

Solution: We can make the SQL Query syntax by removing the unwanted string functions and avoiding the escaping of single quotes. Consider the above example, we can avoid the $apos and the string functions used inside the concat by simply using the below rule:

• Mapper Utility Class function

java:MapperUtilityClass.executeDbInsertQuery($mysql , "insert into PRTC_KEY_ID_REF (PRTC_KEY_ID, PRTC_ID,CLIENT_ID,ROW_UPDATE_DATE) values('789','789','ORCL',sysdate())")

Note that, we are not using concat as well for this particular query. The string values can simply define within single quotes. But if we wish to give a source XPath in one of the columns, we will have to make use of the concat function to make the query and include the single quotes of Xpath string value within concat double-quotes. Consider the below example:-

java:MapperUtilityClass.executeDbInsertQuery( $mysql, concat("insert into PRTC_KEY_ID_REF (PRTC_KEY_ID, PRTC_ID,CLIENT_ID,ROW_UPDATE_DATE) values('",$Input_benefits_csv_file/Root/Record/EmpSSN,"','789','ORCL', sysdate())") )

Explanation: In the above rule, we wanted to give PRTC_KEY_ID be the EmpSSN from source file, so to make this query, we used the concat function.
Here, The first parameter of concat function is: "insert into PRTC_KEY_ID_REF (PRTC_KEY_ID, PRTC_ID,CLIENT_ID,ROW_UPDATE_DATE) values('"
The second parameter of concat function is: $Input_benefits_csv_file/Root/Record/EmpSSN
The third parameter of concat function is: "','789','ORCL',sysdate())"

Query: concat( "insert into PRTC_KEY_ID_REF (PRTC_KEY_ID, PRTC_ID,CLIENT_ID,ROW_UPDATE_DATE) values('" ,$Input_benefits_csv_file/Root/Record/EmpSSN, "','789','ORCL',sysdate())" )
The output of above queries when mapping is executed:

With this approach, we are able to avoid the use of $apos, string functions, and having more number of parameters to concat, so this reduces the chances of having missing brackets, commas, $apos etc... 

Likewise, if you want to provide more than one source Xpaths in values, then the query will be

concat( "insert into PRTC_KEY_ID_REF (PRTC_KEY_ID, PRTC_ID,CLIENT_ID,ROW_UPDATE_DATE) values('" , $Input_benefits_csv_file/Root/Record/EmpSSN, "','",$Input_benefits_csv_file/Root/Record/EmpCode,"','ORCL',sysdate())" )

• DBQuery function

 With the same approach, we can create the query to use with DBQuery function. Here also, we will first make a variable in Data Mapping( usually local variable) which will have the concat rule as follows for making the query:

Query: concat( "Select PRTC_KEY_ID from PRTC_KEY_ID_REF where PRTC_KEY_ID='",$Input_benefits_csv_file/Root/Record/EmpSSN,"' AND PRTC_ID='",$Input_benefits_csv_file/Root/Record/EmpZipCode,"'" ) 

Now we can define our DBQuery function as follows:

DBQuery { $query, $mysql, false }

Note: You cannot directly use the concat function instead of $query in the DBQuery as it results in an error.