SQLiteConnectionStringBuilder not working as advertised

SQLiteConnectionStringBuilder not working as advertised

Postby scope_creep » Tue 25 Jan 2011 18:28

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.
scope_creep
 
Posts: 12
Joined: Mon 24 Jan 2011 18:21

Re: SQLiteConnectionStringBuilder not working as advertised

Postby Shalex » Wed 26 Jan 2011 11:24

Please initialize only corresponding connection string parameter (one) when setting any property of the SQLiteConnectionStringBuilder object.
1.
scope_creep wrote: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.
scope_creep wrote: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.
Last edited by Shalex on Mon 31 Jan 2011 16:01, edited 1 time in total.
Shalex
Devart Team
 
Posts: 5870
Joined: Thu 14 Aug 2008 12:44

Updates for SQLiteConnectionStringBuilder failure.

Postby scope_creep » Wed 26 Jan 2011 14:45

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.
scope_creep
 
Posts: 12
Joined: Mon 24 Jan 2011 18:21

Postby Shalex » Thu 27 Jan 2011 14:54

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.
Shalex
Devart Team
 
Posts: 5870
Joined: Thu 14 Aug 2008 12:44

Updates for SQLiteConnectionStringBuilder failure.

Postby scope_creep » Fri 28 Jan 2011 16:15

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
 
Posts: 12
Joined: Mon 24 Jan 2011 18:21

Thanks for your help.

Postby scope_creep » Fri 28 Jan 2011 16:18

Shalex,
Thanks for your help on this one. I guess the other one still needs attention.
Bob.
scope_creep
 
Posts: 12
Joined: Mon 24 Jan 2011 18:21

Postby Shalex » Mon 31 Jan 2011 15:59

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.
scope_creep wrote: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
Devart Team
 
Posts: 5870
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Mon 31 Jan 2011 16:06

Shalex
Devart Team
 
Posts: 5870
Joined: Thu 14 Aug 2008 12:44

Getting Started Documentation

Postby scope_creep » Tue 01 Feb 2011 23:12

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.
scope_creep
 
Posts: 12
Joined: Mon 24 Jan 2011 18:21

Postby Shalex » Wed 02 Feb 2011 17:15

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";
Shalex
Devart Team
 
Posts: 5870
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for SQLite