| Bug #23862 | Problem with CommandBuilder 'GetInsertCommand' method | ||
|---|---|---|---|
| Submitted: | 1 Nov 2006 22:23 | Modified: | 14 Dec 2006 14:58 |
| Reporter: | Lynn Eriksen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / NET | Severity: | S5 (Performance) |
| Version: | 1.08 | OS: | Windows (Windows XP) |
| Assigned to: | CPU Architecture: | Any | |
[3 Nov 2006 6:51]
Lynn Eriksen
Here is the corresponding command in the 'DbCommandBuilder' class that the 5.01 object uses (code via Reflector):
private bool IncludeInInsertValues(DbSchemaRow row)
{
if ((!row.IsAutoIncrement && !row.IsHidden) && !row.IsExpression)
{
return !row.IsRowVersion;
}
return false;
}
Note that to be included the schema row must not be:
IsAutoIncrement
IsHidden
IsExpression
IsRowVersion
This supports my proposalof adding 'IsAutoIncrement' checking for the 1.08 provider.
[3 Nov 2006 6:54]
Lynn Eriksen
The method above from the DbCommandBuilder is using Reflector on the .Net 2.0 DbCommandBuilder class.
[23 Nov 2006 14:57]
Tonci Grgin
Hi and thanks for your problem report. I am having the trouble with: > 2. Create a corresponding DataTable in the DataSet designer that matches the mysql table. Use AutoIncStart = -1 and AutoIncSeed = -1 for the primary key column. I just don't have "AutoIncStart" property. Can you please post entire VS project describing this problem? As for code consideration, I agree with you.
[23 Nov 2006 15:00]
Tonci Grgin
Object inspector for AI field
Attachment: 23862s.jpg (image/jpeg, text), 54.26 KiB.
[10 Dec 2006 15:23]
Johan Djupmarker
Try this code:
MySqlConnection con = new MySqlConnection("server=localhost;Database=mydb;UserName=root;Pwd=xxx;");
con.Open();
MySqlCommand cmdCreateTable = new MySqlCommand("CREATE TABLE `test` (`Col1` int(10) NOT NULL auto_increment, `Col2` int(10) NOT NULL, PRIMARY KEY (`Col1`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;", con);
cmdCreateTable.ExecuteNonQuery();
MySqlCommand cmdInsert = new MySqlCommand("SELECT Col1, Col2 FROM test", con);
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(cmdInsert);
da.Fill(dt);
dt.Columns["Col1"].AutoIncrement = true;
dt.Columns["Col1"].AutoIncrementSeed = -1;
dt.Columns["Col1"].AutoIncrementStep = -1;
DataRow dr = dt.NewRow();
dr["Col2"] = 3;
dt.Rows.Add(dr);
MySqlCommandBuilder cmdBuilder = new MySqlCommandBuilder(da);
da.Update(dt);
con.Close();
/Johan
[12 Dec 2006 11:35]
Tonci Grgin
Hi all. Yes there seems to be a problem with AI field included in INSERT. When you turn logging on you see "Executing command QUERY with text ='INSERT INTO `test`.`bug23862` (`Col1`, `Col2`) VALUES (-1, 3); SELECT `Col1`, `Col2` FROM `test`.`bug23862` WHERE (`Col1`=last_insert_id())'" Verified as described with test case provided with - MySQL server 5.0.27BK on WinXP Pro SP2 localhost - c/NET 1.0.x rev 494 (SVN) - NET FW 2.0
[13 Dec 2006 16:42]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/16891
[13 Dec 2006 16:47]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/16892
[13 Dec 2006 16:48]
Reggie Burnett
fixed in 1.0.9
[14 Dec 2006 14:58]
MC Brown
A note has been added to the 1.0.9 changelog.
Description: Currently, the command builder is adding an insert parameter for MySQL auto increment table columns when 'GetInsertCommand' is called. This is incorrect as the value should be assigned by the MySQL database and not the insert command. This is not a problem in 1.07. How to repeat: Try the following: 1. Create a mysql table with unsigned auto increment couln as primary key. 2. Create a corresponding DataTable in the DataSet designer that matches the mysql table. Use AutoIncStart = -1 and AutoIncSeed = -1 for the primary key column. 3. Use a data adapter/command builder to update the table with test data. 4. While debugging, inspect the inser command generated by the command builder. You'll see that the insert command included the auto in column erroneously. 5. The DbDataAdapter class with throw a conversion to UInt32 for a negative value halting executio before update. Suggested fix: This is an update for the 'IncludedInInsert' method in the 'CommandBuilder' class. Specifically, if a column is set to 'IsAutoIncrement' in the schema it should not be in the insert command. The 'CreateFinalSelect' command still continues to work correctly after this change. private static bool IncludedInInsert(DataRow schemaRow) { // If the parameter has one of these properties, then we don't include it in the insert: /* if ((bool) schemaRow ["IsHidden"]) return false; if ((bool) schemaRow ["IsExpression"]) return false;*/ if ((bool)schemaRow["IsRowVersion"]) return false; if ((bool)schemaRow["IsReadOnly"]) return false; /* prevents accidental insert in auto increment column */ if ((bool)schemaRow["IsAutoIncrement"]) return false; return true; }