Java API By Example, From Geeks To Geeks.

1 /* 2  * Copyright (c) 1998-2006 Caucho Technology -- all rights reserved 3  * 4  * This file is part of Resin(R) Open Source 5  * 6  * Each copy or derived work must preserve the copyright notice and this 7  * notice unmodified. 8  * 9  * Resin Open Source is free software; you can redistribute it and/or modify 10  * it under the terms of the GNU General Public License as published by 11  * the Free Software Foundation; either version 2 of the License, or 12  * (at your option) any later version. 13  * 14  * Resin Open Source is distributed in the hope that it will be useful, 15  * but WITHOUT ANY WARRANTY; without even the implied warranty of 16  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE, or any warranty 17  * of NON-INFRINGEMENT. See the GNU General Public License for more 18  * details. 19  * 20  * You should have received a copy of the GNU General Public License 21  * along with Resin Open Source; if not, write to the 22  * 23  * Free Software Foundation, Inc. 24  * 59 Temple Place, Suite 330 25  * Boston, MA 02111-1307 USA 26  * 27  * @author Rodrigo Westrupp 28  */ 29 30 package com.caucho.quercus.lib.db; 31 32 import com.caucho.quercus.UnimplementedException; 33 import com.caucho.quercus.annotation.NotNull; 34 import com.caucho.quercus.annotation.Optional; 35 import com.caucho.quercus.annotation.ReturnNullAsFalse; 36 import com.caucho.quercus.env.*; 37 import com.caucho.quercus.module.AbstractQuercusModule; 38 import com.caucho.util.L10N; 39 import com.caucho.util.Log; 40 import com.caucho.vfs.Path; 41 import com.caucho.vfs.ReadStream; 42 import com.caucho.vfs.WriteStream; 43 44 import java.io.InputStream ; 45 import java.io.OutputStream ; 46 import java.lang.reflect.Constructor ; 47 import java.lang.reflect.Method ; 48 import java.sql.Connection ; 49 import java.sql.DatabaseMetaData ; 50 import java.sql.ResultSet ; 51 import java.sql.ResultSetMetaData ; 52 import java.sql.Statement ; 53 import java.sql.Types ; 54 import java.util.Map ; 55 import java.util.logging.Level ; 56 import java.util.logging.Logger ; 57 58 // Do not add new compile dependencies (use reflection instead) 59 // import org.postgresql.largeobject.*; 60 61 62 /** 63  * Quercus postgres routines. 64  */ 65 public class PostgresModule extends AbstractQuercusModule { 66 67   private static final Logger log = Log.open(PostgresModule.class); 68   private static final L10N L = new L10N(PostgresModule.class); 69 70   public static final int PGSQL_ASSOC = 0x01; 71   public static final int PGSQL_NUM = 0x02; 72   public static final int PGSQL_BOTH = 0x03; 73   public static final int PGSQL_CONNECT_FORCE_NEW = 0x04; 74   public static final int PGSQL_CONNECTION_BAD = 0x05; 75   public static final int PGSQL_CONNECTION_OK = 0x06; 76   public static final int PGSQL_SEEK_SET = 0x07; 77   public static final int PGSQL_SEEK_CUR = 0x08; 78   public static final int PGSQL_SEEK_END = 0x09; 79   public static final int PGSQL_EMPTY_QUERY = 0x0A; 80   public static final int PGSQL_COMMAND_OK = 0x0B; 81   public static final int PGSQL_TUPLES_OK = 0x0C; 82   public static final int PGSQL_COPY_OUT = 0x0D; 83   public static final int PGSQL_COPY_IN = 0x0E; 84   public static final int PGSQL_BAD_RESPONSE = 0x0F; 85   public static final int PGSQL_NONFATAL_ERROR = 0x10; 86   public static final int PGSQL_FATAL_ERROR = 0x11; 87   public static final int PGSQL_TRANSACTION_IDLE = 0x12; 88   public static final int PGSQL_TRANSACTION_ACTIVE = 0x13; 89   public static final int PGSQL_TRANSACTION_INTRANS = 0x14; 90   public static final int PGSQL_TRANSACTION_INERROR = 0x15; 91   public static final int PGSQL_TRANSACTION_UNKNOWN = 0x16; 92   public static final int PGSQL_DIAG_SEVERITY = 0x17; 93   public static final int PGSQL_DIAG_SQLSTATE = 0x18; 94   public static final int PGSQL_DIAG_MESSAGE_PRIMARY = 0x19; 95   public static final int PGSQL_DIAG_MESSAGE_DETAIL = 0x20; 96   public static final int PGSQL_DIAG_MESSAGE_HINT = 0x21; 97   public static final int PGSQL_DIAG_STATEMENT_POSITION = 0x22; 98   public static final int PGSQL_DIAG_INTERNAL_POSITION = 0x23; 99   public static final int PGSQL_DIAG_INTERNAL_QUERY = 0x24; 100   public static final int PGSQL_DIAG_CONTEXT = 0x25; 101   public static final int PGSQL_DIAG_SOURCE_FILE = 0x26; 102   public static final int PGSQL_DIAG_SOURCE_LINE = 0x27; 103   public static final int PGSQL_DIAG_SOURCE_FUNCTION = 0x28; 104   public static final int PGSQL_ERRORS_TERSE = 0x29; 105   public static final int PGSQL_ERRORS_DEFAULT = 0x2A; 106   public static final int PGSQL_ERRORS_VERBOSE = 0x2B; 107   public static final int PGSQL_STATUS_LONG = 0x2C; 108   public static final int PGSQL_STATUS_STRING = 0x2D; 109   public static final int PGSQL_CONV_IGNORE_DEFAULT = 0x2E; 110   public static final int PGSQL_CONV_FORCE_NULL = 0x2F; 111 112   /** 113    * Constructor 114    */ 115   public PostgresModule() 116   { 117   } 118 119   /** 120    * Returns true for the postgres extension. 121    */ 122   public String []getLoadedExtensions() 123   { 124     return new String [] { "postgres", "pgsql" }; 125   } 126 127   /** 128    * Returns number of affected records (tuples) 129    */ 130   public static int pg_affected_rows(Env env, 131                                      @NotNull PostgresResult result) 132   { 133     try { 134 135       return result.getAffectedRows(); 136 137     } catch (Exception ex) { 138       log.log(Level.FINE, ex.toString(), ex); 139       return 0; 140     } 141   } 142 143   /** 144    * Cancel an asynchronous query 145    */ 146   public static boolean pg_cancel_query(Env env, 147                                         @NotNull Postgres conn) 148   { 149     try { 150 151       conn.setAsynchronousStatement(null); 152       conn.setAsynchronousResult(null); 153 154       return true; 155 156     } catch (Exception ex) { 157       log.log(Level.FINE, ex.toString(), ex); 158       return false; 159     } 160   } 161 162   /** 163    * Gets the client encoding 164    */ 165   @ReturnNullAsFalse 166   public static String pg_client_encoding(Env env, 167                                           @Optional Postgres conn) 168   { 169     try { 170       if (conn == null) 171         conn = getConnection(env); 172 173       return conn.getClientEncoding(); 174 175     } catch (Exception ex) { 176       log.log(Level.FINE, ex.toString(), ex); 177       return null; 178     } 179   } 180 181   /** 182    * Closes a PostgreSQL connection 183    */ 184   public static boolean pg_close(Env env, 185                                  @Optional Postgres conn) 186   { 187     try { 188       if (conn == null) 189         conn = getConnection(env); 190 191       if (conn != null) { 192 193         if (conn == getConnection(env)) 194           env.removeSpecialValue("caucho.postgres"); 195 196         conn.close(env); 197 198         return true; 199       } 200 201     } catch (Exception ex) { 202       log.log(Level.FINE, ex.toString(), ex); 203     } 204 205     return false; 206   } 207 208   /** 209    * Open a PostgreSQL connection 210    */ 211   @ReturnNullAsFalse 212   public static Postgres pg_connect(Env env, 213                                     String connectionString, 214                                     @Optional int connectionType) 215   { 216     try { 217       String host = "localhost"; 218       int port = 5432; 219       String dbName = ""; 220       String userName = ""; 221       String password = ""; 222 223       String s = connectionString.trim(); 224 225       String sp[]; 226 227       sp = s.split("(host=)"); 228 229       if (sp.length >= 2) 230         host = sp[1].replaceAll("\\s(.*)$", ""); 231 232       sp = s.split("(port=)"); 233 234       if (sp.length >= 2) { 235         String portS = sp[1].replaceAll("\\s(.*)$", ""); 236         try { 237           port = Integer.parseInt(portS); 238         } catch (Exception ex) { 239         } 240       } 241 242       sp = s.split("(dbname=)"); 243 244       if (sp.length >= 2) 245         dbName = sp[1].replaceAll("\\s(.*)$", ""); 246 247       sp = s.split("(user=)"); 248 249       if (sp.length >= 2) 250         userName = sp[1].replaceAll("\\s(.*)$", ""); 251 252       sp = s.split("(password=)"); 253 254       if (sp.length >= 2) 255         password = sp[1].replaceAll("\\s(.*)$", ""); 256 257       String driver = "org.postgresql.Driver"; 258       String url = "jdbc:postgresql://" + host + ":" + port + "/" + dbName; 259 260       Postgres postgres 261         = new Postgres(env, host, userName, password, dbName, port, driver, url); 262 263       if (! postgres.isConnected()) 264         return null; 265 266       env.setSpecialValue("caucho.postgres", postgres); 267 268       return postgres; 269 270     } catch (Exception ex) { 271       log.log(Level.FINE, ex.toString(), ex); 272       return null; 273     } 274   } 275 276   /** 277    * Get connection is busy or not 278    */ 279   public static boolean pg_connection_busy(Env env, 280                                            @NotNull Postgres conn) 281   { 282     // Always return false, for now (pg_send_xxxx are not asynchronous) 283 // so there should be no reason for a connection to become busy in 284 // between different pg_xxx calls. 285 286     return false; 287   } 288 289   /** 290    * Reset connection (reconnect) 291    */ 292   public static boolean pg_connection_reset(Env env, 293                                             @NotNull Postgres conn) 294   { 295     try { 296 297       conn.close(env); 298 299       conn = new Postgres(env, 300                           conn.getHost(), 301                           conn.getUserName(), 302                           conn.getPassword(), 303                           conn.getDbName(), 304                           conn.getPort(), 305                           conn.getDriver(), 306                           conn.getUrl()); 307 308       env.setSpecialValue("caucho.postgres", conn); 309 310       return true; 311 312     } catch (Exception ex) { 313       log.log(Level.FINE, ex.toString(), ex); 314       return false; 315     } 316   } 317 318   /** 319    * Get connection status 320    */ 321   public static int pg_connection_status(Env env, 322                                          @NotNull Postgres conn) 323   { 324     try { 325 326       boolean ping = pg_ping(env, conn); 327 328       return ping ? PGSQL_CONNECTION_OK : PGSQL_CONNECTION_BAD; 329 330     } catch (Exception ex) { 331       log.log(Level.FINE, ex.toString(), ex); 332       return PGSQL_CONNECTION_BAD; 333     } 334   } 335 336   /** 337    * Convert associative array values into suitable for SQL statement 338    */ 339   @ReturnNullAsFalse 340   public static ArrayValue pg_convert(Env env, 341                                       @NotNull Postgres conn, 342                                       String tableName, 343                                       ArrayValue assocArray, 344                                       @Optional("0") int options) 345   { 346     try { 347 348       // XXX: options has not been implemented yet. 349 350       // XXX: the following PHP note has not been implemented yet. 351 // Note: If there are boolean fields in table_name don't use 352 // the constant TRUE in assoc_array. It will be converted to the 353 // string 'TRUE' which is no valid entry for boolean fields in 354 // PostgreSQL. Use one of t, true, 1, y, yes instead. 355 356       if (options > 0) { 357         throw new UnimplementedException("pg_convert with options"); 358       } 359 360       PostgresResult result; 361 362       Connection jdbcConn = conn.getJavaConnection(); 363       DatabaseMetaData dbMetaData = jdbcConn.getMetaData(); 364 365       ResultSet rs = dbMetaData.getColumns("", "", tableName, ""); 366 367       // Check column count 368 ResultSetMetaData rsMetaData = rs.getMetaData(); 369       int n = rsMetaData.getColumnCount(); 370       if (n < assocArray.getSize()) 371         return null; 372 373       ArrayValueImpl newArray = new ArrayValueImpl(); 374 375       // Keep track of column matches: assocArray vs. table columns 376 int matches = 0; 377 378       while (rs.next()) { 379         // Retrieve the original value to be converted 380 String columnName = rs.getString("COLUMN_NAME"); 381         Value columnNameV = StringValue.create(columnName); 382         Value value = assocArray.get(columnNameV); 383 384         // Check for column not passed in 385 if (value == UnsetValue.UNSET) 386           continue; 387 388         matches++; 389 390         if (value.isNull()) { 391           value = StringValue.create("NULL"); 392           // Add the converted value 393 newArray.put(columnNameV, value); 394           continue; 395         } 396 397         // Retrieve the database column type 398 int dataType = rs.getInt("DATA_TYPE"); 399 400         // Convert the original value to the database type 401 switch (dataType) { 402         case Types.BIT: 403         case Types.TINYINT: 404         case Types.SMALLINT: 405         case Types.INTEGER: 406         case Types.BIGINT: 407           if (value.isLongConvertible()) { 408             value = LongValue.create(value.toLong()); 409           } else { 410             StringBuilderValue sb = new StringBuilderValue(); 411             value = sb.append("'").append(value).append("'"); 412           } 413           break; 414 415         case Types.DECIMAL: 416         case Types.DOUBLE: 417         case Types.FLOAT: 418         case Types.NUMERIC: 419         case Types.REAL: 420           if (value.isDoubleConvertible()) { 421             value = DoubleValue.create(value.toDouble()); 422           } else { 423             StringBuilderValue sb = new StringBuilderValue(); 424             value = sb.append("'").append(value).append("'"); 425           } 426           break; 427 428         default: 429           StringBuilderValue sb = new StringBuilderValue(); 430           if (value.isNumberConvertible()) { 431             value = sb.append(value); 432           } else { 433             value = sb.append("'").append(value).append("'"); 434           } 435         } 436 437         // Add the converted value 438 newArray.put(columnNameV, value); 439       } 440 441       rs.close(); 442 443       // Check if all columns were consumed. Otherwise, there are 444 // wrong column names passed in. 445 if (matches < assocArray.getSize()) { 446         return null; 447       } 448 449       return newArray; 450 451     } catch (Exception ex) { 452       log.log(Level.FINE, ex.toString(), ex); 453       return null; 454     } 455   } 456 457   /** 458    * Insert records into a table from an array 459    */ 460   public static boolean pg_copy_from(Env env, 461                                      @NotNull Postgres conn, 462                                      String tableName, 463                                      ArrayValue rows, 464                                      @Optional("") String delimiter, 465                                      @Optional("") String nullAs) 466   { 467     try { 468 469       // XXX: At the time this was implemented, the JDBC driver 470 // did not support SQL COPY operations that could simplify 471 // the code below. 472 473       String delimiterRegex; 474       if (delimiter.equals("")) { 475         delimiter = "\t"; 476         delimiterRegex = "\\t"; 477       } else { 478         // XXX: even the native php version does not seem to do it very well. 479 throw new UnimplementedException("pg_copy_from with non-default delimiter"); 480       } 481 482       if (nullAs.equals("")) { 483         nullAs = "\\N"; 484       } else { 485         // XXX: even the native php version does not seem to do it very well. 486 throw new UnimplementedException("pg_copy_from with non-default nullAs"); 487       } 488 489       ArrayValueImpl array = (ArrayValueImpl) rows; 490       int size = array.size(); 491 492       String baseInsert = "INSERT INTO " + tableName + " VALUES("; 493 494       StringBuilder sb = new StringBuilder (baseInsert); 495 496       int lenBaseInsert = sb.length(); 497 498       for (int i=0; i<size; i++) { 499         // Every line has a new-line '\n' character and 500 // possibly many NULL values "\\N". Ex: 501 // line = "\\N\tNUMBER1col\t\\N\t\\N\tNUMBER2col\tNUMBER3col\tNUMBER4col\t\\N\n"; 502 String line = array.get(LongValue.create(i)).toString(); 503         line = line.substring(0, line.length()-1); 504 505         // "INSERT INTO " + tableName + " VALUES(" 506 sb.setLength(lenBaseInsert); 507 508         // Split columns 509 String cols[] = line.split(delimiterRegex); 510 511         int len = cols.length; 512 513         if (len > 0) { 514 515           len--; 516 517           for (int j=0; j<len; j++) { 518             if (cols[j].equals(nullAs)) { 519               sb.append("NULL, "); 520             } else { 521               sb.append("'"); 522               sb.append(cols[j]); 523               sb.append("', "); 524             } 525           } 526 527           if (cols[len].equals(nullAs)) { 528             sb.append("NULL)"); 529           } else { 530             sb.append("'"); 531             sb.append(cols[len]); 532             sb.append("')"); 533           } 534 535           // Insert record 536 pg_query(env, conn, sb.toString()); 537         } 538       } 539 540       return true; 541 542     } catch (Exception ex) { 543       log.log(Level.FINE, ex.toString(), ex); 544       return false; 545     } 546   } 547 548   /** 549    * Copy a table to an array 550    */ 551   @ReturnNullAsFalse 552   public static ArrayValue pg_copy_to(Env env, 553                                       @NotNull Postgres conn, 554                                       String tableName, 555                                       @Optional("") String delimiter, 556                                       @Optional("") String nullAs) 557   { 558     try { 559 560       // XXX: At the time this was implemented, the JDBC driver 561 // did not support SQL COPY operations that could simplify 562 // the code below. 563 564       // XXX: This should be replaced when @Optional("\t") is fixed. 565 if (delimiter.equals("")) { 566         delimiter = "\t"; 567       } 568 569       // XXX: This should be replaced when @Optional("\\N") is fixed. 570 // Note: according to php.net, it must be \\N, i.e. the 571 // two-character sequence: {'\\', 'N'} 572 if (nullAs.equals("")) { 573         nullAs = "\\N"; 574       } 575 576       PostgresResult result = pg_query(env, conn, "SELECT * FROM " + tableName); 577 578       ArrayValueImpl newArray = new ArrayValueImpl(); 579 580       Object value; 581 582       int curr = 0; 583 584       while ((value = result.fetchArray(env, PGSQL_NUM)) != null) { 585 586         ArrayValueImpl arr = (ArrayValueImpl) value; 587         int count = arr.size(); 588 589         StringBuilderValue sb = new StringBuilderValue(); 590 591         LongValue currValue = LongValue.create(curr); 592 593         for (int i=0; i<count; i++) { 594 595           if (sb.length() > 0) 596             sb.append(delimiter); 597 598           Value v = newArray.get(currValue); 599 600           Value fieldValue = arr.get(LongValue.create(i)); 601 602           if (fieldValue instanceof NullValue) { 603             sb.append(nullAs); 604           } else { 605             sb.append(fieldValue.toString()); 606           } 607         } 608 609         // Every line has a new-line character. 610 sb.append("\n"); 611 612         newArray.put(currValue, sb); 613 614         curr++; 615       } 616 617       return newArray; 618 619     } catch (Exception ex) { 620       log.log(Level.FINE, ex.toString(), ex); 621       return null; 622     } 623   } 624 625   /** 626    * Get the database name 627    */ 628   @ReturnNullAsFalse 629   public static String pg_dbname(Env env, 630                                  @Optional Postgres conn) 631   { 632     try { 633       if (conn == null) 634         conn = getConnection(env); 635 636       return conn.getDbName(); 637 638     } catch (Exception ex) { 639       log.log(Level.FINE, ex.toString(), ex); 640       return null; 641     } 642   } 643 644   /** 645    * Deletes records 646    */ 647   public static boolean pg_delete(Env env, 648                                   @NotNull Postgres conn, 649                                   String tableName, 650                                   ArrayValue assocArray, 651                                   @Optional("-1") int options) 652   { 653     // From php.net: this function is EXPERIMENTAL. 654 // This function is EXPERIMENTAL. The behaviour of this function, 655 // the name of this function, and anything else documented about this function 656 // may change without notice in a future release of PHP. 657 // Use this function at your own risk. 658 659     try { 660 661       if (options > 0) { 662         throw new UnimplementedException("pg_delete with options"); 663       } 664 665       StringBuilder condition = new StringBuilder (); 666 667       boolean isFirst = true; 668 669       for (Map.Entry <Value,Value> entry : assocArray.entrySet()) { 670         Value k = entry.getKey(); 671         Value v = entry.getValue(); 672         if (isFirst) { 673           isFirst = false; 674         } else { 675           condition.append(" AND "); 676         } 677         condition.append(k.toString()); 678         condition.append("='"); 679         condition.append(v.toString()); 680         condition.append("'"); 681       } 682 683       StringBuilder query = new StringBuilder (); 684       query.append("DELETE FROM "); 685       query.append(tableName); 686       query.append(" WHERE "); 687       query.append(condition); 688 689       pg_query(env, conn, query.toString()); 690 691       return true; 692 693     } catch (Exception ex) { 694       log.log(Level.FINE, ex.toString(), ex); 695       return false; 696     } 697   } 698 699   /** 700    * Sync with PostgreSQL backend 701    */ 702   public static boolean pg_end_copy(Env env, 703                                     @Optional Postgres conn) 704   { 705     env.stub("pg_end_copy"); 706 707     return false; 708   } 709 710   /** 711    * Escape a string for insertion into a bytea field 712    */ 713   @ReturnNullAsFalse 714   public static StringValue pg_escape_bytea(Env env, 715                                             InputStream is) 716   { 717     try { 718 719       Postgres conn = getConnection(env); 720 721       if (conn == null) 722         return null; 723 724       BinaryBuilderValue binaryBuilder = new BinaryBuilderValue(); 725 726       int nbytes; 727       byte buffer[] = new byte[128]; 728       while ((nbytes = is.read(buffer, 0, 128)) > 0) { 729         binaryBuilder.append(buffer, 0, nbytes); 730       } 731 732       Class cl = Class.forName("org.postgresql.util.PGbytea"); 733 734       Method method = cl.getDeclaredMethod("toPGString", new Class [] {byte[].class}); 735 736       String s = (String ) method.invoke(cl, new Object [] {binaryBuilder.toBytes()}); 737 738       return conn.realEscapeString((StringValue) StringValue.create(s)); 739 740     } catch (Exception ex) { 741       log.log(Level.FINE, ex.toString(), ex); 742       return null; 743     } 744   } 745 746   /** 747    * Escape a string for insertion into a text field 748    */ 749   @ReturnNullAsFalse 750   public static StringValue pg_escape_string(Env env, 751                                              StringValue data) 752   { 753     try { 754 755       Postgres conn = getConnection(env); 756 757       if (conn == null) 758         return null; 759 760       return conn.realEscapeString(data); 761 762     } catch (Exception ex) { 763       log.log(Level.FINE, ex.toString(), ex); 764       return null; 765     } 766   } 767 768   /** 769    * Sends a request to execute a prepared statement with given parameters, 770    * and waits for the result 771    */ 772   @ReturnNullAsFalse 773   public static PostgresResult pg_execute(Env env, 774                                           @NotNull Postgres conn, 775                                           String stmtName, 776                                           ArrayValue params) 777   { 778     try { 779 780       PostgresStatement pstmt = conn.getStatement(stmtName); 781 782       return executeInternal(env, conn, pstmt, params); 783 784     } catch (Exception ex) { 785       log.log(Level.FINE, ex.toString(), ex); 786       conn.setResultResource(null); 787       return null; 788     } 789   } 790 791   /** 792    * Fetches all rows in a particular result column as an array 793    */ 794   @ReturnNullAsFalse 795   public static ArrayValue pg_fetch_all_columns(Env env, 796                                                 @NotNull PostgresResult result, 797                                                 @Optional("0") int column) 798   { 799     try { 800 801       ArrayValueImpl newArray = new ArrayValueImpl(); 802 803       int curr = 0; 804 805       for (ArrayValue row = result.fetchRow(env); 806            row != null; 807            row = result.fetchRow(env)) { 808 809         newArray.put(LongValue.create(curr++), 810                      row.get(LongValue.create(column))); 811 812       } 813 814       if (newArray.getSize() > 0) { 815         return newArray; 816       } 817 818     } catch (Exception ex) { 819       log.log(Level.FINE, ex.toString(), ex); 820     } 821 822     return null; 823   } 824 825   /** 826    * Fetches all rows from a result as an array 827    */ 828   @ReturnNullAsFalse 829   public static ArrayValue pg_fetch_all(Env env, 830                                         @NotNull PostgresResult result) 831   { 832     try { 833 834       ArrayValueImpl newArray = new ArrayValueImpl(); 835 836       int curr = 0; 837 838       for (ArrayValue row = result.fetchAssoc(env); 839            row != null; 840            row = result.fetchAssoc(env)) { 841 842         newArray.put(LongValue.create(curr++), row); 843 844       } 845 846       if (newArray.getSize() > 0) { 847         return newArray; 848       } 849 850     } catch (Exception ex) { 851       log.log(Level.FINE, ex.toString(), ex); 852     } 853 854     return null; 855   } 856 857   /** 858    * Fetch a row as an array 859    */ 860   @ReturnNullAsFalse 861   public static ArrayValue pg_fetch_array(Env env, 862                                           @NotNull PostgresResult result, 863                                           @Optional("-1") Value row, 864                                           @Optional("PGSQL_BOTH") int resultType) 865   { 866     try { 867 868       // NOTE: pg_fetch_array has an interesting memory feature. 869 // Calls to pg_fetch_array usually return the next row for 870 // successive calls. There is an exception though. 871 // The first time a NULL row is passed in, the previously 872 // returned row is returned again. After that, successive 873 // calls return the next row as usual. 874 // We set a flag for this. See PostgresResult and php/4342 875 876       if (row == NullValue.NULL) { 877         if (result.getPassedNullRow()) { 878           result.setPassedNullRow(); 879         } else { 880           // Step the cursor back to the previous position 881 ResultSet rs = result.getResultSet(); 882           rs.previous(); 883         } 884       } 885 886       // NOTE: row is of type Value because row is optional and there is 887 // only one way to specify that 'row' will not be used: 888 // 889 // pg_fetch_array(result, NULL, resultType) 890 // 891 // The resultType will be used above though. 892 // 893 // For such a case, the marshalling code passes row in as NullValue.NULL 894 // If we used 'int row' there would be no way to distinguish row 'zero' 895 // from row 'null'. 896 897       if (result == null) 898         return null; 899 900       if (row.isLongConvertible() && row.toInt() >= 0) { 901         if (!result.seek(env, row.toInt())) { 902           env.warning(L.l("Unable to jump to row {0} on PostgreSQL result", 903                           row.toInt())); 904           return null; 905         } 906       } 907 908       return result.fetchArray(env, resultType); 909 910     } catch (Exception ex) { 911       log.log(Level.FINE, ex.toString(), ex); 912       return null; 913     } 914   } 915 916   /** 917    * Fetch a row as an associative array 918    */ 919   @ReturnNullAsFalse 920   public static ArrayValue pg_fetch_assoc(Env env, 921                                           @NotNull PostgresResult result, 922                                           @Optional("-1") Value row) 923   { 924     try { 925 926       if ((row != null) && (!row.equals(NullValue.NULL)) && (row.toInt() >= 0)) { 927         result.seek(env, row.toInt()); 928       } 929 930       return result.fetchAssoc(env); 931 932     } catch (Exception ex) { 933       log.log(Level.FINE, ex.toString(), ex); 934       return null; 935     } 936   } 937 938   /** 939    * Fetch a row as an object 940    */ 941   public static Value pg_fetch_object(Env env, 942                                       @NotNull PostgresResult result, 943                                       @Optional("-1") Value row, 944                                       @Optional int resultType) 945   { 946     try { 947 948       //@todo use optional resultType 949 if ((row != null) && (!row.equals(NullValue.NULL)) && (row.toInt() >= 0)) { 950         result.seek(env, row.toInt()); 951       } 952 953       return result.fetchObject(env); 954 955     } catch (Exception ex) { 956       log.log(Level.FINE, ex.toString(), ex); 957       return BooleanValue.FALSE; 958     } 959   } 960 961   /** 962    * Returns values from a result resource 963    */ 964   public static Value pg_fetch_result(Env env, 965                                       @NotNull PostgresResult result, 966                                       Value row, 967                                       @Optional("-1") Value fieldNameOrNumber) 968   { 969     try { 970 971       // NOTE: row is of type Value because there is a case where 972 // row is optional. In such a case, the row value passed in 973 // is actually the field number or field name. 974 975       int rowNumber = -1; 976 977       // Handle the case: optional row with mandatory fieldNameOrNumber. 978 if (fieldNameOrNumber.isLongConvertible() && 979           (fieldNameOrNumber.toInt() < 0)) { 980         fieldNameOrNumber = row; 981         rowNumber = -1; 982       } else { 983         rowNumber = row.toInt(); 984       } 985 986       if (rowNumber >= 0) { 987         result.seek(env, rowNumber); 988       } 989 990       Value fetchRow = result.fetchRow(env); 991 992       int fieldNumber = result.getColumnNumber(fieldNameOrNumber, 0); 993 994       return fetchRow.get(LongValue.create(fieldNumber)); 995 996     } catch (Exception ex) { 997       log.log(Level.FINE, ex.toString(), ex); 998       return BooleanValue.FALSE; 999     } 1000  } 1001 1002  /** 1003   * Get a row as an enumerated array 1004   */ 1005  @ReturnNullAsFalse 1006  public static ArrayValue pg_fetch_row(Env env, 1007                                        @NotNull PostgresResult result, 1008                                        @Optional("-1") Value row) 1009  { 1010    try { 1011 1012      if ((row != null) && (!row.equals(NullValue.NULL)) && (row.toInt() >= 0)) { 1013        result.seek(env, row.toInt()); 1014      } 1015 1016      return result.fetchRow(env); 1017 1018    } catch (Exception ex) { 1019      log.log(Level.FINE, ex.toString(), ex); 1020      return null; 1021    } 1022  } 1023 1024  /** 1025   * Test if a field is SQL NULL 1026   */ 1027  @ReturnNullAsFalse 1028  public static LongValue pg_field_is_null(Env env, 1029                                           @NotNull PostgresResult result, 1030                                           Value row, 1031                                           @Optional("-1") Value fieldNameOrNumber) 1032  { 1033    try { 1034 1035      // NOTE: row is of type Value because there is a case where 1036 // row is optional. In such a case, the row value passed in 1037 // is actually the field number or field name. 1038 1039      int rowNumber = -1; 1040 1041      // Handle the case: optional row with mandatory fieldNameOrNumber. 1042 if (fieldNameOrNumber.isLongConvertible() && 1043          (fieldNameOrNumber.toInt() == -1)) { 1044        fieldNameOrNumber = row; 1045        rowNumber = -1; 1046      } else { 1047        rowNumber = row.toInt(); 1048      } 1049 1050      if (rowNumber >= 0) { 1051        if (!result.seek(env, rowNumber)) { 1052          env.warning(L.l("Unable to jump to row {0} on PostgreSQL result", 1053                          rowNumber)); 1054          return null; 1055        } 1056      } 1057 1058      int fieldNumber = result.getColumnNumber(fieldNameOrNumber, 0); 1059 1060      Value field = pg_fetch_result(env, 1061                                    result, 1062                                    LongValue.create(-1), 1063                                    LongValue.create(fieldNumber)); 1064 1065      if ((field == null) || (field == NullValue.NULL)) { 1066        return LongValue.create(1); 1067      } 1068 1069      return LongValue.create(0); 1070 1071    } catch (Exception ex) { 1072      log.log(Level.FINE, ex.toString(), ex); 1073      return null; 1074    } 1075  } 1076 1077  /** 1078   * Returns the name of a field 1079   */ 1080  public static Value pg_field_name(Env env, 1081                                    @NotNull PostgresResult result, 1082                                    int fieldNumber) 1083  { 1084    try { 1085 1086      if (result == null) 1087        return BooleanValue.FALSE; 1088 1089      return result.getFieldName(env, fieldNumber); 1090 1091    } catch (Exception ex) { 1092      log.log(Level.FINE, ex.toString(), ex); 1093      return BooleanValue.FALSE; 1094    } 1095  } 1096 1097  /** 1098   * Returns the field number of the named field 1099   * 1100   * @return the field number (0-based) or -1 on error 1101   */ 1102  public static int pg_field_num(Env env, 1103                                 @NotNull PostgresResult result, 1104                                 String fieldName) 1105  { 1106    try { 1107 1108      return result.getColumnNumber(fieldName); 1109 1110    } catch (Exception ex) { 1111      log.log(Level.FINE, ex.toString(), ex); 1112      return -1; 1113    } 1114  } 1115 1116  /** 1117   * Returns the printed length 1118   */ 1119  public static int pg_field_prtlen(Env env, 1120                                    @NotNull PostgresResult result, 1121                                    Value rowNumber, 1122                                    @Optional("-1") Value fieldNameOrNumber) 1123  { 1124    try { 1125      int row = rowNumber.toInt(); 1126 1127      if (fieldNameOrNumber.toString().equals("-1")) { 1128        fieldNameOrNumber = rowNumber; 1129        row = -1; 1130      } 1131 1132      int fieldNumber = result.getColumnNumber(fieldNameOrNumber, 0); 1133 1134      ResultSetMetaData metaData = result.getMetaData(); 1135      String typeName = metaData.getColumnTypeName(fieldNumber+1); 1136      if (typeName.equals("bool")) { 1137        return 1; 1138      } 1139 1140      Value value = pg_fetch_result(env, 1141                                    result, 1142                                    LongValue.create(row), 1143                                    LongValue.create(fieldNumber)); 1144 1145      // Step the cursor back to the original position 1146 // See php/430p 1147 result.getResultSet().previous(); 1148 1149      int len = value.toString().length(); 1150 1151      // XXX: check this... 1152 // if (typeName.equals("money")) { 1153 // len++; 1154 // } 1155 1156      return len; 1157 1158    } catch (Exception ex) { 1159      log.log(Level.FINE, ex.toString(), ex); 1160      return -1; 1161    } 1162  } 1163 1164  /** 1165   * Returns the internal storage size of the named field 1166   */ 1167  @ReturnNullAsFalse 1168  public static LongValue pg_field_size(Env env, 1169                                        @NotNull PostgresResult result, 1170                                        int fieldNumber) 1171  { 1172    try { 1173 1174      ResultSetMetaData metaData = result.getMetaData(); 1175 1176      fieldNumber++; 1177 1178      int dataType = metaData.getColumnType(fieldNumber); 1179 1180      int size = -1; 1181 1182      switch (dataType) { 1183      case Types.BIT: 1184        { 1185          String typeName = metaData.getColumnTypeName(fieldNumber); 1186          if (typeName.equals("bool")) { 1187            size = 1; 1188          } 1189          break; 1190        } 1191 1192      case Types.TINYINT: 1193        size = 1; 1194        break; 1195 1196      case Types.SMALLINT: 1197        size = 2; 1198        break; 1199 1200      case Types.DATE: 1201      case Types.FLOAT: 1202      case Types.INTEGER: 1203      case Types.REAL: 1204        size = 4; 1205        break; 1206 1207      case Types.BIGINT: 1208      case Types.DOUBLE: 1209        { 1210          size = 8; 1211          String typeName = metaData.getColumnTypeName(fieldNumber); 1212          if (typeName.equals("money")) { 1213            size = 4; 1214          } 1215        } 1216        break; 1217 1218      case Types.TIME: 1219      case Types.TIMESTAMP: 1220        size = 8; 1221        // fall to specific cases 1222 1223      default: 1224        { 1225          String typeName = metaData.getColumnTypeName(fieldNumber); 1226          if (typeName.equals("timetz") || 1227              typeName.equals("interval")) { 1228            size = 12; 1229          } else if (typeName.equals("macaddr")) { 1230            size = 6; 1231          } else if (typeName.equals("point")) { 1232            size = 16; 1233          } else if (typeName.equals("circle")) { 1234            size = 24; 1235          } else if (typeName.equals("box") || 1236                     typeName.equals("lseg")) { 1237            size = 32; 1238          } 1239        } 1240      } 1241 1242      return LongValue.create(size); 1243 1244    } catch (Exception ex) { 1245      log.log(Level.FINE, ex.toString(), ex); 1246      return null; 1247    } 1248  } 1249 1250  /** 1251   * Returns the name or oid of the tables field 1252   * 1253   * @return By default the tables name that field belongs to 1254   * is returned but if oid_only is set to TRUE, 1255   * then the oid will instead be returned. 1256   */ 1257  @ReturnNullAsFalse 1258  public static String pg_field_table(Env env, 1259                                      @NotNull PostgresResult result, 1260                                      int fieldNumber, 1261                                      @Optional("false") boolean oidOnly) 1262  { 1263    // The Postgres JDBC driver doesn't have a concept of exposing to the client 1264 // what table maps to a particular select item in a result set, therefore the 1265 // driver cannot report anything useful to the caller. Thus the driver always 1266 // returns "" to ResultSetMetaData.getTableName(fieldNumber+1) 1267 1268    env.stub("pg_field_table"); 1269 1270    return ""; 1271  } 1272 1273  /** 1274   * Returns the type ID (OID) for the corresponding field number 1275   */ 1276  @ReturnNullAsFalse 1277  public static LongValue pg_field_type_oid(Env env, 1278                                            @NotNull PostgresResult result, 1279                                            int fieldNumber) 1280  { 1281    try { 1282 1283      ResultSetMetaData metaData = result.getMetaData(); 1284 1285      String columnTypeName = metaData.getColumnTypeName(fieldNumber + 1); 1286 1287      String metaQuery = ("SELECT oid FROM pg_type WHERE typname='"+columnTypeName+"'"); 1288 1289      result = pg_query(env, (Postgres) result.getConnection(), metaQuery); 1290 1291      Value value = pg_fetch_result(env, 1292                                    result, 1293                                    LongValue.create(-1), 1294                                    LongValue.create(0)); 1295 1296      if (value.isLongConvertible()) { 1297        return LongValue.create(value.toLong()); 1298      } 1299 1300    } catch (Exception ex) { 1301      log.log(Level.FINE, ex.toString(), ex); 1302    } 1303 1304    return null; 1305  } 1306 1307  /** 1308   * Returns the type name for the corresponding field number 1309   */ 1310  @ReturnNullAsFalse 1311  public static StringValue pg_field_type(Env env, 1312                                          @NotNull PostgresResult result, 1313                                          int fieldNumber) 1314  { 1315    try { 1316 1317      ResultSetMetaData metaData = result.getMetaData(); 1318 1319      fieldNumber++; 1320 1321      String typeName = metaData.getColumnTypeName(fieldNumber); 1322 1323      return (StringValue) StringValue.create(typeName); 1324 1325    } catch (Exception ex) { 1326      log.log(Level.FINE, ex.toString(), ex); 1327      return null; 1328    } 1329  } 1330 1331  /** 1332   * Free result memory 1333   */ 1334  public static boolean pg_free_result(Env env, 1335                                       @NotNull PostgresResult result) 1336  { 1337    try { 1338 1339      result.close(); 1340      return true; 1341 1342    } catch (Exception ex) { 1343      log.log(Level.FINE, ex.toString(), ex); 1344      return false; 1345    } 1346  } 1347 1348  /** 1349   * Gets SQL NOTIFY message 1350   */ 1351  @ReturnNullAsFalse 1352  public static ArrayValue pg_get_notify(Env env, 1353                                         @NotNull Postgres conn, 1354                                         @Optional("-1") int resultType) 1355  { 1356    try { 1357 1358      if (resultType > 0) { 1359        throw new UnimplementedException("pg_get_notify with result type"); 1360      } 1361 1362      // org.postgresql.PGConnection 1363 Class cl = Class.forName("org.postgresql.PGConnection"); 1364 1365      // public PGNotification[] getNotifications() throws SQLException; 1366 Method method = cl.getDeclaredMethod("getNotifications", null); 1367 1368      Connection pgconn = conn.getJavaConnection(); 1369 1370      // getNotifications() 1371 Object notifications[] = (Object []) method.invoke(pgconn, new Object [] {}); 1372 1373      // org.postgresql.PGNotification 1374 cl = Class.forName("org.postgresql.PGNotification"); 1375 1376      // public String getName(); 1377 Method methodGetName = cl.getDeclaredMethod("getName", null); 1378 1379      // public int getPID(); 1380 Method methodGetPID = cl.getDeclaredMethod("getPID", null); 1381 1382      ArrayValueImpl arrayValue = new ArrayValueImpl(); 1383 1384      int n = notifications.length; 1385 1386      StringValue k; 1387      LongValue v; 1388 1389      for (int i=0; i<n; i++) { 1390        // getName() 1391 k = (StringValue) StringValue.create(methodGetName.invoke(notifications[i], 1392                                                                  new Object [] {})); 1393        // getPID() 1394 v = (LongValue) LongValue.create((Integer ) methodGetPID.invoke(notifications[i], 1395                                                                       new Object [] {})); 1396 1397        arrayValue.put(k, v); 1398      } 1399 1400      return arrayValue; 1401 1402    } catch (Exception ex) { 1403      log.log(Level.FINE, ex.toString(), ex); 1404      return null; 1405    } 1406  } 1407 1408  /** 1409   * Gets the backend's process ID 1410   */ 1411  public static int pg_get_pid(Env env, 1412                               @NotNull Postgres conn) 1413  { 1414    try { 1415 1416      // @todo create a random string 1417 String randomLabel = "caucho_pg_get_pid_random_label"; 1418 1419      pg_query(env, conn, "LISTEN "+randomLabel); 1420      pg_query(env, conn, "NOTIFY "+randomLabel); 1421 1422      ArrayValue arrayValue = pg_get_notify(env, conn, -1); 1423 1424      LongValue pid = (LongValue) arrayValue.get(StringValue.create(randomLabel)); 1425 1426      return pid.toInt(); 1427 1428    } catch (Exception ex) { 1429      log.log(Level.FINE, ex.toString(), ex); 1430      return -1; 1431    } 1432  } 1433 1434  /** 1435   * Get asynchronous query result 1436   */ 1437  @ReturnNullAsFalse 1438  public static PostgresResult pg_get_result(Env env, 1439                                             @Optional Postgres conn) 1440  { 1441    // Three different scenarios for pg_get_result: 1442 // 1443 // 1. pg_send_prepare/pg_send_execute - php/431m 1444 // 1445 // pg_send_prepare($conn, "my_query", 'SELECT * FROM test WHERE data = $1'); 1446 // $res1 = pg_get_result($conn); 1447 // 1448 // pg_send_execute($conn, "my_query", array("Joe's Widgets")); 1449 // $res2 = pg_get_result($conn); 1450 // 1451 // pg_send_execute($conn, "my_query", array("Clothes Clothes Clothes")); 1452 // $res3 = pg_get_result($conn); 1453 // 1454 // 2. Multiquery with pg_send_query - php/430y 1455 // 1456 // pg_send_query($conn, "select * from test; select count(*) from test;"); 1457 // 1458 // // select * from test 1459 // $res = pg_get_result($conn); 1460 // $rows = pg_num_rows($res); 1461 // 1462 // // select count(*) from test 1463 // $res = pg_get_result($conn); 1464 // $rows = pg_num_rows($res); 1465 // 1466 // 3. Individual pg_send_query - php/431g 1467 // 1468 // $res = pg_send_query($conn, "select * from test;"); 1469 // var_dump($res); 1470 // $res = pg_get_result($conn); 1471 // var_dump($res); 1472 // 1473 // $res = pg_send_query($conn, "select * from doesnotexist;"); 1474 // var_dump($res); 1475 // $res = pg_get_result($conn); 1476 // var_dump($res); 1477 1478    try { 1479 1480      if (conn == null) 1481        conn = getConnection(env); 1482 1483      PostgresResult result = (PostgresResult) conn.getResultResource(); 1484 1485      // 1. pg_send_prepare/pg_send_execute 1486 if (conn.getAsynchronousStatement() != null) { 1487        if (conn.getAsynchronousResult() != null) { 1488          conn.setAsynchronousResult(null); 1489          return result; 1490        } 1491        return null; 1492      } 1493 1494      // 2. pg_send_query 1495 if (conn.getAsynchronousResult() != null) { 1496 1497        // Check for next result 1498 // Ex: pg_send_query($conn, "select * from test; select count(*) from test;"); 1499 1500        Statement stmt = result.getJavaStatement(); 1501 1502        if (stmt.getMoreResults()) { 1503          result = (PostgresResult) conn.createResult(stmt, stmt.getResultSet()); 1504        } else { 1505          // 3. Individual pg_send_query (clean up; no futher results) 1506 conn.setResultResource(null); 1507        } 1508      } 1509 1510      conn.setAsynchronousResult(result); 1511 1512      return result; 1513 1514    } catch (Exception ex) { 1515      log.log(Level.FINE, ex.toString(), ex); 1516      return null; 1517    } 1518  } 1519 1520  /** 1521   * Returns the host name associated with the connection 1522   */ 1523  @ReturnNullAsFalse 1524  public static String pg_host(Env env, 1525                               @Optional Postgres conn) 1526  { 1527    try { 1528 1529      if (conn == null) 1530        conn = getConnection(env); 1531 1532      return conn.getHost(); 1533 1534    } catch (Exception ex) { 1535      log.log(Level.FINE, ex.toString(), ex); 1536      return null; 1537    } 1538  } 1539 1540  /** 1541   * Insert array into table 1542   */ 1543  public static boolean pg_insert(Env env, 1544                                  @NotNull Postgres conn, 1545                                  String tableName, 1546                                  ArrayValue assocArray, 1547                                  @Optional("-1") int options) 1548  { 1549    try { 1550 1551      if (options > 0) { 1552        throw new UnimplementedException("pg_insert with options"); 1553      } 1554 1555      StringBuilder names = new StringBuilder (); 1556      StringBuilder values = new StringBuilder (); 1557 1558      boolean isFirst = true; 1559 1560      for (Map.Entry <Value,Value> entry : assocArray.entrySet()) { 1561        Value k = entry.getKey(); 1562        Value v = entry.getValue(); 1563        if (isFirst) { 1564          isFirst = false; 1565        } else { 1566          values.append("','"); 1567          names.append(","); 1568        } 1569        values.append(v.toString()); 1570        names.append(k.toString()); 1571      } 1572 1573      StringBuilder query = new StringBuilder (); 1574      query.append("INSERT INTO "); 1575      query.append(tableName); 1576      query.append("("); 1577      query.append(names); 1578      query.append(") VALUES('"); 1579      query.append(values); 1580      query.append("')"); 1581 1582      pg_query(env, conn, query.toString()); 1583 1584      return true; 1585 1586    } catch (Exception ex) { 1587      log.log(Level.FINE, ex.toString(), ex); 1588      return false; 1589    } 1590  } 1591 1592  /** 1593   * Get the last error message string of a connection 1594   */ 1595  @ReturnNullAsFalse 1596  public static String pg_last_error(Env env, 1597                                     @Optional Postgres conn) 1598  { 1599    try { 1600 1601      if (conn == null) 1602        conn = getConnection(env); 1603 1604      return conn.error(); 1605 1606    } catch (Exception ex) { 1607      log.log(Level.FINE, ex.toString(), ex); 1608      return null; 1609    } 1610  } 1611 1612  /** 1613   * Returns the last notice message from PostgreSQL server 1614   */ 1615  @ReturnNullAsFalse 1616  public static String pg_last_notice(Env env, 1617                                      @NotNull Postgres conn) 1618  { 1619    try { 1620 1621      return conn.getWarnings().toString(); 1622 1623    } catch (Exception ex) { 1624      log.log(Level.FINE, ex.toString(), ex); 1625      return null; 1626    } 1627  } 1628 1629  /** 1630   * Returns the last row's OID 1631   * 1632   * Note that: 1633   * - OID is a unique id. It will not work if the table was created with "No oid". 1634   * - MySql's "mysql_insert_id" receives the conection handler as argument but 1635   * PostgreSQL's "pg_last_oid" uses the result handler. 1636   */ 1637  @ReturnNullAsFalse 1638  public static String pg_last_oid(Env env, 1639                                   PostgresResult result) 1640  { 1641    try { 1642 1643      Statement stmt = result.getJavaStatement(); 1644 1645      Class cl = Class.forName("org.postgresql.jdbc2.AbstractJdbc2Statement"); 1646 1647      Method method = cl.getDeclaredMethod("getLastOID", null); 1648 1649      int oid = Integer.parseInt(method.invoke(stmt, new Object [] {}).toString()); 1650 1651      if (oid > 0) 1652        return ""+oid; 1653 1654    } catch (Exception ex) { 1655      log.log(Level.FINE, ex.toString(), ex); 1656    } 1657 1658    return null; 1659  } 1660 1661  /** 1662   * Close a large object 1663   */ 1664  public static boolean pg_lo_close(Env env, 1665                                    Object largeObject) 1666  { 1667    try { 1668 1669      Class cl = Class.forName("org.postgresql.largeobject.LargeObject"); 1670 1671      Method method = cl.getDeclaredMethod("close", null); 1672 1673      method.invoke(largeObject, new Object [] {}); 1674      // largeObject.close(); 1675 1676      return true; 1677 1678    } catch (Exception ex) { 1679      log.log(Level.FINE, ex.toString(), ex); 1680      return false; 1681    } 1682  } 1683 1684  /** 1685   * Create a large object 1686   */ 1687  @ReturnNullAsFalse 1688  public static LongValue pg_lo_create(Env env, 1689                                       @Optional Postgres conn) 1690  { 1691    try { 1692 1693      int oid = -1; 1694 1695      if (conn == null) 1696        conn = getConnection(env); 1697 1698      // LargeObjectManager lobManager; 1699 Object lobManager; 1700 1701      // org.postgresql.PGConnection 1702 Class cl = Class.forName("org.postgresql.PGConnection"); 1703 1704      Method method = cl.getDeclaredMethod("getLargeObjectAPI", null); 1705 1706      Connection pgconn = conn.getJavaConnection(); 1707 1708      // Large Objects may not be used in auto-commit mode. 1709 pgconn.setAutoCommit(false); 1710 1711      lobManager = method.invoke(pgconn, new Object [] {}); 1712      // lobManager = ((org.postgresql.PGConnection)conn).getLargeObjectAPI(); 1713 1714      // org.postgresql.largeobject.LargeObjectManager 1715 cl = Class.forName("org.postgresql.largeobject.LargeObjectManager"); 1716 1717      method = cl.getDeclaredMethod("create", null); 1718 1719      Object oidObj = method.invoke(lobManager, new Object [] {}); 1720 1721      oid = Integer.parseInt(oidObj.toString()); 1722 1723      // oid = lobManager.create(); 1724 1725      return LongValue.create(oid); 1726 1727    } catch (Exception ex) { 1728      log.log(Level.FINE, ex.toString(), ex); 1729      return null; 1730    } 1731  } 1732 1733  /** 1734   * Export a large object to a file 1735   */ 1736  public static boolean pg_lo_export(Env env, 1737                                     @NotNull Postgres conn, 1738                                     int oid, 1739                                     Path path) 1740  { 1741    try { 1742 1743      //@todo conn should be optional 1744 1745      // LargeObjectManager lobManager; 1746 Object lobManager; 1747 1748      //org.postgresql.largeobject.LargeObjectManager 1749 1750      Class cl = Class.forName("org.postgresql.PGConnection"); 1751 1752      Method method = cl.getDeclaredMethod("getLargeObjectAPI", null); 1753 1754      Connection pgconn = conn.getJavaConnection(); 1755 1756      lobManager = method.invoke(pgconn, new Object [] {}); 1757      // lobManager = ((org.postgresql.PGConnection)conn).getLargeObjectAPI(); 1758 1759      cl = Class.forName("org.postgresql.largeobject.LargeObjectManager"); 1760 1761      method = cl.getDeclaredMethod("open", new Class [] {Integer.TYPE}); 1762 1763      Object lobj = method.invoke(lobManager, new Object [] {oid}); 1764 1765      cl = Class.forName("org.postgresql.largeobject.LargeObject"); 1766 1767      method = cl.getDeclaredMethod("getInputStream", null); 1768 1769      Object isObj = method.invoke(lobj, new Object [] {}); 1770 1771      InputStream is = (InputStream )isObj; 1772 1773      // Open the file 1774 WriteStream os = path.openWrite(); 1775 1776      // copy the data from the large object to the file 1777 os.writeStream(is); 1778 1779      os.close(); 1780      is.close(); 1781 1782      // Close the large object 1783 method = cl.getDeclaredMethod("close", null); 1784 1785      method.invoke(lobj, new Object [] {}); 1786 1787      return true; 1788 1789    } catch (Exception ex) { 1790      log.log(Level.FINE, ex.toString(), ex); 1791      return false; 1792    } 1793  } 1794 1795  /** 1796   * Import a large object from file 1797   */ 1798  @ReturnNullAsFalse 1799  public static LongValue pg_lo_import(Env env, 1800                                       @NotNull Postgres conn, 1801                                       Path path) 1802  { 1803    try { 1804 1805      //@todo conn should be optional 1806 1807      LongValue value = pg_lo_create(env, conn); 1808 1809      if (value != null) { 1810 1811        int oid = value.toInt(); 1812        Object largeObject = pg_lo_open(env, conn, oid, "w"); 1813 1814        String data = ""; 1815 1816        // Open the file 1817 ReadStream is = path.openRead(); 1818 1819        writeLobInternal(largeObject, is, Integer.MAX_VALUE); 1820 1821        pg_lo_close(env, largeObject); 1822 1823        is.close(); 1824 1825        return LongValue.create(oid); 1826      } 1827 1828    } catch (Exception ex) { 1829      log.log(Level.FINE, ex.toString(), ex); 1830    } 1831 1832    return null; 1833  } 1834 1835  /** 1836   * Open a large object 1837   */ 1838  @ReturnNullAsFalse 1839  public static Object pg_lo_open(Env env, 1840                                  @NotNull Postgres conn, 1841                                  int oid, 1842                                  String mode) 1843  { 1844    try { 1845 1846      Object largeObject = null; 1847 1848      // LargeObjectManager lobManager; 1849 Object lobManager; 1850 1851      //org.postgresql.largeobject.LargeObjectManager 1852 1853      Class cl = Class.forName("org.postgresql.PGConnection"); 1854 1855      Method method = cl.getDeclaredMethod("getLargeObjectAPI", null); 1856 1857      Connection pgconn = conn.getJavaConnection(); 1858 1859      lobManager = method.invoke(pgconn, new Object [] {}); 1860 1861      cl = Class.forName("org.postgresql.largeobject.LargeObjectManager"); 1862 1863      method = cl.getDeclaredMethod("open", new Class [] {Integer.TYPE, Integer.TYPE}); 1864 1865      boolean write = mode.indexOf("w") >= 0; 1866      boolean read = mode.indexOf("r") >= 0; 1867 1868      int modeREAD = cl.getDeclaredField("READ").getInt(null); 1869      int modeREADWRITE = cl.getDeclaredField("READWRITE").getInt(null); 1870      int modeWRITE = cl.getDeclaredField("WRITE").getInt(null); 1871 1872      int intMode = modeREAD; 1873 1874      if (read) { 1875        if (write) { 1876          intMode = modeREADWRITE; 1877        } 1878      } else if (write) { 1879        intMode = modeWRITE; 1880      } 1881 1882      largeObject = method.invoke(lobManager, new Object [] {oid, intMode}); 1883 1884      return largeObject; 1885 1886    } catch (Exception ex) { 1887      env.warning(L.l("Unable to open PostgreSQL large object")); 1888      log.log(Level.FINE, ex.toString(), ex); 1889      return null; 1890    } 1891  } 1892 1893  /** 1894   * Reads an entire large object and send straight to browser 1895   */ 1896  @ReturnNullAsFalse 1897  public static LongValue pg_lo_read_all(Env env, 1898                                         Object largeObject) 1899  { 1900    try { 1901 1902      String contents = pg_lo_read(env, largeObject, -1); 1903      if (contents != null) { 1904        env.getOut().print(contents); 1905      } 1906 1907    } catch (Exception ex) { 1908      log.log(Level.FINE, ex.toString(), ex); 1909    } 1910 1911    return null; 1912  } 1913 1914  /** 1915   * Read a large object 1916   */ 1917  @ReturnNullAsFalse 1918  public static String pg_lo_read(Env env, 1919                                  Object largeObject, 1920                                  @Optional("-1") int len) 1921  { 1922    try { 1923 1924      if (len < 0) { 1925        len = Integer.MAX_VALUE; 1926      } 1927 1928      Class cl = Class.forName("org.postgresql.largeobject.LargeObject"); 1929 1930      Method method = cl.getDeclaredMethod("getInputStream", null); 1931 1932      InputStream is = (InputStream ) method.invoke(largeObject, new Object [] {}); 1933 1934      BinaryBuilderValue binaryBuilder = new BinaryBuilderValue(); 1935 1936      int nbytes; 1937      byte buffer[] = new byte[128]; 1938      while ((len > 0) && ((nbytes = is.read(buffer, 0, 128)) > 0)) { 1939        if (nbytes > len) { 1940          nbytes = len; 1941        } 1942        binaryBuilder.append(buffer, 0, nbytes); 1943        len -= nbytes; 1944      } 1945 1946      is.close(); 1947 1948      return binaryBuilder.toString(); 1949 1950    } catch (Exception ex) { 1951      log.log(Level.FINE, ex.toString(), ex); 1952      return null; 1953    } 1954  } 1955 1956  /** 1957   * Seeks position within a large object 1958   */ 1959  public static boolean pg_lo_seek(Env env, 1960                                   Object largeObject, 1961                                   int offset, 1962                                   @Optional int whence) 1963  { 1964    try { 1965 1966      Class cl = Class.forName("org.postgresql.largeobject.LargeObject"); 1967 1968      int seekSET = cl.getDeclaredField("SEEK_SET").getInt(null); 1969      int seekEND = cl.getDeclaredField("SEEK_END").getInt(null); 1970      int seekCUR = cl.getDeclaredField("SEEK_CUR").getInt(null); 1971 1972      switch (whence) { 1973      case PGSQL_SEEK_SET: 1974        whence = seekSET; 1975        break; 1976      case PGSQL_SEEK_END: 1977        whence = seekEND; 1978        break; 1979      default: 1980        whence = seekCUR; 1981      } 1982 1983      Method method = cl.getDeclaredMethod("seek", new Class []{Integer.TYPE,Integer.TYPE}); 1984 1985      method.invoke(largeObject, new Object [] {offset, whence}); 1986 1987      return true; 1988 1989    } catch (Exception ex) { 1990      log.log(Level.FINE, ex.toString(), ex); 1991      return false; 1992    } 1993  } 1994 1995  /** 1996   * Returns current seek position a of large object 1997   */ 1998  public static int pg_lo_tell(Env env, 1999                               Object largeObject) 2000  { 2001    try { 2002 2003      Class cl = Class.forName("org.postgresql.largeobject.LargeObject"); 2004 2005      Method method = cl.getDeclaredMethod("tell", null); 2006 2007      Object obj = method.invoke(largeObject, new Object [] {}); 2008 2009      return Integer.parseInt(obj.toString()); 2010 2011    } catch (Exception ex) { 2012      log.log(Level.FINE, ex.toString(), ex); 2013      return -1; 2014    } 2015  } 2016 2017  /** 2018   * Delete a large object 2019   */ 2020  public static boolean pg_lo_unlink(Env env, 2021                                     @NotNull Postgres conn, 2022                                     int oid) 2023  { 2024    try { 2025 2026      // LargeObjectManager lobManager; 2027 Object lobManager; 2028 2029      //org.postgresql.largeobject.LargeObjectManager 2030 2031      Class cl = Class.forName("org.postgresql.PGConnection"); 2032 2033      Method method = cl.getDeclaredMethod("getLargeObjectAPI", null); 2034 2035      Connection pgconn = conn.getJavaConnection(); 2036 2037      lobManager = method.invoke(pgconn, new Object [] {}); 2038 2039      cl = Class.forName("org.postgresql.largeobject.LargeObjectManager"); 2040 2041      method = cl.getDeclaredMethod("unlink", new Class [] {Integer.TYPE}); 2042 2043      method.invoke(lobManager, new Object [] {oid}); 2044 2045      return true; 2046 2047    } catch (Exception ex) { 2048      log.log(Level.FINE, ex.toString(), ex); 2049      return false; 2050    } 2051  } 2052 2053  /** 2054   * Write to a large object 2055   */ 2056  @ReturnNullAsFalse 2057  public static LongValue pg_lo_write(Env env, 2058                                      @NotNull Object largeObject, 2059                                      String data, 2060                                      @Optional int len) 2061  { 2062    try { 2063 2064      if (len <= 0) { 2065        len = data.length(); 2066      } 2067 2068      int written = len; 2069 2070      Class cl = Class.forName("org.postgresql.largeobject.LargeObject"); 2071 2072      Method method = cl.getDeclaredMethod("write", 2073                                           new Class [] {byte[].class, 2074                                                        Integer.TYPE, 2075                                                        Integer.TYPE}); 2076 2077      method.invoke(largeObject, new Object [] {data.getBytes(), 0, len}); 2078 2079      return LongValue.create(written); 2080 2081    } catch (Exception ex) { 2082      log.log(Level.FINE, ex.toString(), ex); 2083      return null; 2084    } 2085  } 2086 2087  /** 2088   * Get meta data for table 2089   */ 2090  @ReturnNullAsFalse 2091  public static ArrayValue pg_meta_data(Env env, 2092                                        @NotNull Postgres conn, 2093                                        String tableName) 2094  { 2095    env.stub("pg_meta_data"); 2096 2097    return null; 2098  } 2099 2100  /** 2101   * Returns the number of fields in a result 2102   */ 2103  public static int pg_num_fields(Env env, 2104                                  @NotNull PostgresResult result) 2105  { 2106    try { 2107 2108      return result.getFieldCount(); 2109 2110    } catch (Exception ex) { 2111      log.log(Level.FINE, ex.toString(), ex); 2112      return -1; 2113    } 2114  } 2115 2116  /** 2117   * Returns the number of rows in a result 2118   */ 2119  public static LongValue pg_num_rows(Env env, 2120                                      @NotNull PostgresResult result) 2121  { 2122    int numRows = -1; 2123 2124    try { 2125 2126      if ((result != null) && (result.getResultSet() != null)) { 2127        numRows = result.getNumRows(); 2128      } 2129 2130      if (numRows < 0) { 2131        env.warning(L.l("supplied argument is not a valid PostgreSQL result resource")); 2132      } 2133 2134    } catch (Exception ex) { 2135      log.log(Level.FINE, ex.toString(), ex); 2136    } 2137 2138    return LongValue.create(numRows); 2139  } 2140 2141  /** 2142   * Get the options associated with the connection 2143   */ 2144  public static String pg_options(Env env, 2145                                  @Optional Postgres conn) 2146  { 2147    throw new UnimplementedException("pg_options"); 2148  } 2149 2150  /** 2151   * Looks up a current parameter setting of the server 2152   */ 2153  public static Value pg_parameter_status(Env env, 2154                                          @NotNull Postgres conn, 2155                                          String paramName) 2156  { 2157    try { 2158 2159      PostgresResult result = pg_query(env, conn, "SHOW "+paramName); 2160 2161      Value value = pg_fetch_result(env, result, LongValue.create(0), LongValue.create(0)); 2162 2163      if ((value == null) || value.isNull()) 2164        return BooleanValue.FALSE; 2165 2166      return value; 2167 2168    } catch (Exception ex) { 2169      log.log(Level.FINE, ex.toString(), ex); 2170      return BooleanValue.FALSE; 2171    } 2172  } 2173 2174  /** 2175   * Open a persistent PostgreSQL connection 2176   */ 2177  @ReturnNullAsFalse 2178  public static Postgres pg_pconnect(Env env, 2179                                     String connectionString, 2180                                     @Optional int connectType) 2181  { 2182    return pg_connect(env, connectionString, connectType); 2183  } 2184 2185  /** 2186   * Ping database connection 2187   */ 2188  public static boolean pg_ping(Env env, 2189                                @Optional Postgres conn) 2190  { 2191    try { 2192 2193      if (conn == null) 2194        conn = getConnection(env); 2195 2196      return pg_query(env, conn, "SELECT 1") != null; 2197 2198    } catch (Exception ex) { 2199      log.log(Level.FINE, ex.toString(), ex); 2200      return false; 2201    } 2202  } 2203 2204  /** 2205   * Return the port number associated with the connection 2206   */ 2207  @ReturnNullAsFalse 2208  public static StringValue pg_port(Env env, 2209                                    @Optional Postgres conn) 2210  { 2211    try { 2212 2213      if (conn == null) 2214        conn = getConnection(env); 2215 2216      // In PHP, a real pg_port test case returns String 2217 2218      return (StringValue) StringValue.create(conn.getPort()); 2219 2220    } catch (Exception ex) { 2221      log.log(Level.FINE, ex.toString(), ex); 2222      return null; 2223    } 2224  } 2225 2226  /** 2227   * Submits a request to create a prepared statement with the given parameters, 2228   * and waits for completion 2229   */ 2230  @ReturnNullAsFalse 2231  public static PostgresStatement pg_prepare(Env env, 2232                                             @NotNull Postgres conn, 2233                                             String stmtName, 2234                                             String query) 2235  { 2236    try { 2237 2238      PostgresStatement pstmt = conn.prepare(env, query); 2239      conn.putStatement(stmtName, pstmt); 2240      return pstmt; 2241 2242    } catch (Exception ex) { 2243      log.log(Level.FINE, ex.toString(), ex); 2244      return null; 2245    } 2246  } 2247 2248  /** 2249   * Send a NULL-terminated string to PostgreSQL backend 2250   */ 2251  public static boolean pg_put_line(Env env, 2252                                    @NotNull Postgres conn, 2253                                    String data) 2254  { 2255    try { 2256 2257      Class cl = Class.forName("org.postgresql.core.PGStream"); 2258 2259      Constructor constructor = cl.getDeclaredConstructor(new Class [] { 2260        String .class, Integer.TYPE}); 2261 2262      Object object = constructor.newInstance(new Object [] {conn.getHost(), conn.getPort()}); 2263 2264      byte dataArray[] = data.getBytes(); 2265 2266      Method method = cl.getDeclaredMethod("Send", new Class [] {byte[].class}); 2267 2268      method.invoke(object, new Object [] {dataArray}); 2269 2270      return true; 2271 2272    } catch (Exception ex) { 2273      log.log(Level.FINE, ex.toString(), ex); 2274      return false; 2275    } 2276 2277  } 2278 2279  /** 2280   * Submits a command to the server and waits for the result, 2281   * with the ability to pass parameters separately from the SQL command text 2282   */ 2283  @ReturnNullAsFalse 2284  public static PostgresResult pg_query_params(Env env, 2285                                               @NotNull Postgres conn, 2286                                               String query, 2287                                               ArrayValue params) 2288  { 2289    try { 2290 2291      if (pg_send_query_params(env, conn, query, params)) { 2292        return (PostgresResult) conn.getResultResource(); 2293      } 2294 2295      return null; 2296 2297    } catch (Exception ex) { 2298      log.log(Level.FINE, ex.toString(), ex); 2299      return null; 2300    } 2301  } 2302 2303  /** 2304   * Execute a query 2305   */ 2306  @ReturnNullAsFalse 2307  public static PostgresResult pg_query(Env env, 2308                                        @NotNull Postgres conn, 2309                                        String query) 2310  { 2311    try { 2312 2313      // XXX: the PHP api allows conn to be optional but we 2314 // totally disallow this case. 2315 2316      if (conn == null) 2317        conn = getConnection(env); 2318 2319      PostgresResult result = conn.query(query); 2320 2321      String error = conn.error(); 2322 2323      if ((error != null) && (! error.equals(""))) { 2324        env.warning(L.l("Query failed: {0}", error)); 2325        return null; 2326      } 2327 2328      return result; 2329 2330    } catch (Exception ex) { 2331      log.log(Level.FINE, ex.toString(), ex); 2332    } 2333 2334    return null; 2335  } 2336 2337  /** 2338   * Returns an individual field of an error report 2339   */ 2340  public static Value pg_result_error_field(Env env, 2341                                            @NotNull PostgresResult result, 2342                                            int fieldCode) 2343  { 2344    try { 2345 2346      Object errorField = null; 2347 2348      // Get the postgres specific server error message 2349 // org.postgresql.util.ServerErrorMessage 2350 Object serverError = ((Postgres) result.getConnection()).getServerErrorMessage(); 2351 2352      if (serverError != null) { 2353        Class cl = Class.forName("org.postgresql.util.ServerErrorMessage"); 2354 2355        String methodName; 2356 2357        switch (fieldCode) { 2358        case PGSQL_DIAG_SEVERITY: 2359          methodName = "getSeverity"; 2360          break; 2361        case PGSQL_DIAG_SQLSTATE: 2362          methodName = "getSQLState"; 2363          break; 2364        case PGSQL_DIAG_MESSAGE_PRIMARY: 2365          methodName = "getMessage"; 2366          break; 2367        case PGSQL_DIAG_MESSAGE_DETAIL: 2368          methodName = "getDetail"; 2369          break; 2370        case PGSQL_DIAG_MESSAGE_HINT: 2371          methodName = "getHint"; 2372          break; 2373        case PGSQL_DIAG_STATEMENT_POSITION: 2374          methodName = "getPosition"; 2375          break; 2376        case PGSQL_DIAG_INTERNAL_POSITION: 2377          methodName = "getInternalPosition"; 2378          break; 2379        case PGSQL_DIAG_INTERNAL_QUERY: 2380          methodName = "getInternalQuery"; 2381          break; 2382        case PGSQL_DIAG_CONTEXT: 2383          methodName = "getWhere"; 2384          break; 2385        case PGSQL_DIAG_SOURCE_FILE: 2386          methodName = "getFile"; 2387          break; 2388        case PGSQL_DIAG_SOURCE_LINE: 2389          methodName = "getLine"; 2390          break; 2391        case PGSQL_DIAG_SOURCE_FUNCTION: 2392          methodName = "getRoutine"; 2393          break; 2394        default: 2395          return null; 2396        } 2397 2398        Method method = cl.getDeclaredMethod(methodName, null); 2399        errorField = method.invoke(serverError, new Object [] {}); 2400      } 2401 2402      if (errorField == null) { 2403        if (fieldCode == PGSQL_DIAG_INTERNAL_QUERY) 2404          return BooleanValue.FALSE; 2405 2406        return NullValue.NULL; 2407      } 2408 2409      if (fieldCode == PGSQL_DIAG_STATEMENT_POSITION) { 2410 2411        Integer position = (Integer ) errorField; 2412 2413        if (position.intValue() == 0) 2414          return NullValue.NULL; 2415      } 2416 2417      if (fieldCode == PGSQL_DIAG_INTERNAL_POSITION) { 2418 2419        Integer position = (Integer ) errorField; 2420 2421        if (position.intValue() == 0) 2422          return BooleanValue.FALSE; 2423      } 2424 2425      return StringValue.create(errorField.toString()); 2426 2427    } catch (Exception ex) { 2428      log.log(Level.FINE, ex.toString(), ex); 2429      return NullValue.NULL; 2430    } 2431  } 2432 2433  /** 2434   * Get error message associated with result 2435   */ 2436  @ReturnNullAsFalse 2437  public static String pg_result_error(Env env, 2438                                       @NotNull PostgresResult result) 2439  { 2440    try { 2441 2442      return result.getConnection().getErrorMessage(); 2443 2444    } catch (Exception ex) { 2445      log.log(Level.FINE, ex.toString(), ex); 2446      return null; 2447    } 2448  } 2449 2450  /** 2451   * Set internal row offset in result resource 2452   */ 2453  public static boolean pg_result_seek(Env env, 2454                                       @NotNull PostgresResult result, 2455                                       int offset) 2456  { 2457    try { 2458 2459      if (result == null) 2460        return false; 2461 2462      return result.seek(env, offset); 2463 2464    } catch (Exception ex) { 2465      log.log(Level.FINE, ex.toString(), ex); 2466      return false; 2467    } 2468  } 2469 2470  /** 2471   * Get status of query result 2472   */ 2473  public static int pg_result_status(Env env, 2474                                     @NotNull PostgresResult result, 2475                                     @Optional("PGSQL_STATUS_LONG") int type) 2476  { 2477    try { 2478 2479      if (type == PGSQL_STATUS_STRING) { 2480        throw new UnimplementedException("pg_result_status with PGSQL_STATUS_STRING"); 2481      } 2482 2483      if (result != null) { 2484        Statement stmt = result.getStatement(); 2485        if (stmt.getUpdateCount() >= 0) { 2486          return PGSQL_COMMAND_OK; 2487        } 2488 2489        ResultSet rs = result.getResultSet(); 2490        if (rs == null) { 2491          return PGSQL_EMPTY_QUERY; 2492        } 2493 2494        return PGSQL_TUPLES_OK; 2495      } 2496 2497    } catch (Exception ex) { 2498      log.log(Level.FINE, ex.toString(), ex); 2499    } 2500 2501    return -1; 2502  } 2503 2504  /** 2505   * Select records 2506   */ 2507  @ReturnNullAsFalse 2508  public static ArrayValue pg_select(Env env, 2509                                     @NotNull Postgres conn, 2510                                     String tableName, 2511                                     ArrayValue assocArray, 2512                                     @Optional("-1") int options) 2513  { 2514    try { 2515 2516      if (conn == null) 2517        return null; 2518 2519      StringBuilderValue whereClause = new StringBuilderValue(); 2520 2521      boolean isFirst = true; 2522 2523      for (Map.Entry <Value,Value> entry : assocArray.entrySet()) { 2524        Value k = entry.getKey(); 2525        Value v = entry.getValue(); 2526        if (isFirst) { 2527          isFirst = false; 2528        } else { 2529          whereClause.append(" AND "); 2530        } 2531        whereClause.append(k.toString()).append("='").append(v.toString()).append("'"); 2532        // String pi = conn.realEscapeString(p).toString(); 2533 // pi = pi.replaceAll("\\\\", "\\\\\\\\"); 2534 } 2535 2536      StringBuilderValue query = new StringBuilderValue(); 2537      query.append("SELECT * FROM ").append(tableName).append(" WHERE ").append(whereClause); 2538 2539      PostgresResult result = pg_query(env, conn, query.toString()); 2540 2541      return pg_fetch_all(env, result); 2542 2543    } catch (Exception ex) { 2544      log.log(Level.FINE, ex.toString(), ex); 2545      return null; 2546    } 2547  } 2548 2549  /** 2550   * Sends a request to execute a prepared statement with given parameters, 2551   * without waiting for the result(s) 2552   */ 2553  public static boolean pg_send_execute(Env env, 2554                                        @NotNull Postgres conn, 2555                                        String stmtName, 2556                                        ArrayValue params) 2557  { 2558    try { 2559 2560      // Note: for now, this is essentially the same as pg_execute. 2561 2562      PostgresResult result = pg_execute(env, conn, stmtName, params); 2563 2564      conn.setAsynchronousResult(result); 2565 2566      if (result != null) { 2567        return true; 2568      } 2569 2570    } catch (Exception ex) { 2571      log.log(Level.FINE, ex.toString(), ex); 2572    } 2573 2574    return false; 2575  } 2576 2577  /** 2578   * Sends a request to create a prepared statement with the given parameters, 2579   * without waiting for completion 2580   */ 2581  public static boolean pg_send_prepare(Env env, 2582                                        @NotNull Postgres conn, 2583                                        String stmtName, 2584                                        String query) 2585  { 2586    try { 2587 2588      // Note: for now, this is the same as pg_prepare. 2589 2590      PostgresStatement stmt = pg_prepare(env, conn, stmtName, query); 2591 2592      conn.setAsynchronousStatement(stmt); 2593 2594      if (stmt != null) { 2595        return true; 2596      } 2597 2598    } catch (Exception ex) { 2599      log.log(Level.FINE, ex.toString(), ex); 2600    } 2601 2602    return false; 2603  } 2604 2605  /** 2606   * Submits a command and separate parameters to the server without waiting for the result(s) 2607   */ 2608  public static boolean pg_send_query_params(Env env, 2609                                             @NotNull Postgres conn, 2610                                             String query, 2611                                             ArrayValue params) 2612  { 2613    try { 2614 2615      PostgresStatement pstmt = conn.prepare(env, query); 2616 2617      return executeInternal(env, conn, pstmt, params) != null; 2618 2619      /* 2620      ArrayValueImpl arrayImpl = (ArrayValueImpl)params; 2621      int sz = arrayImpl.size(); 2622 2623      for (int i=0; i<sz; i++) { 2624        StringValue p = arrayImpl.get(LongValue.create(i)).toStringValue(); 2625        String pi = conn.realEscapeString(p).toString(); 2626        pi = pi.replaceAll("\\\\", "\\\\\\\\"); 2627        query = query.replaceAll("\\$"+(i+1), "\\'"+pi+"\\'"); 2628      } 2629 2630      pg_send_query(env, conn, query); 2631 2632      return true; 2633      */ 2634 2635    } catch (Exception ex) { 2636      log.log(Level.FINE, ex.toString(), ex); 2637      return false; 2638    } 2639  } 2640 2641  /** 2642   * Sends asynchronous query 2643   */ 2644  public static boolean pg_send_query(Env env, 2645                                      @NotNull Postgres conn, 2646                                      String query) 2647  { 2648    try { 2649 2650      PostgresResult result = pg_query(env, conn, query); 2651 2652      // We probably won't need this for now. See pg_get_result(). 2653 // conn.setAsynchronousResult(result); 2654 2655      // This is to be compliant with real expected results. 2656 // Even a SELECT * FROM doesnotexist returns true from pg_send_query. 2657 return true; 2658 2659    } catch (Exception ex) { 2660      log.log(Level.FINE, ex.toString(), ex); 2661    } 2662 2663    return false; 2664  } 2665 2666  /** 2667   * Set the client encoding 2668   */ 2669  public static int pg_set_client_encoding(Env env, 2670                                           @NotNull Postgres conn, 2671                                           String encoding) 2672  { 2673    //@todo conn should be optional 2674 if (conn == null) 2675      conn = getConnection(env); 2676 2677    if (pg_query(env, conn, "SET CLIENT_ENCODING TO '" + encoding +"'") == null) { 2678      return -1; 2679    } 2680 2681    return 0; 2682  } 2683 2684  /** 2685   * Determines the verbosity of messages returned by pg_last_error() and pg_result_error() 2686   */ 2687  public static int pg_set_error_verbosity(Env env, 2688                                           @NotNull Postgres conn, 2689                                           int intVerbosity) 2690  { 2691    try { 2692 2693      //@todo conn should be optional 2694 2695      String verbosity; 2696 2697      PostgresResult result = pg_query(env, conn, "SHOW log_error_verbosity"); 2698 2699      ArrayValue arr = pg_fetch_row(env, result, LongValue.create(0)); 2700 2701      String prevVerbosity = arr.get(LongValue.create(0)).toString(); 2702 2703      switch (intVerbosity) { 2704      case PGSQL_ERRORS_TERSE: 2705        verbosity = "TERSE"; 2706        break; 2707      case PGSQL_ERRORS_VERBOSE: 2708        verbosity = "VERBOSE"; 2709        break; 2710      default: 2711        verbosity = "DEFAULT"; 2712      } 2713 2714      pg_query(env, conn, "SET log_error_verbosity TO '"+verbosity+"'"); 2715 2716      if (prevVerbosity.equals("TERSE")) { 2717        return PGSQL_ERRORS_TERSE; 2718      } else if (prevVerbosity.equals("VERBOSE")) { 2719        return PGSQL_ERRORS_VERBOSE; 2720      } else { 2721        return PGSQL_ERRORS_DEFAULT; 2722      } 2723 2724    } catch (Exception ex) { 2725      log.log(Level.FINE, ex.toString(), ex); 2726      return -1; 2727    } 2728  } 2729 2730  /** 2731   * Enable tracing a PostgreSQL connection 2732   */ 2733  public static boolean pg_trace(Env env, 2734                                 Path path, 2735                                 @Optional String mode, 2736                                 @Optional Postgres conn) 2737  { 2738    env.stub("pg_trace"); 2739 2740    return false; 2741  } 2742 2743  /** 2744   * Returns the current in-transaction status of the server 2745   */ 2746  public static int pg_transaction_status(Env env, 2747                                          @Optional Postgres conn) 2748  { 2749    return PGSQL_TRANSACTION_IDLE; 2750  } 2751 2752  /** 2753   * Return the TTY name associated with the connection 2754   */ 2755  public static String pg_tty(Env env, 2756                              @Optional Postgres conn) 2757  { 2758    // Note: pg_tty() is obsolete, since the server no longer pays attention to 2759 // the TTY setting, but the function remains for backwards compatibility. 2760 2761    env.stub("pg_tty"); 2762 2763    return ""; 2764  } 2765 2766  /** 2767   * Unescape binary for bytea type 2768   */ 2769  @ReturnNullAsFalse 2770  public static String pg_unescape_bytea(Env env, 2771                                         String data) 2772  { 2773    try { 2774 2775      byte dataBytes[] = data.getBytes(); 2776 2777      Class cl = Class.forName("org.postgresql.util.PGbytea"); 2778 2779      Method method = cl.getDeclaredMethod("toBytes", new Class [] {byte[].class}); 2780 2781      return new String ((byte[]) method.invoke(cl, new Object [] {dataBytes})); 2782 2783    } catch (Exception ex) { 2784      log.log(Level.FINE, ex.toString(), ex); 2785      return null; 2786    } 2787  } 2788 2789  /** 2790   * Disable tracing of a PostgreSQL connection 2791   */ 2792  public static boolean pg_untrace(Env env, 2793                                   @Optional Postgres conn) 2794  { 2795    // Always returns TRUE 2796 2797    env.stub("pg_untrace"); 2798 2799    return true; 2800  } 2801 2802  /** 2803   * Update table 2804   */ 2805  public static boolean pg_update(Env env, 2806                                  @NotNull Postgres conn, 2807                                  String tableName, 2808                                  ArrayValue data, 2809                                  ArrayValue condition, 2810                                  @Optional int options) 2811  { 2812    // From php.net: This function is EXPERIMENTAL. 2813 2814    // The behaviour of this function, the name of this function, and 2815 // anything else documented about this function may change without 2816 // notice in a future release of PHP. Use this function at your own risk. 2817 2818    try { 2819 2820      if (options > 0) { 2821        throw new UnimplementedException("pg_update with options"); 2822      } 2823 2824      StringBuilder values = new StringBuilder (); 2825 2826      boolean isFirst = true; 2827 2828      for (Map.Entry <Value,Value> entry : data.entrySet()) { 2829        Value k = entry.getKey(); 2830        Value v = entry.getValue(); 2831        if (isFirst) { 2832          isFirst = false; 2833        } else { 2834          values.append(", "); 2835        } 2836        values.append(k.toString()); 2837        values.append("='"); 2838        values.append(v.toString()); 2839        values.append("'"); 2840      } 2841 2842      StringBuilder whereClause = new StringBuilder (); 2843 2844      isFirst = true; 2845 2846      for (Map.Entry <Value,Value> entry : condition.entrySet()) { 2847        Value k = entry.getKey(); 2848        Value v = entry.getValue(); 2849        if (isFirst) { 2850          isFirst = false; 2851        } else { 2852          whereClause.append(" AND "); 2853        } 2854        whereClause.append(k.toString()); 2855        whereClause.append("='"); 2856        whereClause.append(v.toString()); 2857        whereClause.append("'"); 2858      } 2859 2860      StringBuilder query = new StringBuilder (); 2861      query.append("UPDATE "); 2862      query.append(tableName); 2863      query.append(" SET "); 2864      query.append(values); 2865      query.append(" WHERE "); 2866      query.append(whereClause); 2867 2868      pg_query(env, conn, query.toString()); 2869 2870      return true; 2871 2872    } catch (Exception ex) { 2873      log.log(Level.FINE, ex.toString(), ex); 2874      return false; 2875    } 2876  } 2877 2878  /** 2879   * Returns an array with client, protocol and server version (when available) 2880   */ 2881  @ReturnNullAsFalse 2882  public static String pg_version(Env env, 2883                                  @Optional Postgres conn) 2884  { 2885    try { 2886 2887      //@todo return an array 2888 2889      if (conn == null) 2890        conn = getConnection(env); 2891 2892      return conn.getServerInfo(); 2893 2894    } catch (Exception ex) { 2895      log.log(Level.FINE, ex.toString(), ex); 2896      return null; 2897    } 2898  } 2899 2900  private static Postgres getConnection(Env env) 2901  { 2902    Postgres conn = (Postgres) env.getSpecialValue("caucho.postgres"); 2903 2904    if (conn != null) 2905      return conn; 2906 2907    String driver = "org.postgresql.Driver"; 2908    String url = "jdbc:postgresql://localhost:5432/"; 2909 2910    conn = new Postgres(env, "localhost", "", "", "", 5432, driver, url); 2911 2912    env.setSpecialValue("caucho.postgres", conn); 2913 2914    return conn; 2915  } 2916 2917  private static PostgresResult executeInternal(Env env, 2918                                                @NotNull Postgres conn, 2919                                                PostgresStatement pstmt, 2920                                                ArrayValue params) 2921  { 2922    try { 2923 2924      StringBuilder stringBuilder = new StringBuilder (); 2925 2926      int size = params.getSize(); // pstmt.getPreparedMappingSize(); 2927 2928      for (int i=0; i<size; i++) 2929        stringBuilder.append('s'); 2930 2931      String types = stringBuilder.toString(); 2932 2933      Value value[] = params.getValueArray(env); 2934      pstmt.bindParams(env, types, value); 2935 2936      if (!pstmt.execute(env)) 2937        return null; 2938 2939      if (pstmt.getStatementType().equals("SELECT")) { 2940        PostgresResult result = new PostgresResult(null, pstmt.getResultSet(), null); 2941        conn.setResultResource(result); 2942        return result; 2943      } else { 2944        // XXX: ??? return type? 2945 return null; 2946        // return pstmt; 2947 } 2948 2949    } catch (Exception ex) { 2950      log.log(Level.FINE, ex.toString(), ex); 2951      return null; 2952    } 2953  } 2954 2955  private static int writeLobInternal(Object largeObject, 2956                                      InputStream is, 2957                                      int len) 2958  { 2959    try { 2960 2961      Class cl = Class.forName("org.postgresql.largeobject.LargeObject"); 2962 2963      Method method = cl.getDeclaredMethod("getOutputStream", null); 2964 2965      OutputStream os = (OutputStream ) method.invoke(largeObject, new Object [] {}); 2966 2967      int written = 0; 2968 2969      int b; 2970 2971      while(((b = is.read()) >= 0) && (written++ < len)) { 2972        os.write(b); 2973      } 2974 2975      os.close(); 2976 2977      return written; 2978 2979    } catch (Exception ex) { 2980      log.log(Level.FINE, ex.toString(), ex); 2981      return -1; 2982    } 2983  } 2984} 2985

A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame Popular Tags