SQLite3.exe, etc. problems

[Spliff]

1)

Signature back-change

Thank you, Kyle, for this clarification. I know this, in other, more general, wording (from which it could be interfered this should be two-way indeed), was touted some versions ago, but then, from my experience, this was one-way, UR being able to read the generic SQLite signature, but then overwriting it again, hence my problem.

Thus, in order to access it with a front-end - rarely, since such a fuss from my pov, I always ran a 010 Editor script in order to replace the UR signature with the generic one again: CRAZY!

I must have made a mistake once (mixing up the "original" UR file with its backup or such), and from then on must have wrongly assumed, just running the script (then "replacing" the native signature with the native one, i.e. doing nothing in fact) without looking anymore? (And of course, external front-ends won't open the files with the .urd suffix, that's understood.)

Sorry to have bothered you with my obvious mistake!


2)

SQLite3.exe special version

As for the weird lack of function of SQLite3.exe - my special UR version -, can you confirm what I have described?

a) .output stdout and (when it rarely works) .output |clip with ASCII-replaced, "unreadable" special chars äöüéàè etc - probably there is no solution?

b) .output |clip totally unreliable and not working most of the time?

c) .output filepath not working at all?

Since from my above snippets and links, it seems that I did it all right, so I can't understand those awful (non-) results b) and c) at least (as said, identical even executed from within an administrator command window)?

You special version is from 2020, so obviously, it's quite recent, not derived from some age-old native version... thus it should work as expected?

[kinook]

Will have to review, but I suspect that, despite the timestamp of the executable (which could just be a result of having signed the executable with a newer code signing cert), the version of code for our SQLite.exe is quite old and doesn't include those newer features.

[Spliff]

Thank you, Kyle, for looking into this. I had downloaded the sqlite-dll-win64-x64-3380500.zip
(896.46 KiB) 64-bit DLL (x64) for SQLite version 3.38.5.
from https://sqlite.org/download.html and had intended, after unzipping, to install the resulting .exe in parallel to my/your
c:\SqLite.exe, as
c:\SqLite64.exe,
in order to compare their behavior upon DBs,

being aware of course that while your exe works from inside UR, the "new" one would only work on UR files after renaming the suffix to ".db" or ".db3".

(Btw, you are absolutely right re the signature: almost all my UR files have born in fact the generic signature now, without my knowledge, me having run the same, now unnecessary script on them again and again!)

As for their aforementioned, "new" zip, against my expectations, this did NOT resolve to an .exe, but to a .dll, and double-click on that does NOT bring about a command-window executable, but a (basic) graphic user interface (!!!), and from their site, there is NO "modern" and generic version of the (old/older) executable you had compiled!

Since in-between (i.e. AFTER compiling the .exe), you have kindly resolved the "signature problem", it seems that another "special executable" version of yours will ONLY have the specific to also work on (otherwise generic) SQLite files with ".urd" suffix, which obviously is a much less "intervention" into their original code than you had to do before;

thus, it might be quite easy (?) for you to build a new .exe file, from their current source code? ;-)

(I might have overlooked that with a special command line, i.e. instead of just double-clicking on their .dll, triggering it within a command-window, together with special attributes, the .dll might work as originally expected by me, i.e. without showing the aforementioned GUI instead?)

[kinook]

If you go to

https://www.sqlite.org/download.html

and download the link labeled "A bundle of command-line tools for managing SQLite database files, including the command-line shell program..."

which is currently

https://www.sqlite.org/2022/sqlite-t...86-3380500.zip

it contains SQLite3.exe which is the latest version of the SQLite console executable.

[Spliff]

Thank you for the hint, Kyle, you're right again!

I had "overlooked", i.e. not cared about these, them being 32bit, but after all, UR is 32bit, so their 64bit .dll looked the "way to go"; I had wrongly assumed that .exe was an old one, but it's from May 6, 2022 indeed!

Of course, and as expected - I tried, in order to not spread fake news again -, it's not able to open .urd DBs, while they come with the .urd suffix, so this can't be used for some "quick change" while the db is open in UR.

And, obviously, the SQLite developers "update" all their stuff, including the 32bit .exe, regularly, so that whenever you, Kyle, update your special sqlite(32bit).exe version, it will not be up-to-date but for some weeks or months.

THUS:

Why not do it the other way round, here again: After UR's being able now to process generic signature sqlite DBs, why not also enable it to process them with the .db (or .db3) suffix?

The worst that could occur then, with a non-UR db: UR could crash (or even need re-install), and the (non-UR) db could become affected, too, BUT when we process a UR db with SQLite.exe or similar, we are deemed to know what we do, and when processing a non-UR db with UR, this should apply, too, i.e. non-"power-users" should be "protected" from such ab-use, here, too.

THUS: Why not do a REGISTRY entry with (default) 0 = no / yes = 1 for "Enable UR to open ".db" (or .db3", too, or only; I use ".db" currently, but we all could rename to ".db3", obviously), and THEN only, i.e. if the user manually changes that registry entry, UR will open those SQLite3-generically-suffixed UR databases?

Problem solved then, and we always could use the most recent sqlite.exe command line tool, without bothering you again - and for "regular" users, UR would continue to refuse to open non-".urd" DBs.

Btw, I really don't know what's their multiple problem(s) with their sqlite.exe versions (see the above-mentioned problems with the old version):

When I open a trial db with their latest sqlite.exe (which is deemed successful, with .open ..., and with or without .mode, .output), and then do
select "itemtitle" from "item" where "statusid" not null;
I invariably of "item" or "Item" and of quotes and double-quotes for the elements
(the correct SQLite syntax being identifiers in double-quotes, and strings/literals (values) in single-quotes),
I always get "Parse error: no such table: Item", whilst the same query in Navicat correctly lists the 311 results, and with äöü etc correctly displayed.

So this could be a WARNING that the intermediate use of ANY sqlite.exe upon a UR db could result in problems with the UR db in case.

It seems their sqlite.exe are NOT RELIABLE, unfortunately...

(By a quick switch between UR and sqlite.exe, in UR, it would be possible, for example, to "sort the immediate child items of the current (parent) item by some value of any other attribute", and then immediately, visually check the result, just for these siblings, thus avoiding, possibly unwanted, "global" sort results; such "minimalistic" proceeding is not realistic though if the user has to close the db in UR, open it in an SQLite front-end, close it in there, re-open it in UR, etc., etc. ...

The above sqlite(32bit).exe fault MIGHT be caused though by my using it in my 64bit W10 system, so that the use of their .dll then would be necessary now indeed and instead (and could possibly also used from the command-line).

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