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.Reference; 36 import com.caucho.quercus.annotation.ReturnNullAsFalse; 37 import com.caucho.quercus.env.*; 38 import com.caucho.quercus.module.AbstractQuercusModule; 39 import com.caucho.util.L10N; 40 import com.caucho.util.Log; 41 42 import java.lang.reflect.Constructor ; 43 import java.lang.reflect.Method ; 44 import java.sql.*; 45 import java.util.Map ; 46 import java.util.logging.Level ; 47 import java.util.logging.Logger ; 48 import java.util.regex.Matcher ; 49 import java.util.regex.Pattern ; 50 51 52 /** 53  * Quercus oracle routines. 54  * 55  * NOTE from php.net: 56  * 57  * "... 58  * These functions allow you to access Oracle 10, Oracle 9, Oracle 8 and Oracle 7 59  * databases using the Oracle Call Interface (OCI). They support binding of PHP 60  * variables to Oracle placeholders, have full LOB, FILE and ROWID support, and 61  * allow you to use user-supplied define variables. 62  * 63  * Requirements 64  * 65  * You will need the Oracle client libraries to use this extension. 66  * Windows users will need libraries with version at least 10 to use the php_oci8.dll. 67  * 68  * ..." 69  * 70  */ 71 public class OracleModule extends AbstractQuercusModule { 72   private static final Logger log = Log.open(OracleModule.class); 73   private static final L10N L = new L10N(OracleModule.class); 74 75   // WARNING: Do not change order or constant values. 76 // They are mapped to oracle types below. 77 // See arrayPhpToOracleType. 78 public static final int OCI_B_BFILE = 0x01; 79   public static final int OCI_B_CFILEE = 0x02; 80   public static final int OCI_B_CLOB = 0x03; 81   public static final int OCI_B_BLOB = 0x04; 82   public static final int OCI_B_ROWID = 0x05; 83   public static final int OCI_B_CURSOR = 0x06; 84   public static final int OCI_B_NTY = 0x07; 85   public static final int OCI_B_BIN = 0x08; 86   public static final int OCI_DTYPE_FILE = 0x09; 87   public static final int OCI_DTYPE_LOB = 0x0A; 88   public static final int OCI_DTYPE_ROWID = 0x0B; 89   public static final int OCI_D_FILE = 0x0C; 90   public static final int OCI_D_LOB = 0x0D; 91   public static final int OCI_D_ROWID = 0x0E; 92   public static final int OCI_SYSDATE = 0x0F; 93   public static final int OCI_TEMP_CLOB = 0x10; 94   public static final int OCI_TEMP_BLOB = 0x11; 95   public static final int SQLT_BFILEE = 0x12; 96   public static final int SQLT_CFILEE = 0x13; 97   public static final int SQLT_CLOB = 0x14; 98   public static final int SQLT_BLOB = 0x15; 99   public static final int SQLT_RDD = 0x16; 100   public static final int SQLT_NTY = 0x17; 101   public static final int SQLT_LNG = 0x18; 102   public static final int SQLT_LBI = 0x19; 103   public static final int SQLT_BIN = 0x1A; 104   public static final int SQLT_NUM = 0x1B; 105   public static final int SQLT_INT = 0x1C; 106   public static final int SQLT_AFC = 0x1D; 107   public static final int SQLT_CHR = 0x1E; 108   public static final int SQLT_VCS = 0x1F; 109   public static final int SQLT_AVC = 0x20; 110   public static final int SQLT_STR = 0x21; 111   public static final int SQLT_LVC = 0x22; 112   public static final int SQLT_FLT = 0x23; 113   public static final int SQLT_ODT = 0x24; 114   public static final int SQLT_BDOUBLE = 0x25; 115   public static final int SQLT_BFLOAT = 0x26; 116   public static final int SQLT_RSET = 0x27; 117   public static final int SQLT_FILE = 0x28; 118   public static final int SQLT_CFILE = 0x29; 119 120   // Reserved for future types and extensions 121 // 0x30 - 0x4F 122 123   // OCI Control Constants 0x50 - ... 124 public static final int OCI_DEFAULT = 0x50; 125   public static final int OCI_DESCRIBE_ONLY = 0x51; 126   public static final int OCI_COMMIT_ON_SUCCESS = 0x52; 127   public static final int OCI_EXACT_FETCH = 0x53; 128   public static final int OCI_FETCHSTATEMENT_BY_COLUMN = 0x54; 129   public static final int OCI_FETCHSTATEMENT_BY_ROW = 0x55; 130   public static final int OCI_ASSOC = 0x56; 131   public static final int OCI_NUM = 0x57; 132   public static final int OCI_BOTH = 0x58; 133   public static final int OCI_RETURN_NULLS = 0x59; 134   public static final int OCI_RETURN_LOBS = 0x5A; 135   public static final int OCI_SYSOPER = 0x5B; 136   public static final int OCI_SYSDBA = 0x5C; 137   public static final int OCI_LOB_BUFFER_FREE = 0x5D; 138   public static final int OCI_SEEK_SET = 0x5E; 139   public static final int OCI_SEEK_CUR = 0x5F; 140   public static final int OCI_SEEK_END = 0x6A; 141 142 143   // Cache class oracle.jdbc.OracleTypes to be used below. 144 private static Class classOracleTypes; 145 146   // Map php to oracle type 147 private static int arrayPhpToOracleType[]; 148 149   static { 150     try { 151       classOracleTypes = Class.forName("oracle.jdbc.OracleTypes"); 152 153       arrayPhpToOracleType = new int[] { 154         -1, 155         classOracleTypes.getDeclaredField("BFILE").getInt(null), // OCI_B_BFILE 156 -1, // OCI_B_CFILEE 157 classOracleTypes.getDeclaredField("CLOB").getInt(null), // OCI_B_CLOB 158 classOracleTypes.getDeclaredField("BLOB").getInt(null), // OCI_B_BLOB 159 classOracleTypes.getDeclaredField("ROWID").getInt(null), // OCI_B_ROWID 160 classOracleTypes.getDeclaredField("CURSOR").getInt(null), // OCI_B_CURSOR 161 classOracleTypes.getDeclaredField("OTHER").getInt(null), // OCI_B_NTY 162 classOracleTypes.getDeclaredField("RAW").getInt(null), // OCI_B_BIN 163 -1, // OCI_DTYPE_FILE 164 -1, // OCI_DTYPE_LOB 165 -1, // OCI_DTYPE_ROWID 166 -1, // OCI_D_FILE 167 -1, // OCI_D_LOB 168 -1, // OCI_D_ROWID 169 classOracleTypes.getDeclaredField("TIMESTAMP").getInt(null), // OCI_SYSDATE 170 -1, // OCI_TEMP_CLOB 171 -1, // OCI_TEMP_BLOB 172 classOracleTypes.getDeclaredField("BFILE").getInt(null), // SQLT_BFILEE 173 -1, // SQLT_CFILEE 174 classOracleTypes.getDeclaredField("CLOB").getInt(null), // SQLT_CLOB 175 classOracleTypes.getDeclaredField("BLOB").getInt(null), // SQLT_BLOB 176 classOracleTypes.getDeclaredField("ROWID").getInt(null), // SQLT_RDD 177 classOracleTypes.getDeclaredField("OTHER").getInt(null), // SQLT_NTY 178 classOracleTypes.getDeclaredField("NUMBER").getInt(null), // SQLT_LNG 179 classOracleTypes.getDeclaredField("RAW").getInt(null), // SQLT_LBI 180 classOracleTypes.getDeclaredField("RAW").getInt(null), // SQLT_BIN 181 classOracleTypes.getDeclaredField("NUMBER").getInt(null), // SQLT_NUM 182 classOracleTypes.getDeclaredField("INTEGER").getInt(null), // SQLT_INT 183 classOracleTypes.getDeclaredField("CHAR").getInt(null), // SQLT_AFC 184 classOracleTypes.getDeclaredField("CHAR").getInt(null), // SQLT_CHR 185 classOracleTypes.getDeclaredField("VARCHAR").getInt(null), // SQLT_VCS 186 classOracleTypes.getDeclaredField("CHAR").getInt(null), // SQLT_AVC 187 classOracleTypes.getDeclaredField("VARCHAR").getInt(null), // SQLT_STR 188 classOracleTypes.getDeclaredField("LONGVARCHAR").getInt(null), // SQLT_LVC 189 classOracleTypes.getDeclaredField("FLOAT").getInt(null), // SQLT_FLT 190 classOracleTypes.getDeclaredField("DATE").getInt(null), // SQLT_ODT 191 classOracleTypes.getDeclaredField("DOUBLE").getInt(null), // SQLT_BDOUBLE 192 classOracleTypes.getDeclaredField("FLOAT").getInt(null), // SQLT_BFLOAT 193 classOracleTypes.getDeclaredField("CURSOR").getInt(null), // SQLT_RSET 194 classOracleTypes.getDeclaredField("BFILE").getInt(null), // SQLT_FILE 195 -1 // SQLT_CFILE 196 }; 197     } catch (Exception e) { 198       L.l("Unable to load Oracle types from oracle.jdbc.OracleTypes. Check your Oracle JDBC driver version."); 199     } 200   } 201 202   public OracleModule() 203   { 204   } 205 206   /** 207    * Returns true for the oracle extension. 208    */ 209   public String []getLoadedExtensions() 210   { 211     return new String [] { "oracle" }; 212   } 213 214   /** 215    * Binds PHP array to Oracle PL/SQL array by name. 216    * 217    * oci_bind_array_by_name() binds the PHP array 218    * varArray to the Oracle placeholder name, which 219    * points to Oracle PL/SQL array. Whether it will 220    * be used for input or output will be determined 221    * at run-time. The maxTableLength parameter sets 222    * the maximum length both for incoming and result 223    * arrays. Parameter maxItemLength sets maximum 224    * length for array items. If maxItemLength was 225    * not specified or equals to -1, 226    * oci_bind_array_by_name() will find the longest 227    * element in the incoming array and will use it as 228    * maximum length for array items. type parameter 229    * should be used to set the type of PL/SQL array 230    * items. See list of available types below. 231    * 232    * @param env the PHP executing environment 233    * @param stmt the Oracle statement 234    * @param name the Oracle placeholder 235    * @param varArray the array to be binded 236    * @param maxTableLength maximum table length 237    * @param maxItemLength maximum item length 238    * @param type one of the following types: 239    * <br/> 240    * SQLT_NUM - for arrays of NUMBER. 241    * <br/> 242    * SQLT_INT - for arrays of INTEGER 243    * (Note: INTEGER it is actually a synonym for 244    * NUMBER(38), but SQLT_NUM type won't work in 245    * this case even though they are synonyms). 246    * <br/> 247    * SQLT_FLT - for arrays of FLOAT. 248    * <br/> 249    * SQLT_AFC - for arrays of CHAR. 250    * <br/> 251    * SQLT_CHR - for arrays of VARCHAR2. 252    * <br/> 253    * SQLT_VCS - for arrays of VARCHAR. 254    * <br/> 255    * SQLT_AVC - for arrays of CHARZ. 256    * <br/> 257    * SQLT_STR - for arrays of STRING. 258    * <br/> 259    * SQLT_LVC - for arrays of LONG VARCHAR. 260    * <br/> 261    * SQLT_ODT - for arrays of DATE. 262    * 263    * @return true on success of false on failure 264    */ 265   public static boolean oci_bind_array_by_name(Env env, 266                                                @NotNull OracleStatement stmt, 267                                                @NotNull String name, 268                                                @NotNull ArrayValue varArray, 269                                                @NotNull int maxTableLength, 270                                                @Optional("0") int maxItemLength, 271                                                @Optional("0") int type) 272   { 273     try { 274 275       // JDBC underlying connection 276 Connection conn = stmt.getJavaConnection(); 277 278       // Oracle underlying statement 279 PreparedStatement oracleStmt = stmt.getPreparedStatement(); 280 281       // Create an oracle.sql.ARRAY object to hold the values 282 // oracle.sql.ArrayDescriptor arrayDesc = 283 // oracle.sql.ArrayDescriptor.createDescriptor("number_varray", conn); 284 285 286       Class clArrayDescriptor = Class.forName("oracle.sql.ArrayDescriptor"); 287 288       Method method 289         = clArrayDescriptor.getDeclaredMethod("createDescriptor", 290                                               new Class [] {String .class, Connection.class}); 291 292       Object arrayDesc = method.invoke(clArrayDescriptor, 293                                        new Object [] {"NUMBER_VARRAY", conn}); 294 295       Value valueArray[] = varArray.valuesToArray(); // int arrayValues[] = {123, 234}; 296 297       Object objectArray[] = new Object [5]; // {"aaa", "bbb", "ccc"}; 298 for (int i=0; i<valueArray.length; i++) { 299         Object obj = valueArray[i].toJavaObject(); 300         objectArray[i] = obj; 301       } 302 303       // oracle.sql.ARRAY array = new oracle.sql.ARRAY(arrayDesc, conn, arrayValues); 304 305       Class clARRAY = Class.forName("oracle.sql.ARRAY"); 306 307       Constructor constructor = clARRAY.getDeclaredConstructor(new Class [] { 308         clArrayDescriptor, Connection.class, Object .class}); 309 310       Array oracleArray = (Array) constructor.newInstance(new Object [] 311         {arrayDesc, conn, objectArray}); 312 313       // Bind array 314 // ((oracle.jdbc.OraclePreparedStatement)oracleStmt).setARRAY(1, array); 315 316       // cl = Class.forName("oracle.jdbc.OraclePreparedStatement"); 317 318       // method = cl.getDeclaredMethod("setARRAY", 319 // new Class[] {Integer.TYPE, Object[].class}); 320 321       if (name == null) { 322         return false; 323       } 324 325       if (!name.startsWith(":")) { 326         name = ":" + name; 327       } 328 329       if (name.length() < 2) { 330         return false; 331       } 332 333       // method.invoke(oracleStmt, new Object[] {name, oracleArray}); 334 335       Integer index = stmt.getBindingVariable(name); 336 337       if (index == null) 338         return false; 339 340       int i = index.intValue(); 341       Object object = varArray.toJavaObject(); 342 343       if (object instanceof OracleOciCollection) { 344         oracleArray = ((OracleOciCollection) object).getCollection(); 345         oracleStmt.setArray(i, oracleArray); 346       } else if (varArray instanceof ArrayValueImpl) { 347         // oracleStmt.setObject(i, varArray.getKeyArray()); 348 // Object objectArray[] = new Object[] {"aaa", "bbb", "ccc"}; 349 // oracleStmt.setObject(i, objectArray); 350 oracleStmt.setArray(i, oracleArray); 351       } else { 352         oracleStmt.setObject(i, object); 353       } 354 355       // drop descriptor ???? 'number_varray' ???? 356 357       return true; 358 359     } catch (Exception e) { 360       log.log(Level.FINE, e.toString(), e); 361       return false; 362     } 363   } 364 365   /** 366    * Binds the PHP variable to the Oracle placeholder 367    * 368    * @param type one of the following types: 369    * 370    * SQLT_INT - for integers; 371    * 372    * SQLT_CHR - for VARCHARs; 373    * 374    * SQLT_RSET - for cursors, that were created before with oci_new_cursor() 375    * 376    * OCI_B_BFILE (integer) 377    * 378    * Used with oci_bind_by_name() when binding BFILEs. 379    * 380    * OCI_B_CFILEE (integer) 381    * 382    * Used with oci_bind_by_name() when binding CFILEs. 383    * 384    * OCI_B_CLOB (integer) 385    * 386    * Used with oci_bind_by_name() when binding CLOBs. 387    * 388    * OCI_B_BLOB (integer) 389    * 390    * Used with oci_bind_by_name() when binding BLOBs. 391    * 392    * OCI_B_ROWID (integer) 393    * 394    * Used with oci_bind_by_name() when binding ROWIDs. 395    * 396    * OCI_B_CURSOR (integer) 397    * 398    * Used with oci_bind_by_name() when binding cursors, 399    * previously allocated with oci_new_descriptor(). 400    * 401    * OCI_B_NTY (integer) 402    * 403    * Used with oci_bind_by_name() when binding named data 404    * types. Note: in PHP < 5.0 it was called OCI_B_SQLT_NTY. 405    * 406    * OCI_B_BIN (integer) 407    * 408    * SQLT_FILE (integer) 409    * 410    * SQLT_BFILEE (integer) 411    * 412    * The same as OCI_B_BFILE. 413    * 414    * SQLT_CFILE (integer) 415    * 416    * SQLT_CFILEE (integer) 417    * 418    * The same as OCI_B_CFILEE. 419    * 420    * SQLT_CLOB (integer) 421    * 422    * The same as OCI_B_CLOB. 423    * 424    * SQLT_BLOB (integer) 425    * 426    * The same as OCI_B_BLOB. 427    * 428    * SQLT_RDD (integer) 429    * 430    * The same as OCI_B_ROWID. 431    * 432    * SQLT_NTY (integer) 433    * 434    * The same as OCI_B_NTY. 435    * 436    * SQLT_LNG (integer) 437    * 438    * Used with oci_bind_by_name() to bind LONG values. 439    * 440    * SQLT_LBI (integer) 441    * 442    * Used with oci_bind_by_name() to bind LONG RAW values. 443    * 444    * SQLT_BIN (integer) 445    * 446    * Used with oci_bind_by_name() to bind RAW values. 447    * 448    */ 449   public static boolean oci_bind_by_name(Env env, 450                                          @NotNull OracleStatement stmt, 451                                          @NotNull String placeholderName, 452                                          Value variable, 453                                          @Optional("0") int maxLength, 454                                          @Optional("0") int type) 455   { 456     if ((type == OCI_B_CFILEE) || 457         (type == SQLT_CFILE) || 458         (type == SQLT_CFILEE)) { 459       throw new UnimplementedException("oci_bind_by_name with CFILE"); 460     } 461 462     try { 463 464       if (placeholderName == null) { 465         return false; 466       } 467 468       if (!placeholderName.startsWith(":")) { 469         placeholderName = ":" + placeholderName; 470       } 471 472       if (placeholderName.length() < 2) { 473         return false; 474       } 475 476       Integer index = stmt.getBindingVariable(placeholderName); 477 478       if (index == null) 479         return false; 480 481       int i = index.intValue(); 482 483       PreparedStatement pstmt = stmt.getPreparedStatement(); 484 485       CallableStatement callableStmt = (CallableStatement) pstmt; 486 487       // XXX: We could use ParameterMetaData.getParameterMode 488 // to figure out which parameters are IN and/or OUT and 489 // then setObject and/or registerOutParameter according 490 // to the parameter mode. However, getParameterMode() 491 // is unsupported from Oracle JDBC drivers (Jun-2006). 492 // 493 // ParameterMetaData metaData = pstmt.getParameterMetaData(); 494 // 495 // int paramMode = metaData.getParameterMode(i); 496 // 497 // switch (paramMode) { 498 // case ParameterMetaData.parameterModeInOut: 499 // { 500 // int oracleType = arrayPhpToOracleType[type]; 501 // callableStmt.registerOutParameter(i, oracleType); 502 // pstmt.setObject(i, variable.toJavaObject()); 503 // break; 504 // } 505 // case ParameterMetaData.parameterModeOut: 506 // { 507 // int oracleType = arrayPhpToOracleType[type]; 508 // callableStmt.registerOutParameter(i, oracleType); 509 // break; 510 // } 511 // default: // case ParameterMetaData.parameterModeIn: 512 // { 513 // pstmt.setObject(i, variable.toJavaObject()); 514 // break; 515 // } 516 // } 517 518       switch (type) { 519       case OCI_B_CURSOR: 520       case SQLT_RSET: 521         { 522           // Assume the most common scenario: OUT parameter mode. 523 int oracleType = arrayPhpToOracleType[type]; 524           callableStmt.registerOutParameter(i, oracleType); 525           // Set the cursor's underlying statement (see php/4404). 526 Object cursor = variable.toJavaObject(); 527           if ((cursor == null) || !(cursor instanceof OracleStatement)) { 528             return false; 529           } 530           ((OracleStatement) cursor).setPreparedStatement(callableStmt); 531           break; 532         } 533       case OCI_B_BFILE: // BFILE 534 case SQLT_BFILEE: // ... 535 case SQLT_FILE: // ... 536 case SQLT_BLOB: // BLOB 537 case OCI_B_BLOB: // ... 538 case SQLT_CLOB: // CLOB 539 case OCI_B_CLOB: // ... 540 case OCI_B_ROWID: // ROWID 541 case SQLT_RDD: // ... 542 { 543           // Assume the most common scenario: OUT parameter mode. 544 int oracleType = arrayPhpToOracleType[type]; 545           callableStmt.registerOutParameter(i, oracleType); 546           Object ociLob = variable.toJavaObject(); 547           if ((ociLob == null) || !(ociLob instanceof OracleOciLob)) { 548             return false; 549           } 550           stmt.setOutParameter((OracleOciLob) ociLob); 551           break; 552         } 553       default: 554         { 555           // Assume the most common scenario: IN parameter mode. 556 557           // XXX: Check the spec. if there is a case where the 558 // variable would not be initialized yet 559 // stmt.putByNameVariable(placeholderName, variable); 560 Object object = variable.toJavaObject(); 561           if (object instanceof OracleOciCollection) { 562             object = ((OracleOciCollection) object).getCollection(); 563           } 564           pstmt.setObject(i, object); 565         } 566       } 567 568       return true; 569 570     } catch (Exception ex) { 571       log.log(Level.FINE, ex.toString(), ex); 572 573       try { 574         stmt.resetBindingVariables(); 575         stmt.resetByNameVariables(); 576       } catch (Exception ex2) { 577         log.log(Level.FINE, ex2.toString(), ex2); 578       } 579     } 580 581     return false; 582   } 583 584   /** 585    * Cancels reading from cursor 586    */ 587   public static boolean oci_cancel(Env env, 588                                    @NotNull OracleStatement stmt) 589   { 590     return oci_free_statement(env, stmt); 591   } 592 593   /** 594    * Closes Oracle connection 595    */ 596   public static boolean oci_close(Env env, 597                                   @NotNull Oracle conn) 598   { 599     if (conn == null) 600       conn = getConnection(env); 601 602     if (conn != null) { 603       if (conn == getConnection(env)) 604         env.removeSpecialValue("caucho.oracle"); 605 606       conn.close(env); 607 608       return true; 609     } 610     else 611       return false; 612   } 613 614   /** 615    * Commits outstanding statements 616    */ 617   public static boolean oci_commit(Env env, 618                                    @NotNull Oracle conn) 619   { 620     try { 621       return conn.commit(); 622     } catch (Exception ex) { 623       log.log(Level.FINE, ex.toString(), ex); 624       return false; 625     } 626   } 627 628   /** 629    * Establishes a connection to the Oracle server 630    */ 631   public static Value oci_connect(Env env, 632                                   @NotNull String username, 633                                   @NotNull String password, 634                                   @Optional String db, 635                                   @Optional String charset, 636                                   @Optional("0") int sessionMode) 637   { 638     // Note: The second and subsequent calls to oci_connect() with the same parameters 639 // will return the connection handle returned from the first call. This means that 640 // queries issued against one handle are also applied to the other handles, because 641 // they are the same handle. (source: php.net) 642 643     if (!((charset == null) || charset.length() == 0)) { 644       throw new UnimplementedException("oci_connect with charset"); 645     } 646 647     if ((sessionMode == OCI_DEFAULT) || 648         (sessionMode == OCI_SYSOPER) || 649         (sessionMode == OCI_SYSDBA)) { 650       throw new UnimplementedException("oci_connect with session mode"); 651     } 652 653     return connectInternal(env, true, username, password, db, charset, sessionMode); 654   } 655 656   /** 657    * Uses a PHP variable for the define-step during a SELECT 658    */ 659   public static boolean oci_define_by_name(Env env, 660                                            @NotNull OracleStatement stmt, 661                                            @NotNull String columnName, 662                                            @NotNull @Reference Value variable, 663                                            @Optional("0") int type) 664   { 665     // Example: 666 // 667 // $stmt = oci_parse($conn, "SELECT id, data FROM test"); 668 // 669 // /* the define MUST be done BEFORE oci_execute! */ 670 // 671 // oci_define_by_name($stmt, "ID", $myid); 672 // oci_define_by_name($stmt, "DATA", $mydata); 673 // 674 // oci_execute($stmt, OCI_DEFAULT); 675 // 676 // while ($row = oci_fetch($stmt)) { 677 // echo "id:" . $myid . "\n"; 678 // echo "data:" . $mydata . "\n"; 679 // } 680 681     try { 682       stmt.putByNameVariable(columnName, variable); 683       return true; 684     } catch (Exception ex) { 685       log.log(Level.FINE, ex.toString(), ex); 686       return false; 687     } 688   } 689 690   /** 691    * Returns the last error found 692    */ 693   public static String oci_error(Env env, 694                                  @Optional Value resource) 695   { 696     JdbcConnectionResource conn = null; 697 698     if (resource == null) { 699       conn = getConnection(env).validateConnection(); 700     } else { 701       Object object = resource.toJavaObject(); 702 703       if (object instanceof Oracle) { 704         conn = ((Oracle) object).validateConnection(); 705       } else { 706         conn = ((OracleStatement) object).validateConnection(); 707       } 708     } 709 710     return conn.getErrorMessage(); 711   } 712 713   /** 714    * Executes a statement 715    */ 716   public static boolean oci_execute(Env env, 717                                     @NotNull OracleStatement stmt, 718                                     @Optional("0") int mode) 719   { 720     try { 721 722       // Scenarios for oci_execute. 723 // 724 // 725 // 1. Simple query: oci_parse / oci_execute 726 // 727 // $query = 'SELECT * FROM TEST'; 728 // 729 // $stmt = oci_parse($conn, $query); 730 // 731 // oci_execute($stmt, OCI_DEFAULT); 732 // 733 // $result = oci_fetch_array($stid, OCI_BOTH); 734 // 735 // 736 // 2. Define by name: oci_parse / oci_define_by_name / oci_execute 737 // 738 // $stmt = oci_parse($conn, "SELECT id, data FROM test"); 739 // 740 // /* the define MUST be done BEFORE oci_execute! */ 741 // 742 // oci_define_by_name($stmt, "ID", $myid); 743 // oci_define_by_name($stmt, "DATA", $mydata); 744 // 745 // oci_execute($stmt, OCI_DEFAULT); 746 // 747 // while ($row = oci_fetch($stmt)) { 748 // echo "id:" . $myid . "\n"; 749 // echo "data:" . $mydata . "\n"; 750 // } 751 // 752 // 753 // 3. Cursors: oci_new_cursor / oci_parse / 754 // oci_bind_by_name / oci_execute($stmt) / oci_execute($cursor) 755 // 756 // $stmt = oci_parse($conn, 757 // "CREATE OR REPLACE PACKAGE cauchopkgtestoci AS ". 758 // "TYPE refcur IS REF CURSOR; ". 759 // "PROCEDURE testproc (var_result out cauchopkgtestoci.refcur); ". 760 // "END cauchopkgtestoci; "); 761 // 762 // oci_execute($stmt); 763 // 764 // $stmt = oci_parse($conn, 765 // "CREATE OR REPLACE PACKAGE BODY cauchopkgtestoci IS ". 766 // "PROCEDURE testproc (var_result out cauchopkgtestoci.refcur) IS ". 767 // "BEGIN ". 768 // "OPEN var_result FOR SELECT data FROM caucho.test; ". 769 // "END testproc; ". 770 // "END; "); 771 // 772 // oci_execute($stmt); 773 // 774 // $curs = oci_new_cursor($conn); 775 // 776 // $stmt = oci_parse($conn, "begin cauchopkgtestoci.testproc(:dataCursor); end;"); 777 // 778 // oci_bind_by_name($stmt, "dataCursor", $curs, 255, SQLT_RSET); 779 // 780 // oci_execute($stmt); 781 // 782 // oci_execute($curs); 783 // 784 // while ($data = oci_fetch_row($curs)) { 785 // var_dump($data); 786 // } 787 788       // Get the underlying JDBC connection. 789 Connection conn = stmt.getJavaConnection(); 790 791       // Large Objects can not be used in auto-commit mode. 792 conn.setAutoCommit(false); 793 794       // Use the underlying callable statement to check different scenarios. 795 CallableStatement callableStatement = stmt.getCallableStatement(); 796 797       // Check for case (3) oci_execute($cursor); 798 // A previous statement has been executed and holds the result set. 799 Object cursorResult = null; 800       try { 801         cursorResult = callableStatement.getObject(1); 802         if ((cursorResult != null) && (cursorResult instanceof ResultSet)) { 803           ResultSet rs = (ResultSet) cursorResult; 804           stmt.setResultSet(rs); 805           return true; 806         } 807       } catch (Exception e) { 808         // We assume this is not case (3). No error. 809 } 810 811       // Case (1) or executing a more complex query. 812 stmt.execute(env); 813 814       OracleOciLob ociLob = stmt.getOutParameter(); 815       if (ociLob != null) { 816         // Ex: java.sql.Clob outParameter = callableStatement.getClob(1); 817 ociLob.setLob(callableStatement.getObject(1)); 818       } 819 820       // Fetch and assign values to corresponding binded variables 821 // This is necessary for LOB support and 822 // should be reworked calling a private fetch method instead. 823 // oci_fetch(env, stmt); 824 825       if (mode == OCI_COMMIT_ON_SUCCESS) { 826         conn.commit(); 827       } 828 829       return true; 830 831     } catch (Exception ex) { 832       log.log(Level.FINE, ex.toString(), ex); 833 834       try { 835         stmt.resetBindingVariables(); 836         stmt.resetByNameVariables(); 837       } catch (Exception ex2) { 838         log.log(Level.FINE, ex2.toString(), ex2); 839       } 840 841       return false; 842     } 843   } 844 845   /** 846    * Fetches all rows of result data into an array 847    */ 848   @ReturnNullAsFalse 849   public static ArrayValue oci_fetch_all(Env env, 850                                          @NotNull OracleStatement stmt, 851                                          @NotNull Value output, 852                                          @Optional int skip, 853                                          @Optional int maxrows, 854                                          @Optional int flags) 855   { 856     JdbcResultResource resource = null; 857 858     ArrayValueImpl newArray = new ArrayValueImpl(); 859 860     try { 861       if (stmt == null) 862         return null; 863 864       resource = new JdbcResultResource(null, stmt.getResultSet(), null); 865 866       Value value = resource.fetchArray(env, JdbcResultResource.FETCH_ASSOC); 867 868       int curr = 0; 869 870       while(value != NullValue.NULL) { 871         newArray.put(LongValue.create(curr), value); 872 873         curr++; 874 875         value = resource.fetchArray(env, JdbcResultResource.FETCH_ASSOC); 876       } 877     } catch (Exception ex) { 878       log.log(Level.FINE, ex.toString(), ex); 879       return null; 880     } 881 882     return newArray; 883   } 884 885   /** 886    * Returns the next row from the result data as an associative or numeric array, or both 887    */ 888   @ReturnNullAsFalse 889   public static ArrayValue oci_fetch_array(Env env, 890                                            @NotNull OracleStatement stmt, 891                                            @Optional("-1") int mode) 892   { 893     if (stmt == null) 894       return null; 895 896     if (mode == OCI_RETURN_LOBS) 897       throw new UnimplementedException("oci_fetch_array with OCI_RETURN_LOBS"); 898 899     if (mode == OCI_RETURN_NULLS) 900       throw new UnimplementedException("oci_fetch_array with OCI_RETURN_NULLS"); 901 902     try { 903 904       JdbcResultResource resource = new JdbcResultResource(null, stmt.getResultSet(), null); 905 906       switch (mode) { 907       case OCI_ASSOC: 908         return resource.fetchArray(env, JdbcResultResource.FETCH_ASSOC); 909       case OCI_NUM: 910         return resource.fetchArray(env, JdbcResultResource.FETCH_NUM); 911       default: // case OCI_BOTH: 912 return resource.fetchArray(env, JdbcResultResource.FETCH_BOTH); 913       } 914 915     } catch (Exception ex) { 916       log.log(Level.FINE, ex.toString(), ex); 917       return null; 918     } 919   } 920 921   /** 922    * Returns the next row from the result data as an associative array 923    */ 924   @ReturnNullAsFalse 925   public static ArrayValue oci_fetch_assoc(Env env, 926                                            @NotNull OracleStatement stmt) 927   { 928     try { 929 930       if (stmt == null) 931         return null; 932 933       JdbcResultResource resource = new JdbcResultResource(null, stmt.getResultSet(), null); 934       ArrayValue arrayValue = resource.fetchArray(env, JdbcResultResource.FETCH_ASSOC); 935 936       return arrayValue; 937 938     } catch (Exception ex) { 939       log.log(Level.FINE, ex.toString(), ex); 940       return null; 941     } 942   } 943 944   /** 945    * Returns the next row from the result data as an object 946    */ 947   public static Value oci_fetch_object(Env env, 948                                        @NotNull OracleStatement stmt) 949   { 950     try { 951       if (stmt == null) 952         return BooleanValue.FALSE; 953 954       JdbcResultResource resource = new JdbcResultResource(null, stmt.getResultSet(), null); 955       return resource.fetchObject(env); 956     } catch (Exception ex) { 957       log.log(Level.FINE, ex.toString(), ex); 958       return BooleanValue.FALSE; 959     } 960   } 961 962   /** 963    * Returns the next row from the result data as a numeric array 964    */ 965   @ReturnNullAsFalse 966   public static ArrayValue oci_fetch_row(Env env, 967                                          @NotNull OracleStatement stmt) 968   { 969     try { 970 971       if (stmt == null) 972         return null; 973 974       JdbcResultResource resource = new JdbcResultResource(null, stmt.getResultSet(), null); 975       return resource.fetchArray(env, JdbcResultResource.FETCH_NUM); 976 977     } catch (Exception ex) { 978       log.log(Level.FINE, ex.toString(), ex); 979       return null; 980     } 981   } 982 983   /** 984    * Fetches the next row into result-buffer 985    */ 986   public static boolean oci_fetch(Env env, 987                                   @NotNull OracleStatement stmt) 988   { 989     try { 990 991       if (stmt == null) 992         return false; 993 994       JdbcResultResource resource = new JdbcResultResource(null, stmt.getResultSet(), null); 995 996       Value result = resource.fetchArray(env, JdbcResultResource.FETCH_BOTH); 997 998       stmt.setResultBuffer(result); 999 1000      if (!(result instanceof ArrayValue)) { 1001        return false; 1002      } 1003 1004      ArrayValue arrayValue = (ArrayValue) result; 1005 1006      for (Map.Entry <String ,Value> entry : stmt.getByNameVariables().entrySet()) { 1007        String fieldName = entry.getKey(); 1008        Value var = entry.getValue(); 1009 1010        Value newValue = arrayValue.get(StringValue.create(fieldName)); 1011        var.set(newValue); 1012      } 1013 1014      // See oci_num_rows() 1015 stmt.increaseFetchedRows(); 1016 1017      return true; 1018 1019    } catch (Exception ex) { 1020      log.log(Level.FINE, ex.toString(), ex); 1021      try { 1022        stmt.resetByNameVariables(); 1023      } catch (Exception ex2) { 1024        log.log(Level.FINE, ex2.toString(), ex2); 1025      } 1026      return false; 1027    } 1028  } 1029 1030  /** 1031   * Checks if the field is NULL 1032   * 1033   * @param stmt oracle statement 1034   * @param fieldNameOrNumber field's index (1-based) or it's name 1035   * @return TRUE if the field is null or FALSE otherwise. 1036   */ 1037  public static boolean oci_field_is_null(Env env, 1038                                          @NotNull OracleStatement stmt, 1039                                          @NotNull Value fieldNameOrNumber) 1040  { 1041    if (stmt == null) 1042      return false; 1043 1044    try { 1045 1046      ResultSet rs = stmt.getResultSet(); 1047 1048      JdbcResultResource resource = new JdbcResultResource(null, rs, null); 1049 1050      int fieldNumber = resource.getColumnNumber(fieldNameOrNumber, 1); 1051 1052      if (fieldNumber < 0) 1053        return false; 1054 1055      ResultSetMetaData metaData = rs.getMetaData(); 1056 1057      return metaData.isNullable(fieldNumber + 1) == ResultSetMetaData.columnNullable; 1058 1059    } catch (Exception ex) { 1060      log.log(Level.FINE, ex.toString(), ex); 1061      return false; 1062    } 1063  } 1064 1065  /** 1066   * Returns the name of a field from the statement 1067   */ 1068  public static Value oci_field_name(Env env, 1069                                     @NotNull OracleStatement stmt, 1070                                     @NotNull int fieldNumber) 1071  { 1072    try { 1073      if (stmt == null) 1074        return BooleanValue.FALSE; 1075 1076      JdbcResultResource resource = new JdbcResultResource(null, stmt.getResultSet(), null); 1077 1078      return resource.getFieldName(env, fieldNumber); 1079    } catch (Exception ex) { 1080      log.log(Level.FINE, ex.toString(), ex); 1081      return BooleanValue.FALSE; 1082    } 1083  } 1084 1085  /** 1086   * Tell the precision of a field 1087   */ 1088  @ReturnNullAsFalse 1089  public static LongValue oci_field_precision(Env env, 1090                                              @NotNull OracleStatement stmt, 1091                                              @NotNull int field) 1092  { 1093    if (stmt == null) 1094      return null; 1095 1096    try { 1097 1098      ResultSet rs = stmt.getResultSet(); 1099      ResultSetMetaData metaData = rs.getMetaData(); 1100 1101      int precision = metaData.getPrecision(field); 1102      return LongValue.create(precision); 1103 1104    } catch (Exception ex) { 1105      log.log(Level.FINE, ex.toString(), ex); 1106      return null; 1107    } 1108  } 1109 1110  /** 1111   * Tell the scale of the field 1112   */ 1113  @ReturnNullAsFalse 1114  public static LongValue oci_field_scale(Env env, 1115                                          @NotNull OracleStatement stmt, 1116                                          @NotNull int field) 1117  { 1118    if (stmt == null) 1119      return null; 1120 1121    try { 1122 1123      ResultSet rs = stmt.getResultSet(); 1124      ResultSetMetaData metaData = rs.getMetaData(); 1125 1126      int precision = metaData.getScale(field); 1127      return LongValue.create(precision); 1128 1129    } catch (Exception ex) { 1130      log.log(Level.FINE, ex.toString(), ex); 1131      return null; 1132    } 1133  } 1134 1135  /** 1136   * Returns field's size 1137   * 1138   * @param stmt oracle statement 1139   * @param fieldNameOrNumber field's index (1-based) or it's name 1140   * @return the field's size 1141   */ 1142  public static Value oci_field_size(Env env, 1143                                     @NotNull OracleStatement stmt, 1144                                     @Optional("-1") Value fieldNameOrNumber) 1145  { 1146    try { 1147 1148      if (stmt == null) 1149        return BooleanValue.FALSE; 1150 1151      ResultSet rs = stmt.getResultSet(); 1152 1153      JdbcResultResource resource = new JdbcResultResource(null, rs, null); 1154 1155      int fieldNumber = resource.getColumnNumber(fieldNameOrNumber, 1); 1156 1157      return resource.getFieldLength(env, fieldNumber); 1158 1159    } catch (Exception ex) { 1160      log.log(Level.FINE, ex.toString(), ex); 1161      return BooleanValue.FALSE; 1162    } 1163  } 1164 1165  /** 1166   * Tell the raw Oracle data type of the field 1167   * 1168   * @param field the field number (1-based) 1169   */ 1170  public static int oci_field_type_raw(Env env, 1171                                       @NotNull OracleStatement stmt, 1172                                       int field) 1173  { 1174    try { 1175 1176      if (stmt == null) 1177        return -1; 1178 1179      if (field <= 0) 1180        return -1; 1181 1182      JdbcResultResource resource = new JdbcResultResource(null, stmt.getResultSet(), null); 1183 1184      Value typeV = resource.getJdbcType(--field); 1185 1186      if (typeV instanceof LongValue) { 1187 1188        int type = typeV.toInt(); 1189 1190        switch (type) { 1191 1192        case Types.BLOB: 1193        case Types.LONGVARCHAR: 1194        case Types.LONGVARBINARY: 1195          type = SQLT_BLOB; 1196          break; 1197 1198        case Types.CLOB: 1199          type = SQLT_CLOB; 1200          break; 1201 1202        case Types.BIGINT: 1203        case Types.BIT: 1204        case Types.BOOLEAN: 1205        case Types.DECIMAL: 1206        case Types.DOUBLE: 1207        case Types.FLOAT: 1208        case Types.INTEGER: 1209        case Types.NUMERIC: 1210        case Types.REAL: 1211        case Types.SMALLINT: 1212        case Types.TINYINT: 1213          type = SQLT_NUM; 1214          break; 1215 1216        default: 1217          type = SQLT_CHR; 1218        } 1219 1220        return type; 1221      } 1222 1223    } catch (Exception ex) { 1224      log.log(Level.FINE, ex.toString(), ex); 1225    } 1226 1227    return -1; 1228  } 1229 1230  /** 1231   * Returns field's data type 1232   */ 1233  public static Value oci_field_type(Env env, 1234                                     @NotNull OracleStatement stmt, 1235                                     int fieldNumber) 1236  { 1237    try { 1238      if (stmt == null) 1239        return BooleanValue.FALSE; 1240 1241      JdbcResultResource resource = new JdbcResultResource(null, stmt.getResultSet(), null); 1242 1243      return resource.getFieldType(env, fieldNumber); 1244    } catch (Exception ex) { 1245      log.log(Level.FINE, ex.toString(), ex); 1246      return BooleanValue.FALSE; 1247    } 1248  } 1249 1250  /** 1251   * Frees all resources associated with statement or cursor 1252   */ 1253  public static boolean oci_free_statement(Env env, 1254                                           @NotNull OracleStatement stmt) 1255  { 1256    try { 1257 1258      stmt.close(); 1259 1260      return true; 1261 1262    } catch (Exception ex) { 1263      log.log(Level.FINE, ex.toString(), ex); 1264      return false; 1265    } 1266  } 1267 1268  /** 1269   * Enables or disables internal debug output 1270   */ 1271  public static void oci_internal_debug(Env env, 1272                                        @NotNull int onoff) 1273  { 1274    throw new UnimplementedException("oci_internal_debug"); 1275  } 1276 1277  /** 1278   * Copies large object 1279   */ 1280  public static boolean oci_lob_copy(Env env, 1281                                     @NotNull OracleOciLob lobTo, 1282                                     @NotNull OracleOciLob lobFrom, 1283                                     @Optional("-1") int length) 1284  { 1285    try { 1286 1287      return lobTo.save(env, lobFrom.read(env, length).toString(), 0); 1288 1289    } catch (Exception ex) { 1290      log.log(Level.FINE, ex.toString(), ex); 1291      return false; 1292    } 1293  } 1294 1295  /** 1296   * Compares two LOB/FILE locators for equality 1297   */ 1298  public static boolean oci_lob_is_equal(Env env, 1299                                         @NotNull OracleOciLob lob1, 1300                                         @NotNull OracleOciLob lob2) 1301  { 1302    try { 1303 1304      return lob1.equals(lob2); 1305 1306    } catch (Exception ex) { 1307      log.log(Level.FINE, ex.toString(), ex); 1308      return false; 1309    } 1310  } 1311 1312  /** 1313   * Allocates new collection object 1314   */ 1315  @ReturnNullAsFalse 1316  public static OracleOciCollection oci_new_collection(Env env, 1317                                                       @NotNull Oracle conn, 1318                                                       @NotNull String tdo, 1319                                                       @Optional String schema) 1320  { 1321    try { 1322 1323      String typeName = tdo; 1324 1325      if ((schema != null) && (schema.length() > 0)) { 1326        typeName = schema + "." + tdo; 1327      } 1328 1329      // XXX: Is this case ever possible? 1330 // StructDescriptor structDesc = StructDescriptor.createDescriptor(typeName, jdbcConn); 1331 1332      // JDBC underlying connection 1333 Connection jdbcConn = conn.getJavaConnection(); 1334 1335      // Oracle underlying statement 1336 // PreparedStatement oracleStmt = stmt.getPreparedStatement(); 1337 1338      // Use reflection 1339 // ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor(typeName, jdbcConn); 1340 1341      Class clArrayDescriptor = Class.forName("oracle.sql.ArrayDescriptor"); 1342 1343      Method method = clArrayDescriptor.getDeclaredMethod("createDescriptor", 1344                                                          new Class [] {String .class, 1345                                                                       Connection.class}); 1346 1347      Object arrayDesc = method.invoke(clArrayDescriptor, 1348                                       new Object [] {typeName, jdbcConn}); 1349 1350      if (arrayDesc != null) { 1351        return new OracleOciCollection(jdbcConn, arrayDesc); 1352      } 1353 1354    } catch (Exception ex) { 1355      log.log(Level.FINE, ex.toString(), ex); 1356    } 1357 1358    return null; 1359  } 1360 1361  /** 1362   * Establishes a new connection to the Oracle server 1363   */ 1364  public static Value oci_new_connect(Env env, 1365                                      @NotNull String username, 1366                                      @NotNull String password, 1367                                      @Optional String db, 1368                                      @Optional String charset, 1369                                      @Optional("0") int sessionMode) 1370  { 1371    if (!((charset == null) || charset.length() == 0)) { 1372      throw new UnimplementedException("oci_new_connect with charset"); 1373    } 1374 1375    if ((sessionMode == OCI_DEFAULT) || 1376        (sessionMode == OCI_SYSOPER) || 1377        (sessionMode == OCI_SYSDBA)) { 1378      throw new UnimplementedException("oci_new_connect with session mode"); 1379    } 1380 1381    return connectInternal(env, false, username, password, db, charset, sessionMode); 1382  } 1383 1384  /** 1385   * Allocates and returns a new cursor (statement handle) 1386   */ 1387  @ReturnNullAsFalse 1388  public static OracleStatement oci_new_cursor(Env env, 1389                                               @NotNull Oracle conn) 1390  { 1391    try { 1392 1393      OracleStatement stmt = new OracleStatement((Oracle) conn.validateConnection()); 1394 1395      return stmt; 1396 1397    } catch (Exception ex) { 1398      log.log(Level.FINE, ex.toString(), ex); 1399      return null; 1400    } 1401  } 1402 1403  /** 1404   * Initializes a new empty LOB or FILE descriptor 1405   * 1406   * @param type one of the following types: 1407   * 1408   * OCI_D_FILE - a FILE descriptor 1409   * 1410   * OCI_D_LOB - a LOB descriptor 1411   * 1412   * OCI_D_ROWID - a ROWID descriptor 1413   */ 1414  @ReturnNullAsFalse 1415  public static OracleOciLob oci_new_descriptor(Env env, 1416                                                @NotNull Oracle conn, 1417                                                @Optional("-1") int type) 1418  { 1419    try { 1420 1421      if ((type == OCI_D_FILE) || 1422          (type == OCI_D_LOB) || 1423          (type == OCI_D_ROWID)) { 1424 1425        OracleOciLob oracleLob = new OracleOciLob(conn, type); 1426 1427        return oracleLob; 1428      } 1429 1430    } catch (Exception ex) { 1431      log.log(Level.FINE, ex.toString(), ex); 1432    } 1433 1434    return null; 1435  } 1436 1437  /** 1438   * Returns the number of result columns in a statement 1439   */ 1440  public static Value oci_num_fields(Env env, 1441                                     @NotNull OracleStatement stmt) 1442  { 1443    try { 1444      if (stmt == null) 1445        return BooleanValue.FALSE; 1446 1447      JdbcResultResource resource = new JdbcResultResource(null, stmt.getResultSet(), null); 1448 1449      return LongValue.create(resource.getFieldCount()); 1450    } catch (Exception ex) { 1451      log.log(Level.FINE, ex.toString(), ex); 1452      return BooleanValue.FALSE; 1453    } 1454  } 1455 1456  /** 1457   * Returns number of rows affected during statement execution 1458   * 1459   * Note: This function does not return number of rows selected! 1460   * For SELECT statements this function will return the number of rows, 1461   * that were fetched to the buffer with oci_fetchxxxx() functions. 1462   */ 1463  @ReturnNullAsFalse 1464  public static LongValue oci_num_rows(Env env, 1465                                       @NotNull OracleStatement stmt) 1466  { 1467    try { 1468 1469      if (stmt == null) 1470        return null; 1471 1472      // JdbcResultResource resource = new JdbcResultResource(null, stmt.getResultSet(), null); 1473 1474      // return LongValue.create(resource.getNumRows()); 1475 1476      return LongValue.create(stmt.getFetchedRows()); 1477 1478    } catch (Exception ex) { 1479      log.log(Level.FINE, ex.toString(), ex); 1480      return null; 1481    } 1482  } 1483 1484  /** 1485   * Prepares Oracle statement for execution 1486   */ 1487  @ReturnNullAsFalse 1488  public static OracleStatement oci_parse(Env env, 1489                                          @NotNull Oracle conn, 1490                                          String query) 1491  { 1492    try { 1493      // XXX: Rework this. 1494 // Enclose the query with "begin ...; end;" so any regular statement 1495 // or stored procedure call can be executed with a CallableStatement. 1496 query = query.trim(); 1497 1498      String lowerCaseQuery = query.toLowerCase(); 1499 1500      if (lowerCaseQuery.startsWith("insert") || 1501          lowerCaseQuery.startsWith("update") || 1502          lowerCaseQuery.startsWith("delete")) { 1503        if (!lowerCaseQuery.startsWith("begin ")) { 1504          query = "begin " + query; 1505        } 1506 1507        if (!lowerCaseQuery.endsWith(";")) { 1508          query += ";"; 1509        } 1510 1511        if (!lowerCaseQuery.endsWith(" end;")) { 1512          query += " end;"; 1513        } 1514      } 1515 1516      // Make the PHP query a JDBC like query replacing (:mydata -> ?) with question marks. 1517 // Store binding names for future reference (see oci_execute) 1518 String regex = ":[a-zA-Z0-9_]+"; 1519      String jdbcQuery = query.replaceAll(regex, "?"); 1520      OracleStatement pstmt = conn.prepare(env, jdbcQuery); 1521 1522      Pattern pattern = Pattern.compile(regex); 1523      Matcher matcher = pattern.matcher(query); 1524      int i = 0; 1525      while (matcher.find()) { 1526        String group = matcher.group(); 1527        pstmt.putBindingVariable(group, new Integer (++i)); 1528      } 1529 1530      return pstmt; 1531 1532    } catch (Exception ex) { 1533      log.log(Level.FINE, ex.toString(), ex); 1534      return null; 1535    } 1536  } 1537 1538  /** 1539   * Changes password of Oracle's user 1540   */ 1541  public static boolean oci_password_change(Env env, 1542                                            @NotNull Oracle conn, 1543                                            @NotNull String username, 1544                                            @NotNull String oldPassword, 1545                                            @NotNull String newPassword) 1546  { 1547    try { 1548 1549      // XXX: When is oldPassword used? 1550 1551      if (conn == null) 1552        return false; 1553 1554      OracleStatement oracleStmt; 1555 1556      oracleStmt = oci_parse(env, conn, "ALTER USER "+username+" IDENTIFIED BY "+newPassword); 1557 1558      oci_execute(env, oracleStmt, 0); 1559 1560      return true; 1561 1562    } catch (Exception ex) { 1563      log.log(Level.FINE, ex.toString(), ex); 1564      return false; 1565    } 1566  } 1567 1568  /** 1569   * Connect to an Oracle database using a persistent connection 1570   */ 1571  public static Value oci_pconnect(Env env, 1572                                   @NotNull String username, 1573                                   @NotNull String password, 1574                                   @Optional String db, 1575                                   @Optional String charset, 1576                                   @Optional("0") int sessionMode) 1577  { 1578    if (!((charset == null) || charset.length() == 0)) { 1579      throw new UnimplementedException("oci_pconnect with charset"); 1580    } 1581 1582    if ((sessionMode == OCI_DEFAULT) || 1583        (sessionMode == OCI_SYSOPER) || 1584        (sessionMode == OCI_SYSDBA)) { 1585      throw new UnimplementedException("oci_pconnect with session mode"); 1586    } 1587 1588    return connectInternal(env, true, username, password, db, charset, sessionMode); 1589  } 1590 1591  /** 1592   * Returns field's value from the fetched row 1593   */ 1594  public static Value oci_result(Env env, 1595                                 @NotNull OracleStatement stmt, 1596                                 @NotNull Value field) 1597  { 1598    try { 1599      if (stmt == null) 1600        return BooleanValue.FALSE; 1601 1602      Value result = stmt.getResultBuffer(); 1603 1604      return ((ArrayValueImpl)result).get(field); 1605    } catch (Exception ex) { 1606      log.log(Level.FINE, ex.toString(), ex); 1607      return BooleanValue.FALSE; 1608    } 1609  } 1610 1611  /** 1612   * Rolls back outstanding transaction 1613   */ 1614  public static Value oci_rollback(Env env, 1615                                   @NotNull Oracle conn) 1616  { 1617    try { 1618      return BooleanValue.create(conn.rollback()); 1619    } catch (Exception ex) { 1620      log.log(Level.FINE, ex.toString(), ex); 1621      return BooleanValue.FALSE; 1622    } 1623  } 1624 1625  /** 1626   * Returns server version 1627   */ 1628  @ReturnNullAsFalse 1629  public static String oci_server_version(Env env, 1630                                          @NotNull Oracle conn) 1631  { 1632    try { 1633      if (conn == null) 1634        conn = getConnection(env); 1635 1636      return conn.getServerInfo(); 1637    } catch (Exception ex) { 1638      log.log(Level.FINE, ex.toString(), ex); 1639      return null; 1640    } 1641  } 1642 1643  /** 1644   * Sets number of rows to be prefetched 1645   */ 1646  public static boolean oci_set_prefetch(Env env, 1647                                         @NotNull OracleStatement stmt, 1648                                         @Optional("1") int rows) 1649  { 1650    try { 1651 1652      if (stmt == null) 1653        return false; 1654 1655      PreparedStatement pstmt = stmt.getPreparedStatement(); 1656      pstmt.setFetchSize(rows); 1657 1658      return true; 1659 1660    } catch (Exception ex) { 1661      log.log(Level.FINE, ex.toString(), ex); 1662      return false; 1663    } 1664  } 1665 1666  /** 1667   * Returns the type of an OCI statement 1668   */ 1669  public static String oci_statement_type(Env env, 1670                                          @NotNull OracleStatement stmt) 1671  { 1672    return stmt.getStatementType(); 1673  } 1674 1675  /** 1676   * Alias of oci_bind_by_name() 1677   */ 1678  public static boolean ocibindbyname(Env env, 1679                                      @NotNull OracleStatement stmt, 1680                                      @NotNull String variable, 1681                                      @NotNull Value value, 1682                                      @Optional("0") int maxLength, 1683                                      @Optional("0") int type) 1684  { 1685    return oci_bind_by_name(env, stmt, variable, value, maxLength, type); 1686  } 1687 1688  /** 1689   * Alias of oci_cancel() 1690   */ 1691  public static boolean ocicancel(Env env, 1692                                  @NotNull OracleStatement stmt) 1693  { 1694    return oci_cancel(env, stmt); 1695  } 1696 1697  /** 1698   * Alias of OCI-Lob->close 1699   */ 1700  public static Value ocicloselob(Env env, 1701                                  @NotNull Oracle conn) 1702  { 1703    throw new UnimplementedException("ocicloselob"); 1704  } 1705 1706  /** 1707   * Alias of OCI-Collection->append 1708   */ 1709  public static Value ocicollappend(Env env, 1710                                    @NotNull Oracle conn) 1711  { 1712    throw new UnimplementedException("ocicollappend"); 1713  } 1714 1715  /** 1716   * Alias of OCI-Collection->assign 1717   */ 1718  public static Value ocicollassign(Env env, 1719                                    @NotNull Oracle conn) 1720  { 1721    throw new UnimplementedException("ocicollassign"); 1722  } 1723 1724  /** 1725   * Alias of OCI-Collection->assignElem 1726   */ 1727  public static Value ocicollassignelem(Env env, 1728                                        @NotNull Oracle conn) 1729  { 1730    throw new UnimplementedException("ocicollassignelem"); 1731  } 1732 1733  /** 1734   * Alias of OCI-Collection->getElem 1735   */ 1736  public static Value ocicollgetelem(Env env, 1737                                     @NotNull Oracle conn) 1738  { 1739    throw new UnimplementedException("ocicollgetelem"); 1740  } 1741 1742  /** 1743   * Alias of OCI-Collection->max 1744   */ 1745  public static Value ocicollmax(Env env, 1746                                 @NotNull Oracle conn) 1747  { 1748    throw new UnimplementedException("ocicollmax"); 1749  } 1750 1751  /** 1752   * Alias of OCI-Collection->size 1753   */ 1754  public static Value ocicollsize(Env env, 1755                                  @NotNull Oracle conn) 1756  { 1757    throw new UnimplementedException("ocicollsize"); 1758  } 1759 1760  /** 1761   * Alias of OCI-Collection->trim 1762   */ 1763  public static Value ocicolltrim(Env env, 1764                                  @NotNull Oracle conn) 1765  { 1766    throw new UnimplementedException("ocicolltrim"); 1767  } 1768 1769  /** 1770   * Alias of oci_field_is_null() 1771   */ 1772  public static boolean ocicolumnisnull(Env env, 1773                                        @NotNull OracleStatement stmt, 1774                                        @NotNull Value field) 1775  { 1776    return oci_field_is_null(env, stmt, field); 1777  } 1778 1779  /** 1780   * Alias of oci_field_name() 1781   */ 1782  public static Value ocicolumnname(Env env, 1783                                    @NotNull OracleStatement stmt, 1784                                    @NotNull int fieldNumber) 1785  { 1786    return oci_field_name(env, stmt, fieldNumber); 1787  } 1788 1789  /** 1790   * Alias of oci_field_precision() 1791   */ 1792  public static Value ocicolumnprecision(Env env, 1793                                         @NotNull OracleStatement stmt, 1794                                         @NotNull int field) 1795  { 1796    return oci_field_precision(env, stmt, field); 1797  } 1798 1799  /** 1800   * Alias of oci_field_scale() 1801   */ 1802  public static Value ocicolumnscale(Env env, 1803                                     @NotNull OracleStatement stmt, 1804                                     @NotNull int field) 1805  { 1806    return oci_field_scale(env, stmt, field); 1807  } 1808 1809  /** 1810   * Alias of oci_field_size() 1811   */ 1812  public static Value ocicolumnsize(Env env, 1813                                    @NotNull OracleStatement stmt, 1814                                    @Optional Value field) 1815  { 1816    return oci_field_size(env, stmt, field); 1817  } 1818 1819  /** 1820   * Alias of oci_field_type() 1821   */ 1822  public static Value ocicolumntype(Env env, 1823                                    @NotNull OracleStatement stmt, 1824                                    @Optional int fieldNumber) 1825  { 1826    return oci_field_type(env, stmt, fieldNumber); 1827  } 1828 1829  /** 1830   * Alias of oci_field_type_raw() 1831   */ 1832  public static int ocicolumntyperaw(Env env, 1833                                     @NotNull OracleStatement stmt, 1834                                     @Optional int field) 1835  { 1836    return oci_field_type_raw(env, stmt, field); 1837  } 1838 1839  /** 1840   * Alias of oci_commit() 1841   */ 1842  public static boolean ocicommit(Env env, 1843                                  @NotNull Oracle conn) 1844  { 1845    return oci_commit(env, conn); 1846  } 1847 1848  /** 1849   * Alias of oci_define_by_name() 1850   */ 1851  public static boolean ocidefinebyname(Env env, 1852                                        @NotNull OracleStatement stmt, 1853                                        @NotNull String columnName, 1854                                        @NotNull Value variable, 1855                                        @Optional("0") int type) 1856  { 1857    return oci_define_by_name(env, stmt, columnName, variable, type); 1858  } 1859 1860  /** 1861   * Alias of oci_error() 1862   */ 1863  public static String ocierror(Env env, 1864                                @Optional Value resource) 1865  { 1866    return oci_error(env, resource); 1867  } 1868 1869  /** 1870   * Alias of oci_execute() 1871   */ 1872  public static boolean ociexecute(Env env, 1873                                   @NotNull OracleStatement stmt, 1874                                   @Optional("0") int mode) 1875  { 1876    return oci_execute(env, stmt, mode); 1877  } 1878 1879  /** 1880   * Alias of oci_fetch() 1881   */ 1882  public static boolean ocifetch(Env env, 1883                                 @NotNull OracleStatement stmt) 1884  { 1885    return oci_fetch(env, stmt); 1886  } 1887 1888  /** 1889   * Fetches the next row into an array 1890   */ 1891  public static Value ocifetchinto(Env env, 1892                                   @NotNull Oracle conn) 1893  { 1894    throw new UnimplementedException("ocifetchinto"); 1895  } 1896 1897  /** 1898   * Alias of oci_fetch_all() 1899   */ 1900  public static Value ocifetchstatement(Env env, 1901                                        @NotNull OracleStatement stmt, 1902                                        @NotNull Value output, 1903                                        @Optional int skip, 1904                                        @Optional int maxrows, 1905                                        @Optional int flags) 1906  { 1907    return oci_fetch_all(env, stmt, output, skip, maxrows, flags); 1908  } 1909 1910  /** 1911   * Alias of OCI-Collection->free 1912   */ 1913  public static Value ocifreecollection(Env env, 1914                                        @NotNull Oracle conn) 1915  { 1916    throw new UnimplementedException("ocifreecollection"); 1917  } 1918 1919  /** 1920   * Alias of oci_free_statement() 1921   */ 1922  public static boolean ocifreecursor(Env env, 1923                                      @NotNull OracleStatement stmt) 1924  { 1925    return oci_free_statement(env, stmt); 1926  } 1927 1928  /** 1929   * Alias of OCI-Lob->free 1930   */ 1931  public static Value ocifreedesc(Env env, 1932                                  @NotNull Oracle conn) 1933  { 1934    throw new UnimplementedException("ocifreedesc"); 1935  } 1936 1937  /** 1938   * Alias of oci_free_statement() 1939   */ 1940  public static boolean ocifreestatement(Env env, 1941                                         @NotNull OracleStatement stmt) 1942  { 1943    return oci_free_statement(env, stmt); 1944  } 1945 1946  /** 1947   * Alias of oci_internal_debug() 1948   */ 1949  public static void ociinternaldebug(Env env, 1950                                      @NotNull int onoff) 1951  { 1952    oci_internal_debug(env, onoff); 1953  } 1954 1955  /** 1956   * Alias of OCI-Lob->load 1957   */ 1958  public static Value ociloadlob(Env env, 1959                                 @NotNull Oracle conn) 1960  { 1961    throw new UnimplementedException("ociloadlob"); 1962  } 1963 1964  /** 1965   * Alias of oci_close() 1966   */ 1967  public static boolean ocilogoff(Env env, 1968                                  @NotNull Oracle conn) 1969  { 1970    return oci_close(env, conn); 1971  } 1972 1973  /** 1974   * Alias of oci_connect() 1975   */ 1976  public static Value ocilogon(Env env, 1977                               @NotNull String username, 1978                               @NotNull String password, 1979                               @Optional String db, 1980                               @Optional String charset, 1981                               @Optional("0") int sessionMode) 1982  { 1983    return oci_connect(env, username, password, db, charset, sessionMode); 1984  } 1985 1986  /** 1987   * Alias of oci_new_collection() 1988   */ 1989  @ReturnNullAsFalse 1990  public static OracleOciCollection ocinewcollection(Env env, 1991                                                     @NotNull Oracle conn, 1992                                                     @NotNull String tdo, 1993                                                     @Optional String schema) 1994  { 1995    return oci_new_collection(env, conn, tdo, schema); 1996  } 1997 1998  /** 1999   * Alias of oci_new_cursor() 2000   */ 2001  @ReturnNullAsFalse 2002  public static OracleStatement ocinewcursor(Env env, 2003                                             @NotNull Oracle conn) 2004  { 2005    return oci_new_cursor(env, conn); 2006  } 2007 2008  /** 2009   * Alias of oci_new_descriptor() 2010   */ 2011  public static OracleOciLob ocinewdescriptor(Env env, 2012                                              @NotNull Oracle conn, 2013                                              @Optional("-1") int type) 2014  { 2015    return oci_new_descriptor(env, conn, type); 2016  } 2017 2018  /** 2019   * Alias of oci_new_connect() 2020   */ 2021  public static Value ocinlogon(Env env, 2022                                @NotNull String username, 2023                                @NotNull String password, 2024                                @Optional String db, 2025                                @Optional String charset, 2026                                @Optional("0") int sessionMode) 2027  { 2028    return oci_new_connect(env, username, password, db, charset, sessionMode); 2029  } 2030 2031  /** 2032   * Alias of oci_num_fields() 2033   */ 2034  public static Value ocinumcols(Env env, 2035                                 @NotNull OracleStatement stmt) 2036  { 2037    return oci_num_fields(env, stmt); 2038  } 2039 2040  /** 2041   * Alias of oci_parse() 2042   */ 2043  @ReturnNullAsFalse 2044  public static OracleStatement ociparse(Env env, 2045                                         @NotNull Oracle conn, 2046                                         @NotNull String query) 2047  { 2048    return oci_parse(env, conn, query); 2049  } 2050 2051  /** 2052   * Alias of oci_pconnect() 2053   */ 2054  public static Value ociplogon(Env env, 2055                                @NotNull String username, 2056                                @NotNull String password, 2057                                @Optional String db, 2058                                @Optional String charset, 2059                                @Optional("0") int sessionMode) 2060  { 2061    return oci_pconnect(env, username, password, db, charset, sessionMode); 2062  } 2063 2064  /** 2065   * Alias of oci_result() 2066   */ 2067  public static Value ociresult(Env env, 2068                                @NotNull OracleStatement stmt, 2069                                @NotNull Value field) 2070  { 2071    return oci_result(env, stmt, field); 2072  } 2073 2074  /** 2075   * Alias of oci_rollback() 2076   */ 2077  public static Value ocirollback(Env env, 2078                                  @NotNull Oracle conn) 2079  { 2080    return oci_rollback(env, conn); 2081  } 2082 2083  /** 2084   * Alias of oci_num_rows() 2085   */ 2086  public static Value ocirowcount(Env env, 2087                                  @NotNull OracleStatement stmt) 2088  { 2089    return oci_num_rows(env, stmt); 2090  } 2091 2092  /** 2093   * Alias of OCI-Lob->save 2094   */ 2095  public static Value ocisavelob(Env env, 2096                                 @NotNull Oracle conn) 2097  { 2098    throw new UnimplementedException("ocisavelob"); 2099  } 2100 2101  /** 2102   * Alias of OCI-Lob->import 2103   */ 2104  public static Value ocisavelobfile(Env env, 2105                                     @NotNull Oracle conn) 2106  { 2107    throw new UnimplementedException("ocisavelobfile"); 2108  } 2109 2110  /** 2111   * Alias of oci_server_version() 2112   */ 2113  public static String ociserverversion(Env env, 2114                                        @NotNull Oracle conn) 2115  { 2116    return oci_server_version(env, conn); 2117  } 2118 2119  /** 2120   * Alias of oci_set_prefetch() 2121   */ 2122  public static boolean ocisetprefetch(Env env, 2123                                       @NotNull OracleStatement stmt, 2124                                       @Optional("1") int rows) 2125  { 2126    return oci_set_prefetch(env, stmt, rows); 2127  } 2128 2129  /** 2130   * Alias of oci_statement_type() 2131   */ 2132  public static String ocistatementtype(Env env, 2133                                        @NotNull OracleStatement stmt) 2134  { 2135    return oci_statement_type(env, stmt); 2136  } 2137 2138  /** 2139   * Alias of OCI-Lob->export 2140   */ 2141  public static Value ociwritelobtofile(Env env, 2142                                        @NotNull Oracle conn) 2143  { 2144    throw new UnimplementedException("ociwritelobtofile"); 2145  } 2146 2147  /** 2148   * Alias of OCI-Lob->writeTemporary 2149   */ 2150  public static Value ociwritetemporarylob(Env env, 2151                                           @NotNull Oracle conn) 2152  { 2153    throw new UnimplementedException("ociwritetemporarylob"); 2154  } 2155 2156  private static Oracle getConnection(Env env) 2157  { 2158    Oracle conn = null; 2159 2160    ConnectionInfo connectionInfo = (ConnectionInfo) env.getSpecialValue("caucho.oracle"); 2161 2162    if (connectionInfo != null) { 2163      // Reuse the cached connection 2164 conn = connectionInfo.getConnection(); 2165      return conn; 2166    } 2167 2168    String driver = "oracle.jdbc.OracleDriver"; 2169    String url = "jdbc:oracle:thin:@localhost:1521"; 2170 2171    conn = new Oracle(env, "localhost", "", "", "", 1521, driver, url); 2172 2173    env.setSpecialValue("caucho.oracle", conn); 2174 2175    return conn; 2176  } 2177 2178  private static Value connectInternal(Env env, 2179                                       boolean reuseConnection, 2180                                       String username, 2181                                       String password, 2182                                       String db, 2183                                       String charset, 2184                                       int sessionMode) 2185  { 2186    String host = "localhost"; 2187    int port = 1521; 2188 2189    String driver = "oracle.jdbc.OracleDriver"; 2190 2191    String url; 2192 2193    if (db.indexOf("//") == 0) { 2194      // db is the url itself: "//db_host[:port]/database_name" 2195 url = "jdbc:oracle:thin:@" + db.substring(2); 2196      url = url.replace('/', ':'); 2197    } else { 2198      url = "jdbc:oracle:thin:@" + host + ":" + port + ":" + db; 2199    } 2200 2201    Oracle conn = null; 2202 2203    ConnectionInfo connectionInfo = (ConnectionInfo)env.getSpecialValue("caucho.oracle"); 2204 2205    if (reuseConnection && (connectionInfo != null) && url.equals(connectionInfo.getUrl())) { 2206      // Reuse the cached connection 2207 conn = connectionInfo.getConnection(); 2208    } else { 2209      conn = new Oracle(env, host, username, password, db, port, driver, url); 2210 2211      if (! conn.isConnected()) 2212        return BooleanValue.FALSE; 2213 2214      connectionInfo = new ConnectionInfo(url, conn); 2215 2216      env.setSpecialValue("caucho.oracle", connectionInfo); 2217    } 2218 2219    Value value = env.wrapJava(conn); 2220 2221    return value; 2222  } 2223 2224  private static class ConnectionInfo { 2225    private String _url; 2226    private Oracle _conn; 2227 2228    public ConnectionInfo(String url, Oracle conn) 2229    { 2230      _url = url; 2231      _conn = conn; 2232    } 2233 2234    public String getUrl() 2235    { 2236      return _url; 2237    } 2238 2239    public Oracle getConnection() 2240    { 2241      return _conn; 2242    } 2243  } 2244} 2245

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