2 // OracleParameterTest.cs -
3 // NUnit Test Cases for OracleParameter
6 // Leszek Ciesielski <skolima@gmail.com>
8 // Copyright (C) 2006 Forcom (http://www.forcom.com.pl/)
10 // Permission is hereby granted, free of charge, to any person obtaining
11 // a copy of this software and associated documentation files (the
12 // "Software"), to deal in the Software without restriction, including
13 // without limitation the rights to use, copy, modify, merge, publish,
14 // distribute, sublicense, and/or sell copies of the Software, and to
15 // permit persons to whom the Software is furnished to do so, subject to
16 // the following conditions:
18 // The above copyright notice and this permission notice shall be
19 // included in all copies or substantial portions of the Software.
21 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
22 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
23 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
24 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
25 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
26 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
27 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
31 using System.Configuration;
33 using System.Data.OracleClient;
34 using System.Globalization;
35 using System.Threading;
37 using NUnit.Framework;
39 namespace MonoTests.System.Data.OracleClient
42 public class OracleParameterTest
44 String connection_string;
45 OracleConnection connection;
46 OracleCommand command;
49 string test_value = " simply trim test ";
50 string test_value2 = " simply trim test in query ";
53 public void FixtureSetUp ()
55 connection_string = ConfigurationSettings.AppSettings.Get ("ConnectionString");
61 if (connection_string == null)
64 connection = new OracleConnection (connection_string);
66 using (command = connection.CreateCommand ()) {
69 "create table oratest (id number(10), text varchar2(64),"
70 + " text2 varchar2(64) )";
71 command.ExecuteNonQuery ();
74 "create table culture_test (id number(10), value1 float,"
75 + " value2 number(20,10), value3 number (20,10))";
76 command.ExecuteNonQuery ();
79 "create table oratypes_test (id NUMBER(10), value1 VARCHAR2(100),"
81 command.ExecuteNonQuery ();
84 "create or replace procedure params_pos_test (param1 in number,"
85 + "param2 in number,param3 in number,result out number) as"
86 + " begin result:=param3; end;";
87 command.ExecuteNonQuery ();
92 public void TearDown ()
94 if (connection_string == null)
97 using (command = connection.CreateCommand ()) {
98 command.CommandText = "drop table oratest";
99 command.ExecuteNonQuery ();
100 command.CommandText = "drop table culture_test";
101 command.ExecuteNonQuery ();
102 command.CommandText = "drop table oratypes_test";
103 command.ExecuteNonQuery ();
107 connection.Dispose ();
111 public void Constructor1 ()
113 OracleParameter param = new OracleParameter ();
114 Assert.AreEqual (DbType.AnsiString, param.DbType, "#1");
115 Assert.AreEqual (ParameterDirection.Input, param.Direction, "#2");
116 Assert.IsFalse (param.IsNullable, "#3");
117 Assert.AreEqual (OracleType.VarChar, param.OracleType, "#4");
118 Assert.AreEqual (string.Empty, param.ParameterName, "#5");
119 Assert.AreEqual ((byte) 0, param.Precision, "#6");
120 Assert.AreEqual ((byte) 0, param.Scale, "#7");
121 Assert.AreEqual (0, param.Size, "#8");
122 Assert.AreEqual (string.Empty, param.SourceColumn, "#9");
123 Assert.IsFalse (param.SourceColumnNullMapping, "#10");
124 Assert.AreEqual (DataRowVersion.Current, param.SourceVersion, "#11");
125 Assert.IsNull (param.Value, "#12");
129 public void Constructor2 ()
131 OracleParameter param;
133 param = new OracleParameter ("firstName", "Miguel");
134 Assert.AreEqual (DbType.AnsiString, param.DbType, "#A1");
135 Assert.AreEqual (ParameterDirection.Input, param.Direction, "#A2");
136 Assert.IsFalse (param.IsNullable, "#A3");
137 Assert.AreEqual (OracleType.VarChar, param.OracleType, "#A4");
138 Assert.AreEqual ("firstName", param.ParameterName, "#A5");
139 Assert.AreEqual ((byte) 0, param.Precision, "#A6");
140 Assert.AreEqual ((byte) 0, param.Scale, "#A7");
141 Assert.AreEqual (6, param.Size, "#A8");
142 Assert.AreEqual (string.Empty, param.SourceColumn, "#A9");
143 Assert.IsFalse (param.SourceColumnNullMapping, "#A10");
144 Assert.AreEqual (DataRowVersion.Current, param.SourceVersion, "#A11");
145 Assert.AreEqual ("Miguel", param.Value, "#A12");
147 param = new OracleParameter ((string) null, new DateTime (2006, 1, 5));
148 Assert.AreEqual (DbType.DateTime, param.DbType, "#B1");
149 Assert.AreEqual (ParameterDirection.Input, param.Direction, "#B2");
150 Assert.IsFalse (param.IsNullable, "#B3");
151 Assert.AreEqual (OracleType.DateTime, param.OracleType, "#B4");
152 Assert.AreEqual (string.Empty, param.ParameterName, "#B5");
153 Assert.AreEqual ((byte) 0, param.Precision, "#B6");
154 Assert.AreEqual ((byte) 0, param.Scale, "#B7");
155 Assert.AreEqual (7, param.Size, "#B8");
156 Assert.AreEqual (string.Empty, param.SourceColumn, "#B9");
157 Assert.IsFalse (param.SourceColumnNullMapping, "#B10");
158 Assert.AreEqual (DataRowVersion.Current, param.SourceVersion, "#B11");
159 Assert.AreEqual (new DateTime (2006, 1, 5), param.Value, "#B12");
163 public void ParameterName ()
165 OracleParameter param = new OracleParameter ("A", "B");
166 param.ParameterName = null;
167 Assert.AreEqual (string.Empty, param.ParameterName, "#1");
168 param.ParameterName = "B";
169 Assert.AreEqual ("B", param.ParameterName, "#2");
170 param.ParameterName = string.Empty;
171 Assert.AreEqual (string.Empty, param.ParameterName, "#3");
175 public void TrimsTrailingSpacesTest ()
177 if (connection_string == null)
178 Assert.Ignore ("Please consult README.tests.");
180 using (command = connection.CreateCommand ()) { // reusing command from SetUp causes parameter names mismatch
181 // insert test values
182 command.CommandText =
183 "insert into oratest (id,text,text2) values (:id,:txt,'"
184 + test_value2 + "')";
185 command.Parameters.Add (new OracleParameter ("ID", OracleType.Int32));
186 command.Parameters.Add( new OracleParameter ("TXT", OracleType.VarChar));
187 command.Parameters ["ID"].Value = 100;
188 command.Parameters ["TXT"].Value = test_value;
189 command.ExecuteNonQuery ();
192 command.CommandText =
193 "select text,text2 from oratest where id = 100";
194 command.Parameters.Clear ();
195 using (OracleDataReader reader = command.ExecuteReader ()) {
196 if (reader.Read ()) {
197 Assert.AreEqual (test_value2, reader.GetString (1), "Directly passed value mismatched");
198 Assert.AreEqual (test_value, reader.GetString (0), "Passed through bind value mismatched");
200 Assert.Fail ("Expected records not found.");
207 public void CultureSensitiveNumbersTest ()
209 if (connection_string == null)
210 Assert.Ignore ("Please consult README.tests.");
212 CultureInfo currentCulture = Thread.CurrentThread.CurrentCulture;
214 Thread.CurrentThread.CurrentCulture = new CultureInfo ("en-GB", false);
215 CultureSensitiveNumbersInsertTest (1);
216 CultureSensitiveNumbersSelectTest (1);
218 Thread.CurrentThread.CurrentCulture = new CultureInfo ("pl-PL", false);
219 CultureSensitiveNumbersInsertTest (2);
220 CultureSensitiveNumbersSelectTest (2);
222 Thread.CurrentThread.CurrentCulture = new CultureInfo ("ja-JP", false);
223 CultureSensitiveNumbersInsertTest (3);
224 CultureSensitiveNumbersSelectTest (3);
226 Thread.CurrentThread.CurrentCulture = currentCulture;
229 // regression for bug #79284
230 protected void CultureSensitiveNumbersInsertTest (int id)
232 using (command = connection.CreateCommand ()) { // reusing command from SetUp causes parameter names mismatch
233 // insert test values
234 command.CommandText =
235 "insert into culture_test (id,value1,value2,value3) values (:id,:value1,:value2,:value3)";
236 command.Parameters.Add (new OracleParameter ("ID", OracleType.Int32));
237 command.Parameters.Add( new OracleParameter ("VALUE1", OracleType.Float));
238 command.Parameters.Add( new OracleParameter ("VALUE2", OracleType.Double));
239 command.Parameters.Add( new OracleParameter ("VALUE3", OracleType.Number));
240 command.Parameters ["ID"].Value = id;
241 command.Parameters ["VALUE1"].Value = 2346.2342f;
242 command.Parameters ["VALUE2"].Value = 4567456.23412m;
243 command.Parameters ["VALUE3"].Value = new OracleNumber(4567456.23412m);
246 command.ExecuteNonQuery ();
247 } catch (OracleException e) {
249 Assert.Fail("Culture incompatibility error while inserting [" + id + ']');
255 // regression for bug #79284
256 protected void CultureSensitiveNumbersSelectTest (int id)
258 using (command = connection.CreateCommand ()) { // reusing command from SetUp causes parameter names mismatch
260 command.CommandText =
261 "select value1,value2,value3 from culture_test where id = " + id;
262 command.Parameters.Clear ();
264 using (OracleDataReader reader = command.ExecuteReader ()) {
265 if (reader.Read ()) {
266 Assert.AreEqual (2346.2342f,reader.GetFloat(0),
267 "Float value improperly stored [" + id + ']');
268 Assert.AreEqual (4567456.23412m, reader.GetDecimal (1),
269 "Decimal value improperly stored [" + id + ']');
270 Assert.AreEqual (4567456.23412m, reader.GetOracleNumber(2).Value,
271 "OracleNumber value improperly stored [" + id + ']');
273 Assert.Fail ("Expected records not found [" + id + ']');
276 } catch (FormatException) {
277 Assert.Fail("Culture incompatibility error while reading [" + id + ']');
282 // added support for OracleString, OracleNumber and OracleDateTime in OracleParameter
284 public void OracleTypesInValueTest ()
286 if (connection_string == null)
287 Assert.Ignore ("Please consult README.tests.");
291 string test_string = "koza";
292 DateTime test_dateTime = DateTime.MinValue;
293 using (command = connection.CreateCommand ()) { // reusing command from SetUp causes parameter names mismatch
294 // insert test values
295 command.CommandText =
296 "insert into oratypes_test (id,value1,value2)"
297 +" values (:idx,:txtx,:datex)";
298 command.Parameters.Add(
299 new OracleParameter("IDX", OracleType.Number))
300 .Direction = ParameterDirection.Input;
301 command.Parameters.Add(
302 new OracleParameter("TXTX", OracleType.VarChar))
303 .Direction = ParameterDirection.Input;
304 command.Parameters.Add(
305 new OracleParameter("DATEX", OracleType.DateTime))
306 .Direction = ParameterDirection.Input;
308 command.Parameters ["IDX"].Value = new OracleNumber(test_int);
309 command.Parameters ["TXTX"].Value = new OracleString(test_string);
310 command.Parameters ["DATEX"].Value = new OracleDateTime(test_dateTime);
312 command.ExecuteNonQuery ();
315 command.CommandText =
316 "select value1,value2 from oratypes_test where id = "
318 command.Parameters.Clear ();
319 using (OracleDataReader reader = command.ExecuteReader ()) {
320 if (reader.Read ()) {
321 Assert.AreEqual (test_string, reader.GetString (0), "OracleString mismatched");
322 Assert.AreEqual (test_dateTime, reader.GetDateTime(1), "OracleDateTime mismatched");
324 Assert.Fail ("Expected records not found.");
328 } catch (ArgumentException e) {
329 Assert.Fail("OracleType not handled: " + e.Message);
333 [Test] // verify that parameters are bound by name
334 public void ProcedureParametersByNameTest ()
336 if (connection_string == null)
337 Assert.Ignore ("Please consult README.tests.");
339 using (command = connection.CreateCommand ()) { // reusing command from SetUp causes parameter names mismatch
340 command.CommandText = "params_pos_test";
341 command.CommandType = CommandType.StoredProcedure;
343 command.Parameters.Add (new OracleParameter ("PARAM3", OracleType.Int32));
344 command.Parameters.Add (new OracleParameter ("PARAM1", OracleType.Int32));
345 command.Parameters.Add (new OracleParameter ("PARAM2", OracleType.Int32));
346 command.Parameters.Add (new OracleParameter ("RESULT", OracleType.Int32))
347 .Direction = ParameterDirection.Output;
349 command.Parameters ["PARAM1"].Value = 1;
350 command.Parameters ["PARAM2"].Value = 2;
351 command.Parameters ["PARAM3"].Value = 3;
353 command.ExecuteNonQuery ();
355 Assert.AreEqual (3, command.Parameters ["RESULT"].Value,
356 "Unexpected result value.");
360 private void ParamSize_SPCreation_ValueInsertion (OracleConnection conn)
363 "CREATE OR REPLACE PROCEDURE GetTextValue \n" +
365 "idParam IN Number(10),\n" +
366 "text OUT varchar2(64) \n" +
370 "SELECT oratest.text INTO text \n" +
372 " WHERE oratest.id = idParam; \n" +
375 string insertValue = "INSERT INTO oratest VALUES " +
376 "(424908, \"This is a test for 424908 parameter size bug\", NULL);";
378 using (command = conn.CreateCommand ()) {
379 command.CommandText = createSP;
380 command.CommandType = CommandType.Text;
381 command.ExecuteNonQuery ();
383 command.CommandText = insertValue;
384 command.ExecuteNonQuery ();
386 command.CommandText = "commit";
387 command.ExecuteNonQuery ();
392 [Category("NotWorking")]
393 public void ParamSize_424908_ValueError ()
395 //OracleConnection conn = new OracleConnection (connection_string);
398 ParamSize_SPCreation_ValueInsertion (connection);
400 using (command = connection.CreateCommand ()) {
402 OracleParameter id = new OracleParameter ();
403 id.ParameterName = "idParam";
404 id.OracleType = OracleType.Number;
405 id.Direction = ParameterDirection.Input;
407 command.Parameters.Add (id);
409 OracleParameter text = new OracleParameter ();
410 text.ParameterName = "text";
411 text.OracleType = OracleType.NVarChar;
412 text.Direction = ParameterDirection.Output;
413 text.Value = string.Empty;
415 command.Parameters.Add (text);
418 command.CommandType = CommandType.StoredProcedure;
419 command.CommandText = "GetTextValue";
420 command.ExecuteNonQuery ();
421 Assert.Fail ("Expected OracleException not occurred!");
422 } catch (OracleException ex) {
423 Assert.AreEqual ("6502", ex.Code, "Error code mismatch");
430 [Category("NotWorking")]
431 public void ParamSize_424908_ConstructorSizeSetTest ()
433 //OracleConnection conn = new OracleConnection (connection_string);
436 ParamSize_SPCreation_ValueInsertion (connection);
438 using (command = connection.CreateCommand ()) {
439 OracleParameter id = new OracleParameter ();
440 id.ParameterName = "idParam";
441 id.OracleType = OracleType.Number;
442 id.Direction = ParameterDirection.Input;
444 command.Parameters.Add (id);
446 OracleParameter text = new OracleParameter ("text", OracleType.NVarChar, 64);
447 text.Direction = ParameterDirection.Output;
448 text.Value = string.Empty;
450 command.Parameters.Add (text);
452 command.CommandType = CommandType.StoredProcedure;
453 command.CommandText = "GetTextValue";
454 command.ExecuteNonQuery ();
456 Assert.AreEqual ("This is a test for 424908 parameter size bug", text.Value, "OracleParameter value mismatch");
461 [Category("NotWorking")]
462 public void ParamSize_424908_SizeNotSetError ()
465 ParamSize_SPCreation_ValueInsertion (connection);
467 using (command = connection.CreateCommand ()) {
468 OracleParameter id = new OracleParameter ();
469 id.ParameterName = "idParam";
470 id.OracleType = OracleType.Number;
471 id.Direction = ParameterDirection.Input;
473 command.Parameters.Add (id);
475 OracleParameter text = new OracleParameter ();
476 text.ParameterName = "text";
477 text.OracleType = OracleType.NVarChar;
478 text.Direction = ParameterDirection.Output;
479 text.Value = DBNull.Value;
480 command.Parameters.Add (text);
483 command.CommandType = CommandType.StoredProcedure;
484 command.CommandText = "GetTextValue";
485 command.ExecuteNonQuery ();
486 Assert.Fail ("Expected System.Exception not occurred!");
487 } catch (Exception ex) {
488 Assert.AreEqual ("Size must be set.", ex.Message, "Exception mismatch");