bpel-runtime/src/main/java/org/apache/ode/bpel/extvar/jdbc/JdbcExternalVariableModule.java - ode - Git at Google

 /*
  * Licensed to the Apache Software Foundation (ASF) under one
  * or more contributor license agreements.  See the NOTICE file
  * distributed with this work for additional information
  * regarding copyright ownership.  The ASF licenses this file
  * to you under the Apache License, Version 2.0 (the
  * "License"); you may not use this file except in compliance
  * with the License.  You may obtain a copy of the License at
  *
  *    http://www.apache.org/licenses/LICENSE-2.0
  *
  * Unless required by applicable law or agreed to in writing,
  * software distributed under the License is distributed on an
  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
  * KIND, either express or implied.  See the License for the
  * specific language governing permissions and limitations
  * under the License.
  */
 package org.apache.ode.bpel.extvar.jdbc;

 import java.math.BigDecimal;
 import java.math.BigInteger;
 import java.sql.Connection;
 import java.sql.DatabaseMetaData;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Types;
 import java.util.HashMap;
 import java.util.List;

 import javax.naming.InitialContext;
 import javax.naming.NamingException;
 import javax.sql.DataSource;
 import javax.swing.text.StyleContext.SmallAttributeSet;
 import javax.xml.namespace.QName;

 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 import org.apache.ode.bpel.extvar.jdbc.DbExternalVariable.Column;
 import org.apache.ode.bpel.extvar.jdbc.DbExternalVariable.RowKey;
 import org.apache.ode.bpel.extvar.jdbc.DbExternalVariable.RowVal;
 import org.apache.ode.utils.DOMUtils;
 import org.apache.ode.utils.ObjectPrinter;
 import org.apache.ode.bpel.evar.ExternalVariableModule;
 import org.apache.ode.bpel.evar.ExternalVariableModuleException;
 import org.apache.ode.bpel.evar.IncompleteKeyException;
 import org.w3c.dom.Element;

 public class JdbcExternalVariableModule implements ExternalVariableModule {

     private static final Logger __log = LoggerFactory.getLogger(JdbcExternalVariableModule.class);

     public static final String JDBC_NS = "http://ode.apache.org/externalVariables/jdbc";

     /** Unique QName for the engine, this should be the element used for the external-variable configuration. */
     public static final QName NAME = new QName(JDBC_NS, "jdbc");

     /** Manually configured data sources. */
     private final HashMap<String, DataSource> _dataSources = new HashMap<String, DataSource>();

     /** Variables we know about via configure() method calls. */
     private final HashMap<EVarId, DbExternalVariable> _vars = new HashMap<EVarId, DbExternalVariable>();

     public void configure(QName pid, String extVarId, Element config) throws ExternalVariableModuleException {
         EVarId evarId = new EVarId(pid, extVarId);
         DataSource ds = null;

         Element jndiDs = DOMUtils.findChildByName(config, new QName(JDBC_NS, "datasource-jndi"));
         Element jndiRef = DOMUtils.findChildByName(config, new QName(JDBC_NS, "datasource-ref"));
         Element initMode = DOMUtils.findChildByName(config, new QName(JDBC_NS, "init-mode"));
         if (jndiRef != null) {
             String refname = jndiRef.getTextContent().trim();
             ds = _dataSources.get(refname);
             if (ds == null)
                 throw new ExternalVariableModuleException("Data source reference \"" + refname
                         + "\" not found for external variable " + evarId
                         + "; make sure to register the data source with the engine!");
         } else if (jndiDs != null) {
             String name = jndiDs.getTextContent().trim();
             Object dsCandidate;
             InitialContext ctx;
             try {
                 ctx = new InitialContext();
             } catch (Exception ex) {
                 throw new ExternalVariableModuleException("Unable to access JNDI context for external variable " + evarId, ex);
             }

             try {
                 dsCandidate = ctx.lookup(name);
             } catch (Exception ex) {
                 throw new ExternalVariableModuleException("Lookup of data source for " + evarId + "  failed.", ex);
             } finally {
                 try {
                     ctx.close();
                 } catch (NamingException e) { /* ignore */ }
             }

             if (dsCandidate == null)
                 throw new ExternalVariableModuleException("Data source \"" + name + "\" not found in JNDI!");

             if (!(dsCandidate instanceof DataSource))
                 throw new ExternalVariableModuleException("JNDI object \"" + name + "\" does not implement javax.sql.DataSource");

             ds = (DataSource) dsCandidate;
         }

         if (ds == null) {
             throw new ExternalVariableModuleException("No valid data source configuration for JDBC external varible " + evarId);
         }

         Connection conn = null;
         DatabaseMetaData metaData;
         try {
             conn = ds.getConnection();
             metaData = conn.getMetaData();
         } catch (Exception ex) {
             try {
                 if (conn != null) conn.close();
             } catch (SQLException e) {
                 // ignore
             }
             throw new ExternalVariableModuleException("Unable to open database connection for external variable " + evarId, ex);
         }


         try {
             DbExternalVariable dbev = new DbExternalVariable(evarId, ds);
             if (initMode != null)
                 try {
                     dbev._initType = InitType.valueOf(initMode.getTextContent().trim());
                 } catch (Exception ex) {
                     throw new ExternalVariableModuleException("Invalid <init-mode> value: " + initMode.getTextContent().trim());
                 }

             Element tableName = DOMUtils.findChildByName(config, new QName(JDBC_NS, "table"));
             if (tableName == null || tableName.getTextContent().trim().equals(""))
                 throw new ExternalVariableModuleException("Must specify <table> for external variable " + evarId);
             String table = tableName.getTextContent().trim();
             String schema = null;
             if (table.indexOf('.') != -1) {
                 schema = table.substring(0, table.indexOf('.'));
                 table = table.substring(table.indexOf('.') + 1);
             }

             if (metaData.storesLowerCaseIdentifiers()) {
                 table = table.toLowerCase();
                 if (schema != null)
                     schema = table.toLowerCase();
             } else if (metaData.storesUpperCaseIdentifiers()) {
                 table = table.toUpperCase();
                 if (schema != null)
                     schema = schema.toUpperCase();
             }

             dbev.generatedKeys = metaData.supportsGetGeneratedKeys();
             ResultSet tables = metaData.getTables(null, schema, table, null);
             if (tables.next()) {
                 dbev.table = tables.getString("TABLE_NAME");
                 dbev.schema = tables.getString("TABLE_SCHEM");
             } else
                 throw new ExternalVariableModuleException("Table \"" + table + "\" not found in database.");

             tables.close();

             List<Element> columns = DOMUtils.findChildrenByName(config, new QName(JDBC_NS, "column"));

             for (Element col : columns) {
                 String name = col.getAttribute("name");
                 String colname = col.getAttribute("column-name");
                 String key = col.getAttribute("key");
                 String gentype = col.getAttribute("generator");
                 String expression = col.getAttribute("expression");

                 if (key == null || "".equals(key))
                     key = "no";
                 if (gentype == null || "".equals(gentype))
                     gentype = GenType.none.toString();
                 if (colname == null || "".equals(colname))
                     colname = name;

                 if (name == null || "".equals(name))
                     throw new ExternalVariableModuleException("External variable " + evarId
                             + " <column> element must have \"name\" attribute. ");

                 if (metaData.storesLowerCaseIdentifiers())
                     colname = colname.toLowerCase();
                 else if (metaData.storesUpperCaseIdentifiers())
                     colname = colname.toUpperCase();

                 GenType gtype;
                 try {
                     gtype = GenType.valueOf(gentype);
                 } catch (Exception ex) {
                     throw new ExternalVariableModuleException("External variable " + evarId + " column \"" + name
                             + "\" generator type \"" + gentype + "\" is unknown.");

                 }

                 if (gtype == GenType.expression && (expression == null || "".equals(expression)))
                     throw new ExternalVariableModuleException("External variable " + evarId + " column \"" + name
                             + "\" used \"expression\" generator, but did not specify an expression");

                 Column c = dbev.new Column(name, colname, key.equalsIgnoreCase("yes"), gtype, expression);
                 ResultSet cmd = metaData.getColumns(null, dbev.schema, dbev.table, colname);
                 try {
                     if (cmd.next()) {
                         c.dataType = cmd.getInt("DATA_TYPE");
                         c.nullok = cmd.getInt("NULLABLE") != 0;
                     } else
                         throw new ExternalVariableModuleException("External variable " + evarId + " referenced "
                             + "non-existant column \"" + colname + "\"!");
                 } finally {
                     cmd.close();
                 }

                 dbev.addColumn(c);
             }

             if (dbev.numColumns() == 0)
                 throw new ExternalVariableModuleException("External variable " + evarId + " did not have any <column> elements!");

             _vars.put(evarId, dbev);
         } catch (SQLException se) {
             throw new ExternalVariableModuleException("SQL Error", se);
         } finally {
             try {
                 conn.close();
             } catch (SQLException e) {
             }
         }
     }

     public QName getName() {
         return NAME;
     }

     public boolean isTransactional() {
         return true;
     }

     public void shutdown() {
     }

     public void start() {
     }

     public void stop() {
     }

     public Value writeValue(QName varType, Value newval) throws ExternalVariableModuleException {
         EVarId evarId = new EVarId(newval.locator.pid, newval.locator.varId);
         DbExternalVariable evar = _vars.get(evarId);
         if (evar == null)
             throw new ExternalVariableModuleException("No such variable. "); // todo

         RowKey key = evar.keyFromLocator(newval.locator);
         RowVal val = evar.parseXmlRow(evar.new RowVal(), (Element) newval.value);
         if (__log.isDebugEnabled())
             __log.debug("JdbcExternalVariable.writeValue() RowKey: " + key + " RowVal: " + val);

         if (!key.missingValues() && evar._initType == InitType.delete_insert) {
             // do delete...
             throw new ExternalVariableModuleException("Delete not implemented. "); // todo
         }

         // should we try an update first? to do this we need to have all the required keys
         // and there should be some keys
         boolean tryupdatefirst = (evar._initType == InitType.update || evar._initType == InitType.update_insert)
                 && !evar._keycolumns.isEmpty() && !key.missingDatabaseGeneratedValues();

         boolean insert = evar._initType != InitType.update;

         if (__log.isDebugEnabled())
             __log.debug("tryUpdateFirst: " + tryupdatefirst
                         + " insert: " + insert
                         + " initType: " + evar._initType
                         + " key.isEmpty: " + evar._keycolumns.isEmpty()
                         + " key.missingValues: " + key.missingValues()
                         + " key.missingDBValues: " + key.missingDatabaseGeneratedValues());

         try {
             if (tryupdatefirst)
                 insert = execUpdate(evar, key, val) == 0;
             if (insert) {
                 key = execInsert(evar, newval.locator, key, val);
                 // Transfer the keys obtained from the db.
                 key.write(varType, newval.locator);
             }
         } catch (SQLException se) {
             throw new ExternalVariableModuleException("Error updating row.", se);
         }

         return newval;
     }

     public Value readValue(QName varType, Locator locator) throws ExternalVariableModuleException {
         EVarId evarId = new EVarId(locator.pid, locator.varId);
         DbExternalVariable evar = _vars.get(evarId);
         if (evar == null)
             throw new ExternalVariableModuleException("No such variable: "+evarId);

         Element val;
         try {
             RowVal rowval = execSelect(evar, locator);
             val = evar.renderXmlRow(locator, varType, rowval);
         } catch (SQLException se) {
             throw new ExternalVariableModuleException("SQL Error.", se);
         }

         return new Value(locator, val, null);
     }

     /**
      * Manually register a data source. Handy if you don't want to use JNDI to look these up.
      *
      * @param dsName
      * @param ds
      */
     public void registerDataSource(String dsName, DataSource ds) {
         _dataSources.put(dsName, ds);
     }

     int execUpdate(DbExternalVariable dbev, RowKey key, RowVal values) throws SQLException {
         Connection conn = dbev.dataSource.getConnection();
         PreparedStatement stmt = null;
         try {
             if (__log.isDebugEnabled()) {
                 __log.debug("execUpdate: key=" + key + " values=" + values);
                 __log.debug("Prepare statement: " + dbev.update);
             }
             stmt = conn.prepareStatement(dbev.update);
             int idx = 1;
             for (Column c : dbev._updcolumns) {
                 Object val = values.get(c.name);
                 if (__log.isDebugEnabled()) __log.debug("Set value parameter "+idx+": "+val);
                 if (val == null)
                     stmt.setNull(idx, c.dataType);
                 else
                     stmt.setObject(idx, downcastValue(val, c.dataType));
                 idx++;
             }

             for (Column ck : dbev._keycolumns) {
                 Object val = key.get(ck.name);
                 if (__log.isDebugEnabled()) __log.debug("Set key parameter "+idx+": "+val);
                 if (val == null)
                     stmt.setNull(idx, ck.dataType);
                 else
                     stmt.setObject(idx, downcastValue(val, ck.dataType));
                 idx++;
             }
             return stmt.executeUpdate();
         } finally {
             if (stmt != null) stmt.close();
             try {
                 conn.close();
             } catch (SQLException e) {
                 // ignore
             }
         }
     }

     private Object downcastValue(Object value, int dataType) {
         if (value == null) {
             return null;
         }
         // Try down casting the value as per its column type.
         try {
             // Some JDBC 4.0 types have been ignored to avoid compilation errors
             switch (dataType) {
             case Types.ARRAY:
                 break;
             case Types.BIGINT:
                 if (!(value instanceof BigInteger)) {
                     value = new BigDecimal(value.toString()).longValue();
                 }
                 break;
             case Types.BINARY:
                 break;
             case Types.BIT:
                 if (!(value instanceof Boolean)) {
                     value = new Boolean(value.toString());
                 }
                 break;
             case Types.BLOB:
                 break;
             case Types.BOOLEAN:
                 if (!(value instanceof Boolean)) {
                     value = new Boolean(value.toString());
                 }
                 break;
             case Types.CHAR:
                 break;
             case Types.CLOB:
                 break;
             case Types.DATALINK:
                 break;
             case Types.DATE:
                 break;
             case Types.DECIMAL:
                 //ODE-872: Oracle 9g and 10g has problems with BigDecimal on Java1.5
                 value = new BigDecimal(new BigDecimal(value.toString()).toPlainString());
                 break;
             case Types.DISTINCT:
                 break;
             case Types.DOUBLE:
                 if (!(value instanceof Double)) {
                     value = Double.valueOf(value.toString()).doubleValue();
                 }
                 break;
             case Types.FLOAT:
                 if (!(value instanceof Float)) {
                     value = Float.valueOf(value.toString()).floatValue();
                 }
                 break;
             case Types.INTEGER:
                 if (!(value instanceof Integer)) {
                     value = Double.valueOf(value.toString()).intValue();
                 }
                 break;
             case Types.JAVA_OBJECT:
                 break;
 //	    	case Types.LONGNVARCHAR:
 //	    		break;
             case Types.LONGVARBINARY:
                 break;
             case Types.LONGVARCHAR:
                 break;
 //	    	case Types.NCHAR:
 //	    		break;
 //	    	case Types.NCLOB:
 //	    		break;
             case Types.NUMERIC:
                 //ODE-872: Oracle 9g and 10g has problems with BigDecimal on Java1.5
                 value = new BigDecimal(new BigDecimal(value.toString()).toPlainString());
                 break;
 //	    	case Types.NVARCHAR:
 //	    		break;
             case Types.OTHER:
                 break;
             case Types.REAL:
                 if (!(value instanceof Double)) {
                     value = Float.valueOf(value.toString()).floatValue();
                 }
                 break;
             case Types.REF:
                 break;
 //	    	case Types.ROWID:
 //	    		break;
             case Types.SMALLINT:
                 if (!(value instanceof Short)) {
                     value = new Short(value.toString()).shortValue();
                 }
                 break;
 //	    	case Types.SQLXML:
 //	    		break;
             case Types.STRUCT:
                 break;
             case Types.TIME:
                 break;
             case Types.TIMESTAMP:
                 break;
             case Types.TINYINT:
                 if (!(value instanceof Short)) {
                     value = new Short(value.toString()).shortValue();
                 }
                 break;
             case Types.VARBINARY:
                 break;
             case Types.VARCHAR:
                 break;
             default:
                 break;
             }
         } catch (Exception e) {
             // couldn't cast... let's just use original value object
         }
         return value;
     }

     RowVal execSelect(DbExternalVariable dbev, Locator locator) throws SQLException, ExternalVariableModuleException {
         RowKey rowkey = dbev.keyFromLocator(locator);
         if (__log.isDebugEnabled()) __log.debug("execSelect: " + rowkey);

         if (rowkey.missingDatabaseGeneratedValues()) {
             return null;
         }

         if (rowkey.missingValues()) {
             throw new IncompleteKeyException(rowkey.getMissing());
         }

         RowVal ret = dbev.new RowVal();
         Connection conn = dbev.dataSource.getConnection();
         PreparedStatement stmt = null;
         try {
             if (__log.isDebugEnabled()) __log.debug("Prepare statement: " + dbev.select);
             stmt = conn.prepareStatement(dbev.select);
             int idx = 1;
             for (Object k : rowkey) {
                 if (__log.isDebugEnabled()) __log.debug("Set key parameter "+idx+": "+k);
                 stmt.setObject(idx++, k);
             }

             ResultSet rs = stmt.executeQuery();
             try {
                 if (rs.next()) {
                     for (Column c : dbev._columns)  {
                         Object val;
                         int i = c.idx+1;
                         if (c.isDate()) val = rs.getDate(i);
                         else if (c.isTimeStamp()) val = rs.getTimestamp(i);
                         else if (c.isTime()) val = rs.getTime(i);
                         else if (c.isInteger()) val = new Long(rs.getLong(i));
                         else if (c.isReal()) val = new Double(rs.getDouble(i));
                         else if (c.isBoolean()) val = new Boolean(rs.getBoolean(i));
                         else val = rs.getObject(i);
                         if (__log.isDebugEnabled()) __log.debug("Result column index "+c.idx+": "+val);
                         ret.set(c.idx,val);
                     }
                 } else
                     return null;
             } finally {
                 rs.close();
             }
         } finally {
             if (stmt != null) stmt.close();
             try {
                 conn.close();
             } catch (SQLException e) {
                 // ignore
             }
         }

         return ret;
     }

     RowKey execInsert(DbExternalVariable dbev, Locator locator, RowKey keys, RowVal values) throws SQLException {
         Connection conn = dbev.dataSource.getConnection();
         PreparedStatement stmt = null;
         try {
             if (__log.isDebugEnabled()) {
                 __log.debug("execInsert: keys=" + keys + " values=" + values);
                 __log.debug("Prepare statement: " + dbev.insert);
                 __log.debug("missingDatabaseGeneratedValues: " + keys.missingDatabaseGeneratedValues());
                 __log.debug("_autoColNames: " + ObjectPrinter.stringifyNvList(dbev._autoColNames));
             }

             stmt = keys.missingDatabaseGeneratedValues()
                 ? conn.prepareStatement(dbev.insert, dbev._autoColNames)
                 : conn.prepareStatement(dbev.insert);

             int idx = 1;
             for (Column c : dbev._inscolumns) {
                 Object val = c.getValue(c.name, keys, values, locator.iid);
                 values.put(c.name, val);
                 if (__log.isDebugEnabled()) __log.debug("Set parameter "+idx+": "+val);
                 if (val == null)
                     stmt.setNull(idx, c.dataType);
                 else
                     stmt.setObject(idx, val);
                 idx++;
             }

             stmt.execute();

             for (Column ck : keys._columns) {
                 Object val = values.get(ck.name);
                 if (__log.isDebugEnabled()) __log.debug("Key "+ck.name+": "+val);
                 keys.put(ck.name,val);
             }

             if (keys.missingDatabaseGeneratedValues() ) {
                 // With JDBC 3, we can get the values of the key columns (if the db supports it)
                 ResultSet keyRS = stmt.getGeneratedKeys();
                 try {
                     if (keyRS == null)
                         throw new SQLException("Database did not return generated keys");
                     keyRS.next();
                     for (Column ck : keys._columns) {
                         Object value = keyRS.getObject(ck.idx+1);
                         if (__log.isDebugEnabled()) __log.debug("Generated key "+ck.name+": "+value);
                         keys.put(ck.name, value);
                     }
                 } finally {
                     keyRS.close();
                 }
             }
             return keys;
         } finally {
             if (stmt != null) stmt.close();
             try {
                 conn.close();
             } catch (SQLException e) {
                 // ignore
             }
         }
     }

 }
	/*
	* Licensed to the Apache Software Foundation (ASF) under one
	* or more contributor license agreements. See the NOTICE file
	* distributed with this work for additional information
	* regarding copyright ownership. The ASF licenses this file
	* to you under the Apache License, Version 2.0 (the
	* "License"); you may not use this file except in compliance
	* with the License. You may obtain a copy of the License at
	*
	* http://www.apache.org/licenses/LICENSE-2.0
	*
	* Unless required by applicable law or agreed to in writing,
	* software distributed under the License is distributed on an
	* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
	* KIND, either express or implied. See the License for the
	* specific language governing permissions and limitations
	* under the License.
	*/
	package org.apache.ode.bpel.extvar.jdbc;

	import java.math.BigDecimal;
	import java.math.BigInteger;
	import java.sql.Connection;
	import java.sql.DatabaseMetaData;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Types;
	import java.util.HashMap;
	import java.util.List;

	import javax.naming.InitialContext;
	import javax.naming.NamingException;
	import javax.sql.DataSource;
	import javax.swing.text.StyleContext.SmallAttributeSet;
	import javax.xml.namespace.QName;

	import org.slf4j.Logger;
	import org.slf4j.LoggerFactory;
	import org.apache.ode.bpel.extvar.jdbc.DbExternalVariable.Column;
	import org.apache.ode.bpel.extvar.jdbc.DbExternalVariable.RowKey;
	import org.apache.ode.bpel.extvar.jdbc.DbExternalVariable.RowVal;
	import org.apache.ode.utils.DOMUtils;
	import org.apache.ode.utils.ObjectPrinter;
	import org.apache.ode.bpel.evar.ExternalVariableModule;
	import org.apache.ode.bpel.evar.ExternalVariableModuleException;
	import org.apache.ode.bpel.evar.IncompleteKeyException;
	import org.w3c.dom.Element;

	public class JdbcExternalVariableModule implements ExternalVariableModule {

	private static final Logger __log = LoggerFactory.getLogger(JdbcExternalVariableModule.class);

	public static final String JDBC_NS = "http://ode.apache.org/externalVariables/jdbc";

	/** Unique QName for the engine, this should be the element used for the external-variable configuration. */
	public static final QName NAME = new QName(JDBC_NS, "jdbc");

	/** Manually configured data sources. */
	private final HashMap<String, DataSource> _dataSources = new HashMap<String, DataSource>();

	/** Variables we know about via configure() method calls. */
	private final HashMap<EVarId, DbExternalVariable> _vars = new HashMap<EVarId, DbExternalVariable>();

	public void configure(QName pid, String extVarId, Element config) throws ExternalVariableModuleException {
	EVarId evarId = new EVarId(pid, extVarId);
	DataSource ds = null;

	Element jndiDs = DOMUtils.findChildByName(config, new QName(JDBC_NS, "datasource-jndi"));
	Element jndiRef = DOMUtils.findChildByName(config, new QName(JDBC_NS, "datasource-ref"));
	Element initMode = DOMUtils.findChildByName(config, new QName(JDBC_NS, "init-mode"));
	if (jndiRef != null) {
	String refname = jndiRef.getTextContent().trim();
	ds = _dataSources.get(refname);
	if (ds == null)
	throw new ExternalVariableModuleException("Data source reference \"" + refname
	+ "\" not found for external variable " + evarId
	+ "; make sure to register the data source with the engine!");
	} else if (jndiDs != null) {
	String name = jndiDs.getTextContent().trim();
	Object dsCandidate;
	InitialContext ctx;
	try {
	ctx = new InitialContext();
	} catch (Exception ex) {
	throw new ExternalVariableModuleException("Unable to access JNDI context for external variable " + evarId, ex);
	}

	try {
	dsCandidate = ctx.lookup(name);
	} catch (Exception ex) {
	throw new ExternalVariableModuleException("Lookup of data source for " + evarId + " failed.", ex);
	} finally {
	try {
	ctx.close();
	} catch (NamingException e) { /* ignore */ }
	}

	if (dsCandidate == null)
	throw new ExternalVariableModuleException("Data source \"" + name + "\" not found in JNDI!");

	if (!(dsCandidate instanceof DataSource))
	throw new ExternalVariableModuleException("JNDI object \"" + name + "\" does not implement javax.sql.DataSource");

	ds = (DataSource) dsCandidate;
	}

	if (ds == null) {
	throw new ExternalVariableModuleException("No valid data source configuration for JDBC external varible " + evarId);
	}

	Connection conn = null;
	DatabaseMetaData metaData;
	try {
	conn = ds.getConnection();
	metaData = conn.getMetaData();
	} catch (Exception ex) {
	try {
	if (conn != null) conn.close();
	} catch (SQLException e) {
	// ignore
	}
	throw new ExternalVariableModuleException("Unable to open database connection for external variable " + evarId, ex);
	}


	try {
	DbExternalVariable dbev = new DbExternalVariable(evarId, ds);
	if (initMode != null)
	try {
	dbev._initType = InitType.valueOf(initMode.getTextContent().trim());
	} catch (Exception ex) {
	throw new ExternalVariableModuleException("Invalid <init-mode> value: " + initMode.getTextContent().trim());
	}

	Element tableName = DOMUtils.findChildByName(config, new QName(JDBC_NS, "table"));
	if (tableName == null \|\| tableName.getTextContent().trim().equals(""))
	throw new ExternalVariableModuleException("Must specify <table> for external variable " + evarId);
	String table = tableName.getTextContent().trim();
	String schema = null;
	if (table.indexOf('.') != -1) {
	schema = table.substring(0, table.indexOf('.'));
	table = table.substring(table.indexOf('.') + 1);
	}

	if (metaData.storesLowerCaseIdentifiers()) {
	table = table.toLowerCase();
	if (schema != null)
	schema = table.toLowerCase();
	} else if (metaData.storesUpperCaseIdentifiers()) {
	table = table.toUpperCase();
	if (schema != null)
	schema = schema.toUpperCase();
	}

	dbev.generatedKeys = metaData.supportsGetGeneratedKeys();
	ResultSet tables = metaData.getTables(null, schema, table, null);
	if (tables.next()) {
	dbev.table = tables.getString("TABLE_NAME");
	dbev.schema = tables.getString("TABLE_SCHEM");
	} else
	throw new ExternalVariableModuleException("Table \"" + table + "\" not found in database.");

	tables.close();

	List<Element> columns = DOMUtils.findChildrenByName(config, new QName(JDBC_NS, "column"));

	for (Element col : columns) {
	String name = col.getAttribute("name");
	String colname = col.getAttribute("column-name");
	String key = col.getAttribute("key");
	String gentype = col.getAttribute("generator");
	String expression = col.getAttribute("expression");

	if (key == null \|\| "".equals(key))
	key = "no";
	if (gentype == null \|\| "".equals(gentype))
	gentype = GenType.none.toString();
	if (colname == null \|\| "".equals(colname))
	colname = name;

	if (name == null \|\| "".equals(name))
	throw new ExternalVariableModuleException("External variable " + evarId
	+ " <column> element must have \"name\" attribute. ");

	if (metaData.storesLowerCaseIdentifiers())
	colname = colname.toLowerCase();
	else if (metaData.storesUpperCaseIdentifiers())
	colname = colname.toUpperCase();

	GenType gtype;
	try {
	gtype = GenType.valueOf(gentype);
	} catch (Exception ex) {
	throw new ExternalVariableModuleException("External variable " + evarId + " column \"" + name
	+ "\" generator type \"" + gentype + "\" is unknown.");

	}

	if (gtype == GenType.expression && (expression == null \|\| "".equals(expression)))
	throw new ExternalVariableModuleException("External variable " + evarId + " column \"" + name
	+ "\" used \"expression\" generator, but did not specify an expression");

	Column c = dbev.new Column(name, colname, key.equalsIgnoreCase("yes"), gtype, expression);
	ResultSet cmd = metaData.getColumns(null, dbev.schema, dbev.table, colname);
	try {
	if (cmd.next()) {
	c.dataType = cmd.getInt("DATA_TYPE");
	c.nullok = cmd.getInt("NULLABLE") != 0;
	} else
	throw new ExternalVariableModuleException("External variable " + evarId + " referenced "
	+ "non-existant column \"" + colname + "\"!");
	} finally {
	cmd.close();
	}

	dbev.addColumn(c);
	}

	if (dbev.numColumns() == 0)
	throw new ExternalVariableModuleException("External variable " + evarId + " did not have any <column> elements!");

	_vars.put(evarId, dbev);
	} catch (SQLException se) {
	throw new ExternalVariableModuleException("SQL Error", se);
	} finally {
	try {
	conn.close();
	} catch (SQLException e) {
	}
	}
	}

	public QName getName() {
	return NAME;
	}

	public boolean isTransactional() {
	return true;
	}

	public void shutdown() {
	}

	public void start() {
	}

	public void stop() {
	}

	public Value writeValue(QName varType, Value newval) throws ExternalVariableModuleException {
	EVarId evarId = new EVarId(newval.locator.pid, newval.locator.varId);
	DbExternalVariable evar = _vars.get(evarId);
	if (evar == null)
	throw new ExternalVariableModuleException("No such variable. "); // todo

	RowKey key = evar.keyFromLocator(newval.locator);
	RowVal val = evar.parseXmlRow(evar.new RowVal(), (Element) newval.value);
	if (__log.isDebugEnabled())
	__log.debug("JdbcExternalVariable.writeValue() RowKey: " + key + " RowVal: " + val);

	if (!key.missingValues() && evar._initType == InitType.delete_insert) {
	// do delete...
	throw new ExternalVariableModuleException("Delete not implemented. "); // todo
	}

	// should we try an update first? to do this we need to have all the required keys
	// and there should be some keys
	boolean tryupdatefirst = (evar._initType == InitType.update \|\| evar._initType == InitType.update_insert)
	&& !evar._keycolumns.isEmpty() && !key.missingDatabaseGeneratedValues();

	boolean insert = evar._initType != InitType.update;

	if (__log.isDebugEnabled())
	__log.debug("tryUpdateFirst: " + tryupdatefirst
	+ " insert: " + insert
	+ " initType: " + evar._initType
	+ " key.isEmpty: " + evar._keycolumns.isEmpty()
	+ " key.missingValues: " + key.missingValues()
	+ " key.missingDBValues: " + key.missingDatabaseGeneratedValues());

	try {
	if (tryupdatefirst)
	insert = execUpdate(evar, key, val) == 0;
	if (insert) {
	key = execInsert(evar, newval.locator, key, val);
	// Transfer the keys obtained from the db.
	key.write(varType, newval.locator);
	}
	} catch (SQLException se) {
	throw new ExternalVariableModuleException("Error updating row.", se);
	}

	return newval;
	}

	public Value readValue(QName varType, Locator locator) throws ExternalVariableModuleException {
	EVarId evarId = new EVarId(locator.pid, locator.varId);
	DbExternalVariable evar = _vars.get(evarId);
	if (evar == null)
	throw new ExternalVariableModuleException("No such variable: "+evarId);

	Element val;
	try {
	RowVal rowval = execSelect(evar, locator);
	val = evar.renderXmlRow(locator, varType, rowval);
	} catch (SQLException se) {
	throw new ExternalVariableModuleException("SQL Error.", se);
	}

	return new Value(locator, val, null);
	}

	/**
	* Manually register a data source. Handy if you don't want to use JNDI to look these up.
	*
	* @param dsName
	* @param ds
	*/
	public void registerDataSource(String dsName, DataSource ds) {
	_dataSources.put(dsName, ds);
	}

	int execUpdate(DbExternalVariable dbev, RowKey key, RowVal values) throws SQLException {
	Connection conn = dbev.dataSource.getConnection();
	PreparedStatement stmt = null;
	try {
	if (__log.isDebugEnabled()) {
	__log.debug("execUpdate: key=" + key + " values=" + values);
	__log.debug("Prepare statement: " + dbev.update);
	}
	stmt = conn.prepareStatement(dbev.update);
	int idx = 1;
	for (Column c : dbev._updcolumns) {
	Object val = values.get(c.name);
	if (__log.isDebugEnabled()) __log.debug("Set value parameter "+idx+": "+val);
	if (val == null)
	stmt.setNull(idx, c.dataType);
	else
	stmt.setObject(idx, downcastValue(val, c.dataType));
	idx++;
	}

	for (Column ck : dbev._keycolumns) {
	Object val = key.get(ck.name);
	if (__log.isDebugEnabled()) __log.debug("Set key parameter "+idx+": "+val);
	if (val == null)
	stmt.setNull(idx, ck.dataType);
	else
	stmt.setObject(idx, downcastValue(val, ck.dataType));
	idx++;
	}
	return stmt.executeUpdate();
	} finally {
	if (stmt != null) stmt.close();
	try {
	conn.close();
	} catch (SQLException e) {
	// ignore
	}
	}
	}

	private Object downcastValue(Object value, int dataType) {
	if (value == null) {
	return null;
	}
	// Try down casting the value as per its column type.
	try {
	// Some JDBC 4.0 types have been ignored to avoid compilation errors
	switch (dataType) {
	case Types.ARRAY:
	break;
	case Types.BIGINT:
	if (!(value instanceof BigInteger)) {
	value = new BigDecimal(value.toString()).longValue();
	}
	break;
	case Types.BINARY:
	break;
	case Types.BIT:
	if (!(value instanceof Boolean)) {
	value = new Boolean(value.toString());
	}
	break;
	case Types.BLOB:
	break;
	case Types.BOOLEAN:
	if (!(value instanceof Boolean)) {
	value = new Boolean(value.toString());
	}
	break;
	case Types.CHAR:
	break;
	case Types.CLOB:
	break;
	case Types.DATALINK:
	break;
	case Types.DATE:
	break;
	case Types.DECIMAL:
	//ODE-872: Oracle 9g and 10g has problems with BigDecimal on Java1.5
	value = new BigDecimal(new BigDecimal(value.toString()).toPlainString());
	break;
	case Types.DISTINCT:
	break;
	case Types.DOUBLE:
	if (!(value instanceof Double)) {
	value = Double.valueOf(value.toString()).doubleValue();
	}
	break;
	case Types.FLOAT:
	if (!(value instanceof Float)) {
	value = Float.valueOf(value.toString()).floatValue();
	}
	break;
	case Types.INTEGER:
	if (!(value instanceof Integer)) {
	value = Double.valueOf(value.toString()).intValue();
	}
	break;
	case Types.JAVA_OBJECT:
	break;
	// case Types.LONGNVARCHAR:
	// break;
	case Types.LONGVARBINARY:
	break;
	case Types.LONGVARCHAR:
	break;
	// case Types.NCHAR:
	// break;
	// case Types.NCLOB:
	// break;
	case Types.NUMERIC:
	//ODE-872: Oracle 9g and 10g has problems with BigDecimal on Java1.5
	value = new BigDecimal(new BigDecimal(value.toString()).toPlainString());
	break;
	// case Types.NVARCHAR:
	// break;
	case Types.OTHER:
	break;
	case Types.REAL:
	if (!(value instanceof Double)) {
	value = Float.valueOf(value.toString()).floatValue();
	}
	break;
	case Types.REF:
	break;
	// case Types.ROWID:
	// break;
	case Types.SMALLINT:
	if (!(value instanceof Short)) {
	value = new Short(value.toString()).shortValue();
	}
	break;
	// case Types.SQLXML:
	// break;
	case Types.STRUCT:
	break;
	case Types.TIME:
	break;
	case Types.TIMESTAMP:
	break;
	case Types.TINYINT:
	if (!(value instanceof Short)) {
	value = new Short(value.toString()).shortValue();
	}
	break;
	case Types.VARBINARY:
	break;
	case Types.VARCHAR:
	break;
	default:
	break;
	}
	} catch (Exception e) {
	// couldn't cast... let's just use original value object
	}
	return value;
	}

	RowVal execSelect(DbExternalVariable dbev, Locator locator) throws SQLException, ExternalVariableModuleException {
	RowKey rowkey = dbev.keyFromLocator(locator);
	if (__log.isDebugEnabled()) __log.debug("execSelect: " + rowkey);

	if (rowkey.missingDatabaseGeneratedValues()) {
	return null;
	}

	if (rowkey.missingValues()) {
	throw new IncompleteKeyException(rowkey.getMissing());
	}

	RowVal ret = dbev.new RowVal();
	Connection conn = dbev.dataSource.getConnection();
	PreparedStatement stmt = null;
	try {
	if (__log.isDebugEnabled()) __log.debug("Prepare statement: " + dbev.select);
	stmt = conn.prepareStatement(dbev.select);
	int idx = 1;
	for (Object k : rowkey) {
	if (__log.isDebugEnabled()) __log.debug("Set key parameter "+idx+": "+k);
	stmt.setObject(idx++, k);
	}

	ResultSet rs = stmt.executeQuery();
	try {
	if (rs.next()) {
	for (Column c : dbev._columns) {
	Object val;
	int i = c.idx+1;
	if (c.isDate()) val = rs.getDate(i);
	else if (c.isTimeStamp()) val = rs.getTimestamp(i);
	else if (c.isTime()) val = rs.getTime(i);
	else if (c.isInteger()) val = new Long(rs.getLong(i));
	else if (c.isReal()) val = new Double(rs.getDouble(i));
	else if (c.isBoolean()) val = new Boolean(rs.getBoolean(i));
	else val = rs.getObject(i);
	if (__log.isDebugEnabled()) __log.debug("Result column index "+c.idx+": "+val);
	ret.set(c.idx,val);
	}
	} else
	return null;
	} finally {
	rs.close();
	}
	} finally {
	if (stmt != null) stmt.close();
	try {
	conn.close();
	} catch (SQLException e) {
	// ignore
	}
	}

	return ret;
	}

	RowKey execInsert(DbExternalVariable dbev, Locator locator, RowKey keys, RowVal values) throws SQLException {
	Connection conn = dbev.dataSource.getConnection();
	PreparedStatement stmt = null;
	try {
	if (__log.isDebugEnabled()) {
	__log.debug("execInsert: keys=" + keys + " values=" + values);
	__log.debug("Prepare statement: " + dbev.insert);
	__log.debug("missingDatabaseGeneratedValues: " + keys.missingDatabaseGeneratedValues());
	__log.debug("_autoColNames: " + ObjectPrinter.stringifyNvList(dbev._autoColNames));
	}

	stmt = keys.missingDatabaseGeneratedValues()
	? conn.prepareStatement(dbev.insert, dbev._autoColNames)
	: conn.prepareStatement(dbev.insert);

	int idx = 1;
	for (Column c : dbev._inscolumns) {
	Object val = c.getValue(c.name, keys, values, locator.iid);
	values.put(c.name, val);
	if (__log.isDebugEnabled()) __log.debug("Set parameter "+idx+": "+val);
	if (val == null)
	stmt.setNull(idx, c.dataType);
	else
	stmt.setObject(idx, val);
	idx++;
	}

	stmt.execute();

	for (Column ck : keys._columns) {
	Object val = values.get(ck.name);
	if (__log.isDebugEnabled()) __log.debug("Key "+ck.name+": "+val);
	keys.put(ck.name,val);
	}

	if (keys.missingDatabaseGeneratedValues() ) {
	// With JDBC 3, we can get the values of the key columns (if the db supports it)
	ResultSet keyRS = stmt.getGeneratedKeys();
	try {
	if (keyRS == null)
	throw new SQLException("Database did not return generated keys");
	keyRS.next();
	for (Column ck : keys._columns) {
	Object value = keyRS.getObject(ck.idx+1);
	if (__log.isDebugEnabled()) __log.debug("Generated key "+ck.name+": "+value);
	keys.put(ck.name, value);
	}
	} finally {
	keyRS.close();
	}
	}
	return keys;
	} finally {
	if (stmt != null) stmt.close();
	try {
	conn.close();
	} catch (SQLException e) {
	// ignore
	}
	}
	}

	}