I’m a total noob playing with Xojo for a week or so. I’m hipper to databases than I am to Xojo, so I didn’t expect to have so much of a problem with this, as in PHP (and a few other web languages) prepared statements give you a few options. I am told by the Xojo docs that SQLite named parameters are supported in prepared statements. A gentleman on the Database forum suggested otherwise, which is my experience thus far. Another gentleman wrote a source code generator which uses named parameters and, in place of a bind function, something called “sql.parameters(fieldname)”, about which I can find nothing. Does anybody know what the score is on this? And, if there is example code available, please point me in that general direction.
While I’m on the subject, the SQLite docs do not advise using autoincrement on a primary key field definition, citing overhead. They also mention (inscrutably, I think) that the invisible field (or property) ‘rowid’ is ‘aliased’ by the primary key, which, it must be assumed, inherits an autoincremented value. In my experience, this does not seem to be the case. I tried writing a null to my PK and leaving it unaddressed, and neither results in an autoincrement.
Thank you in advance for your consideration of this subject.
[quote=341160:@friedrich boettger] A gentleman on the Database forum suggested otherwise, which is my experience thus far. Another gentleman wrote a source code generator which uses named parameters and, in place of a bind function, something called “sql.parameters(fieldname)”, about which I can find nothing. Does anybody know what the score is on this? And, if there is example code available, please point me in that general direction.
[/quote]
Without a reference to the specific post its hard to comment
By default SQLite adds a 64bit unsigned Integer value to each record. This value can be referenced by “ROWID”, “ROWID” or “OID”… it is auto incremented with each insert… records can be addressed quickly by using this value in your queries. Deleted records will cause gaps in the sequence. If you delete the current highest rowid and insert a new row, that new row will get the same rowid, as it is again the “next in sequence”
If you DO add a PK of type Integer with the Auto-increment attribute, SQLite will map that to the ROWID value so the two are synomous.
WARNING : due to the loose definitions and typecasting of SQLite, a developer could in fact create a table and name a field “ROWID”… doing so not only causes confusion for others that may use the data and make “assumptions”, but in some cases might actually confuse the SQLite database engine itself.
That all being said… a SQLite table can be created without this default value by using the “WITHOUT ROWID” clause in the CREATE statement… While personally I would recommend against this… it seems there has been talk about future versions of SQLite making “WITHOUT ROWID” the default… however there has also been talk about removing other features, that Dr.Hipp was convinced by the user base to not touch, so this may or may not happen.
Of course managing the extra table with the current values for all the auto increments is kind of an overhead.
But I prefer that over managing my own counter.
I agree to Christian, i always manage my Primary Keys by code (writing a simple Method “NextFreeID” or however you name it).
If you have to change the DB-Engine (customer wishes i.e.) this is always compatible and has no specific side effects on how the DB manages autoincrement Fields.
I don’t think Christian is recommending rolling your own AutoIncrement, but using the built-in auto incrementing system! In any multi-user system, managing your own auto incrementing primary key is asking for trouble, even if the chance of a conflict is minute.
How do you know that the ID number returned by NextFreeID is not the same ID number as another user running NextFreeID milliseconds after you? Both of you INSERT your new records with the same ID and they clobber one another!
Your absolutely right if you’re in an multiuser environment, and my simple comment was intended for a single user Database.
In a multiuser environment there are a lot more things to do. I use a separate Table wich holds the LastUsedID of every Datatable and enclose the NextFreeID and the SaveRecord in a transaction. Then you can handle the event if an ID is used twice.
We use this method in our General Ledger Programs for many years and it works perfect. We did it because we found out that some DB-Engines managed the AI’s not correct in every situation (even DB-Engines are only programs with errors).
David Cox. You do not need to use Auto Increment on a PK. It’s already done for you. It’s a complicated relationship between ROWID and your PK
BUT
you can’t use ROWID as a PK as it’s not and will re-order if there isn’t a PK declared. The PK just stops the ROWID from re-ordering and if you SELECT ROWID, PK you’ll see the returned columns are PK & PK_1
You were looking for the link where Dave was quoting ?
Thanks Jym, I just assumed it was needed, so have always put it in.
From the article, I agree if you don’t need a Primary Key field, then you can leave it out, but I find it easier to include so I am sure I am updating the correct field and it alone.
[quote=341340:@David Cox]Thanks Jym, I just assumed it was needed, so have always put it in.
From the article, I agree if you don’t need a Primary Key field, then you can leave it out, but I find it easier to include so I am sure I am updating the correct field and it alone.[/quote]
Need it or not I always put it in a PK I’ve been caught with ROWID changing it’s value. If you’re going to edit and delete you need a PK plain and simple
David Cox if you have a Database Browser do the following
Make a table with 1 text column
Add 5 rows of data
SELECT ROWID, myTEXT from myTable
delete from myTable where ROWID = 3
Run the above SELECT - rowid = 1, 2, 4, 5
run Vacuum
Run the above SELECT rowid = 1, 2, 3, 4
Do the same thing with a PK in the table.
SELECT ROWID, myTEXT, pk from myTable it will return
pk, myText, pk_1 and pk\pk_1 will be 1, 2, 4, 5 (it doesn’t reorder)
[quote=341262:@David Cox]I don’t think Christian is recommending rolling your own AutoIncrement, but using the built-in auto incrementing system! In any multi-user system, managing your own auto incrementing primary key is asking for trouble, even if the chance of a conflict is minute.
How do you know that the ID number returned by NextFreeID is not the same ID number as another user running NextFreeID milliseconds after you? Both of you INSERT your new records with the same ID and they clobber one another![/quote]
Its actually trivial to do in any db that properly support transactions
create a table “Sequences” (tablename, nextkey) <<< use suitably sized types for the name (varchar and integer or int64)
for every table you need to get NextKeyID you have a row in the table “Sequences”
then to properly get the next key with no collisions its
begin transaction
update sequences set nextkey = nextkey + 1 where tablename = tablename your getting next key for>
select nextkey from sequences
commit transaction
Because this is in a transaction it will properly block until it can correctly get an id and then returns that id within the transaction