Text displays as Byte[]

2004-01-08
2013-04-09
  • Hello,

    I have a table with one column which is of the MySQL type TEXT. I created a dataset with a datatable with a datacolumn with the name of my column and the datatype System.String.

    The select statement is: "SELECT MyColumn FROM MyTable;".

    The datatable is displayed in a DataGrid. When I fill my dataset with the information from the MySQL database, it says: "System.Byte[]" in stead of the string.

    I changed the select statement to: "SELECT LOWER(MyColumn) FROM MyTable;". Now it does display the string. But I don't want the information in the database converted to non-capitals.

    Why doesn't it display the string and how can I make it display the string?

    Thanks, Guillaume Hanique.

     
    • RD
      RD
      2004-01-08

      Same problem here ...

      I haven't tried using the "lower" function though. Is this a recent problem with MySQLDriverCS? As there is no mention of it anywhere else.

      Have you tried using an older version ? I might do that over the weekend.

      Thanks, RD.

       
      • No, I haven't. Would it make sense that a previous version will do it right?

        I think there is a problem here. MySQL is a considerably different database than MSSQL or MsAccess. Both MSSQL and MsAccess for example have the Guid type. What I also expect the MySQL interface to do, is convert data between MySQL and the datatypes of the .NET framework, i.e.: Type is TEXT? Convert to string. Type is BLOB? Convert to Byte[]. That kind of conversion.

         
        • RD
          RD
          2004-01-09

          It looks like MySQL stores Text / Blob fields as a Byte array, and does not attempt to distinguish between then when returning to a query. Why the function "lower" probably works is that it must've been overloaded to accept a text field, but it always returns a string.

          I think I at least have a temporary solution ( ... well, it is almost a permanent solution). You can use the following query, and it will definitely get you going:
          select cast(text_col as char) from table;

          MySQL offers conversion functions called "cast" and "convert". I have used it my query, and am seeing it work ok so far.

          May be you can also apply this BLOB fields, and get the desired return type.

          - RD.

           
          • This workaround may work, but it seems my SQL version is rather old. I would rather have the driver make the conversion, in stead of using this workaround, though. But it may be fine for a while.

            Thanks for your reply.

            Guillaume Hanique.

             
    • Francesco
      Francesco
      2004-01-22

      Is there a way to solve this? (on new versions maybe...)
      I prefer not to change all my queries with that cast to use the driver...

      I tried to convert manually the data into strings.
      I usually retrieve data by this way (the fastest one i found):

         arr = new Object[myReader.FieldCount];
         myReader.GetValues(arr);

      So i added this:

      var en =new UTF8Encoding();
      for(i=0;i<myReader.FieldCount;i++)
      if(arr[i] != null && arr[i] instanceof Byte[]) {
          arr[i]=en.GetString(arr[i]);
      }

      and it works, but i don't like this solution....

      Why this driver return byte[] ?
      No other driver does.
      And this is a very fast driver ( and i really need a fast one! )

      Bye

       
    • I have won the fight storing GUIDs.
      Some advices:
      Use the last libMySQL.dll (get it from the mysql server for windows)
      Change your database and tables default character set to UTF8 and collates to utf8_general_ci. I found that if you try to store a guid in a varchar field (lenght 16) you have to set collate to this, or you will get a splitted guid.
      If you are having still problems look at the Encoder nad Decoder class in c# help.

       
    • The corelab driver works fine with text fields but costs money! Don't try the one from bytefx! it's more buggy then mysqlcs driver.

      Has anyone a tip for using this driver with a datagrid? It will filled automaticly and i don't want to check each field (if text or not...blabla)!

      Cheers

       
    • I checked the driver with the MysqlDataReader and it will works fine - displays all the text in a listbox. I guess there's a problem with the MySQLAdapter or a converting problem...

       
    • Waquesseix
      Waquesseix
      2004-03-13

      I've faced the same problem and I've partially solved it. But it can't adapt to DataGrid usage. This is useful for Repeater or DataView.

      MySQLDataReader::GetFieldType() returns wrong data type. It always return 'string'. So DataAdapter misunderstand byte[] as string.

      If change the source code as below, DataAdapter don't store 'System.Byte' in byte[] columns.

      public Type GetFieldType( int i )
      {
      return dt.Columns[i].DataType;
      //return typeof(string);
      }

      This is available to 'BLOB' type but 'text' type still returns type of 'byte[]'. So in DataGrid this solution have no effect.

      MySQLDataReader now regard text type as binary. So this should be changed. I try to make a patch for it.
      But I don't know such a patch will be taken or not.

       
    • Waquesseix
      Waquesseix
      2004-03-13

      I checked the source, and I've knew binary support patch. TEXT and BLOB is originally string type column. But MySQL doesn't make a distinction between large binary and large text. So Christophe Ravier createtd a patch to support a binary column in TEXT and BLOB. But you know, it causes the problem in DataGrid.

      I think MySQLDriverCS should have the property which determine to deal TEXT and BLOB as binary or long text.

      If you doesn't use binary and immediately want to use large text for DataGrid, you change the source file as follows.

      MySQLDataReader.cs

      /*
      if (dt.Columns[(int)i].DataType == System.Type.GetType("System.Byte[]"))
      {
          ....
      }
      */

      I've confirmed DataGrid correctly worked.

       
    • DarkShadow81
      DarkShadow81
      2007-06-05

      as long as you want to save binary data in a blobb you can convert the byte array to base64 and store sthe base64string in the blobb.reconverting at the same way

      some short sample as i need it:

      Loading from BLOB Field:

      string Tmp = myReader.GetString(3);
      byte[] rawData = new byte[FileSize];   
      rawData = System.Convert.FromBase64String(Tmp);

      Saving into BLOB Field:

      UInt32 FileSize; byte[] rawData; FileStream fs;
      fs = new FileStream(@Datei, FileMode.Open, FileAccess.Read);           
      FileSize = System.Convert.ToUInt32(fs.Length);
      rawData = new byte[FileSize];
      fs.Read(rawData, 0, (int)FileSize);
      fs.Close();
      string str = System.Convert.ToBase64String(rawData,0,(int)FileSize);

      compiled and test with images, and it works fine :-)