Page Comparison

...

• formatTableName(tableName) - send in table name (can be library/table or schema.table) and it will return the :
  • IN: Table name.  It can also include a library or schema (such as mylib/mytable or myschema.mytable)
  • OUT: The string of a table name along with
  the
  • all escape characters.
   
  • 
    This function does support exact case match, but if no match then looks for first match regardless of case.
    In MySQL table1 is returned as `table1`, but in MSSQL it is returned as [table1]
• formatColumnName(fieldName) - return the columnName):
  • IN: Column name.  It can also include the table name/as (such as customers.id)
  • OUT: The string of a table field name along with all the escape characters.
   
  • 
    This function does support exact case match, but if no match then looks for first match regardless of case.
    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 valuedataType):
  
  • IN: actual value
  • IN: column type (such as number, date, time, datetime, char, etc)
  • OUT: Return the string token. In almost every case this will return a "?"
    Reason for this → Oracle does not support date, time, or timestamp column types)
• injectLimits(sqlString, rows, skip) - insert this is useful when doing loading data a page at a time
  • IN: sql Statement
  • IN: Maximum # of rows to return, null = no max
  • IN: # of rows to skip, null = do not skip any
  • OUT: Returns a new SQL statement with 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. 
  • IN: sql Statement
  • IN: The SQL Arguments array (if any)
  • OUT: Returns a new SQL statement with a finalized statement for this type of database.
    Reason for this → MSSQL requires the tokens to be named instead of the simple "?"
    If debugged is enabled,
  this
  • it will also automatically call the dumpSQLStatement()
• dumpSQLStatement(sqlString, sqlArgs) - this will send the passed in is useful when will trying to figure out what SQL statement was generated
  • IN: sql Statement
  • IN: The SQL Arguments array (if any)
  • Send the entire resolved sql statement to the standard output
  .
  • log (or console)

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;

...