Execute a dynamic SqlQuery

Permanently deleted user

20. August 2015 17:39

In a workflow I want to execute a dynamic build-up query. Not only the where clause is flexible, but also the columns are flexible.

For this a variable called strQuery is filled with the correct query. I tested the query, copy from an alert window and putting the result in the command parameter for SqlQuery. It works fine.

If I'm using strQuery as the value for command in SqlQuery an error pops up.

"Unhandled exception: 'One or more errors occurred.' in activity '1.28: SqlQuery'. One or more errors occurred. ORA-00900: invalid SQL statement" 

How can I use a dynamic query in workflow.

Thanks in advance.

Ronnie

0

• 

  Ryan Cooney

  20. August 2015 23:41

  Hi Ronnie,

  The out of the box SQL Query and SQL Non Query activities intentionally prevent you from using a SQL statement that is determined at runtime. We do this to prevent SQL injection attacks.  You'll notice that the "Command Text " input is actually just a literal string. It does not accept a VB expression so you can't use a variable to set it. The " invalid SQL statement" error you are getting is the database complaining that the literal string " strQuery "  is not SQL.

  The way to make a dynamic SQL query in a workflow is to create a custom activity that will accept a dynamic  SQL statement as an input argument, and then be extremely careful how you use it so you don't put your data at risk.

   

  --Ryan

  0
• 

  Permanently deleted user

  24. August 2015 15:14

  Hi Ryan,

   

  thanks for the answer. I found a work around.  Another question about the same function. Is it possible to set the connection string with a variable? Or is this also blocked due to security reasons? The reason we want to be able to set the connection string with a variable is, we use different databases for development, testing, acception and prodcution and want to be able to set the connection string as a parameter from the viewer. Otherwise we have to create a workflow for each environment.

   

  Ronnie

  0
• 

  Ryan Cooney

  24. August 2015 22:33

  Hi Ronnie, 

  The connection settings are blocked for the same reason. For this scenario I would suggest referencing a named connection string from the web.config, rather than explicitly setting the connecting string on the activity.

  --Ryan

  0
• 

  Permanently deleted user

  4. September 2015 15:08

  Hi Ryan,

  thanks for your answer. I've tried your solution but I have troubles making to get it working. What ever I try, the named connection is not appering in the list of named connections in the SQL query activity.

  Where should I put the web config file (so that is only available for this site) and what is the format of the connection string?

  Ronnie

   

  0
• 

  Ryan Cooney

  4. September 2015 22:32

  Hi Ronnie,

  The connection string needs to be added to the existing "REST Elements\REST\web.config" and "Workflow Designer\Geocortex.Workflow.Designer.exe.config" files.

  --Ryan

  0
• 

  Permanently deleted user

  12. September 2015 18:15

  Hello,

  This is something similar to what i wish to do in Workflow.

  I have feature layers loading from ArcGIS Online account. I have organization database which contains few important tables holding x,y co-ordinates. I want to dynamicallly show those points over feature layer thru workflow. 

  I used SQLQuery function , connected to database, add query there. In sequence, i added DataTable to Featureset tool where i just assigned datatable variable as input and created featureset variable to hold the output result. 

   

  I m unsure if any of them got populated while my site loaded as i simply see no action. 

  Can you guide me if am on correct path? Do i need to do anything with featureset variable to show points on map? 

   

  Thanks. 

  P.S - I m newbie to GE and exploring workflow on own. 

  0

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.