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