Open topic with navigation
Queries are used extensively in Workflows to search for data and data sets. A query is a precise request to retrieve information from a table in a database or to access information from an information system. In workflows, queries are usually performed on a layer or table resource, and target either ArcGIS or Geocortex. The result of a query is usually data in the form of a feature set. Each feature in a feature set may have a field value and/or geometry data associated with it. For tables, the feature set does not include geometries.
Queries are defined by a SQL-like WHERE clause. SQL (Structured Query Language) is the standard universal language used by most relational database management systems (RDBMS), including ORACLE, Microsoft SQL server, Informix, etc. Geodatabases usually consist of multi-tiered architecture that layers an RDBMS with a Geodatabase like ArcSDE that acts as a gateway and manages geographic data. To learn about the SQL WHERE clause, read SQL WHERE Clause at W3Schools.com.
SQL has a number of wildcard characters:
% percent is a substitute for zero or more characters.
_ underscore is a substitute for exactly one character.
[charlist] Any single character in a charlist.
[^charlist] or [!charlist] any character not in a charlist.
For more information, see SQL Wildcards at W3Schools.com.
The syntax in the examples below assumes the query is being run against a layer that has the following fields:
"OBJECTID>1000 AND Name LIKE 'Victoria' AND Height>5.5"
You can incorporate variables of type string and Int32 into any string-typed property in any activity, for example:
"the value of my variable is " & variable1.
To add a new line, use the Visual Basic command:
"Line 1" & Environment.NewLine & "Line 2"
To get the attribute value of a feature, use:
To add list items to a Combo Box using a query:
This article illustrates how to create a parameterized
IN condition with Microsoft SQL Server. However, it could be recreated with any SQL variant that allows user-defined functions. See also Create a Parameterized IN Condition with Oracle for an Oracle-specific function.
When you are doing work with very large dataset, you may want to allow users to retrieve large subsets of the data. For example, if there are 10,000 tax parcel IDs in a database, users might want to choose only 700 of them to export to an Excel spreadsheet.
With the SQL Query Activity, you could use an
IN condition that targets a list of chosen parcel IDs. However, in order to make this
IN condition safe in a production environment, we must work within the following constraints:
We must parameterize the
IN condition so that malicious users cannot inject harmful SQL statements into the workflow.
Because the parameter must be entered initially as a string, the string must be a list of values that is separated by commas.
We must create a user-defined function that converts the comma-separated values from a string into a table that SQL can read.
In the case that your viewer is only available to users you trust, you can use the SQL Query Unsafe Activity to bypass the need for a parameterized IN condition all together. Using the SQL Query Unsafe Activity to perform this function is a security vulnerability and we do not recommend that you use this method.
In order to accept a list as a parameter, you need to configure a function in your SQL database. To learn more about adding functions to SQL Server, see Microsoft's CREATE FUNCTION (Transact-SQL) documentation.
F_Split function allows you to use one parameter in your
CREATE FUNCTION dbo.F_Split ( @InputString VARCHAR(MAX) ,@Separator VARCHAR(MAX) ) RETURNS @ValueTable TABLE (Value VARCHAR(MAX)) AS BEGIN DECLARE @SeparatorIndex INT, @TotalLength INT, @StartIndex INT, @Value VARCHAR(MAX) SET @TotalLength=LEN(@InputString) SET @StartIndex = 1 IF @Separator IS NULL RETURN WHILE @StartIndex <= @TotalLength BEGIN SET @SeparatorIndex = CHARINDEX(@Separator, @InputString, @StartIndex) IF @SeparatorIndex > 0 BEGIN SET @Value = SUBSTRING(@InputString, @StartIndex, @SeparatorIndex-@StartIndex) SET @StartIndex = @SeparatorIndex + 1 END ELSE BEGIN Set @Value = SUBSTRING(@InputString, @StartIndex, @TotalLength-@StartIndex+1) SET @StartIndex = @TotalLength+1 END INSERT INTO @ValueTable (Value) VALUES (@Value) END RETURN END
Once this function is configured on your SQL server, you can create server activities that invoke the
F_Split function in your
To allow users to input a list of records they want to retrieve, we will need to take their input string and convert it into data that the SQL server can read by using the
F_Split function. In the case of parcel IDs, which are integers, we use the CAST function to to convert the result to integers as well.
WHERE ParcelID IN (SELECT CAST(Value AS INT) FROM F_Split(@groupIDs, ','))
As a result, the list of parcel IDs that a user would input (
"1,5,9,15") can be processed as individual IDs and understood by the SQL server as individual parcel IDs from the database (
You could use the
F_Split function for other layers, too:
WHERE Parks IN (F_Split(@groupColors, ','))
In the same way, this takes the received string of values
"Blue, Yellow, Grey" and converts it to the values to the format that the SQL server understands and can match in the database. In this case,
With Oracle, our approach to this user-defined function is different. However, this result is the same. This user-defined function breaks a string with comma-separated values into a format that the Oracle database can read.
'SELECT RouteEventID FROM ops.pods_subsystemrange WHERE subsystemEventID IN ( SELECT regexp_substr(:grpIDs,'[^,]+', 1, level) from dual connect by regexp_substr(:grpIDs, '[^,]+', 1, level) is not null )'
In this case,
:grpIDs would be the parameter's name. Its value should be a string with comma-separated values, like