Dot Connect for MySql 8.4.583 Not Escaping column Names

[matt_dbp]

Hello,

I am currently trialling your product and am unable to update a table that has a reserved key word as its column name as they are not being escaped.

My Table Schema is below:

Code: Select all

CREATE TABLE `apikey` (
  `Oid` char(38) NOT NULL,
  `Key` varchar(100) DEFAULT NULL,
  `User` char(38) DEFAULT NULL,
  `OptimisticLockField` int(11) DEFAULT NULL,
  `GCRecord` int(11) DEFAULT NULL,
  PRIMARY KEY (`Oid`),
  KEY `iGCRecord_ApiKey` (`GCRecord`),
  KEY `iUser_ApiKey` (`User`),
  CONSTRAINT `FK_ApiKey_User` FOREIGN KEY (`User`) REFERENCES `extendeduser` (`Oid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I am attempting to perform an update using the below code:

Code: Select all

MySqlConnection myConn = new MySqlConnection("User Id=*****;Password=******;database=*******;Host=127.0.0.1;");
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
myDataAdapter.SelectCommand = new MySqlCommand(string.Format("Select * FROM `{0}`", mapping.DestinationTable), myConn);
MySqlCommandBuilder myCommandBuilder = new MySqlCommandBuilder(myDataAdapter);

myConn.Open();
DataSet myDataSet = new DataSet();
myDataAdapter.Fill(myDataSet, String.Format("`{0}`", mapping.DestinationTable));

var row = myDataSet.Tables[0].NewRow();
//Set Row Values here
//...
myDataSet.Tables[0].Rows.Add(row);
myDataAdapter.Update(myDataSet, String.Format("`{0}`", mapping.DestinationTable));

myConn.Close();

On calling update I receive the following Exception:
An unhandled exception of type 'Devart.Data.MySql.MySqlException' occurred in System.Data.dll

Additional information: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Key, User, OptimisticLockField, GCRecord) VALUES ('700458f0-5a24-42fc-824b-579bc' at line 1

I have searched the forums and it appears this should have been fixed back in 2010? Am i missing a configuration option to turn on Column Escaping?

The in dbMonitor Query being sent is:

Code: Select all

INSERT INTO creatatest.apikey (Oid, Key, User, OptimisticLockField, GCRecord) VALUES (:p1, :p2, :p3, :p4, :p5)

I believe this should be:

Code: Select all

INSERT INTO creatatest.apikey (`Oid`, `Key`, `User`, `OptimisticLockField`, `GCRecord`) VALUES (:p1, :p2, :p3, :p4, :p5)

Thanks in advance,
Matt

[matt_dbp]

Never mind I found I needed to change the configuration of the MySqlCommandBuilder to enable Quoted

Code: Select all

myCommandBuilder.Quoted = true;