SQLiteConnectionStringBuilder not working as advertised
-
- Posts: 12
- Joined: Mon 24 Jan 2011 18:21
SQLiteConnectionStringBuilder not working as advertised
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.
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.
Re: SQLiteConnectionStringBuilder not working as advertised
Please initialize only corresponding connection string parameter (one) when setting any property of the SQLiteConnectionStringBuilder object.
1.
connSB.FailIfMissing = false;
-->
connSB.DataSource = @"D:\TestApplication\database.db";
connSB.FailIfMissing = false;
2.
-->
ConnectionBuild.DataSource = @"C:\Rufus.db";
ConnectionBuild.FailIfMissing = true;
Notify us about the results.
1.
connSB.DataSource = @"Data Source=D:\TestApplication\database.db;FailIfMissing=False;";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.FailIfMissing = false;
-->
connSB.DataSource = @"D:\TestApplication\database.db";
connSB.FailIfMissing = false;
2.
ConnectionBuild.DataSource = @"C:\Rufus.db;FailIfMissing=true;";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";
ConnectionBuild.FailIfMissing = true;
Notify us about the results.
Last edited by Shalex on Mon 31 Jan 2011 16:01, edited 1 time in total.
-
- Posts: 12
- Joined: Mon 24 Jan 2011 18:21
Updates for SQLiteConnectionStringBuilder failure.
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.
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.
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.
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.
-
- Posts: 12
- Joined: Mon 24 Jan 2011 18:21
Updates for SQLiteConnectionStringBuilder failure.
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.
Delete the database.db from D:\ and altered connection object to
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
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
THAT worked fine. A single row was entered.
Trying it with the following
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
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.
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)";
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);
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);
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);
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);
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);
"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.
-
- Posts: 12
- Joined: Mon 24 Jan 2011 18:21
Thanks for your help.
Shalex,
Thanks for your help on this one. I guess the other one still needs attention.
Bob.
Thanks for your help on this one. I guess the other one still needs attention.
Bob.
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.
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.
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.
Code: Select all
ConnectionBuild.DataSource = @"D:\TestApplication\database.db;FailIfMissing=false;";
2.
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.scope_creep wrote: Trying it with the followingIt opened the connection, executed the sql, closed the connection, BUT no sequel was entered into the database and no exception were reported.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);
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.
Here is the SQLiteConnectionStringBuilder documentation:
http://www.devart.com/dotconnect/sqlite ... ilder.html.
http://www.devart.com/dotconnect/sqlite ... ilder.html.
-
- Posts: 12
- Joined: Mon 24 Jan 2011 18:21
Getting Started Documentation
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.
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.