SQLite3.exe, etc. problems

[Spliff]

This morning, I tried with an ADMINISTRATOR command prompt:

c:\sqlite3.exe d:\ur\trial.urd
.mode line
.output d:\ur\trial.txt
select "itemtitle" from "item" where "itemtitle" like "a%";

The target file remains empty, be it an existant file, or a new file, to be written by Sqlite3.exe; ditto for
.mode csv
and (new or existant) files something.csv.

The same, with
.output stdout
displays the list of the respective item titles in the command window, in the form of
ItemTitle = Then the respective title
ItemTitle = and so on

The same, with
.output |clip
puts the same list (with not even 100 entries (i.e. the titles beginning with "A" or "a", so it's really not "big"), SOMETIMES, RARELY, into the clipboard, but most of the time, the clipboard remains empty (if I have emptied it before) or just contains the content from before; I leave the system (i7, 16GB RAM, of which more than 10 GB are free) alone many seconds, in order for sqlite3.exe to fill the clipboard, then only try to access the clipboard (by ^v into an editor).

In both success cases (always to stdout and rarely to clipboard), all the special chars äöü, etc., are rendered in the ancient ASCII way - can't say about file output since never successful.


This "special" version of SQLite3.exe not working as expected - even the clipboard output being totally unreliable, "working" perhaps in 1 case out of 10 -, why not UR leaving alone the generic SQLite signature if the user has changed it in a binary editor, once-and-for-all (!), to generic then:

Which would mean that the user could then close those UR files, just rename the suffix from .urd to .db, open the files within a RELIABLE SQLite frontend of their choice, rename them back from .db to .urd, and re-open them normally within UR, without having the binary change problem (not anymore back into UR but always) out of UR, for external opening;

with the non-expert UR user just opening their UR files within UR, whilst their .db or .db3 files would be refused by UR (as a persistent UR security measure), just as it is today?

(And as for internal SPECIAL SQLite3.exe version use from within SQL Razor, since this special SQLite3.exe version at the very least is to be considered unreliable in at least several respects, I suppose this special version will not behave any better from within SQL Razor... hopefully, the generic version will (?!), but with that one, SQL Razor doesn't present any advantages over Navicat, etc., with respect to UR files anymore...)

(As for the special chars, that problem might possibly be resolvable by changing the console code page - I haven't delved into this possible Windows 10 problem...)


EDIT:
To clarify: With UR leaving alone the generic SQLite signature, in case, there would be TWO security measures for the non-expert user, preventing changes outside of UR: the .urd suffix, AND the (persistent) UR signature; whilst for the expert user, ONCE they will have changed the UR signature, once-and-for-all, there will remain ONE security measure, the .urd suffix, which external SQLite frontends will reject.

[Spliff]

In-between, I downloaded your SQLite3.exe special version again = your .zip, extracted that, too, and then did a hex compare (with Beyond Compare, paid) between my c:\SQLite3.exe (from my first download) and the new SQLite3.exe, in my Downloads folder. Both are 479.232 bytes, and BC did not display any difference between the two.

This should exclude a download error for the above-mentioned misses in SQLite3.exe (special version) functionality, and of course, I ask myself if table "update" sql commands by this will then really be reliable in every case (understood that in real use, these would be the commands executed by SQLite.exe, e.g. title changes or even conditional table updates, with updates in table a if conditions are met in table b - if SQLite3.exe special version wasn't totally reliable with those, its use could indeed create havoc, which might not become obvious in time (so that any previous backups will not be that helpful, weeks or months later on) - since even the export does not work correctly, as described above, so that no table / records compare immediately before and after - with an external tool upon partial dumps will not be possible).

Thank you very much for your help, Kyle!

[kinook]

UR DBs are SQLite3 DB's, but with .urd suffix, instead of .db / .db3 suffix, and with their special UR signature, i.e. first 15 bytes of the DB being _Ultra Recall DB_ instead of _SQLite format 3_

That is correct.

UR is able to open somefile.urd, even with the generic _SQLite format 3_ signature

Also correct. And UR can open UR format files (with either signature) if they have another extension (i.e., .db, .db3) or no extension.

but then again, stores this file with its own signature.

Incorrect. UR accepts either signature, and it does not update the signature of an existing file. So after you change a file (once) to the standard SQLite format 3 signature, you can open/edit it in UR and other SQLite tools from there on out.

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