Having switched to Xojo and from REALSQLDatabase to SQLiteDatebase, my small background database is not working properly. The database is created and the initial insert works fine, but I use a recordset to periodically update it and this is not working. I am getting the “Recordset is not editable because no primary key exists” error when I call rs.edit, despite including rowid in my select statement:
rs = db.SQLSelect("SELECT rowid, * FROM FilesToProcess")
At this point, I am at a loss on how to get past this.
Did you have a separate Column as ID ? Please see reference below:
Auto-Incrementing Primary Keys
With SQLite, if a table has a single column specified as the
INTEGER primary key, then that column auto-increments when a
row is added to the table. This column is said to map to the
internal rowid column that is on all SQLite tables.
However, just because SQLite has an internal rowid column, you
should not rely on it as your primary key. Rowid values can be
changed behind the scenes by SQLite and this could possibly
corrupt any relationships in your database. Always create a
separate primary key for your tables.
Rowid should always exist on every sqlite table - whether you create it or not
There is a downside to NOT creating your own (they can get reset / altered when a vacuum is invoked)
see http://sqlite.org/lang_vacuum.html
However its possible to define an integer primary key that is NOT aliased to rowid
That could be the issue here
If you defined your table as
CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));
CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));
the in these 3 cases referring to X and ROWID is equivalent
However doing
CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);
means X is NOT an alias to rowid but IS the primary key
The responses so far ignore the fact that this should have worked, even in the absence of a primary key. Especially if it worked in REALSQLdatabase and the only difference is the automatic inclusion of rowid. There must be something else going on that you haven’t mentioned.
I’m suspicious about rowid NOT being the primary key which you can do (see my post)
It’s about the only way I can think of to not have the rowid as primary key
What OS are you on ?
If you’re on OS X you can actually open your database using the command line sqlite tool & dump out the create statements for your tables with select * from sqlite_master where type=‘table’
That’d be useful to see whats up with this table
It’ll be fine with simple queries.
Complex queries (ones using intersections, unions etc) you’ll have issues.
It’s why we altered the plugin in the way we did & made it your responsibility to select out the primary key instead of trying to add it automagically.
rowid as used in my original select statement was directed at the automatically created rowid. I was not creating my own. I looked at the created database with SQLiteManage and rowid was in the database. So, I am still at a loss as to why the select statement did not work.
I added at primary key in the table create statement:
rs = db.SQLSelect("SELECT PrimeKey, * FROM FilesToProcess)
I am able to edit and update the record set and database.
So, the fix is not too hard, once you understand what it is, but the failure of the original select statement, which should work, is still unexplained.
I don’t know how many threads this issue is discussed in but a posting from Paul Lefebvre (of Xojo, Inc. fame) explained it to me when I encountered this. The problem is not with the select statement but shows up with the rs.edit when there is no primary key.
Xojo no longer does this for you so you need to have the a primary key in the table and in the select statement. Yes, rowid probably will work, too.
For me SQLite was creating a rowid in the background and I was invoking it in the select statement creating the recordset, but Xojo was still insisting there was no primary key at the point of editing. When I created a separate interger primary key and invoked it in the select statement, the edit statement worked.
There’s the culprit !
PrimeKey is NOT an alias to rowid or vice versa
So in your table rowid is NOT a primary key so you cannot edit it if you ONLY select the rowid
For PRIMEKEY to be an alias to rowid & vice versa the clause needs to be EXACTLY INTEGER PRIMARY KEY
The ASC wrecks that
See my earlier post where I mentioned this
[quote=15127:@Norman Palardy]Rowid should always exist on every sqlite table - whether you create it or not
However doing
CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);
means X is NOT an alias to rowid but IS the primary key[/quote]
"This means that the following three table declarations all cause the column “x” to be an alias for the rowid (an integer primary key):
CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));
CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));"
So, PrimeKey should be an alias for rowid.
However, when I was using:
rs = db.SQLSelect("SELECT rowid, * FROM FilesToProcess")
There was no separate primary key set. I was depending solely on rowid as the primary key, which everything above and in the documentation tells me should work, but it did not.
I’d try making PRIMEKEY just be defined as INTEGER PRIMARY KEY and see
ROWID not being an alias for PRIMEKEY (and vice versa) is the only reason I can think of
rs = db.SQLSelect("SELECT rowid, * FROM FilesToProcess")
PRIMEKEY was not a field in the database at all. There was no alias to ROWID. ROWID was present in the database. I could see it using SQLiteManager. It should have been the primary key, but the recordset was not editable. It is my impression that theoretically a recordset called with the above statement should be editable when there is no other primary key in the database. However, that was not the case for me.
As noted above, there is a relatively simple work around for this, but it also appears there is a bug somewhere.
At this point, we’re all just speculating. Can you recreate your original table in a new database file with no primary key and verify whether you can or cannot edit a record using "select rowid, * … "? That will help narrow down the problem. It will also produce a sample that can be included in a feedback case, if necessary.