Sylvian,
I'm not completely convinced a rewriting of SQLTransformer is needed to handle
transactions... I tried the following:
<sql:query isstoredprocedure="false">
name="test">
begin
DELETE FROM CRRegion
WHERE Id > 80;
COMMIT;
INSERT INTO CRRegion
(Id, Name)
VALUES
('90', 'Foo');
INSERT INTO CRRegion
(Id, Name)
VALUES
('91', 'Bar');
ROLLBACK;
end;
</sql:query>
And it worked just fine (both the commit and the rollback parts). I even tried
some variations on this theme and they worked just as well.
There is a catch though: I tried on Oracle only.
I don't know whether the begin/end statement could be used with other DBMSes.
Anyway, I agree the use of a single connection to process the complete input document
would increase performance and reduce
side-effects.
Best regards,
P.S.
As you may easily imagine, I consider Stored Procedures the only proper place for
handling transactions; but, if people cannot/want
not go for SPs... let's give them some alternatives.
---------------------------------------------
Luca Morandini
GIS Consultant
[EMAIL PROTECTED]
http://utenti.tripod.it/lmorandini/index.html
---------------------------------------------
> -----Original Message-----
> From: Sylvain Wallez [mailto:[EMAIL PROTECTED]]
> Sent: Saturday, December 14, 2002 11:24 PM
> To: [EMAIL PROTECTED]
> Subject: Re: SQLTransformer and Transactions
>
>
> Daniel Fagerstrom wrote:
>
> > I'd like to use transactions in the SQLTransformer and tried something
> > like:
> >
> > ...
> > <execute-query>
> > <query>
> > BEGIN;
> > </query>
> > </execute-query>
> > <execute-query>
> > <query>
> > Do something
> > </query>
> > </execute-query>
> > <execute-query>
> > <query>
> > Do something that is based on the previous query
> > </query>
> > </execute-query>
> > <execute-query>
> > <query>
> > COMMIT;
> > </query>
> > </execute-query>
> > ...
> >
> > This does not work in the current implementation of the SQLTransformer
> > as it gets a new connection from the pool for each execute-query and
> > all statements within one transactions must be run from the same
> > connection. Even worse, as the pooled connections can be reused from
> > another pipeline, the same transaction can continue in a completely
> > unexpected place for another user. So it might work as expected for a
> > single user but in unexpected ways for multiple users.
> >
> > One way to solve this would be to use the same connection for all
> > execute-query in an input xml document. It would still be necessary to
> > open new connections for embedded queries, using the ancestor
> > functionality.
> >
> > Another solution would be to introduce a transaction tag and letting
> > all queries within it use the same connection and let the start tag
> > execute "connection.setAutoCommit(false)" and the end tag execute
> > "connection.commit()". Embedded queries must of course still have own
> > connections.
> >
> > Booth solutions requires some rewriting of the connection handling
> > code in the SQLTransformer.
> >
> > As I need transactions in the application I currently work on, I have
> > to modify the SQLTransformer to handle it. Is there any problems with
> > the above proposed methods? Which one do you prefer? Are there better
> > methods?
>
>
> I consider that getting a connection from the pool for each
> <execute-query> creates some unnececessary load on the pool. So I would
> go for the first solution (use the same connection).
>
> Other thoughts from the database specialists out there ?
>
> Sylvain
>
> --
> Sylvain Wallez Anyware Technologies
> http://www.apache.org/~sylvain http://www.anyware-tech.com
> { XML, Java, Cocoon, OpenSource }*{ Training, Consulting, Projects }
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, email: [EMAIL PROTECTED]
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, email: [EMAIL PROTECTED]
