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!)