SQLite3.exe, etc. problems

[kinook]

Again, UR can already open files with a .db, .db3, any other extension, or no extension at all (provided that they are actually Ultra Recall database files).

If you want to associate some other file extension with UR, right-click the file, Open With, select Ultra Recall, and set 'Always use this app to open'.

You may need to set the file signature to 'SQLite format 3' using a binary file editor, but after that, you will be able to open and query it with SQLite3.exe or any other SQLite tool.

[Spliff]

Oh! Just tried, and you are right again, so NEITHER the UR-specific signature NOR the UR-specific suffic (.urd) are needed anymore, in order to open - or save - UR db files in/with UR: absolutely perfect!!!

Thus, I can now rename all my UR files (with the signatures already changed to the generic format) to "something.db" (they are all within the same folder, so there is no risk), and switch them between UR and any sqlite front-end: what a relief!

THANK YOU SO MUCH for this very valuable info, Kyle!


(
As for the related sqlite.exe problems, I am aware that you are not the "right address" for my "complaints"; I'll ask the developers what they think about it, and will do some tries with their .dll

(see https://stackoverflow.com/questions/...ute-a-dll-file ) with RUNDLL32.EXE <dllname>,<entrypoint> <optional arguments> - thus, using, from within UR, their (hopefully faultless) .dll instead seems to be feasible (if not obvious and easy; they will probably have further information about their .dll use).

And even with their .dll GUI, it seems to be possible to have a UR db open in UR, (re-) open it in the .dll GUI (or another sqlite front-end) (and refresh it in there), do some change in there, then do the necessary refresh in UR, i.e. to avoid closing the db in UR, open it in some front-end, closing it over there, reopen it in UR - which the sqlite.exe (but which seems to come with real "problems"), called from within UR, is deemed to to.
)

[Spliff]

The described problem does not derive from sqlite3.exe, it would probably be UR which does change something in its UR files which causes the problem.

In Navicat, I now also get, for every one of my UR files (all of them renamed to .db, and with signature changed to
SQLite format 3
(15 chars changed, and verified)
"14 - Unable to open database file"

whilst these files open normally in UR.

As said, my "normal" UR files, with .urd suffix and with UR signature, opened correctly in Navicat, after me having renamed them and changed their signature, and now, where I use alleged "native" SQLite files in UR, they don't open anymore in SQLite front-ends incl. sqlite3.exe.

Since the only difference to "before" I can remember of, is the suffix renaming - after your last hint, i had renamed all my UR files from .urd to .db -, whilst the signature change, for most of my about 30 UR files had already been done (as said, I had run a 010Editor script to "change" the (already changed) signature again and again, wrongly assuming that UR had changed it back, each time, to its UR signature):

When UR opens, or saves, UR databases with .db suffix, does it change anything within the file then, which is does not change when it opens / saves the same file with .urd suffix?

I'm at a complete loss here.

The problem is not caused by my earlier opening the files in question in some front-end, it systematically affects all my UR files, even when, except for the signature change, I only opened them in UR.

As well as UR, Systools SQLite Database Recovery opens the files normally.

What parts of the binary should I check for possible errors, please?


The described problem does not derive from sqlite3.exe, it would probably be UR which does change something in its UR files which causes the problem.

In Navicat, I now also get, for every one of my UR files (all of them renamed to .db, and with signature changed to
SQLite format 3
(15 chars changed, and verified)
"14 - Unable to open database file"

whilst these files open normally in UR.

As said, my "normal" UR files, with .urd suffix and with UR signature, opened correctly in Navicat, after me having renamed them and changed their signature, and now, where I use alleged "native" SQLite files in UR, they don't open anymore in SQLite front-ends incl. sqlite3.exe.

Since the only difference to "before" I can remember of, is the suffix renaming - after your last hint, i had renamed all my UR files from .urd to .db -, whilst the signature change, for most of my about 30 UR files had already been done (as said, I had run a 010Editor script to "change" the (already changed) signature again and again, wrongly assuming that UR had changed it back, each time, to its UR signature):

When UR opens, or saves, UR databases with .db suffix, does it change anything within the file then, which is does not change when it opens / saves the same file with .urd suffix?

I'm at a complete loss here.

The problem is not caused by my earlier opening the files in question in some front-end, it systematically affects all my UR files, even when, except for the signature change, I only opened them in UR.

As well as UR, Systools SQLite Database Recovery opens the files normally.

What parts of the binary should I check for possible errors, please?


EDIT:

Might it be that UR files, with suffix .db or db3 (I tried both), and with generic SQLite3 signature, might not be compliant to newest SQLite3 format anymore?

Since I originally had used 010Editor to switch the signature (by script), then write into another file, and had only used the suffix .db, I now - all manually now! - created a new UR file, then copied all regular items (i.e. without Inbox, Recycle bin, Templates, etc.), then 'v of this "material" into the new UR file, worked fine in UR, even after closing the new file and re-opening it.

Then I renamed the UR file, to .db3 this time (instead of, as said, having always used .db as suffix before); then I opened this file in 010editor, and changed, manually now,
the signature,
Ultra Recall DB
= 15 chars, switched to:
SQLite format 3
= 15 chars

I'm positive I didn't make the slightest typo error, or similar.

Then, in 010editor (I'm not making an advertisement, it's just the binary editor I had bought for this task, some months ago),
I did a "save AS", renaming the output file (and leaving its suffix alone).

I then tried to open the file (i.e. original, new .urd, then renamed to .db3, then with switched signature) in Navicat, and again, I got the above error code, even after replacing my "Navicat for SQLite 16" by its newest update... and I remember I had also replaced my Navicat version, some days ago, by its newest update then, and, as said, sqlite3.exe, from sqlite.org, their newest download (I checked again today on their download page) "loads" my UR files, but then, when I do a "select" re a specific table, sqlite3.exe pretends, "no such table".

All the above seems to indicate that the "newest" SQLite3 format (as treated by both the brand-new versions of sqlite3.exe (some weeks old) and Navicat for SQLite, version 16 (both from some weeks ago, AND from "today" or just some days old) has deviated from what UR produces / stores, independently of the file suffix, and of the file signature.

"Download" errors could be excluded, I think, since after my initial problems with sqlite3.exe, I had downloaded, and installed, it a second time, and my - "brand-new" (I hadn't done further tries after my last post here, some weeks ago) Navicat problems today, with a Navicat version just some weeks old, AND with my brand-new Navicat download of today, i.e. in both versions today I get the same problem, totally parallel / imitate ma sqlite3.exe problems described some weeks ago.

Thus, independently of my SQLite front-end being Navicat (from some weeks before, checked today), Navicat-newest (downloaded and checked today), or sqlite3.exe (downloaded and installed two times with the same result, always the very latest version), I do NOT have access to my (renamed and signature-switched) UR files anymore, even to brand-new UR files - not from "within UR" (by sqlite3.exe), nor when those db files are "freely available" (i.e. closed in UR).

I can't send you those files, bec/of their respective content, but I affirm that
- I'm 100 p.c. honest
- I have applied the utmost care and attention to anything done and referred
- I can't imagine 010Editor is the culprit here (as said, I did the signature changes by script before, whilst I've now done them manually)?:

I now have hex-compared, in Beyond Compare 3, the "result" between
- UR file just renamed to .db3 now, and
- UR file after manual-now signature change, and this is weird:

D:\UR\OTRIAL_just_renamed.db3
vs
D:\UR\OTRIAL_just_renamed_then_signature_change(th en save_as).db3

I would need to be able to send a screenshot now but don't know how to do that; "original" (i.e. first) file (hex start):

original file
55 6C then 2 blanks!!! 74 then 4 blanks 72 then 1 blank then 61 then 1 blank then 20 52 65 then 1 blank then Ul t 1 blank

etc

>

My UR installation does not work correctly anymore, obviously, will have to reinstall!

Second file start (i.e. after the signature switch):
53 51 4C 69 74 65 20 66 20 66 66 6F 72 6D 61 74 20 33 (7 blanks, then) 00 04 00 01 01 04 40 20 20

the 53 51 4C 69 74 65 20 66 20 66 66 6F 72 6D 61 74 20 33 obviously being the correct "SQlite format 3" change (Beyond Compare just displays the non-identicals in this setting).

I obviously have a hardware problem which has affected my UR installation... (but nothing else, it seems?)


EDIT 2:

I just discovered your new version, from June 11 (the last one of my multiple update checks had been just days ago) - so it's 64bit now?!!! The link just guides to your general version 6 page, so I can't see what's really new here - this is a recurring problem of your "history" or "what's new" links, they, most of the time, just go to the MAJOR version's updates, not to the fine-grain, in-between version, so they are useless most of the time, and then, finding the specific-version updates, most of the time, I don't get to them.

This being said, it would be WONDERFUL if you updated UR to 64bit, from 32bit, at last, and make it a "major" i.e. paid upgrade soon, I'd be happy to pay... IF then you also addressed some of those ugly GLITCHES... which never ever have made me lose ANY item, but which are so awful in the daily interaction with UR! Hopefully, too, 64bit will now allow for BIGGER database, possibly in the 6-figure item count? (As said, I've got lots of problems with "bigger" databases, and 50,000 items had seem to be a "reasonable" limit to me, up to now, in order to not multiply my problems... if that now, or soon, will be changed to some really bigger number of items, to even 300,000, that would be wonderful! As said, I had to divide my items into about 30 UR databases, which is manageable, BUT (since it's SQLite3) search is db-specific, and linking to "external" databases is technically possible, but really awful, in direct comparison with links within the same db, so I would so much like to be able to re-unite my 30 UR databases into just one... with the management of 300,000 items becoming manageable, that is, and that had not been the case before: so with 64bit, I should try again? or wait some months?)

Unfortunately, the above-described problems persists: UR databases (with suffix .db or db3, and with changed signature, and which correctly open in (just-downloaded-updated) UR, do NOT open in (just-downloaded-and-updated) Navicat, and trigger the error-code mentioned above, instead.

Whilst the beginning of the hex is:

53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00
04 00 01 01 04 40 20 20 00 00 19 C1 00 01 4C 5D
00 01 4B D7 00 00 00 6D 00 00 00 EC 00 00 00 01
FF FF EC 78 00 00 00 00 00 00 00 01 00 00 00 00
etc

I had to copy this manually, one-by-one, but checked then. I'm really stuck here. UR files not open in SQLite front-ends anymore, even without having fiddled "externally" with them in any way, that's a nightmare. Please check!


EDIT 3:

To clarify the "update" hint: on your download page
https://www.kinook.com/UltraRecall/download.html
you always just put the link to your "History page" in your help file, which is devoid of almost any interest; users AND prospects being interested in the most recent developments, and then you search, and search, and search... and then give up. I know there's a very precise, and very helpful, 6.xx history page somewhere, I'm just fed up to invest another 5 minutes, EVERY time, to get to that page... and prospects will certainly not search for that, more specific, up-to-date "history" page. Hence my suggestion, please make a todo, to update that "download page" history link, every time you do any (minor or major) update!

And please, tell me, is the "reasonable item count being about 50,000 items (when they are almost exlusively (formatted) text only)" (which you may contest, but which I inferred from extensive use of UR, with, in total, about 300,000 items of that sort), MOSTLY due to:
- SQLite limitations
- UR design
- 32bit vs. 64bit?

So is there a realistic chance that with your recent / current change from 32bit to 64bit, we could expect a real progress in UR's (practical, not theoretical only) "robustness" i.e. scalability? (I'm aware of the fact that for most single-seat users, i.e. the very big majority, 50,000 items is more than they will ever have to administer, but especially for little work-groups (and then for the minority of users like myself who got "more" items than "in general"), this would be a BIG step forward, also marketing-wise, relatively cheap work-group "information managers" being extremely rare...! (As said above, both linking / cloning (transclusion in general) and (db-specific, by SQlite 3 itself) full text search are "underwhelming" when you have to use a dozen or more UR databases, instead of using just one.) - if it's SQLite 3 itself which is responsible for those "reasonable limits", I'd understand we'd have to live with it, since it's obvious that we couldn't ask for a Postgres server instead, as UR's "back-office" (which would have, and also for free, just like SQLite 3, integrated full text search, too, and where half a million, or then a million, of items would not be a problem anymore.)

[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...)