RS.Update on PostgreSQL not working

Databases
1

Hi,

is there any difference when handling a recordset update with MySQL or PostgreSQL?

I use this code:

    Dim sqlQuery As String = "SELECT * FROM customers WHERE id = " + Listbox1.RowTag(Listbox1.ListIndex)
    Dim rs As RecordSet
    
    if SqlDBMS = "MySQL" then
      
      OpenMySqlDB
      
      if DBError then return
      
      rs = MySqlDB.SQLSelect(sqlQuery)
      
    elseif SqlDBMS = "Postgres" then
      
      OpenPostgresDB
      
      if DBError then return
      
      rs = PostgresDB.SQLSelect(sqlQuery)
      
    end if
    
  end if
  
  rs.Edit
  
  rs.Field("customernumber").StringValue = txtCustomernumber.Text
  rs.Field("company").StringValue = txtCompany.Text
  rs.Field("telephone").StringValue = txtTelephone.Text
  rs.Field("telefax").StringValue = txtTelefax.Text
  rs.Field("email").StringValue = txtEmail.Text
  rs.Field("internet").StringValue = txtInternet.Text
  rs.Field("street").StringValue = txtStreet.Text
  rs.Field("streetnumber").StringValue = txtStreetnumber.Text
  rs.Field("postcode").StringValue = txtPostcode.Text
  rs.Field("city").StringValue = txtCity.Text
  rs.Field("country").StringValue = txtCountry.Text
  
  rs.Update

When using MySQL, all is fine, the Record is updated. When using PostgreSQL the update is not made, but i also get no errormessage.

Any ideas?

2

Just a thought: Try issuing rs.Commit after the Update.

3

Also, is there a primary key defined in the PostgreSQL table?

4

Oh yeah, that’s bitten me too. Having a field that acts like a primary isn’t enough, it must be designated “PRIMARY”.

5

Kem was right, the commit was missing. Works now. And yes, there is a primary key.

Thanks.

6

I think this is a bug in the Postgres driver, and one that has a Feedback report. It starts a transaction but never commits it.

7

Memory served. See:

<https://xojo.com/issue/35835>

8

I have the same problem … also calling the commit doesn’t update the recordset :frowning:

9

I prefer updating using sql-update-queries / prepared statements, written for the database engine you work with.
Think it’s a stronger method and you as you work with transactions have probably more control.

10

yes but xojo give us merthods to simplify the work like recordsets and they should work updating a table on postgresql

the following code

rs = DB.SQLSelect(“Select * from users where id=3”)

rs.edit
rs.Field(“name”).Value=txtnominativo.text
rs.Field(“user”).Value=txtutente.text
rs.Field(“password”).Value=txtpassword.text
rs.Field(“suspended”).Value=chksospeso.Value
rs.update
DB.commit

works only updating a only a field each time