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)?
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]
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.
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?
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 wont hurt at least.