Board index » jbuilder » transaction/commit/abort with borland components?

transaction/commit/abort with borland components?


2004-07-15 08:38:38 PM
jbuilder3
I have a problem with the borland database componenets.
I am supposed to do a query for editing a single row
select * from x where x.y = 'something' for update;
this sql query locks the row (on database side) so noone else can use
delete, update or for update on it.
BUT, for the lock to work it requires a 'begin transaction;" sql statement.
and to open the lock 'abort;' or 'commit;'
so, is there a way to do a simple query or statement without using the
queryDescriptors wich are kinda overkill for this (and propably use
their own transaction/commit behaviour).
OR is there a way of telling the database object to do this, i tried
.setUseTransactions(), but its only used with .saveChanges() and i NEED
the lock with simple select statememts.
any ideas welcome
-petteri
 
 

Re:transaction/commit/abort with borland components?

On Thu, 15 Jul 2004 15:38:38 +0300, Petteri Torkko
< XXXX@XXXXX.COM >wrote:
Quote
so, is there a way to do a simple query or statement without using the
queryDescriptors wich are kinda overkill for this (and propably use
their own transaction/commit behaviour).
In pure JDBC you would use something like
Connection.prepareStatement("select * from x where x.y = 'something'",
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
You can get the Connection from the Database using getJdbcConnection().
--
Tor Iver Wilhelmsen
TeamB
 

Re:transaction/commit/abort with borland components?

Tor Iver Wilhelmsen (TeamB) wrote:
Quote
In pure JDBC you would use something like

Connection.prepareStatement("select * from x where x.y = 'something'",
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

You can get the Connection from the Database using getJdbcConnection().
Tried this for sending "begin transaction" to the database but it seems
datasets/querydescriptors use their own commit/transaction behaviour and
the lock is not achieved...
I'm using postgre and you can lock a table or a row by selecting it "for
update", but you have to "begin transaction" for it to work. Lock is
removed with "abort" or "commit".
So i need to know how to set DataBase object and dataset+querydescriptr
objects to work how i need them in this case.
"developing database applications for jbuilder x" doesnt say anything on
this subject, and the dbswing documentation is a bit useless to say the
least
any help still appreciated
-petteri
 

{smallsort}

Re:transaction/commit/abort with borland components?

Ok, you can send the statement with database.executeStatement("begin
transaction")
if you use this before query.executeQuery(); the lock will work
qd = new querydescriptor("select * from y where y.x = 'something' for
update" ..... .. . . .)
query.close()
query.setQuery(qd)
database.executeStatement("begin transaction")
query.executeQuery()
and the row in question gets locked for deletion, update and select for
update queries
lock is freed with database.executeStatement("commit") or ("abort")
dunno if this is the best way to do it but atleast it works like i want
it to :)
Petteri Torkko wrote:
Quote
Tor Iver Wilhelmsen (TeamB) wrote:

>In pure JDBC you would use something like
>
>Connection.prepareStatement("select * from x where x.y = 'something'",
>ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
>
>You can get the Connection from the Database using getJdbcConnection().


Tried this for sending "begin transaction" to the database but it seems
datasets/querydescriptors use their own commit/transaction behaviour and
the lock is not achieved...

I'm using postgre and you can lock a table or a row by selecting it "for
update", but you have to "begin transaction" for it to work. Lock is
removed with "abort" or "commit".

So i need to know how to set DataBase object and dataset+querydescriptr
objects to work how i need them in this case.

"developing database applications for jbuilder x" doesnt say anything on
this subject, and the dbswing documentation is a bit useless to say the
least

any help still appreciated

-petteri

 

Re:transaction/commit/abort with borland components?

Did you try..
database1.setAutoCommitt(false);
then latter...
database1.committ();
or..
database1.rollback();
--
=============================================
TeamB are volunteer helpers. Please DO NOT REPLY VIA EMAIL!
Post all questions and replies to this newsgroup ONLY
For papers on DataExpress, Applets, JSP, and Web Development go to:
www.microps.com/mps/papers.html
====================================================
 

Re:transaction/commit/abort with borland components?

John B. Moore (TeamB) wrote:
Quote
Did you try..

database1.setAutoCommitt(false);

then latter...

database1.committ();

or..

database1.rollback();


Yes, it didnt begin the transaction so the row wasnt locked.
Prolly could have been achieved with the drivers but it works
pretty well with
database.executeStatement("begin transaction");
query.execute();
//wait for save or cancel
database.executeStatement("commit"); ||
database.executeStatement("abort");
-petteri