SQLite3.exe, etc. problems

[Spliff]

1)

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_, hence the need to first replace this special signature with the regular one whenever the user opens a UR DB within another SQLite frontend.

From some UR versions ago, UR is able to open somefile.urd, even with the generic _SQLite format 3_ signature, but then again, stores this file with its own signature, so this new UR feature just spares the user the transformation ExternalFrontend > BackToUR, but not also the transformation UR > ExternalFrontend; it would be very helpful if UR then left alone the generic SQLite signature, upon any save of such a file; for UR not trying to open ANY SQLite file, the suffix .url instead of .db / .db3 should suffice:

It goes without saying that users would not be entitled to ask you, dear Kyle, for any help for UR DBs mixed-up by their use of external frontends, AND, that, for the user, just replacing the suffix .urd to .db, and then back from .db to .urd, would be MUCH simpler than to run an external binary editor, every time, in order to get their UR file "readable" by any external SQLite frontend / editor.

(I use 010editor (paid) for this conversion, and Navicat for SQLite (paid, now 16, and yes, it's the very best generic SQLite frontend there is) as frontend then; if you are in a situation similar to mine, you would probably have interest in buying RazorSQL instead (in spite of its multiple shortcomings), since aaronjsolomon, in this thread, https://www.kinook.com/Forum/showthr...ghlight=sqlite , very kindly (but "too late" for me, having discovered the thread just recently) shared his disvovery that "RazorSQL is a great RDBMS because it performs all of its functions via the command-line tool, which gives me an opportunity to point to your custom sqlite3.exe." - but see my point 2) below which then should also apply to RazorSQL's rendering of native UR DBs.

As said above, UR leaving out the "corrective" step of replacing the generic SQLite3 signature by its own, specific signature upon saving, would be extremely helpful, and, obviously, would be technically easy, all the more so since UR now is ABLE to open SQLite3 DBs with their generic signature, but just not willing to then also SAVE them, leaving their generic signature alone.

(Some contributors' posts being real contributions, you might be interested in getting Aaron's list here: https://www.kinook.com/Forum/search....archid=2958396 )

2)

Here again, I discovered brilliant (in theory) functionality quite late, since you, dear Kyle, have published a special SQLite3.exe version (SQLite3.exe being a command line SQLite tool) which is able to open / process your UR SQLite files - even when they are open in UR -, and even WITH those specific _Ultra Recall DB_ signatures (and with the .urd suffix), with myself having not been aware of these special abilities of it;

for example, in order to rename UR items' titles, this is a brilliant since incredible easy tool, sparing the user all the steps detailed above in 1), by just "doing it" within your special SQLite3.exe tool, while the UR remains open in UR, then doing a +f5 ("Refresh all"), back in UR, fellow UR users referring to

https://www.kinook.com/Forum/showthread.php?t=2825= "Accessing .urd files directly via SQLite"

Unfortunately, it seems that this special version isn't up-to-date, and while I have no problem to get command line output for selects (except that then, all special chars like äöü, etc., are not retrieved correctly), like for
select "itemtitle" from "item" where "statusid" not null;
I am not able to get any file output (and then hopefully with the special chars rendered correctly).

Fellow users should know, first, that, e.g.
run, c:\sqlite3.exe d:\ur\trial.urd
opens that file correctly, albeit from your command window prompt, you will not (yet) see it; you will have to enter
.databases
in order to get it confirmed, and thus, your "select" commands will function correctly, with command window output that is, and, as I have said elsewhere in this forum, for "free" access to UR files (AFTER the above-mentioned switch in its file signature though, currently), in order to identify the respective table names, etc., you could free SQLiteBrowser 3 = DB Browser for SQLite, or, much better, free (but with clipped functionality) SQLite Expert Personal.

Now, in SQLite3.exe, and according to Kyle's UR forum post, it should be possible to use file output, for such "selects", but for the heck of me, I don't get it; in SQLite.exe (command window), then:

.mode csv/column/line/tabs (etc)
.output d:\ur\trial.csv (e.g.)
select "itemtitle" from "item" where "statusid" not null;

That output file always remains empty, albeit:

- The same select will display a big list in the command window, so it's not the "select" that fails here

- I had created the output file before, "understanding" / assuming the above command would not create (???) it, in case

- ditto for
.mode line
and then
.output d:\ur\trial.txt
(with that target file also existing beforehand), etc.

- and
.once d:\ur\trial.csv (or similar)
even gives an sqlite3.exe error; it seems that .once has been excluded from its "dot" commands? (It's intended use would have been to set the output file just for the very next "select" command, further-down "selects" then being "outputted" to the command window again.)

Etc., so even if this special "UR" version of SQLite3.exe is sort of a "subset" (?) of its generic version, it's obvious we should get file (or clipboad) output of "selects" done in there, in order to then trigger the respective "update" SQL commands.

(See also https://www.sqlite.org/cli.html and https://www.sqlitetutorial.net/sqlite-commands/ )


It's obvious for me that UR should not revert to its specific "UR" SQLite file signature upon saving... the .urd suffix problem remaining then, but the user could close their UR file, rename it to .urd, re-open it in the SQLite frontend of their liking (which might even be SQLite3.exe then, provided it worked correctly (even for UR files), see 2) above), rename it again to .urd, then re-open it in UR:

Which would be a lot of fuss remaining indeed, but so much easier than the current way of doing things, including the renames AND the binary editor intervention, as it is at this time; and would it possible to "update" your special SQLite.exe version... or then, did I do something / some things wrong?

Thank you very much, Kyle!


EDIT:
See also https://stackoverflow.com/questions/...-as-a-csv-file with the same instructions, and with

"Use sqlite> .output C:/Users/jdoe/Documents/output.csv if you want to use a specific path. –
Dustin
Mar 29, 2016 at 14:01
Hi! I did this. Although my query worked perfectly, the file output is empty. Does someone knows why? –
Valeria Lobos Ossandón
Oct 22, 2018 at 13:44
@ValeriaLobosOssandón this just happened to me, so i thought I'd respond. Either you don't have rights to edit the output file (unlikely), OR if you are viewing the CSVs in Excel, and have another Excel file open, even with your test.csv file closed, Excel will still lock it. In that case you would have to close all Excel windows first."

where it's obvious that the explanation is faulty: the target file remains empty or not (your file manager tells you that), notwithstanding your then possible opening in Excel or not (or as I would then in Ron's Editor, paid, or free with less functionality and up to 1,000 rows).

And the .once filename/filepath target, which is mentioned "everywhere", is, as said, recognized as an SQLite3.exe error, and not listed in its .help.

(I admit I haven't also tried the generic SQLite3.exe version, since, if I have to leave UR and have use external programs, incl. signature changes within a third program, SQLite3.exe isn't my tool of choice... whilst used "within" UR, it could be of brilliant use indeed!)

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