1 // created on 30/11/2002 at 22:35
4 // Francisco Figueiredo Jr. <fxjrlists@yahoo.com>
6 // Copyright (C) 2002 The Npgsql Development Team
7 // npgsql-general@gborg.postgresql.org
8 // http://gborg.postgresql.org/project/npgsql/projdisplay.php
10 // This library is free software; you can redistribute it and/or
11 // modify it under the terms of the GNU Lesser General Public
12 // License as published by the Free Software Foundation; either
13 // version 2.1 of the License, or (at your option) any later version.
15 // This library is distributed in the hope that it will be useful,
16 // but WITHOUT ANY WARRANTY; without even the implied warranty of
17 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
18 // Lesser General Public License for more details.
20 // You should have received a copy of the GNU Lesser General Public
21 // License along with this library; if not, write to the Free Software
22 // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
26 using NUnit.Framework;
29 using System.Globalization;
36 public class CommandTests
38 private NpgsqlConnection _conn = null;
39 private String _connString = "Server=localhost;User ID=npgsql_tests;Password=npgsql_tests;Database=npgsql_tests;SSL=yes;maxpoolsize=2;";
42 protected void SetUp()
44 //NpgsqlEventLog.Level = LogLevel.None;
45 //NpgsqlEventLog.Level = LogLevel.Debug;
46 //NpgsqlEventLog.LogName = "NpgsqlTests.LogFile";
47 _conn = new NpgsqlConnection(_connString);
51 protected void TearDown()
53 if (_conn.State != ConnectionState.Closed)
59 public void ParametersGetName()
61 NpgsqlCommand command = new NpgsqlCommand();
64 command.Parameters.Add(new NpgsqlParameter(":Parameter1", DbType.Boolean));
65 command.Parameters.Add(new NpgsqlParameter(":Parameter2", DbType.Int32));
66 command.Parameters.Add(new NpgsqlParameter(":Parameter3", DbType.DateTime));
71 Assertion.AssertEquals("ParametersGetName", ":Parameter1", command.Parameters[":Parameter1"].ParameterName);
72 Assertion.AssertEquals("ParametersGetName", ":Parameter2", command.Parameters[":Parameter2"].ParameterName);
73 Assertion.AssertEquals("ParametersGetName", ":Parameter3", command.Parameters[":Parameter3"].ParameterName);
76 Assertion.AssertEquals("ParametersGetName", ":Parameter1", command.Parameters[0].ParameterName);
77 Assertion.AssertEquals("ParametersGetName", ":Parameter2", command.Parameters[1].ParameterName);
78 Assertion.AssertEquals("ParametersGetName", ":Parameter3", command.Parameters[2].ParameterName);
85 public void EmptyQuery()
89 NpgsqlCommand command = new NpgsqlCommand(";", _conn);
90 command.ExecuteNonQuery();
94 [ExpectedException(typeof(ArgumentNullException))]
95 public void NoNameParameterAdd()
97 NpgsqlCommand command = new NpgsqlCommand();
99 command.Parameters.Add(new NpgsqlParameter());
104 public void FunctionCallFromSelect()
108 NpgsqlCommand command = new NpgsqlCommand("select * from funcB()", _conn);
110 NpgsqlDataReader reader = command.ExecuteReader();
112 Assertion.AssertNotNull(reader);
118 public void ExecuteScalar()
122 NpgsqlCommand command = new NpgsqlCommand("select count(*) from tablea", _conn);
124 Object result = command.ExecuteScalar();
126 Assertion.AssertEquals(5, result);
132 public void FunctionCallReturnSingleValue()
136 NpgsqlCommand command = new NpgsqlCommand("funcC()", _conn);
137 command.CommandType = CommandType.StoredProcedure;
139 Object result = command.ExecuteScalar();
141 Assertion.AssertEquals(5, result);
148 public void FunctionCallReturnSingleValueWithPrepare()
152 NpgsqlCommand command = new NpgsqlCommand("funcC()", _conn);
153 command.CommandType = CommandType.StoredProcedure;
156 Object result = command.ExecuteScalar();
158 Assertion.AssertEquals(5, result);
164 public void FunctionCallWithParametersReturnSingleValue()
168 NpgsqlCommand command = new NpgsqlCommand("funcC(:a)", _conn);
169 command.CommandType = CommandType.StoredProcedure;
171 command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32));
173 command.Parameters[0].Value = 4;
175 Int64 result = (Int64) command.ExecuteScalar();
177 Assertion.AssertEquals(1, result);
184 public void FunctionCallWithParametersPrepareReturnSingleValue()
188 NpgsqlCommand command = new NpgsqlCommand("funcC(:a)", _conn);
189 command.CommandType = CommandType.StoredProcedure;
192 command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32));
194 Assertion.AssertEquals(1, command.Parameters.Count);
198 command.Parameters[0].Value = 4;
200 Int64 result = (Int64) command.ExecuteScalar();
202 Assertion.AssertEquals(1, result);
208 public void FunctionCallReturnResultSet()
212 NpgsqlCommand command = new NpgsqlCommand("funcB()", _conn);
213 command.CommandType = CommandType.StoredProcedure;
215 NpgsqlDataReader dr = command.ExecuteReader();
224 public void CursorStatement()
231 NpgsqlTransaction t = _conn.BeginTransaction();
233 NpgsqlCommand command = new NpgsqlCommand("declare te cursor for select * from tablea;", _conn);
235 command.ExecuteNonQuery();
237 command.CommandText = "fetch forward 3 in te;";
239 NpgsqlDataReader dr = command.ExecuteReader();
247 Assertion.AssertEquals(3, i);
252 command.CommandText = "fetch backward 1 in te;";
254 NpgsqlDataReader dr2 = command.ExecuteReader();
261 Assertion.AssertEquals(1, i);
263 command.CommandText = "close te;";
265 command.ExecuteNonQuery();
274 public void PreparedStatementNoParameters()
278 NpgsqlCommand command = new NpgsqlCommand("select * from tablea;", _conn);
284 NpgsqlDataReader dr = command.ExecuteReader();
290 public void PreparedStatementWithParameters()
294 NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_int4 = :a and field_int8 = :b;", _conn);
296 command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32));
297 command.Parameters.Add(new NpgsqlParameter("b", DbType.Int64));
299 Assertion.AssertEquals(2, command.Parameters.Count);
301 Assertion.AssertEquals(DbType.Int32, command.Parameters[0].DbType);
305 command.Parameters[0].Value = 3;
306 command.Parameters[1].Value = 5;
308 NpgsqlDataReader dr = command.ExecuteReader();
316 [ExpectedException(typeof(InvalidOperationException))]
317 public void ListenNotifySupport()
322 NpgsqlCommand command = new NpgsqlCommand("listen notifytest;", _conn);
323 command.ExecuteNonQuery();
325 _conn.Notification += new NotificationEventHandler(NotificationSupportHelper);
328 command = new NpgsqlCommand("notify notifytest;", _conn);
329 command.ExecuteNonQuery();
335 private void NotificationSupportHelper(Object sender, NpgsqlNotificationEventArgs args)
337 throw new InvalidOperationException();
341 public void DateTimeSupport()
346 NpgsqlCommand command = new NpgsqlCommand("select field_timestamp from tableb where field_serial = 2;", _conn);
348 DateTime d = (DateTime)command.ExecuteScalar();
351 Assertion.AssertEquals("2002-02-02 09:00:23Z", d.ToString("u"));
353 DateTimeFormatInfo culture = new DateTimeFormatInfo();
354 culture.TimeSeparator = ":";
355 DateTime dt = System.DateTime.Parse("2004-06-04 09:48:00", culture);
357 command.CommandText = "insert into tableb(field_timestamp) values (:a);delete from tableb where field_serial > 4;";
358 command.Parameters.Add(new NpgsqlParameter("a", DbType.DateTime));
359 command.Parameters[0].Value = dt;
361 command.ExecuteScalar();
366 public void DateSupport()
370 NpgsqlCommand command = new NpgsqlCommand("select field_date from tablec where field_serial = 1;", _conn);
372 DateTime d = (DateTime)command.ExecuteScalar();
375 Assertion.AssertEquals("2002-03-04", d.ToString("yyyy-MM-dd"));
380 public void TimeSupport()
384 NpgsqlCommand command = new NpgsqlCommand("select field_time from tablec where field_serial = 2;", _conn);
386 DateTime d = (DateTime)command.ExecuteScalar();
389 Assertion.AssertEquals("10:03:45.345", d.ToString("HH:mm:ss.fff"));
394 public void NumericSupport()
399 NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_numeric) values (:a)", _conn);
400 command.Parameters.Add(new NpgsqlParameter("a", DbType.Decimal));
402 command.Parameters[0].Value = 7.4M;
404 Int32 rowsAdded = command.ExecuteNonQuery();
406 Assertion.AssertEquals(1, rowsAdded);
408 command.CommandText = "select * from tableb where field_numeric = :a";
411 NpgsqlDataReader dr = command.ExecuteReader();
414 Decimal result = dr.GetDecimal(3);
417 command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb) and field_serial != 3;";
418 command.Parameters.Clear();
419 command.ExecuteNonQuery();
422 Assertion.AssertEquals(7.4M, result);
430 public void InsertSingleValue()
435 NpgsqlCommand command = new NpgsqlCommand("insert into tabled(field_float4) values (:a)", _conn);
436 command.Parameters.Add(new NpgsqlParameter(":a", DbType.Single));
438 command.Parameters[0].Value = 7.4F;
440 Int32 rowsAdded = command.ExecuteNonQuery();
442 Assertion.AssertEquals(1, rowsAdded);
444 command.CommandText = "select * from tabled where field_float4 = :a";
447 NpgsqlDataReader dr = command.ExecuteReader();
450 Single result = dr.GetFloat(1);
453 command.CommandText = "delete from tabled where field_serial > 2;";
454 command.Parameters.Clear();
455 command.ExecuteNonQuery();
458 Assertion.AssertEquals(7.4F, result);
463 public void InsertDoubleValue()
468 NpgsqlCommand command = new NpgsqlCommand("insert into tabled(field_float8) values (:a)", _conn);
469 command.Parameters.Add(new NpgsqlParameter(":a", DbType.Double));
471 command.Parameters[0].Value = 7.4D;
473 Int32 rowsAdded = command.ExecuteNonQuery();
475 Assertion.AssertEquals(1, rowsAdded);
477 command.CommandText = "select * from tabled where field_float8 = :a";
480 NpgsqlDataReader dr = command.ExecuteReader();
483 Double result = dr.GetDouble(2);
486 command.CommandText = "delete from tabled where field_serial > 2;";
487 command.Parameters.Clear();
488 //command.ExecuteNonQuery();
491 Assertion.AssertEquals(7.4D, result);
496 public void NegativeNumericSupport()
501 NpgsqlCommand command = new NpgsqlCommand("select * from tableb where field_serial = 4", _conn);
504 NpgsqlDataReader dr = command.ExecuteReader();
507 Decimal result = dr.GetDecimal(3);
509 Assertion.AssertEquals(-4.3M, result);
514 public void PrecisionScaleNumericSupport()
519 NpgsqlCommand command = new NpgsqlCommand("select * from tableb where field_serial = 4", _conn);
522 NpgsqlDataReader dr = command.ExecuteReader();
525 Decimal result = dr.GetDecimal(3);
527 Assertion.AssertEquals(-4.3M, (Decimal)result);
528 //Assertion.AssertEquals(11, result.Precision);
529 //Assertion.AssertEquals(7, result.Scale);
534 public void InsertNullString()
538 NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_text) values (:a)", _conn);
540 command.Parameters.Add(new NpgsqlParameter("a", DbType.String));
542 command.Parameters[0].Value = DBNull.Value;
544 Int32 rowsAdded = command.ExecuteNonQuery();
546 Assertion.AssertEquals(1, rowsAdded);
548 command.CommandText = "select count(*) from tablea where field_text is null";
549 command.Parameters.Clear();
551 Int64 result = (Int64)command.ExecuteScalar();
553 command.CommandText = "delete from tablea where field_serial = (select max(field_serial) from tablea) and field_serial != 4;";
554 command.ExecuteNonQuery();
556 Assertion.AssertEquals(4, result);
563 public void InsertNullDateTime()
567 NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_timestamp) values (:a)", _conn);
569 command.Parameters.Add(new NpgsqlParameter("a", DbType.DateTime));
571 command.Parameters[0].Value = DBNull.Value;
573 Int32 rowsAdded = command.ExecuteNonQuery();
575 Assertion.AssertEquals(1, rowsAdded);
577 command.CommandText = "select count(*) from tableb where field_timestamp is null";
578 command.Parameters.Clear();
580 Object result = command.ExecuteScalar();
582 command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb) and field_serial != 3;";
583 command.ExecuteNonQuery();
585 Assertion.AssertEquals(4, result);
593 public void InsertNullInt16()
598 NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_int2) values (:a)", _conn);
600 command.Parameters.Add(new NpgsqlParameter("a", DbType.Int16));
602 command.Parameters[0].Value = DBNull.Value;
604 Int32 rowsAdded = command.ExecuteNonQuery();
606 Assertion.AssertEquals(1, rowsAdded);
608 command.CommandText = "select count(*) from tableb where field_int2 is null";
609 command.Parameters.Clear();
611 Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
613 command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb);";
614 command.ExecuteNonQuery();
616 Assertion.AssertEquals(4, result);
623 public void InsertNullInt32()
628 NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_int4) values (:a)", _conn);
630 command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32));
632 command.Parameters[0].Value = DBNull.Value;
634 Int32 rowsAdded = command.ExecuteNonQuery();
636 Assertion.AssertEquals(1, rowsAdded);
638 command.CommandText = "select count(*) from tablea where field_int4 is null";
639 command.Parameters.Clear();
641 Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
643 command.CommandText = "delete from tablea where field_serial = (select max(field_serial) from tablea);";
644 command.ExecuteNonQuery();
646 Assertion.AssertEquals(5, result);
652 public void InsertNullNumeric()
657 NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_numeric) values (:a)", _conn);
659 command.Parameters.Add(new NpgsqlParameter("a", DbType.Decimal));
661 command.Parameters[0].Value = DBNull.Value;
663 Int32 rowsAdded = command.ExecuteNonQuery();
665 Assertion.AssertEquals(1, rowsAdded);
667 command.CommandText = "select count(*) from tableb where field_numeric is null";
668 command.Parameters.Clear();
670 Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
672 command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb);";
673 command.ExecuteNonQuery();
675 Assertion.AssertEquals(3, result);
680 public void InsertNullBoolean()
685 NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_bool) values (:a)", _conn);
687 command.Parameters.Add(new NpgsqlParameter("a", DbType.Boolean));
689 command.Parameters[0].Value = DBNull.Value;
691 Int32 rowsAdded = command.ExecuteNonQuery();
693 Assertion.AssertEquals(1, rowsAdded);
695 command.CommandText = "select count(*) from tablea where field_bool is null";
696 command.Parameters.Clear();
698 Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
700 command.CommandText = "delete from tablea where field_serial = (select max(field_serial) from tablea);";
701 command.ExecuteNonQuery();
703 Assertion.AssertEquals(5, result);
708 public void AnsiStringSupport()
712 NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_text) values (:a)", _conn);
714 command.Parameters.Add(new NpgsqlParameter("a", DbType.AnsiString));
716 command.Parameters[0].Value = "TesteAnsiString";
718 Int32 rowsAdded = command.ExecuteNonQuery();
720 Assertion.AssertEquals(1, rowsAdded);
722 command.CommandText = String.Format("select count(*) from tablea where field_text = '{0}'", command.Parameters[0].Value);
723 command.Parameters.Clear();
725 Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
727 command.CommandText = "delete from tablea where field_serial = (select max(field_serial) from tablea);";
728 command.ExecuteNonQuery();
730 Assertion.AssertEquals(1, result);
736 public void MultipleQueriesFirstResultsetEmpty()
740 NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_text) values ('a'); select count(*) from tablea;", _conn);
742 Object result = command.ExecuteScalar();
745 command.CommandText = "delete from tablea where field_serial > 5";
746 command.ExecuteNonQuery();
748 command.CommandText = "select * from tablea where field_serial = 0";
749 command.ExecuteScalar();
752 Assertion.AssertEquals(6, result);
758 [ExpectedException(typeof(NpgsqlException))]
759 public void ConnectionStringWithInvalidParameters()
761 NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=npgsql_tests;Password=j");
763 NpgsqlCommand command = new NpgsqlCommand("select * from tablea", conn);
765 command.Connection.Open();
766 command.ExecuteReader();
767 command.Connection.Close();
773 [ExpectedException(typeof(NpgsqlException))]
774 public void InvalidConnectionString()
776 NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=npgsql_tests");
778 NpgsqlCommand command = new NpgsqlCommand("select * from tablea", conn);
780 command.Connection.Open();
781 command.ExecuteReader();
782 command.Connection.Close();
789 public void AmbiguousFunctionParameterType()
791 NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=npgsql_tests;Password=npgsql_tests");
794 NpgsqlCommand command = new NpgsqlCommand("ambiguousParameterType(:a, :b, :c, :d, :e, :f)", conn);
795 command.CommandType = CommandType.StoredProcedure;
796 NpgsqlParameter p = new NpgsqlParameter("a", DbType.Int16);
798 command.Parameters.Add(p);
799 p = new NpgsqlParameter("b", DbType.Int32);
801 command.Parameters.Add(p);
802 p = new NpgsqlParameter("c", DbType.Int64);
804 command.Parameters.Add(p);
805 p = new NpgsqlParameter("d", DbType.String);
807 command.Parameters.Add(p);
808 p = new NpgsqlParameter("e", DbType.String);
810 command.Parameters.Add(p);
811 p = new NpgsqlParameter("f", DbType.String);
813 command.Parameters.Add(p);
816 command.Connection.Open();
818 command.ExecuteScalar();
819 command.Connection.Close();