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/showthread.php?t=5456&highlight=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.php?searchid=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/6076984/sqlite-how-do-i-save-the-result-of-a-query-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.

[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-tools-win32-x86-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/3044395/how-do-i-execute-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 (https://www.kinook.com/Forum/forumdisplay.php?forumid=27) 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...)

[kinook]

Ultra Recall is a Unicode application, and Windows uses UTF-16 encoding for its APIs.

https://docs.microsoft.com/en-us/windows/win32/intl/unicode

https://docs.microsoft.com/en-us/windows/win32/intl/code-pages

Text is converted to UTF-8 encoding when stored in the URD file.

[Spliff]

Thank you very much, Kyle, for this valuable information, so the "1252" that I read in the urd file "headers" (i.e. quite near to the "top" of the files' binary text) are not to be considered.

So it's clarified then, and sqlite3.exe's clipboard problem seems to be unresolvable, but that's not a real problem after all, and the way to go is:

- by sqlite3.exe, write the select output to file
- read (by AutoHotkey or similar) that file into clipboard or into variable

- do the necessary transformations (e.g. regexreplace for the itemtitle "column") in there (by AHK or else within the var)
- ditto in there (or in a second var, for better checking), transform the result lines into the necessary SQL code ("update tablename...") for sqlite3.exe to execute that

- write that SQL code into a file
(- check that file, create the necessary UR COPY at least now...)
- have sqlite3.exe read and execute that file content (.read filename) (onto the UR COPY...!)


Thank you so much again, Kyle, you've been of tremendous help!


(And obviously, the "there is no such table" error messages by sqlite3.exe I had reported here some weeks ago, must have been caused by the above-mentioned problem of the sqlite.exe ".open filename" command just creating a new, empty file (and without that table then indeed), when you make any typo or don't also enter the suffix, i.e. there is no "there is no such file, create it?" message from slite3.exe then, so you try to work upon an empty file then...)

[Spliff]

Just a detail.

Kyle, you said opening an saving the DBs in UR leaves the db signature, when changed to SQLite format 3, alone, and that's right, and this is fantastic!

"But Tools - Compact and Repair" then changes the signature back to Ultra Recall DB.

(And this explains why I had unexpectedly encountered "chaos" within my db signatures, after having them all changed to the generic SQLite signature in bulk.)

I hope that can be addressed, too? ;-)

[kinook]

There is not a way to prevent that. You would have to change the signature back after compacting. That shouldn't be needed very often.

[Spliff]

Sorry to hear that, all the more so since I made the observation that after lots of moving items around,

"Compact and Repair" seems absolutely necessary!

Here is my observation:

I had not "compacted and repaired" anymore, in order to avoid the above, it's my main db with less than 15,000 items, and sized about 0.6 gb.

Then, in order to get some "intermediate order" at least into my "Inbox" in that - it had grown way faster than I had been able to "file" the items in it, thus had grown to near 2,000 items - I did a "preliminary filing" for its contents, by filing it all, in the meanwhile, not into dozens of real filing targets, but into 5, 6 intermediary ones (parent items = filing targets in the inbox, too, instead of outside of it, as the real targets are, so from a very long item list in the inbox, I went to a short list, with just some items "holding" (and thus "hiding from view") long lists of child items).

This was some days ago, and no problem after. Today then, from one day to the other, new item creation took, instead of less of a second, about 15 seconds for sibling, and 8 seconds for child items, invariably; of course this broke external macros of mine relying on fast item creation.

After compact-and-repair, which, as expected, did not "compact" the db as for its 0.6 gb size since what had been in the db had remained in it, item creation time went back to normal, so it seems that "item moves in numbers" create lots of overhead which then can be discarded again by "compact-and-repair".

Thus, avoiding it, as I had done for the above reason, obviously is counterproductive.

[Spliff]

My UR-SQLite problems have gone in its nth iteration it seems, none of my UR DBs opened in my SQLite frontend anymore, even after re-install of the latter (new Navicat vesion anyway, from 6.0something to 6.1). (Ditto for previous Compact-and-Repair, and then the necessary signature change, of course.)

So I re-installed UR, too (for the nth time), opened the DBs in UR, closed them again after some changes, i.e. after real "save": no chance.

BUT with the re-installed UR, AND after compact-and-repair (in that), then signature change of course, the same UR files opened in Navicat without any problem.

HENCE:

Something on my system obviously deletes / changes some dll or whatever file, so that UR will NOT save my UR files with a correct header, I suppose (my UR files' headers are much longer of course than just the signature); UR will then continue to open those UF files correctly, but the generic form of the header is gone, just my assumption.

Now for a possible culprit: I regularly use (paid and up-to-date) "WiseCare 365", in order to get rid of (each time) thousands of crap files and crap registry entries, and since there are so numerous, I really need that "cleaner" tool.

This is just an assumption of course, something other also could do the change / deletion which then makes that UR obviously does not store my UR files in the generic SQLite 3 format again, so probably this description could help you with identifying the UR problem? (Everything else always works as expected.)

Now for the header, the problem is, those headers are very different from one UR file to the next (except for the signature of course), so in theory I could copy some headers for non-working (= as SQLite DBs) UR files, and of the others that work; this would imply some effort but if that can be helpful I could try, even with the "same" files "before and after" the treatment with the fresh UR install; by my theory, the headers should be different, but since I don't know what those header hyroglyphes "mean", I could not say if the changes then just come from compact-and-repair, or from the alleged fact that fresh UR writes other, correct SQLite headers, than the allegedly incorrect ones non-fresh-UR may write.

But it seems obvious to me that very probably, UR relies upon some "external" dll or such, for writing the headers upon "save", and that some third-party thing in my system changes or deletes that.

For example, the similar tool "RightNote" is said - I have never used it, does not appear "good enough" to me - to write generic SQLite from start on, without the need to do the obvious changes in UR to that effect.

Of course, next time I run WiseCare 365, I'll try before and after, in order to exclude that, in case, but the fact that UR does not write generic SQLite but its own, somewhat different format, seems to be at the heart of the problem, since it then enables the secondary problems to appear.

To make this perfectly clear from above: UR-re-install does not help, Compact-and-Repair does not help, but UR-re-install and immediate Compact-and-Repair after that, with the fresh UR re-install, does help, i.e. does produce the UR file in a format which then, with the necessary signature change in-between, will open in SQLite frontends.

(Ditto of course for the SQLite command line tool, with the additional problem in there that, as explained above, it will not give an error message but will just open some "memory" db in which then the user's sql commands will generate multiple error messages and/or non-"finds".)

All this is of course absolutely awful, I really never know "before" if my UR db "accepts" SQLite commands or if it's "not an SQLite file" again, and again, and again... ;-)


EDIT:

I just tried, Wise Care 365 (WC) is not the culprit:

I made changes in 2 files, in UR, then ran WC while UR was running. I then opened the files in Navicat without problem.

I then made further changes in UR, then closed UR (with the files), then ran again WC, then again opened / run UR again, and opened the files in UR (which now might have been affected by the two WC runs), made changes, saved, closed UR; again I was able to open the files in Navicat without problems.

Hence: Something else running on my system (I don't use another "cleaning" tool though) "fiddles" with something UR reaches out to, and then UR writes its files not in the correct generic SQLite format anymore, and then fresh UR install AND compact-and-repair with that fresh UR install will be necessary, in order to get generic SQLite format again (after then-necessary signature change).

I ALSO want to clarify that the problem is not due to the Navicat installation since I FIRST re-installed the (also updated) Navicat version, without any "progress" in the above-mentioned problems, then only re-installed UR, and even then, the problems persisted (now with new and updated Navicat) for every UR file which I had not yet "compacted and repaired" with the NEW UR install. Thus, some third-party software seems to affect some external file UR seems to need, and without the UR routine making the call becoming "aware" of that change, causing the misfunction of what UR "writes" then, be it in the header or elsewhere... and without that affecting the internal UR processing of the UR files, they are just not generic SQLite anymore then.

TO CLARIFY what above is implied:

If you can give a list of files of which a probably change by third-party software probably might be responsible for the above behavior, I could then copy those files, after a fresh UR installation, into a "safe" folder, in order to then compare them daily with the possibly changed files in their original position.

It would then be of interest, after UR's expected "SQLite fail", to just copy / write those "saved" files back over the "changed originals", then check if UR's "SQLite fail" persists, without doing a complete UR re-installation; thus, we could identify the file(s) which are responsible for the above (and perhaps I could even identify the culprit third-party software which changes that file(s)).

[kinook]

UR does not rely on external DLLs for reading/writing of URD DB files. The SQLite library is statically linked into UltraRecall.exe. And the only difference between a UR-only file and a SQLite file that other tools can open is the first 15 bytes being

Ultra Recall DB

vs.

SQLite format 3

You can open a URD file in a hex editor and change those first 15 bytes if they get changed by UR.

[kinook]

In the latest download (v6.3.0.1), compacting a database will write the 'SQLite format 3' signature rather than 'Ultra Recall DB'.