Top
Top
LibraryEASYProcess Reference ManualProcessesServicesDBWorkshopInquire

Inquire Overview

An “Inquire” is a service that can be used to query a database with a Select statement.

The benefit of using an Inquire to a RunQuery (another service that can be used to query a database), is that you can enter the desired select clause, where clause, and tables and EASYProcess will build the SQL query for you. This does not require that the developer be very familiar with the SQL query syntax.

Inquires build the query for you given the select clause and where clause, but it will generally follow the pattern:

SELECT [SelectClause] FROM [Table] WHERE [WhereClause][GroupByClause] [OrderByClause]

The drawback of an Inquire is that EASYProcess will always use this pattern. Sometimes the developer may have a particular query structure in mind that does not follow this structure. When you want to break from this pattern, a RunQuery can be more useful.

Since EASYProcess is in charge of maintaining the query pattern in an Inquire, it is able to do things for us, like parameterize the query.

A parameterized query is a query in which placeholders are used for parameters and the parameter values are supplied at execution time. The most important reason to use parameterized queries is to avoid SQL injection attacks. These can happen when a value used in the query comes from the page in user entered data.


Inquire Use Examples
Inquire Filters Based On WebPage Input
Inquire Additional Where Clause
Inquire Keys in a For-Each Loop
Workshop:DBWorkshop
Service:Inquire
Inputs
DbType

ConnectionString

FileName

Keys

Key

AndOr

Column

Operator

Type

Value

IgnoreBlanks

IgnoreCase

OrderBy

AdditionalWhereClause

Parameters

Parameter

Name

Value

DataType

ParametersXml

Results

Result

Column

Expr

AdditonalSelectClause

GroupByClause

CommandTimeout

StartIndex

EndIndex

RowCount

Publish

Inquire Basic Use Example

Under the “Database Services”, expand “Inquire”. You will see some options related to the saved database configurations. These will be populated based on the existing instances under the “Database” configuration in the configuration variables.

Drag over the JDEData option to the Process Canvas. This will create a “Inquire” Service on the Process Canvas. Grab one of the connector nodes from the “Start” service and connect it to your newly added “Inquire”. Now double click the service to configure it.

Since we dragged the Inquire from a saved Database Configuration, the DBType and ConnectionString are already filled out with variables pointing to the option we selected. Had we dragged over the Inquire node itself and not one of the database configurations under it, these would still be blank.

Add FileName

The FileName node of the Inquire is the TABLE part of the SQL query structure EASYProcess is maintaining:

SELECT [SelectClause] FROM [Table] WHERE [WhereClause] [OrderByClause]

The FileName can be generated by right-clicking this node and using the query builder tool. Here is an example of a FileName with two joined tables:

Add an Order By Clause

We're going to order the results from this query by the ALEFTB column.

  • Click the “OrderBy” property of the Inquire
  • The Recommended values on the right panel will auto-fill based on the selected tables. Expand “Recommended Values” and “Columns In Table”
  • The columns are arranged alphabetically. Scroll down to find “ALEFTB”
  • Drag this value into the Service Canvas value input area

Add Results

We are now going to add the results to the query. In the resulting SQL query generated from the Inquire service the Results create the select clause.

  • Right-click the “Results” parameter and select “Add New Results”
  • Click the in “Column” of the newly added Result. The Recommended values on the right panel will auto-fill based on the selected tables. Expand “Recommended Values” and “Columns In Table”
  • Just as before find the column you wish to add and drag it to the Service Canvas value input area. Add ABAN8 (Address Number).
  • Complete the same steps to add an additional result for each of the following fields:
  • ABALPH (Name)
  • ABMCU (Branch Plant)
  • ALADD1 (Address Line 1)
  • ALEFTB (Effective Date)

Add a Query Filter (Key)

In the Inquire service, the Where clause of the SQL query is determined by the “Key” parameter. Right-click the “Key” parameter and select “Add New Keys”. See below the Key added for the example Inquire.

An Inquire service always needs a Where Clause. This is EASYProcess’s protection against selecting too wide of a range of records and not being able to return the large quantity.

Add Query Filters Based On WebPage Input

If you are using the Inquire in a process datasource, it could be used to populate a list section. One of the most common features for a list section is the ability to filter with a search box. If this is the case for our test Inquire, it could be setup to pull Key values from the page.

The IgnoreBlanks and IgnoreCase nodes are very useful here. To use, once you click into the “IgnoreBlanks” or “IgnoreCase” parameter of the key, the “Recommended Values” will populate a list called “Valid Values”.

 

When the page loads for the first time, the NameFilter and AddressNumberFilter will be blank. Our “IgnoreBlanks” property will see that our reference to those fields is blank and as a result, remove them from the Where clause.

 

We also want to set the NameFilter key with an IgnoreCase=’True’ because we don’t want to limit the users to typing the matching case as it is stored in the database. If the user types “company” and it is stored as “Company”, it should still come up in the results.


Inquire Filters Based On WebPage Input

If you are using the Inquire in a process datasource, it could be used to populate a list section. One of the most common features for a list section is the ability to filter with a search box. If this is the case for our test Inquire, it could be setup to pull Key values from the page.

The IgnoreBlanks and IgnoreCase nodes are very useful here. To use, once you click into the “IgnoreBlanks” or “IgnoreCase” parameter of the key, the “Recommended Values” will populate a list called “Valid Values”.

 

When the page loads for the first time, the NameFilter and AddressNumberFilter will be blank. Our “IgnoreBlanks” property will see that our reference to those fields is blank and as a result, remove them from the Where clause.

 

We also want to set the NameFilter key with an IgnoreCase=’True’ because we don’t want to limit the users to typing the matching case as it is stored in the database. If the user types “company” and it is stored as “Company”, it should still come up in the results.


Inquire Additional Where Clause

Sometimes it can be difficult to accomplish the desired where clause with an Inquire through the separation of ANDs and ORs. Using Inquire Keys allow EASYProcess to control the parameterization, but if the query where clause is too complicated to use keys and be readable or achieve the desired result, the Additional Where Clause can be used.

In this example, we are working with a feature called “My Lists”. This is saved in the database as a table where each row represents a distinct ItemNumber and UOM (Unit of Measure). Each row also has a ListId associated so that rows from the same list would have the same ListId value. Below is an example of the data that exists in this database table.

When adding new items to this list, we want to check if the ItemNumber/UOM combination already exists in the list. If it does, we can sum the quantities instead of creating a new line.

In this process, an XML node (WorkData/Items/Item) contains all the items the user is trying to add to the list. We would like to query the existing list line records with the following where clause (assuming we are attempting to add three items to the list):

SELECT LineItemId FROM EC_List_Items

WHERE ListId=[ListId] AND (

(ItemNumber=[Item1] AND UOM=[UOM1]) OR

(ItemNumber=[Item2] AND UOM=[UOM2]) OR

(ItemNumber=[Item3] AND UOM=[UOM3])

)

We can add the ListId filter in the regular EASYProcess Keys and the rest can go in the AdditionalWhereClause. The entire contents of the AdditonalWhereClause are put at the end of the Inquire Keys, separated with an AND:

SELECT * FROM TABLE WHERE [InquireKeys] AND [AdditionalWhereClause]

The AdditionalWhereClause here will have to follow this format:

However, since the WorkData/Items/Item that we are looping on comes from user entered data, it must be parameterized to prevent from SQL injection. Here is what the AdditionalWhereClause will look like after parameterizing:

You will notice that we added the “@ParameterName” so EASYProcess could map the parameter value to the location it should be used in the query. We also added an XSLT value-of using the XSLT function “position()”. This allows us to create numbered parameters so they can be mapped back to the correct location in the query. Our end query ends up looking like this:

Before we are done, we must make sure the number of parameters created is the same as the amount of references. To be sure of this, we create the parameters in that same for each loop.

Make sure to name the parameters the same as you referenced them by using the XSLT position function.

Here is both the query and the parameters as they appear in the EASYProcess logs after the query is ran.

Here is a full view of the Inquire service using the Keys, AdditionalWhereClause, and Parameters:


Inquire Keys in a For-Each Loop

Sometimes you will need to filter dynamically on another service’s results. This can be done by putting your Inquire Keys in an XSLT For-Each loop. This means you will have to take care to put the correct AndOr values for each key. This can get confusing to build, but by using Inquire Keys you allow EASYProcess to control the query and there is no need for manual parameterization.

In this example, we are working with a feature called “My Lists”. This is saved in the database as a table where each row represents a distinct ItemNumber and UOM (Unit of Measure). Each row also has a ListId associated so that rows from the same list would have the same ListId value. Below is an example of the data that exists in this database table.

When adding new items to this list, we want to check if the ItemNumber/UOM combination already exists in the list. If it does, we can sum the quantities instead of creating a new line.

In this process, an XML node (WorkData/Items/Item) contains all the items the user is trying to add to the list. We would like to query the existing list line records with the following where clause (assuming we are attempting to add three items to the list):

SELECT LineItemId FROM EC_List_Items

WHERE ListId=[ListId] AND (

(ItemNumber=[Item1] AND UOM=[UOM1]) OR

(ItemNumber=[Item2] AND UOM=[UOM2]) OR

(ItemNumber=[Item3] AND UOM=[UOM3])

)

In order to accomplish this in an EASYProcess Inquire, you could put all of the Keys in a for-each loop and build the following Where Clause which accomplishes the same thing:

SELECT LineItemId FROM EC_List_Items

WHERE (

(ListId=[ListId] AND ItemNumber=[Item1] AND UOM=[UOM1])

OR (ListId=[ListId] AND ItemNumber=[Item2] AND UOM=[UOM2])

OR (ListId=[ListId] AND ItemNumber=[Item3] AND UOM=[UOM3])

)

Here is how that logic would be built in the EASYProcess Inquire. Notice the XSLT For-Each is around all the keys. Also notice that since the results we are looping on do not have a ListId property, we need to reach outside of the loop to the Prevalues property to retrieve the ListId. Since we are existing the loop, a “/” must be placed before the “WorkData” to use an absolute path instead of a relative one.

Here is the query from the EASYProcess logs once it is ran:


Powered by EASYProcess (© 2019 K-Rise Systems, Inc).