[ic] Database transaction?

Mike Heins mike at perusion.com
Mon May 30 19:52:57 EDT 2005


Quoting Kevin Walsh (kevin at cursor.biz):
> T. Stoffels [tstoffels at gmail.com] wrote:
> > I found this
> > (http://www.icdevgroup.org/interchange-doc-5.2.0/frames/ictemplates_17.html):
> >
> > [perl products] ... [/perl]
> >
> > It seems to me, that a query from inside a perl statement is limited
> > to one table (since the table has to be passed in the [perl] tag.
> > When I give it a try it always throws the "Database not shared" at me.
> > 
> Use the "tables" named parameter, rather than a positional parameter,
> as follows:
> 
>     [perl tables="products sometable othertable"] ... [/perl]
> 
> See section 4.53 [perl] in the Interchange Tags Reference for more
> information:
> 
>     http://www.interchange.rtfm.info/docs/rtfm_ictags.html

You can also just put the database in transactions mode and
use multiple queries. (Your idea of semicolon-separated queries
would not ensure that, by the way, with DBI AutoCommit on.)

[flag type=transactions table="products inventory pricing"]

[perl tables="products inventory pricing"]

	delete $Scratch->{all_worked};

	my $db = $Db{products};

	$db->query('update products ...')
		or return "erred";
	$db->query('update products ...')
		or return "erred";
	$db->query('update pricing ...')
		or return "erred";

	$Scratch->{all_worked} = 1;

[/perl]

[if scratch all_worked]
    [flag type=commit table="products inventory pricing"]
[else]
    [flag type=rollback table="products inventory pricing"]
[/else]
[/if]

WARNING: If you have opened the table earlier in the page (in the template,
for example), you might have trouble on Postgres or some databases.  Also,
if you try to write after all this you will need to commit that, too, because
the table is opened in transactions mode (i.e. DBI AutoCommit off).

NOTE: We do the flag operations outside of embedded Perl, as otherwise
DBD drivers might have problems (Postgres most likely).

-- 
Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.765.647.1295  tollfree 800-949-1889 <mike at perusion.com>

Being against torture ought to be sort of a bipartisan thing.
-- Karl Lehenbauer


More information about the interchange-users mailing list