using System.Data.Odbc;

namespace MySqlSSL
{
    class Program
    {
        static void Main(string[] args) {
            string connStr = "Driver={MySQL ODBC 8.0 UNICODE Driver};Server=localhost;Database=ETLBox_DataFlow;User=root;Password=etlboxpassword;Option=3;";
            var conn = new OdbcConnection(connStr);

            conn.Open();
            string sqlcreate = @"CREATE TABLE test (
                                    id INT NOT NULL,
                                    value VARCHAR(100) NULL
            )";
            OdbcCommand cmdCreate = new OdbcCommand(sqlcreate, conn);
            cmdCreate.ExecuteNonQuery();

            string sqlInsert = @"INSERT INTO test VALUES ROW(?,?)";
            OdbcCommand cmdInsert = new OdbcCommand(sqlInsert, conn);
            var insertPar1 = new OdbcParameter("InsertPar1", 1);
            var insertPar2 = new OdbcParameter("InsertPar2", "A");

            cmdInsert.Parameters.Add(insertPar1);
            cmdInsert.Parameters.Add(insertPar2);
                        
            cmdInsert.ExecuteNonQuery();

            string sqlUpdate = @"
UPDATE test ut
    INNER JOIN ( VALUES ROW( ? , ? ) ) vt (id,value)
    ON (ut.id = vt.id)
SET ut.value = vt.value";
            OdbcCommand cmdUpdate = new OdbcCommand(sqlUpdate, conn);            
            var updatePar1 = new OdbcParameter("UpdatePar1", 1);
            var updatePar2 = new OdbcParameter("UpdatePar2", "B");

            cmdUpdate.Parameters.Add(updatePar1);
            cmdUpdate.Parameters.Add(updatePar2);
            cmdUpdate.ExecuteNonQuery();

            string sqlRead = "SELECT value FROM test WHERE id = ?";
            OdbcCommand cmdRead = new OdbcCommand(sqlRead, conn);
            var readPar1 = new OdbcParameter("ReadPar1", 1);
            cmdRead.Parameters.Add(readPar1);
            string result = cmdRead.ExecuteScalar().ToString();
                        
            if (result == "A") 
                throw new System.Exception("No update was performed!");
            //Works on MySql 8.0.21, exception is thrown on MySql 8.0.26

            conn.Close();
        }

    }
}