/* This software is OSI Certified Open Source Software. OSI Certified is a certification mark of the Open Source Initiative. The license (Mozilla version 1.0) can be read at the MMBase site. See http://www.MMBase.org/license */ package org.mmbase.module.database.support; import java.io.IOException; import java.io.Reader; import java.io.Writer; import java.sql.Clob; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Enumeration; import java.util.Iterator; import java.util.List; import java.util.Vector; import oracle.sql.CLOB; import org.mmbase.module.core.MMObjectBuilder; import org.mmbase.module.core.MMObjectNode; import org.mmbase.module.corebuilders.FieldDefs; import org.mmbase.module.corebuilders.InsRel; import org.mmbase.module.database.MultiConnection; import org.mmbase.util.logging.Logger; import org.mmbase.util.logging.Logging; /** * Database driver with views driver for MMBase * @deprecated This code is scheduled for removal once MMBase has been fully converted to the new * StorageManager implementation. * @author Eduard Witteveen * @version $Id: Sql92WithViews.java,v 1.9 2004/08/04 13:08:34 nico_klasens Exp $ * @since MMBase-1.7 */ /* BLOBs and binary files. Difference MySQL and Oracle BLOBs (Binary Large Objects) MySQL supports several different data types to handle BLOBs, e.g. TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB. The difference is the maximum size of the BLOB and the amount of bytes used per record. Oracle 8 and 9 support LOBs (Large Objects) with either character data (CLOB) or binary data (BLOB), The difference lying in the way the data is translated to/from different character sets. BLOBs are never translated. MySQL uses a string interface to BLOBs which is simple to use, you insert data as a string and you retrieve it as a string. Oracle has a special LOB interface which makes is possible to read or modify only parts of the LOB. This makes the SQL interface much more complex to deal with, and Oracle also requires that data is inserted using a hex representation rather than a binary one. There is an Oracle SQL function, RAWTOHEX, which converts from binary to hex but if the binary data contains a null character (ASCII 0) then the Oracle driver barfs and the SQL expression fails. There are size restrictions on Oracle SQL statements as well, the longest SQL expression is only about 64 kb which means you can only insert about 32 kb using SQL. I think. But you can always write the data using the LOB interface *********************************** WRITING DATA USING A LOB First you need a reference to the LOB. Then you write the data to the LOB, which buffers it. Finally, you update the LOB column with the modified LOB, like this: //Use oracle.sql.BLOB because java.sql.Blob lacks setBytes() //JDBC3 java.sql.Blob adds the method setBytes(int,byte[]) //Oracle JDBC uses the method putBytes(int,byte[]) //Execute a select query. The ResultSet is rs oracle.sql.BLOB dbBlob = (oracle.sql.BLOB)rs.getBlob(1); //update blob ps = cxn.prepareStatement(sqlSetBlob); ps.setString(2,SomeValue); dbBlob.putBytes(1,binaryArray); // You can't do this: // ps.setBinaryStream(1,new BinaryInputStream(binaryArray),binaryArray.length); // You must do it like this: // ps.setBlob(1,dbBlob); // Which is weird because you CAN do this: // InputStream is = rs.getBinaryStream(1); // Note that if the column were declared LONGVARBINARY then // setBinaryStream() would work. ps.setBlob(1,dbBlob); cxn.commit(); *********************************** INSERTING ROWS THAT CONTAIN LOB COLUMNS This is trickier than doing updates because you can't write LOB data until you have a reference to the LOB, and you can't get a reference to the LOB until after you insert the row. So you start like this. String sqlNewRow = "INSERT INTO BLOB_TABLE (BLOB_ID,BLOB_DATA) " + "VALUES (?,EMPTY_BLOB())", sqlLockRow = "SELECT BLOB_DATA FROM BLOB_TABLE " + "WHERE BLOB_ID = ? FOR UPDATE", sqlSetBlob = "UPDATE BLOB_TABLE " + "SET BLOB_DATA = ? " + "WHERE BLOB_ID = ?"; Connection cxn = DriverManager.getConnection( connect_string,username_string,password_string); cxn.setAutoCommit(false); //make new row PreparedStatement ps = cxn.prepareStatement(sqlNewRow); ps.setString(1,SomeValue); ps.executeUpdate(); //lock new row ps = cxn.prepareStatement(sqlLockRow); ps.setString(1,SomeValue); ResultSet rs = ps.executeQuery(); rs.next(); The expression EMPTY_BLOB() is Oracle specific. This function manufactures a LOB for insertion into the column. You must do this, because if you don't, when you try to update the row you won't get a LOB, you'll get NULL. At this point you update the row as described in the preceding section (WRITING DATA USING A LOB). WHY "lock new row" ? LOB data is not stored in the table proper, and is not even necessarily stored in the database per se. LOB data can be stored anywhere, even in a file accessed via a network file system. It is therefore outside the scope of the conventional database locking mechanism. Under such conditions it might be quite a lengthy process to obtain a lock, so for performance reasons LOB locks are not obtained unless you explicitly request them. *********************************** READING LOB DATA Although you can use getAsciiStream() or getBinaryStream(), reading LOB data is not entirely straightforward. Before you can retrieve the data, you need to allocate a byte[] buffer sized to accommodate the data. Here's the catch: the stream reports its size according to the amount of data in the JDBC buffer. Before you retrieve the data, this is zero. It gets worse. You cannot read the data twice, once to get the size and once to actually acquire the data, because the stream is forward-only. sqlLockRow = "SELECT BLOB_DATA FROM BLOB_TABLE " + "WHERE BLOB_ID = ? FOR UPDATE", //lock row ps = cxn.prepareStatement(sqlLockRow); ps.setString(1,SomeValue); ResultSet rs = ps.executeQuery(); byte[] returndata = null; rs.next() // The ByteArrayOutputStream buffers all bytes written to it // until we call getBytes() which returns to us an // array of bytes: ByteArrayOutputStream baos = new ByteArrayOutputStream(1024); // Create an input stream from the BLOB column. // By default, rs.getBinaryStream() // returns a vanilla InputStream instance. // We override this for efficiency // but you don't have to: BufferedInputStream bis = new BufferedInputStream( rs.getBinaryStream("BLOB_DATA") ); // A temporary buffer for the byte data: byte bindata[1024]; // Used to return how many bytes are read with each read() of the input stream: int bytesread = 0; // Make sure its not a NULL value in the column: if ( rs.wasNull() ) { return returndata; ) while ( (bytesread = bis.read(bindata,0,bindata.length)) != -1 ) { // Write out 'bytesread' bytes to the writer instance: baos.write(bindata,0,bytesread); } // When the read() method returns -1 we've hit the end of // the stream, // so now we can get our bytes out of the writer object: returndata = baos.getBytes(); // Close the binary input stream: bis.close(); return returndata; */ public class Sql92WithViews extends Sql92SingleFields implements MMJdbc2NodeInterface { private static Logger log = Logging.getLoggerInstance(Sql92WithViews.class.getName()); //variables for placing db object in the right tablespaces //TODO: place the properties in a property file instead of in the code public static final String DATA_TABLESPACE_NAME = "mmbase_data"; public static final String INDEX_TABLESPACE_NAME = "mmbase_index"; protected boolean createSequence() { // CREATE SEQUENCE autoincrement INCREMENT BY 1 START WITH 1 MultiConnection con = null; Statement stmt = null; String sql = "CREATE SEQUENCE "+sequenceTableName()+" INCREMENT BY 1 START WITH 1"; try { log.debug("gonna execute the following sql statement: " + sql); con = mmb.getConnection(); stmt=con.createStatement(); stmt.executeUpdate(sql); stmt.close(); con.close(); } catch (SQLException sqle) { log.error("error, could autoincrement sequence.."+sql); for(SQLException e = sqle;e != null; e = e.getNextException()) { log.error("\tSQLState : " + e.getSQLState()); log.error("\tErrorCode : " + e.getErrorCode()); log.error("\tMessage : " + e.getMessage()); } try { if(stmt!=null) stmt.close(); con.close(); } catch(Exception other) {} return false; } return true; } private String getTableName(MMObjectBuilder bul) { boolean inheritedTable = (getInheritTableName(bul) != null); String tableName = mmb.baseName+"_"+bul.getTableName(); // there are 2 types of table's: // when it is an inherited table, add the '_TABLE' extension, // the view will get the name that shows everything. if (inheritedTable) { tableName += "_TABLE"; } return tableName; } public boolean create(MMObjectBuilder bul) { String tableName = getTableName(bul); log.info("create table definition for:'" + tableName + "'"); // is this the top level superclass? boolean inheritedTable = (getInheritTableName(bul) != null); String sql = null; MultiConnection con=null; Statement stmt=null; // look if we really have to create the table // maybe only view is missing if(!created(tableName) ) { // first thing to do, when object table is not created, is creation of sequence,.. if (!inheritedTable) createSequence(); // please beware that oracle specific code has been added. tablespace the table has to be stored in has been added sql = "CREATE TABLE " + tableName + "(" + getFieldList(bul, false, true, false) + getFieldContrains(bul) + ") tablespace " + DATA_TABLESPACE_NAME; log.info("create table definition for:'" + tableName + "' with sql:" + sql ); try { con=mmb.getConnection(); stmt=con.createStatement(); stmt.executeUpdate(sql); stmt.close(); con.close(); } catch (SQLException sqle) { log.error("error, could not create table for builder " + bul.getTableName() + " with sql:" + sql); for(SQLException e = sqle;e != null; e = e.getNextException()) { log.error("\tSQLState : " + e.getSQLState()); log.error("\tErrorCode : " + e.getErrorCode()); log.error("\tMessage : " + e.getMessage()); } try { if(stmt!=null) stmt.close(); con.close(); } catch(Exception other) {} return false; } } // when it is not the inherited table, no need to create an view of it,... // so we leave this method.. if (!inheritedTable) return true; String viewName = mmb.baseName + "_" + bul.getTableName(); String parentTable = mmb.baseName + "_" + getInheritTableName(bul); sql = "CREATE VIEW " + viewName + " ( " + getFieldList(bul, true, false, false) + ")"; sql += " AS SELECT " + getFieldList(bul, true, false, true); sql += " FROM " + tableName + ", " + parentTable; sql += " WHERE " + tableName + "." + getNumberString() + " = " + parentTable + "." + getNumberString(); log.info("create view definition for:'" + viewName + "' with sql:" + sql ); try { con=mmb.getConnection(); stmt=con.createStatement(); stmt.executeUpdate(sql); stmt.close(); con.close(); // everything wend well,.. log.debug("succes creating datastructure for: " + bul.getTableName()); return true; } catch (SQLException sqle) { log.error("error, could not create view for builder " + bul.getTableName() + " with sql:" + sql); for(SQLException e = sqle;e != null; e = e.getNextException()) { log.error("\tSQLState : " + e.getSQLState()); log.error("\tErrorCode : " + e.getErrorCode()); log.error("\tMessage : " + e.getMessage()); } try { if(stmt!=null) stmt.close(); con.close(); } catch(Exception other) {} } return false; } private String getFieldList(MMObjectBuilder bul, boolean addInheritedFields, boolean addDeclaration, boolean numberReferencesObject) { // retrieve the field of the builder Vector sfields = (Vector) bul.getFields(FieldDefs.ORDER_CREATE); if(sfields == null) { log.error("sfield was null for builder with name :" + bul); return ""; } String fieldList=null; // process all the fields.. for (Enumeration e = sfields.elements();e.hasMoreElements();) { String name=((FieldDefs)e.nextElement()).getDBName(); FieldDefs def = bul.getField(name); if (def.getDBState() != org.mmbase.module.corebuilders.FieldDefs.DBSTATE_VIRTUAL) { // also add explicit the number string to extending table's, // this way an index _could_ be created on extending stuff... if(addInheritedFields || !isInheritedField(bul, name) || getNumberString().equals(getAllowedField(name)) ) { String part; if(addDeclaration) { log.trace("trying to retrieve the part for field : " + name); part = getDbFieldDef(def, bul); log.trace("adding field " + name + " with SQL-subpart: " + part); } else { if(addInheritedFields && getNumberString().equals(getAllowedField(name)) && numberReferencesObject) { // numberstring occurs everywhere,... so be explicit from which table,.. part = mmb.baseName + "_" + getInheritTableName(bul) + "." + getNumberString(); } else part = getAllowedField(def.getDBName()); } // add to the list if (fieldList==null) { fieldList = part; } else { fieldList+=", " + part; } } } } //if all fields are inherited the field list can be empty if (fieldList == null) fieldList=""; // return the result return fieldList; } protected String getDbFieldDef(FieldDefs def, MMObjectBuilder bul) { // create the creation line of one field... // would be something like : fieldname FIELDTYPE NOT NULL KEY " // first get our thingies... String fieldName = getAllowedField(def.getDBName()); boolean fieldRequired = def.getDBNotNull(); boolean fieldIsReferer = isReferenceField(def, bul); boolean fieldIsPrimaryKey = getNumberString().equals(fieldName); boolean inheritedTable = (getInheritTableName(bul) != null); String fieldType = getDbFieldType(def, def.getDBSize(), fieldRequired); String result = fieldName + " " + fieldType; if(fieldRequired) { result += " NOT NULL "; } return result; } private String getFieldContrains(MMObjectBuilder bul) { // retrieve the field of the builder Vector sfields = (Vector) bul.getFields(FieldDefs.ORDER_CREATE); if(sfields == null) { log.error("sfield was null for builder with name :" + bul); return ""; } String constrains = ""; // process all the fields.. for (Enumeration e = sfields.elements();e.hasMoreElements();) { String name = ((FieldDefs)e.nextElement()).getDBName(); FieldDefs def = bul.getField(name); if (def.getDBState() != org.mmbase.module.corebuilders.FieldDefs.DBSTATE_VIRTUAL) { String fieldName = getAllowedField(def.getDBName()); // first get our thingies... boolean fieldIsPrimaryKey = getNumberString().equals(fieldName); if(fieldIsPrimaryKey || !isInheritedField(bul, fieldName)) { boolean inheritedTable = getInheritBuilder(bul) != null; boolean fieldIsReferer = isReferenceField(def, bul) && inheritedTable; boolean fieldUnique = def.isKey(); // please beware that oracle specific code has been added. tablespace the index has to be stored in has been added if(fieldIsPrimaryKey) { constrains += ", CONSTRAINT " + constrainName(bul) + " PRIMARY KEY ( " + fieldName + " ) using index tablespace " + INDEX_TABLESPACE_NAME; // if this is the primary key, it reference's the main table,.. fieldIsReferer = inheritedTable; } // we cannot reference to itselve,incase of a reference else if(fieldIsReferer) { constrains += ", CONSTRAINT " + constrainName(bul) + " FOREIGN KEY (" + fieldName +")"; constrains += " REFERENCES " + objectTableName() + " (" + getNumberString() + ") ON DELETE CASCADE"; } // is this field unique? (and not primary key, since that one always is // unique else if(fieldUnique) { constrains += ", CONSTRAINT " + constrainName(bul) + " UNIQUE (" + fieldName +") USING INDEX tablespace " + INDEX_TABLESPACE_NAME; } } } } // return the result log.debug("fieldcontrains for table:" + bul + " are:" + constrains); return constrains; } /** * @returns a unique contrain name */ private String constrainName(MMObjectBuilder bul) { return mmb.baseName + "_CONTRAIN_" + getDBKey(); } /** * @return a new unique number for new nodes or -1 on failure */ public synchronized int getDBKey() { MultiConnection con=null; Statement stmt=null; // select test8_autoincrement.nextval from dual; String sql = "SELECT " + sequenceTableName() + ".NEXTVAL FROM DUAL"; int number = -1; try { log.debug("gonna execute the following sql statement: " + sql); con = mmb.getConnection(); stmt=con.createStatement(); ResultSet rs = stmt.executeQuery(sql); try { if (rs.next()) { number=rs.getInt("NEXTVAL"); } else { log.warn("could not retieve the number for new node"); } } finally { rs.close(); } stmt.close(); con.close(); } catch (SQLException sqle) { log.error("error, could not retrieve new object number:"+sql); for(SQLException se = sqle;se != null; se = se.getNextException()) { log.error("\tSQL:" + sql); log.error("\tSQLState : " + se.getSQLState()); log.error("\tErrorCode : " + se.getErrorCode()); log.error("\tMessage : " + se.getMessage()); } try { if(stmt!=null) stmt.close(); con.close(); log.info("gonna try to create a new sequence,... maybe missing,.."); // try to create a new sequence,.. maybe wasnt there yet,.. createSequence(); } catch(Exception other) {} throw new RuntimeException(sqle.toString()); } log.debug("new object id #"+number); return number; } public void removeNode(MMObjectBuilder bul,MMObjectNode node) { int number=node.getIntValue("number"); Vector rels=bul.getRelations_main(number); if (rels != null && rels.size() > 0) { // we still had relations .... log.error("still relations attachched : delete from "+mmb.baseName+"_"+bul.tableName+" where "+getNumberString()+"="+number); return; } if (number==-1) { // this is an undefined node... log.error("undefined node : delete from "+mmb.baseName+"_"+bul.tableName+" where "+getNumberString()+"="+number +"("+node+")"); return; } // cascading delete should take care of all the references,.. String sql = "DELETE FROM " + objectTableName() +" WHERE "+getNumberString()+" = "+number; MultiConnection con = null; Statement stmt = null; log.debug("removing node with sql:" + sql); try { con=mmb.getConnection(); stmt=con.createStatement(); stmt.executeUpdate(sql); stmt.close(); con.close(); } catch (SQLException sqle) { for(SQLException e = sqle;e != null; e = e.getNextException()) { log.error("\tSQL : " + sql); log.error("\tSQLState : " + e.getSQLState()); log.error("\tErrorCode : " + e.getErrorCode()); log.error("\tMessage : " + e.getMessage()); } log.error(Logging.stackTrace(sqle)); try { if(stmt != null) stmt.close(); con.close(); } catch(Exception other) {} return; } if (bul.broadcastChanges) { mmb.mmc.changedNode(node.getIntValue("number"),bul.tableName,"d"); if (bul instanceof InsRel) { MMObjectNode n1=bul.getNode(node.getIntValue("snumber")); MMObjectNode n2=bul.getNode(node.getIntValue("dnumber")); mmb.mmc.changedNode(n1.getIntValue("number"),n1.parent.getTableName(),"r"); mmb.mmc.changedNode(n2.getIntValue("number"),n2.parent.getTableName(),"r"); } } } protected int insertRecord(MMObjectBuilder bul,String owner, MMObjectNode node) { // insert records recursive, starting with the highest table first,.. MMObjectBuilder parent = getInheritBuilder(bul); if (parent != null) insertRecord(parent, owner, node); String tableName = getTableName(bul); String sql = insertPreSQL(bul, ((Vector) bul.getFields(FieldDefs.ORDER_CREATE)).elements(), node); MultiConnection con=null; PreparedStatement preStmt=null; // Insert statements, with fields still empty.. try { // Create the DB statement with DBState values in mind. log.debug("executing following insert : " + sql); con=bul.mmb.getConnection(); preStmt=con.prepareStatement(sql); } catch (SQLException sqle) { log.error("error, could not insert record for builder " + tableName + " with sql:" + sql); for(SQLException se = sqle;se != null; se = se.getNextException()) { log.error("\tSQL : " + sql); log.error("\tSQLState : " + se.getSQLState()); log.error("\tErrorCode: " + se.getErrorCode()); log.error("\tMessage : " + se.getMessage()); } try { if(preStmt!=null) preStmt.close(); con.close(); } catch(Exception other) {} throw new RuntimeException(sqle.toString()); } // when an error occures, we know our field-state info... FieldDefs currentField = null; int current = 1; List clobsToUpdate = new ArrayList(); // Now fill the fields try { // why did we do the next? // preStmt.setEscapeProcessing(false); Enumeration enumeration = ((Vector) bul.getFields(FieldDefs.ORDER_CREATE)).elements(); while (enumeration.hasMoreElements()) { currentField = (FieldDefs) enumeration.nextElement(); String key = currentField.getDBName(); int DBState = node.getDBState(key); if (!isInheritedField(bul, key) || getNumberString().equals(getAllowedField(key))) { if ( (DBState == org.mmbase.module.corebuilders.FieldDefs.DBSTATE_PERSISTENT) || ((DBState == org.mmbase.module.corebuilders.FieldDefs.DBSTATE_SYSTEM) || ((DBState == org.mmbase.module.corebuilders.FieldDefs.DBSTATE_UNKNOWN) && node.getName().equals("typedef"))) ) { if (log.isDebugEnabled()) log.trace("DBState = "+DBState+", setValuePreparedStatement for key: "+key+", at pos:"+current+ " value:" + node.getStringValue(key)); if (node.getValue(key) != null) { // if it's a blob store in map and process later (select for update) if (isClob(currentField)) { clobsToUpdate.add(key); } else { setValuePreparedStatement( preStmt, node, key, current); } log.trace("we did set the value for field " + key + " with the number " + current); current++; } } else if (DBState == org.mmbase.module.corebuilders.FieldDefs.DBSTATE_VIRTUAL) { log.trace("DBState = "+DBState+", skipping setValuePreparedStatement for key: "+key); } else { log.warn("DBState = "+DBState+" unknown!, skipping setValuePreparedStatement for key: "+key+" of builder:"+node.getName()); } } else { log.trace("DBState = "+DBState+", skipping setValuePreparedStatement for key: "+key); } } preStmt.executeUpdate(); preStmt.close(); con.close(); } catch (SQLException sqle) { log.error("error, could not insert record for builder " + bul.getTableName()+ " current field:("+current+")"+currentField); // log.error(Logging.stackTrace(sqle)); for(SQLException se = sqle;se != null; se = se.getNextException()) { log.error("\tSQL : " + sql); log.error("\tSQLState : " + se.getSQLState()); log.error("\tErrorCode: " + se.getErrorCode()); log.error("\tMessage : " + se.getMessage()); } try { if(preStmt!=null) preStmt.close(); con.close(); } catch(Exception other) {} throw new RuntimeException(sqle.toString()); } if (!clobsToUpdate.isEmpty()) { updateClobFields(node, tableName, clobsToUpdate); } return node.getIntValue("number"); } /** * update Blob Fields. THis method updates the blob fields of a row in the * DB. The insert and update for the other fields than BLOBS should be done * by the calling method. This method locks the row with an select sql * statement and updates the blob * * @param node * node with the blobs to update * @param tableName * name of table where the blbos should be stored * @param number * number of node * @param blobFields * array of blob field names * * @throws SQLException */ private void updateClobFields(MMObjectNode node,String tableName,List clobFields) { int number = node.getIntValue("number"); MultiConnection con = null; PreparedStatement stmt = null; ResultSet rs = null; boolean oldAutoCommit = true; String sqlClobLock = ""; try { con = mmb.getConnection(); oldAutoCommit = con.getAutoCommit(); con.setAutoCommit(false); sqlClobLock = ""; Iterator clobIter = clobFields.iterator(); while (clobIter.hasNext()) { String fieldname = ((String) clobIter.next()).toLowerCase(); // is this fieldname disallowed ? ifso map it back if (allowed2disallowed.containsKey(fieldname)) { fieldname = (String) allowed2disallowed.get(fieldname); } sqlClobLock = sqlClobLock + fieldname; if (clobIter.hasNext()) { sqlClobLock = sqlClobLock + ", "; } } String numbername = getNumberString(); sqlClobLock = "SELECT " + sqlClobLock + " FROM " + tableName+ " WHERE " + numbername + " = ? FOR UPDATE"; if (log.isDebugEnabled()) log.debug("SQL Clob Lock = " + sqlClobLock); stmt = con.prepareStatement(sqlClobLock); stmt.setInt(1, number); rs = stmt.executeQuery(); if (rs.next()) { Iterator clob2Iter = clobFields.iterator(); while (clob2Iter.hasNext()) { String key = ((String) clob2Iter.next()); String fieldname = key.toLowerCase(); // is this fieldname disallowed ? ifso map it back if (allowed2disallowed.containsKey(fieldname)) { fieldname = (String) allowed2disallowed.get(fieldname); } //Use oracle.sql.CLOB because java.sql.Blob lacks CLOB dbClob = (CLOB)rs.getClob(fieldname); if (log.isDebugEnabled()) log.debug("Clob Update = " + fieldname + "value = " + node.getStringValue(key)); Writer writer = null; try { writer = dbClob.getCharacterOutputStream(); writer.write(node.getStringValue(key)); } catch (IOException ioe) { log.error("Exception:" + ioe.toString()); log.error(Logging.stackTrace(ioe)); } finally { try { if (writer != null) { writer.close(); } } catch (IOException e1) { // no clue what to do } } } } } catch (SQLException sqe) { log.error("Exception:"+sqe.toString()+" while executing query: " + sqlClobLock); log.error(Logging.stackTrace(sqe)); } finally { try { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } if (con != null) { con.commit(); con.setAutoCommit(oldAutoCommit); con.close(); } } catch (SQLException sqe) { log.error("Exception:"+sqe.toString()); log.error(Logging.stackTrace(sqe)); } } } private boolean isClob(FieldDefs field) { return field.getDBType()==FieldDefs.TYPE_STRING && field.getDBSize()>4000; } private String insertPreSQL(MMObjectBuilder bul, Enumeration fieldLayout, MMObjectNode node) { String fieldNames = null; String fieldValues = null; // Append the DB elements to the fieldAmounts String. while(fieldLayout.hasMoreElements()) { FieldDefs field = ((FieldDefs) fieldLayout.nextElement()); String key = field.getDBName(); String fieldName = getAllowedField(key); int DBState = node.getDBState(key); // only create for the fields that are of this builder,.. log.trace("Insert: fieldname:" + key); if (!isInheritedField(bul, key) || getNumberString().equals(getAllowedField(key))) { if ( DBState == org.mmbase.module.corebuilders.FieldDefs.DBSTATE_PERSISTENT || DBState == org.mmbase.module.corebuilders.FieldDefs.DBSTATE_SYSTEM ) { if (node.getValue(key) != null) { // add the values to our lists.... if (fieldNames == null) { fieldNames = fieldName; } else { fieldNames += ", " + fieldName; } if (fieldValues == null) { if (isClob(field)) { fieldValues = "empty_clob()"; } else { fieldValues = "?"; } } else { if (isClob(field)) { fieldValues += ", empty_clob()"; } else { fieldValues += ", ?"; } } } } else if (DBState == org.mmbase.module.corebuilders.FieldDefs.DBSTATE_VIRTUAL) { log.trace("Insert: DBState = "+DBState+", skipping vitual field: "+key); } else { if (node.getValue(key) != null) { if ((DBState == org.mmbase.module.corebuilders.FieldDefs.DBSTATE_UNKNOWN) && node.getName().equals("typedef")) { // add the values to our lists.... if (fieldNames == null) { fieldNames = fieldName; } else { fieldNames += ", " + fieldName; } if(fieldValues == null) { fieldValues = "?"; } else { fieldValues += ", ?"; } } else { log.error("Insert: DBState = "+DBState+" unknown!, skipping key: "+key+" of builder:"+node.getName()); } } } } else { log.trace("Insert: skipping inherited field" + key); } } String sql = "INSERT INTO " + getTableName(bul) + " ("+ fieldNames+") VALUES ("+fieldValues+")"; log.trace("created pre sql: " + sql); return sql; } private boolean commitTable(MMObjectBuilder bul,MMObjectNode node) { // commit records recursive, starting with the highest table first,.. MMObjectBuilder parent = getInheritBuilder(bul); if (parent != null) commitTable(parent, node); List clobFields = new ArrayList(); if (!node.getChanged().isEmpty()) { // precommit call, needed to convert or add things before a save bul.preCommit(node); } // the update statement,... String builderFieldSql = null; // create the prepared statement for (Enumeration e = node.getChanged().elements();e.hasMoreElements();) { String key = (String)e.nextElement(); if (isBuilderField(bul, key) && !isInheritedField(bul, key)) { // not allowed as far as im concerned... if(key.equals("number")) { log.fatal("trying to change the 'number' field"); throw new RuntimeException("trying to change the 'number' field"); } else if(key.equals("otype")) { // hmm i dont like the idea of changing the otype.. log.error("changing the otype field, is this really needed? i dont think so, but hey i dont care.."); } FieldDefs field = bul.getField(key); // is this key disallowed ? ifso map it back String escapedKey = getAllowedField(key); if (isClob(field)) { if (builderFieldSql == null) { builderFieldSql = escapedKey + " = empty_clob()"; } else { builderFieldSql += ", " + escapedKey + " = empty_clob()"; } clobFields.add(field.getDBName()); } else { // add the fieldname,.. and do smart ',' mapping if (builderFieldSql == null) { builderFieldSql = escapedKey + "=?"; } else { builderFieldSql += ", " + escapedKey + "=?"; } } } } // add all changed fields... // when we had a update... // it can happen that in this table, no updates are needed, so dont do it! if(builderFieldSql != null) { String sql = "UPDATE " + getTableName(bul); sql += " SET " + builderFieldSql + " WHERE " + getNumberString() + " = " + node.getValue("number"); MultiConnection con = null; PreparedStatement stmt = null; try { // start with the update of builder itselve first.. con=mmb.getConnection(); stmt = con.prepareStatement(sql); // fill the '?' thingies with the values from the nodes.. Enumeration changedFields = node.getChanged().elements(); int currentParameter = 1; while(changedFields.hasMoreElements()) { String key = (String) changedFields.nextElement(); if (isBuilderField(bul, key) && !isInheritedField(bul, key)) { FieldDefs field = bul.getField(key); if (!isClob(field)) { setValuePreparedStatement(stmt, node, key, currentParameter); currentParameter++; } } } stmt.executeUpdate(); stmt.close(); con.close(); } catch (SQLException sqle) { for(SQLException e = sqle;e != null; e = e.getNextException()) { log.error("\tSQL : " + sql); log.error("\tSQLState : " + e.getSQLState()); log.error("\tErrorCode : " + e.getErrorCode()); log.error("\tMessage : " + e.getMessage()); } log.error(Logging.stackTrace(sqle)); try { if(stmt != null) stmt.close(); con.close(); } catch(Exception other) {} return false; } } if (!clobFields.isEmpty()) { updateClobFields(node, getTableName(bul), clobFields); } return true; } public boolean commit(MMObjectBuilder bul,MMObjectNode node) { // do the commit on the table,.. // quit if fails if (!commitTable(bul, node)) return false; // done database update, so clear changed flags.. node.clearChanged(); // broadcast the changes, if nessecary... if (bul.broadcastChanges) { if (bul instanceof InsRel) { bul.mmb.mmc.changedNode(node.getIntValue("number"),bul.tableName,"c"); // figure out tables to send the changed relations MMObjectNode n1=bul.getNode(node.getIntValue("snumber")); MMObjectNode n2=bul.getNode(node.getIntValue("dnumber")); mmb.mmc.changedNode(n1.getIntValue("number"),n1.parent.getTableName(),"r"); mmb.mmc.changedNode(n2.getIntValue("number"),n2.parent.getTableName(),"r"); } else { mmb.mmc.changedNode(node.getIntValue("number"),bul.tableName,"c"); } } // done ! return true; } public boolean created(String tableName) { tableName = tableName.toUpperCase(); // String sql = "SELECT COUNT(*) FROM " + tableName.toUpperCase(); String sql = ""; if (tableName.endsWith("_OBJECT")) { sql = "select 1 from user_tables where table_name = '" + tableName + "'"; } else { sql = "select 1 from user_views where view_name = '" + tableName + "'"; } MultiConnection con=null; Statement stmt=null; try { con=mmb.getConnection(); stmt=con.createStatement(); ResultSet rs = stmt.executeQuery(sql); return rs.next(); } catch (SQLException sqle) { return false; } finally { try { if (stmt != null) { stmt.close(); } } catch(Exception e) {} try { if (con != null) { con.close(); } } catch(Exception e) {} } } public String getDisallowedField(String allowedfield) { log.trace(allowedfield); allowedfield = allowedfield.toLowerCase(); if (allowed2disallowed.containsKey(allowedfield)) { allowedfield=(String)allowed2disallowed.get(allowedfield); } return allowedfield; } public String getAllowedField(String disallowedfield) { log.trace(disallowedfield); disallowedfield = disallowedfield.toLowerCase(); if (disallowed2allowed.containsKey(disallowedfield)) { disallowedfield=(String)disallowed2allowed.get(disallowedfield); } return disallowedfield; } public boolean addField(MMObjectBuilder bul,String fieldname) { log.debug("addField"); log.fatal("This function is not implemented !!"); throw new UnsupportedOperationException("addField"); } public boolean removeField(MMObjectBuilder bul,String fieldname) { log.debug("removeField"); log.fatal("This function is not implemented !!"); throw new UnsupportedOperationException("removeField"); } public boolean changeField(MMObjectBuilder bul,String fieldname) { log.debug("changeField"); log.fatal("This function is not implemented !!"); throw new UnsupportedOperationException("changeField"); } public boolean createObjectTable(String notUsed) { log.debug("createObjectTable"); log.fatal("This function is not implemented !!"); throw new UnsupportedOperationException("createObjectTable"); } /** * get String of a database clob */ public String readClobData(ResultSet rs,int idx) { // Holds the Clob data when the Clob stream is being read StringBuffer suggestions = new StringBuffer(); Reader clobStream = null; try { Clob clob = rs.getClob(idx); if ( !rs.wasNull() ) { clobStream = clob.getCharacterStream(); int nchars = 0; char[] buffer = new char[4096];; while((nchars = clobStream.read(buffer)) != -1) { suggestions.append(buffer, 0, nchars); } } } catch (Exception e) { log.error("getDBByte exception " + e); log.error(Logging.stackTrace(e)); } finally { try { if (clobStream != null) { clobStream.close(); } } catch (Exception e) { } } return suggestions.toString(); } public MMObjectNode decodeDBnodeField(MMObjectNode node,String fieldname, ResultSet rs,int i,String prefix) { int type=node.getDBType(prefix+fieldname); try { switch (type) { case FieldDefs.TYPE_XML: case FieldDefs.TYPE_STRING: String tmp = null; FieldDefs field = node.getBuilder().getField(fieldname); if (field != null && isClob(field)) { tmp = readClobData(rs, i); } else { tmp = rs.getString(i); } if (tmp==null) { node.setValue(prefix+fieldname,""); } else { node.setValue(prefix+fieldname,tmp); } break; case FieldDefs.TYPE_NODE: case FieldDefs.TYPE_INTEGER: // node.setValue(prefix+fieldname,(Integer)rs.getObject(i)); node.setValue(prefix+fieldname, rs.getInt(i)); break; case FieldDefs.TYPE_LONG: // node.setValue(prefix+fieldname,(Long)rs.getObject(i)); node.setValue(prefix+fieldname,rs.getLong(i)); break; case FieldDefs.TYPE_FLOAT: // who does this now work ???? //node.setValue(prefix+fieldname,((Float)rs.getObject(i))); node.setValue(prefix+fieldname, rs.getFloat(i)); break; case FieldDefs.TYPE_DOUBLE: // node.setValue(prefix+fieldname,(Double)rs.getObject(i)); node.setValue(prefix+fieldname, rs.getDouble(i)); break; case FieldDefs.TYPE_BYTE: node.setValue(prefix+fieldname,"$SHORTED"); break; } } catch(SQLException sqle) { log.error("could not retieve the field("+fieldname+") value of the node from the database("+node+")"); log.error(Logging.stackTrace(sqle)); for(SQLException se = sqle;se != null; se = se.getNextException()) { log.error("\tSQLState : " + se.getSQLState()); log.error("\tErrorCode : " + se.getErrorCode()); log.error("\tMessage : " + se.getMessage()); } } return node; } }