Java Examples for oracle.sql.CLOB

/**
	 * 将clob转化成String
	 * @param in 
	 * @return
	 * @throws ServiceException
	 */
public static String ClobToString(Object in) throws ServiceException {
    String reString = "";
    oracle.sql.CLOB clob = null;
    try {
        if ("oracle.sql.CLOB".equals(in.getClass().getName())) {
            clob = (oracle.sql.CLOB) in;
        } else if ("weblogic.jdbc.wrapper.Clob_oracle_sql_CLOB".equals(in.getClass().getName())) {
            Method method = in.getClass().getMethod("getVendorObj", new Class[] {});
            clob = (oracle.sql.CLOB) method.invoke(in);
        }
        Reader is = // 得到流
        clob.getCharacterStream();
        BufferedReader br = new BufferedReader(is);
        String s = br.readLine();
        StringBuffer sb = new StringBuffer();
        while (// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
        s != null) {
            sb.append(s);
            s = br.readLine();
        }
        br.close();
        reString = sb.toString();
    } catch (SQLException e) {
        throw new ServiceException(e.getMessage());
    } catch (Exception e) {
        throw new ServiceException(e.getMessage());
    }
    return reString;
}

@Test
public void shouldReturnContentsOfClobIFAllOkay() throws SQLException {
    CLOB clob = mock(CLOB.class);
    when(clob.length()).thenReturn(Long.valueOf("CLOB contents".length()));
    when(clob.getChars(eq(1l), eq(10000), any(char[].class))).thenReturn("CLOB contents".length());
    // can't do this as we don't fill up the passed buffer
    //assertEquals("CLOB contents", new OracleClobNormaliser().normalise(clob));
    new OracleClobNormaliser().transform(clob);
}

public Object normalise(Object o) throws SQLException {
    if (o == null)
        return null;
    if (!(o instanceof oracle.sql.CLOB)) {
        throw new UnsupportedOperationException("OracleClobNormaliser cannot work with " + o.getClass());
    }
    oracle.sql.CLOB clob = (oracle.sql.CLOB) o;
    if (clob.length() > MAX_CLOB_LENGTH)
        throw new UnsupportedOperationException("Clobs larger than " + MAX_CLOB_LENGTH + "bytes are not supported by DBFIT");
    InputStream is = clob.getStream();
    String value = ResourceHelper.readFromStream(is);
    return value;
}

void replaceScriptLob(GString gstring) throws SQLException {
    Object[] values = gstring.getValues();
    for (int i = 0; i < values.length; i++) {
        Object o = values[i];
        if (o instanceof ScriptClob) {
            ScriptClob scriptClob = (ScriptClob) o;
            if (scriptClob.isEmpty()) {
                values[i] = null;
            } else {
                oracle.sql.CLOB oracleCLOB;
                try {
                    oracleCLOB = oracle.sql.CLOB.createTemporary(unwrappedJBossConnection(), false, oracle.sql.CLOB.DURATION_SESSION);
                } catch (Exception e) {
                    oracleCLOB = oracle.sql.CLOB.createTemporary(unwrappedDbcpConnection(), false, oracle.sql.CLOB.DURATION_SESSION);
                }
                try {
                    writeClobData(oracleCLOB, scriptClob);
                } catch (IOException e) {
                    throw new SQLException("Exception while writing CLob: ", e);
                }
                values[i] = oracleCLOB;
            }
        } else if (o instanceof ScriptBlob) {
            ScriptBlob scriptBlob = (ScriptBlob) o;
            if (scriptBlob.isEmpty()) {
                values[i] = null;
            } else {
                oracle.sql.BLOB oracleBLOB;
                try {
                    oracleBLOB = oracle.sql.BLOB.createTemporary(unwrappedJBossConnection(), false, oracle.sql.BLOB.DURATION_SESSION);
                } catch (Exception e) {
                    oracleBLOB = oracle.sql.BLOB.createTemporary(unwrappedDbcpConnection(), false, oracle.sql.BLOB.DURATION_SESSION);
                }
                try {
                    writeBlobData(oracleBLOB, scriptBlob);
                } catch (IOException e) {
                    throw new SQLException("Exception while writing BLob: ", e);
                }
                values[i] = oracleBLOB;
            }
        }
    }
}

public int getJavaTypecode(int sqlTypecode) {
    if (!m_mapInitialized) {
        javaMapping();
        m_mapInitialized = true;
    }
    ;
    switch(sqlTypecode) {
        case OracleTypes.ARRAY:
            return arrayMap;
        case OracleTypes.BFILE:
            return bfileMap;
        case OracleTypes.BINARY:
            return binaryMap;
        case OracleTypes.BLOB:
            return blobMap;
        case OracleTypes.CHAR:
            return charMap;
        case OracleTypes.CLOB:
            return clobMap;
        case OracleTypes.CURSOR:
            return cursorMap;
        case OracleTypes.DATE:
            return dateMap;
        case OracleTypes.TIME:
            return timeMap;
        case OracleTypes.TIMESTAMP:
            return timestampMap;
        case ORACLE_TYPES_TIMESTAMPTZ:
            return (timestampMap == JS_TIMESTAMP) ? timestampMap : OS_TIMESTAMPTZ;
        case ORACLE_TYPES_TIMESTAMPLTZ:
            return OS_TIMESTAMPLTZ;
        case OracleTypes.INTERVALYM:
            return intervalYMMap;
        case OracleTypes.INTERVALDS:
            return intervalDSMap;
        case OracleTypes.DECIMAL:
            return decimalMap;
        case OracleTypes.DOUBLE:
            return doubleMap;
        case OracleTypes.FLOAT:
            return floatMap;
        case OracleTypes.INTEGER:
            return integerMap;
        case OracleTypes.LONGVARBINARY:
            return longVarBinaryMap;
        case OracleTypes.NUMERIC:
            return numericMap;
        case ORACLE_TYPES_OPAQUE:
            return OS_CUSTOMDATUM;
        case ORACLE_TYPES_JAVA_STRUCT:
            return structMap;
        case OracleTypes.REAL:
            return realMap;
        case OracleTypes.REF:
            return refMap;
        case OracleTypes.ROWID:
            return rowidMap;
        case OracleTypes.SMALLINT:
            return smallintMap;
        case OracleTypes.STRUCT:
            return structMap;
        case OracleTypes.VARCHAR:
            return varcharMap;
        case OracleTypes.TABLE:
            return tableMap;
        case SqlType.ORACLE_TYPES_NCHAR:
            return ncharMap;
        case SqlType.ORACLE_TYPES_NCLOB:
            return OS_NCLOB;
        case SqlType.ORACLE_TYPES_BOOLEAN:
            return BOOLEAN;
        case OracleTypes.PLSQL_INDEX_TABLE:
            return tableMap;
        case OracleTypes.PLSQL_NESTED_TABLE:
            return tableMap;
        case OracleTypes.PLSQL_VARRAY_TABLE:
            return tableMap;
        case ORACLE_TYPES_BINARY_FLOAT:
            return binaryFloatMap;
        case ORACLE_TYPES_BINARY_DOUBLE:
            return binaryDoubleMap;
        default:
            return UNSUPPORTED_TYPE;
    }
}

@Before
public void setUp() throws SQLException, IOException, MjdbcException {
    MockitoAnnotations.initMocks(this);
    when(stmt.getConnection()).thenReturn(conn);
    when(conn.createARRAY(any(String.class), any(Object[].class))).thenReturn(array);
    when(MappingUtils.invokeFunction(conn, "createBlob", new Class[] {}, new Object[] {})).thenReturn(blob);
    when(MappingUtils.invokeFunction(conn, "createClob", new Class[] {}, new Object[] {})).thenReturn(clob);
    when(blob.setBinaryStream(1)).thenReturn(output);
    when(clob.setAsciiStream(1)).thenReturn(output);
    when(input.read(any(byte[].class))).thenReturn(-1);
    when(input.read(any(byte[].class), any(int.class), any(int.class))).thenReturn(-1);
    when(blob.getBinaryStream()).thenReturn(input);
    when(clob.getAsciiStream()).thenReturn(input);
    params = new QueryParameters();
    params.set("array_list", Arrays.asList("Superman"), MjdbcTypes.ARRAY);
    params.set("blob_byte", "Batman", MjdbcTypes.BLOB);
    params.set("clob_byte", "Wolverine", MjdbcTypes.CLOB);
    params.set("array", array, MjdbcTypes.ARRAY);
    params.set("blob", blob, MjdbcTypes.BLOB);
    params.set("clob", clob, MjdbcTypes.CLOB);
}

public int doUpdate(SBulletin bul) throws Exception {
    String sql = "update s_bulletin " + " set BULLETIN_TITLE = ?, " + //				" CREATE_DATE = ?, " +
    " BULLETIN_CONTENT= ?, " + //				" OPTR_ID= ?, " +
    " EFF_DATE= ?, " + " EXP_DATE= ? " + " where bulletin_id = ?";
    Connection conn = this.getConnection();
    PreparedStatement pst = null;
    int result = 0;
    try {
        pst = conn.prepareStatement(sql);
        //BULLETIN_TITLE
        pst.setString(1, bul.getBulletin_title());
        C3P0NativeJdbcExtractor cp30NativeJdbcExtractor = new C3P0NativeJdbcExtractor();
        OracleConnection oracleConn = (OracleConnection) cp30NativeJdbcExtractor.getNativeConnection(pst.getConnection());
        CLOB clob = oracle.sql.CLOB.createTemporary(oracleConn, false, oracle.sql.CLOB.DURATION_SESSION);
        clob.putString(1, bul.getBulletin_content());
        //BULLETIN_CONTENT
        pst.setClob(2, clob);
        //EFF_DATE
        pst.setDate(//EFF_DATE
        3, //EFF_DATE
        new java.sql.Date(bul.getEff_date().getTime()));
        //EXP_DATE
        pst.setDate(//EXP_DATE
        4, //EXP_DATE
        new java.sql.Date(bul.getExp_date().getTime()));
        pst.setString(5, bul.getBulletin_id());
        result = pst.executeUpdate();
    } catch (SQLException ex) {
        JdbcUtils.closeStatement(pst);
        pst = null;
        DataSourceUtils.releaseConnection(conn, getDataSource());
        conn = null;
        throw ex;
    } finally {
        JdbcUtils.closeStatement(pst);
        DataSourceUtils.releaseConnection(conn, getDataSource());
    }
    return result;
}

/**
	 * Retrieve the {@code oracle.sql.BLOB} and {@code oracle.sql.CLOB}
	 * classes via reflection, and initialize the values for the
	 * DURATION_SESSION, MODE_READWRITE and MODE_READONLY constants defined there.
	 * <p><strong>See Also:</strong>
	 * <ul>
	 * <li><a href="http://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/sql/BLOB.html#DURATION_SESSION">oracle.sql.BLOB.DURATION_SESSION</a></li>
	 * <li><a href="http://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/sql/BLOB.html#MODE_READWRITE">oracle.sql.BLOB.MODE_READWRITE</a></li>
	 * <li><a href="http://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/sql/BLOB.html#MODE_READONLY">oracle.sql.BLOB.MODE_READONLY</a></li>
	 * <li><a href="http://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/sql/CLOB.html#DURATION_SESSION">oracle.sql.CLOB.DURATION_SESSION</a></li>
	 * <li><a href="http://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/sql/CLOB.html#MODE_READWRITE">oracle.sql.CLOB.MODE_READWRITE</a></li>
	 * <li><a href="http://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/sql/CLOB.html#MODE_READONLY">oracle.sql.CLOB.MODE_READONLY</a></li>
	 * </ul>
	 * @param con the Oracle Connection, for using the exact same class loader
	 * that the Oracle driver was loaded with
	 */
protected synchronized void initOracleDriverClasses(Connection con) {
    if (this.blobClass == null) {
        try {
            // Initialize oracle.sql.BLOB class
            this.blobClass = con.getClass().getClassLoader().loadClass(BLOB_CLASS_NAME);
            this.durationSessionConstants.put(this.blobClass, this.blobClass.getField(DURATION_SESSION_FIELD_NAME).getInt(null));
            this.modeReadWriteConstants.put(this.blobClass, this.blobClass.getField(MODE_READWRITE_FIELD_NAME).getInt(null));
            this.modeReadOnlyConstants.put(this.blobClass, this.blobClass.getField(MODE_READONLY_FIELD_NAME).getInt(null));
            // Initialize oracle.sql.CLOB class
            this.clobClass = con.getClass().getClassLoader().loadClass(CLOB_CLASS_NAME);
            this.durationSessionConstants.put(this.clobClass, this.clobClass.getField(DURATION_SESSION_FIELD_NAME).getInt(null));
            this.modeReadWriteConstants.put(this.clobClass, this.clobClass.getField(MODE_READWRITE_FIELD_NAME).getInt(null));
            this.modeReadOnlyConstants.put(this.clobClass, this.clobClass.getField(MODE_READONLY_FIELD_NAME).getInt(null));
        } catch (Exception ex) {
            throw new InvalidDataAccessApiUsageException("Couldn't initialize OracleLobHandler because Oracle driver classes are not available. " + "Note that OracleLobHandler requires Oracle JDBC driver 9i or higher!", ex);
        }
    }
}

@Override
public Object toCFType(TimeZone tz, ResultSet rst, int columnIndex) throws SQLException, IOException {
    Object o = rst.getObject(columnIndex);
    if (o == null)
        return null;
    // we do not have oracle.sql.CLOB in the core, so we need reflection for this
    try {
        return Caster.toString(Reflector.callMethod(o, "stringValue", ZERO_ARGS));
    } catch (PageException pe) {
        throw ExceptionUtil.toIOException(pe);
    }
}

/**
     * execute with output filter
     */
public OmniDTO execute(UserDatabaseConnection udc, Map<String, Object> inputs, Map<String, String> outputFilters) throws BaseSQLException {
    Connection connection = udc.getConnection();
    DBAdapter dba = DBAdapterFactory.getInstance().getAdapter(udc.getConnectionName());
    OmniDTO returnTO = new OmniDTO();
    CallableStatement cstmt = null;
    try {
        cstmt = connection.prepareCall(sp.getJavaAPIString());
        // check
        int inputCount = sp.getInputParameterCount();
        if (inputs.size() < inputCount)
            throw new Exception("Input parameters insufficient exception.");
        // set parameters
        Collection<Parameter> parameters = sp.getParameters();
        Iterator<Parameter> pit = parameters.iterator();
        while (pit.hasNext()) {
            Parameter p = pit.next();
            if (Parameter.MODE_INOUT.equals(p.getMode()) || Parameter.MODE_OUT.equals(p.getMode()) || Parameter.MODE_RETURN.equals(p.getMode())) {
                //function return
                cstmt.registerOutParameter(p.getIndex(), p.getSqlDataType());
            }
            if (Parameter.MODE_IN.equals(p.getMode()) || Parameter.MODE_INOUT.equals(p.getMode())) {
                String key = p.getName();
                if (!inputs.containsKey(key))
                    throw new Exception("There must be a key/value pair corresponding to key " + key + " in input parameters.");
                Object obj = inputs.get(key);
                if (obj == null || "".equals(obj.toString()) && p.getSqlDataType() != Types.CHAR && p.getSqlDataType() != Types.VARCHAR && p.getSqlDataType() != Types.LONGVARCHAR) {
                    setNull(cstmt, p.getIndex(), p.getSqlDataType());
                } else {
                    if ("oracle.sql.CLOB".equals(p.getJavaClassName())) {
                        String tmp = (String) obj;
                        int strLength = tmp.length();
                        StringReader r = new StringReader(tmp);
                        cstmt.setCharacterStream(p.getIndex(), r, strLength);
                        r.close();
                    } else if (Parameter.UNKNOWN_SQL_DATA_TYPE != p.getSqlDataType()) {
                        setObject(cstmt, obj, p);
                    } else {
                        //It is up to JDBC driver's PreparedStatement implementation 
                        //class to deal with. Usually the class will make a decision 
                        //on which setXXX(Type) method to call based on the type of 
                        //the obj instance.
                        cstmt.setObject(p.getIndex(), obj);
                    }
                }
            }
        }
        cstmt.execute();
        // handle out cursors or other outputs if there is any
        if (outputFilters == null) {
            handleResultSet(dba, returnTO, cstmt);
        } else {
            handleFilteredResultSet(dba, returnTO, cstmt, outputFilters);
        }
    } catch (Exception ex) {
        throw new BaseSQLException(ex);
    } finally {
        DAOUtil.closeStatement(cstmt);
    }
    return returnTO;
}

public static Clob createClob(Connection conn, String value, Clob defaultValue) {
    try {
        Class clazz = ClassUtil.loadClass("oracle.sql.CLOB");
        // CLOB.DURATION_SESSION;
        if (duration == null)
            duration = Caster.toInteger(clazz.getField("DURATION_SESSION").getInt(null));
        // CLOB.MODE_READWRITE
        if (mode == null)
            mode = Caster.toInteger(clazz.getField("MODE_READWRITE").getInt(null));
        //CLOB c = CLOB.createTemporary(conn, false, CLOB.DURATION_SESSION);
        if (createTemporary == null || createTemporary.getDeclaringClass() != clazz)
            createTemporary = clazz.getMethod("createTemporary", new Class[] { Connection.class, boolean.class, int.class });
        Object clob = createTemporary.invoke(null, new Object[] { conn, Boolean.FALSE, duration });
        // c.open(CLOB.MODE_READWRITE);
        if (open == null || open.getDeclaringClass() != clazz)
            open = clazz.getMethod("open", new Class[] { int.class });
        open.invoke(clob, new Object[] { mode });
        //c.setString(1,value);
        if (setString == null || setString.getDeclaringClass() != clazz)
            setString = clazz.getMethod("setString", new Class[] { long.class, String.class });
        setString.invoke(clob, new Object[] { Long.valueOf(1), value });
        return (Clob) clob;
    } catch (Throwable t) {
    }
    return defaultValue;
}

public boolean setClobBody(Connection con, String sql, String colnumname, String body) {
    try {
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        if (rs != null) {
            if (rs.next()) {
                oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob(colnumname);
                BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
                out.write(body);
                out.close();
            }
        }
        rs.close();
        stmt.close();
        return true;
    } catch (Exception e) {
        if (Globa.sysInfo)
            Globa.logger.info(e.toString());
        if (Globa.sysDebug)
            e.printStackTrace();
        return false;
    }
}

@Test
public void pumpMessages() throws Exception {
    java.util.logging.LogManager.getLogManager().getLogger("").setLevel(Level.FINEST);
    SLF4JBridgeHandler.removeHandlersForRootLogger();
    SLF4JBridgeHandler.install();
    log.info("Creating UNIT TEST Class");
    createOracleAQQueue();
    Clob message = CLOB.createTemporary(dbConnection, false, CLOB.DURATION_SESSION);
    message.setString(1, TEST_MESSAGE);
    String plsql = String.format("begin mdb_aq.send_message('" + DB_SCHEMA_NAME + ".%1$s', ?); end;", oracleQueueName);
    CallableStatement statement = dbConnection.prepareCall(plsql);
    statement.setClob(1, message);
    statement.execute();
    statement.close();
    message.free();
}

private static void initOracle8DriverInformation() {
    initDone = true;
    // configure static information
    try {
        outputStreamFromBlobMethod = Class.forName("oracle.sql.BLOB").getMethod("getBinaryOutputStream");
        writerFromClobMethod = Class.forName("oracle.sql.CLOB").getMethod("getCharacterOutputStream");
    } catch (Throwable th) {
    }
}

public Object normalise(Object o) throws SQLException {
    if (o == null)
        return null;
    if (!(o instanceof oracle.sql.CLOB)) {
        throw new UnsupportedOperationException("OracleClobNormaliser cannot work with " + o.getClass());
    }
    oracle.sql.CLOB clob = (oracle.sql.CLOB) o;
    if (clob.length() > MAX_CLOB_LENGTH)
        throw new UnsupportedOperationException("Clobs larger than " + MAX_CLOB_LENGTH + "bytes are not supported by DBFIT");
    char[] buffer = new char[MAX_CLOB_LENGTH];
    int total = clob.getChars(1, MAX_CLOB_LENGTH, buffer);
    return String.valueOf(buffer, 0, total);
}

@Test
public void testNewSQLParamInsert() throws Exception {
    SQLParams params = new SQLParams();
    params.addSQLParam("id", "1", SQLParams.STRING);
    // ID,HOST_ID,PLUGIN_ID,CATEGORY_ID,NAME,DESCRIPTION,DATASOURCE_NAME,DRIVER,JDBC_URL,USERNAME,PASSWORD,VALIDATION_QUERY
    params.addSQLParam("blobname", "abcdblob", SQLParams.BLOB);
    params.addSQLParam("clobname", "abcdclob", SQLParams.CLOB);
    SQLExecutor.insertBean("insert into test(id,blobname,clobname) values(#[id],#[blobname],#[clobname])", params);
}

// Process the writing of big data type, such as:BLOB/CLOB
protected void ProcessBigDataType(EntityParser parser) throws IllegalEntityException, DataAccessException, FileAccessException {
    // 非置空的大字段属性
    HashSet<String> bigFieldNameSet = parser.getBigFieldNameSet();
    if (bigFieldNameSet.isEmpty()) {
        // if not exists BLOB/CLOB column type, then return.
        return;
    }
    // 解析后的非置空的属性
    HashMap<String, FieldExp> fieldExpMap = parser.getFieldExpMap();
    // 主键属性字段
    HashSet<String> pkSet = parser.getPKSet();
    if (pkSet.isEmpty()) {
        throw new IllegalEntityException(IllegalEntityException.NOT_SPECIFY_PK2, "");
    }
    OutputStream out = null;
    InputStream in = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        String tableName = MappingUtil.getTableName(parser.getClassName());
        // select the BLOB/CLOB column from TABLE where pk=? for update
        StringBuffer getBClobSql = new StringBuffer();
        getBClobSql.append("SELECT ");
        for (Iterator<String> it = bigFieldNameSet.iterator(); it.hasNext(); ) {
            String fieldName = (String) it.next();
            getBClobSql.append(MappingUtil.getColumnName(fieldName));
            getBClobSql.append(",");
        }
        getBClobSql.deleteCharAt(getBClobSql.length() - 1);
        getBClobSql.append(" FROM ");
        getBClobSql.append(tableName);
        getBClobSql.append(" WHERE 1 = 1 ");
        for (Iterator<String> it = pkSet.iterator(); it.hasNext(); ) {
            // the filed name which be associated with the primary key
            String pkFieldName = (String) it.next();
            getBClobSql.append(" AND ");
            getBClobSql.append(MappingUtil.getColumnName(pkFieldName));
            getBClobSql.append(" = ");
            // ==========================================================================
            // 仅这一段的处理 与 方法ProcessBigDataType(EntityParser parser,Criteria
            // criteria)不同.
            //
            // =========================== BEGIN ==========================
            // ==========================================================================
            FieldExp fieldExp = (FieldExp) fieldExpMap.get(pkFieldName);
            // 如果主键列没有匹配值, 而对BLOB/CLOB的写入必须是某主键值标识的单条记录
            if (fieldExp == null) {
                throw new DataAccessException(DataAccessException.WRITING_BIGTYPE_FORONLYONE, parser.getClassName());
            }
            Object fieldValue = fieldExp.getFieldValue();
            // 对于字符串类型的列,在参数值前后加上 ' 号
            if (fieldValue instanceof EString) {
                getBClobSql.append("'");
                getBClobSql.append(fieldValue.toString());
                getBClobSql.append("'");
            } else if (fieldValue instanceof ELong) {
                getBClobSql.append(((ELong) fieldValue).longValue());
            } else if (fieldValue instanceof EInteger) {
                getBClobSql.append(((EInteger) fieldValue).intValue());
            } else if (fieldValue instanceof EDouble) {
                getBClobSql.append(((EDouble) fieldValue).doubleValue());
            } else if (fieldValue instanceof EFloat) {
                getBClobSql.append(((EFloat) fieldValue).floatValue());
            } else {
                // 在含有大字段列的表中，只能以数字或字符串类型的列作为主键，而不能是日期或大字段列！
                throw new IllegalEntityException(IllegalEntityException.EXCEED_PK_COLUMN_SCOPE, "");
            }
        // ==========================================================================
        // 仅这一段的处理 与 方法(EntityParser parser,Criteria criteria)不同.
        //
        // =========================== END ==========================
        // ==========================================================================
        }
        // enf for (Iterator it
        getBClobSql.append(" FOR UPDATE ");
        log.info("SQL: " + getBClobSql.toString());
        stmt = conn.prepareStatement(getBClobSql.toString());
        getBClobSql.delete(0, getBClobSql.length());
        rs = stmt.executeQuery();
        if (rs.next()) {
            int k = 1;
            for (Iterator<String> it = bigFieldNameSet.iterator(); it.hasNext(); ) {
                String fieldName = (String) it.next();
                FieldExp fieldExp = (FieldExp) fieldExpMap.get(fieldName);
                Object fieldValue = fieldExp.getFieldValue();
                // bsolute path of the file
                String filePath = ((File) fieldValue).getCanonicalPath();
                // if the file not exists
                if (!(new File(filePath).exists())) {
                    rs.close();
                    stmt.close();
                    throw new FileAccessException(FileAccessException.FILE_NOTFOUND, filePath);
                }
                // BLOB
                if (fieldValue instanceof EBinFile) {
                    Blob blob = rs.getBlob(k++);
                    //采用oracle早期的驱动包ojdbc14_g.jar，对BLOB类型的处理
                    //out = ((oracle.sql.BLOB) blob).getBinaryOutputStream();
                    //采用ojdbc5.jar驱动包后，对BLOB的处理，其写法不需要再转型。
                    out = blob.setBinaryStream(1L);
                    in = new FileInputStream(filePath);
                    byte[] b = new byte[((oracle.sql.BLOB) blob).getBufferSize()];
                    int len = 0;
                    while ((len = in.read(b)) != -1) out.write(b, 0, len);
                    in.close();
                    out.close();
                // File inFile = new File(filePath);
                // in = new FileInputStream(inFile);
                // out = blob.setBinaryStream(inFile.length());
                //
                // byte[] b = new byte[1024*8];
                // int len = 0;
                // while ((len = in.read(b)) != -1)
                // out.write(b, 0, len);
                //
                // in.close();
                // out.close();
                // CLOB
                } else {
                    Clob clob = rs.getClob(k++);
                    //采用oracle早期的驱动包ojdbc14_g.jar，对CLOB类型的处理
                    //out = ((oracle.sql.CLOB) clob).getAsciiOutputStream();
                    //采用ojdbc5.jar驱动包后，对CLOB的处理，其写法不需要再转型。
                    out = clob.setAsciiStream(1L);
                    in = new FileInputStream(filePath);
                    byte[] b = new byte[((oracle.sql.CLOB) clob).getBufferSize()];
                    int len = 0;
                    while ((len = in.read(b)) != -1) out.write(b, 0, len);
                    in.close();
                    out.close();
                }
            }
        // end for (Iterator it
        }
    // end if (rs.next
    } catch (SQLException e) {
        log.error("", e);
        throw new DataAccessException(e.getMessage());
    } catch (IOException e) {
        log.error("", e);
        throw new FileAccessException(e.getMessage());
    } finally {
        try {
            if (in != null) {
                in.close();
            }
            if (out != null) {
                out.close();
            }
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        } catch (Exception e1) {
            log.error("", e1);
        }
    }
}

private static CLOB getCLOB(String xmlData, Connection conn) throws SQLException {
    CLOB tempClob = null;
    try {
        // If the temporary CLOB has not yet been created, create new
        tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION);
        // Open the temporary CLOB in readwrite mode to enable writing
        tempClob.open(CLOB.MODE_READWRITE);
        // Get the output stream to write
        Writer tempClobWriter = tempClob.getCharacterOutputStream();
        // Write the data into the temporary CLOB
        tempClobWriter.write(xmlData);
        // Flush and close the stream
        tempClobWriter.flush();
        tempClobWriter.close();
        // Close the temporary CLOB
        tempClob.close();
    } catch (SQLException sqlexp) {
        tempClob.freeTemporary();
        sqlexp.printStackTrace();
    } catch (Exception exp) {
        tempClob.freeTemporary();
        exp.printStackTrace();
    }
    return tempClob;
}

/**
     *  Uses Oracle-specific API on the raw, underlying Connection to create a temporary CLOB.
     *  <b>Users are responsible for calling freeTemporary on the returned BLOB prior to Connection close() / check-in!
     *  c3p0 will <i>not</i> automatically clean up temporary CLOBs.</b>
     *
     * @param c3p0ProxyCon may be a c3p0 proxy for an <tt>oracle.jdbc.driver.OracleConnection</tt>, or an 
     *        <tt>oracle.jdbc.driver.OracleConnection</tt> directly.
     */
public static CLOB createTemporaryCLOB(Connection c3p0ProxyCon, boolean cache, int duration) throws SQLException {
    if (c3p0ProxyCon instanceof C3P0ProxyConnection) {
        try {
            C3P0ProxyConnection castCon = (C3P0ProxyConnection) c3p0ProxyCon;
            Method m = CLOB.class.getMethod("createTemporary", CREATE_TEMP_ARGS);
            Object[] args = new Object[] { C3P0ProxyConnection.RAW_CONNECTION, Boolean.valueOf(cache), new Integer(duration) };
            return (CLOB) castCon.rawConnectionOperation(m, null, args);
        } catch (InvocationTargetException e) {
            if (Debug.DEBUG)
                e.printStackTrace();
            throw SqlUtils.toSQLException(e.getTargetException());
        } catch (Exception e) {
            if (Debug.DEBUG)
                e.printStackTrace();
            throw SqlUtils.toSQLException(e);
        }
    } else if (c3p0ProxyCon instanceof OracleConnection)
        return CLOB.createTemporary(c3p0ProxyCon, cache, duration);
    else
        throw new SQLException("Cannot create an oracle CLOB from a Connection that is neither an oracle.jdbc.driver.Connection, " + "nor a C3P0ProxyConnection wrapped around an oracle.jdbc.driver.Connection.");
}

public Object concludeResults(DbResultSet resultset) throws SQLException {
    if (!resultset.next()) {
        return null;
    }
    CharArrayWriter writer = new CharArrayWriter();
    CLOB clob = (CLOB) resultset.getClob("content");
    if (null == clob) {
        return null;
    }
    Reader reader = clob.getCharacterStream();
    try {
        try {
            char[] buffer = new char[clob.getBufferSize()];
            int size = 0;
            while ((size = reader.read(buffer)) != -1) {
                writer.write(buffer, 0, size);
            }
            writer.flush();
        } finally {
            reader.close();
        }
    } catch (IOException e) {
        throw new UseContentDataErrorException(id, e);
    }
    return writer.toString();
}

public String getString(ResultSet rs, int param) {
    String value = null;
    try {
        char[] cbuf = null;
        java.sql.Clob clob = rs.getClob(param);
        if (clob != null) {
            // Note: Using clob.stringValue() results in StoreManagerTest
            // exception: "java.sql.SQLException: Conversion to String failed"
            StringBuilder sbuf = new StringBuilder();
            Reader reader = clob.getCharacterStream();
            try {
                final int BUFF_SIZE = 4096;
                cbuf = new char[BUFF_SIZE];
                int charsRead = reader.read(cbuf);
                while (-1 != charsRead) {
                    sbuf.append(cbuf, 0, charsRead);
                    java.util.Arrays.fill(cbuf, (char) 0);
                    charsRead = reader.read(cbuf);
                }
            } catch (IOException e) {
                throw new NucleusDataStoreException("Error reading Oracle CLOB object: param = " + param, e);
            } finally {
                try {
                    reader.close();
                } catch (IOException e) {
                    throw new NucleusDataStoreException("Error reading Oracle CLOB object: param = " + param, e);
                }
            }
            value = sbuf.toString();
            if (value.length() == 0) {
                value = null;
            } else if (value.equals(getDatastoreAdapter().getSurrogateForEmptyStrings())) {
                value = "";
            }
        }
    } catch (SQLException e) {
        throw new NucleusDataStoreException(Localiser.msg("055001", "String", "" + param), e);
    }
    return value;
}

/**
     * Add a record to the database.  This action assumes that
     * the file referenced by the "descriptor" parameter conforms
     * to the AbstractDatabaseAction specifications.
     */
public Map act(Redirector redirector, SourceResolver resolver, Map objectModel, String source, Parameters param) throws Exception {
    DataSourceComponent datasource = null;
    Connection conn = null;
    int currentIndex = 0;
    try {
        Configuration conf = this.getConfiguration(param.getParameter("descriptor", null));
        String query = this.getAddQuery(conf);
        datasource = this.getDataSource(conf);
        conn = datasource.getConnection();
        Request request = ObjectModelHelper.getRequest(objectModel);
        if (conn.getAutoCommit()) {
            conn.setAutoCommit(false);
        }
        PreparedStatement statement = conn.prepareStatement(query);
        getLogger().info(query);
        Configuration[] keys = conf.getChild("table").getChild("keys").getChildren("key");
        Configuration[] values = conf.getChild("table").getChild("values").getChildren("value");
        currentIndex = 1;
        // Insert the keys into the query
        for (int i = 0; i < keys.length; i++) {
            String mode = keys[i].getAttribute("mode", "automatic");
            if ("manual".equals(mode)) {
                String selectQuery = this.getSelectQuery(keys[i]);
                Statement stmt = null;
                ResultSet set = null;
                try {
                    stmt = conn.createStatement();
                    set = stmt.executeQuery(selectQuery);
                    set.next();
                    int value = set.getInt("maxid") + 1;
                    statement.setInt(currentIndex, value);
                    request.setAttribute(keys[i].getAttribute("param"), String.valueOf(value));
                } catch (SQLException sqle) {
                    getLogger().warn("There was an error retrieving the next key while inserting on the database", sqle);
                    throw sqle;
                } finally {
                    set.close();
                    stmt.close();
                    currentIndex++;
                }
            } else if ("form".equals(mode)) {
                String parameter = keys[i].getAttribute("param");
                request.setAttribute(parameter, request.getParameter(parameter));
                this.setColumn(statement, currentIndex, request, keys[i]);
                currentIndex++;
            }
        }
        // insert the values into the query
        for (int i = 0; i < values.length; i++) {
            String type = values[i].getAttribute("type");
            String parameter = values[i].getAttribute("param");
            if (type.equals("image")) {
                File binaryFile = (File) request.get(parameter);
                Parameters iparam = new Parameters();
                iparam.setParameter("image-size", String.valueOf(binaryFile.length()));
                ImageProperties prop = ImageUtils.getImageProperties(binaryFile);
                iparam.setParameter("image-width", Integer.toString(prop.width));
                iparam.setParameter("image-height", Integer.toString(prop.height));
                synchronized (this.files) {
                    this.files.put(binaryFile, param);
                }
            }
            if (!this.isLargeObject(type)) {
                this.setColumn(statement, currentIndex, request, values[i]);
                currentIndex++;
            }
        }
        statement.execute();
        statement.close();
        query = this.getSelectLOBQuery(conf);
        // Process the large objects if they exist
        if (query != null) {
            PreparedStatement LOBstatement = conn.prepareStatement(query);
            getLogger().info(query);
            if (keys.length > 0) {
                currentIndex = 1;
                for (int i = 0; i < keys.length; i++) {
                    this.setColumn(LOBstatement, currentIndex, request, keys[i]);
                    currentIndex++;
                }
            }
            OracleResultSet set = (OracleResultSet) LOBstatement.executeQuery();
            if (set.next()) {
                int index = 0;
                for (int i = 0; i < values.length; i++) {
                    String type = values[i].getAttribute("type", "");
                    if (this.isLargeObject(type)) {
                        Object attr = request.get(values[i].getAttribute("param"));
                        int length = -1;
                        InputStream stream = null;
                        OutputStream output = null;
                        int bufSize = 1024;
                        index++;
                        if (type.equals("ascii")) {
                            CLOB ascii = set.getCLOB(index);
                            if (attr instanceof File) {
                                File asciiFile = (File) attr;
                                stream = new BufferedInputStream(new FileInputStream(asciiFile));
                            } else {
                                String asciiText = (String) attr;
                                stream = new BufferedInputStream(new ByteArrayInputStream(asciiText.getBytes()));
                            }
                            output = new BufferedOutputStream(ascii.getAsciiOutputStream());
                            bufSize = ascii.getBufferSize();
                        } else {
                            BLOB binary = set.getBLOB(index);
                            File binaryFile = (File) attr;
                            stream = new BufferedInputStream(new FileInputStream(binaryFile));
                            length = (int) binaryFile.length();
                            output = new BufferedOutputStream(binary.getBinaryOutputStream());
                            bufSize = binary.getBufferSize();
                        }
                        byte[] buffer = new byte[bufSize];
                        while ((length = stream.read(buffer)) != -1) {
                            output.write(buffer, 0, length);
                        }
                        stream.close();
                        output.close();
                    }
                }
            }
            set.close();
            set.getStatement().close();
        }
        conn.commit();
    } catch (Exception e) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException se) {
                getLogger().debug("There was an error rolling back the transaction", se);
            }
        }
        throw new ProcessingException("Could not add record :position = " + (currentIndex - 1), e);
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException sqe) {
                getLogger().warn("There was an error closing the datasource", sqe);
            }
        }
        if (datasource != null) {
            this.dbselector.release(datasource);
        }
    }
    return null;
}

//    public SQLProcessorOracleTomcat4Delegate() {
//      // Load the Oracle JDBC driver
//        try {
//            DriverManager.registerDriver
//                    (new oracle.jdbc.driver.OracleDriver());
//            Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@rosaura","scott","tiger");
//
//            // Create Oracle DatabaseMetaData object
//            DatabaseMetaData meta = conn.getMetaData();
//
//            // gets driver info:
//            System.out.println("JDBC driver version is " + meta.getDriverVersion());
//            System.out.println("JDBC driver nameis     " + meta.getDriverName());
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//    }
public void setClob(PreparedStatement stmt, int index, String value) throws SQLException {
    // Get an OraclePreparedStatement
    final OraclePreparedStatement oracleStmt = getOraclePreparedStatement(stmt);
    // Get an OracleConnection to prevent ClassCastException in oracle.sql.CLOB
    // - see this post from 2004 https://forums.oracle.com/thread/281238
    // - this still seems to be the case with Oracle 12c Release 1 (12.1.0.1) ojdbc6_g.jar
    // - we only observed OraclePreparedStatement.getConnection() returning a wrapped
    //   connection when getting the OraclePreparedStatement through JDBC 4, so
    //   unwrapping the connection with JDBC 4's unwrap should be fine
    final Connection connection = oracleStmt.getConnection();
    final OracleConnection oracleConnection = connection instanceof OracleConnection ? (OracleConnection) connection : connection.unwrap(OracleConnection.class);
    // Create a temporary CLOB
    final CLOB clob = CLOB.createTemporary(oracleConnection, true, CLOB.DURATION_SESSION);
    // Write to the CLOB
    final Writer writer = clob.getCharacterOutputStream();
    try {
        NetUtils.copyStream(new StringReader(value), writer);
        writer.flush();
    } catch (IOException e) {
        throw new OXFException(e);
    }
    // Set the CLOB on the statement
    oracleStmt.setClob(index, clob);
}

private int updateDataByPreparedstatement(ReportRequest rrequest, Map<String, String> mRowData, Map<String, String> mParamValues, PreparedStatement pstmt, String sql) throws SQLException {
    AbsDatabaseType dbtype = rrequest.getDbType(this.datasource);
    Oracle oracleType = null;
    ReportBean rbean = this.ownerUpdateBean.getOwner().getReportBean();
    int rtnVal = 1;
    if (sql.trim().toLowerCase().startsWith("select ") && (dbtype instanceof Oracle)) {
        //当前在执行更新大字段的SQL语句
        oracleType = (Oracle) dbtype;
        if (lstParamBeans != null && lstParamBeans.size() > 0) {
            int colidx = 1;
            for (EditableReportParamBean paramBean : lstParamBeans) {
                if ((paramBean.getDataTypeObj() instanceof ClobType) || (paramBean.getDataTypeObj() instanceof BlobType))
                    continue;
                paramBean.getDataTypeObj().setPreparedStatementValue(colidx++, paramBean.getRuntimeParamValue(rrequest, rbean, mRowData, mParamValues, this.datasource, ownerUpdateBean.isAutoReportdata()), pstmt, dbtype);
            }
        }
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            if (lstParamBeans != null && lstParamBeans.size() > 0) {
                int colidx = 1;
                for (EditableReportParamBean paramBean : lstParamBeans) {
                    if (!(paramBean.getDataTypeObj() instanceof ClobType) && !(paramBean.getDataTypeObj() instanceof BlobType))
                        continue;
                    String paramvalue = paramBean.getRuntimeParamValue(rrequest, rbean, mRowData, mParamValues, this.datasource, ownerUpdateBean.isAutoReportdata());
                    if (paramBean.getDataTypeObj() instanceof ClobType) {
                        oracleType.setClobValueInSelectMode(paramvalue, (oracle.sql.CLOB) rs.getClob(colidx++));
                    } else {
                        oracleType.setBlobValueInSelectMode(paramBean.getDataTypeObj().label2value(paramvalue), (oracle.sql.BLOB) rs.getBlob(colidx++));
                    }
                }
            }
        }
        rs.close();
    } else {
        if (lstParamBeans != null && lstParamBeans.size() > 0) {
            int idx = 1;
            for (EditableReportParamBean paramBean : lstParamBeans) {
                paramBean.getDataTypeObj().setPreparedStatementValue(idx++, paramBean.getRuntimeParamValue(rrequest, rbean, mRowData, mParamValues, this.datasource, ownerUpdateBean.isAutoReportdata()), pstmt, dbtype);
            }
        }
        rtnVal = pstmt.executeUpdate();
    }
    return rtnVal;
}

private void setBindValueAtName(PreparedStatement statement, String bindValueName, Object bindValue, OracleTableColumn column) throws SQLException {
    if (column.getOracleType() == OraOopOracleQueries.getOracleType("NUMBER")) {
        OraOopOracleQueries.setBigDecimalAtName(statement, bindValueName, (BigDecimal) bindValue);
    } else if (column.getOracleType() == OraOopOracleQueries.getOracleType("VARCHAR")) {
        OraOopOracleQueries.setStringAtName(statement, bindValueName, (String) bindValue);
    } else if (column.getOracleType() == OraOopOracleQueries.getOracleType("TIMESTAMP") || column.getOracleType() == OraOopOracleQueries.getOracleType("TIMESTAMPTZ") || column.getOracleType() == OraOopOracleQueries.getOracleType("TIMESTAMPLTZ")) {
        Object objValue = bindValue;
        if (objValue instanceof Timestamp) {
            Timestamp value = (Timestamp) objValue;
            OraOopOracleQueries.setTimestampAtName(statement, bindValueName, value);
        } else {
            String value = (String) objValue;
            if (value == null || value.equalsIgnoreCase("null")) {
                value = "";
            }
            OraOopOracleQueries.setStringAtName(statement, bindValueName, value);
        }
    } else if (column.getOracleType() == OraOopOracleQueries.getOracleType("BINARY_DOUBLE")) {
        Double value = (Double) bindValue;
        if (value != null) {
            OraOopOracleQueries.setBinaryDoubleAtName(statement, bindValueName, value);
        } else {
            OraOopOracleQueries.setObjectAtName(statement, bindValueName, null);
        }
    } else if (column.getOracleType() == OraOopOracleQueries.getOracleType("BINARY_FLOAT")) {
        Float value = (Float) bindValue;
        if (value != null) {
            OraOopOracleQueries.setBinaryFloatAtName(statement, bindValueName, value);
        } else {
            OraOopOracleQueries.setObjectAtName(statement, bindValueName, null);
        }
    } else if (column.getOracleType() == OraOopOracleQueries.getOracleType("STRUCT")) {
        // <- E.g. URITYPE
        if (column.getDataType().equals(OraOopConstants.Oracle.URITYPE)) {
            String value = (String) bindValue;
            OraOopOracleQueries.setStringAtName(statement, bindValueName, value);
        } else {
            String msg = String.format("%s needs to be updated to cope with the data-type: %s " + "where the Oracle data_type is \"%s\".", OraOopUtilities.getCurrentMethodName(), column.getDataType(), column.getOracleType());
            LOG.error(msg);
            throw new UnsupportedOperationException(msg);
        }
    } else {
        // LOB data-types are currently not supported during
        // a Sqoop Export.
        // JIRA: SQOOP-117
        // OraOopConstants.SUPPORTED_EXPORT_ORACLE_DATA_TYPES_CLAUSE
        // will already have excluded all LOB columns.
        // case oracle.jdbc.OracleTypes.CLOB:
        // {
        // oracle.sql.CLOB clob = new
        // oracle.sql.CLOB(connection);
        // Object value = fieldMap.get(colName);
        // //clob.set
        // statement.setCLOBAtName(bindValueName, clob);
        // break;
        // }
        String msg = String.format("%s may need to be updated to cope with the data-type: %s", OraOopUtilities.getCurrentMethodName(), column.getOracleType());
        LOG.debug(msg);
        OraOopOracleQueries.setObjectAtName(statement, bindValueName, bindValue);
    }
}

/**
     * Populate the properties of the specified JavaBean from the next record of the specified ResultSet, based on
     * matching each column name against the corresponding JavaBeans "property setter" methods in the bean's class.
     * Suitable conversion is done for argument types as described under <code>convert()</code>.
     * 
     * @param bean The JavaBean whose properties are to be set
     * @param resultSet The ResultSet whose parameters are to be used to populate bean properties
     * @exception SQLException if an exception is thrown while setting property values or access the ResultSet
     */
public static void populate(final Object bean, final ResultSet resultSet) throws SQLException {
    // Format pour les dates
    final SimpleDateFormat formatDate = new SimpleDateFormat("dd/MM/yyyy HH:mm");
    // Build a list of relevant column properties from this resultSet
    final HashMap properties = new HashMap();
    // Acquire resultSet MetaData
    final ResultSetMetaData metaData = resultSet.getMetaData();
    final int cols = metaData.getColumnCount();
    // if (resultSet.next())
    for (int i = 1; i <= cols; i++) {
        if (metaData.getColumnClassName(i).equals("java.sql.Timestamp")) {
            properties.put(metaData.getColumnName(i), getFormattedDateTimeColumn(resultSet.getTimestamp(i), formatDate));
        } else if (metaData.getColumnClassName(i).equals("oracle.sql.CLOB")) {
            properties.put(metaData.getColumnName(i), getFormattedClobColumn(resultSet.getAsciiStream(i)));
        } else {
            properties.put(metaData.getColumnName(i), getFormattedStringColumn(resultSet.getString(i)));
        }
    }
    // Set the corresponding properties of our bean
    try {
        populateIgnoreCase(bean, properties);
    } catch (final Exception e) {
        throw new SQLException("BeanUtils.populate threw " + e.toString());
    }
}

public String getSQLType(String javaClassName, int maxlength) {
    String theReturn;
    if (javaClassName.equals("java.lang.Integer")) {
        theReturn = "NUMBER";
    } else if (javaClassName.equals("java.lang.String")) {
        if (maxlength < 0) {
            theReturn = "VARCHAR2(255)";
        } else if (maxlength <= 4000) {
            theReturn = "VARCHAR2(" + maxlength + ")";
        } else {
            theReturn = "CLOB";
        }
    } else if (javaClassName.equals("java.lang.Boolean")) {
        theReturn = "CHAR(1)";
    } else if (javaClassName.equals("java.lang.Float")) {
        theReturn = "FLOAT";
    } else if (javaClassName.equals("java.lang.Double")) {
        theReturn = "FLOAT(15)";
    } else if (javaClassName.equals("java.sql.Timestamp")) {
        theReturn = "DATE";
    } else if (javaClassName.equals("java.sql.Date") || javaClassName.equals("java.util.Date")) {
        theReturn = "DATE";
    } else if (javaClassName.equals("java.sql.Blob")) {
        theReturn = "BLOB";
    } else if (javaClassName.equals("java.sql.Time")) {
        theReturn = "TIME";
    } else if (javaClassName.equals("com.idega.util.Gender")) {
        theReturn = "VARCHAR(1)";
    } else if (javaClassName.equals("com.idega.data.BlobWrapper")) {
        theReturn = "BLOB";
    } else {
        theReturn = "";
    }
    return theReturn;
}

@Override
public void setNull(PreparedStatement stmnt, int idx, int colType, Column col) throws SQLException {
    if ((colType == Types.CLOB || colType == Types.BLOB) && col.isNotNull())
        throw new UserException(_loc.get("null-blob-in-not-nullable", toDBName(col.getFullDBIdentifier())));
    if (colType == Types.BLOB && _driverBehavior == BEHAVE_ORACLE)
        stmnt.setBlob(idx, getEmptyBlob());
    else if (colType == Types.CLOB && _driverBehavior == BEHAVE_ORACLE && !col.isXML())
        stmnt.setClob(idx, getEmptyClob());
    else if ((colType == Types.STRUCT || colType == Types.OTHER) && col != null && !DBIdentifier.isNull(col.getTypeIdentifier()))
        stmnt.setNull(idx, Types.STRUCT, col.getTypeIdentifier().getName());
    else // setNull with DATE; see bug #1171
    if (colType == Types.DATE)
        super.setNull(stmnt, idx, Types.TIMESTAMP, col);
    else // the Oracle driver does not support Types.OTHER with setNull
    if (colType == Types.OTHER || col.isXML())
        super.setNull(stmnt, idx, Types.NULL, col);
    else
        super.setNull(stmnt, idx, colType, col);
}

private CLOB getCLOB(String xmlData) throws SQLException {
    CLOB tempClob = null;
    Connection dbConn = conn.getConnection();
    try {
        // If the temporary CLOB has not yet been created, create new
        tempClob = CLOB.createTemporary(dbConn, true, CLOB.DURATION_SESSION);
        // Open the temporary CLOB in readwrite mode to enable writing
        tempClob.open(CLOB.MODE_READWRITE);
        // Get the output stream to write
        // Writer tempClobWriter = tempClob.getCharacterOutputStream();
        Writer tempClobWriter = tempClob.setCharacterStream(0);
        // Write the data into the temporary CLOB
        tempClobWriter.write(xmlData);
        // Flush and close the stream
        tempClobWriter.flush();
        tempClobWriter.close();
        // Close the temporary CLOB
        tempClob.close();
    } catch (SQLException sqlexp) {
        tempClob.freeTemporary();
        sqlexp.printStackTrace();
    } catch (Exception exp) {
        tempClob.freeTemporary();
        exp.printStackTrace();
    }
    return tempClob;
}

private Document getDatasetConfigDocumentByDatasetIDOracle(final String user, final String dataset, final String datasetID) throws ConfigurationException {
    Connection conn = null;
    try {
        final String metatable = this.createMetaTables(user);
        final String sql = "select xml, compressed_xml from " + this.getSchema()[0] + "." + this.MARTXMLTABLE + " where dataset_id_key = ?";
        if (this.logger.isLoggable(Level.FINE))
            this.logger.fine("Using " + sql + " to get DatasetConfig for datasetID " + datasetID + "and dataset " + dataset + "\n");
        conn = this.dsource.getConnection();
        final PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, datasetID);
        // ps.setString(2, dataset);
        final ResultSet rs = ps.executeQuery();
        if (!rs.next()) {
            // will only get one result
            rs.close();
            conn.close();
            return null;
        }
        final CLOB stream = (CLOB) rs.getClob(1);
        final BLOB cstream = (BLOB) rs.getBlob(2);
        InputStream rstream = null;
        if (cstream != null)
            rstream = new GZIPInputStream(cstream.getBinaryStream());
        else
            rstream = stream.getAsciiStream();
        final Document ret = this.dscutils.getDocumentForXMLStream(rstream);
        rstream.close();
        rs.close();
        return ret;
    } catch (final SQLException e) {
        throw new ConfigurationException("Caught SQL Exception during fetch of requested DatasetConfig: " + e.getMessage(), e);
    } catch (final IOException e) {
        throw new ConfigurationException("Caught IOException during fetch of requested DatasetConfig: " + e.getMessage(), e);
    } finally {
        DetailedDataSource.close(conn);
    }
}

//    private void applyInsertIntoTable(PreparedStatement statement, Example example, Iterator<AttributeRole> attributes, boolean addAutoGeneratedPrimaryKeys, Attribute genPrimaryKey) throws SQLException {
//    	List<Example> wrapperList = new LinkedList<Example>();
//    	wrapperList.add(example);
//    	applyBatchInsertIntoTable(statement, wrapperList, attributes, addAutoGeneratedPrimaryKeys, genPrimaryKey);
//    }
private void applyBatchInsertIntoTable(PreparedStatement statement, List<Example> examples, Iterator<AttributeRole> attributes, boolean addAutoGeneratedPrimaryKeys, Attribute genPrimaryKey) throws SQLException {
    // create List of attributes from attributes iterator for reuse:
    List<Attribute> attributeList = new LinkedList<Attribute>();
    while (attributes.hasNext()) {
        attributeList.add(attributes.next().getAttribute());
    }
    int counter = 1;
    for (Example example : examples) {
        for (Attribute attribute : attributeList) {
            if (addAutoGeneratedPrimaryKeys && attribute == genPrimaryKey) {
                continue;
            }
            double value = example.getValue(attribute);
            if (Double.isNaN(value)) {
                int sqlType = statementCreator.getSQLTypeForRMValueType(attribute.getValueType()).getDataType();
                statement.setNull(counter, sqlType);
            } else {
                if (attribute.isNominal()) {
                    String valueString = attribute.getMapping().mapIndex((int) value);
                    // circumvent problem that large clobs could not be written to Oracle DB (no longer necessary since this can be done via connection parameters)
                    //					if (Ontology.ATTRIBUTE_VALUE_TYPE.isA(attribute.getValueType(), Ontology.STRING)) {
                    //						CLOB clob = oracle.sql.CLOB.createTemporary(connection, true, oracle.sql.CLOB.DURATION_CALL);
                    //						clob.putString(1, valueString);
                    //						statement.setClob(counter, clob);
                    //					}
                    //					DataTypeSyntaxInformation sqlType = statementCreator.getSQLTypeForRMValueType(attribute.getValueType());
                    //					switch (sqlType.getDataType()) {
                    //						case Types.CLOB:
                    //							//statement.setCharacterStream(counter, new StringReader(valueString));
                    //							//statement.setClob(counter, new SerialClob(valueString.toCharArray()));
                    //							//statement.setAsciiStream(counter, new StringInputStream(valueString));
                    //							statement.setString(counter, valueString);
                    //							break;
                    //						case Types.VARCHAR:
                    //						default:
                    //							statement.setString(counter, valueString);
                    //							break;
                    //					}
                    statement.setString(counter, valueString);
                } else {
                    if (Ontology.ATTRIBUTE_VALUE_TYPE.isA(attribute.getValueType(), Ontology.DATE_TIME)) {
                        if (Ontology.ATTRIBUTE_VALUE_TYPE.isA(attribute.getValueType(), Ontology.TIME)) {
                            statement.setTime(counter, new Time((long) value));
                        } else {
                            statement.setTimestamp(counter, new Timestamp((long) value));
                        }
                    } else {
                        statement.setDouble(counter, value);
                    }
                }
            }
            counter++;
        }
    }
    statement.executeUpdate();
    if (addAutoGeneratedPrimaryKeys) {
        ResultSet generatedKeys = statement.getGeneratedKeys();
        Iterator<Example> exampleIterator = examples.iterator();
        int generatedKeyCount = 0;
        while (generatedKeys.next()) {
            ++generatedKeyCount;
            int key = generatedKeys.getInt(1);
            exampleIterator.next().setValue(genPrimaryKey, key);
        }
        if (generatedKeyCount != examples.size()) {
            throw new SQLException("The table does not contain a auto increment primary key. Please deactivate the Parameter \"" + DatabaseExampleSetWriter.PARAMETER_GET_GENERATED_PRIMARY_KEYS + "\".");
        }
    }
}

/**
    * Get the column value in the specified result set.
    *
    * @param env the PHP executing environment
    * @param rs the result set
    * @param metaData the result set meta data
    * @param column the column number
    * @return the column value
    */
public Value getColumnValue(Env env, ResultSet rs, ResultSetMetaData metaData, int column) throws SQLException {
    try {
        switch(metaData.getColumnType(column)) {
            case Types.NULL:
                return NullValue.NULL;
            case Types.BIT:
                {
                    String typeName = metaData.getColumnTypeName(column);
                    // Postgres matches BIT for BOOL columns
                    if (!typeName.equals("bool")) {
                        String value = rs.getString(column);
                        if (rs.wasNull()) {
                            return NullValue.NULL;
                        } else {
                            return _env.createString(value);
                        }
                    }
                // else fall to boolean
                }
            case Types.BOOLEAN:
                {
                    boolean b = rs.getBoolean(column);
                    if (rs.wasNull()) {
                        return NullValue.NULL;
                    } else {
                        return env.createString(b ? "t" : "f");
                    }
                }
            case Types.TINYINT:
            case Types.SMALLINT:
            case Types.INTEGER:
            case Types.BIGINT:
                {
                    long value = rs.getLong(column);
                    if (rs.wasNull()) {
                        return NullValue.NULL;
                    } else {
                        return _env.createString(String.valueOf(value));
                    }
                }
            case Types.REAL:
            case Types.DOUBLE:
                {
                    double value = rs.getDouble(column);
                    if (rs.wasNull()) {
                        return NullValue.NULL;
                    } else if (metaData.isCurrency(column)) {
                        StringValue sb = _env.createUnicodeBuilder();
                        sb.append("$");
                        return sb.append(value);
                    } else if (value == 0.0) {
                        StringValue sb = _env.createUnicodeBuilder();
                        return sb.append("0");
                    } else {
                        StringValue sb = _env.createUnicodeBuilder();
                        return sb.append(value);
                    }
                }
            case Types.BLOB:
                {
                    Object object = rs.getBlob(column);
                    if (object.getClass().getName().equals("oracle.sql.BLOB")) {
                        OracleOciLob ociLob = new OracleOciLob((Oracle) _conn, OracleModule.OCI_D_LOB);
                        ociLob.setLob(object);
                        object = ociLob;
                    }
                    return env.wrapJava(object);
                }
            case Types.CLOB:
                {
                    Object object = rs.getClob(column);
                    if (object.getClass().getName().equals("oracle.sql.CLOB")) {
                        OracleOciLob ociLob = new OracleOciLob((Oracle) _conn, OracleModule.OCI_D_LOB);
                        ociLob.setLob(object);
                        object = ociLob;
                    }
                    return env.wrapJava(object);
                }
            case Types.LONGVARBINARY:
            case Types.VARBINARY:
            case Types.BINARY:
                {
                    StringValue bb = env.createBinaryBuilder();
                    InputStream is = rs.getBinaryStream(column);
                    if (// || rs.wasNull())
                    is == null) {
                        return NullValue.NULL;
                    }
                    try {
                        bb.appendReadAll(is, Long.MAX_VALUE / 2);
                    } catch (RuntimeException e) {
                        log.log(Level.WARNING, e.toString(), e);
                        return NullValue.NULL;
                    }
                    return bb;
                }
            case Types.VARCHAR:
            case Types.LONGVARCHAR:
                if (env.isUnicodeSemantics()) {
                    return getUnicodeColumnString(env, rs, metaData, column);
                } else {
                    return getColumnString(env, rs, metaData, column);
                }
            case Types.TIME:
                return getColumnTime(env, rs, column);
            case Types.TIMESTAMP:
                return getColumnTimestamp(env, rs, column);
            case Types.DATE:
                return getColumnDate(env, rs, column);
            default:
                {
                    String strValue = rs.getString(column);
                    if (// || rs.wasNull())
                    strValue == null) {
                        return NullValue.NULL;
                    } else {
                        return env.createString(strValue);
                    }
                }
        }
    } catch (IOException e) {
        log.log(Level.FINE, e.toString(), e);
        return NullValue.NULL;
    } catch (SQLException e) {
        log.log(Level.FINE, e.toString(), e);
        return NullValue.NULL;
    }
}

@Override
public void set(BindingSetSQLOutputContext<U> ctx) throws SQLException {
    Configuration configuration = ctx.configuration();
    T value = converter.to(ctx.value());
    if (value == null) {
        ctx.output().writeObject(null);
    } else if (type == Blob.class) {
        ctx.output().writeBlob((Blob) value);
    } else if (type == Boolean.class) {
        ctx.output().writeBoolean((Boolean) value);
    } else if (type == BigInteger.class) {
        ctx.output().writeBigDecimal(new BigDecimal((BigInteger) value));
    } else if (type == BigDecimal.class) {
        ctx.output().writeBigDecimal((BigDecimal) value);
    } else if (type == Byte.class) {
        ctx.output().writeByte((Byte) value);
    } else if (type == byte[].class) {
        // Use reflection to avoid dependency on OJDBC
        if (isLob) {
            Blob blob = null;
            try {
                blob = on("oracle.sql.BLOB").call("createTemporary", on(ctx.output()).call("getSTRUCT").call("getJavaSqlConnection").get(), false, on("oracle.sql.BLOB").get("DURATION_SESSION")).get();
                blob.setBytes(1, (byte[]) value);
                ctx.output().writeBlob(blob);
            } finally {
                DefaultExecuteContext.register(blob);
            }
        } else {
            ctx.output().writeBytes((byte[]) value);
        }
    } else if (type == Clob.class) {
        ctx.output().writeClob((Clob) value);
    } else if (type == Date.class) {
        Date date = (Date) value;
        ctx.output().writeDate(date);
    } else if (type == Double.class) {
        ctx.output().writeDouble((Double) value);
    } else if (type == Float.class) {
        ctx.output().writeFloat((Float) value);
    } else if (type == Integer.class) {
        ctx.output().writeInt((Integer) value);
    } else if (type == Long.class) {
        ctx.output().writeLong((Long) value);
    } else if (type == Short.class) {
        ctx.output().writeShort((Short) value);
    } else if (type == String.class) {
        // Use reflection to avoid dependency on OJDBC
        if (isLob) {
            Clob clob = null;
            try {
                clob = on("oracle.sql.CLOB").call("createTemporary", on(ctx.output()).call("getSTRUCT").call("getJavaSqlConnection").get(), false, on("oracle.sql.CLOB").get("DURATION_SESSION")).get();
                clob.setString(1, (String) value);
                ctx.output().writeClob(clob);
            } finally {
                DefaultExecuteContext.register(clob);
            }
        } else {
            ctx.output().writeString((String) value);
        }
    } else if (type == Time.class) {
        ctx.output().writeTime((Time) value);
    } else if (type == Timestamp.class) {
        ctx.output().writeTimestamp((Timestamp) value);
    } else if (type == YearToMonth.class) {
        ctx.output().writeString(value.toString());
    } else if (type == DayToSecond.class) {
        ctx.output().writeString(value.toString());
    } else //        }
    if (UNumber.class.isAssignableFrom(type)) {
        ctx.output().writeString(value.toString());
    } else if (type == UUID.class) {
        ctx.output().writeString(value.toString());
    } else if (EnumType.class.isAssignableFrom(type)) {
        ctx.output().writeString(((EnumType) value).getLiteral());
    } else if (UDTRecord.class.isAssignableFrom(type)) {
        ctx.output().writeObject((UDTRecord<?>) value);
    } else {
        throw new UnsupportedOperationException("Type " + type + " is not supported");
    }
}

/**
     * Retrieves the <code>Rights</code> from the <code>Registry</code> that are associated with
     * a given <code>Role</code> and a <code>SecurableObject</code>.
     * 
     * @param securityAccess
     * @param object
     * @param role
     * @throws GeneralSecurityException
     */
public Right[] getRights(SecurityAccess securityAccess, SecurableObject object, Role role) throws GeneralSecurityException {
    ArrayList rights = new ArrayList();
    Connection con = acquireLocalConnection(securityAccess);
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        pstmt = con.prepareStatement("SELECT SEC_RIGHTS.ID,SEC_RIGHTS.NAME," + "SEC_JT_ROLES_SECOBJECTS.CONSTRAINTS FROM SEC_JT_ROLES_SECOBJECTS," + "SEC_RIGHTS WHERE SEC_JT_ROLES_SECOBJECTS.FK_ROLES=? AND " + "SEC_JT_ROLES_SECOBJECTS.FK_SECURABLE_OBJECTS=? AND " + "SEC_JT_ROLES_SECOBJECTS.FK_RIGHTS=SEC_RIGHTS.ID");
        pstmt.setInt(1, role.getID());
        pstmt.setInt(2, object.getID());
        rs = pstmt.executeQuery();
        ResultSetMetaData metadata = rs.getMetaData();
        int constraintType = metadata.getColumnType(3);
        while (rs.next()) {
            Right right = null;
            RightType type = new RightType(rs.getInt(1), rs.getString(2));
            String constraints = null;
            Object o = rs.getObject(3);
            if (o != null) {
                if (constraintType == Types.CLOB) {
                    Reader reader = ((Clob) o).getCharacterStream();
                    StringBuffer sb = new StringBuffer(2000);
                    int c;
                    try {
                        while ((c = reader.read()) > -1) {
                            sb.append((char) c);
                        }
                        reader.close();
                    } catch (IOException e) {
                        throw new GeneralSecurityException("Error converting CLOB to constraint string: " + e.getMessage());
                    }
                    constraints = sb.toString();
                } else {
                    constraints = o.toString();
                }
            }
            // check if the right has constraints
            if (constraints != null && constraints.length() > 3) {
                right = new Right(object, type, buildFilter(constraints));
            } else {
                right = new Right(object, type, null);
            }
            rights.add(right);
        }
    } catch (SQLException e) {
        LOG.logError(e.getMessage(), e);
        throw new GeneralSecurityException(e);
    } finally {
        closeResultSet(rs);
        closeStatement(pstmt);
        releaseLocalConnection(securityAccess, con);
    }
    return (Right[]) rights.toArray(new Right[rights.size()]);
}

// http://technology-ameyaaloni.blogspot.com/2010/06/mysql-to-hsql-migration-tips.html
/**
	 * 
	 */
public String formSql(String fieldinfo, String vendor) {
    Properties info = parseFormString(fieldinfo);
    String field = info.getProperty("field", null);
    String type = info.getProperty("type", null);
    if ("header".equals(type))
        return null;
    String maxs = info.getProperty("maxlength", null);
    int maxlength = 0;
    if (maxs != null)
        maxlength = (new Integer(maxs)).intValue();
    if (maxlength < 1)
        maxlength = 80;
    String required = info.getProperty("required", null);
    if (field == null || type == null) {
        throw new IllegalArgumentException("All model elements must include field name and type");
    }
    String schema = null;
    if ("key".equals(type)) {
        if ("hsqldb".equals(vendor)) {
            schema = "INTEGER IDENTITY PRIMARY KEY";
        } else if ("oracle".equals(vendor)) {
            schema = "INTEGER";
        } else {
            schema = "INTEGER NOT NULL AUTO_INCREMENT";
        }
    } else if ("autodate".equals(type)) {
        if ("oracle".equals(vendor)) {
            schema = "TIMESTAMP NOT NULL";
        } else {
            schema = "DATETIME NOT NULL";
        }
    } else if ("integer".equals(type)) {
        if ("oracle".equals(vendor)) {
            schema = "INTEGER";
        } else {
            schema = "INT";
        }
    } else if ("url".equals(type) || "text".equals(type) || "textarea".equals(type)) {
        if ("oracle".equals(vendor)) {
            if (maxlength < 4000) {
                schema = "VARCHAR2(" + maxlength + ")";
            } else {
                schema = "CLOB";
            }
        } else if ("hsqldb".equals(vendor)) {
            schema = "VARCHAR(" + maxlength + ")";
        } else {
            if (maxlength < 512) {
                schema = "VARCHAR(" + maxlength + ")";
            } else {
                schema = "TEXT(" + maxlength + ")";
            }
        }
    } else if ("radio".equals(type) || "checkbox".equals(type)) {
        if ("oracle".equals(vendor)) {
            schema = "NUMBER(1) DEFAULT '0'";
        } else {
            schema = "TINYINT DEFAULT '0'";
        }
    }
    if (schema == null)
        return null;
    //schema += " NOT NULL";
    return "    " + field + " " + schema;
}

/**
	 * This method issues special blob-inserts command to the db. 
	 * I had to build my own adoption of sql to make this feature.
	 */
protected void issueSpecialBlobCommand(Connection conn, String originalSql) throws Exception {
    String sql = originalSql;
    try {
        String valuesPart = sql.substring(sql.indexOf("VALUES") + 6).trim();
        sql = sql.substring(0, sql.indexOf("VALUES") + 6);
        //logger.info("sql:" + sql);
        //logger.info("valuesPart:" + valuesPart);
        String tableName = null;
        int blobColumn = 0;
        List columns = null;
        List columnValues = null;
        StringTokenizer st = new StringTokenizer(sql, " ");
        int i = 0;
        while (st.hasMoreTokens()) {
            String part = st.nextToken();
            if (i == 1)
                blobColumn = new Integer(part).intValue();
            if (i == 4)
                tableName = part;
            if (i == 5) {
                columns = parseColumns(part);
            }
            i++;
        }
        columnValues = parseValues(valuesPart);
        String columnsString = "";
        String valuesString = "";
        Iterator columnsIterator = columns.iterator();
        while (columnsIterator.hasNext()) {
            columnsString += (columnsString.equals("")) ? (String) columnsIterator.next() : "," + columnsIterator.next();
            valuesString += (valuesString.equals("")) ? "?" : ",?";
        }
        sql = "INSERT INTO " + tableName + "(" + columnsString + ") VALUES (" + valuesString + ")";
        PreparedStatement ps = conn.prepareStatement(sql);
        int index = 1;
        int loopCount = 0;
        Iterator columnValuesIterator = columnsIterator = columns.iterator();
        while (columnsIterator.hasNext()) {
            columnsIterator.next();
            String value = (String) columnValues.get(loopCount);
            if (index == 1 || value.indexOf("'") == -1) {
                ps.setInt(index, new Integer(value).intValue());
            } else if (index == blobColumn) {
                //Logger.logInfo("value:" + value);
                value = value.substring(1, value.length() - 1);
                if (value.indexOf("assetBlob:") > -1) {
                    String fileName = value.substring(10);
                    FileInputStream fis = new FileInputStream(fileName);
                    BLOB bl = BLOB.createTemporary(conn, true, BLOB.DURATION_CALL);
                    bl.open(BLOB.MODE_READWRITE);
                    BufferedOutputStream out = new BufferedOutputStream(bl.getBinaryOutputStream());
                    byte[] buffer = new byte[1024];
                    int len;
                    while ((len = fis.read(buffer)) >= 0) out.write(buffer, 0, len);
                    out.flush();
                    fis.close();
                    out.close();
                    ps.setBlob(index, bl);
                } else {
                    CLOB cl = CLOB.createTemporary(conn, true, CLOB.DURATION_CALL);
                    cl.putString(1, value);
                    ps.setClob(index, cl);
                }
            } else if (value.indexOf("date:") > -1) {
                value = value.substring(6);
                Date date = parseDate(value, "yyyy-MM-dd HH:mm:ss");
                ps.setDate(index, new java.sql.Date(date.getTime()));
            } else {
                //Logger.logInfo("value:" + value);
                value = value.substring(1, value.length() - 1);
                //Logger.logInfo("value:" + value);
                ps.setString(index, value);
            }
            index++;
            loopCount++;
        }
        ps.executeUpdate();
    } catch (Exception ex) {
        logger.error("Command failed: " + ex.getMessage());
        logger.error("SQL: " + originalSql);
        throw ex;
    }
}

@Override
public void releaseClob(Connection connection, Clob clob) throws SQLException {
    if (clob instanceof CLOB) {
        ((CLOB) clob).freeTemporary();
    }
}

public int[] sqlTypes() {
    return new int[] { Types.CLOB };
}

@Override
public CLOB getCLOB(int arg0) throws SQLException {
    // TODO Auto-generated method stub
    return null;
}