API Overview
The pjs.query() API is a shortcut for executing an SQL statement and automatically fetching the results. The results are returned by default as a primitive JavaScript array or object. However, if the 4th parameter is specified, the results are instead returned into a set of strongly defined fields, a strongly defined array, or a strongly defined data structure.
This API first prepares the SQL statement, binds parameters (if any), executes the statement, and then fetches the records.
This API also has the capability to INSERT
or UPDATE
records using a primitive JavaScript object that represent fields and their corresponding values.
INSERT
or an UPDATE
, parameter 3 and parameter 4 are not applicable.Contents
Important!
When using an IBM i database, this API requires the Profound.js Connector module.
Parameters
This API can accept an optional database connection definition object to select between multiple database connections. To select the database connection, call pjs.getDB() and pass the result as the first parameter, followed by the parameters documented below. If a database connection definition object is not passed, the default database connection is used.
- SQL statement string
The SQL statement must be valid for the database you are using - parameter or array of parameters to bind (optional)
- number of records to fetch (optional)
This parameter only works with the IBM i DB2 database driver.
If omitted,SQL_FETCH_ALL
is assumed. - 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.
Return Value
- If parameter 4 is specified, the API does not return a value.
- If 1 record is requested, the API returns a JavaScript object that represents the fetched record or null if no record was found.
- 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
Load a Grid
pjs.defineDisplay("display", "mydisplay.json"); var records = pjs.query("SELECT * FROM ORDERS"); display.grid1.replaceRecords(records);
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"}; var recordArray = [record1, record2] ; pjs.query("INSERT INTO table SET ?", recordArray);
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;
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);