Page Comparison

Section

Column

width	60%

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.

Note
If the SQL statement is an `INSERT` or an `UPDATE`, parameter 3 and parameter 4 are not applicable.

Column

width	40%

Parameters

SQL statement string
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.

Column

width	25%

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

Code Block

language	javascript
title	Load a grid where screen fields match database fields

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

Retrieve Records by Criteria

Code Block

language	javascript
title	Retrieve records that match search critertia

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

Code Block

language	javascript
title	Retrieve product description through a primitive JavaScript object

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

Code Block

language	javascript
title	Add a record using a primitive JavaScript object

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

Update a Record

Code Block

language	javascript
title	Update a record using a primitive JavaScript object

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

Code Block

language	javascript
title	Retrieve product description into a strongly typed field

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

Code Block

language	javascript
title	Retrieve multiple strongly typed fields

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

Versions Compared

Old Version 11

New Version 12

Key

API Overview

Contents

Parameters

Return Value

Examples

Load a Grid

Retrieve Records by Criteria

Examples with Primitive JavaScript Objects

Retrieve a Description

Add a Record

Update a Record

Examples with Strongly Typed Fields

Retrieve a Description

Retrieve Multiple Fields