RecordSet.Edit will not Update the Table Unless it is ran Twice

Hi Everyone,

So here is the code that i am using to update values in a table. I am using Postgre as the database (and i looked up the RecordSet rule and the rules state that the only RecordSet method that works for Postgre is the MoveNext method (RecordSet Rules)). Anyways the code below does in fact work, but only if i run it 2 times, it does not work on the first try. any ideas why this is (or ways around it)?

[code] lngAdjId = lngAdjustId
lngDlrAdjCode =ctlList.Cell(x,0).Val
strMasDlr = ctlList.Cell(x,1)
dblAmt = ctlList.Cell(x,3).Val
dblTaxRt = ctlList.Cell(x,4).Val

Dim Rst As RecordSet = mDBS.SQLSelect("SELECT * FROM TableName WHERE PrimaryKey="+_
CStr(lngDlrAdjCode) + ";")
If Rst<>Nil Then
  If Rst.RecordCount>1 Then
    strMsg= "Error Updating Dealer Rate..." + EndOfLine+EndOfLine + "Multiple Entries Found For Dealer Rate # " +_
    CStr(lngDlrAdjCode) + "!"
    GoTo ErrorIT
  Else
    Rst.Edit
    Rst.Field("daamteach").DoubleValue=dblAmt
    Rst.Field("dataxrate").DoubleValue = dblTaxRt
    Rst.Update
    If mDBS.Error= True Then
      strMsg= "Error Updating Dealer Rate..." + EndOfLine + EndOfLine + mDBS.ErrorMessage
      GoTo ErrorIT
    End If
  End If
End If[/code]

Thanks!

I would use SQL and Prepared Statements instead of recordset Update

NOT using PreparedStatements (I will leave that as a learning exercise)

Dim Rst As RecordSet = mDBS.SQLSelect("SELECT * FROM TableName WHERE PrimaryKey="+ CStr(lngDlrAdjCode) + ";")
    If Rst<>Nil Then
      If Rst.RecordCount>1 Then
        strMsg= "Error Updating Dealer Rate..." + EndOfLine+EndOfLine + "Multiple Entries Found For Dealer Rate # " +_
        CStr(lngDlrAdjCode) + "!"
        GoTo ErrorIT
      Else
     mDBS.SQLExecute("UPDATE tablename SET dammteach="+str(dblamt)+","+dataxrate="+str(dbltaxrt)+" WHERE PrimaryKey="+ CStr(lngDlrAdjCode) + ";")
        If mDBS.Error= True Then
          strMsg= "Error Updating Dealer Rate..." + EndOfLine + EndOfLine + mDBS.ErrorMessage
          GoTo ErrorIT
        End If
      End If
    End If

use an UPDATE…SET sql command instead of the SELECT and then recordset.edit method.
it will be easier, faster if you have a lot of (remote) records, and works all the time even if the recordset does not contain the primary key.

[quote=373261:@Jean-Yves Pochez]use an UPDATE…SET sql command instead of the SELECT and then recordset.edit method.
it will be easier, faster if you have a lot of (remote) records, and works all the time even if the recordset does not contain the primary key.[/quote]
which is what I just said

Both of these answers I will consider for future coding projects. for now i did find the issue. in other parts of my project i am performing the same actions and in those actions, after the RecordSet.Update i had the statement Database.Commit which solved my problem.

which is what I just said in the other thread … :wink:

try this :

Rst.Edit Rst.Field("daamteach").DoubleValue=dblAmt Rst.Field("dataxrate").DoubleValue = dblTaxRt Rst.Update If mDBS.Error= True Then strMsg= "Error Updating Dealer Rate..." + EndOfLine + EndOfLine + mDBS.ErrorMessage GoTo ErrorIT else mDBS.commit End If

The way to go. I would advise to write some generic code to be used all over in your projects, so ylu don’t have to deal with complexity all the time.

Doesn’t recordset.Update use a prepared statement behind the scenes anyway?

They only thing I know is that recordset.update mostly doesn’t work smoothly while you haven\t much control.

also when you make mass batch updates, the UPDATE SET is far more faster then a loop on recordset.edit.
I dont use recordset.edit anymore.

#metoo

All these replies on avoiding RecordSet.Edit / Update and what to do instead may be good advice, but does not really it the point. To me the only issues known with RecordSet.Edit / Update for PostgreSQLDatabase are <https://xojo.com/issue/11597> and <https://xojo.com/issue/35835>. The former seems not to apply here, the latter means you have to .Commit the changes after one or more updates, as was already indicated by [quote=373297:@luciano monti]mDBS.commit[/quote]
But this does not explain, why you experienced running the Update twice does actually update the DB

Can we try to isolate that issue? How do you test if it ‘worked’? Do you have a Commit somewhere in the code before the Update?

yes

To Answer Tobias Bussmann, no i did not have the .commit anywhere in that branch of code. i am not sure why it would work the second runtime and not the first either. as far as how i was testing it, i had a pg database function that watched for changes in the table, one the first run - nothing was triggered, on the second run - the function returned a change - which you could see in the table.

I would recommend to build a module or own class that wraps every database command in a db.error check routine and only use these methods.
I did so recently on an older (working) project and was surprised (and ashamed) to find errors, sometimes simply due to field name typos, while the project was running before too.

Recordset.Edit and Update are wrappers for a transaction, so if they fail there must be something causing an error. Could be that an earlier transaction is still open. Checking for error before and after each database access will reveal the reason.

sure, this is a good advice, even better if you does subclass the stuff and throw Exceptions on Error.

Transaction as an abstract concept or as in SQL? The latter is definitely not the case (at least for PostgreSQLDatabase). I’d expect .Edit does an SELECT FOR UPDATE and .Update does UPDATE but that would either require an Transction to be open already or would need to stat one. (I’d rather like explicit control over them, and this is handled inconsitently across the Framework see <https://xojo.com/issue/14477> which has gotten better with the move from REALSQLDatabase to SQLiteDatabase at least - but I digress…) However what happens is that .Edit does not interact with the DB at all and .Update does an BEGIN TRANSACTION and UPDATE was prepared parametrised statement (For details on the diff, see: <https://xojo.com/issue/37805>). That is what <https://xojo.com/issue/35835> is about but does not explain the issue here.

that wouldn’t be an error but rather issue a WARNING which is not accessible through the Xojo DB interface. (FR: <https://xojo.com/issue/13158>)

But sure, testing for errors and trying to build a small self-containted demo should be the next steps for @James Rogers

Do you have a .Commit somewhere later in the code? You need to.

How do you “watch for changes”? With a Trigger on the Table? or with something that periodically polls it’s content? After the first run, before a commit, the changed value should be only visible to readers within the same DB Session.

How do you ‘see’ it? In a different DB Management application using a different session to the DB e.g. with psql’s \\watch?

I meant it as a concept. The implementation surely depends on the database engine, and I have no clue what goes on under the hood.
I did not know the FB case you mentioned, so maybe this approach will not give a clue. It surely won’t hurt at least. :wink:

I never use it.