http://bugzilla.novell.com/show_bug.cgi?id=575841
http://bugzilla.novell.com/show_bug.cgi?id=575841#c0 Summary: Mono.Data.Sqlite Positional parameter used twice in multi-statement command Classification: Mono Product: Mono: Class Libraries Version: 2.6.x Platform: Macintosh OS/Version: Mac OS X 10.5 Status: NEW Severity: Normal Priority: P5 - None Component: Mono.Data.Sqlite AssignedTo: [email protected] ReportedBy: [email protected] QAContact: [email protected] Found By: --- Blocker: --- I have a multi-statement SQL command that contains 6 wildcard parameters (positional, not named or numbered) but only 5 of the 6 values I add to the Parameters collection are actually used. It seems that one of them is being used twice. Here is the SQL: ---------------- ExecuteNonQuery( "BEGIN; "+ "INSERT OR REPLACE INTO sectors (name) VALUES (?); "+ "INSERT OR REPLACE INTO industries (sector_id, name) "+ "VALUES ((SELECT sector_id FROM sectors WHERE name = ?), ?); "+ "INSERT OR REPLACE INTO stocks (industry_id, ticker, name) " + "VALUES ((SELECT industry_id FROM industries WHERE name = ?), ?, ?); "+ "COMMIT", stock.SectorName, stock.SectorName, stock.IndustryName, // <- this parameter is used twice // The previous param is used twice! (6 wildcards, but only 5 params are used) /*stock.IndustryName,*/ stock.Ticker, stock.Name); (All parameters are strings) ExecuteNonQuery is implemented as: ---------------------------------- private int ExecuteNonQuery (string commandText, params object[] parameters) { Debug.Assert(commandText != null); Debug.Assert(parameters != null); using (SqliteConnection conn = new SqliteConnection(_connString)) { conn.Open(); using (SqliteCommand cmd = new SqliteCommand(commandText, conn)) { foreach (object p in parameters) { cmd.Parameters.AddWithValue(null, p); } return cmd.ExecuteNonQuery(); } } } (_connString is "Data Source=/Users/chip/stocks.db") Database Schema: ---------------- CREATE TABLE sectors ( sector_id INTEGER PRIMARY KEY, name TEXT UNIQUE ); CREATE TABLE industries ( industry_id INTEGER PRIMARY KEY, sector_id INTEGER, -- FK name TEXT UNIQUE ); CREATE TABLE stocks ( stock_id INTEGER PRIMARY KEY, industry_id INTEGER, -- FK ticker TEXT UNIQUE, name TEXT ); (The database is initially empty when I tested this) Expected Results: ----------------- I would expect all 6 parameters to be required. Actual Result: -------------- The statement works as desired if the 4th parameter is commented out (as shown above). Removing additional parameters causes an error (not enough parameters). This happens to work for this particular statement since the parameter is repeated anyway, but its certainly not a general solution. I haven't tried using named parameters, but I would guess that would be a work-around as well. -- Configure bugmail: http://bugzilla.novell.com/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug. _______________________________________________ mono-bugs maillist - [email protected] http://lists.ximian.com/mailman/listinfo/mono-bugs
