Top
Top
LibraryEASYProcess Reference ManualProcessesServicesDBWorkshopUpsert

Upsert Overview

An “Upsert” is a service that can be used to run a SQL update or insert statement. This means it can either run an Update or an Insert SQL statement depending on whether the Key (Where Clause) used found any records. If no records were found to update, an insert is done instead.

The benefit of using an Upsert to an ExecuteUpdate (another service that can be used to run an update and insert statement), is that you can enter the desired table, where clause and values, and EASYProcess will build the SQL statement for you. It will decide whether an update or an insert is required. This also does not require that the developer be very familiar with the SQL syntax.

Upsert services build the query for you given the table, where clause, and values. It follows one of the following patterns:

UPDATE [Table] SET [Column1]=[Value1], [Column2]=[Value2], …

WHERE [WhereClause]

INSERT INTO [Table] ([Column1], [Column2], ...)

WHERE ([Value1], [Value2], ...)

The drawback of the Upsert service is that EASYProcess will always use one of these patterns and requires that the WhereClause be predefined in the Keys. Sometimes a developer would like to build the update/inquire statement based on XSLT functions. A developer also might just feel more comfortable writing the statement themselves which gives more control. In these examples an ExecuteUpdate service might be a better fit.

Since EASYProcess is in charge of maintaining the query pattern in an Upsert service, 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.


Upsert Use Examples
Workshop:DBWorkshop
Service:Upsert
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.

FileName"FileName" is the name of the database table on which we would like to run the Upsert.

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.

KeyOnce EASYProcess builds each Key it will have the following format: [Column] [Operator] [Value]. Whether or not the Value is encased in single quotes is determined by the selected Type. "Key" is a container node which accepts no input but represents a single Key that can contain information related to a key. Each Key node is indexed.

And"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.

OperatorThe operator to be used in the Key.

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

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

ValuesThe "Values" are all the updates/inserts against the table that will occur. This is the container node that contains all the "Value" nodes. It does not accept any input, but you can Right-Click to add new Values (Key#0, Key#1, etc.). Each Value below this node is built into the update statement.

ValueA "Value" specifies both the column name and the value to be updated/inserted in that column in the upsert. "Value" is a container node which accepts no input but represents a single Value that can contain information related to a Value. Each Value node is indexed.

ColumnThe column in the table the value will be updated.

TypeThis is the data type of the "Val". This determines whether the "Val" is encased in single quotes.

ValThis is the actual data which is updated/inserted in the specified column.

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

Upsert Use Examples

Upsert in a ForEachLoop

In this example, records are being either updated or inserted. The Key is used to find the existing records in the InvoiceReporting_TempHolder table. If the requested record(s) do not exist, it is inserted into the table.

This upsert is within an EASYProcess for each loop, so the first time it runs, it is inserting a record. Each time after that that created record is updated with the new values.

Converting Update and Insert in a Process to Upsert

In this example, we can see that there is a binary decision to check whether an Update or an Insert is needed.

This is a button process from a webpart that is used to both add and edit users for a site. A query string with either the “Add” or “Edit” value is passed to this webpart, so the “AddorEdit” evaluate is reading that query string to determine the user’s action.

If the user is being added, we check if the chosen userid is already taken. If so, a message is displayed to the user that the userid is not available. If the userid is available, the Insert places the new user in the EP_Users table.

If the user was being edited, an update selects the existing record and updates the columns in the EP_User table with the user entered values.

Here we can see that same process was converted to use just one Upsert service in place of the previous Update and Insert. If the user is going to be added, it still checks if the userid is available, but if it is a valid userid, it directs right back the Upsert.

Here is the service configuration for that Upsert. The Key (WhereClause) is used to filter down to the userid. This is used as the Where Clause in the Update query. If this finds no results, an Insert statement is run to create the record.


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