SQLite Forum

empty DB created if DB file missing - is this inherent to SQLite
Login

empty DB created if DB file missing - is this inherent to SQLite

(1) By Art H (ahansen) on 2022-01-25 16:22:54 [link] [source]

Preface: I'm a novice; programming about a year & using SQLite about 4 mo.

I'm developing a C# app in Microsoft Visual Studio (VS) that's using SQLite. In perusing the SQLite documentation I ran across integrity_check. As a test I renamed the DB. Much to my surprise integrity_check returned "ok". Looking in the target folder I found a new 0kb file with the correct name. Opening the file in a DB manager showed a complete SQLite DB structure; apparently this was the file that was "integrity_check"'ed.

My first work around attempt was to check for the file's existence (in C# File.Exist) but it turns out that's unreliable when working with "Special Folders".

My second attempt was to load data from a nonexistent DB and capture the resulting error message. This time it did error out but with "no such table" and the new 0kb file with the correct name.

Is this expected SQLite behavior? If yes, how does one check for the existence of the DB before doing anything else?

(2) By Larry Brasfield (larrybr) on 2022-01-25 16:43:24 in reply to 1 [link] [source]

See Opening a New Database Connection, where someone as clearly aware of details as you can see what is going on. Pay particular attention to the issue of flags passed into the open.

(3) By Art H (ahansen) on 2022-01-26 13:26:27 in reply to 2 [link] [source]

Thank you for the response.

I've not actually been executing any Open() or Close() statements and I've been able to save to/load from the db fine so for example this C# method (function)

public static List<Sched> LoadSchedDistinctNames()

{ using (IDbConnection dbc = new SQLiteConnection(BURS_Connections.SQLiteDB()))

{ var input = dbc.Query<Sched>($"select distinct saveName from Sched", new DynamicParameters()); return input.ToList(); } }

returns a listing of distinct schedule names as intended. My app is quite simple/small (22 windows), SQLite (14 tables, no FKs) is primarily replacing .csv files and the format is from a tutorial. That format is used for nearly all interaction with the DB so "something" (I suspect the System.Data.SQLite.dll) is performing the Core Routines (a new concept for me). It must be doing a vanilla Open() which I think defaults to mode = rwc and would explain the behavior I'm seeing.

The only exception to the above format is the execution of the PRAGMA integrity_check which uses a format copied from MSDN so I could use the ExecuteScalar method. That does have an explicit Open() statement but when I try use Open_V2() instead I get an error that "SQLiteConnection does not conttain a definition for Open_V2()". Is this a question that could be answered in this forum?

(4) By Larry Brasfield (larrybr) on 2022-01-26 13:51:44 in reply to 3 [link] [source]

I've not actually been executing any Open() or Close() statements ...

The library you are using is layered over the SQLite library. If you are causing that intermediate layer to create, read and modify SQLite databases, it is a virtual certainty that one of the sqlite3_open*() family of functions is being called. Equally certain is that your code is instigating such calls.

I gather that you have not focused on the flags passed to the _v2 variant, because if you had, you would have seen that there is some control to be had over whether the DB file is auto-created. I had thought that once you understood that, you would then look at the docs for the library you are using most directly to see how to affect what flags get passed into the sqlite3_open*() API on your behalf.

I could go read the System.Data.SQLite docs for you, but decline. Please consider this tip as to where to begin reading as enough to get you going: The sqlite3_open* calls are made from connection object methods.

(5) By Art H (ahansen) on 2022-01-26 14:39:18 in reply to 4 [link] [source]

Actually, I focused specifically on the _V2 discussion & recognize I need to do the Open_V2() with a mode = rw flag. I do not understand how to do that. Thus my closing question in the previous post.

(6.1) By Ryan Smith (cuz) on 2022-01-26 15:11:26 edited from 6.0 in reply to 5 [link] [source]

Your question was:

..."SQLiteConnection does not conttain a definition for Open_V2()". Is this a question that could be answered in this forum?

Answer: It is not impossible, there are very many SQLiters here and a broad range of wrapper users, but a forum of specifically the wrapper/platform you are using is much more likely to produce more and better answers.

What we can tell you, and what Larry tried to, is that SQLite itself can very easily do what you ask, it is part and parcel of its open_v2 capabilities. That said, the wrapper you use may have some way of communicating those same flags through to SQLite's open command (which it MUST call internally to actually open a DB), but we do not know how exactly to communicate that by using your wrapper since we (or most of us) know nothing about it, and that is what you may find more readily answered on a forum of people who also use it.

What Larry further believes (and I'm inclined to agree) is that your wrapper would very probably have documentation to enlighten that very question, so starting there (now knowing what you are looking for) is almost smarter and faster than hopping to the next forum.

(8) By Art H (ahansen) on 2022-01-26 16:21:42 in reply to 6.1 [source]

My "wrapper" is - as stated - System.Data.SQLite with the .Net Framework Stub, the official package provided by the SQLite Development Team and obtained via NuGet.

Per the System.Data.SQLite page:

"This page contains all supported downloadable packages (e.g. sources, binaries, etc) for the current release version of the official "System.Data.SQLite" project. This page and the associated NuGet packages are the only official distribution points for these downloadable packages."

System.Data.SQLite Support:

"SQLite has an active forum and support community and users of the System.Data.SQLite project available on this web site are encouraged to use these for support questions.

The SQLite Forum has been established to help support SQLite and System.Data.SQLite"

This forum is where users of System.Data.SQLite are directed.

(10) By Ryan Smith (cuz) on 2022-01-26 16:36:36 in reply to 8 [link] [source]

Ok, we are not saying you should sod off, just that frequency of helpers are probably higher on C# or whatever platform you are using.

Where are you directed for documentation on your wrapper? Have you found anything there on opening of SQlite DBs? Did it fail to answer the question?

If so, feel free to post the section from the documentation that is relevant and say how it is unclear or what remains to be answered, that way someone here can fill in the gaps or perhaps even non-wrapper users (like myself) could offer advice or figure out what would make it work for you - or you could just wait until a System.Data.SQLite user rolls by (in case RandomCoder's reply isn't already enough).

(11) By Larry Brasfield (larrybr) on 2022-01-26 16:38:31 in reply to 8 [link] [source]

For the System.Data.SQLite wrapper, this forum is the right place to come for help. There is no "support" in the sense of anybody obliged to try to get users through their difficulties. That is why you are encouraged to read the documentation for System.Data.SQLite as your primary source of help.

Last time I looked, that documentation was in the form of a .htm file (which is some sort of Microsoft-devised HTML archive), and is impossible to link from here. You should have gotten that archive along with the library. Somewhere in the connection constructor or methods doc, your options with respect to auto-creation of the DB file can be found.

(7) By RandomCoder on 2022-01-26 16:03:46 in reply to 3 [link] [source]

Since Open() wraps all calls to sqlite3_open, you need to pass the options to it to the SQLite object you create:

var csb = new SqliteConnectionStringBuilder
{
    DataSource = BURS_Connections.SQLiteDB(),
    Mode = SqliteOpenMode.ReadWrite
};
var sqlite = new SqliteConnection(csb.ConnectionString);
try
{
    sqlite.Open();
}
catch (SqliteException e)
{
    if (e.SqliteErrorCode == 14) // SQLITE_CANTOPEN
    {
        Console.WriteLine("Unable to open DB");
    }
}

(9) By Art H (ahansen) on 2022-01-26 16:29:50 in reply to 7 [link] [source]

RandomCoder - Thank you very much! That's where I was stuck.

(12) By Art H (ahansen) on 2022-01-27 17:19:35 in reply to 7 [link] [source]

I don't see a way to edit my previous post so I'm adding a reply ...

Just in case somebody ends up here despite the title of this thread ...

The above code is not for System.Data.SQLite. It is for Microsoft.Data.SQLite and you'll need the Microsoft.Data.SQLite NuGet package.

For System.Data.SQLite the following returns a list a table's contents:

public static List<ActiveSaves> LoadActives()

{ var csb = new SQLiteConnectionStringBuilder

{
	DataSource = BURS_Path.DB(),
	FailIfMissing = true,   
};
using (var dbc = new SQLiteConnection(csb.ConnectionString))

{
	var input = dbc.Query<ActiveSaves>($"select * from {DBtbl.Actives}");

	try
	{
		dbc.Open();
	}

	catch (Exception ex)
	{
		ErrMsg(ex);
	}
	return input.ToList();
}

}

Obviously RandomCoder pointed me down the correct path ... Thanx again.