UPDATE (SQL related)

Databases
1

[code] ’ ## UPDATE BATCH :: SCORE + EST. TIME
DIM sql as String
sql = "SELECT intID FROM [KATbatch] WHERE intID = " + str(intLastID) + “;”
dim rs2 as RecordSet = dbDatabase.SQLSelect(sql)

    rs2.Edit
    'rs2.Field("intTotalScore") = intTotalScore
    rs2.Field("intTotalScore").IntegerValue = intTotalScore
    rs2.Update
    
    dbDatabase.Commit[/code]

I think I’m doing wrong when updating a post in the database.
Why not simply use the UPDATE command within SQL!?

I don’t love recordsets… I rather use simple SQL and the efficient EXECUTE command. For me, my eyes and my sole it’s less confusing and for this reason a better choice.

2

You can use the Execute command instead. I do all the time :slight_smile:
Preferably a PreparedStatement if the input data is altered by anyone else but you.

3

You can certainly use an SQL Update statement if you wish…If you use the recordset, you should test to make sure that the recordset is valid before issuing rs2.edit. Also, after issuing rs2.update, you should check to see if you have a database error before issuing a commit…

4

I’d do it something like this (if using postgres)

Pseudo code:

Dim sql as String
Dim ps as PostgreSQLPreparedStatement

sql = ”UPDATE KATbatch SET intTotalScore=$1 WHERE intID=$2”
ps = PostgreSQLPreparedStatement(dbDatabase.Prepare(sql))
ps.Bind(0, intTotalScore)
ps.Bind(1, intLastID)
ps.SQLExecute

And then all the error checking and stuff.

5

Can’t emphasize this enough from recent experience. Also, with the prepared statements, you don’t have to go all crazy with escaping out the apostrophes, quotes, etc. (all them little buggars that screw up an UPDATE transaction)

6

Exactly. I tend to use PPS even if I’m the only one controlling the input data :stuck_out_tongue_winking_eye:
And using Postgres you don’t even have to define the BindType! That’s sweet!

7

You must read it before you can update it.

select intID, intTotalScore from …

8

[quote=120991:@Tim Hare]You must read it before you can update it.

select intID, intTotalScore from …[/quote]

[code] ’ ## UPDATE BATCH :: SCORE + EST. TIME
DIM sql as String
sql = "SELECT intID, intTotalScore FROM [KATbatch] WHERE intID = " + str(intLastID) + “;”
dim rs2 as RecordSet = dbDatabase.SQLSelect(sql)

    rs2.Edit
    rs2.Field("intTotalScore").IntegerValue = intTotalScore
    rs2.Update[/code]

Thank you!
“It’s easy when you know how!”

9

Can you please change the title of this thread? I keep thinking it is an official statement from Xojo, regarding a new release :slight_smile:

10

Excuse me!
I hate bad naming of threads…

It’s in the Database- section. It may not be clear enough! :slight_smile:
Respect!