[C#] sqlite - how setting WAL mode in sqlite

Question

1.00/5 (1 vote)

See more:

Windows CE, .NET compact framework 3.5 
Using SQlite Dll File : sqlite-netFx35-binary-PocketPC-ARM-2008-1.0.105.0.zip
"http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki"

I want to use wal mode. so, I written the below code.

string sql = "PRAGMA journal_mode = WAL;";
 SQLiteCommand cmd = new SQLiteCommand(sql, cnn);
 var journalMode = cmd.ExecuteScalar();

But, journalMode was set to 'delete'.

What I have tried:

The other setting like 'journal_mode = OFF' is ok.
And when I try to open database file that is set to wal mode, an error occurs.

"file is encrypted or is not a database\r\nfile is encrypted or is not a database

What is problem?

Posted 9-May-17 20:29pm

Member 12753057

Updated 10-May-17 22:03pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2017-05-10T00:45:00

Solution 1

As SQLite documentation[^] states, a database in WAL journaling mode can only be accessed by SQLite version 3.7.0 (2010-07-21) or later.

Conclusion: you have to use newer version of SQLite[^] database.

[EDIT]
To be able to open SQLite database in WAL mode, you have to open it using special connection string:

C#

SQLiteConnection connection = new SQLiteConnection("Data Source=" + file + ";PRAGMA journal_mode=WAL;")
connection.Open();
using (var command = new SQLiteCommand(sqliteConnection))
{
    command.CommandText = "Your query string here";
    command.ExecuteNonQuery();
}

Posted 10-May-17 0:45am

Maciej Los

Updated 10-May-17 20:25pm

v2

Comments

Member 12753057 10-May-17 20:48pm

The SQlite dll file that I using was descripted : (This binary package contains all the binaries for the PocketPC version of the System.Data.SQLite 1.0.105.0 (3.18.0) package. The included native binaries should work on all supported ARM versions of Windows CE prior to Windows Embedded Compact 2013. The .NET Compact Framework 3.5 is required.)

Is that mean the SQLite version include version 3.18.0?

I can't find the other SQLite version that supported Windows CE(.NET Compact framework 3.5)

Maciej Los 11-May-17 2:16am

Seems, the package of SQLite drivers for .net framework 3.5 is OK, but the database version is older than 3.7. You have to create proper database version: sqlite_create_database.
Read more: Database File Format

Member 12753057 11-May-17 3:09am

My server version of database is 3.18.0.
Is your 'database version' server version?

I make a database by below code.

using (var cnn = new SQLiteConnection("Data Source= testDB.db; Version=3"))
{
cnn.Open();
cnn.Execute(@"create table testTable ( col1 int )");
}

(+)
I check that "SQLiteConnection.SQLiteVersion" is 3.18.0 too

Maciej Los 11-May-17 6:47am

SQLite is not server-site database.

Member 12753057 11-May-17 6:58am

SQLiteConnection.SQLiteVersion that I using is 3.18.0.
Was not version 3.18.0 released later than version 3.7.0?