mcs/class/System.Data/Test/ReadPostgresData.cs

   1 //
   2 // ReadPostgresData.cs
   3 //
   4 // Uses the PostgresLibrary to retrieve a recordset.
   5 // This is not meant to be used in Production, but as a
   6 // learning aid in coding class System.Data.SqlClient.SqlDataReader.
   7 //
   8 // Author:
   9 //      Daniel Morgan <danmorg@sc.rr.com>
  10 //
  11 // (C) 2002 Daniel Morgan
  12 //
  13
  14 using System;
  15 using System.Data;
  16 using System.Runtime.InteropServices;
  17 using System.Diagnostics;
  18
  19 namespace LearnToCreateSqlDataReader
  20 {
  21         sealed public class PostgresLibrary {
  22
  23                 public enum ConnStatusType {
  24                         CONNECTION_OK,
  25                         CONNECTION_BAD,
  26                         CONNECTION_STARTED,
  27                         CONNECTION_MADE,
  28                         CONNECTION_AWAITING_RESPONSE,
  29                         CONNECTION_AUTH_OK,
  30                         CONNECTION_SETENV
  31                 }
  32
  33                 public enum PostgresPollingStatusType {
  34                         PGRES_POLLING_FAILED = 0,
  35                         PGRES_POLLING_READING,
  36                         PGRES_POLLING_WRITING,
  37                         PGRES_POLLING_OK,
  38                         PGRES_POLLING_ACTIVE
  39                 }
  40
  41                 public enum ExecStatusType {
  42                         PGRES_EMPTY_QUERY = 0,
  43                         PGRES_COMMAND_OK,
  44                         PGRES_TUPLES_OK,
  45                         PGRES_COPY_OUT,
  46                         PGRES_COPY_IN,
  47                         PGRES_BAD_RESPONSE,
  48                         PGRES_NONFATAL_ERROR,
  49                         PGRES_FATAL_ERROR
  50                 }
  51
  52                 [DllImport("pq")]
  53                 public static extern string PQerrorMessage (IntPtr conn);
  54                 // char *PQerrorMessage(const PGconn *conn);
  55
  56                 [DllImport("pq")]
  57                 public static extern IntPtr PQconnectdb(String conninfo);
  58                 // PGconn *PQconnectdb(const char *conninfo)
  59
  60                 [DllImport("pq")]
  61                 public static extern void PQfinish(IntPtr conn);
  62                 // void PQfinish(PGconn *conn)
  63
  64                 [DllImport("pq")]
  65                 public static extern IntPtr PQexec(IntPtr conn,
  66                         String query);
  67                 // PGresult *PQexec(PGconn *conn,       const char *query);
  68
  69                 [DllImport("pq")]
  70                 public static extern int PQntuples (IntPtr res);
  71                 // int PQntuples(const PGresult *res);
  72
  73                 [DllImport("pq")]
  74                 public static extern int PQnfields (IntPtr res);
  75                 // int PQnfields(const PGresult *res);
  76
  77                 [DllImport("pq")]
  78                 public static extern ConnStatusType PQstatus (IntPtr conn);
  79                 // ConnStatusType PQstatus(const PGconn *conn);
  80                 [DllImport("pq")]
  81                 public static extern ExecStatusType PQresultStatus (IntPtr res);
  82                 // ExecStatusType PQresultStatus(const PGresult *res);
  83
  84                 [DllImport("pq")]
  85                 public static extern string PQresStatus (ExecStatusType status);
  86                 // char *PQresStatus(ExecStatusType status);
  87
  88                 [DllImport("pq")]
  89                 public static extern string PQresultErrorMessage (IntPtr res);
  90                 // char *PQresultErrorMessage(const PGresult *res);
  91
  92                 [DllImport("pq")]
  93                 public static extern int PQbinaryTuples (IntPtr res);
  94                 // int PQbinaryTuples(const PGresult *res);
  95
  96                 [DllImport("pq")]
  97                 public static extern string PQfname (IntPtr res,
  98                         int field_num);
  99                 // char *PQfname(const PGresult *res,
 100                 //      int field_num);
 101
 102                 [DllImport("pq")]
 103                 public static extern int PQfnumber (IntPtr res,
 104                         string field_name);
 105                 // int PQfnumber(const PGresult *res,
 106                 //      const char *field_name);
 107
 108
 109                 [DllImport("pq")]
 110                 public static extern int PQfmod (IntPtr res, int field_num);
 111                 // int PQfmod(const PGresult *res, int field_num);
 112
 113                 [DllImport("pq")]
 114                 public static extern int PQftype (IntPtr res,
 115                         int field_num);
 116                 // Oid PQftype(const PGresult *res,
 117                 //      int field_num);
 118
 119                 [DllImport("pq")]
 120                 public static extern int PQfsize (IntPtr res,
 121                         int field_num);
 122                 // int PQfsize(const PGresult *res,
 123                 //      int field_num);
 124
 125                 [DllImport("pq")]
 126                 public static extern string PQcmdStatus (IntPtr res);
 127                 // char *PQcmdStatus(PGresult *res);
 128
 129                 [DllImport("pq")]
 130                 public static extern string PQoidStatus (IntPtr res);
 131                 // char *PQoidStatus(const PGresult *res);
 132
 133                 [DllImport("pq")]
 134                 public static extern int PQoidValue (IntPtr res);
 135                 // Oid PQoidValue(const PGresult *res);
 136
 137                 [DllImport("pq")]
 138                 public static extern string PQcmdTuples (IntPtr res);
 139                 // char *PQcmdTuples(PGresult *res);
 140
 141                 [DllImport("pq")]
 142                 public static extern string PQgetvalue (IntPtr res,
 143                         int tup_num, int field_num);
 144                 // char *PQgetvalue(const PGresult *res,
 145                 //      int tup_num, int field_num);
 146
 147                 [DllImport("pq")]
 148                 public static extern int PQgetlength (IntPtr res,
 149                         int tup_num, int field_num);
 150                 // int PQgetlength(const PGresult *res,
 151                 //      int tup_num, int field_num);
 152
 153                 [DllImport("pq")]
 154                 public static extern int PQgetisnull (IntPtr res,
 155                         int tup_num, int field_num);
 156                 // int PQgetisnull(const PGresult *res,
 157                 //      int tup_num, int field_num);
 158
 159                 [DllImport("pq")]
 160                 public static extern void PQclear (IntPtr res);
 161                 // void PQclear(PGresult *res);
 162
 163
 164         }
 165
 166         public class ReadPostgresData
 167         {
 168
 169                 static void Test() {
 170                         String errorMessage;
 171
 172                         IntPtr pgConn;
 173                         String sConnInfo;
 174                         PostgresLibrary.ConnStatusType connStatus;
 175
 176                         String sQuery;
 177                         IntPtr pgResult;
 178
 179                         sConnInfo = "host=localhost dbname=test user=danmorg password=viewsonic";
 180
 181                         sQuery =
 182                                 "select tid, tdesc " +
 183                                 "from sometable ";
 184
 185                         pgConn = PostgresLibrary.PQconnectdb (sConnInfo);
 186
 187                         connStatus = PostgresLibrary.PQstatus (pgConn);
 188                         if(connStatus ==
 189                                 PostgresLibrary.
 190                                 ConnStatusType.CONNECTION_OK) {
 191
 192                                 Console.WriteLine("CONNECTION_OK");
 193
 194                                 pgResult = PostgresLibrary.PQexec(pgConn, sQuery);
 195
 196                                 PostgresLibrary.ExecStatusType execStatus;
 197
 198                                 execStatus = PostgresLibrary.
 199                                         PQresultStatus (pgResult);
 200
 201                                 if(execStatus ==
 202                                         PostgresLibrary.
 203                                         ExecStatusType.PGRES_TUPLES_OK)
 204                                 {
 205                                         Console.WriteLine("PGRES_TUPLES_OK");
 206
 207                                         int nRows = PostgresLibrary.
 208                                                 PQntuples(pgResult);
 209                                         Console.WriteLine("Rows: " + nRows);
 210
 211                                         int nFields = PostgresLibrary.
 212                                                 PQnfields(pgResult);
 213                                         Console.WriteLine("Columns: " + nFields);
 214
 215
 216                                         String fieldName;
 217
 218                                         // get meta data fromm result set (schema)
 219                                         // for each column (field)
 220                                         for(int fieldIndex = 0;
 221                                                 fieldIndex < nFields;
 222                                                 fieldIndex ++) {
 223
 224                                                 // get column name
 225                                                 fieldName = PostgresLibrary.
 226                                                         PQfname(pgResult, fieldIndex);
 227
 228                                                 Console.WriteLine("Field " +
 229                                                         fieldIndex + ": " +
 230                                                         fieldName);
 231
 232                                                 int oid;
 233                                                 // get PostgreSQL data type (OID)
 234                                                 oid = PostgresLibrary.
 235                                                         PQftype(pgResult, fieldIndex);
 236
 237                                                 int definedSize;
 238                                                 // get defined size of column
 239                                                 definedSize = PostgresLibrary.
 240                                                         PQfsize(pgResult, fieldIndex);
 241                                         }
 242
 243                                         // for each row and column, get the data value
 244                                         for(int row = 0;
 245                                                 row < nRows;
 246                                                 row++) {
 247
 248                                                 for(int col = 0;
 249                                                         col < nFields;
 250                                                         col++) {
 251
 252                                                         String value;
 253                                                         // get data value
 254                                                         value = PostgresLibrary.
 255                                                                         PQgetvalue(
 256                                                                                 pgResult,
 257                                                                                 row, col);
 258
 259                                                         Console.WriteLine("Row: " + row +
 260                                                                         " Col: " + col +
 261                                                                         " " + value);
 262
 263                                                         int columnIsNull;
 264                                                         // is column NULL?
 265                                                         columnIsNull = PostgresLibrary.
 266                                                                 PQgetisnull(pgResult,
 267                                                                         row, col);
 268
 269                                                         int actualLength;
 270                                                         actualLength = PostgresLibrary.
 271                                                                 PQgetlength(pgResult,
 272                                                                         row, col);
 273                                                 }
 274                                         }
 275
 276                                         // close result set
 277                                         PostgresLibrary.PQclear (pgResult);
 278                                 }
 279                                 else {
 280                                         // display connection error
 281                                         errorMessage = PostgresLibrary.
 282                                                 PQresStatus(execStatus);
 283
 284                                         errorMessage += " " + PostgresLibrary.
 285                                                 PQresultErrorMessage(pgResult);
 286
 287                                         Console.WriteLine(errorMessage);
 288                                 }
 289
 290                                 // close database conneciton
 291                                 PostgresLibrary.PQfinish(pgConn);
 292
 293                         }
 294                         else {
 295                                 errorMessage = PostgresLibrary.
 296                                         PQerrorMessage (pgConn);
 297                                 errorMessage += ": Could not connect to database.";
 298                                 Console.WriteLine(errorMessage);
 299                         }
 300
 301
 302                 }
 303
 304                 [STAThread]
 305                 static void Main(string[] args)
 306                 {
 307                         Test();
 308                 }
 309         }
 310 }