I just tried an example using rowid, * on a table without a primary key and I also get the “Error: RecordSet is not editable because no primary key exists.”
I am having the same problem.
Having just switched from Real Studio to Xojo, I was attempting to replace the Deprecated RealSQLDatabase with the new SQLiteDatabase Class. None of my RealSQL Databases had a primary key and I never used rowid.
What is the quickest way to to make the new SQLiteDatabase Class compatable with the existing RealSQLDatabase files ?
Ron Bower
If you do not use RecordSet.Edit then you don’t have to do anything.
If you do use RecordSet.Edit then you need to add a primary key. Unfortunately, due to the above bug the easiest change of doing “SELECT rowid, * FROM MyTable” does not work in 2013r1. The bug is fixed for 2013r2, though.
So right now, the quickest thing to do would be to create new tables with a primary key and then copy your data to them using INSERT INTO SELECT FROM.
Thanks, Paul… I believe that I’m going to continue to use the deprecated RealSQLDatabase class until, at least, some of these issues are resolved.
I appreciate your response and will be monitoring this change closely.
Ron Bower
There’s nothing to resolve. If you want to use the new class, you need to adjust your table layout or your code.
Oops, I left the most obvious thing off my reply: The easiest thing to do if you have tables without a defined primary key is to stick with RealSQLDatabase for the time being.
That’s what I had to do. If you update your code to replace the deprecated REALSQLdatabase with SQLiteDatabase, RecordSet.Update won’t work without a Primary Key and it doesn’t appear to be possible to add a Primary Key to an existing table that lacks one in SQLiteDatabase.
Did you try EXPLAIN ?
(SQLite EXPLAIN)
In sqlite your table has one - ALWAYS - you may just have to explicitly select the ROWID instead of just selecting *
I tried using rowid but I kept getting the no primary key error. I also tried to add a primary key column but got an error message saying that I couldn’t add a primary key to the table. It is possible to add a primary key to a table that doesn’t have one in MySQL, but that does not appear to be possible in SQLite. All of the tables my app creates have a primary key except for one, and that is the user settings table that has only one record set, which is why I didn’t specify a primary key for that one.
Doesn’t matter whether you did specify one when you created the table or not.
I’d urge you to but sqlite adds one if you didn’t specify one.
Which version of Xojo ?
What does your sql look like ?
[quote=21724:@Norman Palardy]Doesn’t matter whether you did specify one when you created the table or not.
I’d urge you to but sqlite adds one if you didn’t specify one.
Which version of Xojo ?
What does your sql look like ?[/quote]
Xojo version is 2013r2. SQL looks like this:
RS = DB.SQLSelect("SELECT * FROM SETTINGS WHERE ROWID='1'")As far as I can tell, the records are being selected as I can read them, the problem occurs with RS.UPDATE, which results in the no primary key error.
Try
RS = DB.SQLSelect(“SELECT ROWID, * FROM SETTINGS WHERE ROWID=‘1’”)
If you did not explicitly add the integer primary key column then “select *” wont grab it
But you CAN get it by including it in the select statement (and you should list the columns explicitly)
I believe that I have successfully upgraded from the RealSQLDatabase class to the new SQLiteDatabase class.
Current IDE: Xojo 2013r2
I think I can now go back and remove some unnecessary or redundant code but wanted to be consistent as possible and do complete regression testing of all operations that affected the database.
Under Real Studio, I had a couple RealSQLDatabase class databases for which I never defined a Primary Key as I did not see the need in my application. The large majority of the queries were of the format “SELECT * FROM…” and I had only one place where I actually performed a db.Edit method call.
Upgrading to the SQLiteDatabase class involved making the following changes:
- Redefining every instance of the RealSQLDatabase class to be a SQLiteDatabase class.
- Changing every db.SQLSelect( “SELECT * FROM…” ) to be db.SQLSelect( “SELECT ROWID, * FROM…” ).
- Making sure that every db.Commit statement was preceded by a db.SQLExecute( “BEGIN TRANSACTION” ). The statement was placed before any loops were entered or before the first non-loop change to a database.
So far, everything I’ve tested seems to be working just fine - no problems have been detected that I can see.
So, first question… Do you see anything that I missed ?
Second question, just to make sure I understand some things:
- I assume I can eliminate the “BEGIN TRANSACTION” and Commit commands for one-at-a-time updates and only leave it for multiple updates in a loop. Correct ?
- I assume that the ONLY place where I really need the “SELECT ROWID, * FROM …” in place of the “SELECT * FROM …” is where I perform the db.Edit. Correct ?
Thanks for all you guys do for all of us.
No. Looks good to me.
[quote=22227:@Ron Bower]Second question, just to make sure I understand some things:
- I assume I can eliminate the “BEGIN TRANSACTION” and Commit commands for one-at-a-time updates and only leave it for multiple updates in a loop. Correct ?[/quote]
Correct.
[quote=22227:@Ron Bower]
2. I assume that the ONLY place where I really need the “SELECT ROWID, * FROM …” in place of the “SELECT * FROM …” is where I perform the db.Edit. Correct ?[/quote]
Correct.
Thank you very much for the quick feedback, Paul.
Now I’m ready to start adding some requested enhancements to the existing application.
Interestingly enough, this did work for the table that was created without a primary key:
DB.SQLSelect("SELECT ROWID,* FROM SETTINGS")However, this resulted in a no primary key error on a table with a primary key:
DB.SQLSelect("SELECT ThisColumn FROM SETTINGS WHERE PRIMARY KEY='1'")And this resulted in a type mismatch error:
DB.SQLSelect("SELECT PrimaryKey,ThisColumn FROM SETTINGS WHERE PRIMARY KEY='1'")As did this:
DB.SQLSelect("SELECT ROWID,ThisColumn FROM SETTINGS WHERE PRIMARY KEY='1'")I should also point out that the language reference uses this as an example for SQLiteDatabase:
DB.SQLSelect("SELECT * FROM SomeTable")[quote=22414:@Carl Hogue] this resulted in a no primary key error on a table with a primary key:
DB.SQLSelect("SELECT ThisColumn FROM SETTINGS WHERE PRIMARY KEY='1'")[/quote]
Because your recordset does not contain the primary key column.
[quote]And this resulted in a type mismatch error:
DB.SQLSelect("SELECT PrimaryKey,ThisColumn FROM SETTINGS WHERE PRIMARY KEY='1'")[/quote]
Not on thise line, probably. Where did the type mismatch show up?
Very new at this, so need to ask and learn…
As I understand it, Primary Key is always an Integer. So would you put single quotes around the number being matched ?
Quotes around integer values are optional.