Database Transactions

General
1

I am working on migrating to Xojo 2018 from Xojo 2013 and I am finding different behavior within a transaction.

In this case, I am making a change to a payment that is posted to an invoice.

In Xojo 2013, I can do something like this:

//SavePayment Method
db.SQLExecute("BEGIN TRANSACTION")

//remove the previously posted payment to the invoice using the UndoPayment_Method
UndoPayment_Method

//post new payment using the PostPayment_Method
PostPayment_Method

db.Commit
//UndoPayment_Method
dim rs_invoice as recordset = db.SQLSELECT("Select * from Invoices WHERE invnumber = 123")
If rs_invoice <> Nil then
  If Not(rs_invoice.EOF) then
      rs_invoice.edit
      rs_invoice.field("balance").value = rs_invoice.field("balance").value - vOldValue
      rs_invoice.update
   End if
End if

//PostPayment_Method
dim rs_invoice as recordset = db.SQLSELECT("Select * from Invoices WHERE invnumber = 123")
If rs_invoice <> Nil then
  If Not(rs_invoice.EOF) then
      rs_invoice.edit
      rs_invoice.field("balance").value = rs_invoice.field("balance").value + NewValue
      rs_invoice.update
   End if
End if

In Xojo 2013, I find that in the UndoPayment_Method, the balance of the invoice is changed. Then when I search for the invoice in the PostPayment_Method, the value set in the UndoPayment_Method is retrieved.

In Xojo 2018, I am finding that in the UndoPayment_Method, the balance of the invoice is changed. Then when I search for the invoice in the PostPayment_Method, the value pulled is the value before the transaction was started.

I am tracing the same code accessing the same Postgres v10 database, the only difference being Xojo 2013 and Xojo 2018 as far as I can tell. I am not sure why the behavior would be different between the two applications, but while inside a transaction, it appears Xojo 2013 is able to retrieve the changed value where Xojo 2018 pulls the unchanged value.

2

I can’t answer your question directly since we never use a RecordSet to edit, but I wonder why you aren’t doing this in a single UPDATE statement? You wouldn’t even need the transaction.

UPDATE
  Invoices
SET
  balance = balance - str( vOldValue ) + str( NewValue )
WHERE
  invnumber = 123

(Pseudo-code above)

3

Thanks. Yes, I should have done that long ago. I only wish it was as easy as the example.

In the meantime, is there some workaround that I am overlooking? Shouldn’t pulls from the database within a transaction reflect changes you have made within the transaction?

4

Yes. You’re positive you’re using the same db instance?

5

Pretty sure. I have an app property called app.mydatabase and each call to the database is via app.mydatabase.SQLSelect which should remain as a single connection across each method within the transaction.

6

Temporarily, copy the code from each method into the one calling method between the BEGIN and COMMIT and see what happens.

7

Thanks for the advice. I’ll let you know how it works out.

8

Here’s a little trick to minimize your code changes. Since variable declarations are scoped, you can create blocks that don’t clobber each other this way:

if true then
  dim rs as RecordSet = ...
end if

if true then
  dim rs as RecordSet = ...
end if
9

I would use a trigger (if your database allows it) to do such balance calculations, instead of on-the-fly record updates.
this would be automatically handled by the database engine, and would lead to less integrity errors.

10

OK, this turned out to be something quite interesting.

When I did the first SELECT statement I did not include the primary index, so Postgres could not confirm the uniqueness of the record when updated.

For some reason, Postgres did not complain.

Once I included the primary index, the second SELECT statement pulled the modified data (as it should when within a transaction).

Not sure why Xojo 2013 let me get away with this and Xojo 2018 did not.

11

for the xojo record.update to work, there must be the primary key in the recordset select query
otherwise you should get an error message (if you display them) as the update will not be possible.

12

(When No passed Unique ID) It does not udate all Records instead ?

13

[quote=389692:@Jean-Yves Pochez]for the xojo record.update to work, there must be the primary key in the recordset select query
otherwise you should get an error message (if you display them) as the update will not be possible.[/quote]
That is not true… as Emile said… no “WHERE” will update all records

Update myTable set xyz=3

affects every record in “myTable”

and fields referenced in the Where clause do not have to be members of the Primary Key, nor is that table “required” to have one

14

I spoke about the xojo recordset.update method
from the LR : http://developer.xojo.com/recordset

[quote]Some databases do not allow you to update if the SQL statement used to get the RecordSet contains more than one table. Additionally, some databases may require that the SQL statement specifically include the Primary Key for the table.
[/quote]
and also (which I follow even if it’s not mysql …)

[quote]MySQL cannot guarantee the contents of a RecordSet after issuing an Update call (after having previously called Edit). This means you should not try to modify the contents of a RecordSet in a loop. Instead select just the single record you wish to modify.
[/quote]

15

Personally I only use recordsets to read data, any inserts or update I always do using pure SQL statements,