pjs.sqlHelper

This API can be used with Profound.js in releases newer than 4.15.2. This API is currently available to use in Profound.js Spaces.

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.

pjs.sqlHelper.formatTableName(targetDB, tableName):
- IN: Target database (string). Valid values are "IBMi", "mysql", "mssql", and "oracledb".
- 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 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]
pjs.sqlHelper.formatColumnName(targetDB, columnName):
- IN: Target database (string). Valid values are "IBMi", "mysql", "mssql", and "oracledb".
- 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]
pjs.sqlHelper.getParameterToken(targetDB, value, dataType):
- IN: Target database (string). Valid values are "IBMi", "mysql", "mssql", and "oracledb".
- 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(targetDB, sqlString, rows, skip) - this is useful when doing loading data a page at a time
- IN: Target database (string). Valid values are "IBMi", "mysql", "mssql", and "oracledb".
- 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 so that the statement will only return those results.
pjs.sqlHelper.finalizeSQL(targetDB, sqlString, sqlArgs)
- IN: Target database (string). Valid values are "IBMi", "mysql", "mssql", and "oracledb".
- 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, it will also automatically call the dumpSQLStatement()
pjs.sqlHelper.dumpSQLStatement(sqlString, sqlArgs) - this 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)

function demo(request, response) {

  const targetDB = pjs.getDB().driver; // Default connection.

  // 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(pjs.sqlHelper.formatColumnName(targetDB, "customerNumber"));
  columns.push(pjs.sqlHelper.formatColumnName(targetDB, "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 = pjs.sqlHelper.formatTableName(targetDB, "customers");


  // Build the "Where Clause"
  let custIDField = pjs.sqlHelper.formatColumnName(targetDB, "customerNumber");

  let fromCustID = 100;
  let toCustID = 300;
  let fromCustIDToken = pjs.sqlHelper.getParameterToken(targetDB, fromCustID, "number");
  let toCustIDToken = pjs.sqlHelper.getParameterToken(targetDB, 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 = pjs.sqlHelper.injectLimits(targetDB, sqlStmt, 5, 10);


  // Some databases (like MSSQL) require these parameters be named instead of the just a simple ?.
  pjs.sqlHelper.finalizeSQL(targetDB, 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;