Delete after read using database-inbound protocol

I’m not sure Cloverleaf can keep track of specific rows (or identifiers of rows) while it is performing it’s select query, and then hand that information to the read success or read failure UPOC. Perhaps it can be an enhancement, but this is kind of a vague request. How would Cloverleaf know what to keep track of? It may be cumbersome to implement. You could designate a column to track, in a new variable on the config screen, and then Cloverleaf would make this variable available to the Read Success query.

On the other hand, ‘deleting behind’ is a typical component of a transaction and it would be nice to have a feature to help handle this activity.

Using a stored procedure instead of a raw SQL query may help. The stored procedure could do both of returning the desired data and also setting some indicator that certain rows were fetched. The indicator would be somewhere in the database. Perhaps the best place for the indicator is another column on the same table. The next time the DB Inbound is executed, the stored procedure would ignore the rows that had the indicator set.

The Read Success UPOC would also execute a stored procedure that would delete the rows with the indicator set. However that does not help executing the Read Failure UPOC – if you could guarantee that the Inbound DB thread only runs single-threaded, then the Read Failure UPOC could remove the ‘processed’ indicator, thus making the rows available for the next execution. But if this is multi-threaded, you could have two executions of the Inbound, get two different result sets, and have ‘processed’ indicators set for both of them, and then if the Read Failure is executed for one of the sets, it could clear out the processed flag for both result sets.

Perhaps a Cloverleaf enahncement in this area would be to request a timestamp or guid be generated for each execution, and be made available in a special variable that could be included as an argument to the stored procedure call. And this same value/variable would be made available to the Read Success or Read Failure action.

For this type of case, I’ve used a transaction previously.

ie

BEGIN TRANSACTION

SELECT

DELETE

if all ok:

COMMIT TRANSACTION

otherwise

ROLLBACK TRANSACTION

Its a good idea to set the timeout value for sqlite when opening the database.

If the timeout is not set, it will return an error if the table is locked. If it is set it will take up to the timeout before it returns an error.

ie
if {[catch {sqlite gDb $aDatabaseFile
[code]if {[catch {sqlite gDb $aDatabaseFile

I believe that SQLite has a ROWID for each row in the table.

Using a SELECT * gives you a whole list of records that you would need to loop over.  If timing is quite critical, it might be worth getting them all in a list, looping over the list and processing the DELETEs one at a time by the ROWID.

If timing is not critical, it might be simpler to use the query:

SELECT ROWID, * FROM MyTable

ORDER BY ROWID

LIMIT 1

This will give you a single record that you can parse out the columns on.

You can then

DELETE FROM MyTable

WHERE ROWID = $RowId

I always like to be explicit in my row handling.  The DELETE * FROM MyTable  is dangerous if you have more than one process looking at or manipulating the table.  That may not be your intention from the beginning, but you may be setting a land mine for yourself later.

With other databases I’ve handled this type of process using database cursors. I’m not sure whether or not this is supported in Sqlite.

Here’s an example of how Oracle can do it:

http://www.techonthenet.com/oracle/cursors/current_of.php

You could have a cursor that processes each row, then you delete the row with a “where current of cursor_name” clause.

You could try the following:

1. Define a database schema include the identity column.  Said rowid, value

2. Read the data into the table define by the schema.

3. Read Success use:

Delete from mytable where rowid =

Notice that is needed so that rowid will replace with the value in the table.