Could the PP Database plugin be enhanced, so it can be used as an input method?

The driving data for one of my forms comes from an SQL database, but currently there's no way of knowing that new data has been added.

What I'm doing to work round it is to create a file on disk that will force the Workflow to read the database, but it isn't ideal.

Thanks, Rob.

Rob,

You could just use a Create File input task that creates an empty file followed by a DB Plugin as the second task. The frequency at which polling the database is performed will be driven by the Process Schedule.

You have to know that an Input task never actively runs by itself: it is either triggered by the Process Schedule or by a Helper Service (like HTTP, LPD, etc.) that actively monitors an input (the only exception is the Folder Input, which is triggered by a Directory Monitoring service, which is a native component of Windows).

So the only way for your suggestion to work would be to create an independent service that actively polls the database and that triggers a process when new data (what is new data anyway?!?) arrives. It would be nearly impossible for us to come up with a generic service to do that.

Philippe, I'm trying to get my head around what you've written, so apologies if I don't complete understand it.

The Creat File method of polling with the DB will certainly work, although it is a little verbose.

Your description around how processes are triggered in Watch implies that they are largely passive. I would have agreed with this, but then I started looking at the other Input methods and came across the FTP Input.

This is by no means passive and requires an active connection to a remote system to determine if there's any work for the process to do.

This is how I would envisage a Database Input working, except that it's connecting to a database rather than an FTP server.

In terms of what is 'new' data. This would be determined by the Query String embedded in the Database Input plugin. If any records are returned from the query, then the process would become active and the recordset processed as if it were called using a standard Planet Press Database plugin.

What do you think?

Rob.

The FTP input is triggered by the Process Schedule: it will only poll the FTP folder when the Process asks it to do so.

The FTP Input first issues a "dir" on the FTP server to find out if anything new is in the folder. That kind of command can be issued every 4 seconds (the default value for Process Schedules set to ASAP) because it's a lightweight command that requires barely any bandwidth.

So why not do the same for the DB Plugin? Well, we could, but it might exact a hefty toll on your bandwidth. Issuing a SQL request that may potentially retrieve a full recordset could be a bit of a strain on the network if you send it every 4 seconds. Or every minute, for that matter.

However, I do see the value in the plugin having the ability to "launch" the process if it receives any recordset and to abort if no records are returned from the Query.

Let me ponder on that one for a while, you never know...

To get round the problem of retrieving lost of data, you could dynamically translate the query first, e.g. :-

select * from mytable where status = 'READY'

..becomes..

select count(*) from mytable where status = 'READY'

If the count is > 1, then run the actual query as well.


Having said that, I would assume the records in the original 'select *' query are all required anyway, so it doesn't matter about the bandwidth - what's the point in selecting records to trigger a process that you don't want to use?

Ultimately, it's up to the person configuring such a plugin to design the query sensibly, understanding what's going on and what effect it could have.

Just my thoughts.

Rob.

Well to expand on your ideas, I'd rather we didn't have to parse, interpret and modify the actual query to determine whether new data is available: there's just too many forms of the SELECT statement that could screw up the parsing.

However, I think a solution to determining if new data does exist would be to have an area where the user could type in a preliminary SQL request to be issued prior to the actual request.
The user might then enter something like:

SELECT TOP 1 from mytable where status = 'READY'

If that preliminary statement retrieves 1 or more records, then we would issue the actual request.

(Note that I'm not saying we will be doing that... we're just chatting here... :-P )

> we're just chatting here... :-P

Agreed :-)