1 // 2 // Copyright (c) 2006 Mainsoft Co. 3 // 4 // Permission is hereby granted, free of charge, to any person obtaining 5 // a copy of this software and associated documentation files (the 6 // "Software"), to deal in the Software without restriction, including 7 // without limitation the rights to use, copy, modify, merge, publish, 8 // distribute, sublicense, and/or sell copies of the Software, and to 9 // permit persons to whom the Software is furnished to do so, subject to 10 // the following conditions: 11 // 12 // The above copyright notice and this permission notice shall be 13 // included in all copies or substantial portions of the Software. 14 // 15 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, 16 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF 17 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND 18 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE 19 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION 20 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION 21 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. 22 // 23 24 using System; 25 using System.Text; 26 using System.Data; 27 using System.Data.OracleClient ; 28 29 using MonoTests.System.Data.Utils; 30 31 32 using NUnit.Framework; 33 #if DAAB 34 using Microsoft.ApplicationBlocks; 35 #endif 36 37 namespace MonoTests.System.Data.OracleClient 38 { 39 [TestFixture] 40 public class OracleCommand_ExecuteReader : ADONetTesterClass 41 { 42 OracleConnection con; 43 OracleCommand cmd; 44 45 [SetUp] SetUp()46 public void SetUp() 47 { 48 Exception exp = null; 49 BeginCase("Setup"); 50 try 51 { 52 con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); 53 cmd = new OracleCommand("", con); 54 con.Open(); 55 this.Pass("Setup."); 56 } 57 catch(Exception ex) {exp = ex;} 58 finally {EndCase(exp); exp = null;} 59 } 60 61 [TearDown] TearDown()62 public void TearDown() 63 { 64 if (con != null) 65 { 66 if (con.State == ConnectionState.Open) con.Close(); 67 } 68 } 69 Main()70 public static void Main() 71 { 72 OracleCommand_ExecuteReader tc = new OracleCommand_ExecuteReader(); 73 Exception exp = null; 74 try 75 { 76 tc.BeginTest("OracleCommand_ExecuteReader"); 77 tc.SetUp(); 78 tc.run(); 79 tc.TearDown(); 80 } 81 catch(Exception ex){exp = ex;} 82 finally {tc.EndTest(exp);} 83 } 84 85 [Test] run()86 public void run() 87 { 88 Exception exp = null; 89 bool RecordsExists = false; 90 OracleDataReader rdr =null; 91 92 // testBug3965(); 93 // TestMultipleResultsets(); 94 // TestCompoundVariable(); 95 96 cmd.CommandText = "Select FirstName,City From Employees"; 97 if (con.State != ConnectionState.Open) 98 { 99 con.Open(); 100 } 101 102 try 103 { 104 BeginCase("check reader is null"); 105 rdr = cmd.ExecuteReader(); 106 Compare(rdr==null, false); 107 } 108 catch(Exception ex){exp = ex;} 109 finally 110 { 111 if (rdr != null) rdr.Close(); 112 EndCase(exp); 113 exp = null; 114 } 115 116 try 117 { 118 BeginCase("check reader.read"); 119 rdr = cmd.ExecuteReader(); 120 RecordsExists = rdr.Read(); 121 Compare(RecordsExists ,true); 122 } 123 catch(Exception ex){exp = ex;} 124 finally 125 { 126 if (rdr != null) rdr.Close(); 127 EndCase(exp); 128 exp = null; 129 } 130 131 try 132 { 133 BeginCase("execute reader again "); 134 rdr = cmd.ExecuteReader(); 135 Compare(rdr==null, false); 136 } 137 catch(Exception ex){exp = ex;} 138 finally 139 { 140 if (rdr != null) rdr.Close(); 141 EndCase(exp); 142 exp = null; 143 } 144 145 try 146 { 147 BeginCase("Test compound SQL statement"); 148 //Build a compund SQL command. 149 string[] sqlStatements = new string[] { 150 "INSERT INTO Categories (CategoryName, Description) VALUES('__TEST_RECORD__', 'Inserted')", 151 "UPDATE Categories SET Description='Updated' WHERE CategoryName='__TEST_RECORD__'", 152 "DELETE FROM Categories WHERE CategoryName='__TEST_RECORD__'" , 153 }; 154 cmd.CommandText = CreateCompundSqlStatement(sqlStatements, ConnectedDataProvider.GetDbType()); 155 rdr = cmd.ExecuteReader(); 156 Compare(rdr.Read(), false); 157 } 158 catch(Exception ex){exp = ex;} 159 finally 160 { 161 if (rdr != null) rdr.Close(); 162 EndCase(exp); 163 exp = null; 164 } 165 166 167 if (ConnectedDataProvider.GetDbType() != DataBaseServer.Oracle) 168 { 169 try 170 { 171 BeginCase("Check that in a compound SQL statement, resultsets are returned only for SELECT statements. (bug #3358)"); 172 //prepare db: 173 OracleCommand prepare = new OracleCommand("DELETE FROM Categories WHERE CategoryName='__TEST_RECORD__'", con); 174 prepare.ExecuteNonQuery(); 175 176 177 //Test body 178 int resultSetCount ; 179 180 //Build a compund SQL command that contains only one select statement. 181 string[] sqlStatements = new string[] { 182 "INSERT INTO Categories (CategoryName, Description) VALUES('__TEST_RECORD__', 'Inserted')", 183 "UPDATE Categories SET Description='Updated' WHERE CategoryName='__TEST_RECORD__'", 184 "DELETE FROM Categories WHERE CategoryName='__TEST_RECORD__'" , 185 "SELECT * FROM Categories " 186 }; 187 string insertCmdTxt = CreateCompundSqlStatement(sqlStatements, ConnectedDataProvider.GetDbType()); 188 //this.Log(insertCmdTxt); 189 OracleCommand InsertCmd = new OracleCommand(insertCmdTxt, con); 190 rdr = InsertCmd.ExecuteReader(); 191 192 //Count the number of result sets. 193 resultSetCount = 0; 194 do 195 { 196 resultSetCount++; 197 }while (rdr.NextResult()); 198 199 //Test that there is only one result set. 200 Compare(resultSetCount, 1); 201 } 202 catch(Exception ex){exp = ex;} 203 finally 204 { 205 if (rdr != null) rdr.Close(); 206 EndCase(exp); 207 exp = null; 208 //cleanup db: 209 OracleCommand cleanup = new OracleCommand("DELETE FROM Categories WHERE CategoryName='__TEST_RECORD__'", con); 210 cleanup.ExecuteNonQuery(); 211 } 212 } 213 } 214 215 216 //Create the compund sql statement according to the dbserver. CreateCompundSqlStatement(string[] sqlStatements, DataBaseServer dbServer)217 private string CreateCompundSqlStatement(string[] sqlStatements, DataBaseServer dbServer) 218 { 219 string beginStatement; 220 string endStatement; 221 string commandDelimiter; 222 223 GetDBSpecificSyntax(dbServer, out beginStatement, out endStatement, out commandDelimiter); 224 225 StringBuilder cmdBuilder = new StringBuilder(); 226 cmdBuilder.Append(beginStatement); 227 cmdBuilder.Append(" "); 228 foreach (string statement in sqlStatements) 229 { 230 cmdBuilder.Append(statement); 231 cmdBuilder.Append(commandDelimiter); 232 cmdBuilder.Append(" "); 233 } 234 cmdBuilder.Append(endStatement); 235 236 return cmdBuilder.ToString(); 237 } 238 GetDBSpecificSyntax(DataBaseServer dbServer, out string beginStatement, out string endStatement, out string commandDelimiter)239 private void GetDBSpecificSyntax(DataBaseServer dbServer, out string beginStatement, out string endStatement, out string commandDelimiter) 240 { 241 switch (dbServer) 242 { 243 case DataBaseServer.SQLServer: 244 case DataBaseServer.PostgreSQL: 245 beginStatement = ""; 246 endStatement = ""; 247 commandDelimiter = ";"; 248 break; 249 250 case DataBaseServer.Sybase: 251 beginStatement = "BEGIN"; 252 endStatement = "END"; 253 commandDelimiter = ""; 254 break; 255 case DataBaseServer.Oracle: 256 beginStatement = "BEGIN"; 257 endStatement = "END;"; 258 commandDelimiter = ";"; 259 break; 260 261 case DataBaseServer.DB2: 262 beginStatement = "BEGIN ATOMIC"; 263 endStatement = "END"; 264 commandDelimiter = ";"; 265 break; 266 267 default: 268 this.Fail("Unknown DataBaseServer type"); 269 throw new ApplicationException("Unknown DataBaseServer type"); 270 } 271 } 272 273 274 [Test] TestMultipleResultsets()275 public void TestMultipleResultsets() 276 { 277 #if !JAVA 278 if (ConnectedDataProvider.GetDbType(con) == DataBaseServer.Oracle) 279 { 280 //In .NET there is a bug when calling a SP with multiple REFCURSORS, the workaround is to use OracleClient and not Oracle. 281 //In GH we are not bug complient in this issue, because there is no workaround (We do not support the OracleClient namespace. 282 this.Log("Not testing multi result set Oracle on .NET"); 283 return; 284 } 285 286 if (ConnectedDataProvider.GetDbType(con) == DataBaseServer.PostgreSQL) 287 { 288 // fail to work on .NET OLEDB 289 //reader = Microsoft.ApplicationBlocks.Data.PostgresOracleHelper.ADOExecuteReader(cmd1); 290 this.Log("Not testing PostgreSQL CommandType.StoredProcedure which return SETOF"); 291 return; 292 } 293 #endif 294 295 Exception exp = null; 296 BeginCase("Test multi result set from stored procedure"); 297 298 299 OracleDataReader reader = null; 300 OracleTransaction tr = null; 301 302 try 303 { 304 //Check SP with the structre : insert Select + update Select + delete Select 305 if (con.State != ConnectionState.Open) 306 { 307 con.Open(); 308 } 309 310 // transaction use was add for PostgreSQL 311 tr = con.BeginTransaction(); 312 OracleCommand cmd1 = new OracleCommand("GHSP_TYPES_SIMPLE_4", con, tr); 313 cmd1.CommandType = CommandType.StoredProcedure; 314 315 OracleParameter param = new OracleParameter(); 316 param.ParameterName = "ID1"; 317 param.Value = string.Format("13268_{0}", this.TestCaseNumber); 318 param.OracleType = OracleType.VarChar; 319 cmd1.Parameters.Add(param); 320 cmd1.Parameters.Add(new OracleParameter("RESULT", OracleType.Cursor)).Direction = ParameterDirection.Output; 321 cmd1.Parameters.Add(new OracleParameter("RESULT1", OracleType.Cursor)).Direction = ParameterDirection.Output; 322 cmd1.Parameters.Add(new OracleParameter("RESULT2", OracleType.Cursor)).Direction = ParameterDirection.Output; 323 324 325 reader = cmd1.ExecuteReader(); 326 327 //Count the number of result sets. 328 int resultSetCount = 0; 329 //Count the number of the records 330 int recordCounter=0; 331 332 do 333 { 334 //this.Log(string.Format("resultSetCount:{0}",resultSetCount)); 335 while (reader.Read()) 336 { 337 recordCounter++; 338 } 339 //this.Log(string.Format("recordCounter:{0}",recordCounter)); 340 if (resultSetCount != 2) 341 { 342 Compare(recordCounter,1); //Insert + update 343 } 344 else 345 { 346 Compare(recordCounter,0); //Delete 347 } 348 349 recordCounter=0; 350 resultSetCount++; 351 }while (reader.NextResult()); 352 353 Compare(resultSetCount,3); 354 } 355 catch (Exception ex) 356 { 357 exp=ex; 358 } 359 finally 360 { 361 EndCase(exp); 362 if (reader != null) reader.Close(); 363 tr.Commit(); 364 con.Close(); 365 } 366 } 367 [Test] TestCompoundVariable()368 public void TestCompoundVariable() 369 { 370 OracleDataReader rdr = null; 371 if (ConnectedDataProvider.GetDbType(con) == DataBaseServer.PostgreSQL) 372 { 373 this.Log("not testing PostgreSQL"); 374 return; 375 } 376 377 Exception exp = null; 378 try 379 { 380 BeginCase("Check sql statement that declares a local variable and uses it."); 381 382 if (con.State != ConnectionState.Open) 383 { 384 con.Open(); 385 } 386 387 string sqlTxt = ""; 388 switch (ConnectedDataProvider.GetDbType(cmd.Connection)) 389 { 390 case DataBaseServer.SQLServer: 391 sqlTxt = "declare @var int; select @var=1;"; 392 break; 393 case DataBaseServer.Sybase: 394 sqlTxt = "declare @var int select @var=1"; 395 break; 396 case DataBaseServer.Oracle: 397 sqlTxt = "declare var int;begin var:=1;end;"; 398 break; 399 case DataBaseServer.DB2: 400 sqlTxt = "begin atomic declare var integer; set var = 1; end"; 401 break; 402 case DataBaseServer.PostgreSQL: 403 // we don't know how the heck to do this in PostgreSQL 404 sqlTxt = ""; 405 break; 406 default: 407 throw new ApplicationException(string.Format("GHT: Unknown DataBaseServer '{0}'", ConnectedDataProvider.GetDbType(cmd.Connection))); 408 } 409 cmd.CommandText = sqlTxt; 410 rdr = cmd.ExecuteReader(); 411 Compare(rdr.Read(), false); 412 } 413 catch(Exception ex){exp = ex;} 414 finally 415 { 416 if (rdr != null) rdr.Close(); 417 EndCase(exp); 418 exp = null; 419 } 420 } 421 } 422 423 }