I want to be able to do batch edits on text tables. I’d like to use SQL to do this. There is a way to do this using SQLite Expert Personal, but getting that to work has been hit or miss because of the special collating sequence that RM uses. It requires tinkering with adding a default DLL which is a tad above my pay grade. Is there any other possible way to do this?
You have to “fake” collation when work with “text” fields.
https://sqlitetoolsforrootsmagic.com/?s=RM+NOCASE
Check out about rebuilding indexes when using fake methods
Kevin
I’m just a simple country boy. I’m also a pretty lazy country boy. But I’m pretty fluent with SQL and I know most of the RM database tables by heart. So I do a lot of SQLite with my RM database. But being both simple and lazy, I never wanted to have to become an expert in SQLite managers.
The first one I downloaded was very simple and easy to install and easy to use - namely SQLiteSpy. Despite its simplicity and paucity of features, it has always suited my needs very well and I have not felt the need to research and install a more comprehensive solution such as SQLite Expert Personal. And in fact, I have even figured out in the last year or so how to do recursive SQLite queries from within the SQLiteSpy environment, which has opened a whole new universe to me of possibilities for useful queries.
I did look just now at the directions for installing the special collating sequence with SQLite Expert Personal. The directions don’t look like rocket science, but at first glance they look a little daunting. The special collating sequence I use with SQliteSpy is much less comprehensive, but I don’t really care. And it’s very easy to download and install. You just download it, and there is in option in SQLiteSpy where you tell it where the special collating sequence is stored on your disk. That’s it, and it never actually has to be “installed”… So that might be an option. Otherwise just fight through the directions for downloading and installing the special collating sequence that works with SQLite Expert Personal. You really have to have such a special collating sequence available to your SQLite manager if you are going to anything in SQLite for RM that’s very comprehensive.
If you get either solution to work, do be sure to take under careful consideration the advice from kevync1985 about reindexing. If you update any text columns that are indexed, you will always have to run Files > Tools > Rebuild Indexes in RM after making the changes with SQLite. My experiences in the other direction is mixed and I don’t underhand why. Sometimes I have to do a REINDEX in SQLiteSpy before running a query on indexed text columns and sometimes I do not.
Thank you for this comprehensive reply! I’ll definitely have a look at SQLiteSpy. And maybe I’ll try once again to figure out the collating sequence problem in Expert Personal. I use Expert Personal for some other databases that I maintain so it would be convenient to be able to use it with RM databases. Definitely beats having to update a huge table manually.
If I were in your situation, I’m pretty sure I would try to fight through the installation process for the the special collating sequence that works with SQLite Expert Personal. In your situation, that’s surely a better outcome than needing to run two different SQLite managers when you are already experienced in using one of them.
I have a write-up about using SQL on RM database at my site-
see: Run SQL on your RootsMagic database safely
on the page-
1 Like
Wow! Really nice! Thanks very much! I love the idea of a bare minimum site as well. Might have to do that.
Thanks.
I just updated that section to make it clearer.
If you need help creating a GitHub.io website, I can help.