I'm Using SQLite3.EXE on Windows with the window maximised (that is, the size of the window has no bearing on this):

SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
sqlite> .open './db/chinook.db'
sqlite> .mode column
sqlite> select * from albums where albumid = 213;
AlbumId  Title                                                         ArtistId
-------  ------------------------------------------------------------  --------
213      Pure Cult: The Best Of The Cult (For Rockers, Ravers, Lovers  139
          & Sinners) [UK]
sqlite>

1. The string for Title wraps i.e. uses two lines.
2. The column name underline string for Title is shorter than the width of that column.

a. How do I prevent column wraps?

b. Any way to coerce the underlining of column names to be as wide as the column content?

When I redirect stdOut, I receive the same data as in the CLI; this is not surprising.

Set the column widths with the .width command.

sqlite> select * from album where albumid = 213;
┌─────────┬──────────────────────────────────────────────────────────────┬──────────┐
│ AlbumId │                            Title                             │ ArtistId │
├─────────┼──────────────────────────────────────────────────────────────┼──────────┤
│ 213     │ 'Pure Cult: The Best Of The Cult (For Rockers, Ravers, Lover │ 139      │
│         │ s & Sinners) [UK]'                                           │          │
└─────────┴──────────────────────────────────────────────────────────────┴──────────┘
VM-steps: 17
Run Time: real 0.011 user 0.000000 sys 0.000000
sqlite> .width 0 80 0
sqlite> select * from album where albumid = 213;
┌─────────┬──────────────────────────────────────────────────────────────────────────────────┬──────────┐
│ AlbumId │                                      Title                                       │ ArtistId │
├─────────┼──────────────────────────────────────────────────────────────────────────────────┼──────────┤
│ 213     │ 'Pure Cult: The Best Of The Cult (For Rockers, Ravers, Lovers & Sinners) [UK]'   │ 139      │
└─────────┴──────────────────────────────────────────────────────────────────────────────────┴──────────┘
VM-steps: 17
Run Time: real 0.011 user 0.000000 sys 0.000000
sqlite> .mode column
sqlite> select * from album where albumid = 213;
AlbumId  Title                                                                             ArtistId
-------  --------------------------------------------------------------------------------  --------
213      Pure Cult: The Best Of The Cult (For Rockers, Ravers, Lovers & Sinners) [UK]      139
VM-steps: 17
Run Time: real 0.010 user 0.000000 sys 0.000000
sqlite>

I think the assumption behind the intended behavior you see is that query results are not to be truncated but some usually reasonable formatting defaults apply.

You can pick column widths other than the default. And, given that you are fond of single-line row display, you can put a string truncation into your query to fit.

Examples: create table t(id int, title text); insert into t values(1, 'some longish title that is unlikely to fit many consoles, as setup or defaulted.'); .mode column .width 5 90 select * from t; . This produces: id title ----- ------------------------------------------------------------------------------------------ 1 some longish title that is unlikely to fit many consoles, as setup or defaulted. .

Truncating: .width 5 60 select id, substring(title,1,60) as chopped_title from t; , producing: id chopped_title ----- ------------------------------------------------------------ 1 some longish title that is unlikely to fit many consoles, as .

There is:

• no column wrap
• the column name underlining is as wide as the column content

when the select statement returns fewer characters per line:

sqlite> select albumid,title from albums where albumid = 213;
AlbumId  Title
-------  ----------------------------------------------------------------------------
213      Pure Cult: The Best Of The Cult (For Rockers, Ravers, Lovers & Sinners) [UK]
sqlite>

Is there a setting or dot command that will override the default behaviour that causes column wraps?

I am aware of the .width workaround or feature, if you prefer.

I don't think this is workable principally because I cannot use .width with its right hand argument determined dynamically (perhaps another SQL statement that queries the maximum len(everyColumn))

To me it appears that column wraps are triggered when the row/line exceeds a certain width.

What is that threshold width?

Can I elect not to have column wraps? Via some dot command setting?

Consider this:

sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: on
        mode: column --wrap 0 --wordwrap off --noquote
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    filename: ./db/chinook.db
sqlite>

Note the line:

   mode: column --wrap 0 --wordwrap off --noquote

Does that suggest that I can control column/word wrapping? Perhaps using some SQLite3.EXE start-up parameter?

Update

sqlite> .mode column -wrap 0
sqlite> select * from albums where albumid=213;
AlbumId  Title                                                                         ArtistId
-------  ----------------------------------------------------------------------------  --------
213      Pure Cult: The Best Of The Cult (For Rockers, Ravers, Lovers & Sinners) [UK]  139
sqlite>

This turned off column wrap.

sqlite> .mode column -wrap 0

.width has its uses/place but is unwieldy.

You can control line wrapping with the .mode command. Try .help mode

The default for mode column is --wrap 60 --wordwrap off --noquote

If you wish a specific set of settings, then you set them:

.mode column --wrap 0

Of course, this will depend on the version of the SDI (SQLite3 Debugging Interface aka CLI) you are using.

THe application will process the .sqliterc file contained in your home directory whenever the CLI application is started.

You may also put "stuff" in just any file located anywhere and cause it to run when the application is loaded by using the -init <filename> command line parameter.

Processing is somewhat analogous to the .read command, but not exactly the same.

If you have a .sqliterc file, you can start the CLI and tell it to process an alternate -init file. If the file is a "null stream", then no -init will be processed (a null stream would be /dev/nul on systems which use /dev/nul as the null stream (such as Linux), else whatever is the null stream on the operating system in question (NUL on CP/M, DOS, Windows, NT, etc), or an empty unit on MVS.

Need some clarification, please:

in your home directory whenever the CLI application is started

With Windows, is that c:/users/myName or c:/users/myName/AppData?

Is there more documentation than in 22. Command-line Options on command line options?

Rather than .sqliterc route, I'd like to use the command line options in a shortcut for SQLite3.EXE (because a. it is more transparent b. quicker to see the effect).

Unlike other exe's it is not possible to get the command line options for SQLite3.exe using sqlite3.exe /? as it is for ,say, dir /?

I am about to figure out the .sqliterc route: is there more documentation on this than what you have already shared?

Tried .sqliterc successfully

The SQLite3 session shows the explicit result, where available, of each line without echoing the line itself.

.sqliterc gets processed whether I start SQLite3.EXE directly (double-click) or via the shortcut I have set up. However, there is a difference in the session.

Using the shortcut, above the cursor line I get:

SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
sqlite> .databases
main: D:\SQLite32\db\chinook.db r/w
sqlite>

Using double-click on sqlite3.exe, above the cursor line I get:

SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .databases
main: D:\SQLite32\db\chinook.db r/w
sqlite>

Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database.

Is a little disconcerting especially since there is a database open as reported by .databases.

Or, have I got the wrong end of the stick: is the database loaded in memory?

I spent way too much time on this (very messy) kludge ! I had kinda hoped it would be easier - was going to try an use params , but that didn't pan out. (.commands don't seem to see the @a, $a etc values unfortunately - if they did, then it might have been easier - by writing directly to the 'sqlite_parameters' table).

I seemed to have discovered something weird in doing-so - I'll log a separate post about that. (joining on 'pragma_table_info(t)' seems to be affected by order...)

.version
-- uncomment the following 3 lines if you already have an 'albums' table: otherwise it will get zapped :-/
-- DROP TABLE IF EXISTS albums;
-- CREATE TABLE albums(AlbumId PRIMARY KEY NOT NULL,Title,ArtistId);
-- INSERT INTO albums VALUES(213,'Pure Cult: The Best Of The Cult (For Rockers, Ravers, Lovers & Sinners) [UK]',139);

--  urgh
DROP TABLE IF EXISTS temp.widths;
.mode col
.head off
.width 0
.once get_widths.sql
WITH
        _tab(t)            AS (VALUES('albums')),
        _char(nl)          AS (VALUES(char(10))),
        _sql(s)            AS (SELECT 'SELECT MAX(LENGTH(%s)) AS s FROM '||t FROM _tab),
        _comp(c)           AS (SELECT GROUP_CONCAT(PRINTF(s,name),nl||' UNION ALL'||nl)
                               FROM _sql
                               CROSS JOIN _tab
                               CROSS JOIN _char
                               CROSS JOIN pragma_table_info(t)) -- has to be last?
SELECT 'CREATE TEMPORARY TABLE widths AS '||nl||c FROM _comp CROSS JOIN _char;
.read get_widths.sql
.once set_widths.sql
SELECT '.width '||group_concat(s,' ') FROM temp.widths;
.read set_widths.sql
.head on
.mode col
SELECT * FROM albums WHERE AlbumId=213;

Output:

sqlite> .read wrap.sql
SQLite 3.39.0 2022-03-10 23:37:58 531e6ad0389c6c820bb8c64db5049fb6b0bffd30bd394fd8ee7412959b1752e2
zlib version 1.2.11
gcc-9.4.0
Alb  Title                                                                         Art
---  ----------------------------------------------------------------------------  ---
213  Pure Cult: The Best Of The Cult (For Rockers, Ravers, Lovers & Sinners) [UK]  139

Alas, I could not get this to work using Version 3.38.2 on Windows; see below.

As I understand, in essence, you have managed to determine the argument to .width programmatically. Am I correct?

Some useful techniques in your script; thanks. However, I prefer the simplicity of how I managed it.

sqlite> .version
SQLite 3.38.2 2022-03-26 13:51:10 d33c709cc0af66bc5b6dc6216eba9f1f0b40960b9ae83694c986fbf4c1d6f08f
zlib version 1.2.11
gcc-5.2.0
sqlite> DROP TABLE IF EXISTS albums;
sqlite> CREATE TABLE albums(AlbumId PRIMARY KEY NOT NULL,Title,ArtistId);
sqlite> INSERT INTO albums VALUES(213,'Pure Cult: The Best Of The Cult (For Rockers, Ravers, Lovers & Sinners) [UK]',139);
sqlite>
sqlite> --  urgh
sqlite> DROP TABLE IF EXISTS temp.widths;
sqlite> .mode col
sqlite> .head off
sqlite> .width 0
sqlite> .once /tmp/get_widths.sql
Error: cannot open "/tmp/get_widths.sql"
Error: cannot write to "/tmp/get_widths.sql"
sqlite> WITH
   ...>         _tab(t)            AS (VALUES('albums')),
   ...>         _char(nl)          AS (VALUES(char(10))),
   ...>         _sql(s)            AS (SELECT 'SELECT MAX(LENGTH(%s)) AS s FROM '||t FROM _tab),
   ...>         _comp(c)           AS (SELECT GROUP_CONCAT(FORMAT(s,name),nl||' UNION ALL'||nl)
   ...>                                FROM _sql
   ...>                                CROSS JOIN tab
   ...>                                CROSS JOIN _char
   ...>                                CROSS JOIN pragma_table_info(t)) -- has to be last?
   ...> SELECT 'CREATE TEMPORARY TABLE widths AS '||nl||c FROM _comp CROSS JOIN _char;
Parse error: no such table: tab
sqlite> .read /tmp/get_widths.sql
Error: cannot open "/tmp/get_widths.sql"
sqlite> .once /tmp/set_widths.sql
Error: cannot open "/tmp/set_widths.sql"
Error: cannot write to "/tmp/set_widths.sql"
sqlite> SELECT '.width '||group_concat(s,' ') FROM temp.widths;
Parse error: no such table: temp.widths
sqlite> .read /tmp/set_widths.sql
Error: cannot open "/tmp/set_widths.sql"
sqlite> .head on
sqlite> .mode col
sqlite> SELECT * FROM albums WHERE AlbumId=213;
AlbumId  Title                                                         ArtistId
-------  ------------------------------------------------------------  --------
213      Pure Cult: The Best Of The Cult (For Rockers, Ravers, Lovers  139
          & Sinners) [UK]
sqlite>

whether I start SQLite3.EXE directly (double-click) or via the shortcut I have set up. However, there is a difference in the session.

I can see that the output you cite is a bit confusing under the circumstances.

The extra, cautionary text and advice about "transient" and "persistent" is something that was found useful for naive users who see sqlite3.exe and double-click on it, interact with the CLI, then exit. They would then come here asking "Where are my tables and data?" or similar.

You have, apparently, made your shortcut tell sqlite3.exe to load a DB. That is why there is no mention of the DB being transient. But when you simply click-launch the sqlite3.exe, it gets no arguments and starts with an in-memory DB open.

I do not know how your "double-click on sqlite3.exe" could work as shown. Without arguments, and without running .sqliterc to load a DB, there should be no DB open except the default, in-memory DB. That's when the warning shows up in the banner. When .sqliterc is found and read in, a message is emitted saying "-- Loading resources from ...". Since I do not see that, I wonder how that DB listed by the .databases command was loaded.

have I got the wrong end of the stick: is the database loaded in memory?

Some in-memory DB was created. But when the .databases listing names the DB file, the DB is in that file. I think maybe the banner emission could be improved, but without knowing what you are actually doing (beyond the clicking you mention), I am not sure why you see (only) what you report.

Oops - filenames were hard coded to Linux. Change the “/tmp” paths to windows style - and it should work.

Thanks for your prescription.

The easiest solution: Don't abuse the SQLite CLI as your presentation layer.

However, I do not see how I am abusing the CLI when I query why it is wrapping column calues. As it happens, and as I reported, I found the solution myself

Use the C Api (maybe together with a wrapper for your favorite programming language) and roll your own presentation layer.

I have done that also (and it was no picnic) primarily because I wanted to be able to choose the version of SQLite I use, and where I wanted to store the SQLite library, tools and executables. Besides:

1. The Werner ODBC driver is statically linked to a particular historical version of SQLite.
2. System.Data.SQLite is also statically linked to a particular historical
3. Microsoft.Data.SQLite statically linked to a particular historical

The static linking would not matter but for these facts:

a. I have no control on the timing of updates.

b. It prevents me from using the latest features.

I edited the original.

• There was a typo 'tab'-> '_tab'.
• I changed FORMAT to PRINTF ('format' is an alias found in later versions of sqlite only)
• Changed the filenames : removed the path element.
  • This will litter-up the working directory.
• Commented-out (and commented-on) the initial DROP/CREATE/INSERT lines.
  • Apologies to anybody using the 'chinook.db' and now find they are only able to see a single album by The Cult in their DB. ;-)

But as you say: the option

.mode column -wrap 0

Fair point; but a great thing about sqlite is that is lets you do so much with just the CLI tool itself and finding out the limits is perhaps fruitful for new ideas. (The whole exercise was unnecessary it seems in this case: since the option already exists to switch off wrapping!).

In terms of new ideas: maybe exposing all the current dot-command options in a table-like format might be something worth considering in a future version. For example - the '.mode' command already usefully shows the current mode:

sqlite> .mode
current output mode: column

And you can list all the options with '.show'

sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: on
        mode: column
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width: 
    filename: :memory:

But it might useful if this was available as a table (updateable) - to allow more dynamic configuration (or checks , or reporting).

(In fact; it seems the whole exercise was unnecessary - since the option already exists!)

Unnecessary? Look at the the first two responses from those I consider to be authorities on SQLite.

Response 1: circa 36 minutes after my post/query

Response 2: circa 37 minutes later

Conclusion: column wrapping in the CLI is hard to notice and the setting to prevent it is equally obscure.

On the bright side, I solved my problem and the solution is transparent for others.

I edited the original.

Thanks for editing the original. I confirm that it works for me (Windows 11).

Your script also gives me valuable insights.

However, I do not see how I am abusing the CLI when I query why it is wrapping column calues

Perhaps abusing was a strong word, what Gunter correctly tried to convey is not that you are abusing the system, but that the SQLite CLI is intended to be a raw, programmer/admin-oriented tool for direct access to SQLite. It adds some niceness for displaying to make life a bit easier for programmers/admins who need to see their data. Now while the SQLite maintainers do often fix bugs in that presentation layer and do in general make it nicer to use, by no means do they spend hard rigor and testing on ensuring those niceness features work flawlessly for some intended general audience. Not at all.

In other words, using it for the purpose of flawless display characteristics to a general audience would be using it outside the intended purpose (i.e: abusing it).

Further to this, I have to say, using SQLite's C api is quite a picnic in C. Much more friendly than a ton of other APIs. I can't speak for the wrappers or C# of course, but I'm sure you could devise a base set of C# functions to access the library directly and so either use compile-in the SQLite code or use a library (I presume .DLL) adjacent to your executable. Usually this is not needed since the wrappers are quicker and easier, but you seem to have a real use-case for using the latest library.

I know it is some work to do, but I bet it is faster than trying to massage the CLI output into your liking.

BTW. to anyone else reading this - I don't use C# so this might be a very ignorant question, but I've searched for examples of direct SQLite C-API calls in C#, which I imagined would be ten-a-penny, but not finding much - are there some good open source examples somewhere? Can it be done?

The CLI is useful for prototyping, for testing out bits of SQL, examining a row/col to see precisely what is stored there and other developer-oriented tasks. Unless they are developers themselves, users will not expect to be presented with CLI output.

I view these table-oriented modes as nice frills, mostly.

I don't use C# so this might be a very ignorant question, but I've searched for examples of direct SQLite C-API calls in C#, which I imagined would be ten-a-penny, but not finding much - are there some good open source examples somewhere? Can it be done?

I don't know for certain which language was used to build System,.Data.SQLite and Microsoft.Data.SQLite. These greatly facilitate the use of SQLite. I've stopped using them as they are linked to an earlier version of SQLite and their updates are outside my control.

I use C# to access the SQLite C-APIs. For me it appeared impossible at first sight (because I do NOT know C which makes reading the API signature very hard), extremely hard to begin with but perseverance pays off as it gets easier and easier.

The CLI is useful for prototyping,...

Sure, but if it does not fultil a quest, you've got to wonder whether it is an SQLite feature or whether the user is somehow lacking. (as with column wrapping).

The CLI is more than that; the CLI is

• a learning environment for SQLite for novice and experienced developers
• a rapid development environment for designing the data tier of an application.
• the administration tool for SQLiite

I started with SQLite v3.34 with prior experience of Micosoft SQL Server. This forum is a primary tool for accelerated learning. I have developped

• a wrapper for the SQLite APis in C# and can choose to use any version 3.34 onwards and from any location on the hard disk
• developed a tool to redirect SQLite3.exe's stdIn, stdOut, and stdErr and to manage what I want to achieve
• some utilities using the ODBC drivers; these are restrictive in scope as the driver(s) are tied to a much earler version of SQLite than the current version.

The CLI for SQLite3 is the equivalent of isql.exe in Sybase

Not so. SQLITE3.EXE has a user interface, isql did not. SQLite.exe from the command prompt opens its own command prompt like interface. isql does not.

Microsoft has deprecated isql.exe (and osql.exe) in favour of humongous GUI applications

isql.exe and osql.exe have been deprecated and replaced by sqlcmd.exe (which, like its predecessors) does not have a user interface. The GUI application is SSMS (SQL Server Management Studio) - it is a GUI and cannot be used from the command prompt like sqlcmd.exe.

SQLite3.EXE is somewhere between sqlcmd.exe and SSMS: it can be used with arguments from the command prompt like sqlcmd.exe and it can also be used without any arguments and it presents an interface like SSMS albeit much simpler.

Command prompt usage: SQLite3.exe to execute the script in e:/temp/employees.sql

D:\SQLite32>sqlite3.exe <e:\temp\employees.sql

D:\SQLite32>

Any result from the script is echoed to the Command Prompt. Thereafter, I can execute any command viable in the operating syste,.

isql and osql have a user interface. It is not the "best user interface ever designed" (that award goes to the Ashton-Tate dBase II -- clear the terminal and prompt with a single .).

sqlcmd is a tweeny version of a "send the command" interface (isql.exe) with some (not very useful IMHO) enhancements putting it closed to guified SQL Administrator (a GUI version of isql that is small and fast).

Microsoft got rid of anything that can run in less that a gigabyte of RAM because, well, Microsoft. (and yes, any executable can be used from the "command line" -- how do you think a "load image" gets loaded and executed)?

BTW. to anyone else reading this - I don't use C# so this might be a very ignorant question, but I've searched for examples of direct SQLite C-API calls in C#, which I imagined would be ten-a-penny, but not finding much - are there some good open source examples somewhere? Can it be done?

Building function-by-function interfaces between disparate languages is a pain.

Syntactically, C# is reasonably close to C++, and thus to C. Architecturally, it is more like many "scripted" languages, such as Python (different memory model: garbage collection, references rather than pointers, reflection, run-time code generation, ...).

In both Python and C#, you could write an interface to directly call something like sqlite3_vmprintf(), but mostly it isn't worth the trouble.

In C#, people mostly use database abstractions that work with multiple database engines. In theory, that makes it easier to switch which database engine you are using.