[ic] Database Connection Concurrency/Exclusivity

[Mike Heins]

Quoting Todd L. Cawthron (todd at lunsfordgroup.com):
> I'm running Interchange using a SQL database (Sybase to be exact). Recently,
> I ran into a situation where I wanted to use a transaction when deleting and
> then inserting rows into a table.
> 
> I could stitch together a batch of SQL statements and send the batch as a
> single query, or I could use multiple queries. The batch would probably be
> the safest route, although it could be a problem if the batch was really
> large.
> 
> If I went the multiple query route, I would begin a transaction, run a
> delete query, then a number of insert queries, and finally commit the
> transaction. If I do this with a number of calls to the query tag (or by
> using the underlying database handle directly), will I have any problems
> running in pre-fork mode? In other words, will the interchange process that
> is running this subroutine always use the same database handle for each call
> and will it have exclusive use of this handle? Or, will other interchange
> processes potentially issue database commands using the same database
> connection, which might result in queries being unintentionally included in
> my transaction?

This should not be a problem. Unless you specify that the table is
HOT, which is not the default, the same handle will not be used.

You *will* have to specify 

	Database foo  HAS_TRANSACTIONS  1

as we don't do that for Sybase.

The rest should be just like in the other ones:

	[flag type=transactions table=foo]
	[query sql="insert into foo values (...)"]
	[query sql="update foo set ..."]
	[flag type=commit table=foo]

The flag for transactions should always be in ITL, not in embedded
perl. The rest can be in either.

-- 
Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.513.523.7621      <mike at perusion.com>

For a successful technology, reality must take precedence over public
relations, for Nature cannot be fooled. -- Dick Feynman