Fabric API for DB Interfaces

Connect DB Interface - Db Class

Fabric provides a Java Db class to use the methods and functions that invoke database (SQL) queries and statements and Fabric commands.

How Can I Create a Db Object?

The following Fabric UserCode methods can be used to create a Db object:

User Code Method

Description

db(String interfaceName)

Obtain the Db object to connect the given logical interface name whose input must be populated by a DB interface defined in Fabric Studio.

ludb(), fabric()

Obtain a Db object to connect the current Fabric context.

ludb(String lutype, String luid)

Obtain a  Db connection to the local Fabric and GET the specific Logical Unit Instance. If the Logical Unit is the same as the last one referenced by this method, GET is not invoked.

DB Creation - Use Cases and Examples

Use Cases

User Code Method

Examples

Connect to the local Fabric via an LU sync. Invoke the currently-synced LU Instance. 

db(String interfaceName)

ludb()

fabric()

Db conn = db(“fabric”);

Note: you do not need to define the “fabric” interface in the Fabric Studio.

Db conn = ludb();

Db conn = fabric();

Connect to the local Fabric using a Web Service.

ludb(String lutype, String luid)

Db Conn = ludb("Customer", 2);

Connect to the local Fabric via an LU sync.  Get the LU Instance from another LU.

ludb(String lutype, String luid)

Db Conn = ludb("Customer", 2);

Connect to another DB interface.

db(String interfaceName)

Db conn = db(“CRM_DB”);

Db Conn = db("dbFabricRemote");

Note:

Execute the GET instance command on the Db object to get the LU instance before running SQL statements on the Fabric Remote interface.

Execute Statements and Queries on the DB Interface

DB Class Methods - Common Use Cases

The following table describes common use cases when working with DB interfaces.\ To view the list of Fabric APIs, click http://[Fabric IP address]:3213/static/doc/user-api/index.html

Use Case

Db Method

Example

Execute Fabric Command

 

execute or fetch methods. Depends if you get an output from the executed Fabric command.

fabric().execute("get CRM.3");

ludb().execute(“"set environment='"+src_env_name+ "'");

ludb().fetch(“"DESCRIBE TABLE " + tblNameFabric);

Execute DB Statement. For example, run update statement on a table

public void execute(String sql, Object params)

String sql = "UPDATE CONTRACT SET NO_OF_OPEN_ORDERS =… WHERE CONTRACT_ID = ? ”;
ludb().execute(sql, cotractId);

Run select statement on DB interface

public Db.Rows fetch(String sql,

Object params)

 

String sql = "SELECT COUNT(*) AS TOT_NUM_OF_ORDERS FROM ORDERS WHERE CONTRACT_ID = ? and ORDER_STATUS != ?";
Db.Rows rows = ludb().fetch(sql, contractId, orderStatus);

Using Binding Variables on SQL Statements

In specific cases, a query or statement may require input parameters. For example, Select all Customers by Customer Status.\ When using prepared statement queries always use binding parameters:

  • Add a question mark in the SQL statement for each parameter in the SQL query.
  • Each input parameter must be sent as a parameter to the execute() or fetch() methods.
  • The order of the input parameters must be aligned with the order of these parameters in the SQL statement.

See the example in the table above.

How Can I Invoke the Result Set of the DB Query?

DB.ROWS and DB.ROW Classes\ The fetch() method returns the Db.Rows object which represents a result set of a query.\ You can iterate the result set and get a Db.Row object for each record, or alternatively, get the first value or the first row of the result set using firstValue() and firstRow() methods.

Notes

  • Db.Row class represents a row in the result and implements Map.
  • Use the following methods to access the array of the column values of the Db.Row object:
    • cells() - returns the array of cells
    • cell(int i) - returns the cell at position I (starts by zero), null if row is empty.
  • Use the isEmpty() method to know if the row has data.
  • To iterate column names and data simultaneously, use the values() method.
  • This map cannot be changed or edited. Trying to change this object will result in a runtime exception.

To view the list of Fabric APIs, click http://[Fabric IP address]:3213/static/doc/user-api/index.html

Loop on the Result Set Methods

The following methods can be used to loop a result set:

  1. Using forEach/each loop also closes the ResultSet and is the recommended way to iterate over a result.\ Note that the each method is similar to the forEach method and uses a Lambda interface to let exceptions pass through and also enables closing a result set when an exception breaks the loop.

Example 1

Iterating the result set and checking the column name and value for each record:

Db.Rows rows = db(..).fetch(...);
row.forEach(r->{
   // each r is a map that represents a row.
   // Iteration order is guaranteed to follow the column order.
   r.forEach((col,value)->{
      ...
   });
 });

Example 2

Iterating the result set and yielding the values of each record:

Db.Rows rows = db(..).fetch(...);
rows.each(row-> yield(row.cells()));
  1. Using for loop:
try (Db.Rows rows = db(..).fetch(...)) {
   for (Db.Row row:rows) {

   }
} 

Note that since you are not using the each/forEach method the ResultSet will not close at the end of the loop and is deferred to the end of the current execution (sync/webservice/job). The safet way to control closure of the result set, is to use the try-resource structure as seen above.

Code Examples

  1. Select the number of orders by contract_id and order_status. The Select statement returns only one record and one field. Therefore, use the firstValue() method.
String sql = "SELECT COUNT(*) AS TOT_NUM_OF_ORDERS FROM ORDERS WHERE CONTRACT_ID = ? and ORDER_STATUS != ?";
Db.Rows rows = ludb().fetch(sql, contractId, orderStatus);
Integer noOfOrders = Integer.parseInt(rows.firstValue().toString());

Note that calling firstValue also closes the result set.

  1. Select a single record from the DB. Use the firstRow() method to get the single record returned by the DB query.
String sql = "SELECT * from CONTRACT WHERE CONTRACT_ID = ? “;
Db.Row row = ludb().fetch(sql, contractId).firstRow();

Note that calling firstRow also closes the result set.

  1. Root function, select records from the CASES table and yield each record:
String sql = "SELECT ACTIVITY_ID, CASE_ID, CASE_DATE, CASE_TYPE, STATUS FROM CASES where activity_id = ?";
Db.Rows rows = db("CRM_DB").fetch(sql, input);
// Option 1 - using each method 
rows.each(row-> yield(row.cells()));

// Option 2- using for loop
for (Db.Row row:rows) {
   yield(row.cells());
}
  1. Run a Select and check the first value of each record.
Db.Rows rows = ludb().fetch(sql);

for (Db.Row row : rows) {
   if (row.cell(0) != null) { // check the value of first column of the select statement
      values.append("\"" + row.cell(0) + "\",");
   }
}

Fabric - DB Interface - Deprecated Methods

Fabric has the following set of deprecated methods for handling DB interfaces:

  • DBExecute
  • DBExecutePrepared
  • DBQuery
  • DBQueryPrepared
  • DBSelectValue
  • DBPrepapredStatement

Although they work, a warning message is displayed when deprecated methods are used in Fabric code. It is recommended to use the new Fabric methods to invoke DB interfaces instead of working with deprecated methods.

Previous

Fabric API for DB Interfaces

Connect DB Interface - Db Class

Fabric provides a Java Db class to use the methods and functions that invoke database (SQL) queries and statements and Fabric commands.

How Can I Create a Db Object?

The following Fabric UserCode methods can be used to create a Db object:

User Code Method

Description

db(String interfaceName)

Obtain the Db object to connect the given logical interface name whose input must be populated by a DB interface defined in Fabric Studio.

ludb(), fabric()

Obtain a Db object to connect the current Fabric context.

ludb(String lutype, String luid)

Obtain a  Db connection to the local Fabric and GET the specific Logical Unit Instance. If the Logical Unit is the same as the last one referenced by this method, GET is not invoked.

DB Creation - Use Cases and Examples

Use Cases

User Code Method

Examples

Connect to the local Fabric via an LU sync. Invoke the currently-synced LU Instance. 

db(String interfaceName)

ludb()

fabric()

Db conn = db(“fabric”);

Note: you do not need to define the “fabric” interface in the Fabric Studio.

Db conn = ludb();

Db conn = fabric();

Connect to the local Fabric using a Web Service.

ludb(String lutype, String luid)

Db Conn = ludb("Customer", 2);

Connect to the local Fabric via an LU sync.  Get the LU Instance from another LU.

ludb(String lutype, String luid)

Db Conn = ludb("Customer", 2);

Connect to another DB interface.

db(String interfaceName)

Db conn = db(“CRM_DB”);

Db Conn = db("dbFabricRemote");

Note:

Execute the GET instance command on the Db object to get the LU instance before running SQL statements on the Fabric Remote interface.

Execute Statements and Queries on the DB Interface

DB Class Methods - Common Use Cases

The following table describes common use cases when working with DB interfaces.\ To view the list of Fabric APIs, click http://[Fabric IP address]:3213/static/doc/user-api/index.html

Use Case

Db Method

Example

Execute Fabric Command

 

execute or fetch methods. Depends if you get an output from the executed Fabric command.

fabric().execute("get CRM.3");

ludb().execute(“"set environment='"+src_env_name+ "'");

ludb().fetch(“"DESCRIBE TABLE " + tblNameFabric);

Execute DB Statement. For example, run update statement on a table

public void execute(String sql, Object params)

String sql = "UPDATE CONTRACT SET NO_OF_OPEN_ORDERS =… WHERE CONTRACT_ID = ? ”;
ludb().execute(sql, cotractId);

Run select statement on DB interface

public Db.Rows fetch(String sql,

Object params)

 

String sql = "SELECT COUNT(*) AS TOT_NUM_OF_ORDERS FROM ORDERS WHERE CONTRACT_ID = ? and ORDER_STATUS != ?";
Db.Rows rows = ludb().fetch(sql, contractId, orderStatus);

Using Binding Variables on SQL Statements

In specific cases, a query or statement may require input parameters. For example, Select all Customers by Customer Status.\ When using prepared statement queries always use binding parameters:

  • Add a question mark in the SQL statement for each parameter in the SQL query.
  • Each input parameter must be sent as a parameter to the execute() or fetch() methods.
  • The order of the input parameters must be aligned with the order of these parameters in the SQL statement.

See the example in the table above.

How Can I Invoke the Result Set of the DB Query?

DB.ROWS and DB.ROW Classes\ The fetch() method returns the Db.Rows object which represents a result set of a query.\ You can iterate the result set and get a Db.Row object for each record, or alternatively, get the first value or the first row of the result set using firstValue() and firstRow() methods.

Notes

  • Db.Row class represents a row in the result and implements Map.
  • Use the following methods to access the array of the column values of the Db.Row object:
    • cells() - returns the array of cells
    • cell(int i) - returns the cell at position I (starts by zero), null if row is empty.
  • Use the isEmpty() method to know if the row has data.
  • To iterate column names and data simultaneously, use the values() method.
  • This map cannot be changed or edited. Trying to change this object will result in a runtime exception.

To view the list of Fabric APIs, click http://[Fabric IP address]:3213/static/doc/user-api/index.html

Loop on the Result Set Methods

The following methods can be used to loop a result set:

  1. Using forEach/each loop also closes the ResultSet and is the recommended way to iterate over a result.\ Note that the each method is similar to the forEach method and uses a Lambda interface to let exceptions pass through and also enables closing a result set when an exception breaks the loop.

Example 1

Iterating the result set and checking the column name and value for each record:

Db.Rows rows = db(..).fetch(...);
row.forEach(r->{
   // each r is a map that represents a row.
   // Iteration order is guaranteed to follow the column order.
   r.forEach((col,value)->{
      ...
   });
 });

Example 2

Iterating the result set and yielding the values of each record:

Db.Rows rows = db(..).fetch(...);
rows.each(row-> yield(row.cells()));
  1. Using for loop:
try (Db.Rows rows = db(..).fetch(...)) {
   for (Db.Row row:rows) {

   }
} 

Note that since you are not using the each/forEach method the ResultSet will not close at the end of the loop and is deferred to the end of the current execution (sync/webservice/job). The safet way to control closure of the result set, is to use the try-resource structure as seen above.

Code Examples

  1. Select the number of orders by contract_id and order_status. The Select statement returns only one record and one field. Therefore, use the firstValue() method.
String sql = "SELECT COUNT(*) AS TOT_NUM_OF_ORDERS FROM ORDERS WHERE CONTRACT_ID = ? and ORDER_STATUS != ?";
Db.Rows rows = ludb().fetch(sql, contractId, orderStatus);
Integer noOfOrders = Integer.parseInt(rows.firstValue().toString());

Note that calling firstValue also closes the result set.

  1. Select a single record from the DB. Use the firstRow() method to get the single record returned by the DB query.
String sql = "SELECT * from CONTRACT WHERE CONTRACT_ID = ? “;
Db.Row row = ludb().fetch(sql, contractId).firstRow();

Note that calling firstRow also closes the result set.

  1. Root function, select records from the CASES table and yield each record:
String sql = "SELECT ACTIVITY_ID, CASE_ID, CASE_DATE, CASE_TYPE, STATUS FROM CASES where activity_id = ?";
Db.Rows rows = db("CRM_DB").fetch(sql, input);
// Option 1 - using each method 
rows.each(row-> yield(row.cells()));

// Option 2- using for loop
for (Db.Row row:rows) {
   yield(row.cells());
}
  1. Run a Select and check the first value of each record.
Db.Rows rows = ludb().fetch(sql);

for (Db.Row row : rows) {
   if (row.cell(0) != null) { // check the value of first column of the select statement
      values.append("\"" + row.cell(0) + "\",");
   }
}

Fabric - DB Interface - Deprecated Methods

Fabric has the following set of deprecated methods for handling DB interfaces:

  • DBExecute
  • DBExecutePrepared
  • DBQuery
  • DBQueryPrepared
  • DBSelectValue
  • DBPrepapredStatement

Although they work, a warning message is displayed when deprecated methods are used in Fabric code. It is recommended to use the new Fabric methods to invoke DB interfaces instead of working with deprecated methods.

Previous