Re: Queuing DBI transactions

Not too difficult. Throw the queries into a queue prior to sending them to the DBMS. If the query was successful, pop the query from the queue. There are many different types of queues but a simple @query_queue sounds like it will be sufficient (pseudo code):

my @query_queue;
my $query = qq{
           UPDATE wms_rf_server_port
            SET    available           = ?,
                   last_updated_by     = ?,
                   last_updated_date    = ?
            WHERE  port_id IN ( $port_ids )
    };

push @query_queue, $query

.... if the query is successful {
   pop @query_queue;

.... else, reconnect and try sending the query back to the DBMS
[download]

Jason L. Froebe

Blog, Tech Blog

Comment on Re: Queuing DBI transactions Download Code

Replies are listed 'Best First'.
Re^2: Queuing DBI transactions by ikegami (Patriarch) on Oct 04, 2007 at 19:03 UTC
You need to save the args too `my @query_queue; sub do_query { my ($sth, @args) = @_; push @query_queue, [ $sth, @args ]; flush_queries(); } sub flush_queries { while (@query_queue) { my ($sth, @args) = @{ $query_queue[0] }; ...[ attempt query ]... if (...[ successful ]...) { shift @query_queue; } else { return; } } return 1; } do_query(...); do_query(...); do_query(...); flush_queries() or die;` [download] Depending on the reconnect code, it might be more advantageous to pass the dbh and the stmt instead of the sth, but you get the idea.	[reply] [d/l]
Re^2: Queuing DBI transactions by Akoya (Scribe) on Oct 04, 2007 at 18:10 UTC
Your suggestion accounts for the query string, but not the parameters that must accompany it. Perhaps, I could push a hash reference onto the queue, with the hash containing 1) the SQL statement, and 2) an array of parameters.	[reply]
Re^3: Queuing DBI transactions by jfroebe (Parson) on Oct 04, 2007 at 18:43 UTC
I would tack on another level to the hash which would contain the connection the query was supposed to go out on - else you will have to handle multiple queues if you have multiple connections. Jason L. Froebe Blog, Tech Blog	[reply]
Re^2: Queuing DBI transactions by SuicideJunkie (Vicar) on Oct 04, 2007 at 18:04 UTC
That sounds more like a query stack, what with the push vs pop. Perhaps push and shift would be better. (With unshift to allow an operation to "jump the queue")	[reply]
Re^3: Queuing DBI transactions by jfroebe (Parson) on Oct 04, 2007 at 18:38 UTC
Partially :) In this simple example, the queue would be a queue with the exception of removing the good query run from the end of the queue. It's a hybrid queue/stack depending on your point of view ;-) Jason L. Froebe Blog, Tech Blog	[reply]