Unable to update table data using ODBC
(1) By Luigi Visintin (lvth01) on 2022-03-19 23:43:21 [link] [source]
Hi everyone, I'm a newbie on SQLite, however an old programmer in MS environment (MS Access + SQL Server...) and I'm trying to use Access AND ODBC (from ch-werner.de) to make mass changes in a specific SQLite db: the Thunderbird Address Book. I can connect and read each table on the db file, search with filters and any other READ activity, but I'm unable to change data, even using DBEngine.BeginTrans/CommitTrans. After several searches I did NOT find any mention about specific parms needed for updating data, but I can't do it. Neither can I find specific parm to put inside ODBC properties to allow updating. Maybe I'm missing some info telling that updating such db is not doable? Thanks Luigi V.
(2) By Larry Brasfield (larrybr) on 2022-03-20 00:27:12 in reply to 1 [link] [source]
Maybe I'm missing some info telling that updating such db is not doable?
I don't think there is any such info. I have used ODBC and Werner's ODBC/SQLite adapter to create, load, and query SQLite databases. While I can remember some frustration at times getting it all setup, I always found it worked fine once I had a connection.
Perhaps you should reveal a bit more about how you are opening and modifying your DB.
(3) By TripeHound on 2022-03-20 08:12:34 in reply to 1 [link] [source]
make mass changes in a specific SQLite db: the Thunderbird Address Book
If you haven't already done so, I'd suggest initially working on a free-standing copy of the database, or – at the very least – making sure Thunderbird isn't running (which may be more involved than just "not having the client open").
(4) By Luigi Visintin (lvth01) on 2022-03-20 23:14:02 in reply to 3 [link] [source]
I did as suggested: made a copy of the db (abook-1.sqlite) in a separate folder, made while Thunderbird was NOT running. Then created a new Access DB and linked to that copy of db using ch-werner.de ODBC driver with following parms: "ODBC;DSN=SQLite3 Datasource;Database=D:\Documenti\_Mail\_Profiles\abook-1.sqlite;StepAPI=0;SyncPragma=NORMAL;NoTXN=0;Timeout=100000;ShortNames=0;LongNames=0;NoCreat=0;NoWCHAR=0;FKSupport=0;JournalMode=;OEMCP=0;LoadExt=;BigInt=0;JDConv=0;;TABLE=properties" but no way to update a single row. It's really weird. I don't understand what's wrong.
(5) By Luigi Visintin (lvth01) on 2022-03-20 23:41:42 in reply to 4 [source]
After a few futile test I got a new idea: Access is unable to update a table without proper unique id. So I did the following:
- copy the original Thunderbird address book db
- open the copy using SQLite Expert
- modify "properties" table structure adding a new column ID (int64)
- update ID making it equal to rowid
- save db Then I run Access and made some change to a few rows. Eventually I opened again Thunderbird and the changes where there. Bingo, but... now I still have a doubt: will this structural change harm in some way the Thunderbird use of that modified table? I will use it for a couple of day (after having done a copy of the original, just in case...) and cross my fingers.... Cheers Luigi
(6) By Larry Brasfield (larrybr) on 2022-03-21 00:19:55 in reply to 5 [link] [source]
You would reduce the number of "confusion factors"a complicating your debugging quest if you were to get an isolated (not in-use or valuable) copy of the DB, then use the SQLite CLI shell as you try to run your SQL against that DB and its schema. Get the CLI shell here and learn to use it here.
Anytime you have a problem whose solution eludes you, if you can reduce it to { "I provided this input to the CLI shell, "...", but got this result, "..." }, you will get much faster answers here than when you bring in extraneous confusion factors such as ODBC, possible use by another program, your own code driving the ODBC adapter, and etc.
a. A "confusion factor" is anything which adds to the possible causes of an observed problem. The number of causes is additive, but the number of possible combinations of causes is often the product of the cause source possibility counts. Hence, "factor" is apropos.
(7) By Luigi Visintin (lvth01) on 2022-03-21 00:31:49 in reply to 6 [link] [source]
Thanks Larry, but I think I've found the real problem, Access unable to update table without unique row id. And I overcome this adding a unique key to the table. Now my problem (maybe it is not a problem) is only the question: will Thunderbird accept and continue using the modified table (where I've added a new column ID with unique values) without problems? I said in my previous post that I'll use this modified db for a few days, looking for the possibility of error from Thunderbird. I'll post my results later. Any suggestion will be very welcome. Cheers Luigi