Standard Query:
var insertStatement = "" +
"INSERT INTO tmp_common_types (" +
" varcharCol, " +
" bigintCol, " +
" dateCol " +
") VALUES (" +
" '{mc:varcharMc}', " +
" {mc:bigintMc}, " +
" '{mc:dateMc}' " +
")";
var statementCount = qie.doUpdateQuery('works', insertStatement);
qie.debug("insert count: " + statementCount);
var selectStatement = "" +
"SELECT " +
" varcharCol, " +
" bigintCol, " +
" dateCol " +
" FROM tmp_common_types " +
" WHERE " +
" varcharCol = '{mc:varcharMc}' " +
" AND bigintCol = {mc:bigintMc} " +
" AND dateCol = '{mc:dateMc}' " +
"";
var results = qie.doQuery('works', selectStatement, true);
qie.debug(results);
Note: that in the above INSERT and SELECT statements that we are using "Node Tags" to pull from the message cache variables named: varcharMc, bigIntMc, and dateMc. If these message cache variables were set by an incoming message then SQL Injection could be a problem. The conversion to parameterized Queries below fixes this problem. Also, note the use of single quotes in the inserted values and the predicates.
Parameterized Query:
var insertStatement = "" +
"INSERT INTO tmp_common_types (" +
" varcharCol, " +
" bigintCol, " +
" dateCol " +
") VALUES (" +
" :varcharVal, " +
" :bigintVal, " +
" :dateVal " +
")";
var pQuery = qie.getParameterizedQuery(insertStatement);
pQuery.setString('varcharVal', messageCache.getValue('varcharMc'));
pQuery.setLong('bigintVal', new java.lang.Long(messageCache.getValue('bigintMc')));
pQuery.setDate('dateVal', qie.deduceDate(messageCache.getValue('dateMc')));
var statementCount = pQuery.doUpdateQuery('works');
qie.debug("insert count: " + statementCount);
var selectStatement = "" +
"SELECT " +
" varcharCol, " +
" bigintCol, " +
" dateCol " +
" FROM tmp_common_types " +
" WHERE " +
" varcharCol = :varcharVal " +
" AND bigintCol = :bigintVal " +
" AND dateCol = :dateVal " +
"";
pQuery = qie.getParameterizedQuery(selectStatement);
pQuery.setString('varcharVal', messageCache.getValue('varcharMc'));
pQuery.setLong('bigintVal', new java.lang.Long(messageCache.getValue('bigintMc')));
pQuery.setDate('dateVal', qie.deduceDate(messageCache.getValue('dateMc')));
var results = pQuery.doQuery('works', true);
qie.debug(results);
The bigest difference above is that we have to create a ParameterizedQuery object. Once that is done we can set its values. Finaly, it is executed. The node tags ({mc:varcharMc}) were replaced by named parameters (:varcharVal) even though the values are still set from the message cache variable in the set methods (pQuery.setString('varcharVal', messageCache.getValue('varcharMc'))). Note that except for the qie.getParameterizedQuery(...) that the set methods, doUpdateQuery, and doQuery act on the object stored in the 'pQuery' variable. The single quotes in this example are removed in the values and predicates are removed.
The examples above use this table definition:
var createStatement = "" +
"CREATE TABLE tmp_common_types (" +
"charCol CHAR(100), " +
"varcharCol VARCHAR(100), " +
"numericCol NUMERIC(10, 2), " +
"decimalCol DECIMAL(10, 2), " +
"bitCol BIT, " +
"tinyintCol TINYINT, " +
"smallintCol SMALLINT, " +
"integerCol INT, " +
"bigintCol BIGINT, " +
"realCol REAL, " +
"floatCol FLOAT(53), " +
"binaryCol BINARY(100), " +
"varbinaryCol VARBINARY(100), " +
"dateCol DATE, " +
"timeCol TIME, " +
"timestampCol DATETIME " +
");";
See also:
How to insert binary data using a parameterized query
How to insert nulls using a parameterized query
