I am working on a method to update a single field in one or more records. The number of records will most often be a few, but it could be a great many - tens or hundreds. I am using a prepared statement, and am wondering whether I should I repeatedly execute something like this (in pseudo code):
repeat from 1 to numRecordIDs
prepare the statement("UPDATE myTable SET myField=? WHERE id=?")
execute the statement
next
or, should I do it with one statement:
UPDATE myTable SET myField=? WHERE (id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=? OR id=?...)
Is there a limit on the number of placeholders that the prepared statement can resolve? (I’m assembling the SQL string programmatically, so I don’t care how long it is, but am guessing the database engine might.)
What are the implications of doing it the second way if there are a lot of records to update?
(BTW, we are using cubeSQL, so it’s got its own prepared statement class which not a subclass of Xojo’s PreparedSQLStatement. I plan to ask Marco this question as well as posing it here.)
Thank you for the suggestions, Norman. As for your first suggestion, I couldn’t seem to get that to work with the cubeSQL prepared statement. I would end up with a nilObjectException on the second time through the loop. in fact, as I recall, I actually had to instantiate a NEW instance of the class every time through the loop in order to get it to work.
I had forgotten about in . That’s a good idea. As I mentioned, I am already constructing the statements in my database access routines dynamically, so this is not a problem.
Is there a limit, either theoretically or practically, on the number of “?” parameters that can be in a single prepared statement? (I know you might not be able to answer specifically for Marco’s plug-in, but how about for the Xojo preparedSQLstatement subclasses?)
I also like the temporary table idea; it may not be worth it in this case, but if not, I’ll keep it in my hip pocket for other cases in this project.
[quote=39046:@Peter Truskier]Thank you for the suggestions, Norman. As for your first suggestion, I couldn’t seem to get that to work with the cubeSQL prepared statement. I would end up with a nilObjectException on the second time through the loop. in fact, as I recall, I actually had to instantiate a NEW instance of the class every time through the loop in order to get it to work.
[/quote]
That kind of defeats the purpose of a prepared statement.
I’m not aware of any limit on the number of parameters for a prepared stmt BUT I doubt I’d count on it being infinite
The temp table with select in is an old trick I used to use on db’s long ago
Best part about some db’s is they keep temp tables with the same name unique per login so regardless of how many users you had you never had to worry about collisions in temp tables. Your login could have a temp table named “foo” and mine could also but they were unique tables.
Well, at least one purpose, though it still offers the advantage of not having to worry about sanitizing user-entered values, etc. But i do appreciate what you’re saying> I’m asking Marco about that too
Just to be safe, if I do use the temp table approach, I think I’lll generate a GUID to avoid collisions - sounds like a nasty bug to run into at runtime…
The point of a prepared statement is two fold - or should be
you don’t have to sanitize user values
the “query” is precompiled so it does not have to be recompiled every time just to use new values
The way this is it gets rid of the value of point 2 it would seem
As for generating unique table names that probably safest since sqlite really doesn’t have the notion of a login or seperating things like temp tables by login which is how this works in things like Sybase etc.
