SQLiteConnectionStringBuilder not working as advertised

[scope_creep]

In the Devart sqlite documentation to build a SQLiteConnectionStringBuilder object it states:- Use the following.

SQLiteConnectionStringBuilder connSB = new SQLiteConnectionStringBuilder();
connSB.DataSource = @"Data Source=D:\TestApplication\database.db;FailIfMissing=False;";
connSB.FailIfMissing = false;
connSB.Locking = LockingMode.Exclusive;
connSB.AutoVacuum = AutoVacuumMode.Full;
connSB.ConnectionTimeout = 20;
SQLiteConnection sqLiteConnection1 = new SQLiteConnection(connSB.ConnectionString);

Its excepts with "Path is not of the correct format" when I replace D:\TestApplication\database.db with C:\Rufus.db. An examination of the connection found two DataSource string entries in. So I thought, mistake in the docs.

So changed it to the following, losing the DataSource bit.

ConnectionBuild = new SQLiteConnectionStringBuilder();
ConnectionBuild.DataSource = @"C:\Rufus.db;FailIfMissing=true;";
ConnectionBuild.Locking = LockingMode.Normal;
ConnectionBuild.AutoVacuum = AutoVacuumMode.Full;
ConnectionBuild.ConnectionTimeout = 20;
Connection = new SQLiteConnection(ConnectionBuild.ConnectionString);

with a precreated Rufus.db, and FailIfMissing=true, it excepts with {"SQLite error\r\nno such table: IDMEF"} from a SQLiteException. Different exception.

However, when I use a simple connection string such as

Connection = new SQLiteConnection();
Connection.ConnectionString = @"Data Source=C:\Rufus.db;FailIfMissing=true;";

it inserts into the IDMEF table, no problem.

So is their something wrong with either SQLiteConnectionStringBuilder object creating the connection string, or the docs wrong or am i create the wrong connection string.

Thanks for your help.

[Shalex]

Please initialize only corresponding connection string parameter (one) when setting any property of the SQLiteConnectionStringBuilder object.
1.

In the Devart sqlite documentation to build a SQLiteConnectionStringBuilder object it states:- Use the following.

SQLiteConnectionStringBuilder connSB = new SQLiteConnectionStringBuilder();
connSB.DataSource = @"Data Source=D:\TestApplication\database.db;FailIfMissing=False;";
connSB.FailIfMissing = false;
connSB.Locking = LockingMode.Exclusive;
connSB.AutoVacuum = AutoVacuumMode.Full;
connSB.ConnectionTimeout = 20;
SQLiteConnection sqLiteConnection1 = new SQLiteConnection(connSB.ConnectionString);

Its excepts with "Path is not of the correct format" when I replace D:\TestApplication\database.db with C:\Rufus.db. An examination of the connection found two DataSource string entries in. So I thought, mistake in the docs.

connSB.DataSource = @"Data Source=D:\TestApplication\database.db;FailIfMissing=False;";
connSB.FailIfMissing = false;
-->
connSB.DataSource = @"D:\TestApplication\database.db";
connSB.FailIfMissing = false;
2.

So changed it to the following, losing the DataSource bit.

ConnectionBuild = new SQLiteConnectionStringBuilder();
ConnectionBuild.DataSource = @"C:\Rufus.db;FailIfMissing=true;";
ConnectionBuild.Locking = LockingMode.Normal;
ConnectionBuild.AutoVacuum = AutoVacuumMode.Full;
ConnectionBuild.ConnectionTimeout = 20;
Connection = new SQLiteConnection(ConnectionBuild.ConnectionString);

with a precreated Rufus.db, and FailIfMissing=true, it excepts with {"SQLite error\r\nno such table: IDMEF"} from a SQLiteException. Different exception.

ConnectionBuild.DataSource = @"C:\Rufus.db;FailIfMissing=true;";
-->
ConnectionBuild.DataSource = @"C:\Rufus.db";
ConnectionBuild.FailIfMissing = true;

Notify us about the results.

[scope_creep]

Posted


ConnectionBuild = new SQLiteConnectionStringBuilder();
ConnectionBuild.DataSource = @"Data Source=C:\Rufus.db FailIfMissing=False;";
ConnectionBuild.FailIfMissing = false;
// ConnectionBuild.DataSource = @"C:\Rufus.db;FailIfMissing=true;";
ConnectionBuild.Locking = LockingMode.Normal;
ConnectionBuild.AutoVacuum = AutoVacuumMode.Full;
ConnectionBuild.ConnectionTimeout = 20;
Connection = new SQLiteConnection(ConnectionBuild.ConnectionString);

returned exception

{"The given path's format is not supported."}

with path contents in SQLiteConnection connection being

"Connection Timeout=20;Data Source=\"Data Source=C:\Rufus.db;FailIfMissing=False;\";AutoVacuum=Full;Locking=Normal;FailIfMissing=False;"

Noticed their was two settings on the property FailIfMissing. My Fault. Removed the second one. Tried.

ConnectionBuild = new SQLiteConnectionStringBuilder();
ConnectionBuild.DataSource = @"Data Source=C:\Rufus.db";
ConnectionBuild.FailIfMissing = false;
// ConnectionBuild.DataSource = @"C:\Rufus.db;FailIfMissing=true;";
ConnectionBuild.Locking = LockingMode.Normal;
ConnectionBuild.AutoVacuum = AutoVacuumMode.Full;
ConnectionBuild.ConnectionTimeout = 20;
Connection = new SQLiteConnection(ConnectionBuild.ConnectionString);

This generated a exception of {"The given path's format is not supported."} with path contents in SQLiteConnection connection being

"Connection Timeout=20;Data Source=\"Data Source=C:\Rufus.db\";AutoVacuum=Full;Locking=Normal;FailIfMissing=False;"

Tried the following

ConnectionBuild = new SQLiteConnectionStringBuilder();
ConnectionBuild.DataSource = @"Data Source=D:\TestApplication\database.db;FailIfMissing=False;";
ConnectionBuild.Locking = LockingMode.Normal;
ConnectionBuild.AutoVacuum = AutoVacuumMode.Full;
ConnectionBuild.ConnectionTimeout = 20;
Connection = new SQLiteConnection(ConnectionBuild.ConnectionString);

created a folder on D called TestApplication, Exception returned was

{"The given path's format is not supported."} with the connection object holding the string

"Connection Timeout=20;Data Source=\"Data Source=D:\TestApplication\database.db;FailIfMissing=False;\";AutoVacuum=Full;Locking=Normal;"

Tried

ConnectionBuild = new SQLiteConnectionStringBuilder();
ConnectionBuild.DataSource = @"Data Source=D:\TestApplication\database.db";
ConnectionBuild.FailIfMissing = false;
ConnectionBuild.Locking = LockingMode.Normal;
ConnectionBuild.AutoVacuum = AutoVacuumMode.Full;
ConnectionBuild.ConnectionTimeout = 20;
Connection = new SQLiteConnection(ConnectionBuild.ConnectionString);

excepted with the following exception message:- {"The given path's format is not supported."}

with connection object connection string holding

"Connection Timeout=20;Data Source=\"Data Source=D:\TestApplication\database.db\";AutoVacuum=Full;Locking=Normal;FailIfMissing=False;"

This is vs2010 ultimate, win 2008 Ent SP2. TestApplication in all cased didn't receive a new database. I don't want a db created on the fly. I would like a precreated one built using SqliteStudio, which is found, loaded and a connection created to. I would use the simple SQLiteConnection connection, but for the locking=Normal instead of Exclusive, which is too restrive for this application.

Thanks for your help.
Bob.

[Shalex]

Sorry, it was my fault in my previous post. Please correct your code again:

1) in the first sample -
ConnectionBuild.DataSource = @"Data Source=C:\Rufus.db";
==>
ConnectionBuild.DataSource = @"C:\Rufus.db";

2) in the second sample -
ConnectionBuild.DataSource = @"Data Source=D:\TestApplication\database.db";
==>
ConnectionBuild.DataSource = @"D:\TestApplication\database.db";

For more information about using connection string builders, please refer to MSDN: http://msdn.microsoft.com/en-us/library/ms254947.aspx.

[scope_creep]

Test: Creating the database on the fly.

The following connection string builder enables a connection to be opened, a database to created on the fly, a table to be created and a row inserted.

Code: Select all

 
ConnectionBuild = new SQLiteConnectionStringBuilder();
ConnectionBuild.DataSource = @"D:\TestApplication\database.db;"; 
ConnectionBuild.FailIfMissing = false;
ConnectionBuild.Locking = LockingMode.Normal;
ConnectionBuild.AutoVacuum = AutoVacuumMode.Full;
ConnectionBuild.ConnectionTimeout = 20;
Connection = new SQLiteConnection(ConnectionBuild.ConnectionString);  

SQL to create the following table. 

@"CREATE TABLE IF NOT EXISTS IDMEF    
 (                     
    MESSAGENO INTEGER PRIMARY KEY,
    CREATEDATE DEFAULT CURRENT_TIMESTAMP,  
    MESSAGE VARCHAR(40000),
    COLLECTORNAME VARCHAR(32)
  )";

with insert statement 

"insert into IDMEF(MESSAGENO,MESSAGE,COLLECTORNAME) values(null, :IDMEFXML, :COLLECTOR)";

Delete the database.db from D:\ and altered connection object to

Code: Select all

 ConnectionBuild = new SQLiteConnectionStringBuilder();
ConnectionBuild.DataSource = @"D:\TestApplication\database.db;FailIfMissing=false;"; 
ConnectionBuild.Locking = LockingMode.Normal;
ConnectionBuild.AutoVacuum = AutoVacuumMode.Full;
ConnectionBuild.ConnectionTimeout = 20;
Connection = new SQLiteConnection(ConnectionBuild.ConnectionString);  

returns with "Unable to open database file". From docs, When FailIfMissing is false, an empty database is created. No database was created.

So when it's in it's own property, it works. When in DataSource, it excepts.

Trying on the precreated, which i'm looking for.

Using the following

Code: Select all

ConnectionBuild = new SQLiteConnectionStringBuilder();
ConnectionBuild.DataSource = @"C:\Rufus.db;";
ConnectionBuild.FailIfMissing = true;
ConnectionBuild.Locking = LockingMode.Normal;
ConnectionBuild.AutoVacuum = AutoVacuumMode.Full;
ConnectionBuild.ConnectionTimeout = 20;
Connection = new SQLiteConnection(ConnectionBuild.ConnectionString);  

fails with "Unable to open database file" exception. Details are

Connection String = "Connection Timeout=20;Data Source=\"C:\Rufus.db;\";AutoVacuum=Full;Locking=Normal;FailIfMissing=True;"

DataSource="C:\Rufus.db;"

This time, removed the semicolon from the end of DataSource string using

Code: Select all

ConnectionBuild = new SQLiteConnectionStringBuilder();
ConnectionBuild.DataSource = @"C:\Rufus.db";
ConnectionBuild.FailIfMissing = true;
ConnectionBuild.Locking = LockingMode.Normal;
ConnectionBuild.AutoVacuum = AutoVacuumMode.Full;
ConnectionBuild.ConnectionTimeout = 20;
Connection = new SQLiteConnection(ConnectionBuild.ConnectionString);  

THAT worked fine. A single row was entered.

Trying it with the following

Code: Select all

ConnectionBuild = new SQLiteConnectionStringBuilder();
ConnectionBuild.DataSource = @"C:\Rufus.db;FailIfMissing=true;";
ConnectionBuild.Locking = LockingMode.Normal;
ConnectionBuild.AutoVacuum = AutoVacuumMode.Full;
ConnectionBuild.ConnectionTimeout = 20;
Connection = new SQLiteConnection(ConnectionBuild.ConnectionString);  

It opened the connection, executed the sql, closed the connection, BUT no sequel was entered into the database and no exception were reported.

Removing the semi colon from the end of FailIfMissing=true; resulting in

Code: Select all

ConnectionBuild = new SQLiteConnectionStringBuilder();
ConnectionBuild.DataSource = @"C:\Rufus.db;FailIfMissing=true";
ConnectionBuild.Locking = LockingMode.Normal;
ConnectionBuild.AutoVacuum = AutoVacuumMode.Full;
ConnectionBuild.ConnectionTimeout = 20;
Connection = new SQLiteConnection(ConnectionBuild.ConnectionString);  

resulted in a "Unable to open database file" exception. With ConnectionString being

"Connection Timeout=20;Data Source=\"C:\Rufus.db;FailIfMissing=true\";AutoVacuum=Full;Locking=Normal;"

and DataSource = "C:\Rufus.db;FailIfMissing=true"


So.
1. Your docs are wrong. All properties should be on their own get;set; line when SQLiteConnectionStringBuilder is used.

Update your docs to reflect this.

2. Docs don't provide any advice in docs for when FailIfMissing=true.

Update your docs to offer advice.

3. When you do add in FailIfMissing=true into DataSource it is read literally but still added to connectionstring. It's seems to work, but not sql gets to the db. It completly wrecks the object.

I guess you can close this as I now it works, notwithstanding your incorrect docs.

[scope_creep]

Shalex,
Thanks for your help on this one. I guess the other one still needs attention.
Bob.

[Shalex]

1. Only one corresponding connection string parameter (without semicolon) should be initialized in any property of the SQLiteConnectionStringBuilder object: http://msdn.microsoft.com/en-us/library/ms254947.aspx.

Code: Select all

ConnectionBuild.DataSource = @"D:\TestApplication\database.db;FailIfMissing=false;";

In this case FailIfMissing=false; is not a part of connection string but a part of the database extension. So this setting is disregarded. That's why the new database is not created.
2.

Trying it with the following

Code: Select all

ConnectionBuild = new SQLiteConnectionStringBuilder();
ConnectionBuild.DataSource = @"C:\Rufus.db;FailIfMissing=true;";
ConnectionBuild.Locking = LockingMode.Normal;
ConnectionBuild.AutoVacuum = AutoVacuumMode.Full;
ConnectionBuild.ConnectionTimeout = 20;
Connection = new SQLiteConnection(ConnectionBuild.ConnectionString); 

It opened the connection, executed the sql, closed the connection, BUT no sequel was entered into the database and no exception were reported.

This code works only if you have the "Rufus.db;FailIfMissing=true;" file (db;FailIfMissing=true; is extension) in the root of your C: drive.
3. Re-read my previous posts in this thread. I corrected my first post. Documentation is valid concerning the mentioned issue.
4. If it doesn't help, specify your current version (x.xx.xxx) of dotConnect for SQLite and the code that doesn't work.

[Shalex]

Here is the SQLiteConnectionStringBuilder documentation:
http://www.devart.com/dotconnect/sqlite ... ilder.html.

[scope_creep]

In the page, http://www.devart.com/dotconnect/sqlite ... ilder.html

in section "Logging onto the Server" the docs state the following:

[C#]

SQLiteConnectionStringBuilder connSB = new SQLiteConnectionStringBuilder();
connSB.DataSource = @"Data Source=D:\TestApplication\database.db;FailIfMissing=False;";
connSB.FailIfMissing = false;
connSB.Locking = LockingMode.Exclusive;
connSB.AutoVacuum = AutoVacuumMode.Full;
connSB.ConnectionTimeout = 20;
SQLiteConnection sqLiteConnection1 = new SQLiteConnection(connSB.ConnectionString);

That will completly fail. Worth updating.

Bob.

[Shalex]

Thank you for your report. We will correct our docs in the next build.
connSB.DataSource = @"Data Source=D:\TestApplication\database.db;FailIfMissing=False;";
-->
connSB.DataSource = @"D:\TestApplication\database.db";