This API will return an object containing a set of functions that can help dynamically create SQL statements based on the current database connection type.
- formatTableName(tableName) - send in table name (can be library/table or schema.table) and it will return the string of a table name along with the escape characters. In MySQL table1 is returned as `table1`, but in MSSQL it is returned as [table1]
- formatColumnName(fieldName) - return the string of a table field name along with the escape characters. In MySQL field1 is returned as `field1`, but in MSSQL it is returned as [field1]
- getParameterToken(value, fieldType) - this will return the token parameter for that value. In almost every case will return a "?"
- injectLimits(sqlString, rows, skip) - insert the necessary verbiage into the sqlString so that the statement will only return those results.
- finalizeSQL(sqlString, sqlArgs) - SQL statement and the array of arguments. If debugged is enabled, this will automatically call the dumpSQLStatement()
- dumpSQLStatement(sqlString, sqlArgs) - this will send the passed in sql statement to the standard output.
| Code Block |
|---|
|
const sqlHelper = profound.utils.sqlHelper();
function demo(request, response) {
// Build the "Select Clause"
let columns = [];
// This function does check for exact case match, but if not found then finds first match regardless of case.
columns.push(sqlHelper.formatColumnName("customerNumber"));
columns.push(sqlHelper.formatColumnName("customerName"));
// Build the "From Clause"
// This function does check for exact case match, but if not found then finds first match regardless of case.
let table = sqlHelper.formatTableName("customers");
// Build the "Where Clause"
let custIDField = sqlHelper.formatColumnName("customerNumber");
let fromCustID = 100;
let toCustID = 300;
let fromCustIDToken = sqlHelper.getParameterToken(fromCustID, "number");
let toCustIDToken = sqlHelper.getParameterToken(toCustID, "number");
// Construct entire statement
let sqlStmt = `SELECT ${columns.join(",")} FROM ${table} WHERE ${custIDField} BETWEEN ${fromCustIDToken} AND ${toCustIDToken} Order by ${custIDField}`;
// Get the next 5 records, after skipping the first 10 records --> aka 3rd page of 5 records per page
sqlStmt = sqlHelper.injectLimits(sqlStmt, 5, 10);
// Some databases (like MSSQL) require these parameters be named instead of the just a simple ?.
sqlHelper.finalizeSQL(sqlStmt, [fromCustID, toCustID]);
// Run the statement
let results = pjs.query(sqlStmt, [fromCustID, toCustID]);
// Send the results back to browser
response.send(results);
}
exports.default = demo;
|