Re: SQLTransformer and Transactions
Luca,
I would prefer to use stored procedures, but they are not implemented
yet in MySQL and we use MySQL in all our applications. MySQL doesn't
suport the begin-end; construction you used below either AFAIK.
/Daniel Fagerstrom
Luca Morandini wrote:
Sylvian,
I'm not completely convinced a rewriting of SQLTransformer is needed to handle transactions... I tried the following:
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;
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
-
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, email: [EMAIL PROTECTED]
Re: SQLTransformer and Transactions
Sylvain Wallez wrote: I consider that getting a connection from the pool for each creates some unnececessary load on the pool. So I would go for the first solution (use the same connection). I will try that. There will still be a need for new connections for sub queries, to reuse the same connection for sub queries as well one would need to cache the result from the embedding query. As I haven't used subqueries yet and don't know what typical use cases are, I can't evaluate the consequnces of such cashing, any ideas? /Daniel Fagerstrom - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, email: [EMAIL PROTECTED]
RE: SQLTransformer and Transactions
> -Original Message- > From: Sylvain Wallez [mailto:[EMAIL PROTECTED]] > Sent: Sunday, December 15, 2002 1:48 PM > To: [EMAIL PROTECTED] > Subject: Re: SQLTransformer and Transactions > In the above example, the transaction is contained in a _single_ > element. What Daniel pointed out, IIUC, is that it's not > possible to have a transaction across several . Sure, but why do that when there is an alternative ? Best regards, - Luca Morandini GIS Consultant [EMAIL PROTECTED] http://utenti.tripod.it/lmorandini/index.html - - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, email: [EMAIL PROTECTED]
Re: SQLTransformer and Transactions
Luca Morandini wrote:
Sylvian,
I'm not completely convinced a rewriting of SQLTransformer is needed to handle transactions... I tried the following:
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;
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.
In the above example, the transaction is contained in a _single_
element. What Daniel pointed out, IIUC, is that it's not
possible to have a transaction across several .
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]
Re: SQLTransformer and Transactions
Antonio Gallardo wrote:
Can you explain what you have in mind Sylvain? I dont understand what you
have in mind.
I guess it's the "load on the pool" that is unclear.
What I'm saying is that I consider a waste of CPU to get a connection
from the same pool for each encountered in the
document. The transformer can be made quicker by getting a connection
either at the start of the document or at the first ,
and close it in endDocument() or recycle() (this last one is better
since endDocument() may not be called if an exception occurs in the
pipeline).
Sylvain Wallez dijo:
I consider that getting a connection from the pool for each
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]
RE: SQLTransformer and Transactions
Sylvian,
I'm not completely convinced a rewriting of SQLTransformer is needed to handle
transactions... I tried the following:
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;
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:
> >
> > ...
> >
> >
> > BEGIN;
> >
> >
> >
> >
> > Do something
> >
> >
> >
> >
> > Do something that is based on the previous query
> >
> >
> >
> >
> > COMMIT;
> >
> >
> > ...
> >
> > 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
> 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]
Re: SQLTransformer and Transactions
Can you explain what you have in mind Sylvain? I dont understand what you
have in mind.
Antonio Gallardo
Sylvain Wallez dijo:
>
> I consider that getting a connection from the pool for each
> 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]
Re: SQLTransformer and Transactions
Daniel Fagerstrom wrote:
I'd like to use transactions in the SQLTransformer and tried something
like:
...
BEGIN;
Do something
Do something that is based on the previous query
COMMIT;
...
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
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]
