Top
Top
LibraryEASYProcess Reference Manual 5ProcessesServicesDBWorkshopRunQuery

RunQuery Overview

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

The benefit of using a RunQuery to an Inquire (another service that can be used to query a database), is that you can type out your query and do complex queries with unions and subqueries. Inquires build the query for you given the select clause and where clause, but it will generally follow the pattern: SELECT [select clause] FROM [table] WHERE [where clause] [order by clause]. When you want to break from this pattern, a RunQuery can be more useful.

The drawback of a RunQuery is that EASYProcess isn’t aware of the pattern you are following. EASYProcess is in charge of maintaining the query pattern in an Inquire, so it is able to do things for us, like parameterize the query. In RunQuery, the developer is responsible for the parameterization of 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. It is good practice to parameterize all filter values, even the hard coded ones. This allows future developers to change the hard coded value to a dynamic one that could come from user input without having to first change the syntax of the query to use parameterization.

Even though the RunQuery gives the developer full control over the SQL statement that will execute, the RunQuery EASYProcess service expects a response. This means the service will throw an exception if it is used for other than a select statement. The query run will still work, but the service will return an exception. If you want to write an update or delete statement instead, use the ExecuteUpdate service which does not expect a response and will allow the queries to run and not throw an exception in EASYProcess.


RunQuery Use Examples
Parameters XML
iSeries Parameterization
RunQuery Math
Workshop:DBWorkshop
Service:RunQuery
Inputs
DbType"DbType" is the database type. This input is usually loaded from configuration variables.

ConnectionString"ConnectionString" is the connection string to the database. This input is usually loaded from configuration variables.

Query"Query" is the query we would like to execute.

ResultsThis is the container node that contains all the Result nodes. It does not accept any input, but you can Right-Click to add new Results (Result#0, Result#1, etc.). Each Result below this node is built into the query as select column.

Result"Results" is a container node which accepts no input but represents a single Result that can contain a column in itself. Each Result node is indexed.

Column"Column" is the name of the column that we would like to include in the select statement.

ParametersThese are Parameters that are built by EASYProcess with the given input for each Parameter node. This is the container node that contains all the Parameter nodes. It does not accept any input, but you can Right-Click to add new Parameters (Parameter#0, Parameter#1, etc.). Each Parameter below this node can be used in our query.

ParameterThere must be a "@[Name]" existing somewhere in the "Query" which matches the assigned name of the parameter in order for EASYProcess to know where in the query this parameter must be placed. "Parameter" is a container node which accepts no input but represents a single Parameter that can contain Name, Value and DataType of the Parameter. Each Parameter node is indexed.

Name"Name" is the name we would like to assign to the parameter.

Value"Value" is the value we would like to assign to the parameter.

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

ParametersXml"ParametersXml" is where we can pass parameters in XML format instead of through the EASYProcess-built parameters. This must have the following format:

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

RunQuery Use Examples

Basic Use Example

Under the “Database Services”, expand “RunQuery”. 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 “RunQuery” Service on the Process Canvas. Grab one of the connector nodes from the “Start” service and connect it to your newly added “RunQuery”. Now double click the service to configure it.

Since we dragged the RunQuery 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 RunQuery node itself and not one of the database configurations under it, these would still be blank.

The RunQuery’s Query parameter takes the full SQL query as input.

In order to configure the service, only the DbType, ConnectionString, and Query needs to be entered. As you use the RunQuery service more, you could type out your query without using additional tools. However, if you are unfamiliar with the necessary SQL query syntax, you could right-click the “Query” node and select “Query Builder” to help create your query.

Adding Parameters to a RunQuery

In the end we want the query to resemble this:

SELECT * from <xsl:value-of select="$EP_Profile_Database_JDEData_DbName"/>.F0101 WHERE ABAN8=’4242'

  • If you used the Query Builder to build the query, you will need to add a Where clause. Add the “WHERE ABAN8=” to the end of the query that was created by the Query Builder in the “Query” parameter of the RunQuery.
  • Right click “Parameters” and select “Add New Parameters”. This will create a parameter with the nodes Name, Value and Datatype below it.
  • Type “Address” in Name. This is the name you decide to describe your parameter. The name you choose will need to be referenced in the query so EASYProcess knows where to put the value.
  • Type “4242” in Value
  • Click into Datatype and look in the right panel under “Recommended Values”. Expand it to reveal “Valid Db Data Types”. Expand that and find “String”. The String type means the value will be encased in single quotes.
  • Drag the “String” type onto the service canvas value input area.
  • Now the RunQuery is almost complete. It has most of a query and one parameter (named Addess) defined.
  • Finish the WHERE clause of the Query with “@Address”
  • The final result will be: WHERE ABAN8=@Address

The “@” character in the RunQuery Query signals EASYProcess to replace it with a parameter with a name matching the text that comes after the “@” symbol.

This query will access the database “JDEData” (using the saved database configuration data). It will run the given query and return a record from the JDE Address Book table where the Address Number is equal to 4242. The RunQuery could be configured to query a local database and other tables or joined tabled. We could also select specific values from the table instead of “*” which selects all.

Using RunQuery with SubQueries

In this example, we we be looking at a RunQuery which was chosen because the SQL query contains subqueries. This differs from the typical query structure other services like Inquire can handle. This means the developer is in charge of the query structure and as always with RunQueries, must parameterize the query themselves.

This RunQuery is named GetCartHeader. It is designed to retrieve information from the user’s shopping cart header. However, it uses a subquery to also find the subtotal of that cart by summing all the lines in the shopping cart details table. Then it wraps all of this so far in another query which sums the Tax, Shipping, and Subtotal in order to create the TotalAmount.

Notice how all inner queries reference the outermost query’s “CartId” value. Then in that outermost query, the Where clause references the specific CartId we want this to be calculated for (the user’s current cart). This is the query’s only parameter and it is named “CartId”.

Using RunQuery with a Union

RunQuery is a good tool to use when you want full control over your query. Unions are one example where the query structure could still be maintained by EASYProcess in another service, but it might make it visually easier to understand in a RunQuery.

See this query below. It was intended to retrieve the items on an order in JDE. This is a little more difficult than a regular query, because after a certain amount of time, when an order has already been processed, it is no longer in the regular order details table (F4211). The records are moved to the order detail history table (F42119). This means, in order to have one query which returns the lines on an order and have it work for archived orders as well as new orders, we must use a union.

The interesting thing about this query structure is that if we edit it a little bit, it can be maintained in EASYProcess. This is because the WhereClause applied to both sides of the union is the same. So, it could be condensed and eliminate the need for developer maintained parameterization.

RunQuery

Inquire

SELECT [SelectClause] FROM [Table1] WHERE [WhereClause]

UNION

SELECT [SelectClause] FROM [Table2] WHERE [WhereClause]

SELECT [SelectClause]

FROM

[Table1] UNION [Table2]

WHERE [WhereClause]


Using ParametersXML

RunQueries require that the developer maintain the parameterization manually. This can be done by right-clicking the “Parameters” node on the service canvas and selecting “Add New Parameter”. However, as an alternative, the RunQuery could accept the XML for the parameters instead of the individually added parameters.

This must have the following structure:

<Parameters>

        <Parameter>

                <Name></Name

                <Value></Value>

                <DataType></DataType>

</Parameter>

</Parameters>

Typing ParametersXML Straight Into RunQuery

As long as the necessary structure is used, the ParametersXML can be typed right into this node on the RunQuery. See below an example of this:

This is done if the order of the parameters really matters and the developer might find it difficult to use EASYProcess to maintain the order by right-clicking the individual parameters and selecting to move up or down. With the ParametersXML, moving the Parameters around is just copying and pasting the chunks of XML.

Another reason this is useful is if some of your parameters will not always exist. They could be present based on certain conditions. For these reasons, you might want to encase your parameters in XSLT functions like “if” or “choose”. You may also want your parameters to be created based on another service’s results. In this case, you may want to create your parameters with an XSLT “for-each” function. See below an example of using XSLT functions in the ParametersXML.

Used with QueryToRun Evaluate

This is used because sometimes queries are stored in Evaluates named “QueryToRun”.

This could be done to store the text of the query and then reference it in two Queries:

This could also be done so that two process flow branches could each create a QueryToRun variable and the actual RunQuery can then reference it and not have to know which branch it came from:

In either case, when a QueryToRun evaluate is used, it must have two evals: Query and Parameters. See here, the RunQuery is referencing an Evaluate’s “Parameters” Evaluate. Also notice the XSLT variable is a “copy-of”. This means it will not just copy the values, but the XML itself.

 This means the Evaluate had to have a “Eval” named “Parameters”. Within the “Expression”, the parameters themselves can be listed in “Parameter” tags: <Parameter></Parameter>


iSeries Parameterization

When using a RunQuery to query the JDE database, the database type can differ which will require a different syntax in queries. There will be actual syntax differences which will affect how you write your query (ISNULL vs IFNULL, TOP vs FETCH FIRST N ROWS ONLY, etc). However, the biggest change you will have to be aware of, is how parameterization is affected when DB2 database configuration (iSeries) is used.

Regularly, parameterization uses @[ParameterName] in the query to tell EASYProcess which parameter should be used in the query. With iSeries, a “?” is used in the query instead. Since all the parameters in the query will be shown with a question mark, there will be no way to distinguish between them. This makes the order the parameters are defined very important. In order, EASYProcess replaces a question mark with the next parameter in the list.

In the example below, there is a RunQuery which has a SQL query using a Union. Each side of the union has 4 parameters:

Here are the parameters that are referenced in the query. Notice that they are used in order:


Publish

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