Mailing-List: contact commits-help@ode.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@ode.apache.org
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r590545 - in
 /ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar:
 ./ jdbc/ jdbc/DbExternalVariable.java jdbc/EVarId.java jdbc/GenType.java
 jdbc/InitType.java jdbc/JdbcExternalVariableEngine.java
Date: Wed, 31 Oct 2007 02:43:48 -0000
To: commits@ode.apache.org
From: mszefler@apache.org
Message-Id: <20071031024349.346031A9832@eris.apache.org>

Author: mszefler
Date: Tue Oct 30 19:43:47 2007
New Revision: 590545

URL: http://svn.apache.org/viewvc?rev=590545&view=rev
Log:
experimental: external variables

Added:
    ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/
    ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/
    ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/DbExternalVariable.java   (with props)
    ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/EVarId.java   (with props)
    ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/GenType.java   (with props)
    ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/InitType.java   (with props)
    ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/JdbcExternalVariableEngine.java   (with props)

Added: ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/DbExternalVariable.java
URL: http://svn.apache.org/viewvc/ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/DbExternalVariable.java?rev=590545&view=auto
==============================================================================
--- ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/DbExternalVariable.java (added)
+++ ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/DbExternalVariable.java Tue Oct 30 19:43:47 2007
@@ -0,0 +1,487 @@
+package org.apache.ode.bpel.evar.jdbc;
+
+import java.sql.Timestamp;
+import java.sql.Types;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.HashMap;
+
+import javax.sql.DataSource;
+import javax.xml.namespace.QName;
+
+import org.apache.ode.utils.DOMUtils;
+import org.apache.ode.utils.GUID;
+import org.apache.ode.utils.ISO8601DateParser;
+import org.apche.ode.bpel.evar.ExternalVariableModuleException;
+import org.apche.ode.bpel.evar.ExternalVariableModule.Locator;
+import org.w3c.dom.Document;
+import org.w3c.dom.Element;
+import org.w3c.dom.Node;
+import org.w3c.dom.NodeList;
+
+/**
+ * Configuration for an external variable.
+ * 
+ * @author Maciej Szefler <mszefler at gmail dot com>
+ */
+class DbExternalVariable {
+
+    private static final String XSI_NS = "http://www.w3.org/2001/XMLSchema-instance";
+
+    EVarId evarId;
+
+    DataSource dataSource;
+
+    final ArrayList<Column> columns = new ArrayList<Column>();
+
+    private final HashMap<String, Column> colmap = new HashMap<String, Column>();
+
+    final ArrayList<Column> keycolumns = new ArrayList<Column>();
+
+    final ArrayList<Column> inscolumns = new ArrayList<Column>();
+
+    final ArrayList<Column> updcolumns = new ArrayList<Column>();
+
+    InitType initType = InitType.update_insert;
+
+    public String[] autoColNames;
+
+    String select;
+
+    String insert;
+
+    String update;
+
+    String table;
+
+    String schema;
+
+    /** Does the database support retrieval of generated keys? */
+    boolean generatedKeys;
+
+    QName rowname = new QName(null, "row");
+
+    DbExternalVariable(EVarId evar, DataSource ds) {
+        this.evarId = evar;
+        this.dataSource = ds;
+    }
+
+    Column getColumn(String key) {
+        return colmap.get(key);
+    }
+
+    void addColumn(Column c) {
+        c.idx = columns.size(); 
+        colmap.put(c.name, c);
+        columns.add(c);
+        if (c.key) {
+            keycolumns.add(c);
+            autoColNames = new String[keycolumns.size()];
+            for (int i = 0; i < autoColNames.length; ++i)
+                autoColNames[i] = keycolumns.get(i).colname;
+        }
+        createSelect();
+        createInsert();
+        createUpdate();
+    }
+
+    public int numColumns() {
+        return columns.size();
+    }
+
+    /**
+     * Create a key from a locator.
+     * 
+     * @param locator
+     * @return
+     */
+    RowKey generateKey(Locator locator) throws ExternalVariableModuleException {
+        RowKey rc = new RowKey();
+        for (Column kc : keycolumns) {
+            String s = locator.get(kc.name);
+            if (s == null) /* incomplete key */
+                return null;
+            rc.add(kc.fromText(s));
+
+        }
+
+        return rc;
+    }
+
+    private void createSelect() {
+        StringBuilder sb = new StringBuilder("select ");
+        boolean first = true;
+        for (Column c : columns) {
+            if (!first) {
+                sb.append(',');
+            }
+            first = false;
+
+            sb.append(c.colname);
+        }
+        sb.append(" from " + table);
+        if (keycolumns.size() > 0) {
+            sb.append(" where ");
+            first = true;
+
+            for (Column kc : keycolumns) {
+                if (!first) {
+                    sb.append(" and ");
+                }
+                first = false;
+
+                sb.append(kc.colname);
+                sb.append(" = ?");
+            }
+            select = sb.toString();
+
+        } else {
+            select = null;
+        }
+    }
+
+    private void createUpdate() {
+        updcolumns.clear();
+        StringBuilder sb = new StringBuilder("update ");
+        sb.append(table);
+        sb.append(" set ");
+        boolean first = true;
+        for (Column c : columns) {
+            // Don't ever update keys or sequences or create time stamps
+            if (c.genType == GenType.sequence || c.key || c.genType == GenType.ctimestamp)
+                continue;
+
+            if (!first)
+                sb.append(", ");
+            first = false;
+
+            sb.append(c.colname);
+            sb.append(" = ");
+            if (c.genType == GenType.expression)
+                sb.append(c.expression);
+            else {
+                sb.append(" ?");
+                updcolumns.add(c);
+            }
+        }
+
+        if (keycolumns.size() > 0) {
+            sb.append(" where ");
+            first = true;
+
+            for (Column kc : keycolumns) {
+                if (!first) {
+                    sb.append(" and ");
+                }
+                first = false;
+
+                sb.append(kc.colname);
+                sb.append(" = ?");
+            }
+
+        }
+
+        // If we have no key columns, we cannot do an update
+        if (keycolumns.size() == 0)
+            update = null;
+        else
+            update = sb.toString();
+
+    }
+
+    private void createInsert() {
+        inscolumns.clear();
+        StringBuilder sb = new StringBuilder("insert into ");
+        sb.append(table);
+        sb.append(" ( ");
+        boolean first = true;
+        for (Column c : columns) {
+            if (c.genType == GenType.sequence)
+                continue;
+
+            if (!first)
+                sb.append(',');
+
+            first = false;
+            sb.append(c.colname);
+        }
+        sb.append(" ) ");
+
+        sb.append(" values ( ");
+
+        first = true;
+        for (Column c : columns) {
+            if (c.genType == GenType.sequence)
+                continue;
+            if (!first)
+                sb.append(',');
+            first = false;
+
+            if (c.genType == GenType.expression)
+                sb.append(c.expression);
+            else {
+                sb.append(" ? ");
+                inscolumns.add(c);
+            }
+        }
+        sb.append(" ) ");
+
+        insert = sb.toString();
+
+    }
+
+    class Column {
+        
+        int idx;
+        
+        /** name of the column */
+        String name;
+
+        /** database name of the column (in case we need to override */
+        String colname;
+
+        /** Is this a key column? */
+        boolean key;
+
+        /** Type of value generator to use for creating values for this column. */
+        GenType genType;
+
+        /** The (SQL) expression used to populate the column. */
+        String expression;
+
+        /** The SQL data type of this column, one of java.sql.Types */
+        int dataType;
+
+        /** Indicates NULL values are OK */
+        boolean nullok;
+        
+        QName elname;
+
+
+        Column(String name, String colname, boolean key, GenType genType, String expression) {
+            this.name = name;
+            this.colname = colname == null ? name : colname;
+            this.key = key;
+            this.genType = genType;
+            this.expression = expression;
+            elname = new QName(null, name);
+        }
+
+        public Object getValue(String name, RowVal values, Long iid) {
+            switch (genType) {
+            case ctimestamp:
+            case utimestamp:
+                return isTimeStamp() ? new Timestamp(new Date().getTime()) : new Date();
+            case uuid:
+                return new GUID().toString();
+            case pid:
+                return evarId.pid.toString();
+            case iid:
+                return iid;
+            case none:
+            default:
+                return values.get(name);
+            }
+        }
+
+        /**
+         * Return <code>true</code> if column is a date-like type.
+         * 
+         * @return
+         */
+        boolean isDate() {
+            return dataType == Types.DATE;
+        }
+
+        boolean isTimeStamp() {
+            return dataType == Types.TIMESTAMP;
+        }
+
+        boolean isTime() {
+            return dataType == Types.TIME;
+        }
+
+        /**
+         * Is this column best represented as an integer?
+         * 
+         * @return
+         */
+        boolean isInteger() {
+            switch (dataType) {
+            case Types.BIGINT:
+            case Types.INTEGER:
+            case Types.SMALLINT:
+            case Types.TINYINT:
+                return true;
+            default:
+                return false;
+            }
+        }
+
+        /**
+         * Is this column best represented as a real number?
+         * 
+         * @return
+         */
+        boolean isReal() {
+            switch (dataType) {
+            case Types.DECIMAL:
+            case Types.REAL:
+            case Types.NUMERIC:
+                return true;
+            default:
+                return false;
+            }
+
+        }
+
+        boolean isBoolean() {
+            switch (dataType) {
+            case Types.BIT:
+                return true;
+            default:
+                return false;
+            }
+        }
+
+        String toText(Object val) {
+            if (val == null)
+                return null;
+            
+            if (isDate())
+                return ISO8601DateParser.format((Date)val);
+            else if (isTime())
+                return ISO8601DateParser.format((Date)val);
+            else if (isTimeStamp())
+                return ISO8601DateParser.format((Date)val);
+            else
+                return val.toString();
+        }
+
+        Object fromText(String val) throws ExternalVariableModuleException {
+            try {
+                // TODO: use xsd:date and xsd:time conversions
+                if (isDate())
+                    return new java.sql.Date(ISO8601DateParser.parse(val).getTime());
+                else if (isTime())
+                    return new java.sql.Time(ISO8601DateParser.parse(val).getTime());
+                else if (isTimeStamp())
+                    return new java.sql.Timestamp(ISO8601DateParser.parse(val).getTime());
+                else if (isInteger())
+                    return Long.valueOf(val);
+                else if (isReal())
+                    return Double.valueOf(val);
+                else if (isBoolean())
+                    return Boolean.valueOf(val);
+
+                return val;
+            } catch (Exception ex) {
+                throw new ExternalVariableModuleException("Unable to convert value \"" + val + "\" for column \"" + name + "\" !",
+                        ex);
+            }
+        }
+    }
+
+    /**
+     * 
+     * Key used to identify a row.
+     * 
+     * @author Maciej Szefler <mszefler at gmail dot com>
+     * 
+     */
+    class RowKey extends ArrayList<Object> {
+
+        /**
+         * Create empty row key.
+         */
+        RowKey() {
+        }
+
+        /**
+         * Write the key to a locator.
+         * 
+         * @param locator
+         */
+        void write(Locator locator) {
+            locator.clear();
+            int idx = 0;
+            for (Column kc : keycolumns)
+                locator.put(kc.name, kc.toText(get(idx++)));
+        }
+
+    }
+
+    /**
+     * Row values.
+     * 
+     * @author Maciej Szefler <mszefler at gmail dot com>
+     * 
+     */
+    class RowVal extends ArrayList<Object> {
+        RowVal() {
+            super(columns.size());
+            for (int i = 0; i < columns.size(); ++i)
+                add(null);
+        }
+
+        Object get(String name) {
+            Column c = colmap.get(name);
+            if (c == null)
+                return null;
+            int idx = columns.indexOf(c);
+            return get(idx);
+        }
+
+        public void put(String name, Object val) {
+            Column c = colmap.get(name);
+            if (c == null)
+                return;
+
+            int idx = columns.indexOf(c);
+            this.set(idx, val);
+        }
+    }
+
+    Element renderXmlRow(RowVal value) {
+        Document doc = DOMUtils.newDocument();
+        Element el = doc.createElementNS(rowname.getNamespaceURI(), rowname.getLocalPart());
+        doc.appendChild(el);
+        for (Column c : columns) {
+            Object data = value.get(c.idx);
+            Element cel = doc.createElementNS(c.elname.getNamespaceURI(), c.elname.getLocalPart());
+            String strdat = c.toText(data);
+            if (strdat != null)
+                cel.appendChild(doc.createTextNode(strdat));
+            else
+                cel.setAttributeNS(XSI_NS, "xsi:nil", "true");
+                    
+            el.appendChild(cel);
+        }
+
+        return el;
+    }
+
+    RowVal parseXmlRow( Element rowel) throws ExternalVariableModuleException {
+        RowVal ret = new RowVal();
+        NodeList nl = rowel.getChildNodes();
+        for (int i = 0; i < nl.getLength(); ++i) {
+            Node n = nl.item(i);
+            if (n.getNodeType() != Node.ELEMENT_NODE)
+                continue;
+            String key = n.getLocalName();
+            String val = n.getTextContent();
+
+            Column column = getColumn(key);
+            if (column == null)
+                continue;
+
+            String nil = ((Element)n).getAttributeNS(XSI_NS, "nil");
+            if (nil != null && "true".equalsIgnoreCase(nil))
+                ret.put(key,null);
+            else
+                ret.put(key, column.fromText(val));
+        }
+        return ret;
+    }
+
+}
\ No newline at end of file

Propchange: ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/DbExternalVariable.java
------------------------------------------------------------------------------
    svn:eol-style = native

Added: ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/EVarId.java
URL: http://svn.apache.org/viewvc/ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/EVarId.java?rev=590545&view=auto
==============================================================================
--- ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/EVarId.java (added)
+++ ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/EVarId.java Tue Oct 30 19:43:47 2007
@@ -0,0 +1,32 @@
+package org.apache.ode.bpel.evar.jdbc;
+
+import javax.xml.namespace.QName;
+
+/**
+ * Key for identifiying an external variable. 
+ * 
+ * @author Maciej Szefler <mszefler at gmail dot com>
+ *
+ */
+class EVarId {
+    final QName pid;
+    final String varId;
+    
+    
+    EVarId(QName pid, String varId) {
+        this.pid = pid;
+        this.varId = varId;
+    }
+    
+    public boolean equals(Object o) {
+        return ((EVarId)o).varId.equals(varId ) && ((EVarId)o).pid.equals(pid);
+    }
+    
+    public int hashCode() {
+        return varId.hashCode() ^ pid.hashCode(); 
+    }
+    
+    public String toString() {
+        return pid + "#" + varId;
+    }
+}

Propchange: ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/EVarId.java
------------------------------------------------------------------------------
    svn:eol-style = native

Added: ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/GenType.java
URL: http://svn.apache.org/viewvc/ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/GenType.java?rev=590545&view=auto
==============================================================================
--- ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/GenType.java (added)
+++ ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/GenType.java Tue Oct 30 19:43:47 2007
@@ -0,0 +1,33 @@
+package org.apache.ode.bpel.evar.jdbc;
+
+/**
+ * Generator type enumaration.
+ * 
+ * @author Maciej Szefler <mszefler at gmail dot com>
+ *
+ */
+enum GenType {
+    /** plain old column */
+    none,
+    
+    /** sequence column */
+    sequence,
+    
+    /** SQL expression column */
+    expression,
+    
+    /** server-generated uuid column */
+    uuid, 
+    
+    /** process-id column */
+    pid, 
+    
+    /** instance-id column */
+    iid, 
+    
+    /** create timestamp */
+    ctimestamp, 
+    
+    /** update timestamp */
+    utimestamp
+}
\ No newline at end of file

Propchange: ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/GenType.java
------------------------------------------------------------------------------
    svn:eol-style = native

Added: ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/InitType.java
URL: http://svn.apache.org/viewvc/ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/InitType.java?rev=590545&view=auto
==============================================================================
--- ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/InitType.java (added)
+++ ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/InitType.java Tue Oct 30 19:43:47 2007
@@ -0,0 +1,22 @@
+package org.apache.ode.bpel.evar.jdbc;
+
+/**
+ * Enumeration of methods in which a new external variable row initialization is handled. 
+ * 
+ * @author Maciej Szefler <mszefler at gmail dot com>
+ *
+ */
+public enum InitType {
+    /** Just try to update the row, if does not already  exist, fails. */
+    update,
+    
+    /** Just insert the row, if already exist fails. */
+    insert,
+    
+    /** Try updating the row, if no exist, then try inserting. */
+    update_insert,
+    
+    /** First delete the row, then insert a new one. */
+    delete_insert
+
+}

Propchange: ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/InitType.java
------------------------------------------------------------------------------
    svn:eol-style = native

Added: ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/JdbcExternalVariableEngine.java
URL: http://svn.apache.org/viewvc/ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/JdbcExternalVariableEngine.java?rev=590545&view=auto
==============================================================================
--- ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/JdbcExternalVariableEngine.java (added)
+++ ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/JdbcExternalVariableEngine.java Tue Oct 30 19:43:47 2007
@@ -0,0 +1,375 @@
+package org.apache.ode.bpel.evar.jdbc;
+
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.HashMap;
+import java.util.List;
+
+import javax.naming.InitialContext;
+import javax.naming.NamingException;
+import javax.sql.DataSource;
+import javax.xml.namespace.QName;
+
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.apache.ode.bpel.evar.jdbc.DbExternalVariable.Column;
+import org.apache.ode.bpel.evar.jdbc.DbExternalVariable.RowKey;
+import org.apache.ode.bpel.evar.jdbc.DbExternalVariable.RowVal;
+import org.apache.ode.utils.DOMUtils;
+import org.apche.ode.bpel.evar.ExternalVariableModule;
+import org.apche.ode.bpel.evar.ExternalVariableModuleException;
+import org.w3c.dom.Element;
+
+public class JdbcExternalVariableEngine implements ExternalVariableModule {
+
+    private static final Log __log = LogFactory.getLog(JdbcExternalVariableEngine.class);
+
+    /** Unique QName for the engine, this should be the element used for the external-variable configuration. */
+    public static final QName NAME = new QName("http://www.apache.org/ode/extensions/externalVariables", "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(null, "datasource-jndi"));
+        Element jndiRef = DOMUtils.findChildByName(config, new QName(null, "datasource-ref"));
+        Element initMode = DOMUtils.findChildByName(config, new QName(null, "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) {
+                    ;
+                    ;
+                }
+            }
+
+            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;
+        DatabaseMetaData metaData;
+        try {
+            conn = ds.getConnection();
+            metaData = conn.getMetaData();
+        } catch (Exception ex) {
+            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(null, "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(null, "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);
+                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 + "\"!");
+
+                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(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
+
+        RowVal val = evar.parseXmlRow((Element) newval.value);
+        RowKey key = evar.generateKey(newval.locator);
+
+        if (key != null && evar.initType == InitType.delete_insert) {
+            // do delete...
+            // 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 != null;
+
+        boolean insert = evar.initType != InitType.update;
+
+        try {
+            if (tryupdatefirst)
+                insert = execUpdate(evar, val) == 0;
+            if (insert) {
+                key = execInsert(evar, val);
+                // Transfer the keys obtained from the db.
+                key.write(newval.locator);
+            }
+        } catch (SQLException se) {
+            throw new ExternalVariableModuleException("Error updating row.", se);
+        }
+
+        return newval;
+
+    }
+
+    public Value readValue(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. "); // todo
+        
+        Element val;
+        try {
+            RowVal rowval = execSelect(evar, locator);
+            val = evar.renderXmlRow(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 JDBC to look these up.
+     * 
+     * @param dsName
+     * @param ds
+     */
+    public void registerDataSource(String dsName, DataSource ds) {
+        _dataSources.put(dsName, ds);
+    }
+
+    int execUpdate(DbExternalVariable dbev, RowVal values) throws SQLException {
+        Connection conn = dbev.dataSource.getConnection();
+        try {
+            PreparedStatement stmt = conn.prepareStatement(dbev.update);
+            int idx = 1;
+            for (Column c : dbev.updcolumns) {
+                Object val = values.get(c.name);
+                stmt.setObject(idx, val);
+                idx++;
+            }
+
+            for (Column ck : dbev.keycolumns) {
+                Object val = values.get(ck.name);
+                stmt.setObject(idx, val);
+                idx++;
+            }
+
+            return stmt.executeUpdate();
+
+        } finally {
+            conn.close();
+        }
+
+    }
+
+    RowVal execSelect(DbExternalVariable dbev, Locator locator) throws SQLException, ExternalVariableModuleException {
+        RowKey rowkey = dbev.generateKey(locator);
+        RowVal ret = dbev.new RowVal();
+        Connection conn = dbev.dataSource.getConnection();
+        try {
+            PreparedStatement stmt = conn.prepareStatement(dbev.select);
+            int idx = 1;
+            for (Object k : rowkey)
+                stmt.setObject(idx++, k);
+
+            ResultSet rs = stmt.executeQuery();
+            try {
+                if (rs.next()) {
+                    for (Column cr : dbev.columns) 
+                        ret.set(cr.idx,rs.getObject(cr.idx+1));
+
+                } else
+                    return null;
+            } finally {
+                rs.close();
+            }
+        } finally {
+            conn.close();
+        }
+
+        return ret;
+    }
+
+    RowKey execInsert(DbExternalVariable dbev, RowVal values) throws SQLException {
+        RowKey keys = dbev.new RowKey();
+        Connection conn = dbev.dataSource.getConnection();
+        try {
+            PreparedStatement stmt = dbev.generatedKeys ? conn.prepareStatement(dbev.insert, dbev.autoColNames) : conn
+                    .prepareStatement(dbev.insert);
+            int idx = 1;
+            for (Column c : dbev.inscolumns) {
+                Object val = c.getValue(c.name, values, null);
+                values.put(c.name, val);
+                stmt.setObject(idx, val);
+                idx++;
+            }
+
+            stmt.execute();
+
+            if (dbev.generatedKeys) {
+                // With JDBC 3, we can get the values of the key columns (if the db supports it)
+                ResultSet keyRS = stmt.getResultSet();
+                keyRS.next();
+                for (Column ck : dbev.keycolumns)
+                    keys.add(keyRS.getObject(ck.colname));
+            } else {
+                for (Column ck : dbev.keycolumns) {
+                    Object val = values.get(ck.name);
+                    if (val != null)
+                        keys.add(val);
+                }
+            }
+
+            return keys;
+
+        } finally {
+            conn.close();
+        }
+
+    }
+
+}

Propchange: ode/branches/extvar/bpel-runtime/src/main/java/org/apache/ode/bpel/evar/jdbc/JdbcExternalVariableEngine.java
------------------------------------------------------------------------------
    svn:eol-style = native