Page Comparison

Parameters

Parameters

...

1. Database Connection (optional/omittable)
   Pass an object returned by pjs.getDB() to use an alternate database connection. If not passed, the default connection will be used.
2. Query Options (optional/omittable)
   Pass an object containing statement attributes to set before executing the query.
3. SQL statement string
   The SQL statement must be valid for the database you are using
4. parameter or array of parameters to bind (optional)
5. number of records to fetch (optional)
   This parameter only works with the IBM i DB2 database driver.
   If omitted, SQL_FETCH_ALL is assumed.
6. one or more Profound.js strongly typed field names (optional)
   This parameter only works with the IBM i DB2 database driver.
   If field names are passed, they will be bound to columns in the resulting rows and populated by this API if the fetch is successful.

...

1. number of rows per INSERT SQL request on a multi-insert action (optional; if not specified, the default is 100; available in Profound.s version 5.2.0). This parameter is applicable only for an INSERT statement.

Return Value

1. If

...

1. parameter 4 is specified, the API does not return a value.
2. If 1 record is requested, the API returns a JavaScript object that represents the fetched record or null if no record was found.
3. If more than 1 record is requested, the API returns an array of JavaScript objects, where each Object represents a record. If no records were fetched, an empty array is returned.

Examples

Example 1

Examples

Load a Grid

pjs.defineDisplay("display", "mydisplay.json");
var records = pjs.query("SELECT * FROM ORDERS");
display.grid1.replaceRecords(records);

Example 2

Retrieve Records by Criteria

search = '%' + search.trim() + '%';  // adjust for LIKE search
var records = pjs.query("SELECT * FROM EMPLOYEES WHERE firstname LIKE ? OR LASTNAME LIKE ?", [search.trim(), search.trim()]);

Examples with Primitive JavaScript Objects

Retrieve a Description

var productId = 123;
var record = pjs.query("SELECT prdesc* FROM PRODUCTS WHERE prid = ?", productId, 1);

if (record != null) {
  var description = record.prdesc;
}

Add a Record

var record = {
  id: counter,
  firstname: fname,
  lastname: lname
}
pjs.query("INSERT INTO table SET ?", record);

Add multiple records with one INSERT statement (available in Profound.js version 5.0.0)

var record1     = {flda: "AAA", fldb: "BBB"};
var record2     = {flda: "XXX", fldb: "YYY"}

Example 3

;
var recordArray = [record1, record2]        ;

// 5th parameter is not specified, so default of 100 is used; there will be 100 rows per INSERT SQL request
pjs.query("INSERT INTO table SET ?", recordArray);		

// 5th parameter is specified as 50, so there will be 50 rows per INSERT SQL request
pjs.query("INSERT INTO table SET ?", recordArray, null, null, 50);		
	

Add a Record

var version = "12345";

var record = {};
record.OneField = version;                         // variable
record.TwoField = data[1].__SOMEFIELD;             // object property
record.RedField = "Suspended";                     // literal
record.BlueField = cvtDate (data[1].DUEDATE);     // function call

pjs.query("INSERT INTO table SET ?", record)

Update a Record

var fieldsToUpdate = {};
fieldsToUpdate.firstname = fname;
fieldsToUpdate.lastname = lname;
pjs.query("UPDATE table SET ? WHERE id = ?", [ fieldsToUpdate, currentId ]);

Examples with Strongly Typed Fields

Retrieve a Description

var productId = 123;
pjs.define("prdesc", { type: "char", length: 60 });
pjs.query("SELECT prdesc FROM PRODUCTS WHERE prid = ?", productId, 1, prdesc);
var description = prdesc;

Example 4

Retrieve Multiple Fields

var productId = 123;
pjs.define("prdesc", { type: "char", length: 60 });
pjs.define("prprice", { type: "packed", length: 7, decimals: 2 });
pjs.query("SELECT *prdesc, prprice FROM PRODUCTS WHERE prid = ?", productId, 1, prdesc, prrice);

Example 5

search = '%' + search.trim() + '%';  // adjust for LIKE search
var records = pjs.query("SELECT * FROM EMPLOYEES WHERE firstname LIKE ? OR LASTNAME LIKE ?", [search.trim(), search.trim()]);

Example 6

var record = {
  id: counter,
  firstname: fname,
  lastname: lname
}
pjs.query("INSERT INTO table SET ?", record);

 The following example(s) are available with Profound.js version 3.0.3 and later.

Example 7

...

...

Case of column names in result set for IBMi database

For IBMi database, prior to Profound.js release 5.7.0, the names of the columns in the result set returned by pjs.query() and other Profound.js APIs are always in lowercase. Since Profound.js release 5.7.0, you can use either configuration keepColNameCase (in effect for the entire instance ) or API pjs.setOptions() (in effect for the current Profound.js session) to instruct pjs.query() and other SQL APIs to keep the case of the columns names as is, as resulted from the SQL statement.  For backward compatibility and to avoid breaking current user code, the default behavior is the "old" behavior; that is, the column names are always in lowercase.

Default behavior: column names are in lowercase

// Example web service
function app(req, res) {
  let result = pjs.query(`select  name as "custName", custno as "custNumber" 
                            from  custadrp 
                            where custno = 1234`);
  res.json(result[0]);
}
exports.run = app;
// data returned:
{
  "custname": "My Company",
  "custnumber": 1234
}

To keep case of column names by using pjs.setOptions()

// Example web service
function app(req, res) {
  pjs.setOptions({keepColNameCase: true});			// to keep case of column names as is; in effect for this session
  let result = pjs.query(`select  name as "custName", custno as "custNumber" 
                            from  custadrp 
                            where custno = 1234`);
  res.json(result[0]);
}
exports.run = app;
// data returned: 
{
  "custName": "My Company",
  "custNumber": 1234
}

To keep case of column names, for entire instance

// Set configuration setting "keepColNameCase" in config.js to "true", to enable that behavior for entire instance
keepColNameCase: true