Profound Pro Tips is a blog series focused on issues that our support team receives and questions our Profound Logic customers have. Don't see your question/concern in Profound Pro Tips? Please feel free to contact our team or leave us a comment below.
Today’s Topic: Simplifying Your Database Access with pjs.query()
Keep reading to learn more....
When Profound.js was first created one of the goals was to enable a direct, nearly one to one, conversion between RPG and Node. To provide this functionality, Profound.js was written with several functions that mimic many of the RPG opcodes. In the area of database access, this includes record level access functions as well as SQL function which correlate to the embedded SQL opcodes in RPG. For example, you can prepare a statement with pjs.prepare(), set parameters for placeholders with pjs.bindParameteres(), execute the statement, pjs.execute(), fetch the results with pjs.fetch() and finally close the cursor using pjs.close(). While this process is very familiar to anyone building a prepared SQL statement in RPG, it is a little cumbersome and requires a lot of lines of code, especially if you’re checking your sqlcode after each statement like you should be.
You can now use pjs.query() to do all of that and more. Pjs.query() is a shortcut API that will prepare, bind, execute, fetch, and close all in a single statement. Pjs.query() can also be used to update or insert data and can be used with primitive JavaScript objects or strongly defined fields. In addition to simplifying the process of database access, pjs.query() can also be used with several other database drivers.
Prerequisites
The pjs.query() API requires the Profound.js Connector module.
Prior to calling pjs.query() the program needs a connection to a database. If you do not explicitly create a connection using pjs.connect() a connection will automatically made to the default database if possible.
SQL CLI Attributes
It is possible to set any of the SQL connection attributes. This is accomplished using pjs.setConnectAttr(). A list of the possible DB2 SQL CLI attributes can be found in the IBM documentation .
One possible use of this API is to disable commitment control:
%20-%201.jpg)
Parameters
The API accepts up to 4 parameters: statement, parameters, number of records to fetch, and strongly typed field names. Only the statement is required. The last two parameters are only valid for connections to IBM DB2.
SQL statement - The first parameter, statement, is the SQL statement to run. This statement can be any statement that is valid for the database you are using. In addition, there are a couple special cases that will be described below.
Parameters - This can be a single parameter or an array of parameters in a JavaScript object.
Number of Records - This parameter is only valid for IBM DB2 and specifies the number of records to fetch and defaults to fetching all records if not used.
Field Names - Also only valid for DB2, this parameter allows you to specify one or more strongly defined field names for the data to be fetched into.
Fetching Data
The simplest use of pjs.query() is to retrieve records into a JavaScript object.
%20-%202.jpg)
This simple command will result in an array something like:
[{ id: 1, name: “Allen” }, {id:2, name: “Betty”}, {id:3, name: “Carl”}]
You can then add criteria to your query using parameters.
%20-%203.jpg)
Multiple parameters can be specified as an array.
%20-%204.jpg)
Fetching Data into a Grid
Returning data from a query into a JavaScript object array is very convenient and does not require you to strongly define the fields ahead of time. The array can then easily be used to access the data. Some of the other pjs API are designed specifically to accept JavaScript object arrays. One API in particular, the replaceRecords() API, when used with the pjs.query() API makes it very easy to reload a Grid widget with data from an SQL query. Because pjs.query() returns a JavaScript array and replaceRecords() accepts a JavaScript array as a parameter you can simply pass the result from the query directly into replaceRecords() to reload the Grid.
%20-%205.jpg)
In fact you can actually pass pjs.query() directly into replaceRecords() if you really want to save space.
%20-%206.jpg)
Using Strongly Defined Fields
The last two parameters, number of records and strongly defined field names, are unique to IBM i DB2.
The number of records defines how many records you want to fetch. One important note about specifying the number of records to return is that if you specify 1 record be returned the result will be a single JavaScript object instead of an array. When you use only the first or first two parameters the result is always an array of object even if that array only contains 1 object. This is an important distinction because with a single object you will refer to the data with data.id or data.name. With an array with only one object in it you would use data[0].id and data[0].name.
The fourth parameter lets you specify strongly defined fields to return the data into. This is useful when you need to fetch the data into a defined data structure or anytime you need the data for APIs that require strongly typed fields.
%20-%207.jpg)
Updating Data
Updating a record or records is also very concise using pjs.query(). The data to be updated needs to be supplied in a JavaScript object as the first element in the parameters array followed by any additional parameters. The statements below create a JavaScript object containing name and color called data. The data object is then passed in as the first object in the parameters array and the name and color fields for the record with id of 3 are updated with the values of newName and newColor.
%20-%208-1.jpg)
%20-%209.jpg)
The data object can also be specified directly in the pjs.query() call instead of defining it ahead of time.
%20-%2010.jpg)
Inserting Records
Similar to updating, inserting records uses a special statement: INSERT INTO tablename SET ? followed by a JavaScript object containing the field names and values to insert. Just like with the update the object can be constructed ahead of time and passed in or created on the fly inside of pjs.query().
%20-%2011.jpg)
Or...
%20-%2012.jpg)
As these examples show, pjs.query() is a very convenient tool for simplifying data access in Profound.js. By utilizing pjs.query() you can cut several lines of code and spend more time focusing on the business logic.
Learn how to Deliver Modern IBM i Business Applications with Profound UI Version 6! Watch the replay of this webinar hosted by Scott Klement Today!
