mcs/class/System.Data/Test/DataProviderTests/datareadertests/OracleDataReaderTest.cs

   1 //
   2 // OracleDataReaderTest.cs :- Defines a class OraRetrieve' derived from the
   3 //                            'BaseRetrieve' class :
   4 //                                 - Contains code specific to oracle database
   5 //
   6 // Author:
   7 //   Satya Sudha K (ksathyasudha@novell.com)
   8 //
   9 //
  10 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
  11 //
  12 // Permission is hereby granted, free of charge, to any person obtaining
  13 // a copy of this software and associated documentation files (the
  14 // "Software"), to deal in the Software without restriction, including
  15 // without limitation the rights to use, copy, modify, merge, publish,
  16 // distribute, sublicense, and/or sell copies of the Software, and to
  17 // permit persons to whom the Software is furnished to do so, subject to
  18 // the following conditions:
  19 //
  20 // The above copyright notice and this permission notice shall be
  21 // included in all copies or substantial portions of the Software.
  22 //
  23 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  24 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  25 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  26 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  27 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  28 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  29 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  30 //
  31
  32 using System;
  33 using System.Xml.XPath;
  34 using System.Data;
  35 using System.Data.OracleClient;
  36 using System.Text.RegularExpressions;
  37
  38 namespace MonoTests.System.Data {
  39
  40         public class OraRetrieve : BaseRetrieve {
  41
  42                 public OraRetrieve (string database) : base (database)
  43                 {
  44                 }
  45
  46                 // returns a Open connection
  47                 public override void GetConnection ()
  48                 {
  49                         string connectionString = null;
  50                         try {
  51                                 connectionString = ConfigClass.GetElement (configDoc, "database", "connectionString");
  52                         } catch (XPathException e) {
  53                                 Console.WriteLine ("Error reading the config file !!");
  54                                 Console.WriteLine (e.Message);
  55                                 return;
  56                         }
  57
  58                         con = new OracleConnection (connectionString);
  59
  60                         try {
  61                                 con.Open ();
  62                         } catch (OracleException e) {
  63                                 Console.WriteLine ("Cannot establish connection with the database");
  64                                 Console.WriteLine ("Probably the database is down");
  65                                 con = null;
  66                         } catch (InvalidOperationException e) {
  67                                 Console.WriteLine ("Cannot open connection");
  68                                 Console.WriteLine ("Probably the connection is already open");
  69                                 con = null;
  70                         } catch (Exception e) {
  71                                 Console.WriteLine ("Cannot open connection");
  72                                 con = null;
  73                         }
  74                 }
  75
  76                 public override object GetValue (IDataReader reader, int columnIndex)
  77                 {
  78
  79                         object value = null;
  80
  81                         if (reader.IsDBNull (columnIndex))
  82                                 return null;
  83
  84                         OracleDataReader rdr = (OracleDataReader) reader;
  85                         Type type = rdr.GetFieldType (columnIndex);
  86                         string datatypeName = rdr.GetDataTypeName (columnIndex);
  87
  88                         if (datatypeName.ToLower ().Equals ("interval year to month")) {
  89                                 value = rdr.GetOracleMonthSpan (columnIndex);
  90                                 return value;
  91                         }
  92
  93                         switch (type.Name.ToLower ()) {
  94
  95                         case "int32": value = rdr.GetInt32 (columnIndex);
  96                                 break;
  97                         case "decimal" :
  98                                 try {
  99                                         value = rdr.GetDecimal (columnIndex);
 100                                 } catch (Exception e) {
 101                                         value = rdr.GetOracleNumber (columnIndex);
 102                                 }
 103                                 break;
 104                         case "datetime": value = rdr.GetDateTime (columnIndex);
 105                                         break;
 106                         case "string": value = rdr.GetString (columnIndex);
 107                                         break;
 108                         case "timespan" : value = rdr.GetTimeSpan (columnIndex);
 109                                         break;
 110                         default :      value = rdr.GetValue (columnIndex);
 111                                         break;
 112                         }
 113                         return value;
 114                 }
 115
 116                 public override Boolean AreEqual (object obj, string value, ref string errorMsg)
 117                 {
 118                         if ((obj == null ) || (value.Equals ("null"))) {
 119                                 if (obj == null && value.Equals ("null"))
 120                                         return true;
 121                                 return false;
 122                         }
 123
 124                         object valObj = ConvertToValueType (obj.GetType (), value, ref errorMsg);
 125                         return valObj.Equals (obj);
 126                 }
 127
 128                 public override object ConvertToValueType (Type objType, string value, ref string errorMsg)
 129                 {
 130
 131                         value = value.Trim ('\'');
 132                         value = value.Trim ('\"');
 133
 134                         switch (objType.ToString ()) {
 135
 136                         case "System.Data.OracleClient.OracleNumber" :
 137                                 return ConvertToOracleNumber (objType, value, ref errorMsg);
 138                         case "System.Data.OracleClient.OracleMonthSpan" :
 139                                 return ConvertToOracleMonthSpan (objType, value, ref errorMsg);
 140                         case "System.Decimal" :
 141                                 return ConvertToDecimal (objType, value, ref errorMsg);
 142                         case "System.String" :
 143                                 return value;
 144                         case "System.TimeSpan" :
 145                                 return ConvertToTimespan (objType, value, ref errorMsg);
 146                         case "System.DateTime" :
 147                                 return ConvertToDateTime (objType, value, ref errorMsg);
 148                         }
 149
 150                         return ConvertValue (objType, value, ref errorMsg);
 151                 }
 152
 153                 public override object ConvertToDecimal (Type type, string value, ref string errorMsg)
 154                 {
 155                         decimal decimalval;
 156                         try {
 157                                 decimalval = Convert.ToDecimal (value);
 158                         } catch (FormatException e) {
 159                                 // This may be bcoz value is of the form 'd.ddEdd'
 160                                 Double doubleVal = Convert.ToDouble (value);
 161                                 decimalval = Convert.ToDecimal (doubleVal);
 162                         } catch (Exception e) {
 163                                 errorMsg = "ERROR : " + e.Message;
 164                                 errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 165                                 return false;
 166                         }
 167                         return decimalval;
 168                 }
 169
 170                 public override object ConvertToDateTime (Type type, string value, ref string errorMsg)
 171                 {
 172                         value = value.ToLower ().Trim ();
 173                         string format = null, date = null;
 174                         if (value.StartsWith ("to_date")) {
 175                                 value = value.Remove (0, 8);
 176                                 value = value.Remove (value.Length - 1, 1);
 177                                 string [] dateParts = value.Split (',');
 178                                 date = dateParts [0].Trim ();
 179                                 format = dateParts [1].Trim ();
 180
 181                                 date = date.Trim ('\'');
 182                                 format = format.Trim ('\'');
 183                                 // Assuming that date will be in yyyy-mm-dd hh24:mi:ss only
 184                                 format = format.Replace ("yyyy", "(?<yyyy>\\d{4})");
 185                                 format = format.Replace ("mm", "(?<mm>\\d{2})");
 186                                 format = format.Replace ("dd", "(?<dd>\\d{2})");
 187                                 format = format.Replace ("hh24", "(?<hh24>\\d{2})");
 188                                 format = format.Replace ("mi", "(?<mi>\\d{2})");
 189                                 format = format.Replace ("ss", "(?<ss>\\d{2})");
 190                         } else if (value.ToLower().StartsWith ("timestamp")) {
 191                                 value = value.Remove (0, 9);
 192                                 value = value.Trim ();
 193                                 date = value.Trim ('\'');
 194                                 format = "\\b(?<yyyy>\\d{4})\\-(?<mm>\\d{2})\\-(?<dd>\\d{2}) (?<hh24>\\d{2}):(?<mi>\\d{2}):(?<ss>\\d{2})";
 195
 196                         }
 197
 198                         Regex re = new Regex (format);
 199                         Match m = re.Match (date);
 200                         if (!m.Success)
 201                                 return false;
 202                         int year = Convert.ToInt32 (m.Result ("${yyyy}"));
 203                         int month = Convert.ToInt32 (m.Result ("${mm}"));
 204                         int day = Convert.ToInt32 (m.Result ("${dd}"));
 205                         int hour = Convert.ToInt32 (m.Result ("${hh24}"));
 206                         int min = Convert.ToInt32 (m.Result ("${mi}"));
 207                         int sec = Convert.ToInt32 (m.Result ("${ss}"));
 208
 209                         DateTime newDt ;
 210                         try {
 211                                 newDt = new DateTime (year, month, day, hour, min, sec);
 212                         } catch (Exception e) {
 213                                 errorMsg = "ERROR : " + e.Message;
 214                                 errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 215                                 return false;
 216                         }
 217                         return newDt;
 218                 }
 219
 220                 object ConvertToOracleNumber (Type type, string value, ref string errorMsg)
 221                 {
 222                         return OracleNumber.Parse (value);
 223                 }
 224
 225                 object ConvertToOracleMonthSpan (Type type, string value, ref string errorMsg)
 226                 {
 227                         int intervalInMonths = 0;
 228                         bool isNegative = false;
 229                         if (value.StartsWith ("-")) {
 230                                 isNegative = true;
 231                                 value = value.Trim ('-');
 232                         }
 233                         string [] intParts = value.Split ('-');
 234                         if (intParts.Length > 0) {
 235                                 intervalInMonths = Convert.ToInt32 (intParts [0]) * 12;
 236                                 if (intParts.Length > 1)
 237                                         intervalInMonths += Convert.ToInt32 (intParts [1]);
 238                         } else {// Should not come here
 239                                 return false;
 240                         }
 241                         if (isNegative) {
 242                                 intervalInMonths *= -1;
 243                         }
 244                         return new OracleMonthSpan (intervalInMonths);
 245                 }
 246
 247                 public override object ConvertToTimespan (Type type, string value, ref string errorMsg)
 248                 {
 249                         // Input in the form '[-]dd hh:mi:ss'
 250                         value = value.Replace (" ", ".");
 251                         return TimeSpan.Parse (value);
 252                 }
 253
 254                 public override IDataReader QueryUsingStoredProc (IDbCommand cmd,
 255                                                                  string storedProcName,
 256                                                                  string paramName)
 257                 {
 258                         IDataReader reader = null;
 259                         OracleCommand command = (OracleCommand) cmd;
 260                         command.CommandText = storedProcName;
 261                         command.CommandType = CommandType.StoredProcedure;
 262                         OracleParameter pOutput = command.Parameters.Add ("ref_cur", OracleType.Cursor);
 263                         pOutput.Direction = ParameterDirection.Output;
 264                         try {
 265                                 reader =  cmd.ExecuteReader ();
 266                         } catch (Exception e) {
 267                                 Console.WriteLine (e.Message);
 268                                 Console.WriteLine (e.StackTrace);
 269                                 reader = null;
 270                         }
 271                         command.Parameters.Remove (pOutput);
 272                         return reader;
 273                 }
 274         }
 275 }