[Mono-bugs] [Bug 575841] New: Mono.Data.Sqlite Positional parameter used twice in multi-statement command

[bugzilla_noreply at novell.com]

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: mhabersack at novell.com
        ReportedBy: cfbradford at gmail.com
         QAContact: mono-bugs at lists.ximian.com
          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.