List:MySQL and .NET« Previous MessageNext Message »
From:Duncan Paul Attard Date:November 14 2004 8:35am
Subject:Fw: Problem when inserting unicode records
View as plain text  
Hi,

I think I've solved my problem, and it turned out to be a mistake, an 
incomplete
definition of the table. I bumbed into the answer here:
http://lists.mysql.com/dotnet/143

The original ('incomplete') table definition was:

CREATE TABLE `test` (
  `word` varchar(100) collate utf8_bin NOT NULL default '',
  `length` int(11) default NULL,
  PRIMARY KEY  (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

while it should have been (at least it worked for me)

CREATE TABLE `test` (
  `word` varchar(100) collate utf8_bin NOT NULL default '',
  `length` int(11) default NULL,
  PRIMARY KEY  (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

I wish to thank you anyway for your help,
  Duncan.


----- Original Message ----- 
From: "Duncan Paul Attard" <duncanatt@stripped>
To: "Reggie Burnett" <reggie@stripped>
Cc: <dotnet@stripped>
Sent: Saturday, November 13, 2004 11:07 AM
Subject: Re: Problem when inserting unicode records


Dear Reggie,

For the sake of simplicity, I have made a simple table as follows:

  create table test (
  word varchar(100) not null,
  length int,
  primary key (word)
  ) type=innodb character set=utf8

The code which I am using is as follows (placed in the event handler of a 
button named btnTest):

// The command string.
string cmdString;

// The word to insert.
string word;

// Create the connection string.
string connString = "host=localhost; username=root; password=root; 
database=corpus;";

// Create connection.
MySqlConnection connection = new MySqlConnection(connString);

try
{
    // Open connection.
    connection.Open();

    // Create the command.
    MySqlCommand command = new MySqlCommand();
    command.Connection = connection;

    word = "ar\u010bhai\u010b";
    cmdString = "INSERT INTO test VALUES ('" + word + "', " + word.Length + 
");";
    command.CommandText = cmdString;

    // Inform log.
    txtLog.AppendText("Trying to insert: " + word + "\r\n");

    // Insert word.
    command.ExecuteNonQuery();

    // Inform log.
    txtLog.AppendText("Word inserted successfully\r\n");

    word = "archaic";
    cmdString = "INSERT INTO test VALUES ('" + word + "', " + word.Length + 
");";
    command.CommandText = cmdString;

    // Inform log.
    txtLog.AppendText("Trying to insert: " + word + "\r\n");

    // Insert word.
    command.ExecuteNonQuery();

    // Inform log.
    txtLog.AppendText("Word inserted successfully\r\n");

    // Close connection.
    connection.Close();
}
catch (Exception ex)
{
    // Show error msg.
    txtLog.AppendText(ex.Message);
}

And the output from the log (which is a multiline textbox named txtLog) is:

Trying to insert: arċhaiċ
Word inserted successfully
Trying to insert: archaic
#23000Duplicate entry 'archaic' for key 1

Can It be that I am doing something wrong? (I am using MySql Connector/Net 
beta 2).

Thanks in advance, and regards,
  ..Duncan Paul Attard



----- Original Message ----- 
From: "Duncan Paul Attard" <duncanatt@stripped>
To: <dotnet@stripped>
Sent: Friday, November 12, 2004 10:21 AM
Subject: Re: Problem when inserting unicode records


So...

This is the show create output:

# Host: localhost
# Database: corpus
# Table: 'kbase'
#
CREATE TABLE `kbase` (
  `word` varchar(100) NOT NULL default '',
  `changes` varchar(100) NOT NULL default '',
  `status` enum('unknown','accept','reject','change') NOT NULL default
'unknown',
  PRIMARY KEY  (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


and these are the character codes in C# format (to get an idea
of how the characters look like, take a look at the character map
of windowsXP).

Small a with a an accent on top of its head: \u00e0 (U+00E0)
Small c with a dot on top of its head: \u010b (U+010B)
Small h with a small line through its head: \u0127 (U+0127)
Small z with a small dot on top of its head: \u017c (U+017C)

-----------------------

Pls note the following:

If I insert 'accessibli' with the small dots on the c's
and afterwards I insert 'accessibli' without the dots
on the c's (i.e. normal english 'c'), the second insertion is
not successful (duplicate key).

If I insert 'adezjoni' with the small dot on the z,
and afterwards I insert 'adezjoni' without the dot
on the z, the latter insertion is unsuccessful
(duplicate key).

If I insert 'behsiebna' with the h having a small line
through its head, and, afterwards I insert 'behsiebna'
with a normal 'h' (i.e. as in the quotes), the insertion
IS successful.

If I insert 'appogg' with the dots on the g's and afterwards
I insert 'appogg' with a normal english 'g', the latter insertion
is unsuccessful.

If I insert 'realta' with the accent on the 'a', and then insert
'realta' without an accent on the 'a', the second insertion
is unsuccessful.

Duncan.


--------------------------------------------------------------------------------


Incoming mail is certified Virus Free.
Checked by AVG Anti-Virus (http://www.grisoft.com).
Version: 7.0.269 / Virus Database: 265.1.0 - Release Date: 09/11/2004

Thread
Fw: Problem when inserting unicode recordsDuncan Paul Attard14 Nov