I'm working in Microsoft Visual C# 2008 Express with Sqlite.
I understand that an apostrope (') in my text has problems in a query. My problem is that I thought I could replace it with \'. It doesn't seem to be working... Here's a parred down example of my code:
string myString = "I can't believe it!";
cmd.CommandText = "Insert into myTable (myid,mytext) values (1,'" + myString.Replace("'","\\'") + "');";
The error I get is: SQLite error: near "t": syntax error
I've tried a couple other replacements... like the other slash. And I wrote my string and a replaced version of my string out to the console to make sure it was coming out right.
What stupid error am I making here?
Thanks!
-Adeena
If you are running the sqlite3 command-line access program you can type ". tables" to get a list of all tables. Or you can type ". schema" to see the complete database schema including all tables and indices.
16) When can you get an SQLITE_SCHEMA error? The SQLITE_SCHEMA error is returned when a prepared SQL statement is not valid and cannot be executed. Such type occurs only when using the sqlite3 prepare() and sqlite3 step() interfaces to run SQL.
SQLite does not have an ALL operator. You might be tempted to write something like this: select ...
The solution presented by Robert will work (i.e. replacing '
by ''
).
Alternatively you can use parameters as in:
DbCommand cmd = new DbCommand();
DbParameter param = cmd.CreateParameter();
// ...
// more code
// ...
cmd.CommandText = "Insert table (field) values (@param)";
param.ParameterName = "param"
param.DbType = DbType.String;
param.Value = @"This is a sample value with a single quote like this: '";
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
Using parameters protects against sql injection, and makes the ' problems qo away.
It is also much faster because sqlite can reuse the execution plan of statements when you use parameters. It can't when you don't use parameters. In this example using a parameter makes the bulk insert action approximately 3 times faster.
private void TestInsertPerformance() {
const int limit = 100000;
using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=c:\testperf.db")) {
conn.Open();
using (SQLiteCommand comm = new SQLiteCommand()) {
comm.Connection = conn;
comm.CommandText = " create table test (n integer) ";
comm.ExecuteNonQuery();
Stopwatch s = new Stopwatch();
s.Start();
using (SQLiteTransaction tran = conn.BeginTransaction()) {
for (int i = 0; i < limit; i++) {
comm.CommandText = "insert into test values (" + i.ToString() + ")";
comm.ExecuteNonQuery();
}
tran.Commit();
}
s.Stop();
MessageBox.Show("time without parm " + s.ElapsedMilliseconds.ToString());
SQLiteParameter parm = comm.CreateParameter();
comm.CommandText = "insert into test values (?)";
comm.Parameters.Add(parm);
s.Reset();
s.Start();
using (SQLiteTransaction tran = conn.BeginTransaction()) {
for (int i = 0; i < limit; i++) {
parm.Value = i;
comm.ExecuteNonQuery();
}
tran.Commit();
}
s.Stop();
MessageBox.Show("time with parm " + s.ElapsedMilliseconds.ToString());
}
conn.Close();
}
}
Sqlite behaves similar to Oracle when it comes to the importance of using parameterised sql statements.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With