Creating direct SQL statements

Under certain circumstances, SQL statements may need to be run to select data, update data, delete data, or retrieve data independently of the data model. For example, a business operation may insert or delete records in data tables that are not defined in the logical model. Another example would be a case where you update multiple data objects, and it is more efficient to issue a direct SQL rather than using the data service layer to retrieve and update each object. You can create direct SQL statements in these cases, which are stored in a specially named query template file.

About this task

Direct SQL statements are executed directly using the JDBCQueryService class. All direct SQL statements must be in the SQL_STATEMENT section of a file named wc-query-utilities.tpl. This section is the only section that can contain select statements that use aggregate functions, like sum() or avg(), as the result of these queries does not map to physical Java objects by object-relational metadata.

Note: Within the same transaction, you should never read or update the same data using the JDBCQueryService class in conjunction with any of the following classes: PhysicalDataContainer, ChangesBusinessObjectMediator, or ReadBusinessMediator.

When creating customized direct SQL statements in your workspace environment, you must consider the following conditions:

Table names should be the only identifying information when referencing tables. That is, schema names should not be included as part of the table name when referencing tables. For example, TABLE_NAME is an acceptable table name, while SCHEMA_NAME.TABLE_NAME is not.
The WHERE clause in an SQL statement can be any valid SQL search condition, and should not use any database SQL functions. For example, IN or = are acceptable search conditions, while MIN or MAX are not.
SQL statements should be in the form of a complete SQL statement, and should not call stored procedures such as GetItems or ShipItems.

To create your own direct SQL queries:

Procedure

Create a WC\config\com.ibm.commerce.servicemodule-ext folder, if one does not already exist:
1. Right-click the WC\config\com.ibm.commerce.servicemodule-ext folder. You must create the folder in the -ext directory. Do not modify HCL Commerce query templates directly.
Create a custom query template file for your direct SQL statements:
1. Click New > Other > Simple > File > Next
2. Name the custom query template file: wc-query-utilities.tpl.
3. Click Finish.
If needed, create symbol definitions inside a BEGIN_SYMBOL_DEFINITIONS/END_SYMBOL_DEFINITIONS block. Symbol definitions are discussed in detail in Query template file syntax description.
Create an empty BEGIN_SQL_STATEMENT / END_SQL_STATEMENT block, using the following lines:
```
BEGIN_SQL_STATEMENT
  name= myStatementName
  base_table= BASETABLENAME
  sql= 
END_SQL_STATEMENT  
```
1. Set myStatementName to the name of the SQL statement. You will use this name when you call the statement directly in your Java code using the JDBCQueryService class.
2. Set BASETABLENAME to the name of the base table you are accessing.

Add your SQL using the sql= part of the block. Your SQL must follow one of the supported statement formats shown in the following table:

Statement type	Supported statement formats
Select statement	SELECT FROM `TABLE_NAME` [WHERE `<SEARCH CONDITION>`]
Update statement	UPDATE `TABLE_NAME` SET `COL_1`=`<VALUE EXPRESSION 1>`, `COL_2`=`<VALUE EXPRESSION 2>`, ... `COL_N`=`<VALUE EXPRESSION N>` [WHERE `<SEARCH CONDITION>`]
Delete statement	DELETE FROM `TABLE_NAME` [WHERE `<SEARCH CONDITION>`]
Insert statement	INSERT INTO `TABLE_NAME` [(`COL_1`, `COL_2`, ... `COL_N`)] VALUES (`<VALUE EXPRESSION 1>`, `<VALUE EXPRESSION 2>`, ... `<VALUE EXPRESSION N>`) INSERT INTO `TABLE_NAME` [(`COL_1`, `COL_2`, ... `COL_N`)] SELECT `COL_1`, `COL_2`, ... FROM `TABLE_NAME` T1 [WHERE `<SEARCH CONDITION>`]

<VALUE EXPRESSION> can be a literal value, or any expression that evaluates to a value (such as a CASE statement block). However, the <VALUE EXPRESSION> cannot be a subselect statement.

Note: If there are any literal strings containing SQL keywords in the data in your <VALUE EXPRESSION>, or in your custom table/column names, the internal JDBC SQL parser will read the SQL incorrectly and throw an exception. You can use parameter markers instead of literal strings, as discussed in SQL parameters.

<SEARCH CONDITION> does not have the restrictions that apply to <VALUE EXPRESSION>, and can be any valid SQL search condition.

Example

The following example shows a supported direct update SQL statement, following the format UPDATE <TABLE NAME> SET COL_1=<VALUE EXPRESSION 1> WHERE < SEARCH CONDITION>.

BEGIN_SQL_STATEMENT 
  name=IBM_Update_TopCatGroupSequence 
  base_table=CATTOGRP 
  sql= UPDATE CATTOGRP 
    SET CATTOGRP.SEQUENCE = ?sequence? 
    WHERE CATTOGRP.CATGROUP_ID IN (?catalogGroupID?) AND CATTOGRP.CATALOG_ID = ?catalogID? 
END_SQL_STATEMENT