SQL Activity

[mohdali]

I propose to introduce an ExecuteSql activity that can enable reading or writing data using an SQL query. The activity will have the following properties:

• Lets the user select a connecrion on which to execute the query. The connections can be obtained form a provider. For example, provide the connection strings in Elsa configurations, or enumerate from a database, etc.
• The execution can be in read mode or write mode.
  • Read mode will execute a select query and return the result set.
  • Write mode will execute an insert or update query and return the number of affected rows.
• The query will be parameterized so that the user can pass the workflow variables into the query
• The data driver should be also pluggable, which allows the developer to select which data access library to use for executing the query.

As a plus, if Elsa designer can be extended to support SQL syntax it will allow syntax highlighting and possibly code completion while building the query.

[tomy2105]

I like the proposal but read and write mode is a bit of confusing since both delete, insert and update can return a result set same as select.

In addition I would, maybe, not fetch whole result set at once but block to browse through result set (forward, backward, etc...) and fetch as it executes. Fetching everything for big result sets could be bad.

[mohdali]

I like the proposal but read and write mode is a bit of confusing since both delete, insert and update can return a result set same as select.

In addition I would, maybe, not fetch whole result set at once but block to browse through result set (forward, backward, etc...) and fetch as it executes. Fetching everything for big result sets could be bad.

Since we now have the SQL activity we can probably start thinking about this.

I like the idea to make the activity a kind of a blocking iterator.

I also want to have a kind of bulk operations that will be more efficient with large data.

We may need to also support more DB types. Oracle is could be next.