PostgreSQL recordset update bug?

[Andrea_Dalseno]

Hi, I have to Recordsets (rdest -> destination and rorig-> source) from a PostgreSQL database and I was trying to copy each record from the source to the destination with this simple loop (inside a loop to iterate through the source recordset):

rdest.Edit // Field 1 is the primary key and is, by definition, equal between the two recordsets so no need to update For i As Integer = 2 To rorig.FieldCount rdest.IdxField(i).Value= rorig.IdxField(i).value Next rdest.Update
The code works, but if I compare the two tables after running the program I got differences since it looks like some fields (empty?) in the source become NULL in the destination (but, IMHO, they should not).
If I make a copy from inside the database with a SQL Update everything works fine, so it’s a problem with XOJO.
Any idea? Am I doing something wrong?

[Neil_Burkholder]

You would be better of using an SQL statement.

[Norman_P]

@325156:@Neil Burkholder

You would be better of using an SQL statement.

And a sql statement would be 1 line along the lines of

   insert into table ( select from othertable) 

and since its ALL in the db would be a LOT faster too

https://www.postgresql.org/docs/9.6/static/dml-insert.html
https://www.postgresql.org/docs/9.6/static/sql-insert.html

[Neil_Burkholder]

[quote=325159:@Norman Palardy]And a sql statement would be 1 line along the lines of

   insert into table ( select from othertable) 

[/quote]

Oh. I thought maybe something like this:

UPDATE table2 
SET field1 = table1.field1,
    field2 = table1.field2 
FROM table1
WHERE table1.id = table2.id
AND table2.id = 1

And yes, much faster.

[Norman_P]

you could do that
or an “upsert” which is an “insert or update” depending

[Andrea_Dalseno]

That’s what I did actually (I know SQL pretty well), an update statement (a single long update statement since there were a lot of fields, so writing the statement has been tedious). The insert method would not work since the destination table is already filled with data that just need to be updated (and cannot be deleted). SQL is faster for sure but what I wonder is why the code

rdest.IdxField(i).Value= rorig.IdxField(i).value

that is supposed to copy a record field by field actually fails! Is it a bug?

[Neil_Burkholder]

I would start with something like this: (I didn’t test it. Just typed it into the browser.)

rdest.Edit
if rdest.error then msgbox "Error editing record: " + rdest.errormessage
// Field 1 is the primary key and is, by definition, equal between the two recordsets so no need to update
For i As Integer = 2 To rorig.FieldCount
  dim temp as string = rorig.IdxField(i).value
  rdest.IdxField(i).Value = temp 'put a break point here and see if temp contains the expected value.
Next
rdest.Update
if rdest.error then msgbox "Error updating record: " + rdest.errormessage

[Neil_Burkholder]

@325202:@Andrea Dalseno

…just need to be updated (and cannot be deleted).

Curious why it can’t be deleted and reinserted? Are some of the fields not the same?

You could write an update method similar to my PutInDB method. It’s for inserting not updating but you could use the same idea for updating. I also have one that extends SQLiteDatabase. I use it a lot for bringing a recordset into a temporary in memory table for fast access. This method below then takes a recordset and writes the records out to the PostgreSQL database.

Function PutInDB(extends db as PostgreSQLDatabase, Table as String, rs as RecordSet, Delete as Boolean = False, ColNames as String = “”) As string
'delete all record
db.SQLExecute(“BEGIN TRANSACTION”)
if db <> nil and Delete Then db.SQLExecute("DELETE FROM " + Table)
if db.Error Then
db.Rollback
Return db.ErrorMessage
end if
if rs <> Nil and rs.RecordCount> 0 Then

'prepare the insert statment
dim Values, sql as String
dim Temp(), Temp1() as String
if ColNames = "" Then
  for i as Integer = 1 to rs.FieldCount
    temp.Append rs.IdxField(i).Name
    temp1.Append "?"
  next
  ColNames = Join(Temp,",")
Else
  temp = Split(ColNames,",")
  temp1 = Split(ColNames,",")
  for i as Integer = 0 to temp1.Ubound
    temp1(i) = "?"
  next
end if
Values = Join(Temp1,",")
'sql = "INSERT INTO " + Table + "(" + ColNames + ") VALUES(" + Values + ")"

'add the records
dim recs() as DatabaseRecord
do
  dim data as new DatabaseRecord
  for i as Integer = 1 to rs.FieldCount
    dim col as String = rs.IdxField(i).Name
    if temp(i-1) = "User" then temp(i-1) = "UserName"
    select case VarType(rs.IdxField(i).Value)
    case 0
    case else
      data.Column(temp(i-1)) = rs.IdxField(i).StringValue
    end select
  next
  recs.Append data
  rs.MoveNext
loop until rs.EOF

for each rec as DatabaseRecord in recs
  db.InsertRecord(Table, rec)
  if db.Error Then
    Return db.ErrorMessage
  end if
next
db.Commit
if db.Error Then
  Return db.ErrorMessage
end if
Return "ok"

end if

db.Commit
Return “ok”

End Function

[Kem_Tekinay]

What happens if you use almost the same code to build a prepared Update statement instead?

[Neil_Burkholder]

I didn’t try it but it should just work.

destDB.UpdateRecord(table, rs)

Function UpdateRecord(extends db as PostgreSQLDatabase, Table as String,  rs as RecordSet, Key as String = "ID", ColNames as String = "") As string
'...........

End Function

[Maximilian_Tyrtania]

Modern versions of Postgres actually support upserting, see the ON CONFLICT clause at https://www.postgresql.org/docs/9.5/static/sql-insert.html .
FWIW, I work with Xojo and PG since 10 years and never used recordset.edit+update .

[Tim_Hare]

Even though you’re using a Variant (DatabaseField.Value), there is likely one or more implicit conversions happening. It’s usually best to make those conversions explicit, so you can control the edge cases.

[Tim_Hare]

And I, too, have been building database centric apps in Xojo for more than 10 years, and I never use Recordset.Update. It’s great for quick and dirty work, but not for serious use.