From e76e7a3106ed374a40d3a94fb92d2df678bcf845 Mon Sep 17 00:00:00 2001 From: Daniel Morgan Date: Sun, 13 Oct 2002 20:41:21 +0000 Subject: 2002-10-13 Daniel Morgan * Mono.Data.MySql/MySqlCommand.cs: * Mono.Data.MySql/MySqlConnection.cs: * Mono.Data.MySql/MySqlDataReader.cs: * Mono.Data.MySql/MySqlTypes.cs: modifed - handle MySQL types to .NET types and some fixes svn path=/trunk/mcs/; revision=8227 --- mcs/class/Mono.Data.MySql/ChangeLog | 8 + .../Mono.Data.MySql/MySqlCommand.cs | 4 +- .../Mono.Data.MySql/MySqlConnection.cs | 4 +- .../Mono.Data.MySql/MySqlDataReader.cs | 70 ++-- .../Mono.Data.MySql/Mono.Data.MySql/MySqlTypes.cs | 365 +++++++++++++++++++-- 5 files changed, 388 insertions(+), 63 deletions(-) (limited to 'mcs') diff --git a/mcs/class/Mono.Data.MySql/ChangeLog b/mcs/class/Mono.Data.MySql/ChangeLog index eac8fdd79db..cd465afd68f 100644 --- a/mcs/class/Mono.Data.MySql/ChangeLog +++ b/mcs/class/Mono.Data.MySql/ChangeLog @@ -1,3 +1,11 @@ +2002-10-13 Daniel Morgan + + * Mono.Data.MySql/MySqlCommand.cs: + * Mono.Data.MySql/MySqlConnection.cs: + * Mono.Data.MySql/MySqlDataReader.cs: + * Mono.Data.MySql/MySqlTypes.cs: modifed - + handle MySQL types to .NET types and some fixes + 2002-10-12 Daniel Morgan * Test/MySqlTest.cs: added file - to do diff --git a/mcs/class/Mono.Data.MySql/Mono.Data.MySql/MySqlCommand.cs b/mcs/class/Mono.Data.MySql/Mono.Data.MySql/MySqlCommand.cs index 335abdd304f..e668e4f3baf 100644 --- a/mcs/class/Mono.Data.MySql/Mono.Data.MySql/MySqlCommand.cs +++ b/mcs/class/Mono.Data.MySql/Mono.Data.MySql/MySqlCommand.cs @@ -181,6 +181,7 @@ namespace Mono.Data.MySql { typeof(MySqlMarshalledField)); string fieldName = fd.Name; int fieldType = fd.FieldType; + DbType fieldDbType = MySqlHelper.MySqlTypeToDbType((MySqlEnumFieldTypes)fieldType); //Console.WriteLine("*** DEBUG: MySql FieldType: " + fieldType); @@ -191,7 +192,8 @@ namespace Mono.Data.MySql { } else { // only get first column/first row - obj = GetColumnData(row, 0); + string objValue = GetColumnData(row, 0); + obj = MySqlHelper.ConvertDbTypeToSystem (fieldDbType, objValue); } MySql.FreeResult(res); diff --git a/mcs/class/Mono.Data.MySql/Mono.Data.MySql/MySqlConnection.cs b/mcs/class/Mono.Data.MySql/Mono.Data.MySql/MySqlConnection.cs index a7ff6e84a49..843f1a38e74 100644 --- a/mcs/class/Mono.Data.MySql/Mono.Data.MySql/MySqlConnection.cs +++ b/mcs/class/Mono.Data.MySql/Mono.Data.MySql/MySqlConnection.cs @@ -121,7 +121,7 @@ namespace Mono.Data.MySql { [MonoTODO] public void ChangeDatabase (string databaseName) { dbname = databaseName; - Console.WriteLine("MySql Selecting Database: " + dbname + "..."); + //Console.WriteLine("MySql Selecting Database: " + dbname + "..."); Console.Out.Flush(); int sdb = MySql.SelectDb(mysqlInitStruct, dbname); if (sdb != 0) { @@ -296,7 +296,7 @@ namespace Mono.Data.MySql { */ conState = ConnectionState.Closed; MySql.Close(mysqlInitStruct); - // MySql.ThreadEnd(); + MySql.ThreadEnd(); mysqlConn = IntPtr.Zero; } } diff --git a/mcs/class/Mono.Data.MySql/Mono.Data.MySql/MySqlDataReader.cs b/mcs/class/Mono.Data.MySql/Mono.Data.MySql/MySqlDataReader.cs index e937925e161..901efd0014f 100644 --- a/mcs/class/Mono.Data.MySql/Mono.Data.MySql/MySqlDataReader.cs +++ b/mcs/class/Mono.Data.MySql/Mono.Data.MySql/MySqlDataReader.cs @@ -36,13 +36,14 @@ namespace Mono.Data.MySql { // field meta data string[] fieldName; - int[] fieldType; + int[] fieldType; // MySQL data type + DbType[] fieldDbType; // DbType translated from MySQL type uint[] fieldLength; uint[] fieldMaxLength; uint[] fieldFlags; // field data value private object[] dataValue; - + private bool open = false; private int recordsAffected = -1; @@ -125,25 +126,6 @@ namespace Mono.Data.MySql { dataTableSchema.Columns.Add ("IsLong", typeof (bool)); dataTableSchema.Columns.Add ("IsReadOnly", typeof (bool)); - // TODO: for CommandBehavior.SingleRow - // use IRow, otherwise, IRowset - if(numFields > 0) - if((cmdBehavior & CommandBehavior.SingleRow) == CommandBehavior.SingleRow) - numFields = 1; - - // TODO: for CommandBehavior.SchemaInfo - if((cmdBehavior & CommandBehavior.SchemaOnly) == CommandBehavior.SchemaOnly) - numFields = 0; - - // TODO: for CommandBehavior.SingleResult - if((cmdBehavior & CommandBehavior.SingleResult) == CommandBehavior.SingleResult) - if(currentQuery > 0) - numFields = 0; - - // TODO: for CommandBehavior.SequentialAccess - used for reading Large OBjects - //if((cmdBehavior & CommandBehavior.SequentialAccess) == CommandBehavior.SequentialAccess) { - //} - DataRow schemaRow; DbType dbType; Type typ; @@ -155,7 +137,7 @@ namespace Mono.Data.MySql { schemaRow["ColumnName"] = fieldName[i]; schemaRow["ColumnOrdinal"] = i + 1; - schemaRow["ColumnSize"] = (int) fieldLength[i]; + schemaRow["ColumnSize"] = (int) fieldMaxLength[i]; schemaRow["NumericPrecision"] = 0; schemaRow["NumericScale"] = 0; // TODO: need to get KeyInfo @@ -176,9 +158,9 @@ namespace Mono.Data.MySql { // do translation from MySQL type // to .NET Type and then convert the result // to a string - enum_field_types fieldEnum; + MySqlEnumFieldTypes fieldEnum; - fieldEnum = (enum_field_types) fieldType[i]; + fieldEnum = (MySqlEnumFieldTypes) fieldType[i]; dbType = MySqlHelper.MySqlTypeToDbType(fieldEnum); typ = MySqlHelper.DbTypeToSystemType (dbType); string st = typ.ToString(); @@ -253,7 +235,9 @@ namespace Mono.Data.MySql { fieldLength = new uint[numFields]; fieldMaxLength = new uint[numFields]; fieldFlags = new uint[numFields]; - + + fieldDbType = new DbType[numFields]; + // marshal each meta data field // into field* arrays for (int i = 0; i < numFields; i++) { @@ -268,7 +252,29 @@ namespace Mono.Data.MySql { fieldLength[i] = marshField.Length; fieldMaxLength[i] = marshField.MaxLength; fieldFlags[i] = marshField.Flags; + + fieldDbType[i] = MySqlHelper.MySqlTypeToDbType((MySqlEnumFieldTypes)fieldType[i]); } + + // TODO: for CommandBehavior.SingleRow + // use IRow, otherwise, IRowset + if(numFields > 0) + if((cmdBehavior & CommandBehavior.SingleRow) == CommandBehavior.SingleRow) + numFields = 1; + + // TODO: for CommandBehavior.SchemaInfo + if((cmdBehavior & CommandBehavior.SchemaOnly) == CommandBehavior.SchemaOnly) + numFields = 0; + + // TODO: for CommandBehavior.SingleResult + if((cmdBehavior & CommandBehavior.SingleResult) == CommandBehavior.SingleResult) + if(currentQuery > 0) + numFields = 0; + + // TODO: for CommandBehavior.SequentialAccess - used for reading Large OBjects + //if((cmdBehavior & CommandBehavior.SequentialAccess) == CommandBehavior.SequentialAccess) { + //} + } return resultReturned; } @@ -293,10 +299,12 @@ namespace Mono.Data.MySql { } else { for (int i = 0; i < numFields; i++) { - dataValue[i] = cmd.GetColumnData(row, i); - // maybe some tranlation needs to be - // done from the native MySql c type + // marshal column data value + string objValue = cmd.GetColumnData(row, i); + + // tranlate from native MySql c type // to a .NET type here + dataValue[i] = MySqlHelper.ConvertDbTypeToSystem (fieldDbType[i], objValue); } } return true; @@ -337,7 +345,7 @@ namespace Mono.Data.MySql { [MonoTODO] public string GetDataTypeName(int i) { - throw new NotImplementedException (); + return MySqlHelper.GetMySqlTypeName((MySqlEnumFieldTypes)fieldType[i]); } [MonoTODO] @@ -357,11 +365,11 @@ namespace Mono.Data.MySql { [MonoTODO] public Type GetFieldType(int i) { - enum_field_types fieldEnum; + MySqlEnumFieldTypes fieldEnum; DbType dbType; Type typ; - fieldEnum = (enum_field_types) fieldType[i]; + fieldEnum = (MySqlEnumFieldTypes) fieldType[i]; dbType = MySqlHelper.MySqlTypeToDbType(fieldEnum); typ = MySqlHelper.DbTypeToSystemType (dbType); diff --git a/mcs/class/Mono.Data.MySql/Mono.Data.MySql/MySqlTypes.cs b/mcs/class/Mono.Data.MySql/Mono.Data.MySql/MySqlTypes.cs index 252a8d7d51a..4cbab737098 100644 --- a/mcs/class/Mono.Data.MySql/Mono.Data.MySql/MySqlTypes.cs +++ b/mcs/class/Mono.Data.MySql/Mono.Data.MySql/MySqlTypes.cs @@ -10,11 +10,14 @@ // (c)copyright 2002 Daniel Morgan // -using System; -using System.Data; +using System; +using System.Collections; +using System.Data; +using System.Data.Common; +using System.Text; namespace Mono.Data.MySql { - internal enum enum_field_types { + internal enum MySqlEnumFieldTypes { FIELD_TYPE_DECIMAL, FIELD_TYPE_TINY, FIELD_TYPE_SHORT, @@ -41,72 +44,153 @@ namespace Mono.Data.MySql { } sealed internal class MySqlHelper { - - public static DbType MySqlTypeToDbType(enum_field_types mysqlFieldType) { + + public static string GetMySqlTypeName(MySqlEnumFieldTypes mysqlFieldType) { + + string typeName; + + switch(mysqlFieldType) { + case MySqlEnumFieldTypes.FIELD_TYPE_DECIMAL: + typeName = "decimal"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_TINY: + typeName = "tiny"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_SHORT: + typeName = "short"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_LONG: + typeName = "long"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_FLOAT: + typeName = "float"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_DOUBLE: + typeName = "double"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_NULL: + typeName = "null"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_TIMESTAMP: + typeName = "timestamp"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_LONGLONG: + typeName = "longlong"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_INT24: + typeName = "int24"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_DATE: + typeName = "date"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_TIME: + typeName = "time"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_DATETIME: + typeName = "datetime"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_YEAR: + typeName = "year"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_NEWDATE: + typeName = "newdate"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_ENUM: + typeName = "enum"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_SET: + typeName = "set"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_TINY_BLOB: + typeName = "tinyblob"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_MEDIUM_BLOB: + typeName = "mediumblob"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_LONG_BLOB: + typeName = "longblob"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_BLOB: + typeName = "blob"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_VAR_STRING: + typeName = "varchar"; + break; + case MySqlEnumFieldTypes.FIELD_TYPE_STRING: + typeName = "char"; + break; + default: + typeName = "text"; + break; + } + return typeName; + } + + public static DbType MySqlTypeToDbType(MySqlEnumFieldTypes mysqlFieldType) { DbType dbType; // FIXME: verify these translation are correct switch(mysqlFieldType) { - case enum_field_types.FIELD_TYPE_DECIMAL: + case MySqlEnumFieldTypes.FIELD_TYPE_DECIMAL: dbType = DbType.Decimal; break; - case enum_field_types.FIELD_TYPE_TINY: + case MySqlEnumFieldTypes.FIELD_TYPE_TINY: dbType = DbType.Int16; break; - case enum_field_types.FIELD_TYPE_SHORT: + case MySqlEnumFieldTypes.FIELD_TYPE_SHORT: dbType = DbType.Int16; break; - case enum_field_types.FIELD_TYPE_LONG: + case MySqlEnumFieldTypes.FIELD_TYPE_LONG: dbType = DbType.Int32; break; - case enum_field_types.FIELD_TYPE_FLOAT: + case MySqlEnumFieldTypes.FIELD_TYPE_FLOAT: dbType = DbType.Single; break; - case enum_field_types.FIELD_TYPE_DOUBLE: + case MySqlEnumFieldTypes.FIELD_TYPE_DOUBLE: dbType = DbType.Double; break; - case enum_field_types.FIELD_TYPE_NULL: + case MySqlEnumFieldTypes.FIELD_TYPE_NULL: dbType = DbType.String; break; - case enum_field_types.FIELD_TYPE_TIMESTAMP: - dbType = DbType.DateTime; + case MySqlEnumFieldTypes.FIELD_TYPE_TIMESTAMP: + dbType = DbType.String; break; - case enum_field_types.FIELD_TYPE_LONGLONG: + case MySqlEnumFieldTypes.FIELD_TYPE_LONGLONG: dbType = DbType.Int64; break; - case enum_field_types.FIELD_TYPE_INT24: + case MySqlEnumFieldTypes.FIELD_TYPE_INT24: dbType = DbType.Int64; break; - case enum_field_types.FIELD_TYPE_DATE: + case MySqlEnumFieldTypes.FIELD_TYPE_DATE: dbType = DbType.Date; break; - case enum_field_types.FIELD_TYPE_TIME: + case MySqlEnumFieldTypes.FIELD_TYPE_TIME: dbType = DbType.Time; break; - case enum_field_types.FIELD_TYPE_DATETIME: + case MySqlEnumFieldTypes.FIELD_TYPE_DATETIME: dbType = DbType.DateTime; break; - case enum_field_types.FIELD_TYPE_YEAR: + case MySqlEnumFieldTypes.FIELD_TYPE_YEAR: dbType = DbType.Int16; break; - case enum_field_types.FIELD_TYPE_NEWDATE: + case MySqlEnumFieldTypes.FIELD_TYPE_NEWDATE: dbType = DbType.Date; break; - case enum_field_types.FIELD_TYPE_ENUM: + case MySqlEnumFieldTypes.FIELD_TYPE_ENUM: dbType = DbType.Int32; break; - case enum_field_types.FIELD_TYPE_SET: + case MySqlEnumFieldTypes.FIELD_TYPE_SET: dbType = DbType.String; break; - case enum_field_types.FIELD_TYPE_TINY_BLOB: - case enum_field_types.FIELD_TYPE_MEDIUM_BLOB: - case enum_field_types.FIELD_TYPE_LONG_BLOB: - case enum_field_types.FIELD_TYPE_BLOB: + case MySqlEnumFieldTypes.FIELD_TYPE_TINY_BLOB: + case MySqlEnumFieldTypes.FIELD_TYPE_MEDIUM_BLOB: + case MySqlEnumFieldTypes.FIELD_TYPE_LONG_BLOB: + case MySqlEnumFieldTypes.FIELD_TYPE_BLOB: dbType = DbType.Binary; break; - case enum_field_types.FIELD_TYPE_VAR_STRING: - case enum_field_types.FIELD_TYPE_STRING: + case MySqlEnumFieldTypes.FIELD_TYPE_VAR_STRING: + case MySqlEnumFieldTypes.FIELD_TYPE_STRING: dbType = DbType.String; break; default: @@ -159,5 +243,228 @@ namespace Mono.Data.MySql { return typ; } + // Converts data value from database to .NET System type. + public static object ConvertDbTypeToSystem (DbType typ, String myValue) { + object obj = null; + + //Console.WriteLine("DEBUG: ConvertDbTypeToSystem: " + myValue); + + if(myValue == null) { + return DBNull.Value; + } + else if(myValue.Equals("")) { + return DBNull.Value; + } + + // Date, Time, and DateTime + // are parsed based on ISO format + // "YYYY-MM-DD hh:mi:ss" + + switch(typ) { + case DbType.String: + obj = String.Copy(myValue); + break; + case DbType.Boolean: + obj = myValue.Equals("t"); + break; + case DbType.Int16: + obj = Int16.Parse(myValue); + break; + case DbType.Int32: + obj = Int32.Parse(myValue); + break; + case DbType.Int64: + obj = Int64.Parse(myValue); + break; + case DbType.Decimal: + obj = Decimal.Parse(myValue); + break; + case DbType.Single: + obj = Single.Parse(myValue); + break; + case DbType.Double: + obj = Double.Parse(myValue); + break; + case DbType.Date: + String[] sd = myValue.Split(new Char[] {'-'}); + obj = new DateTime( + Int32.Parse(sd[0]), Int32.Parse(sd[1]), Int32.Parse(sd[2]), + 0,0,0); + break; + case DbType.Time: + String[] st = myValue.Split(new Char[] {':'}); + obj = new DateTime(0001,01,01, + Int32.Parse(st[0]),Int32.Parse(st[1]),Int32.Parse(st[2])); + break; + case DbType.DateTime: + Int32 YYYY,MM,DD,hh,mi,ss; + YYYY = Int32.Parse(myValue.Substring(0,4)); + MM = Int32.Parse(myValue.Substring(5,2)); + DD = Int32.Parse(myValue.Substring(8,2)); + hh = Int32.Parse(myValue.Substring(11,2)); + mi = Int32.Parse(myValue.Substring(14,2)); + ss = Int32.Parse(myValue.Substring(17,2)); + obj = new DateTime(YYYY,MM,DD,hh,mi,ss,0); + break; + default: + obj = String.Copy(myValue); + break; + } + + return obj; + } + + // Convert a .NET System value type (Int32, String, Boolean, etc) + // to a string that can be included within a SQL statement. + // This is to methods provides the parameters support + // for the MySQL .NET Data provider + public static string ObjectToString(DbType dbtype, object obj) { + + // TODO: how do we handle a NULL? + //if(isNull == true) + // return "NULL"; + + string s; + + // Date, Time, and DateTime are expressed in ISO format + // which is "YYYY-MM-DD hh:mm:ss.ms"; + DateTime dt; + StringBuilder sb; + + const string zero = "0"; + + switch(dbtype) { + case DbType.String: + s = "'" + obj + "'"; + break; + case DbType.Boolean: + if((bool)obj == true) + s = "'t'"; + else + s = "'f'"; + break; + case DbType.Int16: + s = obj.ToString(); + break; + case DbType.Int32: + s = obj.ToString(); + break; + case DbType.Int64: + s = obj.ToString(); + break; + case DbType.Decimal: + s = obj.ToString(); + break; + case DbType.Single: + s = obj.ToString(); + break; + case DbType.Double: + s = obj.ToString(); + break; + case DbType.Date: + dt = (DateTime) obj; + sb = new StringBuilder(); + sb.Append('\''); + // year + if(dt.Year < 10) + sb.Append("000" + dt.Year); + else if(dt.Year < 100) + sb.Append("00" + dt.Year); + else if(dt.Year < 1000) + sb.Append("0" + dt.Year); + else + sb.Append(dt.Year); + sb.Append("-"); + // month + if(dt.Month < 10) + sb.Append(zero + dt.Month); + else + sb.Append(dt.Month); + sb.Append("-"); + // day + if(dt.Day < 10) + sb.Append(zero + dt.Day); + else + sb.Append(dt.Day); + sb.Append('\''); + s = sb.ToString(); + break; + case DbType.Time: + dt = (DateTime) obj; + sb = new StringBuilder(); + sb.Append('\''); + // hour + if(dt.Hour < 10) + sb.Append(zero + dt.Hour); + else + sb.Append(dt.Hour); + sb.Append(":"); + // minute + if(dt.Minute < 10) + sb.Append(zero + dt.Minute); + else + sb.Append(dt.Minute); + sb.Append(":"); + // second + if(dt.Second < 10) + sb.Append(zero + dt.Second); + else + sb.Append(dt.Second); + sb.Append('\''); + s = sb.ToString(); + break; + case DbType.DateTime: + dt = (DateTime) obj; + sb = new StringBuilder(); + sb.Append('\''); + // year + if(dt.Year < 10) + sb.Append("000" + dt.Year); + else if(dt.Year < 100) + sb.Append("00" + dt.Year); + else if(dt.Year < 1000) + sb.Append("0" + dt.Year); + else + sb.Append(dt.Year); + sb.Append("-"); + // month + if(dt.Month < 10) + sb.Append(zero + dt.Month); + else + sb.Append(dt.Month); + sb.Append("-"); + // day + if(dt.Day < 10) + sb.Append(zero + dt.Day); + else + sb.Append(dt.Day); + sb.Append(" "); + // hour + if(dt.Hour < 10) + sb.Append(zero + dt.Hour); + else + sb.Append(dt.Hour); + sb.Append(":"); + // minute + if(dt.Minute < 10) + sb.Append(zero + dt.Minute); + else + sb.Append(dt.Minute); + sb.Append(":"); + // second + if(dt.Second < 10) + sb.Append(zero + dt.Second); + else + sb.Append(dt.Second); + sb.Append('\''); + s = sb.ToString(); + break; + default: + // default to DbType.String + s = "'" + obj + "'"; + break; + } + return s; + } } } -- cgit v1.2.3