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!
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!
Contributors