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