Mike,

Did you post anything to the MySQL forum regarding the request for a real GUID type as per your latest blog post?

Thanks

Mike,

Have you been able to get the Binary(16) GUID in MySQL to work? From a performance standpoint will it be better than using a varchar(36) field that is indexed? What about compatibility from MySQL to MS SQL or VistaDB?

Well, I know you're going to be disappointed and we almost hacked it in but decided it was too risky. Let me give you some reasoning. Our meta data engine performs its mapping on the core database column type. For instance, we map a CHAR to a' string' (via the esLanguages..xml) mapping file. We don't map against the full datatype name, i.e., CHAR(1) - however, if we did we could say map a CHAR(1) to a 'char' and CHAR(2)+ to a 'string'. I started to put the code in actually for you and it got nasty as I was adding exceptions all over in the code. MySQL's decision is unfortunate it's like say we're going to use NVARCHAR(8) as a LONG and doing conversions rather than adding a true LONG column type in the database.

That being said, all is not lost. I have an email from Reggie Burnett in my inbox and I will be replying to him soon, if all else fails we will enhance our metadata engine to deal with this awkward way of wedging a new datatype into a database via it's data provider alone. In the end, the pressure to get ES2008 released so folks can go live on it took precedence. We do plan to add this in a maintenance release however. We know how important Guids are for doing cross db work and such. Again, my apologies, I really did want to add this for you.

 - Mike
 

No problem. I agree that it would be best to make this as straight forward and compatible without hacks. I just wanted to follow up. If possible, please give an update after you talk with Reggie from MySQL.

 Thanks

I wanted to see if you could give an update and see if you were you able to contact Reggie Burnett at MySQL.

Has there been any luck in getting the MySQL provider working with Guids? Did you get a chance to contact MySQL regarding your suggestion about a real implimentation of a GUID type?

Thanks

I just wanted to check and see if any changes have been made to ES since the new Meta Engine has been added.

Also, was Reggie able to provide any more information?

Well, I'm 4 hours into this tonight and as confused as ever, it does appear a Binary(16) will come back in a DataTable as a Guid (thanks to a specific version of the provider) but it seems like it's to stupid to know how to convert it back for you? There is no MySqlDbType.Guid so you have to store it back as a Binary. So I don't think it can be a true property, ie, you can read it as a Guid but have to set it as a byte[], very confusing, no samples, very suprising that MySql would take this path. Anyway, I'm still on it, but the provide no samples. Sorry, I'm ranting but imagine if microsoft introduced new types through the provider only and you used binary's of different lengths for the datatypes? They wouldn't be taking seriously, ok, ok, I'm shutting up, perhaps tonight something will click?  I'm thinking at this point we might have to treat them as type objects and you'll have to typecast and convert then before set.

See this guys conversion? http://forums.mysql.com/read.php?38,74352

Okay, here's my progress, after adding a Binary(16) to my employees table and hand tweaking the generated code such that it should have worked, only I receive the message "Data too long for column 'TheGuid' at row 1" and no matter what I tried still the same message. So I tried some stuff with the raw MySQL provider.

Here is the raw provider code:

MySqlConnectionStringBuilder csb = new MySqlConnectionStringBuilder();

csb.Database = "test";
csb.UserID = "root";
csb.Password = "yada";
csb.Server = "localhost";
csb.Port = 3306;

MySqlConnection conn = new MySqlConnection(csb.ToString());

conn.Open();
MySqlCommand cmd = new MySqlCommand(@"select * from employees", conn);

DataTable dt = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

try
{
    adapter.Fill(dt);

    MySqlCommandBuilder bb = new MySql.Data.MySqlClient.MySqlCommandBuilder(adapter);

    MySqlCommand iCmd = bb.GetInsertCommand();

    // Give it a guid value
    dt.Rows[0]["TheGuid"] = Guid.NewGuid();

    adapter.InsertCommand = iCmd;

    adapter.Update(dt);
}
catch (Exception ex)
{
    System.Console.WriteLine(ex.Message);
}
   
conn.Close();

Guess what, I still received the same error message. I check the parameter for my Guid column to in the generated Command received back from the CommandBuilder which is what I really wanted to see, ie, what were the settings. To my suprise the raw MySQL provider code will not work either. At this point I'm not sure MySQL truly supports the guid datatype. The code above normally works on all providers.

PS: I emailed Reggie through his blog and alerted him to this thread.

Ok. Lets see what Reggie says. It would be great if they would add a "TRUE" GUID field type.

When I do research for using GUIDs with MySQL, most suggest using a varchar(38) field type to store the GUID. This was before the provider was to support the binary(16) change . I am wondering what the performance difference (selects, joins) is with MySQL with varchar(38) fields to Binary(16) field type. It seems that Binary(16) is a work around to the issue.

Thanks 

Mike,

I was working with the latest MySQL Connector (5.2.5) with GUID values and made some progress. I was able to insert values into the table and return all of the rows. However I was not able to return just one record based on setting a WHERE codition on the GUID value. I have posted a message to the MySQL C# Connector forum and hope someone replies back. The link to the post is http://forums.mysql.com/read.php?38,238452,238452#msg-238452 . You were getting an error of data is too long when you were looking at this before. The GUID value has to be passed as a ByteArray value in the params. Also, below is the code I created to test the GUID values.

            try
            {
                string sSQL = "INSERT INTO users(userguid,username,createddate) ";
                sSQL += " VALUES(?userguid,?username,?createddate)";

                Guid g = System.Guid.NewGuid();               
                byte[] guid = g.ToByteArray();

                MySqlCommand oCommand = new MySqlCommand(sSQL, oConnection);
                oCommand.Parameters.AddWithValue("?userguid", guid);
                oCommand.Parameters.AddWithValue("?username", "John Doe");
                oCommand.Parameters.AddWithValue("?createddate", DateTime.Now);

                oConnection.Open();

                int iReturn = (int)oCommand.ExecuteNonQuery();
                oConnection.Close();
            }
            finally
            {
                oConnection.Close();
            }

            try
            {
                string sSQL = "SELECT * FROM users WHERE userguid=?userguid order by createddate";

                Guid g = new Guid("{b7fbdf40-ed82-4b89-8b39-3803140fc5cd}");
                MySqlCommand oCommand2 = new MySqlCommand(sSQL, oConnection);
                oCommand2.Parameters.AddWithValue("?userguid",g);
               
                MySqlDataAdapter oDataAdapter = new MySqlDataAdapter(oCommand2);
                oConnection.Open();
                DataTable dt = new DataTable();
                oDataAdapter.Fill(dt);
                GridView1.DataSource = dt;
                GridView1.DataBind(); 
            }
            finally
            {
                oConnection.Close();
            }

If I remove the WHERE condition I get the below. The record I am trying to find is the 5th row.

One thing that is weird is that when you create a GUID with the System.Guid.NewGuid and then pass it into the MySQL database, the order of the GUID "{b7fbdf40-ed82-4b89-8b39-3803140fc5cd}" is changed to "40DF FBB7 82ED 894B 8B39 3803 140F C5CD" and stored that way. However, when you do a select on the data it converts it back to the value that was created with System.Guid.NewGuid. See my post to MySQL for more on this topic as well as this link I found http://forums.whirlpool.net.au/forum-replies-archive.cfm/1053762.html

Thanks

Mike,

I have to totally agree with you that this is a MySQL hack/work-around. I was able to save a GUID to the binary(16) field as well as retrieve all. However, as I stated I cannot get their own provider to return one record based on a where condition against the binary(16) field. 

Even more so is that it appears that ONLY their MySQL .NET connector even uses the binary(16) as a GUID. All other MySQL libs and third-party utilities are not able to use this binary(16) type. I use EMS MySQL Manager and have looked also at the NaviCat application. Both show a non-readable value (binary code) in the column due to not knowing how to interact with the field. This also would make it difficult to just insert rows into the table or query the table in batch modes or other non .NET processes during database management such as import/export, merge, bulk inserts, etc.

So, it looks like a varchar(38) would possibly be the best solution if a GUID value needs to be saved. However, there is a serious disadvantage in using a GUID as a PK with it being defined as varchar(38). Basically stating what is already known. :-)

With their major investment, I would hope that Sun would want to make a change in their product to support a true GUID type. I posted another message to their Falcon forum (http://forums.mysql.com/read.php?133,186255,238519#msg-238519). Now, will anyone see it?

I like MySQL but I almost wonder if they refuse to implement it because it's used so heavily in the Microsoft world? It just doesn't make sense not to have a true type. It will take the user base screaming about it to get it in there ...