Is there a way to avoid selecting the primary key and to have the update work?
I don’t want to bother but there was a case with clients who thought they had saved something which was never saved because of that.
Is it also possible to avoid the instruction “db.SQLExecute(“BEGIN TRANSACTION”)” but to have the same results. Maybe I’m stupid but I always forget those few words or I confuse them with the mysql instruction.
I’m just thinking about the REAL plugin and I thought maybe there is a way…
yes in your select statement, select the columns you want.
you dont have to begin transaction/commit. It is a smart thing to do when dealing with updates/inserts especially if there is multiple changes at once.
I’m a bit confused on what you are actually doing that is the problem.
Are you saying that you want to avoid an
db.sqlexecute("update MyTableName set myfield='new value' where primary_key=123456")
If that’s the case, you can effectively address a unique record using another field, or set of fields but using the primary key is the recommended method. This is possible but not recommended:
db.sqlexecute("update MyTableName set myfield='new value' where field1='abc' and mydatefield='2014-05-22' and fieldtype='donuts')
For the “BEGIN TRANSACTION” issue, I have created a database class that does the connection handling, transaction handling and error handling for me. I’d recommend looking into creating a database class to use for all future Xojo applications.
This will only work if the other fields you use in the SQL where clause give you a unique record. If you can’t guarantee that then the primary key is the only way to do it.
You can create your own database classes to do this, as Kevin suggested. Our ActiveRecord classes have that built in but it might be overkill for what you’re attempting. AR can be found at http://www.bkeeney.com/rbinto/activerecord/.
[quote=90568:@Payam Arzani]But Xojo could perhaps recognize rs.Edit and rs.Update, and prevent being buggy in this case, no?
[/quote]
No. I don’t think you appreciate the complexity of the problem.
[quote]It could also recognize db.Commit, for example, and tell itself implicitly START TRANSACTION.
[/quote]
I think that would be a very bad idea.
But Xojo could perhaps recognize rs.Edit and rs.Update, and prevent being buggy in this case, no?
[/quote]
Is was trying to be helpful & it caused certain entire classes of queries to just fail
Its truthfully better for you to know whats going on and why it works or doesn’t
I humbly submit that rs.edit and rs.update are pretty solid. However, how your implementation may not be using them properly.
A couple of suggestions:
Always check the database.error bit after every db operation (SQL selects, executes, database schema, field schema, etc).
Always use auto increment primary keys in EVERY table and always bring them back as part of the query
See #1. usually the biggest cause of logic errors in Xojo code.
One thing that many people don’t know is that Xojo does not throw a database exception for you automatically. You must check the error bit after every operation. Hence rules #1 and #3.
[quote=90576:@Bob Keeney]I humbly submit that rs.edit and rs.update are pretty solid. However, how your implementation may not be using them properly.
A couple of suggestions:
Always check the database.error bit after every db operation (SQL selects, executes, database schema, field schema, etc).
Always use auto increment primary keys in EVERY table and always bring them back as part of the query
See #1. usually the biggest cause of logic errors in Xojo code.
One thing that many people don’t know is that Xojo does not throw a database exception for you automatically. You must check the error bit after every operation. Hence rules #1 and #3. :)[/quote]
Thanks but I mean the plugin was trying to be helpful
The old real sql plugin literally tried to help you by altering your sql query to make sure you included the primary key
This wasn’t a problem as long as you only ever did
select from table .
type queries
it could alter this to
select rowid, from table .
and it would hide the fact it had done this and things were happy
However, as soon as you tried to do something like
select from table .
union
select from table .
the complexity of rewriting that query correctly goes way way up
And this small example is the extremely “simple case”
When you have nested subqueries etc and all the things sql allows you need a full sql parser to be able to do it even close to right
And even if you CAN rewrite this correctly you probably cannot actually use an edit/update with it because the result is a UNION so which table would you update ?
And the problems simply go on from there.
So the BEST course of action was to NOT try and do that and just tell people to include the primary key and edit / update would work as expected AND the plugin would NOT mangle more complex queries trying to rewrite them
