Reputation: 6132
My query is supposed to add an additional column "_DisableAccounting" to the "Settings_Global" table, specifying a DEFAULT value using a SQLiteParemeter.
ALTER TABLE Settings_Global ADD COLUMN `_DisableAccounting` BOOL NOT NULL DEFAULT @defaultValue;
When the SQLiteCommand is executed, @defaultValue should be replaced by the supplied parameter.
// ....
SQLiteParameter defaultValueParam = null;
if (defaultValue != null) {
query += " DEFAULT @defaultValue`";
defaultValueParam = new SQLiteParameter { ParameterName = "@defaultValue", Value = defaultValue };
}
query += ";";
using (SQLiteCommand cmd = _Connection.CreateCommand()) {
cmd.CommandText = query;
if (defaultValueParam != null) {
cmd.Parameters.Add(defaultValueParam);
}
cmd.ExecuteNonQuery();
}
// ....
However, I get the error "'SQL logic error or missing database near @defaultValue": syntax error'". So it seems that the command is never replacing @defaultValue with the actual value.
Why isn't this working? I am doing essentially the same with MySQL (my application can optionally use MySQL) and it is working.
Upvotes: 1
Views: 390
Reputation: 180310
When doing ALTER TABLE, SQLite does not modify the column definition in any way and just inserts it directly at the end of the CREATE TABLE statement. This means that the parameter marker would end up in that CREATE TABLE statement, which would result in the parameter value not being available when the statement is interpreted later.
You have to insert the default value directly into the SQL command.
Upvotes: 1
Reputation: 57103
You appear to have a spurious grave accent. That is @defaultValue is followed grave accent, when it likely shouldn't be.
Try changing
ALTER TABLE Settings_Global ADD COLUMN `_DisableAccounting` BOOL NOT NULL DEFAULT @defaultValue`;
to
ALTER TABLE Settings_Global ADD COLUMN `_DisableAccounting` BOOL NOT NULL DEFAULT @defaultValue;
or to
ALTER TABLE Settings_Global ADD COLUMN `_DisableAccounting` BOOL NOT NULL DEFAULT `@defaultValue`;
Upvotes: 1