class/Npgsql/Test/CommandTests.cs

   1 // created on 30/11/2002 at 22:35
   2 //
   3 // Author:
   4 //      Francisco Figueiredo Jr. <fxjrlists@yahoo.com>
   5 //
   6 //      Copyright (C) 2002 The Npgsql Development Team
   7 //      npgsql-general@gborg.postgresql.org
   8 //      http://gborg.postgresql.org/project/npgsql/projdisplay.php
   9 //
  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.
  14 //
  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.
  19 //
  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
  23
  24 using System;
  25 using Npgsql;
  26 using NUnit.Framework;
  27 using NUnit.Core;
  28 using System.Data;
  29 using System.Globalization;
  30 using NpgsqlTypes;
  31
  32
  33 namespace NpgsqlTests
  34 {
  35         [TestFixture]
  36         public class CommandTests
  37         {
  38                 NpgsqlConnection _conn = null;
  39
  40                 [SetUp]
  41                 protected void SetUp()
  42                 {
  43                         //NpgsqlEventLog.Level = LogLevel.None;
  44                         //NpgsqlEventLog.Level = LogLevel.Debug;
  45                         //NpgsqlEventLog.LogName = "NpgsqlTests.LogFile";
  46                         _conn = new NpgsqlConnection (TestConfiguration.NpgsqlConnectionString);
  47                 }
  48
  49                 [TearDown]
  50                 protected void TearDown()
  51                 {
  52                         if (_conn != null && _conn.State != ConnectionState.Closed)
  53                                 _conn.Close();
  54                 }
  55
  56
  57                 [Test]
  58                 public void ParametersGetName()
  59                 {
  60                         NpgsqlCommand command = new NpgsqlCommand();
  61
  62                         // Add parameters.
  63                         command.Parameters.Add(new NpgsqlParameter(":Parameter1", DbType.Boolean));
  64                         command.Parameters.Add(new NpgsqlParameter(":Parameter2", DbType.Int32));
  65                         command.Parameters.Add(new NpgsqlParameter(":Parameter3", DbType.DateTime));
  66
  67
  68                         // Get by indexers.
  69
  70                         Assert.AreEqual(":Parameter1", command.Parameters[":Parameter1"].ParameterName);
  71                         Assert.AreEqual(":Parameter2", command.Parameters[":Parameter2"].ParameterName);
  72                         Assert.AreEqual(":Parameter3", command.Parameters[":Parameter3"].ParameterName);
  73
  74
  75                         Assert.AreEqual(":Parameter1", command.Parameters[0].ParameterName);
  76                         Assert.AreEqual(":Parameter2", command.Parameters[1].ParameterName);
  77                         Assert.AreEqual(":Parameter3", command.Parameters[2].ParameterName);
  78
  79
  80
  81                 }
  82
  83                 [Test]
  84                 public void EmptyQuery()
  85                 {
  86                         _conn.Open();
  87
  88                         NpgsqlCommand command = new NpgsqlCommand(";", _conn);
  89                         command.ExecuteNonQuery();
  90
  91                 }
  92                 [Test]
  93                 [ExpectedException(typeof(ArgumentNullException))]
  94                 public void NoNameParameterAdd()
  95                 {
  96                         NpgsqlCommand command = new NpgsqlCommand();
  97
  98                         command.Parameters.Add(new NpgsqlParameter());
  99
 100                 }
 101
 102                 [Test]
 103                 public void FunctionCallFromSelect()
 104                 {
 105                         _conn.Open();
 106
 107                         NpgsqlCommand command = new NpgsqlCommand("select * from funcB()", _conn);
 108
 109                         NpgsqlDataReader reader = command.ExecuteReader();
 110
 111                         Assertion.AssertNotNull(reader);
 112                         //reader.FieldCount
 113
 114                 }
 115
 116                 [Test]
 117                 public void ExecuteScalar()
 118                 {
 119                         _conn.Open();
 120
 121                         NpgsqlCommand command = new NpgsqlCommand("select count(*) from tablea", _conn);
 122
 123                         Object result = command.ExecuteScalar();
 124
 125                         Assert.AreEqual(5, result);
 126                         //reader.FieldCount
 127
 128                 }
 129
 130                 [Test]
 131                 public void FunctionCallReturnSingleValue()
 132                 {
 133                         _conn.Open();
 134
 135                         NpgsqlCommand command = new NpgsqlCommand("funcC()", _conn);
 136                         command.CommandType = CommandType.StoredProcedure;
 137
 138                         Object result = command.ExecuteScalar();
 139
 140                         Assert.AreEqual(5, result);
 141                         //reader.FieldCount
 142
 143                 }
 144
 145
 146                 [Test]
 147                 public void FunctionCallReturnSingleValueWithPrepare()
 148                 {
 149                         _conn.Open();
 150
 151                         NpgsqlCommand command = new NpgsqlCommand("funcC()", _conn);
 152                         command.CommandType = CommandType.StoredProcedure;
 153
 154                         command.Prepare();
 155                         Object result = command.ExecuteScalar();
 156
 157                         Assert.AreEqual(5, result);
 158                         //reader.FieldCount
 159
 160                 }
 161
 162                 [Test]
 163                 public void FunctionCallWithParametersReturnSingleValue()
 164                 {
 165                         _conn.Open();
 166
 167                         NpgsqlCommand command = new NpgsqlCommand("funcC(:a)", _conn);
 168                         command.CommandType = CommandType.StoredProcedure;
 169
 170                         command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32));
 171
 172                         command.Parameters[0].Value = 4;
 173
 174                         Int64 result = (Int64) command.ExecuteScalar();
 175
 176                         Assert.AreEqual(1, result);
 177
 178
 179                 }
 180
 181                 [Test]
 182                 public void FunctionCallWithParametersReturnSingleValueNpgsqlDbType()
 183                 {
 184                         _conn.Open();
 185
 186                         NpgsqlCommand command = new NpgsqlCommand("funcC(:a)", _conn);
 187                         command.CommandType = CommandType.StoredProcedure;
 188
 189                         command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Integer));
 190
 191                         command.Parameters[0].Value = 4;
 192
 193                         Int64 result = (Int64) command.ExecuteScalar();
 194
 195                         Assert.AreEqual(1, result);
 196
 197                 }
 198
 199
 200
 201
 202                 [Test]
 203                 public void FunctionCallWithParametersPrepareReturnSingleValue()
 204                 {
 205                         _conn.Open();
 206
 207                         NpgsqlCommand command = new NpgsqlCommand("funcC(:a)", _conn);
 208                         command.CommandType = CommandType.StoredProcedure;
 209
 210
 211                         command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32));
 212
 213                         Assert.AreEqual(1, command.Parameters.Count);
 214                         command.Prepare();
 215
 216
 217                         command.Parameters[0].Value = 4;
 218
 219                         Int64 result = (Int64) command.ExecuteScalar();
 220
 221                         Assert.AreEqual(1, result);
 222
 223
 224                 }
 225
 226                 [Test]
 227                 public void FunctionCallWithParametersPrepareReturnSingleValueNpgsqlDbType()
 228                 {
 229                         _conn.Open();
 230
 231                         NpgsqlCommand command = new NpgsqlCommand("funcC(:a)", _conn);
 232                         command.CommandType = CommandType.StoredProcedure;
 233
 234
 235                         command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Integer));
 236
 237                         Assert.AreEqual(1, command.Parameters.Count);
 238                         command.Prepare();
 239
 240
 241                         command.Parameters[0].Value = 4;
 242
 243                         Int64 result = (Int64) command.ExecuteScalar();
 244
 245                         Assert.AreEqual(1, result);
 246
 247
 248                 }
 249
 250
 251                 [Test]
 252                 public void FunctionCallReturnResultSet()
 253                 {
 254                         _conn.Open();
 255
 256                         NpgsqlCommand command = new NpgsqlCommand("funcB()", _conn);
 257                         command.CommandType = CommandType.StoredProcedure;
 258
 259                         NpgsqlDataReader dr = command.ExecuteReader();
 260
 261
 262
 263
 264                 }
 265
 266
 267                 [Test]
 268                 public void CursorStatement()
 269                 {
 270
 271                         _conn.Open();
 272
 273                         Int32 i = 0;
 274
 275                         NpgsqlTransaction t = _conn.BeginTransaction();
 276
 277                         NpgsqlCommand command = new NpgsqlCommand("declare te cursor for select * from tablea;", _conn);
 278
 279                         command.ExecuteNonQuery();
 280
 281                         command.CommandText = "fetch forward 3 in te;";
 282
 283                         NpgsqlDataReader dr = command.ExecuteReader();
 284
 285
 286                         while (dr.Read())
 287                         {
 288                                 i++;
 289                         }
 290
 291                         Assert.AreEqual(3, i);
 292
 293
 294                         i = 0;
 295
 296                         command.CommandText = "fetch backward 1 in te;";
 297
 298                         NpgsqlDataReader dr2 = command.ExecuteReader();
 299
 300                         while (dr2.Read())
 301                         {
 302                                 i++;
 303                         }
 304
 305                         Assert.AreEqual(1, i);
 306
 307                         command.CommandText = "close te;";
 308
 309                         command.ExecuteNonQuery();
 310
 311                         t.Commit();
 312
 313
 314
 315                 }
 316
 317                 [Test]
 318                 public void PreparedStatementNoParameters()
 319                 {
 320                         _conn.Open();
 321
 322                         NpgsqlCommand command = new NpgsqlCommand("select * from tablea;", _conn);
 323
 324                         command.Prepare();
 325
 326                         command.Prepare();
 327
 328                         NpgsqlDataReader dr = command.ExecuteReader();
 329
 330
 331                 }
 332
 333                 [Test]
 334                 public void PreparedStatementWithParameters()
 335                 {
 336                         _conn.Open();
 337
 338                         NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_int4 = :a and field_int8 = :b;", _conn);
 339
 340                         command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32));
 341                         command.Parameters.Add(new NpgsqlParameter("b", DbType.Int64));
 342
 343                         Assert.AreEqual(2, command.Parameters.Count);
 344
 345                         Assert.AreEqual(DbType.Int32, command.Parameters[0].DbType);
 346
 347                         command.Prepare();
 348
 349                         command.Parameters[0].Value = 3;
 350                         command.Parameters[1].Value = 5;
 351
 352                         NpgsqlDataReader dr = command.ExecuteReader();
 353
 354
 355
 356
 357                 }
 358
 359                 [Test]
 360                 public void PreparedStatementWithParametersNpgsqlDbType()
 361                 {
 362                         _conn.Open();
 363
 364                         NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_int4 = :a and field_int8 = :b;", _conn);
 365
 366                         command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Integer));
 367                         command.Parameters.Add(new NpgsqlParameter("b", NpgsqlDbType.Bigint));
 368
 369                         Assert.AreEqual(2, command.Parameters.Count);
 370
 371                         Assert.AreEqual(DbType.Int32, command.Parameters[0].DbType);
 372
 373                         command.Prepare();
 374
 375                         command.Parameters[0].Value = 3;
 376                         command.Parameters[1].Value = 5;
 377
 378                         NpgsqlDataReader dr = command.ExecuteReader();
 379
 380
 381
 382
 383                 }
 384
 385
 386                 [Test]
 387                 [ExpectedException(typeof(InvalidOperationException))]
 388                 public void ListenNotifySupport()
 389                 {
 390
 391                   _conn.Open();
 392
 393                   NpgsqlCommand command = new NpgsqlCommand("listen notifytest;", _conn);
 394                   command.ExecuteNonQuery();
 395
 396                   _conn.Notification += new NotificationEventHandler(NotificationSupportHelper);
 397
 398
 399                   command = new NpgsqlCommand("notify notifytest;", _conn);
 400                   command.ExecuteNonQuery();
 401
 402
 403
 404                 }
 405
 406                 private void NotificationSupportHelper(Object sender, NpgsqlNotificationEventArgs args)
 407                 {
 408                   throw new InvalidOperationException();
 409                 }
 410
 411                 [Test]
 412                 public void DateTimeSupport()
 413                 {
 414                         _conn.Open();
 415
 416
 417                         NpgsqlCommand command = new NpgsqlCommand("select field_timestamp from tableb where field_serial = 2;", _conn);
 418
 419                         DateTime d = (DateTime)command.ExecuteScalar();
 420
 421
 422                         Assert.AreEqual("2002-02-02 09:00:23Z", d.ToString("u"));
 423
 424                         DateTimeFormatInfo culture = new DateTimeFormatInfo();
 425                         culture.TimeSeparator = ":";
 426                         DateTime dt = System.DateTime.Parse("2004-06-04 09:48:00", culture);
 427
 428                         command.CommandText = "insert into tableb(field_timestamp) values (:a);delete from tableb where field_serial > 4;";
 429                         command.Parameters.Add(new NpgsqlParameter("a", DbType.DateTime));
 430                         command.Parameters[0].Value = dt;
 431
 432                         command.ExecuteScalar();
 433
 434                 }
 435
 436
 437                 [Test]
 438                 public void DateTimeSupportNpgsqlDbType()
 439                 {
 440                         _conn.Open();
 441
 442
 443                         NpgsqlCommand command = new NpgsqlCommand("select field_timestamp from tableb where field_serial = 2;", _conn);
 444
 445                         DateTime d = (DateTime)command.ExecuteScalar();
 446
 447
 448                         Assert.AreEqual("2002-02-02 09:00:23Z", d.ToString("u"));
 449
 450                         DateTimeFormatInfo culture = new DateTimeFormatInfo();
 451                         culture.TimeSeparator = ":";
 452                         DateTime dt = System.DateTime.Parse("2004-06-04 09:48:00", culture);
 453
 454                         command.CommandText = "insert into tableb(field_timestamp) values (:a);delete from tableb where field_serial > 4;";
 455                         command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Timestamp));
 456                         command.Parameters[0].Value = dt;
 457
 458                         command.ExecuteScalar();
 459
 460                 }
 461
 462                 [Test]
 463                 public void DateSupport()
 464                 {
 465                   _conn.Open();
 466
 467                   NpgsqlCommand command = new NpgsqlCommand("select field_date from tablec where field_serial = 1;", _conn);
 468
 469                         DateTime d = (DateTime)command.ExecuteScalar();
 470
 471
 472                         Assert.AreEqual("2002-03-04", d.ToString("yyyy-MM-dd"));
 473
 474                 }
 475
 476                 [Test]
 477                 public void TimeSupport()
 478                 {
 479                   _conn.Open();
 480
 481                   NpgsqlCommand command = new NpgsqlCommand("select field_time from tablec where field_serial = 2;", _conn);
 482
 483                         DateTime d = (DateTime)command.ExecuteScalar();
 484
 485
 486                         Assert.AreEqual("10:03:45.345", d.ToString("HH:mm:ss.fff"));
 487
 488                 }
 489
 490                 [Test]
 491                 public void NumericSupport()
 492                 {
 493                         _conn.Open();
 494
 495
 496                         NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_numeric) values (:a)", _conn);
 497                         command.Parameters.Add(new NpgsqlParameter("a", DbType.Decimal));
 498
 499                         command.Parameters[0].Value = 7.4M;
 500
 501                         Int32 rowsAdded = command.ExecuteNonQuery();
 502
 503                         Assert.AreEqual(1, rowsAdded);
 504
 505                         command.CommandText = "select * from tableb where field_numeric = :a";
 506
 507
 508                         NpgsqlDataReader dr = command.ExecuteReader();
 509                         dr.Read();
 510
 511                         Decimal result = dr.GetDecimal(3);
 512
 513
 514                         command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb) and field_serial != 3;";
 515                         command.Parameters.Clear();
 516                         command.ExecuteNonQuery();
 517
 518
 519                         Assert.AreEqual(7.4000000M, result);
 520
 521
 522
 523
 524                 }
 525
 526                 [Test]
 527                 public void NumericSupportNpgsqlDbType()
 528                 {
 529                         _conn.Open();
 530
 531
 532                         NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_numeric) values (:a)", _conn);
 533                         command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Numeric));
 534
 535                         command.Parameters[0].Value = 7.4M;
 536
 537                         Int32 rowsAdded = command.ExecuteNonQuery();
 538
 539                         Assert.AreEqual(1, rowsAdded);
 540
 541                         command.CommandText = "select * from tableb where field_numeric = :a";
 542
 543
 544                         NpgsqlDataReader dr = command.ExecuteReader();
 545                         dr.Read();
 546
 547                         Decimal result = dr.GetDecimal(3);
 548
 549
 550                         command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb) and field_serial != 3;";
 551                         command.Parameters.Clear();
 552                         command.ExecuteNonQuery();
 553
 554
 555                         Assert.AreEqual(7.4000000M, result);
 556
 557
 558
 559
 560                 }
 561
 562
 563                 [Test]
 564                 public void InsertSingleValue()
 565                 {
 566                   _conn.Open();
 567
 568
 569                         NpgsqlCommand command = new NpgsqlCommand("insert into tabled(field_float4) values (:a)", _conn);
 570                         command.Parameters.Add(new NpgsqlParameter(":a", DbType.Single));
 571
 572                         command.Parameters[0].Value = 7.4F;
 573
 574                         Int32 rowsAdded = command.ExecuteNonQuery();
 575
 576                         Assert.AreEqual(1, rowsAdded);
 577
 578                         command.CommandText = "select * from tabled where field_float4 = :a";
 579
 580
 581                         NpgsqlDataReader dr = command.ExecuteReader();
 582                         dr.Read();
 583
 584                         Single result = dr.GetFloat(1);
 585
 586
 587                         command.CommandText = "delete from tabled where field_serial > 2;";
 588                         command.Parameters.Clear();
 589                         command.ExecuteNonQuery();
 590
 591
 592                         Assert.AreEqual(7.4F, result);
 593
 594                 }
 595
 596
 597                 [Test]
 598                 public void InsertSingleValueNpgsqlDbType()
 599                 {
 600                   _conn.Open();
 601
 602
 603                         NpgsqlCommand command = new NpgsqlCommand("insert into tabled(field_float4) values (:a)", _conn);
 604                         command.Parameters.Add(new NpgsqlParameter(":a", NpgsqlDbType.Real));
 605
 606                         command.Parameters[0].Value = 7.4F;
 607
 608                         Int32 rowsAdded = command.ExecuteNonQuery();
 609
 610                         Assert.AreEqual(1, rowsAdded);
 611
 612                         command.CommandText = "select * from tabled where field_float4 = :a";
 613
 614
 615                         NpgsqlDataReader dr = command.ExecuteReader();
 616                         dr.Read();
 617
 618                         Single result = dr.GetFloat(1);
 619
 620
 621                         command.CommandText = "delete from tabled where field_serial > 2;";
 622                         command.Parameters.Clear();
 623                         command.ExecuteNonQuery();
 624
 625
 626                         Assert.AreEqual(7.4F, result);
 627
 628                 }
 629
 630                 [Test]
 631                 public void InsertDoubleValue()
 632                 {
 633                   _conn.Open();
 634
 635
 636                         NpgsqlCommand command = new NpgsqlCommand("insert into tabled(field_float8) values (:a)", _conn);
 637                         command.Parameters.Add(new NpgsqlParameter(":a", DbType.Double));
 638
 639                         command.Parameters[0].Value = 7.4D;
 640
 641                         Int32 rowsAdded = command.ExecuteNonQuery();
 642
 643                         Assert.AreEqual(1, rowsAdded);
 644
 645                         command.CommandText = "select * from tabled where field_float8 = :a";
 646
 647
 648                         NpgsqlDataReader dr = command.ExecuteReader();
 649                         dr.Read();
 650
 651                         Double result = dr.GetDouble(2);
 652
 653
 654                         command.CommandText = "delete from tabled where field_serial > 2;";
 655                         command.Parameters.Clear();
 656                         //command.ExecuteNonQuery();
 657
 658
 659                         Assert.AreEqual(7.4D, result);
 660
 661                 }
 662
 663
 664                 [Test]
 665                 public void InsertDoubleValueNpgsqlDbType()
 666                 {
 667                   _conn.Open();
 668
 669
 670                         NpgsqlCommand command = new NpgsqlCommand("insert into tabled(field_float8) values (:a)", _conn);
 671                         command.Parameters.Add(new NpgsqlParameter(":a", NpgsqlDbType.Double));
 672
 673                         command.Parameters[0].Value = 7.4D;
 674
 675                         Int32 rowsAdded = command.ExecuteNonQuery();
 676
 677                         Assert.AreEqual(1, rowsAdded);
 678
 679                         command.CommandText = "select * from tabled where field_float8 = :a";
 680
 681
 682                         NpgsqlDataReader dr = command.ExecuteReader();
 683                         dr.Read();
 684
 685                         Double result = dr.GetDouble(2);
 686
 687
 688                         command.CommandText = "delete from tabled where field_serial > 2;";
 689                         command.Parameters.Clear();
 690                         //command.ExecuteNonQuery();
 691
 692
 693                         Assert.AreEqual(7.4D, result);
 694
 695                 }
 696
 697
 698                 [Test]
 699                 public void NegativeNumericSupport()
 700                 {
 701                         _conn.Open();
 702
 703
 704                         NpgsqlCommand command = new NpgsqlCommand("select * from tableb where field_serial = 4", _conn);
 705
 706
 707                         NpgsqlDataReader dr = command.ExecuteReader();
 708                         dr.Read();
 709
 710                         Decimal result = dr.GetDecimal(3);
 711
 712                         Assert.AreEqual(-4.3000000M, result);
 713
 714                 }
 715
 716
 717                 [Test]
 718                 public void PrecisionScaleNumericSupport()
 719                 {
 720                         _conn.Open();
 721
 722
 723                         NpgsqlCommand command = new NpgsqlCommand("select * from tableb where field_serial = 4", _conn);
 724
 725
 726                         NpgsqlDataReader dr = command.ExecuteReader();
 727                         dr.Read();
 728
 729                         Decimal result = dr.GetDecimal(3);
 730
 731                         Assert.AreEqual(-4.3000000M, (Decimal)result);
 732                         //Assert.AreEqual(11, result.Precision);
 733                         //Assert.AreEqual(7, result.Scale);
 734
 735                 }
 736
 737                 [Test]
 738                 public void InsertNullString()
 739                 {
 740                         _conn.Open();
 741
 742                         NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_text) values (:a)", _conn);
 743
 744                         command.Parameters.Add(new NpgsqlParameter("a", DbType.String));
 745
 746                         command.Parameters[0].Value = DBNull.Value;
 747
 748                         Int32 rowsAdded = command.ExecuteNonQuery();
 749
 750                         Assert.AreEqual(1, rowsAdded);
 751
 752                         command.CommandText = "select count(*) from tablea where field_text is null";
 753                         command.Parameters.Clear();
 754
 755                         Int64 result = (Int64)command.ExecuteScalar();
 756
 757                         command.CommandText = "delete from tablea where field_serial = (select max(field_serial) from tablea) and field_serial != 4;";
 758                         command.ExecuteNonQuery();
 759
 760                         Assert.AreEqual(4, result);
 761
 762
 763
 764                 }
 765
 766                 [Test]
 767                 public void InsertNullStringNpgsqlDbType()
 768                 {
 769                         _conn.Open();
 770
 771                         NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_text) values (:a)", _conn);
 772
 773                         command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Text));
 774
 775                         command.Parameters[0].Value = DBNull.Value;
 776
 777                         Int32 rowsAdded = command.ExecuteNonQuery();
 778
 779                         Assert.AreEqual(1, rowsAdded);
 780
 781                         command.CommandText = "select count(*) from tablea where field_text is null";
 782                         command.Parameters.Clear();
 783
 784                         Int64 result = (Int64)command.ExecuteScalar();
 785
 786                         command.CommandText = "delete from tablea where field_serial = (select max(field_serial) from tablea) and field_serial != 4;";
 787                         command.ExecuteNonQuery();
 788
 789                         Assert.AreEqual(4, result);
 790
 791
 792
 793                 }
 794
 795
 796
 797                 [Test]
 798                 public void InsertNullDateTime()
 799                 {
 800                         _conn.Open();
 801
 802                         NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_timestamp) values (:a)", _conn);
 803
 804                         command.Parameters.Add(new NpgsqlParameter("a", DbType.DateTime));
 805
 806                         command.Parameters[0].Value = DBNull.Value;
 807
 808                         Int32 rowsAdded = command.ExecuteNonQuery();
 809
 810                         Assert.AreEqual(1, rowsAdded);
 811
 812                         command.CommandText = "select count(*) from tableb where field_timestamp is null";
 813                         command.Parameters.Clear();
 814
 815                         Object result = command.ExecuteScalar();
 816
 817                         command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb) and field_serial != 3;";
 818                         command.ExecuteNonQuery();
 819
 820                         Assert.AreEqual(4, result);
 821
 822
 823
 824                 }
 825
 826
 827                 [Test]
 828                 public void InsertNullDateTimeNpgsqlDbType()
 829                 {
 830                         _conn.Open();
 831
 832                         NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_timestamp) values (:a)", _conn);
 833
 834                         command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Timestamp));
 835
 836                         command.Parameters[0].Value = DBNull.Value;
 837
 838                         Int32 rowsAdded = command.ExecuteNonQuery();
 839
 840                         Assert.AreEqual(1, rowsAdded);
 841
 842                         command.CommandText = "select count(*) from tableb where field_timestamp is null";
 843                         command.Parameters.Clear();
 844
 845                         Object result = command.ExecuteScalar();
 846
 847                         command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb) and field_serial != 3;";
 848                         command.ExecuteNonQuery();
 849
 850                         Assert.AreEqual(4, result);
 851
 852
 853
 854                 }
 855
 856
 857
 858                 [Test]
 859                 public void InsertNullInt16()
 860                 {
 861                         _conn.Open();
 862
 863
 864                         NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_int2) values (:a)", _conn);
 865
 866                         command.Parameters.Add(new NpgsqlParameter("a", DbType.Int16));
 867
 868                         command.Parameters[0].Value = DBNull.Value;
 869
 870                         Int32 rowsAdded = command.ExecuteNonQuery();
 871
 872                         Assert.AreEqual(1, rowsAdded);
 873
 874                         command.CommandText = "select count(*) from tableb where field_int2 is null";
 875                         command.Parameters.Clear();
 876
 877                         Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
 878
 879                         command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb);";
 880                         command.ExecuteNonQuery();
 881
 882                         Assert.AreEqual(4, result);
 883
 884
 885                 }
 886
 887
 888                 [Test]
 889                 public void InsertNullInt16NpgsqlDbType()
 890                 {
 891                         _conn.Open();
 892
 893
 894                         NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_int2) values (:a)", _conn);
 895
 896                         command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Smallint));
 897
 898                         command.Parameters[0].Value = DBNull.Value;
 899
 900                         Int32 rowsAdded = command.ExecuteNonQuery();
 901
 902                         Assert.AreEqual(1, rowsAdded);
 903
 904                         command.CommandText = "select count(*) from tableb where field_int2 is null";
 905                         command.Parameters.Clear();
 906
 907                         Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
 908
 909                         command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb);";
 910                         command.ExecuteNonQuery();
 911
 912                         Assert.AreEqual(4, result);
 913
 914
 915                 }
 916
 917
 918                 [Test]
 919                 public void InsertNullInt32()
 920                 {
 921                         _conn.Open();
 922
 923
 924                         NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_int4) values (:a)", _conn);
 925
 926                         command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32));
 927
 928                         command.Parameters[0].Value = DBNull.Value;
 929
 930                         Int32 rowsAdded = command.ExecuteNonQuery();
 931
 932                         Assert.AreEqual(1, rowsAdded);
 933
 934                         command.CommandText = "select count(*) from tablea where field_int4 is null";
 935                         command.Parameters.Clear();
 936
 937                         Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
 938
 939                         command.CommandText = "delete from tablea where field_serial = (select max(field_serial) from tablea);";
 940                         command.ExecuteNonQuery();
 941
 942                         Assert.AreEqual(5, result);
 943
 944                 }
 945
 946
 947                 [Test]
 948                 public void InsertNullNumeric()
 949                 {
 950                         _conn.Open();
 951
 952
 953                         NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_numeric) values (:a)", _conn);
 954
 955                         command.Parameters.Add(new NpgsqlParameter("a", DbType.Decimal));
 956
 957                         command.Parameters[0].Value = DBNull.Value;
 958
 959                         Int32 rowsAdded = command.ExecuteNonQuery();
 960
 961                         Assert.AreEqual(1, rowsAdded);
 962
 963                         command.CommandText = "select count(*) from tableb where field_numeric is null";
 964                         command.Parameters.Clear();
 965
 966                         Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
 967
 968                         command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb);";
 969                         command.ExecuteNonQuery();
 970
 971                         Assert.AreEqual(3, result);
 972
 973                 }
 974
 975                 [Test]
 976                 public void InsertNullBoolean()
 977                 {
 978                         _conn.Open();
 979
 980
 981                         NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_bool) values (:a)", _conn);
 982
 983                         command.Parameters.Add(new NpgsqlParameter("a", DbType.Boolean));
 984
 985                         command.Parameters[0].Value = DBNull.Value;
 986
 987                         Int32 rowsAdded = command.ExecuteNonQuery();
 988
 989                         Assert.AreEqual(1, rowsAdded);
 990
 991                         command.CommandText = "select count(*) from tablea where field_bool is null";
 992                         command.Parameters.Clear();
 993
 994                         Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
 995
 996                         command.CommandText = "delete from tablea where field_serial = (select max(field_serial) from tablea);";
 997                         command.ExecuteNonQuery();
 998
 999                         Assert.AreEqual(5, result);
1000
1001                 }
1002
1003         [Test]
1004                 public void AnsiStringSupport()
1005                 {
1006                         _conn.Open();
1007
1008                         NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_text) values (:a)", _conn);
1009
1010                         command.Parameters.Add(new NpgsqlParameter("a", DbType.AnsiString));
1011
1012                         command.Parameters[0].Value = "TesteAnsiString";
1013
1014                         Int32 rowsAdded = command.ExecuteNonQuery();
1015
1016                         Assert.AreEqual(1, rowsAdded);
1017
1018                         command.CommandText = String.Format("select count(*) from tablea where field_text = '{0}'", command.Parameters[0].Value);
1019                         command.Parameters.Clear();
1020
1021                         Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
1022
1023                         command.CommandText = "delete from tablea where field_serial = (select max(field_serial) from tablea);";
1024                         command.ExecuteNonQuery();
1025
1026                         Assert.AreEqual(1, result);
1027
1028                 }
1029
1030
1031         [Test]
1032                 public void MultipleQueriesFirstResultsetEmpty()
1033                 {
1034                         _conn.Open();
1035
1036                         NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_text) values ('a'); select count(*) from tablea;", _conn);
1037
1038             Object result = command.ExecuteScalar();
1039
1040
1041             command.CommandText = "delete from tablea where field_serial > 5";
1042             command.ExecuteNonQuery();
1043
1044             command.CommandText = "select * from tablea where field_serial = 0";
1045             command.ExecuteScalar();
1046
1047
1048             Assert.AreEqual(6, result);
1049
1050
1051                 }
1052
1053         [Test]
1054         [ExpectedException(typeof(NpgsqlException))]
1055         public void ConnectionStringWithInvalidParameters()
1056         {
1057             NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=npgsql_tests;Password=j");
1058
1059             NpgsqlCommand command = new NpgsqlCommand("select * from tablea", conn);
1060
1061             command.Connection.Open();
1062             command.ExecuteReader();
1063             command.Connection.Close();
1064
1065
1066         }
1067
1068                 [Test]
1069         [ExpectedException(typeof(NpgsqlException))]
1070         public void InvalidConnectionString()
1071         {
1072             NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=npgsql_tests");
1073
1074             NpgsqlCommand command = new NpgsqlCommand("select * from tablea", conn);
1075
1076             command.Connection.Open();
1077             command.ExecuteReader();
1078             command.Connection.Close();
1079
1080
1081         }
1082
1083
1084         [Test]
1085         public void AmbiguousFunctionParameterType()
1086         {
1087             NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=npgsql_tests;Password=npgsql_tests");
1088
1089
1090             NpgsqlCommand command = new NpgsqlCommand("ambiguousParameterType(:a, :b, :c, :d, :e, :f)", conn);
1091             command.CommandType = CommandType.StoredProcedure;
1092             NpgsqlParameter p = new NpgsqlParameter("a", DbType.Int16);
1093             p.Value = 2;
1094             command.Parameters.Add(p);
1095             p = new NpgsqlParameter("b", DbType.Int32);
1096             p.Value = 2;
1097             command.Parameters.Add(p);
1098             p = new NpgsqlParameter("c", DbType.Int64);
1099             p.Value = 2;
1100             command.Parameters.Add(p);
1101             p = new NpgsqlParameter("d", DbType.String);
1102             p.Value = "a";
1103             command.Parameters.Add(p);
1104             p = new NpgsqlParameter("e", DbType.String);
1105             p.Value = "a";
1106             command.Parameters.Add(p);
1107             p = new NpgsqlParameter("f", DbType.String);
1108             p.Value = "a";
1109             command.Parameters.Add(p);
1110
1111
1112             command.Connection.Open();
1113             command.Prepare();
1114             command.ExecuteScalar();
1115             command.Connection.Close();
1116
1117
1118         }
1119
1120
1121         [Test]
1122                 public void TestParameterReplace()
1123                 {
1124                         _conn.Open();
1125
1126                         String sql = @"select * from tablea where
1127                         field_serial = :a
1128                         ";
1129
1130
1131                         NpgsqlCommand command = new NpgsqlCommand(sql, _conn);
1132
1133                         command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32));
1134
1135                         command.Parameters[0].Value = 2;
1136
1137                         Int32 rowsAdded = command.ExecuteNonQuery();
1138
1139                 }
1140
1141                 [Test]
1142                 public void TestPointSupport()
1143                 {
1144
1145                         _conn.Open();
1146
1147                         NpgsqlCommand command = new NpgsqlCommand("select field_point from tablee where field_serial = 1", _conn);
1148
1149                         NpgsqlPoint p = (NpgsqlPoint) command.ExecuteScalar();
1150
1151                         Assert.AreEqual(4, p.X);
1152                         Assert.AreEqual(3, p.Y);
1153                 }
1154
1155
1156                 [Test]
1157                 public void TestBoxSupport()
1158                 {
1159
1160                         _conn.Open();
1161
1162                         NpgsqlCommand command = new NpgsqlCommand("select field_box from tablee where field_serial = 2", _conn);
1163
1164                         NpgsqlBox box = (NpgsqlBox) command.ExecuteScalar();
1165
1166                         Assert.AreEqual(5, box.UpperRight.X);
1167                         Assert.AreEqual(4, box.UpperRight.Y);
1168                         Assert.AreEqual(4, box.LowerLeft.X);
1169                         Assert.AreEqual(3, box.LowerLeft.Y);
1170
1171
1172                 }
1173
1174                 [Test]
1175                 public void TestLSegSupport()
1176                 {
1177
1178                         _conn.Open();
1179
1180                         NpgsqlCommand command = new NpgsqlCommand("select field_lseg from tablee where field_serial = 3", _conn);
1181
1182                         NpgsqlLSeg lseg = (NpgsqlLSeg) command.ExecuteScalar();
1183
1184                         Assert.AreEqual(4, lseg.Start.X);
1185                         Assert.AreEqual(3, lseg.Start.Y);
1186                         Assert.AreEqual(5, lseg.End.X);
1187                         Assert.AreEqual(4, lseg.End.Y);
1188
1189
1190                 }
1191
1192                 [Test]
1193                 public void TestClosedPathSupport()
1194                 {
1195
1196                         _conn.Open();
1197
1198                         NpgsqlCommand command = new NpgsqlCommand("select field_path from tablee where field_serial = 4", _conn);
1199
1200                         NpgsqlPath path = (NpgsqlPath) command.ExecuteScalar();
1201
1202                         Assert.AreEqual(false, path.Open);
1203                         Assert.AreEqual(2, path.Count);
1204                         Assert.AreEqual(4, path[0].X);
1205                         Assert.AreEqual(3, path[0].Y);
1206                         Assert.AreEqual(5, path[1].X);
1207                         Assert.AreEqual(4, path[1].Y);
1208
1209
1210                 }
1211
1212                 [Test]
1213                 public void TestOpenPathSupport()
1214                 {
1215
1216                         _conn.Open();
1217
1218                         NpgsqlCommand command = new NpgsqlCommand("select field_path from tablee where field_serial = 5", _conn);
1219
1220                         NpgsqlPath path = (NpgsqlPath) command.ExecuteScalar();
1221
1222                         Assert.AreEqual(true, path.Open);
1223                         Assert.AreEqual(2, path.Count);
1224                         Assert.AreEqual(4, path[0].X);
1225                         Assert.AreEqual(3, path[0].Y);
1226                         Assert.AreEqual(5, path[1].X);
1227                         Assert.AreEqual(4, path[1].Y);
1228
1229
1230                 }
1231
1232
1233
1234                 [Test]
1235                 public void TestPolygonSupport()
1236                 {
1237
1238                         _conn.Open();
1239
1240                         NpgsqlCommand command = new NpgsqlCommand("select field_polygon from tablee where field_serial = 6", _conn);
1241
1242                         NpgsqlPolygon polygon = (NpgsqlPolygon) command.ExecuteScalar();
1243
1244                         Assert.AreEqual(2, polygon.Count);
1245                         Assert.AreEqual(4, polygon[0].X);
1246                         Assert.AreEqual(3, polygon[0].Y);
1247                         Assert.AreEqual(5, polygon[1].X);
1248                         Assert.AreEqual(4, polygon[1].Y);
1249
1250
1251                 }
1252
1253
1254                 [Test]
1255                 public void TestCircleSupport()
1256                 {
1257
1258                         _conn.Open();
1259
1260                         NpgsqlCommand command = new NpgsqlCommand("select field_circle from tablee where field_serial = 7", _conn);
1261
1262                         NpgsqlCircle circle = (NpgsqlCircle) command.ExecuteScalar();
1263
1264                         Assert.AreEqual(4, circle.Center.X);
1265                         Assert.AreEqual(3, circle.Center.Y);
1266                         Assert.AreEqual(5, circle.Radius);
1267
1268
1269
1270                 }
1271
1272
1273
1274
1275
1276
1277         }
1278 }