I have a DB with 5 multi-field tables. I want to make some field changes in all of them. My initial attempt failed and erased 4 of the five tables. I wrote an alter-table program consisting of making the various changes to each table using five transactions as:
Begin Transaction;
create temp table 1
insert into temp table 1 from old table 1
drop old table 1
rename temp table to old table 1 name
Commit;
db.SQLExecute
Repeating same procedure for each table.
At the end, all that remained in the db was the last table. Since I thought I did a reasonable job at studying the SQLite literature, I found nothing warning about serial ops like this nor a warnings about transaction/commit operations being a danger.
create table XX
insert into XX from T1
drop T1 // you now have XX T2 T3 T4 and T5
rename XX to T1 // you now have T1, T2, T3, T4 and T5 with the same contents as when you started.
//
etc…
You most likely need a commit BETWEEN each step, some DB engines create a cache table that doesn’t end up at the desired destination until commited, allowing for rollback,
but in the end you SHOULD have T1 thru T5, with the exact same contents as when you started… so whats the point?
I wrote a second post giving more info. Hopefully it gave additional information. In response to your post:
The overall objective is to create a ‘new’ integer primary key column to enable recordset updates. I keep receiving ‘…operation cannot be performed due to lack of a primary key field’.
I assume it is occurring because the original database was created without a primary key field and because ROWID can not be accessed. So, I am trying to rehabilitate the database by adding a new primary key field. I think there is enough available functionality to get the data because I can see it in sqlitebrowser.
Dave
Well, I don’t see my expanded explanation. Probably too tired to hit the ‘post’ button.
The database was created years ago in Realbasic. Don’t know the version, but it was long ago. Everything functioned well without a primary key field. Now it doesn’t. I am not a database expert. My goal is to extract the existing data and port it into a newly formed database using current xojo/sqlite code.
My one major attempt so far was a failure. It is likely a procedural issue due to lack of experience.
Tim,
I have attempted your suggestion and can’t seem to access rowid either.
Interestingly, it appears sqlitebrowser will dump a CSV of the data but without a rowid field. So, I have been attempting to synthesize a primary key/rowid field to which the CSV data can be added.
I suspect that if this idea doesn’t strike a chord with this list’s experts I am taking a long walk on a short pier.
Can you explain that a little? What did you try? What results did you get?
BTW, nothing you have posted so far would require rowid, so can you explain a little more where you got an error regarding it? You should be able to copy tables without regard to rowid, so that seems to be a bit of a red herring. Take a step back and try copying the tables individually, with and without a primary key. You should minimally be able to get a copy of the tables with no primary key by doing them one at a time instead of all 5 in one transaction.
Have you tried your table copy commands in sqlitebrowser? You need to start from someplace where it works and then try to bring it into Xojo/a single transaction.