PostgreSQL Update Request

Databases
1

Hi,

I would be grateful if you could update the PostgreSQL plugin to add the possibility to Move through the recordset, especially the ability to move to the first record so it would be possible to re-use a recordset instead of having to store it in an array or a SQLite recordset…

Thanks!

2

FYI making the request here is not useful
It needs to be in feedback
<https://xojo.com/issue/22656>

3

OK, Thanks!

4

if its not in feedback it might as well not exist - its like wishing for a unicorn
no one from Xojo has the job of scouring the forums looking for requests, bug reports, etc
if they happen to find one & create a report for it its a gift of sorts

5

[quote=443887:@Roger St-Arneault]Hi,

I would be grateful if you could update the PostgreSQL plugin to add the possibility to Move through the recordset, especially the ability to move to the first record so it would be possible to re-use a recordset instead of having to store it in an array or a SQLite recordset…

Thanks![/quote]

Probably a blessing in disguise! You can end up with a very server extensive application if you are not carefully when you start working with cursors.

6

Hi James,

I was not aware of that… What do you mean exactly? It is better to send the same query twice than to keep the recordset, and moving to the first record?

7

It is a big topic, but generally speaking the pattern when working with a database server as opposed to a local database such as SQLite is:

  • select records, load data into your app and close the record set
  • user edits the data
  • execute prepared statement to update record, while at the same time ensuring that the record has not been changed by another user

Two things to think about in your design:

  • What to do if the record changes while the user is working with it
  • What to do if someone else has the record locked when you try to update it
8

Use optimistic updating - basically you do NOT lock a record and when you go to update you compare your original values to those that exist now. If they do not match then you ask the user what to do - quite possibly restart the edit

locks are loathsome

[quote=443939:@James Dooley]

  • What to do if someone else has the record locked when you try to update it[/quote]
  1. never use record locking
  2. when you think you need record locking see rule #1

hard locks on records should be avoided as its entirely possible for a single lock on a row to lock up things to the point no one else can work depending on the db’s locking strategy (ie/ when it uses table level locks - Sybase and MS SQL used to do this. Dont know if they still do)

locks should be short in duration and are not, IMHO, a concurrency strategy

9

Record locking is a royal pain. Have a client that insists upon it and they had to have ways of bypassing locks because of people walking away from their computer, and multiple other issues. Needless to say it’s their biggest headache in the entire app.

Optimistic record locking is SO much simpler. All you need is LastModifiedDate and at save you compare what the value currently is to what you had when you first opened the record. If it’s different then someone else changed the record on your and you present the user a dialog telling them what’s happening.

If you think you need pessimistic record locking you probably don’t.

10

I currently have a software locking mechanism on my apps.
a table, with tablename, recid, userid, where I create a record when the user opens a record.
and delete the record when he ends editing.
if this record already exists (I check with an unique index in it) , I open the record on the other client in readonly,
and tells the user “already opened by user xxxx”
so far works nice, is easily bypass from an admin user if needed.