How to change strings when iterating through them
I have a loop iterating through a table that looks like this (simplifying error handling to improve readability):
for rows.Next() {
var (
id int
field2 int
field1 int
)
err = rows.Scan(&id, &field1, &field2)
chk(err)
field1 += someFunc()
field2 += someOtherFunc()
err = db.Exec(`UPDATE table SET field1 = ?, field2 = ? WHERE id = ?`, field1, field2, id)
chk(err)
}
As you can see, I want to scan the fields of each row, modify them in some way, and update the database. The values โโreturned by someFunc
and someOtherFunc
are different for each row and cannot be determined from the values โโof its fields alone.
This piece of code doesn't work because the database is locked until rows
closed (I am using the mattn go-sqlite3 driver ). Also, it is inefficient because the database must execute the query every time a row is updated. I know that I could use db.Prepare
and then execute all queries as soon as I am done, but that will consume unnecessary memory and will not alleviate the efficiency problem. I've done some reading and it seems that cursors provide the functionality I'm looking for (I'm not a SQL expert) and the flavor of SQL I'm using (SQLite3) supports them but database/sql
doesn't look like.
Is there any natural way to database/sql
update rows by iterating through them?
You have to do your SELECT and UPDATE for the transaction, not the handle. I came across this in one of my projects and now I have this code:
tx, err := dbh.Begin()
...
rows1, err := tx.Query("SELECT id, data FROM cache")
...
for rows1.Next() {
...
tx.Exec("DELETE FROM cache WHERE id = ?", id)
...
}
tx.Commit()
And no more locked database errors.
source to share