SQLite3.exe, etc. problems

[kinook]

1) UR operates in an identical way regardless of the file extension of the files you open in it
(they must be SQLite format and contain UR database schema, but the file extension is irrelevant)


2) The 64-bit edition of UR has existed since v6.0 (Sept. 2019)

https://www.kinook.com/Forum/showthread.php?t=5602

You can download the 32-bit edition from

https://kinook.com/Download/UltraRecallProEval.exe

and the 64-bit edition from

https://kinook.com/Download/UltraRecallProX64Eval.exe


3) You can always view the detailed history of changes here:

https://www.kinook.com/Forum/forumdisplay.php?f=27

which is linked to at the bottom of

https://www.kinook.com/UltraRecall/Manual/?version6.htm

Note: See the history page on the web site for full details.


4) UR / SQLite can handle much more than 50,000 items. See

https://www.kinook.com/Forum/showthread.php?t=709

https://www.kinook.com/Forum/showthread.php?t=3627

https://www.kinook.com/Forum/showthread.php?t=3890


5) Nothing has changed with UR (the executable is from Aug. 2021 and did not change with the latest update --
see https://www.kinook.com/Forum/showthread.php?t=5776),
so if you are unable open the files in another SQLite front-end, something must have changed on your end.


6) I used a binary editor to change the signature of the Intro sample DB to SQLite Format 3,
renamed it to Intro.db (in attached ZIP file), and was able to open and query it in
SQLite's sqlite3.exe, Navicat, DB Browser for SQLite, and SQLite Studio and UR
(downloaded and installed latest versions of each today).

[Spliff]

Thank you so very much, Kyle for your tremendous help, everything, really everything, works now!

And of course, I would have believed you without the screenshots!

Not having found the "Manage Attachments" button yesterday, I now add a screenshot as an example for how UR created ALL new files then (i.e. before repair); curiously, these files would regularly open in UR; I have checked all my "real" = previously-created UR files, all of them are ok, functionally-, AND Hex-wise for their signature and their signature's, ditto for all newly created UR files now.

After your more than very kind answer, I then googled "windows repair" or similar, then I ran sfc /scannow - I know about that in principle, but as my post yesterday showed (again), when "nothing works anymore", I'm good-for-nothing, unfortunately, multiplying errors and mistakes.

So, sfc / scannow FOUND corrupt files, and was able to repair them - as said / implied above, nothing else though did not work correctly, except (!) for the sqlite problems (first "just" with sqlite3.exe - Navicat had worked for my UR files at the time! -, then also Navicat not opening those files anymore, and then also UR creating those crazy UR file "headers" for new files (I've got my "set" of files now, so I don't create new ones in regular use)...

After the repair, I re-downloaded (again) and re-installed (again) UR, Navicat, 010Editor, and sqlite3.exe (again into c:\), and now everything works as expected, incl. sqlite3.exe (the latter one badly, as expected).

Thank you very, very much, Kyle!


FOR FELLOW UR USERS:

Since UR creates new files (as expected, and before a possible registry key setting perhaps) with .urd, and with its own signature, you better create new files as renamed copies ("save as") from a (not corrupted, of course) "dummy" file, as for doing the necessary changes once-and-for-all. As Kyle has gracefully clarified above, UR will then "leave alone" both of these changes, so you can use the current, generic (!) sqlite3.exe now. Thus:

If I remember well, Kyle's (old, "special") version of SQLite3.exe (which didn't even do special chars like äöü well in command-window output if I remember well) is NOT needed anymore; once you do the signature switch and the suffix rename, use the newest sqlite3.exe from sqlite.org - mine is just days old (i.e. even newer than the one I had downloaded from them only weeks ago.

Their site is misleading: For their 64bit version, you would have to do the necessary compiling yourself (!) - so you use the 32bit version which is, as said, regularly updated. It is to be found here:
https://www.sqlite.org/download.html , and there then
"Precompiled Binaries for Windows", and there finally the THIRD and last entry,
SQLite TOOLS zip
and the sqlite3.exe (32bit then) is only to be found after unzipping that "tools" zip -

and you'll need their help page: https://www.sqlite.org/cli.html

(whilst this is a quite bad page, according to me:
https://www.sqlitetutorial.net/sqlite-commands/ )

Then, the following code works (more or less, see my (comments):

(first you run sqlite3.exe, which will open a command-window, and in there then

.open "d:/ur/filename.db"
(being renamed, and with the generic SQLite3 signature)
(here, you MUST enter the complete path, and incl. the file suffix; unfortunately, sqlite3.exe does NOT give you any hint if you mistype even a single char here, but just "creates" a new, empty db file (without suffix) instead, and it isn't but afterwards that it will you flood with "no such table", etc. error messages: no wonder then since everything you do, will apply to a new, empty db, unknown to you!)

.mode line
(or column or csv or several others)

.output stdout
(
will show your select results in the command window, even with äöü, etc. being preserved now; or then
.output |clip
= most useful for UR use, obviously: will write the output into the clipboard, but here äöü, etc. will create "chaos"; obviously, this is a question for the sqlite.org forum, or perhaps (i.e. in the meanwhile, if the sqlite.org developers do something about it but not immediately) for general Windows forums, re "clipboard page file"; or
.output d:/filename.txt or .csv..., with or without "", and with / or with \ (as above for the db file) but here you must expect problems: sqlite3.exe creates that file (if it not already exists) on the spot (i.e. not waiting for your select command to be written into that file), and in my tries today (mode line and suffix .txt), it will remain empty, even after the successful select command, that one being successfully executed being checked by both .output stdout and .output |clip, before the select - and, of "course", in my partially successful tries some weeks ago, sqlitee.exe effectively wrote into the given file, but scrambling all öäü, etc., as it continues to do now in .output |clip, but NOT in .output stdout anymore...
EDIT:
similar on first try:
.once d:/ur/YourOutputFile.txt
= will create that file immediately (instead of waiting for the subsequent select to "need" it, and then the (correct) output is done in the command window (which is the default anyway, so
.output stdout
is only necessary if you had defined some other output beforehand
EDIT 2:
but on second and third try, the same
.once ... with
select ...
then FILLED the output-file with the expected output, instead of again displaying it in the command window, thus:
sqlite3.exe obviously has its glitches... another very valid reason indeed to use its UPDATE commands onto COPIES of your valuable UR file... ;-)
EDIT 3:
And the FILE output (if it's successful, then...) is with äöü, etc. preserved, whilst the clipboard output is not, so the sqlite3 developers obviously are "working on it"?! ;-)
)

.header on
(in case, etc., etc.)

(for macros / scripts, the
.read d:/folder/SomeSqliteCommandsInATextfile.txt
command could become of real interest, didn't try it out yet though)

select ...;
(here, the ; is required; if you forget it, sqlite3.exe seems to hang, and does not execute the select)

And, not in parentheses, try any SQL that changes things, by "update", etc., on a COPY before, then comparing the resulting, new db with the "original" (there even is a free compare tool in the above-mentioned sqlite.org .zip download, and then there are several proprietary ones, as well as there is one within Navicat, I mentioned that fact some months ago - and finally, as you can see from one of Kyle's screenshots above, he chose Navicat, too, and yes, there's a reason for that - myself, just "playing around", i.e. doing "my things" as a pensioner, not "making money", had not pay for the really expensive regular = commercial version that is...)

It's obvious that if you destroy your UR db with sqlite3.exe, then ask Kyle for help, we'll drove him nuts! Thus:

As said, Navicat for SQLite is really best but if you want to use specific tools, there also is (not mentioning DBeaver, with Data compare, but also expensive):

- SQLiteDiff (25$, not trialed yet)

- KS DB Merge Tools for SQLite ( de-merge-tools.net , free and 50$ for more functionality, not trialed yet)

- SCLite Compare Utility (on codeproject.com, free, download for members only - membership is free I suppose -, not trialed yet)

- there are also Altova DiffDog and Altova DatabaseSpy (both altova.com), Apricot DB (sourceforge.net) and some others...

There are other compare tools (especially or also) for SQLite DBs ("schema compare" will not help you then in most circumstances, it's "sqlite data compare" you'll be after), but the important thing is, do "updates" work on a file copy first, then compare, in SQL, the slightest mistake can create havoc.

And in case of problems, or even on a regular - monthly? - basis, run
sfc / scannow (I've noted a ToDo for that matter; obviously, you can get very specific but very serious problems indeed, even without your whole system going "unstable"...)


My deepest apologies again, for the brave-and-unnecessary work I caused you, Kyle!

[kinook]

Excellent, glad to hear that it is working.

One more note:

7) If you want all new files created by UR to have the SQLite Format 3 signature, take one that you have already modified and copy it to

%APPDATA%\Kinook Software\Ultra Recall\template.urd

(literally type %APPDATA%\Kinook Software\Ultra Recall into Windows Explorer to go to that path)

See "User-defined new Info Database" at

https://kinook.com/UltraRecall/Manual/miscellaneous.htm

[Spliff]

Oh, I didn't know that, that's brilliant! (I had done it "manually" each time.)

What is the UR files' code page / language format?, please, or are there several codepages even? (Window has got two, not one, from my current research. Is there any setting within UR, perhaps in the registry?

They say the codepage for a sqlite file should be UTF-8 (which would be 65001, there is also 437 for U.S., 850, the default Western European one, and then 1252, West Europen Latin, and I read precisely that one multiple times in UR files' headers, but I find there other indications, too, so I am not sure: which one applies, please?

I haven't found, by web search, any indication of the used character set, neither in the UR help file, "language", "utf", "utf-8", "utf-16", "ansi", "code page", etc. all don't give any result. (So my äöüéàè problem for sqlite.exe clipboard output (only) persists, but I'm in discussion about that problem in their forum currently, will "update" if there is a viable solution, or in case create file output to a virtual drive a: within the work memory (1 GB or just 500 MB would be sufficient), then write to and read from these "files" in the virtual drive. Since:

I WAS MISTAKEN about sqlite3.exe's
.output d:/ur/filename.db
and
.once d:/ur/filename.db
(always with /, not \ !!!):
the file manager I use most of the time (instead of several paid ones), FreeCommander XP, does NOT update (at all, or then just after several minutes!) the "0" content indicator in its list view, since it obviously doesn't "get" that there is already present the expected content, having been written into the pre-existing file (since previously created by sqlite3.exe (i.e. before the "select"), then filled up later on (after the "select"), even in the .once case. Thus I erroneously thought those files remaining empty, since FC told me so: my bad!)

Sorry for the additional confusion created!


EDIT:
Sorry, I had not thought to also search the forum, so I have done this search now, I find the threads
https://www.kinook.com/Forum/showthread.php?t=4405
https://www.kinook.com/Forum/showthread.php?t=1705
https://www.kinook.com/Forum/showthread.php?t=2472
and it seems that 1252 here is right?

BUT then, would UR choose the code page it applies to the files it creates, according to, i.e. in function of the user's system codepage? (Which was 437 in my case and up to now, and which is probably not "ideal" for my multi-language needs anyway...)

I understand code pages must be any developer's nightmare...;-( (All the more so since they say there is a second (!) codepage, somewhat "hidden", in Windows... and that there are even two more but which are considered less relevant... thus, FOUR codepages on any given Windows system...)

[kinook]

Ultra Recall is a Unicode application, and Windows uses UTF-16 encoding for its APIs.

https://docs.microsoft.com/en-us/win...2/intl/unicode

https://docs.microsoft.com/en-us/win...ntl/code-pages

Text is converted to UTF-8 encoding when stored in the URD file.

[Spliff]

Thank you very much, Kyle, for this valuable information, so the "1252" that I read in the urd file "headers" (i.e. quite near to the "top" of the files' binary text) are not to be considered.

So it's clarified then, and sqlite3.exe's clipboard problem seems to be unresolvable, but that's not a real problem after all, and the way to go is:

- by sqlite3.exe, write the select output to file
- read (by AutoHotkey or similar) that file into clipboard or into variable

- do the necessary transformations (e.g. regexreplace for the itemtitle "column") in there (by AHK or else within the var)
- ditto in there (or in a second var, for better checking), transform the result lines into the necessary SQL code ("update tablename...") for sqlite3.exe to execute that

- write that SQL code into a file
(- check that file, create the necessary UR COPY at least now...)
- have sqlite3.exe read and execute that file content (.read filename) (onto the UR COPY...!)


Thank you so much again, Kyle, you've been of tremendous help!


(And obviously, the "there is no such table" error messages by sqlite3.exe I had reported here some weeks ago, must have been caused by the above-mentioned problem of the sqlite.exe ".open filename" command just creating a new, empty file (and without that table then indeed), when you make any typo or don't also enter the suffix, i.e. there is no "there is no such file, create it?" message from slite3.exe then, so you try to work upon an empty file then...)

[Spliff]

Just a detail.

Kyle, you said opening an saving the DBs in UR leaves the db signature, when changed to SQLite format 3, alone, and that's right, and this is fantastic!

"But Tools - Compact and Repair" then changes the signature back to Ultra Recall DB.

(And this explains why I had unexpectedly encountered "chaos" within my db signatures, after having them all changed to the generic SQLite signature in bulk.)

I hope that can be addressed, too? ;-)