profound.utils.sqlHelper()

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.

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;