Question about Newer SQLite

Targets Web
1

In the past with sqlite, when updating a record I would use a long concatenated string to build the update statement. For example below. But I ran into the edit row where I can just select the appropriate row and then update a column and then save the row. Question is do I need to Open a transaction before this, and using edit row do I need to commit the transaction before I close the database? Currently during certain situation I open a transaction and then i commit the transaction. The documentation does not say whether the transaction needs commited after the save row. Heres old code:

sSQL = "UPDATE UserRegistrations SET "
sSQL=sSQL+"dbUserLoginID = '"+ ScrambleString(Session.Username) + "', "
sSQL=sSQL+"dbUserPassword = '"+ ScrambleString(Session.UserPassword) + "', "
sSQL=sSQL+"dbUserFirstName = '"+ ScrambleString(Session.UserFirstName) + "', "
sSQL=sSQL+"dbUserLastName = '"+ ScrambleString(Session.UserLastName) + "', "
sSQL=sSQL+"dbUserMobilePhone = '"+ ScrambleString(Session.UserMobilePhone) + "', "
sSQL=sSQL+"dbUserPrimaryPhone = '"+ ScrambleString(Session.UserPrimaryPhone) + "', "
sSQL=sSQL+"dbUserEmailAddress = '"+ ScrambleString(Session.UserEmailAddress) + "', "
'sSQL=sSQL+"dbUserPhotoName = "+ Session.UserPhoto + ", "
sSQL=sSQL+"dbUserJobTitle = '"+ ScrambleString(Session.UserJobTitle) + "', "
sSQL=sSQL+"dbUserDatePWSet = '"+ ScrambleString(Session.UserDatePWSet) + "', "
sSQL=sSQL+"dbUserPasswordHint = '"+ ScrambleString(Session.UserPasswordHint) + "' "
sSQL=sSQL+"WHERE dbID = "+Str(Session.UserRegistrationDBID)+" "

and here is the new way from the documentation. I like the new way much better as it seems like it would be less error prone.

Try
  rs.EditRow
  rs.Column("ProductName").StringValue = "Generic Widgets"
  rs.SaveRow
Catch error As DatabaseException
  MsgBox("DB Error: " + error.Message)
End Try

Thanks for help in advance.

2

a transaction / commit or rollback is used if you edit multiple records, as example you will write a invoice and you need it completely and not half if a error happened.

https://documentation.xojo.com/api/deprecated/deprecated_class_members/database.rollback.htmlTransaction

you should test the behavior at xojo.
at postgre i must use a commit after SaveRow or nothing happened
and it is also xojo version-dependent.

3

IMO the new way is to use prepared statements which are now built into methods such as SelectSQL or ExecuteSQL. Thus:


sSQL = "UPDATE UserRegistrations SET dbUserLoginID = ?1, dbUserPassword = ?2 WHERE dbID = ?3"
db.ExecuteSQL (sSQL, ScrambleString(Session.Username), ScrambleString(Session.UserPassword), Session.UserRegistrationDBID)

.
with no need for single quotes, or converting to string. And no chance of an SQL injection attack.

In SQLite, a single SQL statement doesn’t need transaction treatment, since SQLite itself will automatically wrap each such in a transaction, unless a transaction is already open.

The only place I use transactions is when I know I’ll be looping doing lots of updates. This only happens in a few places in my code, in particular where I have extracted a number of spam tokens from a text and wish to add them all individually to a database. Then I’d do:


db.ExecuteSQL ("BEGIN TRANSACTION")

// here is my loop doing updates

dbExecuteSQL ("COMMIT")

.
I’ve never tried this editrow/saverow stuff as you anyway have to do a select beforehand to have a row to edit. Also it’s Xojo wrapping stuff around an ordinary SQL UPDATE statement and you don’t know what they are actually doing.

1 Like
4

I’ve ran into many problems with editrow.(depends of the database engine)
you need a primary key and it’s not always the case.
I’m now using sql queries “update … set …” with no problem .

5

ExecuteSQL is API2
You can use

db.BeginTransaction

// here is my loop doing updates

db.CommitTransaction

And in case of rollback

db.RollbackTransaction
6

I know I can … but I never do.