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
Workshop:DBWorkshop
Service:Inquire
Inputs
DbType

ConnectionString

FileName

KeysThe Keys create the WHERE Clause of the query. The keys are concatenated to form the following pattern: [AndOr1][Key1][AndOr2][Key2]... The AndOr values determine the order in which the keys are run. ANDs are always evaluated before ORs. This is similar to the way a BinaryDecision service works. This is the container node that contains all the Key nodes. It does not accept any input, but you can Right-Click to add new Keys (Key#0, Key#1, etc.). Each Key below this node is built into the query as a Where Clause.

Key

AndOr"AndOr" is used when we are dealing with more than one key. Valid values are "And" and "Or". If this value is left blank it defaults to "And". The AndOr values determine the order in which the keys are run. ANDs are always evaluated before ORs.

ColumnThe column to be referenced from the table in the query.

Operator

Type

Valuethe value which we will compare against the "Column" of the this Key.

IgnoreBlanks

IgnoreCase

OrderByThis is the OrderByClause. EASYProcess will add in the words "Order by" before your text, so only the column(s) are required here.

AdditionalWhereClause

Parameters

Parameter

Name

Value

DataTypeThis is the data type of the "Value". This determines whether the "Value" is encased in single quotes.

ParametersXml

Results

Result

Column

ExprThe "Expression" is used when using SQL functions such as ROUND, CONCAT, TRIM, and etc. Here are some examples of Expressions: ROUND([Column]) dbo.[Column] 1+1 CASE WHEN [Column]=1 THEN 'True' ELSE 'False' END

AdditonalSelectClause

GroupByClause

CommandTimeout"CommandTimeout" is the timeout for query in milliseconds. After this time the service cancels execution of the query and throws an error.

StartIndex

EndIndex

RowCount

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.


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