Postgres DatabaseUpdate() bug?

[tua]

I believe both methods should work (at least according to the documentation), but one does not.
Any PostgreSQL user to confirm/debunk this?

Code: Select all

UsePostgreSQLDatabase()

EnableExplicit

Global ConnStr.s = "host=localhost port=5432 dbname=folders application_name=MyApp"
Global DBHandle  = OpenDatabase(#PB_Any, ConnStr, "my_user", "my_password")

If Not DBHandle
  Debug "Open DB failed"
  End(-1)
EndIf  

Define columnname.s = "vdatehi", 
       value = RGB(25, 100, 120),
       visitid = 66844


; this does not work???
SetDatabaseString(DBHandle, 0, columnname)
SetDatabaseLong(DBHandle,   1, value)
SetDatabaseLong(DBHandle,   2, visitid)

If DatabaseUpdate(DBHandle, "update visits set $1 = $2 where id = $3")
  Debug "Update OK"
Else  
  Debug "Update failed"
EndIf

; this works just fine
If DatabaseUpdate(DBHandle, "update visits set " + columnname + " = " + Str(value) + " where id = " + Str(visitid))
  Debug "Update OK"
Else  
  Debug "Update failed"
EndIf

[Marc56us]

Hi,
Add DatabaseError() to see full SQL error message

Code: Select all

If DatabaseUpdate(DBHandle, "update visits set $1 = $2 where id = $3")
  Debug "Update OK"
Else  
    Debug "Update failed"
    Debug DatabaseError()
EndIf

[tua]

Oops - I had that in there, but accidentally removed it when stripping my code down before posting ...

ERROR: syntax error at or near "$1"
LINE 1: update visits set $1 = $2 where id = $3

And that's why I am assuming it's a bug

[infratec]

I'm not sure if you can use a parameter as columnname.

Try this:

Code: Select all

update visits set vdatehi = $2 where id = $3"

[tua]

Hmm, that does work - thanks for the suggestion. Too bad

Now off to study the Postgres documentation ...

[Marc56us]

Solution from infratec works for my testing.
(you can't pass column name as statement)