Transaction concept (Real Studio network user group Mailinglist archive)

Back to the thread list
Previous thread: Valentina for RB, V4MD, VAPP 1.9.1b3
Next thread: Valentina, OUTER JOIN and DISTINCT done.


Re: RB NUG FAQ 010   -   Chris Cummer
  Transaction concept   -   Maximilian Tyrtania
   Re: Transaction concept   -   Norman Palardy
    Re: Transaction concept   -   Maximilian Tyrtania
     Re: Transaction concept   -   Norman Palardy
   Re: Transaction concept   -   gary hayenga
    Re: Transaction concept   -   Maximilian Tyrtania
     Re: Transaction concept   -   Norman Palardy
    Transaction concept   -   Jule Epp/Max Tyrtania
     Re: Transaction concept   -   Norman Palardy
      Re: Transaction concept   -   Maximilian Tyrtania
       Re: Transaction concept   -   Norman Palardy

Transaction concept
Date: 06.05.02 09:26 (Mon, 06 May 2002 10:26:31 +0200)
From: Maximilian Tyrtania
Folks,

RB's documentation is a bit vague on the subject of transactions. It says
that a transaction is opened as soon as you successfully went through these
steps

1.open a connection and obtain a database object
2.obtain a databasecursor via SQL-Select
3.call dbc.edit
4.do some inserts or updates

, which is fair enough. But then it states that a new transaction is opened
as soon as you call db.commit or db.rollback, which i find mildly confusing
- does this new transaction refer to the cursor i 've previously obtained in
step 2 ? If not, why don't i need to go through the steps 2-4 again to have
a new transaction started?

Wouldn't it make sense to let the cursor be a property of a database object?
To my mind, the database should be in a transaction, not the cursor.

Please enlighten me,

Maximilian Tyrtania | <email address removed>
fischerAppelt Kommunikation GmbH
Tucholskystraße 18 | D-10117 Berlin | Germany
Tel. ++49-30-726146-728 | Fax ++49-30-726146-10
Mobil 0173-6104345 | www.fischerAppelt.de
-

Re: Transaction concept
Date: 06.05.02 15:45 (Mon, 6 May 2002 08:45:14 -0600)
From: Norman Palardy
The cursor can be edited and so transactions apply to the cursor
IF this were a multi user db, only your cursor would be affected and
anyone reading data while you had the cursor open would get the data in
the database that you had not yet committed
But it's not.

A transaction is "temporary" until such time as you say commit or
rollback
(unless of course you work on a system where each update, etc) is
treated as a transaction.

The transaction is closed when you say commit or rollback

On Monday, May 6, 2002, at 02:26 AM, Maximilian Tyrtania wrote:

> Folks,
>
> RB's documentation is a bit vague on the subject of transactions. It
> says
> that a transaction is opened as soon as you successfully went through
> these
> steps
>
> 1.open a connection and obtain a database object
> 2.obtain a databasecursor via SQL-Select
> 3.call dbc.edit
> 4.do some inserts or updates
>
> , which is fair enough. But then it states that a new transaction is
> opened
> as soon as you call db.commit or db.rollback, which i find mildly
> confusing
> - does this new transaction refer to the cursor i 've previously
> obtained in
> step 2 ? If not, why don't i need to go through the steps 2-4 again to
> have
> a new transaction started?
>
> Wouldn't it make sense to let the cursor be a property of a database
> object?
> To my mind, the database should be in a transaction, not the cursor.
>
> Please enlighten me,
>
> Maximilian Tyrtania | <email address removed>
> fischerAppelt Kommunikation GmbH
> Tucholskystraße 18 | D-10117 Berlin | Germany
> Tel. ++49-30-726146-728 | Fax ++49-30-726146-10
> Mobil 0173-6104345 | www.fischerAppelt.de
> --
>
> ---
> Subscribe to the digest:
> <mailto:<email address removed>>
> Unsubscribe:
> <mailto:<email address removed>>

---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>

Re: Transaction concept
Date: 06.05.02 17:05 (Mon, 06 May 2002 18:05:35 +0200)
From: Maximilian Tyrtania
am 06.05.2002 16:45 Uhr schrieb Norman Palardy:

> The cursor can be edited and so transactions apply to the cursor
> IF this were a multi user db, only your cursor would be affected and
> anyone reading data while you had the cursor open would get the data in
> the database that you had not yet committed
> But it's not.

Oh, actually my database (Openbase) IS a true multi user db. The question I
asked referred to RB's database API in general, not the RB databse.

>
> A transaction is "temporary" until such time as you say commit or
> rollback
> unless of course you work on a system where each update, etc is
> treated as a transaction.

My system doesn't decide if an update is treated as a transaction, RB's
commands issue the "rollback" or "commit".
>
> The transaction is closed when you say commit or rollback

Yeah, but here you stop where the mystery starts: the docs say -
At that point (after committing or rollback) a new transaction is started.
So, obviously the cursor is in a transaction, not the database. (By the way,
i find "database" an unfortunate name for this kind of object - it's a
database connection, really.) This i find contra-intuitive as I am obviously
done with my cursor (e.g. the user cancelled the form) - why would i want it
to be in a second transaction after i've said "commit"?

Maximilian Tyrtania | <email address removed>
fischerAppelt Kommunikation GmbH
Tucholskystraße 18 | D-10117 Berlin | Germany
Tel. ++49-30-726146-728 | Fax ++49-30-726146-10
Mobil 0173-6104345 | www.fischerAppelt.de
-

Re: Transaction concept
Date: 06.05.02 19:27 (Mon, 6 May 2002 12:27:50 -0600)
From: Norman Palardy
On Monday, May 6, 2002, at 10:05 AM, Maximilian Tyrtania wrote:

> am 06.05.2002 16:45 Uhr schrieb Norman Palardy:
>
>> The cursor can be edited and so transactions apply to the cursor
>> IF this were a multi user db, only your cursor would be affected and
>> anyone reading data while you had the cursor open would get the data in
>> the database that you had not yet committed
>> But it's not.
>
> Oh, actually my database (Openbase) IS a true multi user db. The
> question I
> asked referred to RB's database API in general, not the RB databse.

The transaction STILL applies only to the cursor

>>
>> A transaction is "temporary" until such time as you say commit or
>> rollback
>> unless of course you work on a system where each update, etc is
>> treated as a transaction.
>
> My system doesn't decide if an update is treated as a transaction, RB's
> commands issue the "rollback" or "commit".
>>
Some do. Sybase can be set in a mode where every statement is treated as
though it is followed by a COMMIT
Openbase does not seem to have this option so your program has to commit
and rollback as necessary

>> The transaction is closed when you say commit or rollback
>
> Yeah, but here you stop where the mystery starts: the docs say -
> At that point (after committing or rollback) a new transaction is
> started.
> So, obviously the cursor is in a transaction, not the database. (By the
> way,
> i find "database" an unfortunate name for this kind of object - it's a
> database connection, really.) This i find contra-intuitive as I am
> obviously
> done with my cursor (e.g. the user cancelled the form) - why would i
> want it
> to be in a second transaction after i've said "commit"?
>
I think in this regard the documentation is erroneous
In my work with Openbase and with REAL's database a transaction is
started when you issue the curs.edit

---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>

Re: Transaction concept
Date: 06.05.02 22:32 (Mon, 06 May 2002 17:32:10 -0400)
From: gary hayenga
With the Openbase plugin when you first say curs.edit it silently issues
a "Start Transaction" command to Openbase. The default for the database
itself is to Auto-commit everything. If you don't want it to
Auto-commit everytime you issue a curs.update then, after the first
db.commit that ends the silently started transaction, you need to
explicitly say db.sqlExecute("Start Transaction") or else a) You won't
be able to rollback and b) If you do say db.commit it will complain that
you don't have a transaction in progress.

If you want this changed talk to either REALsoftware or Openbase.

gary hayenga

> Subject: Re: Transaction concept
> From: "Maximilian Tyrtania" <<email address removed>>
> Date: Mon, 06 May 2002 18:05:35 +0200
>
> am 06.05.2002 16:45 Uhr schrieb Norman Palardy:
>
> > The cursor can be edited and so transactions apply to the cursor
> > IF this were a multi user db, only your cursor would be affected and
> > anyone reading data while you had the cursor open would get the data in
> > the database that you had not yet committed
> > But it's not.
>
> Oh, actually my database (Openbase) IS a true multi user db. The question I
> asked referred to RB's database API in general, not the RB databse.
>
> >
> > A transaction is "temporary" until such time as you say commit or
> > rollback
> > unless of course you work on a system where each update, etc is
> > treated as a transaction.
>
> My system doesn't decide if an update is treated as a transaction, RB's
> commands issue the "rollback" or "commit".
> >
> > The transaction is closed when you say commit or rollback
>
> Yeah, but here you stop where the mystery starts: the docs say -
> At that point (after committing or rollback) a new transaction is started.
> So, obviously the cursor is in a transaction, not the database. (By the way,
> i find "database" an unfortunate name for this kind of object - it's a
> database connection, really.) This i find contra-intuitive as I am obviously
> done with my cursor (e.g. the user cancelled the form) - why would i want it
> to be in a second transaction after i've said "commit"?
>
> Maximilian Tyrtania | <email address removed>
> fischerAppelt Kommunikation GmbH
> Tucholskystraße 18 | D-10117 Berlin | Germany
> Tel. ++49-30-726146-728 | Fax ++49-30-726146-10
> Mobil 0173-6104345 | www.fischerAppelt.de

---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>

Re: Transaction concept
Date: 07.05.02 09:02 (Tue, 07 May 2002 10:02:50 +0200)
From: Maximilian Tyrtania
gary hayenga wrote:

> With the Openbase plugin when you first say curs.edit it silently issues
> a "Start Transaction" command to Openbase.

Norman Palardy wrote:

> In my work with Openbase and with REAL's database a transaction is
> started when you issue the curs.edit

, but Brad Hutchings, who is an Openbase support staff member, writes:

> cur.edit does not seem to be issuing the "start transaction"

, a view that i used to share (the transaction is started only after you say
cur.edit AND do some Inserts/Updates/Deletes on the cursor).

So, that's 2:2 for cur.edit starting a transaction. My main complaint was
that the docs are somewhat vague about the whole issue - i hope my point is
made.

Maximilian Tyrtania | <email address removed>
fischerAppelt Kommunikation GmbH
Tucholskystraße 18 | D-10117 Berlin | Germany
Tel. ++49-30-726146-728 | Fax ++49-30-726146-10
Mobil 0173-6104345 | www.fischerAppelt.de
-

Re: Transaction concept
Date: 07.05.02 16:21 (Tue, 7 May 2002 09:21:53 -0600)
From: Norman Palardy
Well you could manually issue the start transactions via
sqlexecute("start transaction")
but then IF cur.edit is working the way it should and issuing an start
transaction you'd have issues
as you'd now have nested transactions that could be hard to commit or
rollback

This is easy to deal with by NOT using cur.edit but instead actually
issuing UPDATE sql statements (messy bbut then you do have low level
control)

Perhaps the thing to do is file a bug report with REAL, who I think
produced the plugin, regarding cur.edit not issuing a start transaction ?

On Tuesday, May 7, 2002, at 02:02 AM, Maximilian Tyrtania wrote:

> gary hayenga wrote:
>
>> With the Openbase plugin when you first say curs.edit it silently
>> issues
>> a "Start Transaction" command to Openbase.
>
> Norman Palardy wrote:
>
>> In my work with Openbase and with REAL's database a transaction is
>> started when you issue the curs.edit
>
> , but Brad Hutchings, who is an Openbase support staff member, writes:
>
>> cur.edit does not seem to be issuing the "start transaction"
>
> , a view that i used to share (the transaction is started only after
> you say
> cur.edit AND do some Inserts/Updates/Deletes on the cursor).
>
> So, that's 2:2 for cur.edit starting a transaction. My main complaint
> was
> that the docs are somewhat vague about the whole issue - i hope my
> point is
> made.
>
> Maximilian Tyrtania | <email address removed>
> fischerAppelt Kommunikation GmbH
> Tucholskystraße 18 | D-10117 Berlin | Germany
> Tel. ++49-30-726146-728 | Fax ++49-30-726146-10
> Mobil 0173-6104345 | www.fischerAppelt.de
> --
>
> ---
> Subscribe to the digest:
> <mailto:<email address removed>>
> Unsubscribe:
> <mailto:<email address removed>>

---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>

Transaction concept
Date: 08.05.02 06:14 (Wed, 8 May 2002 07:14:42 +0200)
From: Jule Epp/Max Tyrtania
Norman Palardy wrote:

>Well you could manually issue the start transactions via
>sqlexecute("start transaction")
>but then IF cur.edit is working the way it should and issuing an start
>transaction you'd have issues
>as you'd now have nested transactions that could be hard to commit or
>rollback

No. In Openbase there can only be one transaction per database connection.
So, if i started a transaction manually via
sqlexecute("start transaction") AND said cur.edit AND did an
update/insert/delete i'd get some "can't start a transaction inside a
transaction error". There is also nothing inherently wrong with the way
dbc.edit works. One just has to be aware of its asymmetrical behaviour - a
transaction is started once you call dbc.edit (now the record(s) are locked
only) AND do an update/insert/delete and then immediately again if you
commit or rollback. The scope of the transaction is the cursor (can be more
than one). All of this is slightly odd, but wouldn't be such a mess if it
was properly documented.

>This is easy to deal with by NOT using cur.edit but instead actually
>issuing UPDATE sql statements (messy bbut then you do have low level
>control)

I'd consider abandoning RB's database API altogether (Dr. Gerald Hammond
also recommended this), but I am working with pictures and documents. What's
the SQL for updating a picture field? I'd also love to know if this silly
limitation, that says "don't insert blobs into new records or else you'll
corrupt your data" is still valid with the new plugin v2.1.0.
Brad?

Best,

Maximilian Tyrtania



---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>

Re: Transaction concept
Date: 08.05.02 16:02 (Wed, 8 May 2002 09:02:46 -0600)
From: Norman Palardy

On Tuesday, May 7, 2002, at 11:14 PM, Jule Epp/Max Tyrtania wrote:

> Norman Palardy wrote:
>
>> Well you could manually issue the start transactions via
>> sqlexecute("start transaction")
>> but then IF cur.edit is working the way it should and issuing an start
>> transaction you'd have issues
>> as you'd now have nested transactions that could be hard to commit or
>> rollback
>
> No. In Openbase there can only be one transaction per database
> connection.
> So, if i started a transaction manually via
> sqlexecute("start transaction") AND said cur.edit AND did an
> update/insert/delete i'd get some "can't start a transaction inside a
> transaction error". There is also nothing inherently wrong with the way
> dbc.edit works. One just has to be aware of its asymmetrical
> behaviour - a
> transaction is started once you call dbc.edit (now the record(s) are
> locked
> only) AND do an update/insert/delete and then immediately again if you
> commit or rollback. The scope of the transaction is the cursor (can be
> more
> than one). All of this is slightly odd, but wouldn't be such a mess if
> it
> was properly documented.

In that regard Openbase is different than others I have used (Sybase &
Oracle) which Do permit nested transactions
In this case you could end up with a transaction that, upon terminating
your edits, is uncomiited with the sequence you've described

I'd still contend that a transaction should apply o the cursor, not the
database, regardless of what the underlying database does or does not
allow. Especially in a multi user database like Openbase, because if you
do q query, and someone else does a query that has the same record set
after you've edited on or two records, they should get everything that
has been committed.

>> This is easy to deal with by NOT using cur.edit but instead actually
>> issuing UPDATE sql statements (messy bbut then you do have low level
>> control)
>
> I'd consider abandoning RB's database API altogether (Dr. Gerald Hammond
> also recommended this), but I am working with pictures and documents.
> What's
> the SQL for updating a picture field? I'd also love to know if this
> silly
> limitation, that says "don't insert blobs into new records or else
> you'll
> corrupt your data" is still valid with the new plugin v2.1.0.
> Brad?
>
Hmmmm .... not sure but since I am working on a system to store JPEG's
in the database I'll have a go at this
Right now I don't see why I can't update the column I am using to do this

I do insert these with new records and so far have not found any issues

I'll try the updates tonight and let you know my results

---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>

Re: Transaction concept
Date: 08.05.02 17:03 (Wed, 08 May 2002 18:03:42 +0200)
From: Maximilian Tyrtania
am 08.05.2002 17:02 Uhr schrieb Norman Palardy unter
<email address removed>:

>>I'd also love to know if this
>> silly
>> limitation, that says "don't insert blobs into new records or else
>> you'll
>> corrupt your data" is still valid with the new plugin v2.1.0.
>> Brad?
>>
>>
> Hmmmm .... not sure but since I am working on a system to store JPEG's
> in the database I'll have a go at this
> Right now I don't see why I can't update the column I am using to do this

Seems to be an Openbase issue only. See the RB manual ->OpenOpendatabase
>
> I do insert these with new records and so far have not found any issues
>
> I'll try the updates tonight and let you know my results

If you happen to use openbase, I'd be interested. I AM having troubles with
my data, but I am not sure if it's related to that.

Maximilian Tyrtania | <email address removed>
fischerAppelt Kommunikation GmbH
Tucholskystraße 18 | D-10117 Berlin | Germany
Tel. ++49-30-726146-728 | Fax ++49-30-726146-10
Mobil 0173-6104345 | www.fischerAppelt.de
-

Re: Transaction concept
Date: 09.05.02 04:59 (Wed, 8 May 2002 21:59:31 -0600)
From: Norman Palardy

On Wednesday, May 8, 2002, at 10:03 AM, Maximilian Tyrtania wrote:

> am 08.05.2002 17:02 Uhr schrieb Norman Palardy unter
> <email address removed>:
>
>>
>> I do insert these with new records and so far have not found any issues
>>
>> I'll try the updates tonight and let you know my results
>
> If you happen to use openbase, I'd be interested. I AM having troubles
> with
> my data, but I am not sure if it's related to that.
>

a quick and dirty test suggest that yes I can update object columns with
the jpeg data I am using as long as the select gets the _rowid as well

I did this numerous times, but this is not an exhaustive test of
everything I need to do (yet)

Will try and keep you posted

---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>