Command: sqlite3.exe -header -csv .database.db "Select * from Table;"

Results in: Error: in prepare, no such column: %03d

For latest sqlite3.exe [SQLite version 3.41.2 2023-03-22 11:56:21] downloaded yesterday 2023-03-28 directly from SQLite.

Through the grace of God a coworker had an older version - it appears that is the only way to get older code safely... otherwise you have to either roll the dice on the black market and infest yourself with some viral load or put on a wizard hat and magically transform yourself in to a CODE GOD to "compile it yourself" (its SO easy! - sure it is... and so is brain surgery after you have had the training).

Versions: v3.20.1 (2017-08-24) v3.32.3 (2020-06-18)

Both execute the command above with no issues and return the data in the expected format.

Thought someone might want to know...

I'm unable to recreate the problem. Anybody else have any luck?

Why is there a dot before database.db?

• If the database is where sqlite3.exe is there is no need for the dot. (at least with Windows)

• Else the relative path is missing.

• Or is SQLite simply seeing .database.db as a dot command?

OH! Nope... wasn't my fault.

When you post the 'slashes' are removed. So you have to do your own interpretive backslashing ;)

When you post the 'slashes' are removed.

See the markup help, linked to at the top of the edit field when writing/editing posts, for how to avoid that.

Thanks - considering the pain I endured already: 1 - Troubleshooting and working out what should have been a simple command line. (To be fair - Google and this site made that pretty easy... which is where I got sucked in thinking this was rich, mature, and "easy"). 2 - Testing and finding failure on command line. (simple syntax errors that were mine from not being familiar with the tools) 3 - Validating SQL in DB Browser (DB opens, doesn't throw corruption errors, and simple SQL statement returns data) 4 - Making the assumption that something in v3.41.2 may be at fault and after wasting 45 minutes on the above, spending another hour looking for an older version on the web and finding ... no, a legitimate release was not available on the website or github or in some fashion from something called "Fossile" (something else to learn and another multi hour time sink) - I was at least able to procure the binary from a "trusted friend" and validate it did not exhibit the issue in an older version.

Yeah... I'm pretty content just dropping this here and saying I've had a challenge with v3.41.2 - I don't know why it throws a "no such column: %03d" - it doesn't Google well - but hopefully this will make it easier for "future peeps" - and using an older version (good luck!) resolved my issue. (For now - I probably have exposed myself to all sorts of other issues using an older version - I'm sure I've broken the time continuum somewhere and future me will come back and smack me with a wet trout).

Yeah. With all that - I don't need to learn the eccentricities of markup on the SQLite forum page... at least not today. I'm sure I'll be forced to eat my shoe on that statement.

Regarding the question of older versions: I don't know if it is officially documented, but older versions of the amalgamation, tools and other "official release" files have been available for a long time from the official SQLite site by tweaking the download URLs to refer to older versions. When the web site is updated for a new release, the new files are added and the download page points to the new version, but the previous download files are still there, no longer linked from the web pages.

The starting point is the list on the official download page.

For example, the current release of the tools for Windows has this URL:

https://sqlite.org/2023/sqlite-tools-win32-x86-3410200.zip

Note the first element after the domain name is the year of release (2023), and the version number encoded near the end of the filename: 3410200 is SQLite 3.41.2.0 (or just 3.41.2).

An earlier version can be downloaded by identifying two key pieces of information:

1. The exact version number you want, encoded in the same format as above (3 followed by six digits with two per element of the version number).

2. The year that version was released. To find this, go to Prior Releases, which is linked from the home page via the Prior Releases button, or Complete History, which is linked from the page with the release notes of the current version.

For example, if I want to download the Windows tools for SQLite 3.40.0, the above references tell me that version was released in 2022, therefore the expected URL Is:

https://sqlite.org/2022/sqlite-tools-win32-x86-3400000.zip

which works.

Much older versions get harder to locate due to details like base filenames and other parts of the URL changing, or certain files only existing from a particular version. Without trying hard I was able to get version 3.16.0 (2017) of the Windows tools using the above technique, so the last five or six years are fairly consistent.

Even older versions required referring to the Internet Archive to see what had changed. This allowed me to confirm that downloads are still hosted on the SQLite official web site as far back as version 3.5.2 (late 2007), but it appears versions 3.5.1 and earlier are no longer there.

I can't reproduce it either.

D:\test>D:\bin\sqlite3410200\sqlite3.exe -version
3.41.2 2023-03-22 11:56:21 0d1fc92f94cb6b76bffe3ec34d69cffde2924203304e8ffc4155597af0c191da

D:\test>D:\bin\sqlite3410200\sqlite3.exe .\test.sqlite  "CREATE TABLE T ( id INTEGER PRIMARY KEY NOT NULL, tx TEXT)" "INSERT INTO T (tx) VALUES ('one'),('two'),('three')"

D:\test>D:\bin\sqlite3410200\sqlite3.exe -header -csv .\test.sqlite "Select * from T;"
id,tx
1,one
2,two
3,three

David - THAT is an excellent explanation. - A heartfelt Thank YOU!

I earnestly hope someone pokes the site admin and inserts that gem in the downloads section.<

The 2023 was pretty obvious (grin) - but I did not make the ready connection as to what the numbers at the end represented or how they could be manipulated to retrieve an older version. For the SQLite newbie the version information did not readily present itself.

IA is another good suggestion - I usually have to come at it the other way - lookng for sites that don't exist - for those sites that are still online and are well maintained I rarely think about it - admittedly I was rather grumpy about the obscured resources yesterday and really just wanted to fling a bug report after that frustration so that someone could delve deeper into it.

Regarding the question of older versions: I don't know if it is officially documented, but older versions of the amalgamation ...

It is documented on the download page

The download page documents the encoding of the date in the filenames, but doesn't mention that you can still download older versions, nor other important details like the year in the URL, expectations of this structure being maintained in future, retention of old versions, or historic changes in the URL structure and base filenames.

I can see the point of not making it easy to download old and obsolete versions by providing links to them, but at least an official policy and text description would be nice.

Those who have the right tools and sufficient understanding can of course build specific old versions from source by extracting from Fossil (or the Git mirror), but the pre-built amalgamation and executables make it easier.

Aha, now I see what you mean, yes, it's not explicit about older version being downloadable.

I track the sources (sqlite and sqlitedoc) with fossil. It is the most server load and bandwidth friendly way.

Hello all -

Thanks to the bountiful information I wanted to see if I could provide feedback at least on where the issue was introduced.

It appears somewhere between 3.40.1 and 3.41.0 something changed.

The command line that I'm using for testing under PowerShell:

I've been altering $tool to point to the following directories+sqlite3.exe: 'PATHTO\SQLite\sqlite-tools-win32-x86-3201000\sqlite3.exe' 'PATHTO\SQLite\sqlite-tools-win32-x86-3390400\sqlite3.exe' 'PATHTO\SQLite\sqlite-tools-win32-x86-3400000\sqlite3.exe' 'PATHTO\SQLite\sqlite-tools-win32-x86-3400100\sqlite3.exe' 'PATHTO\SQLite\sqlite-tools-win32-x86-3410000-broken\sqlite3.exe' 'PATHTO\SQLite\sqlite-tools-win32-x86-3410100-broken\sqlite3.exe' 'PATHTO\SQLite\sqlite-tools-win32-x86-3410200-broken\sqlite3.exe'

$query is: 'Select *, DATETIME(SUBSTR(timestamp_raw,1,10), 'unixepoch', '-5 hours') as timestamp_localdt from FileData where timestamp BETWEEN 1681317303084 and 1681317901865'

So I have been using in PowerShell the Up arrow to just re-execute this: '$latestdata = & $tool -header -csv 'C:\path\to\my\data.db' $query'

(it keeps things simple)

My directories above are renamed with '-broken' as I worked through them.

Again the output from the query is: 'Error: in prepare, no such column: %03d'

I hope that helps someone pinpoint where this 'changed' - for others that Search and get here - try version 3.40.1 :)