2 // SqlParameterTest.cs - NUnit Test Cases for testing the
5 // Senganal T (tsenganal@novell.com)
6 // Amit Biswas (amit@amitbiswas.com)
7 // Veerapuram Varadhan (vvaradhan@novell.com)
9 // Copyright (c) 2004 Novell Inc., and the individuals listed
10 // on the ChangeLog entries.
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:
20 // The above copyright notice and this permission notice shall be
21 // included in all copies or substantial portions of the Software.
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.
34 using System.Data.SqlClient;
36 using NUnit.Framework;
38 namespace MonoTests.System.Data.Connected.SqlClient
41 [Category ("sqlserver")]
42 public class SqlParameterTest
52 conn = ConnectionManager.Instance.Sql.Connection;
53 engine = ConnectionManager.Instance.Sql.EngineConfig;
57 public void TearDown ()
63 ConnectionManager.Instance.Close ();
67 public void ParameterSizeTest ()
69 if (ClientVersion == 7)
70 Assert.Ignore ("Hangs on SQL Server 7.0");
72 string longstring = new String('x', 20480);
74 cmd = new SqlCommand ("create table #text1 (ID int not null, Val1 ntext)", conn);
75 cmd.ExecuteNonQuery ();
76 cmd.CommandText = "INSERT INTO #text1(ID,Val1) VALUES (@ID,@Val1)";
77 prm = new SqlParameter ();
78 prm.ParameterName = "@ID";
80 cmd.Parameters.Add (prm);
82 prm = new SqlParameter ();
83 prm.ParameterName = "@Val1";
84 prm.Value = longstring;
85 prm.SqlDbType = SqlDbType.NText; // Comment and enjoy the truncation
86 cmd.Parameters.Add (prm);
87 cmd.ExecuteNonQuery ();
88 cmd = new SqlCommand ("select datalength(Val1) from #text1", conn);
89 Assert.AreEqual (20480 * 2, cmd.ExecuteScalar (), "#1");
91 cmd.CommandText = "INSERT INTO #text1(ID,Val1) VALUES (@ID,@Val1)";
92 prm = new SqlParameter ();
93 prm.ParameterName = "@ID";
95 cmd.Parameters.Add (prm);
97 prm = new SqlParameter ();
98 prm.ParameterName = "@Val1";
99 prm.Value = longstring;
100 //prm.SqlDbType = SqlDbType.NText;
101 cmd.Parameters.Add (prm);
102 cmd.ExecuteNonQuery ();
103 cmd = new SqlCommand ("select datalength(Val1) from #text1", conn);
104 Assert.AreEqual (20480 * 2, cmd.ExecuteScalar (), "#2");
106 cmd.CommandText = "INSERT INTO #text1(ID,Val1) VALUES (@ID,@Val1)";
107 prm = new SqlParameter ();
108 prm.ParameterName = "@ID";
110 cmd.Parameters.Add (prm);
112 prm = new SqlParameter ();
113 prm.ParameterName = "@Val1";
114 prm.Value = longstring;
115 prm.SqlDbType = SqlDbType.VarChar;
116 cmd.Parameters.Add (prm);
117 cmd.ExecuteNonQuery ();
118 cmd = new SqlCommand ("select datalength(Val1) from #text1", conn);
119 Assert.AreEqual (20480 * 2, cmd.ExecuteScalar (), "#3");
120 cmd = new SqlCommand ("drop table #text1", conn);
121 cmd.ExecuteNonQuery ();
125 [Test] // bug #382635
126 [Category("NotWorking")]
127 public void ParameterSize_compatibility_Test ()
129 string longstring = "abcdefghijklmnopqrstuvwxyz";
131 cmd = new SqlCommand ("create table #bug382635 (description varchar(20))", conn);
132 cmd.ExecuteNonQuery ();
135 "CREATE PROCEDURE #sp_bug382635 (@Desc varchar(20)) "
136 + "AS " + Environment.NewLine
137 + "BEGIN" + Environment.NewLine
138 + "UPDATE #bug382635 SET description = @Desc" + Environment.NewLine
140 cmd.CommandType = CommandType.Text;
141 cmd.ExecuteNonQuery ();
143 cmd.CommandText = "INSERT INTO #bug382635 " +
144 "(description) VALUES ('Verifies bug #382635')";
145 cmd.ExecuteNonQuery ();
147 cmd.CommandText = "#sp_bug382635";
148 cmd.CommandType = CommandType.StoredProcedure;
150 SqlParameter p1 = new SqlParameter ("@Desc", SqlDbType.NVarChar, 15);
151 p1.Value = longstring;
152 Assert.AreEqual (longstring, p1.Value);
153 cmd.Parameters.Add (p1);
154 cmd.ExecuteNonQuery ();
156 // Test for truncation
157 SqlCommand selectCmd = new SqlCommand ("SELECT DATALENGTH(description), description from #bug382635", conn);
159 rdr = selectCmd.ExecuteReader ();
160 Assert.IsTrue (rdr.Read (), "#A1");
161 Assert.AreEqual (15, rdr.GetValue (0), "#A2");
162 Assert.AreEqual (longstring.Substring (0, 15), rdr.GetValue (1), "#A3");
163 Assert.AreEqual (longstring, p1.Value, "#A4");
166 // Test to ensure truncation is not done in the Value getter/setter
168 p1.Value = longstring.Substring (0, 22);
170 cmd.ExecuteNonQuery ();
172 rdr = selectCmd.ExecuteReader ();
173 Assert.IsTrue (rdr.Read (), "#B1");
174 Assert.AreEqual (14, rdr.GetValue (0), "#B2");
175 Assert.AreEqual (longstring.Substring (0, 14), rdr.GetValue (1), "#B3");
176 Assert.AreEqual (longstring.Substring (0, 22), p1.Value, "#B4");
179 // Size exceeds size of value
181 cmd.ExecuteNonQuery ();
183 rdr = selectCmd.ExecuteReader ();
184 Assert.IsTrue (rdr.Read (), "#C1");
185 Assert.AreEqual (14, rdr.GetValue (0), "#C2");
186 Assert.AreEqual (longstring.Substring (0, 14), rdr.GetValue (1), "#C3");
191 public void ConversionToSqlTypeInvalid ()
193 string insert_data = "insert into datetime_family (id, type_datetime) values (6000, @type_datetime)";
194 string delete_data = "delete from datetime_family where id = 6000";
196 object [] values = new object [] {
204 for (int i = 0; i < values.Length; i++) {
205 object value = values [i];
207 cmd = conn.CreateCommand ();
208 cmd.CommandText = insert_data;
209 SqlParameter param = cmd.Parameters.Add ("@type_datetime", SqlDbType.DateTime);
214 cmd.ExecuteNonQuery ();
215 Assert.Fail ("#1:" + i);
216 } catch (InvalidCastException) {
219 } catch (FormatException) {
220 if (!(value is string))
225 DBHelper.ExecuteNonQuery (conn, delete_data);
229 [Test] // bug #382589
230 public void DecimalMaxAsParamValueTest ()
232 if (ClientVersion == 7)
233 Assert.Ignore ("Maximum precision is 28.");
235 string create_sp = "CREATE PROCEDURE #sp_bug382539 (@decmax decimal(29,0) OUT)"
236 + "AS " + Environment.NewLine
237 + "BEGIN" + Environment.NewLine
238 + "SET @decmax = 102.34" + Environment.NewLine
241 cmd = new SqlCommand (create_sp, conn);
242 cmd.ExecuteNonQuery ();
244 cmd.CommandText = "[#sp_bug382539]";
245 cmd.CommandType = CommandType.StoredProcedure;
246 SqlParameter pValue = new SqlParameter("@decmax", Decimal.MaxValue);
247 pValue.Direction = ParameterDirection.InputOutput;
248 cmd.Parameters.Add(pValue);
250 Assert.AreEqual (Decimal.MaxValue, pValue.Value, "Parameter initialization value mismatch");
251 cmd.ExecuteNonQuery();
253 Assert.AreEqual (102m, pValue.Value, "Parameter value mismatch");
256 [Test] // bug #382589
257 public void DecimalMinAsParamValueTest ()
259 if (ClientVersion == 7)
260 Assert.Ignore ("Maximum precision is 28.");
262 string create_sp = "CREATE PROCEDURE #sp_bug382539 (@decmax decimal(29,0) OUT)"
263 + "AS " + Environment.NewLine
264 + "BEGIN" + Environment.NewLine
265 + "SET @decmax = 102.34" + Environment.NewLine
268 cmd = new SqlCommand (create_sp, conn);
269 cmd.ExecuteNonQuery ();
271 cmd.CommandText = "[#sp_bug382539]";
272 cmd.CommandType = CommandType.StoredProcedure;
273 SqlParameter pValue = new SqlParameter("@decmax", Decimal.MinValue);
274 pValue.Direction = ParameterDirection.InputOutput;
275 cmd.Parameters.Add(pValue);
277 Assert.AreEqual (Decimal.MinValue, pValue.Value, "Parameter initialization value mismatch");
278 cmd.ExecuteNonQuery();
280 Assert.AreEqual (102m, pValue.Value, "Parameter value mismatch");
283 [Test] // bug #382589
284 public void DecimalMaxAsParamValueExceptionTest ()
286 if (ClientVersion == 7)
287 Assert.Ignore ("Maximum precision is 28.");
289 string create_sp = "CREATE PROCEDURE #sp_bug382539 (@decmax decimal(29,10) OUT)"
290 + "AS " + Environment.NewLine
291 + "BEGIN" + Environment.NewLine
292 + "SET @decmax = 102.36" + Environment.NewLine
295 cmd = new SqlCommand (create_sp, conn);
296 cmd.ExecuteNonQuery ();
298 cmd.CommandText = "[#sp_bug382539]";
299 cmd.CommandType = CommandType.StoredProcedure;
300 SqlParameter pValue = new SqlParameter("@decmax", Decimal.MaxValue);
301 pValue.Direction = ParameterDirection.InputOutput;
302 cmd.Parameters.Add(pValue);
305 cmd.ExecuteNonQuery ();
307 } catch (SqlException ex) {
308 // Error converting data type numeric to decimal
309 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
310 Assert.AreEqual ((byte) 16, ex.Class, "#3");
311 Assert.IsNull (ex.InnerException, "#4");
312 Assert.IsNotNull (ex.Message, "#5");
313 Assert.AreEqual (8114, ex.Number, "#6");
314 Assert.AreEqual ((byte) 5, ex.State, "#7");
318 [Test] // bug# 382589
319 public void DecimalMinAsParamValueExceptionTest ()
321 if (ClientVersion == 7)
322 Assert.Ignore ("Maximum precision is 28.");
324 string create_sp = "CREATE PROCEDURE #sp_bug382539 (@decmax decimal(29,10) OUT)"
325 + "AS " + Environment.NewLine
326 + "BEGIN" + Environment.NewLine
327 + "SET @decmax = 102.36" + Environment.NewLine
330 cmd = new SqlCommand (create_sp, conn);
331 cmd.ExecuteNonQuery ();
333 cmd.CommandText = "[#sp_bug382539]";
334 cmd.CommandType = CommandType.StoredProcedure;
335 SqlParameter pValue = new SqlParameter("@decmax", Decimal.MinValue);
336 pValue.Direction = ParameterDirection.InputOutput;
337 cmd.Parameters.Add(pValue);
339 cmd.ExecuteNonQuery ();
341 } catch (SqlException ex) {
342 // Error converting data type numeric to decimal
343 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
344 Assert.AreEqual ((byte) 16, ex.Class, "#3");
345 Assert.IsNull (ex.InnerException, "#4");
346 Assert.IsNotNull (ex.Message, "#5");
347 Assert.AreEqual (8114, ex.Number, "#6");
348 Assert.AreEqual ((byte) 5, ex.State, "#7");
352 [Test] // bug #526794
353 public void ZeroLengthString ()
355 cmd = new SqlCommand ("create table #bug526794 (name varchar(20) NULL)", conn);
356 cmd.ExecuteNonQuery ();
360 param = new SqlParameter ("@name", SqlDbType.VarChar);
361 param.Value = string.Empty;
363 cmd = new SqlCommand ("insert into #bug526794 values (@name)", conn);
364 cmd.Parameters.Add (param);
365 cmd.ExecuteNonQuery ();
367 cmd = new SqlCommand ("select * from #bug526794", conn);
368 rdr = cmd.ExecuteReader ();
369 Assert.IsTrue (rdr.Read (), "#A1");
370 Assert.AreEqual (string.Empty, rdr.GetValue (0), "#A2");
373 param = new SqlParameter ("@name", SqlDbType.Int);
374 param.Value = string.Empty;
376 cmd = new SqlCommand ("insert into #bug526794 values (@name)", conn);
377 cmd.Parameters.Add (param);
380 cmd.ExecuteNonQuery ();
382 } catch (FormatException ex) {
383 // Failed to convert parameter value from a String to a Int32
384 Assert.AreEqual (typeof (FormatException), ex.GetType (), "#B2");
385 Assert.IsNotNull (ex.Message, "#B3");
386 Assert.IsTrue (ex.Message.IndexOf (typeof (string).Name) != -1, "#B4");
387 Assert.IsTrue (ex.Message.IndexOf (typeof (int).Name) != -1, "#B5");
389 // Input string was not in a correct format
390 Exception inner = ex.InnerException;
391 Assert.IsNotNull (inner, "#B6");
392 Assert.AreEqual (typeof (FormatException), inner.GetType (), "#B7");
393 Assert.IsNull (inner.InnerException, "#B8");
394 Assert.IsNotNull (inner.Message, "#B9");
398 [Test] // bug #595918
399 public void DecimalDefaultScaleTest ()
401 string create_tbl = "CREATE TABLE #decimalScaleCheck (decsclcheck DECIMAL (19, 5) null)";
402 string create_sp = "CREATE PROCEDURE #sp_bug595918(@decsclcheck decimal(19,5) OUT)"
403 + "AS " + Environment.NewLine
404 + "BEGIN" + Environment.NewLine
405 + "INSERT INTO #decimalScaleCheck values (@decsclcheck)" + Environment.NewLine
406 + "SELECT @decsclcheck=decsclcheck from #decimalScaleCheck" + Environment.NewLine
409 cmd = new SqlCommand (create_tbl, conn);
410 cmd.ExecuteNonQuery ();
412 cmd.CommandText = create_sp;
413 cmd.ExecuteNonQuery ();
415 cmd.CommandText = "[#sp_bug595918]";
416 cmd.CommandType = CommandType.StoredProcedure;
417 SqlParameter pValue = new SqlParameter("@decsclcheck", SqlDbType.Decimal);
418 pValue.Value = 128.425;
419 pValue.Precision = 19;
421 pValue.Direction = ParameterDirection.InputOutput;
422 cmd.Parameters.Add(pValue);
423 cmd.ExecuteNonQuery();
425 Assert.AreEqual (128.425, pValue.Value, "Stored decimal value is incorrect - DS - Bug#595918");
428 [Test] // bug #595918
429 public void DecimalGreaterScaleTest ()
431 string create_tbl = "CREATE TABLE #decimalScaleCheck (decsclcheck DECIMAL (19, 5) null)";
432 string create_sp = "CREATE PROCEDURE #sp_bug595918(@decsclcheck decimal(19,5) OUT)"
433 + "AS " + Environment.NewLine
434 + "BEGIN" + Environment.NewLine
435 + "INSERT INTO #decimalScaleCheck values (@decsclcheck)" + Environment.NewLine
436 + "SELECT @decsclcheck=decsclcheck from #decimalScaleCheck" + Environment.NewLine
439 cmd = new SqlCommand (create_tbl, conn);
440 cmd.ExecuteNonQuery ();
442 cmd.CommandText = create_sp;
443 cmd.ExecuteNonQuery ();
445 cmd.CommandText = "[#sp_bug595918]";
446 cmd.CommandType = CommandType.StoredProcedure;
447 SqlParameter pValue = new SqlParameter("@decsclcheck", SqlDbType.Decimal);
448 pValue.Value = 128.425;
449 pValue.Precision = 19;
451 pValue.Direction = ParameterDirection.InputOutput;
452 cmd.Parameters.Add(pValue);
453 cmd.ExecuteNonQuery();
455 Assert.AreEqual (128.42500, pValue.Value, "Stored decimal value is incorrect - GS - Bug#595918");
458 [Test] // bug #595918
459 public void DecimalLesserScaleTest ()
461 string create_tbl = "CREATE TABLE #decimalScaleCheck (decsclcheck DECIMAL (19, 5) null)";
462 string create_sp = "CREATE PROCEDURE #sp_bug595918(@decsclcheck decimal(19,5) OUT)"
463 + "AS " + Environment.NewLine
464 + "BEGIN" + Environment.NewLine
465 + "INSERT INTO #decimalScaleCheck values (@decsclcheck)" + Environment.NewLine
466 + "SELECT @decsclcheck=decsclcheck from #decimalScaleCheck" + Environment.NewLine
469 cmd = new SqlCommand (create_tbl, conn);
470 cmd.ExecuteNonQuery ();
472 cmd.CommandText = create_sp;
473 cmd.ExecuteNonQuery ();
475 cmd.CommandText = "[#sp_bug595918]";
476 cmd.CommandType = CommandType.StoredProcedure;
477 SqlParameter pValue = new SqlParameter("@decsclcheck", SqlDbType.Decimal);
478 pValue.Value = 128.425;
479 pValue.Precision = 19;
481 pValue.Direction = ParameterDirection.InputOutput;
482 cmd.Parameters.Add(pValue);
483 cmd.ExecuteNonQuery();
485 Assert.AreEqual (128.42, pValue.Value, "Stored decimal value is incorrect - LS - Bug#595918");
490 return (engine.ClientVersion);