How to access a SQL database from a workflow using Geocortex Workflow 5 [Geocortex Tech Tip] - Geocortex

How to access a SQL database from a workflow using Geocortex Workflow 5 [Geocortex Tech Tip]

Ryan Cooney
Ryan Cooney
Product Owner
November 9, 2018

There are a lot of benefits when it comes to server workflows, a new type of workflow available for users of the newly released, on-premises version of Geocortex Workflow. Server workflows can read and write files that exist on the server, send emails (as Noah demonstrated last week), run Python scripts, send HTTP requests to secured web services that the end user can’t reach from their browser, and carry out some other heavy-duty types of processing requests.

In this week’s Geocortex Tech Tip, we wanted to show you yet another feature exclusive to server workflows that we’re really excited about: Accessing a SQL database from a workflow. By calling a server workflow to do a SQL database query, the server workflow returns a data structure to the client, enabling it for use with a workflow.

Check out the video below to learn how this process works!

Watch on YouTube

Video Transcript

“Hi, I’m Ryan. I’m on the Geocortex Workflow development team. Today I’d like to show you how to query a SQL database in Geocortex Workflow 5. Let’s take a look!

I have a workflow here and it’s really simple, it has to display form activity and it has a drop-down list asking to pick or select a customer from a list. Now this list (at the moment) is just a hard-coded list of options, but I would like this list to be populated from a database. It’s something that we can normally do through an ArcGIS query, but that’s using the ArcGIS REST API to perform that query. If we want to go direct to the database, that’s something that hasn’t been possible until today inside Workflow and we’re going to use a new features called Server Workflows that are available in Workflow 5.8 and the on-premises version of Workflow.

What this is going to allow us to do is create a whole new type of workflow that is going to run on the server and it’s going to do our work to actually query the database for us. It will have access to activities that can query the database and then it will return us some results so that my client workflow, the workflow the end user is interacting with, can basically call the server workflow as if it were a web service. So, what we’re going to do is create a brand-new workflow and this is going to be my server workflow and I’m actually going to tell it that it’s a server workflow by switching to the info panel and selecting ‘Server Workflow’. This is going to change the toolbox to have a different set of activities that are available and some of the really powerful ones here are the SQL query activities.

So, the SQL query activity – a connection string – is going to allow me to define my query. I’ve set one up in advance here so I’m going to just populate the providers, this is going to be a SQL server database. I’m going to plug in a connection string, this is just some database sitting on the internet that we can use. I’ll quickly show you this table. It’s just a Microsoft Northwind database. There’s a customer table and it’s got a contact name field, and this field is what I want to populate my results with.

I’m going to run this query, and this is going to provide a data table output and what I want is this server workflow to provide that data table as the output to the workflow itself. There’s an activity called ‘Set Workflow Output’ and I can provide whatever name I like for this output, so I’m going to call it ‘contacts’ and the value is going to be sqlQuery1.dataTable, so just the result of that data table. When I save this, I’m going to call this ‘Customer Server Workflow’.

Okay, so my server-side part is done – it’s going to do a query and is going to return that data table as adjacent data structure to this client workflow and the client workflow – rather than using this manual list of options in that drop down – I’m going to change to use a sub workflow and I’m going to just start with a blank one.

Now in this, I want to run that server workflow, so there’s a run workflow activity, and I need to give it a URL. If I go back to my server workflow on the info panel, I can copy the URL, paste that in. That workflow didn’t have any inputs or arguments, so I don’t need to supply any, but it is going to provide a result and then what I want to do is I want to take that result – which will have a data table on it – and convert that data table into something that the form is going to be able to use. There’s an activity called ‘Get Form Element Items From Collection’ which will do that. So, the collection in this case is going to be my runWorkflow1 result, and contacts was the name of the property that I had assigned on that so we’re going to take that contacts collection, and I’m going to provide the name and value of that dropdown list (I’m going use the same field).

The last thing we need to do here is set form element items, so we have to actually tell the dropdown list to use these items. If I save that I should be able to run that.

I’ll just run this in the sandbox and there we go! I’ve got my list that’s coming from the database and if I pick a value and submit it, the reminder of my workflow is just to alert that value back, but we can now go on and do something really useful with that.

So, there you have it, we were able to create a client workflow running in the web browser. It calls a server workflow when it needs to do something server specific like do a SQL database query or send an email or some kind of server processing. That server workflow returns a data structure to the client and then the client is able to use that and carry on with that workflow.

So that’s how you do a query SQL database inside Workflow 5!”

Geocortex Workflow can extend your Web AppBuilder applications by turning even the most sophisticated business processes into a set of simple, guided end-user interactions. Check out our Discovery Center and explore everything Geocortex Workflow 5 has to offer!

Discover Geocortex Workflow


Categories:
Archive
Open: 2018
2018
December 21, 2018

Integrating Pictometry, Bing and other 3rd party maps within your Geocortex applications

December 19, 2018

Recapping the Texas Geocortex Regional User Group

December 14, 2018

How to use Geocortex Workflow 5 to populate the Attribute Table in Web AppBuilder for ArcGIS [Geocortex Tech Tip]

December 12, 2018

Finding yourself: Using geolocation in mobile and web applications [Webinar]

December 7, 2018

Enabling real-time user-to-user map collaboration within Geocortex Essentials

December 5, 2018

Reflecting on 2018

November 30, 2018

Creating mailing labels with reports with Geocortex Reporting 5 [Geocortex Tech Tip]

November 29, 2018

Alberta Energy Regulator: Supporting safe and responsible energy resource operations

November 26, 2018

Important update for ArcGIS and Transport Layer Security (TLS) Protocol Support!

November 23, 2018

Using fine-grained security to control access to layers, features, attributes and application functionality [Geocortex Tech Tip]

November 21, 2018

Enhancing Web AppBuilder for ArcGIS® with Geocortex Reporting [Webinar]

November 16, 2018

Using Geocortex Workflow 5 to automatically display a form for editing layer attributes [Geocortex Tech Tip]

November 15, 2018

Geocortex Achieves Esri’s “Release Ready Specialty” Designation

November 14, 2018

Celebrating GIS Day with York Regional Police: How GIS Data Has Mitigated Risk & Increased Efficiency in Crime Prevention

November 9, 2018

How to access a SQL database from a workflow using Geocortex Workflow 5 [Geocortex Tech Tip]

November 2, 2018

How to send an email from a workflow using Geocortex Workflow 5 [Geocortex Tech Tip]

October 26, 2018

How to add and configure charts inside reports with Geocortex Reporting 5 [Geocortex Tech Tip]

October 24, 2018

Geocortex Water Webinar Series

October 19, 2018

Integrating Geocortex Essentials with ArcGIS Online and ArcGIS Enterprise portal [Geocortex Tech Tip]

October 17, 2018

Cross-Platform Development with Xamarin [Webinar]

October 12, 2018

City of Fort Collins: Dynamic flood maps for public awareness, and flood insurance rate saving

October 2, 2018

Technology Q&A: Geocortex Workflow 5 “Behind the Firewall”

June 27, 2018

Geocortex Essentials 4.10 is here!

June 12, 2018

Geocortex and the GDPR

June 6, 2018

Geocortex at the 2018 Esri User Conference

May 15, 2018

Technology Q&A: The evolution of Geocortex and Web AppBuilder for ArcGIS

April 25, 2018

How to manage data collected from Geocortex Workflow 5 forms [Geocortex Tech Tip]

April 18, 2018

Delivering accessible mapping applications for everyone [Geocortex Tech Tip]

April 11, 2018

How to search for data in a non-spatial database [Geocortex Tech Tip]

April 5, 2018

Configuring Geocortex Analytics to monitor a new Portal for ArcGIS instance [Geocortex Tech Tip]

March 27, 2018

Getting started with forms in Geocortex Workflow 5 [Geocortex Tech Tip]

March 21, 2018

Using Geocortex Workflow with Web AppBuilder for ArcGIS [Geocortex Tech Tip]

March 15, 2018

Understanding tool usage in your GIS applications [Geocortex Tech Tip]

March 7, 2018

Running Geocortex Essentials workflows from an identify operation [Geocortex Tech Tip]

March 2, 2018

GIS Health Assessment: A new way to think about your system

February 28, 2018

Using the in-app help system in Geocortex Workflow 5 [Geocortex Tech Tip]

February 21, 2018

How to configure a personalized dashboard in Geocortex Analytics [Geocortex Tech Tip]

January 25, 2018

GIS is shifting to SaaS, and it’s a win for everyone


Categories

Popular Tags
Accessibility Active Operating Picture ArcGIS ArcGIS Online Customer Story Data Models Energy Esri Geocortex Geocortex 5-Series Geocortex Analytics Geocortex Essentials Geocortex Mobile Viewer Geocortex Printing 5 Geocortex Reporting Geocortex Reporting 5 Geocortex Web Viewer Geocortex Workflow Geocortex Workflow 5 GIS GIS Day GIS Health Integrations Oil & Gas Pipelines Portal for ArcGIS SaaS Tech Tip Tech Tips Transportation Water Industry Web AppBuilder Web GIS Xamarin

Contributors