Create table, where is the file??
(1) By SQL911 (mhnsqlite) on 2023-03-24 07:34:17 [link] [source]
I created a table and inserted a record using the SQLite3.exe app
I want to know just where is the table's file is located. Running .Databases shows 2 lines that have a Seq value, a Name value but no file info.
There has to be a file somewhere and I want to manage where that file resides.
Thanks ever so much.
(2) By Warren Young (wyoung) on 2023-03-24 09:01:00 in reply to 1 [link] [source]
The file is where you told SQLite.exe to put it. If all you did was double-click the executable, it's in memory because you didn't pass a file name.
On reasonable terminals (i.e. not Windows Console) the SQLite3 shell even bold-faces the relevant warning:
SQLite version 3.41.1 2023-03-10 12:13:52
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
(4.1) By Aask (AAsk1902) on 2023-03-24 09:27:17 edited from 4.0 in reply to 2 [link] [source]
(i.e. not Windows Console)
It does ... what you have shown as bold appears in red (I'm using Windows 11).
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .shell ver
Microsoft Windows [Version 10.0.22621.1413]
sqlite>
(5) By Warren Young (wyoung) on 2023-03-24 10:08:58 in reply to 4.1 [link] [source]
So it does.
Apparently drh needs to make it blink and vibrate, then. 🙄
(7) By Florian Balmer (florian.balmer) on 2023-03-24 17:15:23 in reply to 4.1 [source]
Interesting (but off-topic, sorry) trivia:
The legacy Windows console has supported colored output since the early versions of Windows NT. On Windows 10 and above, this can now be achieved using VT escape sequences.
In the (g)old(en) times, apps (at least those small utilities not dealing with
console buffers, themselves) had to call SetConsoleTextAttribute() to change
the color, write their text to output, and call SetConsoleTextAttribute()
again to restore the previous colors. As the venerable CMD.EXE shell never dealt
with colors, it was possible to interrupt programs by Ctrl+C right before the
second call to SetConsoleTextAttribute(), leading to "color leaks" in the
console. (A lock around these 3 functions calls and an interrupt handler to
restore the defaults are required to fix this.) Newer shells like Yori or
Powershell automatically restore their colors themselves when a child process
terminated.
sqlite3.exe also uses the backwards-compatible method to output colored text -- but I haven't been able to trigger this funny "vulnerability", yet ;-)
(8) By SQL911 (mhnsqlite) on 2023-03-24 19:21:50 in reply to 2 [link] [source]
Warren, Thanks for the quick reply. I opened the Sqlite3 utility using a cmd prompt "SQlite3" with no command line. I then used the sql statement "Create Table (Field1,Field2,,,,) No file name/s existed for the command .Databases. I then ".Quit". , Re-started SQLite3.exe with the command : Sqlite3 "C:\MyFolder\Test.db" And Presto! I have an actual file. The command .databases produces the file location for seq 0 named "Main" and a second line for Seq 1 named "temp" with no file name. I then created a table as I did originally, named "MyTable" Is "MyTable" attached to the file C:\MyFolder\Test.db. ? Is C:\MyFolder\Test.db is the database main file for all tables that I create? After quoting SQLite3.exe, in order to reconnect to this database do I use the command : Sqlite3 C:\MyFolder\Test.db" ? Thanks very much
(12) By Warren Young (wyoung) on 2023-03-24 19:40:34 in reply to 8 [link] [source]
Is "MyTable" attached to the file…
The term "attached" implies it could be detached. It's better to say your table is inside the file in a parts-of-the-whole manner.
(Oppose the use of "inside" as in a container, since a table also cannot be meaningfully removed from the file short of a .dump type operation.)
Is C:MyFolderTest.db is the database main file for all tables that I create?
The shell can have any number of database files attached to it, and a database can have any number of tables inside.
in order to reconnect to this database do I use the command…
Try it and see.
(Spoiler: yes.)
(3) By Harald Hanche-Olsen (hanche) on 2023-03-24 09:02:31 in reply to 1 [link] [source]
If you run sqlite3 without a filename argument, it does not create a file. Instead, all your data ends up in temporary tables, and vanish when you quit the program. If you run sqlite3 with a filename argument, the file should end up where you specified: In the current directory unless your filename argument says otherwise.
(6) By Larry Brasfield (larrybr) on 2023-03-24 15:40:50 in reply to 1 [link] [source]
(Without gainsaying other replies:) If you did not specify a DB file as you started sqlite3.exe, you can use its .save command to write out your in-memory database. Use .help or read the doc to see about that and other commands.
(9) By SQL911 (mhnsqlite) on 2023-03-24 19:25:47 in reply to 6 [link] [source]
Larry,
There is no ".Save" command. It's not in .Help and if used it produces an error.
(10) By Stephan Beal (stephan) on 2023-03-24 19:28:30 in reply to 9 [link] [source]
There is no ".Save" command.
.save, case-sensitive.
(11) By Larry Brasfield (larrybr) on 2023-03-24 19:38:18 in reply to 9 [link] [source]
As I said, use the ".save" command. You can see it in ".help" output, and if you enter ".help save" you can see it again. It's been there for a long time.
(13) By SQL911 (mhnsqlite) on 2023-03-24 23:13:30 in reply to 11 [link] [source]
The version that I have is 3.7.2 It is simply not there.
I have a new laptop that I am cloning (so to speak) It has Sqlite3.exe it is version 3.22.0
.save is there, HOWEVER
.help save is no different than .help, you do not get help on .save only
Maybe there is a newer version?
(14) By Larry Brasfield (larrybr) on 2023-03-24 23:48:19 in reply to 13 [link] [source]
Maybe there is a newer version?
The SQLite project is alive. I dare say that if your 5 and 12.5 year old versions were the most recent, I would not make such a claim.
The .save command was added over 9 years ago.
You may want to visit the download page.
I will try to remember that some people run very old software.
(15) By SQL911 (mhnsqlite) on 2023-03-25 21:14:57 in reply to 14 [link] [source]
Larry,
You are very right, the app I have appears to be very old.
Over the past 40 years that I have experienced with the PC, I have learned a very painfully learned mantra concerning "Upgrading" anything.
"If it aint broke (and I mean absolutely broken), Don't fix it"
However, I will keep the old version under a different name and use the latest version.
Thanks all for your help