mcs/class/Mono.Data.PostgreSqlClient/Mono.Data.PostgreSqlClient/PgSqlCommand.cs

   1 //
   2 // System.Data.SqlClient.SqlCommand.cs
   3 //
   4 // Author:
   5 //   Rodrigo Moya (rodrigo@ximian.com)
   6 //   Daniel Morgan (danmorg@sc.rr.com)
   7 //
   8 // (C) Ximian, Inc 2002 http://www.ximian.com/
   9 // (C) Daniel Morgan, 2002
  10 //
  11 // Credits:
  12 //    SQL and concepts were used from libgda 0.8.190 (GNOME Data Access)\r
  13 //    http://www.gnome-db.org/\r
  14 //    with permission from the authors of the\r
  15 //    PostgreSQL provider in libgda:\r
  16 //        Michael Lausch <michael@lausch.at>
  17 //        Rodrigo Moya <rodrigo@gnome-db.org>
  18 //        Vivien Malerba <malerba@gnome-db.org>
  19 //        Gonzalo Paniagua Javier <gonzalo@gnome-db.org>
  20 //
  21
  22 // use #define DEBUG_SqlCommand if you want to spew debug messages
  23 // #define DEBUG_SqlCommand
  24
  25 using System;
  26 using System.Collections;
  27 using System.ComponentModel;
  28 using System.Data;
  29 using System.Data.Common;
  30 using System.Runtime.InteropServices;
  31 using System.Text;
  32 using System.Xml;
  33
  34 namespace System.Data.SqlClient {
  35         /// <summary>
  36         /// Represents a SQL statement that is executed
  37         /// while connected to a SQL database.
  38         /// </summary>
  39         // public sealed class SqlCommand : Component, IDbCommand, ICloneable
  40         public sealed class SqlCommand : IDbCommand {
  41
  42                 #region Fields
  43
  44                 private string sql = "";
  45                 private int timeout = 30;
  46                 // default is 30 seconds
  47                 // for command execution
  48
  49                 private SqlConnection conn = null;
  50                 private SqlTransaction trans = null;
  51                 private CommandType cmdType = CommandType.Text;
  52                 private bool designTime = false;
  53                 private SqlParameterCollection parmCollection = new
  54                         SqlParameterCollection();
  55
  56                 // SqlDataReader state data for ExecuteReader()
  57                 private SqlDataReader dataReader = null;
  58                 private string[] queries = null;
  59                 private int currentQuery;
  60                 private CommandBehavior cmdBehavior = CommandBehavior.Default;
  61
  62                 private ParmUtil parmUtil = null;
  63
  64                 #endregion // Fields
  65
  66                 #region Constructors
  67
  68                 public SqlCommand() {
  69                         sql = "";
  70                 }
  71
  72                 public SqlCommand (string cmdText) {
  73                         sql = cmdText;
  74                 }
  75
  76                 public SqlCommand (string cmdText, SqlConnection connection) {
  77                         sql = cmdText;
  78                         conn = connection;
  79                 }
  80
  81                 public SqlCommand (string cmdText, SqlConnection connection,
  82                         SqlTransaction transaction) {
  83                         sql = cmdText;
  84                         conn = connection;
  85                         trans = transaction;
  86                 }
  87
  88                 #endregion // Constructors
  89
  90                 #region Methods
  91
  92                 [MonoTODO]
  93                 public void Cancel () {
  94                         // FIXME: use non-blocking Exec for this
  95                         throw new NotImplementedException ();
  96                 }
  97
  98                 // FIXME: is this the correct way to return a stronger type?
  99                 [MonoTODO]
 100                 IDbDataParameter IDbCommand.CreateParameter () {
 101                         return CreateParameter ();
 102                 }
 103
 104                 [MonoTODO]
 105                 public SqlParameter CreateParameter () {
 106                         return new SqlParameter ();
 107                 }
 108
 109                 public int ExecuteNonQuery () {
 110                         IntPtr pgResult; // PGresult
 111                         int rowsAffected = -1;
 112                         ExecStatusType execStatus;
 113                         String rowsAffectedString;
 114                         string query;
 115
 116                         if(conn.State != ConnectionState.Open)
 117                                 throw new InvalidOperationException(
 118                                         "ConnnectionState is not Open");
 119
 120                         query = TweakQuery(sql, cmdType);
 121
 122                         // FIXME: PQexec blocks
 123                         // while PQsendQuery is non-blocking
 124                         // which is better to use?
 125                         // int PQsendQuery(PGconn *conn,
 126                         //        const char *query);
 127
 128                         // execute SQL command
 129                         // uses internal property to get the PGConn IntPtr
 130                         pgResult = PostgresLibrary.
 131                                 PQexec (conn.PostgresConnection, query);
 132
 133                         execStatus = PostgresLibrary.
 134                                 PQresultStatus (pgResult);
 135
 136                         if(execStatus == ExecStatusType.PGRES_COMMAND_OK) {
 137                                 rowsAffectedString = PostgresLibrary.
 138                                         PQcmdTuples (pgResult);
 139
 140                                 if(rowsAffectedString != null)
 141                                         if(rowsAffectedString.Equals("") == false)
 142                                                 rowsAffected = int.Parse(rowsAffectedString);
 143
 144                                 PostgresLibrary.PQclear (pgResult);
 145                                 pgResult = IntPtr.Zero;
 146                         }
 147                         else {
 148                                 String errorMessage;
 149
 150                                 errorMessage = PostgresLibrary.
 151                                         PQresStatus(execStatus);
 152
 153                                 errorMessage += " " + PostgresLibrary.\r
 154                                         PQresultErrorMessage(pgResult);\r
 155 \r
 156                                 PostgresLibrary.PQclear (pgResult);
 157                                 pgResult = IntPtr.Zero;
 158 \r
 159                                 throw new SqlException(0, 0,
 160                                         errorMessage, 0, "",
 161                                         conn.DataSource, "SqlCommand", 0);\r
 162                         }
 163
 164                         return rowsAffected;
 165                 }
 166
 167                 [MonoTODO]
 168                 IDataReader IDbCommand.ExecuteReader () {
 169                         return ExecuteReader ();
 170                 }
 171
 172                 [MonoTODO]
 173                 public SqlDataReader ExecuteReader () {
 174                         return ExecuteReader(CommandBehavior.Default);
 175                 }
 176
 177                 [MonoTODO]
 178                 IDataReader IDbCommand.ExecuteReader (
 179                         CommandBehavior behavior) {
 180                         return ExecuteReader (behavior);
 181                 }
 182
 183                 [MonoTODO]
 184                 public SqlDataReader ExecuteReader (CommandBehavior behavior)
 185                 {
 186                         if(conn.State != ConnectionState.Open)
 187                                 throw new InvalidOperationException(
 188                                         "ConnectionState is not Open");
 189
 190                         cmdBehavior = behavior;
 191
 192                         queries = null;
 193                         currentQuery = -1;
 194                         dataReader = new SqlDataReader(this);
 195
 196                         if((behavior & CommandBehavior.SingleResult) == CommandBehavior.SingleResult) {
 197                                 queries = new String[1];
 198                                 queries[0] = sql;
 199                         }
 200                         else {
 201                                 queries = sql.Split(new Char[] {';'});
 202                         }
 203
 204                         dataReader.NextResult();
 205
 206                         return dataReader;
 207                 }
 208
 209                 internal SqlResult NextResult()
 210                 {
 211                         SqlResult res = new SqlResult();
 212                         res.Connection = this.Connection;
 213                         string statement;
 214
 215                         currentQuery++;
 216
 217                         if(currentQuery < queries.Length && queries[currentQuery].Equals("") == false) {
 218                                 statement = TweakQuery(queries[currentQuery], cmdType);
 219                                 ExecuteQuery(statement, res);
 220                                 res.ResultReturned = true;
 221                         }
 222                         else {
 223                                 res.ResultReturned = false;
 224                         }
 225
 226                         return res;
 227                 }
 228
 229                 private string TweakQuery(string query, CommandType commandType) {
 230                         string statement = "";
 231                         StringBuilder td;
 232
 233 #if DEBUG_SqlCommand
 234                         Console.WriteLine("---------[][] TweakQuery() [][]--------");
 235                         Console.WriteLine("CommandType: " + commandType + " CommandBehavior: " + cmdBehavior);
 236                         Console.WriteLine("SQL before command type: " + query);
 237 #endif
 238                         // finish building SQL based on CommandType
 239                         switch(commandType) {
 240                         case CommandType.Text:
 241                                 statement = query;
 242                                 break;
 243                         case CommandType.StoredProcedure:
 244                                 statement =
 245                                         "SELECT " + query + "()";
 246                                 break;
 247                         case CommandType.TableDirect:
 248                                 // NOTE: this is for the PostgreSQL provider
 249                                 //       and for OleDb, according to the docs,
 250                                 //       an exception is thrown if you try to use
 251                                 //       this with SqlCommand
 252                                 string[] directTables = query.Split(
 253                                         new Char[] {','});
 254
 255                                 td = new StringBuilder("SELECT * FROM ");
 256
 257                                 for(int tab = 0; tab < directTables.Length; tab++) {
 258                                         if(tab > 0)
 259                                                 td.Append(',');
 260                                         td.Append(directTables[tab]);
 261                                         // FIXME: if multipe tables, how do we
 262                                         //        join? based on Primary/Foreign Keys?
 263                                         //        Otherwise, a Cartesian Product happens
 264                                 }
 265                                 statement = td.ToString();
 266                                 break;
 267                         default:
 268                                 // FIXME: throw an exception?
 269                                 statement = query;
 270                                 break;
 271                         }
 272 #if DEBUG_SqlCommand
 273                         Console.WriteLine("SQL after command type: " + statement);
 274 #endif
 275                         // TODO: this parameters utility
 276                         //       currently only support input variables
 277                         //       need todo output, input/output, and return.
 278 #if DEBUG_SqlCommand
 279                         Console.WriteLine("using ParmUtil in TweakQuery()...");
 280 #endif
 281                         parmUtil = new ParmUtil(statement, parmCollection);
 282 #if DEBUG_SqlCommand
 283                         Console.WriteLine("ReplaceWithParms...");
 284 #endif
 285
 286                         statement = parmUtil.ReplaceWithParms();
 287
 288 #if DEBUG_SqlCommand
 289                         Console.WriteLine("SQL after ParmUtil: " + statement);
 290 #endif
 291                         return statement;
 292                 }
 293
 294                 private void ExecuteQuery (string query, SqlResult res)
 295                 {
 296                         IntPtr pgResult;
 297
 298                         ExecStatusType execStatus;
 299
 300                         if(conn.State != ConnectionState.Open)
 301                                 throw new InvalidOperationException(
 302                                         "ConnectionState is not Open");
 303
 304                         // FIXME: PQexec blocks
 305                         // while PQsendQuery is non-blocking
 306                         // which is better to use?
 307                         // int PQsendQuery(PGconn *conn,
 308                         //        const char *query);
 309
 310                         // execute SQL command
 311                         // uses internal property to get the PGConn IntPtr
 312                         pgResult = PostgresLibrary.
 313                                 PQexec (conn.PostgresConnection, query);
 314
 315                         execStatus = PostgresLibrary.
 316                                 PQresultStatus (pgResult);
 317
 318                         if(execStatus == ExecStatusType.PGRES_TUPLES_OK) {
 319                                 res.BuildTableSchema(pgResult);
 320                         }
 321                         else {
 322                                 String errorMessage;
 323
 324                                 errorMessage = PostgresLibrary.
 325                                         PQresStatus(execStatus);
 326
 327                                 errorMessage += " " + PostgresLibrary.\r
 328                                         PQresultErrorMessage(pgResult);\r
 329 \r
 330                                 PostgresLibrary.PQclear (pgResult);
 331                                 pgResult = IntPtr.Zero;
 332 \r
 333                                 throw new SqlException(0, 0,
 334                                         errorMessage, 0, "",
 335                                         conn.DataSource, "SqlCommand", 0);\r
 336                         }
 337                 }
 338
 339                 // since SqlCommand has resources so SqlDataReader
 340                 // can do Read() and NextResult(), need to free
 341                 // those resources.  Also, need to allow this SqlCommand
 342                 // and this SqlConnection to do things again.
 343                 internal void CloseReader() {
 344                         conn.OpenReader = false;
 345                         dataReader = null;
 346                         queries = null;
 347                 }
 348
 349                 /// <summary>\r
 350                 /// ExecuteScalar is used to retrieve one object
 351                 /// from one result set \r
 352                 /// that has one row and one column.\r
 353                 /// It is lightweight compared to ExecuteReader.\r
 354                 /// </summary>
 355                 [MonoTODO]
 356                 public object ExecuteScalar () {
 357                         IntPtr pgResult; // PGresult
 358                         ExecStatusType execStatus;
 359                         object obj = null; // return
 360                         int nRow = 0; // first row
 361                         int nCol = 0; // first column
 362                         String value;
 363                         int nRows;
 364                         int nFields;
 365                         string query;
 366
 367                         if(conn.State != ConnectionState.Open)
 368                                 throw new InvalidOperationException(
 369                                         "ConnnectionState is not Open");
 370
 371                         query = TweakQuery(sql, cmdType);
 372
 373                         // FIXME: PQexec blocks
 374                         // while PQsendQuery is non-blocking
 375                         // which is better to use?
 376                         // int PQsendQuery(PGconn *conn,
 377                         //        const char *query);
 378
 379                         // execute SQL command
 380                         // uses internal property to get the PGConn IntPtr
 381                         pgResult = PostgresLibrary.
 382                                 PQexec (conn.PostgresConnection, query);
 383
 384                         execStatus = PostgresLibrary.
 385                                 PQresultStatus (pgResult);
 386
 387                         if(execStatus == ExecStatusType.PGRES_TUPLES_OK) {
 388                                 nRows = PostgresLibrary.
 389                                         PQntuples(pgResult);
 390
 391                                 nFields = PostgresLibrary.
 392                                         PQnfields(pgResult);
 393
 394                                 if(nRows > 0 && nFields > 0) {
 395
 396                                         // get column name
 397                                         //String fieldName;
 398                                         //fieldName = PostgresLibrary.
 399                                         //      PQfname(pgResult, nCol);
 400
 401                                         int oid;
 402                                         string sType;
 403                                         DbType dbType;
 404                                         // get PostgreSQL data type (OID)
 405                                         oid = PostgresLibrary.
 406                                                 PQftype(pgResult, nCol);
 407                                         sType = PostgresHelper.
 408                                                 OidToTypname (oid, conn.Types);
 409                                         dbType = PostgresHelper.
 410                                                 TypnameToSqlDbType(sType);
 411
 412                                         int definedSize;
 413                                         // get defined size of column
 414                                         definedSize = PostgresLibrary.
 415                                                 PQfsize(pgResult, nCol);
 416
 417                                         // get data value
 418                                         value = PostgresLibrary.
 419                                                 PQgetvalue(
 420                                                 pgResult,
 421                                                 nRow, nCol);
 422
 423                                         int columnIsNull;
 424                                         // is column NULL?
 425                                         columnIsNull = PostgresLibrary.
 426                                                 PQgetisnull(pgResult,
 427                                                 nRow, nCol);
 428
 429                                         int actualLength;
 430                                         // get Actual Length
 431                                         actualLength = PostgresLibrary.
 432                                                 PQgetlength(pgResult,
 433                                                 nRow, nCol);
 434
 435                                         obj = PostgresHelper.
 436                                                 ConvertDbTypeToSystem (
 437                                                 dbType,
 438                                                 value);
 439                                 }
 440
 441                                 // close result set
 442                                 PostgresLibrary.PQclear (pgResult);
 443                                 pgResult = IntPtr.Zero;
 444
 445                         }
 446                         else {
 447                                 String errorMessage;
 448
 449                                 errorMessage = PostgresLibrary.
 450                                         PQresStatus(execStatus);
 451
 452                                 errorMessage += " " + PostgresLibrary.\r
 453                                         PQresultErrorMessage(pgResult);\r
 454 \r
 455                                 PostgresLibrary.PQclear (pgResult);
 456                                 pgResult = IntPtr.Zero;
 457 \r
 458                                 throw new SqlException(0, 0,
 459                                         errorMessage, 0, "",
 460                                         conn.DataSource, "SqlCommand", 0);\r
 461                         }
 462
 463                         return obj;
 464                 }
 465
 466                 [MonoTODO]
 467                 public XmlReader ExecuteXmlReader () {
 468                         throw new NotImplementedException ();
 469                 }
 470
 471                 [MonoTODO]
 472                 public void Prepare () {
 473                         // FIXME: parameters have to be implemented for this
 474                         throw new NotImplementedException ();
 475                 }
 476
 477                 [MonoTODO]
 478                 public SqlCommand Clone () {
 479                         throw new NotImplementedException ();
 480                 }
 481
 482                 #endregion // Methods
 483
 484                 #region Properties
 485
 486                 public string CommandText {
 487                         get {
 488                                 return sql;
 489                         }
 490
 491                         set {
 492                                 sql = value;
 493                         }
 494                 }
 495
 496                 public int CommandTimeout {
 497                         get {
 498                                 return timeout;
 499                         }
 500
 501                         set {
 502                                 // FIXME: if value < 0, throw
 503                                 // ArgumentException
 504                                 // if (value < 0)
 505                                 //      throw ArgumentException;
 506                                 timeout = value;
 507                         }
 508                 }
 509
 510                 public CommandType CommandType  {
 511                         get {
 512                                 return cmdType;
 513                         }
 514
 515                         set {
 516                                 cmdType = value;
 517                         }
 518                 }
 519
 520                 // FIXME: for property Connection, is this the correct
 521                 //        way to handle a return of a stronger type?
 522                 IDbConnection IDbCommand.Connection {
 523                         get {
 524                                 return Connection;
 525                         }
 526
 527                         set {
 528                                 // FIXME: throw an InvalidOperationException
 529                                 // if the change was during a
 530                                 // transaction in progress
 531
 532                                 // csc
 533                                 Connection = (SqlConnection) value;
 534                                 // mcs
 535                                 // Connection = value;
 536
 537                                 // FIXME: set Transaction property to null
 538                         }
 539                 }
 540
 541                 public SqlConnection Connection {
 542                         get {
 543                                 // conn defaults to null
 544                                 return conn;
 545                         }
 546
 547                         set {
 548                                 // FIXME: throw an InvalidOperationException
 549                                 // if the change was during
 550                                 // a transaction in progress
 551                                 conn = value;
 552                                 // FIXME: set Transaction property to null
 553                         }
 554                 }
 555
 556                 public bool DesignTimeVisible {
 557                         get {
 558                                 return designTime;
 559                         }
 560
 561                         set{
 562                                 designTime = value;
 563                         }
 564                 }
 565
 566                 // FIXME; for property Parameters, is this the correct
 567                 //        way to handle a stronger return type?
 568                 IDataParameterCollection IDbCommand.Parameters  {
 569                         get {
 570                                 return Parameters;
 571                         }
 572                 }
 573
 574                 public SqlParameterCollection Parameters {
 575                         get {
 576                                 return parmCollection;
 577                         }
 578                 }
 579
 580                 // FIXME: for property Transaction, is this the correct
 581                 //        way to handle a return of a stronger type?
 582                 IDbTransaction IDbCommand.Transaction   {
 583                         get {
 584                                 return Transaction;
 585                         }
 586
 587                         set {
 588                                 // FIXME: error handling - do not allow
 589                                 // setting of transaction if transaction
 590                                 // has already begun
 591
 592                                 // csc
 593                                 Transaction = (SqlTransaction) value;
 594                                 // mcs
 595                                 // Transaction = value;
 596                         }
 597                 }
 598
 599                 public SqlTransaction Transaction {
 600                         get {
 601                                 return trans;
 602                         }
 603
 604                         set {
 605                                 // FIXME: error handling
 606                                 trans = value;
 607                         }
 608                 }
 609
 610                 [MonoTODO]
 611                 public UpdateRowSource UpdatedRowSource {
 612                         // FIXME: do this once DbDataAdaptor
 613                         // and DataRow are done
 614                         get {
 615                                 throw new NotImplementedException ();
 616                         }
 617                         set {
 618                                 throw new NotImplementedException ();
 619                         }
 620                 }
 621
 622                 #endregion // Properties
 623
 624                 #region Inner Classes
 625
 626                 #endregion // Inner Classes
 627
 628                 #region Destructors
 629
 630                 [MonoTODO]
 631                 public void Dispose() {
 632                         // FIXME: need proper way to release resources
 633                         // Dispose(true);
 634                 }
 635
 636                 [MonoTODO]
 637                 ~SqlCommand() {
 638                         // FIXME: need proper way to release resources
 639                         // Dispose(false);
 640                 }
 641
 642                 #endregion //Destructors
 643         }
 644
 645         // SqlResult is used for passing Result Set data
 646         // from SqlCommand to SqlDataReader
 647         internal class SqlResult {
 648
 649                 private DataTable dataTableSchema; // only will contain the schema
 650                 private IntPtr pg_result; // native PostgreSQL PGresult
 651                 private int rowCount;
 652                 private int fieldCount;
 653                 private string[] pgtypes; // PostgreSQL types (typname)
 654                 private bool resultReturned = false;
 655                 private SqlConnection con;
 656
 657                 internal SqlConnection Connection {
 658                         set {
 659                                 con = value;
 660                         }
 661                 }
 662
 663                 internal bool ResultReturned {
 664                         get {
 665                                 return resultReturned;
 666                         }
 667                         set {
 668                                 resultReturned = value;
 669                         }
 670                 }
 671
 672                 internal DataTable Table {
 673                         get {
 674                                 return dataTableSchema;
 675                         }
 676                 }
 677
 678                 internal IntPtr PgResult {
 679                         get {
 680                                 return pg_result;
 681                         }
 682                 }
 683
 684                 internal int RowCount {
 685                         get {
 686                                 return rowCount;
 687                         }
 688                 }
 689
 690                 internal int FieldCount {
 691                         get {
 692                                 return fieldCount;
 693                         }
 694                 }
 695
 696                 internal string[] PgTypes {
 697                         get {
 698                                 return pgtypes;
 699                         }
 700                 }
 701
 702                 internal void BuildTableSchema (IntPtr pgResult) {
 703                         pg_result = pgResult;
 704
 705                         int nCol;
 706
 707                         dataTableSchema = new DataTable();
 708
 709                         rowCount = PostgresLibrary.
 710                                 PQntuples(pgResult);
 711
 712                         fieldCount = PostgresLibrary.
 713                                 PQnfields(pgResult);
 714
 715                         int oid;
 716                         pgtypes = new string[fieldCount];
 717
 718                         for(nCol = 0; nCol < fieldCount; nCol++) {
 719
 720                                 DbType dbType;
 721
 722                                 // get column name
 723                                 String fieldName;
 724                                 fieldName = PostgresLibrary.
 725                                         PQfname(pgResult, nCol);
 726
 727                                 // get PostgreSQL data type (OID)
 728                                 oid = PostgresLibrary.
 729                                         PQftype(pgResult, nCol);
 730                                 pgtypes[nCol] = PostgresHelper.
 731                                         OidToTypname (oid, con.Types);
 732
 733                                 int definedSize;
 734                                 // get defined size of column
 735                                 definedSize = PostgresLibrary.
 736                                         PQfsize(pgResult, nCol);
 737
 738                                 // build the data column and add it the table
 739                                 DataColumn dc = new DataColumn(fieldName);
 740
 741                                 dbType = PostgresHelper.
 742                                         TypnameToSqlDbType(pgtypes[nCol]);
 743                                 dc.DataType = PostgresHelper.
 744                                         DbTypeToSystemType(dbType);
 745                                 dc.MaxLength = definedSize;
 746                                 dc.SetTable(dataTableSchema);
 747
 748                                 dataTableSchema.Columns.Add(dc);
 749                         }
 750                 }
 751         }
 752 }