pjs.data.get()

Owned by Scot Roach
Last updated: Aug 16, 2023 by Jessica Tomkins
2 min read



This API is used to retrieve records from a database object.

Parameters

  1. Database Connection Definition:
    (Object / Optional) - A database connection definition object returned from pjs.getDB(). If not passed, the default database connection will be used.


  2. Database Object:
    (String) Database object name. The name can be qualified with a library/schema. If not qualified the pathlist will be used to resolve the object.
    --or--
    (String) DbDefn file name (with the extension). The file can be qualified with a directory. If not qualified the pathlist will be used to resolve the object.
    --or--
    (Object) DbDefn instance.


  3. Filter:
    Single condition - created using pjs.data.createCondition API
    --or--
    Array of conditions - created using pjs.data.createCondition API
    --or--
    (String) – SQL syntax, because pjs.data.createCondition does support every possible way of conditioning


  4. Limit: (Integer)
    The maximum number of records to retrieve (pass null to include all records)


  5. Skip: (Integer)
    The number of records to skip (pass null to not skip any records).  Using Limit and Skip are useful when loading a page of records at a time


  6. Order By:
    (Array of string) - An array of strings that will override the default order by, which is based on the tables primary key.
    --or--
    (String) - SQL syntax that will be used as the order by clause. 


  7. Select fields:
    (Array of Strings) - An array of all the columns to be returned.  The default is to select all columns.
    --or--
    (String) - SQL syntax that allows for database specific functions, such as substring, data casting, etc.

Exception Handling

An Error instance will be thrown along with these additional properties:

  • sqlstate - The error state.

  • sqlcode - The error code.

  • sqlMessage - The message text.



Example
// This will get all of the customers in the database let data_all = pjs.data.get("customers"); // This will get all of the customers in the database that has customerName >= B let filter = pjs.data.createCondition("customerName", ">=", "B"); let data_filtered = pjs.data.get("customers", filter); // This will get the first 10 customers in the database that has customerName >= B let data_filtered_1 = pjs.data.get("customers", filter, 10); // This will get the next page of 10 customers in the database that has customerName >= B let data_filtered_2 = pjs.data.get("customers", filter, 10, 10); // This will get all of the customers in the database and order by name and then ID. let data_orderby2 = pjs.data.get("customers", null, null, null, ["customerName", "customerID"]); // This will get all of the customers in the database, but only the customerName and ID columns. let data_smallSelect = pjs.data.get("customers", null, null, null, null, ["customerName", "customerID"]); // This will get all of the customers in the database and with a extra column called contact. let data_cust = pjs.data.get("customers", null, null, "", "*, concat(contactLastName, ", ", contactFirstName) as contact");