OpenWFE - Oracle. Can we succed

[chprvkmr]

Hi all,

        We tried to connect to oracle from openWFE by implementing below steps. [partially succeeded. Help needed ;)- ]

step-1:: OracleDataSource
--------------------------
//
// OracleDataSource.java
package openwfe.org.sql.ds;

import openwfe.org.MapUtils;
import openwfe.org.ServiceException;
import openwfe.org.ApplicationContext;

import oracle.jdbc.pool.OracleDataSource;

public class OwfeOracleDataSource

extends OwfeDataSource

{
     private final static org.apache.log4j.Logger log = org.apache.log4j.Logger
     .getLogger(OwfeOracleDataSource.class.getName());
    

    //
    // CONSTANTS (definitions)

    private final static String CON_VALIDITY_SQL_CODE = "select 1 from dual";
    public final static String P_USER = "user";
    public final static String P_PASS = "password";
   
    //
    // FIELDS

    //
    // CONSTRUCTORS

    public void init
    (final String serviceName,
            final ApplicationContext context,
            final java.util.Map serviceParams)
    throws
    ServiceException
    {
        super.init(serviceName, context, serviceParams);

        final String url = MapUtils
        .getMandatoryString(getParams(), P_URL);
        final String user = MapUtils.getMandatoryString(getParams(), P_USER);
        final String password = MapUtils.getMandatoryString(getParams(), P_PASS);
       
        /*final com.mysql.jdbc.jdbc2.optional.MysqlDataSource mds =
            new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();*/
       
        oracle.jdbc.pool.OracleDataSource ods = null;
        try {       
            ods = new oracle.jdbc.pool.OracleDataSource();
        } catch(Exception e) {
            log.debug("**Exception in openwfe.org.sql.ds.OwfeOracleDataSource");
           
            e.printStackTrace();
            throw new ServiceException(e.getMessage());
        }
        ods.setURL(url);
        ods.setUser(user);
        ods.setPassword(password);
       
        setDataSource(ods);
    }

    //
    // METHODS from OwfeDataSource

    public String getConnectionValiditySqlCode ()
    {
        return CON_VALIDITY_SQL_CODE;
    }

    //
    // METHODS from Service

    //
    // METHODS

    /*
     * The new version of the driver (3.0.11) doesn't allow for concurrent
     * connections, this releaseConnection() implementation kills the
     * connection.
     *
     public void releaseConnection ()
     {
     try
     {
     stop();
     }
     catch (Exception e)
     {
     }
     }
     *
     */

}
-----

step-2:: Modified db-worklist-configuration.xml
--------------------------------------

   We added below entry in db-worklist-configuration.xml file and disabled
   other datasource entries that are intended for other databases.

   <service
    name="dataSource"
    class="openwfe.org.sql.ds.OwfeOracleDataSource"
    >
    <param>
        <param-name>url</param-name>
        <param-value>jdbc:oracle:thin:@172.17.170.10:1521:SERPNT</param-value>
    </param>
    <param>
        <param-name>user</param-name>
        <param-value>xlob</param-value>
    </param>
    <param>
        <param-name>password</param-name>
        <param-value>xlob</param-value>
    </param>
    </service>

step-3:: Modified db-engine-configuration.xml
--------------------------------------
  repeated step-2

step-4:: Modify db-engine-configuration.xml
--------------------------------------
  repeated step-2

step-5:: Modified engine-policy.conf
------------------------------------
added below permissions,

    // For oracle support.
    grant codebase "file:./jars/classes12.jar"
    {
        permission java.security.AllPermission;
    };

    grant codebase "file:./jars/classes12.zip"
    {
        permission java.security.AllPermission;
    };

    grant codebase "file:./jars/classes12dms.jar"
    {
        permission java.security.AllPermission;
    };

    grant codebase "file:./jars/classes111.jar"
    {
        permission java.security.AllPermission;
    };

    grant codebase "file:./jars/classes111.zip"
    {
        permission java.security.AllPermission;
    };

    grant codebase "file:./jars/nls_charset11.jar"
    {
        permission java.security.AllPermission;
    };

    grant codebase "file:./jars/nls_charset11.zip"
    {
        permission java.security.AllPermission;
    };

    grant codebase "file:./jars/nls_charset12.jar"
    {
        permission java.security.AllPermission;
    };

    grant codebase "file:./jars/nls_charset12.zip"
    {
        permission java.security.AllPermission;
    };

    grant codebase "file:./jars/ocrs12.jar"
    {
        permission java.security.AllPermission;
    };

    grant codebase "file:./jars/ocrs12.zip"
    {
        permission java.security.AllPermission;
    };

    grant codebase "file:./jars/ojdbc14.jar"
    {
        permission java.security.AllPermission;
    };

    grant codebase "file:./jars/ojdbc14_g.jar"
    {
        permission java.security.AllPermission;
    };

(Temporarily we have given full permissions to all available .jars
as we got initially security exception for reading some properties
related to oracle)

step-6:: Modified worklist-policy.conf
------------------------------------
  Repeated step-5

step-7:: Created database schema for oracle based on scripts in sql folder of openWFE
-----------------------------------------------------------------------------------
[The below tables were created in a user, 'xlob' --> Please verify schema]

    1)
    create table xldocument
    (
        prefix varchar2(30) not null,
        engineId varchar2(40) not null,
        initialEngineId varchar2(40) not null,
        wfdUrl varchar2(255) not null,
        wfdName varchar2(50) not null,
        wfdRevision varchar2(40) not null,
        wfInstanceId varchar2(40) not null,
        expressionName varchar2(40) not null,
        expressionId varchar2(50) not null,
        xldoc clob not null,
        primary key (prefix, engineId, initialEngineId, wfdName, wfdRevision, wfInstanceId, expressionId)
    );

    create index wfid_index on xldocument (wfInstanceId);
    create index exid_index on xldocument (expressionId);

    2)
    create table owfehistory
    (
        date_ varchar2(8) not null,
        -----> [ NOTE:: As we got below stack trace, we changed datatype from 'date' to 'varchar2' - Very Sad ]

        2005-12-12 18:32:21,841 [Thread-9] WARN  openwfe.org.engine.impl.history.SqlHistory - Failed to log to history
        java.sql.SQLException: ORA-01861: literal does not match format string

        <-----

        engineid varchar2(255) not null,
        wfdname varchar2(255) not null,
        wfdrevision varchar2(255) not null,
        wfdid varchar2(10) not null,
        expression varchar2(255) not null,
        eventcode varchar2(7) not null,
        participantname varchar2(255),
        message varchar2(255) not null,
        primary key (date_, engineid, wfdid, expression)
    );

    create index wfdname_index on owfehistory (wfdname);
    create index wfdid_index on owfehistory (wfdid);
    create index eventcode_index on owfehistory (eventcode);

    3)
    create table puser
    (
        name varchar2(100) not null,
        primary key (name)
    );

    4)
    create table pgranted
    (
        username varchar2(100) not null,
        grantname varchar2(100) not null,
        primary key (username, grantname)
    );

    5)
    create table pgrant
    (
        name varchar2(100) not null,
        codebase varchar2(255) not null,
        primary key (name)
    );

    6)
    create table pparameter
    (
        pid int not null,
        pgroup varchar2(100) not null,
        pkey varchar2(100) not null,
        pval varchar2(255) not null,
        primary key (pid, pgroup, pkey)
    );

step-8:: Executed insert scripts inopenwfe-1.6.1\sql\passwd\mysql_powfe_dump.sql
-----------------------------------------------------------------------------------

    Inserts data in tables `pgrant`,`pgranted`,`pgranted`,`pparameter`
    (Is this step necessay ?!)

Stack Trace1 [After starting openWFE]
---------------------------------------

    [engine.log]
    ------------
    2005-12-12 20:19:06,114 [mainEngine::expool.sched] INFO  openwfe.org.engine.impl.expool.AbstractExpressionStore - Failed to load Engine Environment. Returning empty new one ( openwfe.org.engine.expool.PoolException: load failure )
    2005-12-12 20:19:06,161 [mainEngine::expool.sched] DEBUG openwfe.org.xlob.XlobSessionImpl - doQuery() for count(*) / ( fei ee/ee ee ee ee  __env__  )
    2005-12-12 20:19:06,161 [mainEngine::expool.sched] DEBUG openwfe.org.xlob.XlobSessionImpl - prepareStatement() for SELECT count(*) / ( fei ee/ee ee ee ee  __env__  )
    2005-12-12 20:19:06,161 [mainEngine::expool.sched] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() (openwfe.org.sql.ds.OwfeOracleDataSource)
    2005-12-12 20:19:06,223 [mainEngine::expool.sched] DEBUG openwfe.org.sql.ds.OwfeDataSource - isConnectionInvalid() seems that con is valid.
    2005-12-12 20:19:06,223 [mainEngine::expool.sched] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() took 62 ms
    2005-12-12 20:19:06,614 [mainEngine::expool.sched] DEBUG openwfe.org.sql.ds.OwfeDataSource - releaseConnection()
    2005-12-12 20:19:06,614 [mainEngine::expool.sched] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() (openwfe.org.sql.ds.OwfeOracleDataSource)
    2005-12-12 20:19:06,895 [mainEngine::expool.sched] DEBUG openwfe.org.sql.ds.OwfeDataSource - isConnectionInvalid() seems that con is valid.
    2005-12-12 20:19:06,895 [mainEngine::expool.sched] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() took 281 ms
    2005-12-12 20:19:07,067 [mainEngine::expool.sched] DEBUG openwfe.org.sql.ds.OwfeDataSource - releaseConnection()
    2005-12-12 20:19:07,067 [mainEngine::expool.sched] WARN  openwfe.org.engine.impl.expool.AbstractExpressionStore - Failed to store Engine Environment. Resuming anyway...

    openwfe.org.engine.expool.PoolException: storage failure
        at openwfe.org.xlob.engine.XlobExpressionStore.storeExpression(XlobExpressionStore.java:136)
        at openwfe.org.engine.impl.expool.AbstractExpressionStore.loadEngineEnvironment(AbstractExpressionStore.java:136)
        at openwfe.org.engine.impl.expool.SimpleExpressionPool.fetchEngineEnvironment(SimpleExpressionPool.java:310)
        at openwfe.org.engine.impl.expool.SimpleExpressionPool.reschedule(SimpleExpressionPool.java:1133)
        at openwfe.org.engine.impl.expool.SimpleExpressionPool.trigger(SimpleExpressionPool.java:1195)
        at openwfe.org.time.Scheduler$Entry.trigger(Scheduler.java:397)
        at openwfe.org.time.Scheduler.wakeup(Scheduler.java:370)
        at openwfe.org.time.Scheduler$SchedulerThread.run(Scheduler.java:467)
    Caused by: openwfe.org.xlob.XlobException: update failure for ( fei ee/ee ee ee ee  __env__  )
        at openwfe.org.xlob.XlobSessionImpl.store(XlobSessionImpl.java:439)
        at openwfe.org.xlob.XlobSessionImpl.store(XlobSessionImpl.java:374)
        at openwfe.org.xlob.XlobSessionImpl.storeBean(XlobSessionImpl.java:386)
        at openwfe.org.xlob.engine.XlobExpressionStore.storeExpression(XlobExpressionStore.java:132)
        ... 7 more
    Caused by: java.sql.SQLException: ORA-01400: cannot insert NULL into ("XLOB"."XLDOCUMENT"."WFINSTANCEID")

        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
        at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java)
        at oracle.jdbc.ttc7.Oall7.receive(Oall7.java)
        at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java)
        at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java)
        at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java)
        at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java)
        at openwfe.org.xlob.XlobSessionImpl.store(XlobSessionImpl.java:435)
        ... 10 more
    2005-12-12 20:19:07,083 [mainEngine::expool.sched] INFO  openwfe.org.engine.impl.expool.SimpleExpressionPool - reschedule() done.
    2005-12-12 20:20:05,819 [Thread-1] DEBUG openwfe.org.rest.RestService - session cleaner waking up

    (We got disappointed here)

    2005-12-12 20:21:00,211 [Thread-1] INFO  openwfe.org.engine.impl.expool.SimpleExpressionPool - purge() starting...
    2005-12-12 20:21:05,976 [Thread-1] DEBUG openwfe.org.engine.expressions.map.XmlExpressionMap - getNames() for class openwfe.org.engine.expressions.ExpressionWithTimeOut
    2005-12-12 20:21:05,976 [Thread-1] DEBUG openwfe.org.engine.expressions.map.XmlExpressionMap - getNames()   - 'invoke'
    2005-12-12 20:21:05,976 [Thread-1] DEBUG openwfe.org.engine.expressions.map.XmlExpressionMap - getNames()   - 'when'
    2005-12-12 20:21:05,976 [Thread-1] DEBUG openwfe.org.engine.expressions.map.XmlExpressionMap - getNames()   - 'pre-participant'
    2005-12-12 20:21:05,976 [Thread-1] DEBUG openwfe.org.engine.expressions.map.XmlExpressionMap - getNames()   - 'participant'
    2005-12-12 20:21:05,976 [Thread-1] DEBUG openwfe.org.xlob.engine.XlobExpressionStore - contentIterator() esn.length : 4
    2005-12-12 20:21:05,976 [Thread-1] DEBUG openwfe.org.xlob.XlobSessionImpl - doQuery() for xldoc / ( fei null/null null null null null invoke null )
    2005-12-12 20:21:05,976 [Thread-1] DEBUG openwfe.org.xlob.XlobSessionImpl - prepareStatement() for SELECT xldoc / ( fei null/null null null null null invoke null )
    2005-12-12 20:21:05,976 [Thread-1] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() (openwfe.org.sql.ds.OwfeOracleDataSource)
    2005-12-12 20:21:06,336 [Thread-1] DEBUG openwfe.org.sql.ds.OwfeDataSource - isConnectionInvalid() seems that con is valid.
    2005-12-12 20:21:06,336 [Thread-1] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() took 360 ms
    2005-12-12 20:21:06,742 [Thread-1] DEBUG openwfe.org.sql.ds.OwfeDataSource - releaseConnection()

    [webclient.log]
    ---------------

    (Getting the below stack trace repeatedly)

    2005-12-12 20:18:07,566 INFO  org.mortbay.http.SocketListener - Started SocketListener on 0.0.0.0:7080
    2005-12-12 20:18:07,566 INFO  org.mortbay.util.Container - Started org.mortbay.jetty.Server@15e9756
    2005-12-12 20:18:17,425 DEBUG org.mortbay.util.ThreadedServer - IGNORED
    java.net.SocketTimeoutException: Accept timed out
        at java.net.PlainSocketImpl.socketAccept(Native Method)
        at java.net.PlainSocketImpl.accept(PlainSocketImpl.java:353)
        at java.net.ServerSocket.implAccept(ServerSocket.java:439)
        at java.net.ServerSocket.accept(ServerSocket.java:410)
        at org.mortbay.util.ThreadedServer.acceptSocket(ThreadedServer.java:432)
        at org.mortbay.util.ThreadedServer$Acceptor.run(ThreadedServer.java:631)
    2005-12-12 20:18:17,566 DEBUG org.mortbay.util.ThreadedServer - IGNORED
    java.net.SocketTimeoutException: Accept timed out
        at java.net.PlainSocketImpl.socketAccept(Native Method)
        at java.net.PlainSocketImpl.accept(PlainSocketImpl.java:353)
        at java.net.ServerSocket.implAccept(ServerSocket.java:439)
        at java.net.ServerSocket.accept(ServerSocket.java:410)
        at org.mortbay.util.ThreadedServer.acceptSocket(ThreadedServer.java:432)
        at org.mortbay.util.ThreadedServer$Acceptor.run(ThreadedServer.java:631)

Stack Trace2 [After logging in as 'admin' in webclient]
-------------------------------------------------------

    [webclient.log]
    ---------------
    2005-12-12 20:23:07,729 DEBUG org.mortbay.http.ResourceCache - getResource /pages/footer.jsp
    2005-12-12 20:23:07,729 DEBUG org.mortbay.http.ResourceCache - CACHE MISS: file:/D:/openwfe-1.6.1_oracletodo/webapps/webclient/pages/footer.jsp
    2005-12-12 20:23:07,729 DEBUG org.mortbay.jetty.servlet.ServletHandler - IGNORED
    java.io.FileNotFoundException: D:\openwfe-1.6.1_oracletodo\webapps\webclient\pages\footer.jsp (The system cannot find the file specified)
        at java.io.FileInputStream.open(Native Method)
        at java.io.FileInputStream.<init>(FileInputStream.java:103)
        at org.mortbay.util.FileResource.getInputStream(FileResource.java:258)
        at org.mortbay.jetty.servlet.ServletHandler.getResourceAsStream(ServletHandler.java:752)
        at org.mortbay.jetty.servlet.ServletHandler$Context.getResourceAsStream(ServletHandler.java:1077)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:273)
        at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
        at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428)
        at org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandler.java:473)

    (blah..blah..)

    2005-12-12 20:23:07,729 DEBUG org.mortbay.http.HttpContext - Handled by org.mortbay.jetty.servlet.WebApplicationHandler@1ad98ef
    2005-12-12 20:23:07,729 DEBUG org.mortbay.http.HttpInputStream - resetStream()
    2005-12-12 20:23:07,729 DEBUG org.mortbay.http.HttpConnection - RESPONSE:
    HTTP/1.1 200 OK
    Date: Mon, 12 Dec 2005 14:53:03 GMT
    Server: Jetty/5.1.6 (Windows XP/5.1 x86 java/1.4.1_01

   GOOD THING IS ADMIN IS ABLE TO SEE ALL THE STORES (ANYHOW NO STORE IS HAVING ANY WORKITEM)

Stack Trace3 [After launching a workflow from webclient]
-------------------------------------------------------

   [engine.log]
   (quite big. Please go thru it)

    2005-12-12 20:31:12,742 [Thread-3] WARN  openwfe.org.engine.expressions.AbstractFlowExpression - storeItself() failure
    openwfe.org.engine.expool.PoolException: storage failure
        at openwfe.org.xlob.engine.XlobExpressionStore.storeExpression(XlobExpressionStore.java:136)
        at openwfe.org.engine.impl.expool.SimpleExpressionPool.update(SimpleExpressionPool.java:270)
        at openwfe.org.engine.impl.expool.CachedExpressionPool.update(CachedExpressionPool.java:176)
        at openwfe.org.engine.expressions.AbstractFlowExpression.storeItself(AbstractFlowExpression.java:367)
        at openwfe.org.engine.expressions.Environment.put(Environment.java:287)
        at openwfe.org.engine.expressions.Environment.putAll(Environment.java:306)
        at openwfe.org.engine.impl.launch.SimpleXmlLauncher.launchSub(SimpleXmlLauncher.java:345)
        at openwfe.org.engine.impl.launch.SimpleXmlLauncher.launch(SimpleXmlLauncher.java:288)
        at openwfe.org.engine.impl.launch.SimpleXmlLauncher.launch(SimpleXmlLauncher.java:126)
        at openwfe.org.engine.listen.EngineWorkItemConsumer.launch(EngineWorkItemConsumer.java:137)
        at openwfe.org.engine.listen.EngineWorkItemConsumer.use(EngineWorkItemConsumer.java:95)
        at openwfe.org.engine.impl.listen.SocketListener.handle(SocketListener.java:230)
        at openwfe.org.engine.impl.listen.SocketListener.access$000(SocketListener.java:75)
        at openwfe.org.engine.impl.listen.SocketListener$1.handle(SocketListener.java:119)
        at openwfe.org.net.SocketService$1.run(SocketService.java:244)
    Caused by: openwfe.org.xlob.XlobException: update failure for ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 __env__ 0e )
        at openwfe.org.xlob.XlobSessionImpl.store(XlobSessionImpl.java:439)
        at openwfe.org.xlob.XlobSessionImpl.store(XlobSessionImpl.java:374)
        at openwfe.org.xlob.XlobSessionImpl.storeBean(XlobSessionImpl.java:386)
        at openwfe.org.xlob.engine.XlobExpressionStore.storeExpression(XlobExpressionStore.java:132)
        ... 14 more
    Caused by: java.sql.SQLException: No more data to read from socket
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
        at oracle.jdbc.dbaccess.DBError.check_error(DBError.java)
        at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java)
        at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java)
        at oracle.jdbc.ttc7.Oall7.receive(Oall7.java)
        at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java)
        at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java)
        at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java)
        at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java)
        at openwfe.org.xlob.XlobSessionImpl.store(XlobSessionImpl.java:435)
        ... 17 more

        2005-12-12 20:31:12,758 [Thread-3] DEBUG openwfe.org.engine.expressions.Environment - put() 'flow' in ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 __env__ 0e )
        2005-12-12 20:31:12,758 [Thread-3] DEBUG openwfe.org.engine.expressions.AbstractFlowExpression - storeItself() invoked for ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 __env__ 0e ) (openwfe.org.engine.expressions.Environment)
        2005-12-12 20:31:12,758 [Thread-3] DEBUG openwfe.org.xlob.XlobSessionImpl - doQuery() for count(*) / ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 __env__ 0e )
        2005-12-12 20:31:12,758 [Thread-3] DEBUG openwfe.org.xlob.XlobSessionImpl - prepareStatement() for SELECT count(*) / ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 __env__ 0e )
        2005-12-12 20:31:12,758 [Thread-3] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() (openwfe.org.sql.ds.OwfeOracleDataSource)
        2005-12-12 20:31:12,758 [Thread-3] INFO  openwfe.org.sql.ds.OwfeDataSource - isConnectionInvalid() Yes : java.sql.SQLException: No more data to read from socket
        2005-12-12 20:31:12,758 [Thread-3] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() closing old connection
        2005-12-12 20:31:12,773 [Thread-3] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() failed to close old connection java.sql.SQLException: No more data to read from socket
        2005-12-12 20:31:12,773 [Thread-3] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() setting up new connection
        2005-12-12 20:31:13,383 [Thread-3] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() took 625 ms
        2005-12-12 20:31:13,664 [Thread-3] DEBUG openwfe.org.sql.ds.OwfeDataSource - releaseConnection()
        2005-12-12 20:31:13,664 [Thread-3] DEBUG openwfe.org.xlob.XlobSessionImpl - prepareStatement() for DELETE  / ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 __env__ 0e )
        2005-12-12 20:31:13,664 [Thread-3] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() (openwfe.org.sql.ds.OwfeOracleDataSource)
        2005-12-12 20:31:13,664 [Thread-3] DEBUG openwfe.org.sql.ds.OwfeDataSource - isConnectionInvalid() seems that con is valid.
        2005-12-12 20:31:13,664 [Thread-3] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() took 0 ms
        2005-12-12 20:31:13,679 [Thread-3] DEBUG openwfe.org.sql.ds.OwfeDataSource - releaseConnection()
        2005-12-12 20:31:13,679 [Thread-3] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() (openwfe.org.sql.ds.OwfeOracleDataSource)
        2005-12-12 20:31:13,695 [Thread-3] DEBUG openwfe.org.sql.ds.OwfeDataSource - isConnectionInvalid() seems that con is valid.
        2005-12-12 20:31:13,695 [Thread-3] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() took 16 ms
        2005-12-12 20:31:13,945 [Thread-3] DEBUG openwfe.org.sql.ds.OwfeDataSource - releaseConnection()
        2005-12-12 20:31:13,945 [Thread-3] WARN  openwfe.org.engine.expressions.AbstractFlowExpression - storeItself() failure
        openwfe.org.engine.expool.PoolException: storage failure
            at openwfe.org.xlob.engine.XlobExpressionStore.storeExpression(XlobExpressionStore.java:136)
            at openwfe.org.engine.impl.expool.SimpleExpressionPool.update(SimpleExpressionPool.java:270)
            at openwfe.org.engine.impl.expool.CachedExpressionPool.update(CachedExpressionPool.java:176)
            at openwfe.org.engine.expressions.AbstractFlowExpression.storeItself(AbstractFlowExpression.java:367)
            at openwfe.org.engine.impl.launch.SimpleXmlLauncher.launchSub(SimpleXmlLauncher.java:346)
            at openwfe.org.engine.impl.launch.SimpleXmlLauncher.launch(SimpleXmlLauncher.java:288)
            at openwfe.org.engine.impl.launch.SimpleXmlLauncher.launch(SimpleXmlLauncher.java:126)
            at openwfe.org.engine.listen.EngineWorkItemConsumer.launch(EngineWorkItemConsumer.java:137)
            at openwfe.org.engine.listen.EngineWorkItemConsumer.use(EngineWorkItemConsumer.java:95)
            at openwfe.org.engine.impl.listen.SocketListener.handle(SocketListener.java:230)
            at openwfe.org.engine.impl.listen.SocketListener.access$000(SocketListener.java:75)
            at openwfe.org.engine.impl.listen.SocketListener$1.handle(SocketListener.java:119)
            at openwfe.org.net.SocketService$1.run(SocketService.java:244)
        Caused by: openwfe.org.xlob.XlobException: update failure for ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 __env__ 0e )
            at openwfe.org.xlob.XlobSessionImpl.store(XlobSessionImpl.java:439)
            at openwfe.org.xlob.XlobSessionImpl.store(XlobSessionImpl.java:374)
            at openwfe.org.xlob.XlobSessionImpl.storeBean(XlobSessionImpl.java:386)
            at openwfe.org.xlob.engine.XlobExpressionStore.storeExpression(XlobExpressionStore.java:132)
            ... 12 more
        Caused by: java.sql.SQLException: No more data to read from socket
            at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
            at oracle.jdbc.dbaccess.DBError.check_error(DBError.java)
            at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java)
            at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java)
            at oracle.jdbc.ttc7.Oall7.receive(Oall7.java)
            at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java)
            at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java)
            at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java)
            at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java)
            at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java)
            at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java)
            at openwfe.org.xlob.XlobSessionImpl.store(XlobSessionImpl.java:435)
            ... 15 more
        2005-12-12 20:31:13,945 [Thread-3] DEBUG openwfe.org.engine.impl.launch.SimpleXmlLauncher - launchSub() rawExp.id is     ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 process-definition 0 )
        2005-12-12 20:31:13,945 [Thread-3] DEBUG openwfe.org.engine.impl.launch.SimpleXmlLauncher - launchSub() rawExp.parent is null
        2005-12-12 20:31:13,945 [Thread-3] INFO  openwfe.org.engine.impl.launch.SimpleXmlLauncher - addChildToParent() parent is null
        2005-12-12 20:31:13,945 [Thread-3] DEBUG openwfe.org.engine.impl.launch.SimpleXmlLauncher - launchSub() async ? true
        2005-12-12 20:31:13,945 [Thread-3] DEBUG openwfe.org.engine.impl.launch.SimpleXmlLauncher - launchSub() applied 1134399670382
        2005-12-12 20:31:13,945 [Thread-3] DEBUG openwfe.org.engine.impl.launch.SimpleXmlLauncher - launch() launched as sub : ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 process-definition 0 )
        2005-12-12 20:31:13,945 [Thread-3] DEBUG openwfe.org.engine.impl.launch.SimpleXmlLauncher - launch() returning :       1134399670382
        2005-12-12 20:31:13,945 [Thread-3] DEBUG openwfe.org.engine.impl.listen.SocketListener - handle() took 3625
        2005-12-12 20:31:13,945 [Thread-8] DEBUG openwfe.org.engine.impl.expool.SimpleExpressionPool - ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 process-definition 0 ) :: -dbg :: applying
        2005-12-12 20:31:13,961 [Thread-8] DEBUG openwfe.org.engine.impl.expool.SimpleExpressionPool - apply() tagged wi to ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 process-definition 0 )
        2005-12-12 20:31:13,961 [Thread-8] DEBUG openwfe.org.engine.expressions.raw.XmlRawExpression - resolveExpression() expressionName >process-definition<
        2005-12-12 20:31:13,961 [Thread-8] DEBUG openwfe.org.engine.impl.expool.SimpleExpressionPool - lookupEnvironment() for ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 process-definition 0 )
        2005-12-12 20:31:13,961 [Thread-8] DEBUG openwfe.org.engine.expressions.Environment - lookup() in ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 __env__ 0e ) for 'process-definition'
        2005-12-12 20:31:13,961 [Thread-8] DEBUG openwfe.org.engine.expressions.Environment - get() in ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 __env__ 0e ) for 'process-definition'
        2005-12-12 20:31:13,961 [Thread-8] DEBUG openwfe.org.engine.expressions.Environment - get() for 'process-definition'. Not found in ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 __env__ 0e )
        2005-12-12 20:31:13,961 [Thread-9] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() (openwfe.org.sql.ds.OwfeOracleDataSource)
        2005-12-12 20:31:13,961 [Thread-9] INFO  openwfe.org.sql.ds.OwfeDataSource - isConnectionInvalid() Yes : java.sql.SQLException: Io exception: Software caused connection abort: socket write error
        2005-12-12 20:31:13,961 [Thread-9] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() closing old connection
        2005-12-12 20:31:13,961 [Thread-9] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() failed to close old connection java.sql.SQLException: Io exception: Software caused connection abort: socket write error
        2005-12-12 20:31:13,961 [Thread-9] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() setting up new connection
        2005-12-12 20:31:13,976 [Thread-8] DEBUG openwfe.org.xlob.XlobSessionImpl - doQuery() for xldoc / ( fei ee/ee ee ee ee  __env__  )
        2005-12-12 20:31:13,976 [Thread-8] DEBUG openwfe.org.xlob.XlobSessionImpl - prepareStatement() for SELECT xldoc / ( fei ee/ee ee ee ee  __env__  )
        2005-12-12 20:31:14,508 [Thread-9] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() took 547 ms
        2005-12-12 20:31:14,508 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() (openwfe.org.sql.ds.OwfeOracleDataSource)
        2005-12-12 20:31:14,586 [Thread-9] WARN  openwfe.org.engine.impl.history.SqlHistory - Failed to log to history
        java.sql.SQLException: ORA-01401: inserted value too large for column

            at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
            at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java)
            at oracle.jdbc.ttc7.Oall7.receive(Oall7.java)
            at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java)
            at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java)
            at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java)
            at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java)
            at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java)
            at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java)
            at openwfe.org.engine.impl.history.SqlHistory.doLog(SqlHistory.java:199)
            at openwfe.org.engine.impl.history.SqlHistory.access$000(SqlHistory.java:68)
            at openwfe.org.engine.impl.history.SqlHistory$1.run(SqlHistory.java:161)
        2005-12-12 20:31:14,586 [Thread-9] ERROR openwfe.org.engine.impl.history.SqlHistory - log() SQLException : java.sql.SQLException: ORA-01401: inserted value too large for column

        2005-12-12 20:31:14,601 [Thread-9] DEBUG openwfe.org.sql.ds.OwfeDataSource - releaseConnection()
        2005-12-12 20:31:14,601 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - isConnectionInvalid() seems that con is valid.
        2005-12-12 20:31:14,601 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() took 93 ms
        2005-12-12 20:31:14,601 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - releaseConnection()
        2005-12-12 20:31:14,601 [Thread-8] INFO  openwfe.org.engine.impl.expool.AbstractExpressionStore - Failed to load Engine Environment. Returning empty new one ( openwfe.org.engine.expool.PoolException: load failure )
        2005-12-12 20:31:14,601 [Thread-8] DEBUG openwfe.org.xlob.XlobSessionImpl - doQuery() for count(*) / ( fei ee/ee ee ee ee  __env__  )
        2005-12-12 20:31:14,601 [Thread-8] DEBUG openwfe.org.xlob.XlobSessionImpl - prepareStatement() for SELECT count(*) / ( fei ee/ee ee ee ee  __env__  )
        2005-12-12 20:31:14,601 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() (openwfe.org.sql.ds.OwfeOracleDataSource)
        2005-12-12 20:31:14,601 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - isConnectionInvalid() seems that con is valid.
        2005-12-12 20:31:14,601 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() took 0 ms
        2005-12-12 20:31:14,617 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - releaseConnection()
        2005-12-12 20:31:14,617 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() (openwfe.org.sql.ds.OwfeOracleDataSource)
        2005-12-12 20:31:14,617 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - isConnectionInvalid() seems that con is valid.
        2005-12-12 20:31:14,617 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() took 0 ms
        2005-12-12 20:31:14,617 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - releaseConnection()
        2005-12-12 20:31:14,617 [Thread-8] WARN  openwfe.org.engine.impl.expool.AbstractExpressionStore - Failed to store Engine Environment. Resuming anyway...
        openwfe.org.engine.expool.PoolException: storage failure
            at openwfe.org.xlob.engine.XlobExpressionStore.storeExpression(XlobExpressionStore.java:136)
            at openwfe.org.engine.impl.expool.AbstractExpressionStore.loadEngineEnvironment(AbstractExpressionStore.java:136)
            at openwfe.org.engine.impl.expool.SimpleExpressionPool.fetchEngineEnvironment(SimpleExpressionPool.java:310)
            at openwfe.org.engine.expressions.Environment.lookup(Environment.java:258)
            at openwfe.org.engine.impl.expool.SimpleExpressionPool.lookupVariable(SimpleExpressionPool.java:1094)
            at openwfe.org.engine.expressions.AbstractFlowExpression.lookupVariable(AbstractFlowExpression.java:291)
            at openwfe.org.engine.expressions.raw.XmlRawExpression.resolveExpression(XmlRawExpression.java:307)
            at openwfe.org.engine.expressions.raw.XmlRawExpression.resolveExpression(XmlRawExpression.java:269)
            at openwfe.org.engine.expressions.raw.RawExpression.preApply(RawExpression.java:309)
            at openwfe.org.engine.expressions.raw.RawExpression.apply(RawExpression.java:333)
            at openwfe.org.engine.expressions.state.NormalState.apply(NormalState.java:112)
            at openwfe.org.engine.impl.expool.SimpleExpressionPool.apply(SimpleExpressionPool.java:386)
            at openwfe.org.engine.impl.launch.SimpleXmlLauncher$1.run(SimpleXmlLauncher.java:379)
        Caused by: openwfe.org.xlob.XlobException: update failure for ( fei ee/ee ee ee ee  __env__  )
            at openwfe.org.xlob.XlobSessionImpl.store(XlobSessionImpl.java:439)
            at openwfe.org.xlob.XlobSessionImpl.store(XlobSessionImpl.java:374)
            at openwfe.org.xlob.XlobSessionImpl.storeBean(XlobSessionImpl.java:386)
            at openwfe.org.xlob.engine.XlobExpressionStore.storeExpression(XlobExpressionStore.java:132)
            ... 12 more
        Caused by: java.sql.SQLException: ORA-01400: cannot insert NULL into ("XLOB"."XLDOCUMENT"."WFINSTANCEID")

            at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
            at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java)
            at oracle.jdbc.ttc7.Oall7.receive(Oall7.java)
            at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java)
            at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java)
            at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java)
            at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java)
            at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java)
            at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java)
            at openwfe.org.xlob.XlobSessionImpl.store(XlobSessionImpl.java:435)
            ... 15 more
        2005-12-12 20:31:14,617 [Thread-8] DEBUG openwfe.org.engine.expressions.Environment - get() in ( fei ee/ee ee ee ee  __env__  ) for 'process-definition'
        2005-12-12 20:31:14,617 [Thread-8] DEBUG openwfe.org.engine.expressions.raw.XmlRawExpression - resolveExpression() this.parent is null
        2005-12-12 20:31:14,617 [Thread-8] DEBUG openwfe.org.engine.expressions.raw.XmlRawExpression - resolveExpression() oValue is class openwfe.org.engine.expressions.DefineExpression
        2005-12-12 20:31:14,617 [Thread-8] DEBUG openwfe.org.engine.expressions.raw.XmlRawExpression - resolveExpression() built-in expression
        2005-12-12 20:31:14,617 [Thread-8] DEBUG openwfe.org.engine.expressions.raw.XmlRawExpression - init() set id to     ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 sequence 0.0 )
        2005-12-12 20:31:14,617 [Thread-8] DEBUG openwfe.org.engine.expressions.raw.XmlRawExpression - init() set env id to ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 __env__ 0e )
        2005-12-12 20:31:14,633 [Thread-8] DEBUG openwfe.org.xlob.XlobSessionImpl - doQuery() for count(*) / ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 sequence 0.0 )
        2005-12-12 20:31:14,633 [Thread-8] DEBUG openwfe.org.xlob.XlobSessionImpl - prepareStatement() for SELECT count(*) / ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 sequence 0.0 )
        2005-12-12 20:31:14,633 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() (openwfe.org.sql.ds.OwfeOracleDataSource)
        2005-12-12 20:31:14,633 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - isConnectionInvalid() seems that con is valid.
        2005-12-12 20:31:14,633 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() took 0 ms
        2005-12-12 20:31:14,648 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - releaseConnection()
        2005-12-12 20:31:14,648 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() (openwfe.org.sql.ds.OwfeOracleDataSource)
        2005-12-12 20:31:14,664 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - isConnectionInvalid() seems that con is valid.
        2005-12-12 20:31:14,664 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() took 16 ms
        2005-12-12 20:31:14,820 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - releaseConnection()
        2005-12-12 20:31:14,820 [Thread-8] WARN  openwfe.org.engine.impl.launch.SimpleXmlLauncher - launchSub() failure
        openwfe.org.engine.expressions.ApplyException: preApply failed
            at openwfe.org.engine.expressions.raw.RawExpression.preApply(RawExpression.java:323)
            at openwfe.org.engine.expressions.raw.RawExpression.apply(RawExpression.java:333)
            at openwfe.org.engine.expressions.state.NormalState.apply(NormalState.java:112)
            at openwfe.org.engine.impl.expool.SimpleExpressionPool.apply(SimpleExpressionPool.java:386)
            at openwfe.org.engine.impl.launch.SimpleXmlLauncher$1.run(SimpleXmlLauncher.java:379)
        Caused by: openwfe.org.engine.expressions.BuildException: Failed to add child to pool ( fei mainEngine/mainEngine http://localhost:7079/flow__1.0.xml flow 1.0 1134399670382 sequence 0.0 )
            at openwfe.org.engine.expressions.raw.XmlRawExpression.prepareChildren(XmlRawExpression.java:588)
            at openwfe.org.engine.expressions.AbstractCompositeFlowExpression.init(AbstractCompositeFlowExpression.java:267)
            at openwfe.org.engine.expressions.CleanCompositeFlowExpression.init(CleanCompositeFlowExpression.java:95)
            at openwfe.org.engine.expressions.DefineExpression.init(DefineExpression.java:298)
            at openwfe.org.engine.expressions.raw.XmlRawExpression.resolveExpression(XmlRawExpression.java:395)
            at openwfe.org.engine.expressions.raw.XmlRawExpression.resolveExpression(XmlRawExpression.java:269)
            at openwfe.org.engine.expressions.raw.RawExpression.preApply(RawExpression.java:309)
            ... 4 more
        2005-12-12 20:32:05,853 [Thread-1] DEBUG openwfe.org.rest.RestService - session cleaner waking up

        *AFTER LAUNCHING (with above stack trace in engine console) stores.action PAGE OPENED. HERE I OBSERVED
        WORKITEM IS NOT ASSCIATED WITH ANY OF THE STORE. Strange!

        *WHEN DATABASE, xldocument IS EXAMIND I FOUND BELOW ROWS (IN HISTORY TABLE NO DATA WAS THERE)
         
          -----
            PREFIX        ENGINEID    INITIALENGINEID    WFDURL    WFDNAME    WFDREVISION    WFINSTANCEID    EXPRESSIONNAME    EXPRESSIONID    XLDOC
            mainEngine    mainEngine    mainEngine       http://localhost:7079/flow__1.0.xml    flow    1    1.13439E+12    process-definition    0   
            mainEngine    mainEngine    mainEngine       http://localhost:7079/flow__1.0.xml    flow    1    1.13439E+12    __env__    0e   
            mainEngine    mainEngine    mainEngine       http://localhost:7079/flow__1.0.xml    flow    1    1.13439E+12    sequence    0   
          ------

        Sorry for huge post. Please verify is there anything wrong in the steps that we implemented towards connecting
        to oracle. I have spent whole day on this without any success. As already we are behind the schedule, I really want
        to do real stuff with openWFE ASAP. Also please verify tables (schema).

        If possible Ill try to attch logs to John.

Thanks in advance,
Praveen.

[Sebastien Lucas]

Hi,

I'll have a look tonight.

Sébastien

[chprvkmr]

Hi,

     I guess some problems are because of insertion to xlob column in xldocument table, which is of type, 'clob'. So, we can't use openwfe.org.xlob package as it is for oracle. Some changes needs to be done. Apart from this what are the packages that gets affected when we use oracle with openWFE. In brief anyone please tell me what are all changes has to be done to make openWFE compatible with Oracle. (Today also went futile. Real stuff not started yet)

[John Mettraux]

Hi Praveen,

please open your Oracle manual and look for a replacement for the "TEXT" type. You can also have a look at the .sql files for PostgreSQL, it's sometimes closer to Oracle.

Best regards from Costa Rica, sorry for your 'days lost', but you don't spend a penny on any OpenWFE licence or support.

John

[Sebastien Lucas]

Hi,

I think/hope there's no need to change anything in OpenWfe. As soon as I get time to test it, I'll post again in this forum.

That said, don't expect any news from me before friday or saturday as I got a busy life for now.

Regards

Sébastien

[chprvkmr]

Hi All,

        Today I made below changes and until now everything went fine.

Modified XmlSessionImpl.java [store (final FlowExpressionId fei, final String sXml) method]
-------------------------------------------------------------------------------------------

/*
* Copyright (c) 2005, John Mettraux, OpenWFE.org
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
*
* . Redistributions of source code must retain the above copyright notice, this
*   list of conditions and the following disclaimer. 
*
* . Redistributions in binary form must reproduce the above copyright notice,
*   this list of conditions and the following disclaimer in the documentation
*   and/or other materials provided with the distribution.
*
* . Neither the name of the "OpenWFE" nor the names of its contributors may be
*   used to endorse or promote products derived from this software without
*   specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
* ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE
* LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*
* $Id: XlobSessionImpl.java,v 1.4 2005/07/17 16:31:49 jmettraux Exp $
*/

//
// XlobSessionImpl.java
//
// john.mettraux@epfl.ch
//
// generated with
// jtmpl 1.1.01 2004/05/19 (john.mettraux@openwfe.org)
//

package openwfe.org.xlob;

import java.io.BufferedReader;
import java.io.Writer;
import java.sql.Connection;

import openwfe.org.Utils;
import openwfe.org.MapUtils;
import openwfe.org.ServiceException;
import openwfe.org.ApplicationContext;
import openwfe.org.xml.XmlUtils;
import openwfe.org.xml.XmlCoder;
import openwfe.org.xml.XmlCodingException;
import openwfe.org.sql.SqlUtils;
import openwfe.org.sql.ds.OwfeDataSource;
import openwfe.org.engine.expressions.FlowExpressionId;

import java.sql.PreparedStatement;
import java.io.*;
import oracle.sql.*;

public class XlobSessionImpl

    implements XlobSession

{

    private final static org.apache.log4j.Logger log = org.apache.log4j.Logger
        .getLogger(XlobSessionImpl.class.getName());

    //
    // CONSTANTS & co

    private final static String FEI_FIELDS =
        "prefix, "+
        "engineId, "+
        "initialEngineId, "+
        "wfdUrl, "+
        "wfdName, "+
        "wfdRevision, "+
        "wfInstanceId, "+
        "expressionName, "+
        "expressionId";

    private final static String FIELDS =
        FEI_FIELDS + ", xldoc";

    public final static String P_PREFIX
        = "xlobPrefix";

    //
    // FIELDS

    private java.sql.Connection connection = null;
    private OwfeDataSource dataSource = null;

    private String prefix = null;

    //
    // CONSTRUCTORS

    //
    // METHODS

    private java.sql.Connection getConnection ()
        throws java.sql.SQLException
    {
        if (this.connection != null) return this.connection;

        return this.dataSource.getConnection();
    }

    private void releaseConnection ()
    {
        if (this.dataSource != null) this.dataSource.releaseConnection();
    }

    private java.sql.PreparedStatement prepareStatement
        (final String header, FlowExpressionId fei)
    throws
        java.sql.SQLException
    {
        log.debug("prepareStatement() for "+header+" / "+fei);

        if (fei == null) fei = new FlowExpressionId();

        final StringBuffer sb = new StringBuffer();

        sb
            .append(header)
            .append(" FROM xldocument")

            .append(" WHERE")
            .append(" prefix = ?");

        if (fei.getEngineId() != null)
            sb.append(" AND engineId LIKE ?");

        if (fei.getInitialEngineId() != null)
            sb.append(" AND initialEngineId LIKE ?");

        if (fei.getWorkflowDefinitionUrl() != null)
            sb.append(" AND wfdUrl LIKE ?");

        if (fei.getWorkflowDefinitionName() != null)
            sb.append(" AND wfdName LIKE ?");

        if (fei.getWorkflowDefinitionRevision() != null)
            sb.append(" AND wfdRevision LIKE ?");

        if (fei.getWorkflowInstanceId() != null)
            sb.append(" AND wfInstanceId LIKE ?");

        if (fei.getExpressionName() != null)
            sb.append(" AND expressionName LIKE ?");

        if (fei.getExpressionId() != null)
            sb.append(" AND expressionId LIKE ?");

        log.debug("Query for prepared stmt = " + sb.toString());
       
        //
        // prepare statement

        final java.sql.PreparedStatement ps =
            getConnection().prepareStatement(sb.toString());

        int i = 1;

        ps.setString(i++, this.prefix);

        if (fei.getEngineId() != null)
            ps.setString(i++, fei.getEngineId());

        if (fei.getInitialEngineId() != null)
            ps.setString(i++, fei.getInitialEngineId());

        if (fei.getWorkflowDefinitionUrl() != null)
            ps.setString(i++, fei.getWorkflowDefinitionUrl());

        if (fei.getWorkflowDefinitionName() != null)
            ps.setString(i++, fei.getWorkflowDefinitionName());

        if (fei.getWorkflowDefinitionRevision() != null)
            ps.setString(i++, fei.getWorkflowDefinitionRevision());

        if (fei.getWorkflowInstanceId() != null)
            ps.setString(i++, fei.getWorkflowInstanceId());

        if (fei.getExpressionName() != null)
            ps.setString(i++, fei.getExpressionName());

        if (fei.getExpressionId() != null)
            ps.setString(i++, fei.getExpressionId());

        log.debug("this.prefix = " + this.prefix);
        log.debug("fei.getEngineId() = " + fei.getEngineId());
        log.debug("fei.getInitialEngineId() = " + fei.getInitialEngineId());
        log.debug("fei.getWorkflowDefinitionUrl() = " + fei.getWorkflowDefinitionUrl());
        log.debug("fei.getWorkflowDefinitionName() = " + fei.getWorkflowDefinitionName());
        log.debug("fei.getWorkflowDefinitionRevision() = " + fei.getWorkflowDefinitionRevision());
        log.debug("fei.getWorkflowInstanceId() = " + fei.getWorkflowInstanceId());
        log.debug("fei.getExpressionName() = " + fei.getExpressionName());
        log.debug("fei.getExpressionId() = " + fei.getExpressionId());
       
        log.debug("**ps instanceof java.sql.PreparedStatement = " + (ps instanceof java.sql.PreparedStatement));
        log.debug("**ps instanceof oracle.jdbc.driver.OraclePreparedStatement = " + (ps instanceof oracle.jdbc.driver.OraclePreparedStatement));
       
        return ps;
    }

    private java.sql.ResultSet doQuery
        (String selectable, final FlowExpressionId fei)
    throws
        XlobException
    {
        if (selectable == null) selectable = "xldoc";

        log.debug("doQuery() for "+selectable+" / "+fei);

        try
        {
            final java.sql.PreparedStatement ps =
                prepareStatement("SELECT "+selectable, fei);

            return ps.executeQuery();
        }
        catch (final Throwable t)
        {
            throw new XlobException
                ("query failure", t);
        }
    }

    private org.jdom.Document extract (final java.sql.ResultSet rs)
        throws XlobException
    {
        try
        {
            log.debug("extract() -> before rs.getClob(1)");
            final java.sql.Clob clob = rs.getClob(1);
            log.debug("extract() -> rs.getClob(1) = " + clob);
           
            final org.jdom.input.SAXBuilder builder =
                new org.jdom.input.SAXBuilder();

            return builder.build(clob.getCharacterStream());
        }
        catch (final Throwable t)
        {
            throw new XlobException("extraction failure", t);
        }
    }

    private Object extractBean (final java.sql.ResultSet rs)
        throws XlobException
    {
        try
        {
            final org.jdom.Document d = extract(rs);

            return XmlCoder.decode(d);
        }
        catch (final XmlCodingException xce)
        {
            throw new XlobException
                ("Failed to decode bean", xce);
        }
    }

    private FlowExpressionId extractExpressionId (final java.sql.ResultSet rs)
        throws java.sql.SQLException
    {
        int i = 1;

        final FlowExpressionId fei = new FlowExpressionId();

        fei.setEngineId(rs.getString(i++));
        fei.setInitialEngineId(rs.getString(i++));
        fei.setWorkflowDefinitionUrl(rs.getString(i++));
        fei.setWorkflowDefinitionName(rs.getString(i++));
        fei.setWorkflowDefinitionRevision(rs.getString(i++));
        fei.setWorkflowInstanceId(rs.getString(i++));
        fei.setExpressionName(rs.getString(i++));
        fei.setExpressionId(rs.getString(i++));

        return fei;
    }

    private boolean toNext (final java.sql.ResultSet rs)
        throws XlobException
    {
        try
        {
            log.debug("toNext() -> Calling rs.next()");
            return rs.next();
        }
        catch (final Throwable t)
        {
            throw new XlobException("Failed to fetch next row", t);
        }
    }

    private void close (final java.sql.ResultSet rs)
    {
        try
        {
            rs.close();
        }
        catch (final Throwable t)
        {
            // ignore
        }
    }

    private void close (final java.sql.Statement st)
    {
        try
        {
            st.close();
        }
        catch (final Throwable t)
        {
            // ignore
        }
    }

    //
    // METHODS from XlobSession

    public void init (final java.sql.Connection con, final String prefix)
    {
        this.connection = con;
        this.prefix = prefix;
    }

    public void init (OwfeDataSource ds, String prefix)
    {
        this.dataSource = ds;
        this.prefix = prefix;
    }

    public void close ()
    {
        try
        {
            if (this.connection != null) this.connection.close();
        }
        catch (final Throwable t)
        {
            log.info("close() problem", t);
        }
    }

    public void store (final FlowExpressionId fei, final org.jdom.Document doc)
        throws XlobException
    {
        log.debug("Inside store(FlowExpressionId fei, Document doc)");
        log.debug("fei = " + fei + ", doc = " + doc);
       
        store(fei, XmlUtils.toString(doc));
    }

    public void store (final FlowExpressionId fei, final org.jdom.Element elt)
        throws XlobException
    {
        log.debug("Inside store(FlowExpressionId fei, Element elt)");
        log.debug("fei = " + fei + ", elt = " + elt);
       
        store(fei, XmlUtils.toString(elt));
    }

    public void storeBean (final FlowExpressionId fei, final Object bean)
        throws XlobException
    {
        log.debug("Inside storeBean(FlowExpressionId fei, Object bean)");
        log.debug("fei = " + fei + ", bean = " + bean);
       
        try
        {
            store(fei, XmlCoder.encode(bean));
        }
        catch (final XmlCodingException xce)
        {
            throw new XlobException("Failed to encode bean", xce);
        }
    }

    public void store (final FlowExpressionId fei, final String sXml)
        throws XlobException
    {
        java.sql.PreparedStatement ps = null;
        log.debug("****Inside store(FlowExpressionId fei, String sXml)");
        log.debug("fei = " + fei + ", sXml = " + sXml);
       
        try
        {
            if (isStored(fei))
            {
                log.debug("As isStored(fei) is true, calling remove(fei)");
                remove(fei);
            }

            //
            // insert the doc

            final StringBuffer sb = new StringBuffer();

            sb
                .append("INSERT INTO xldocument ")
                .append("(")
                .append(FIELDS)
                .append(") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
           
            Connection con = getConnection();
            ps = con.prepareStatement(sb.toString());

            int i = 1;

            ps.setString(i++, this.prefix);
            ps.setString(i++, fei.getEngineId());
            ps.setString(i++, fei.getInitialEngineId());
            ps.setString(i++, fei.getWorkflowDefinitionUrl());
            ps.setString(i++, fei.getWorkflowDefinitionName());
            ps.setString(i++, fei.getWorkflowDefinitionRevision());
            ps.setString(i++, fei.getWorkflowInstanceId());
            ps.setString(i++, fei.getExpressionName());
            ps.setString(i++, fei.getExpressionId());

            log.debug("Query for prepared stmt = " + sb.toString());

            log.debug("prefix = " + prefix);
            log.debug("fei.getEngineId() = " + fei.getEngineId());
            log.debug("fei.getInitialEngineId() = " + fei.getInitialEngineId());
            log.debug("fei.getWorkflowDefinitionUrl() = " + fei.getWorkflowDefinitionUrl());
            log.debug("fei.getWorkflowDefinitionName() = " + fei.getWorkflowDefinitionName());
            log.debug("fei.getWorkflowDefinitionRevision() = " + fei.getWorkflowDefinitionRevision());
            log.debug("fei.getWorkflowInstanceId() = " + fei.getWorkflowInstanceId());
            log.debug("fei.getExpressionName() = " + fei.getExpressionName());
            log.debug("fei.getExpressionId() = " + fei.getExpressionId());
           
            final java.io.StringReader reader =
                new java.io.StringReader(sXml);

            /*----- Code modification to make it compatible with oracle - Starts */
           
            //ps.setCharacterStream(i++, reader, sXml.length());

            oracle.sql.CLOB newClob = oracle.sql.CLOB.createTemporary(con , false, oracle.sql.CLOB.DURATION_CALL);
            Writer writer = newClob.getCharacterOutputStream();

            BufferedReader  br = new BufferedReader(reader);
            int bytesRead;
            char []byteBuffer = new char[1024];

            log.debug("***Writing to clob***");
            while ((bytesRead = br.read(byteBuffer)) != -1) {
                // After reading a buffer from the text file(char. stream), write the contents
                // of the buffer to the output stream using the write()
                // method.
                writer.write(byteBuffer, 0, bytesRead);
            }
      
            log.debug("***Setting clob to Pstmt***");
            /* Is working iff file.length < 4002 */
            /*ps.setCharacterStream( i++, br, (int)file.length());*/
            ps.setClob(i++, newClob);
           
            log.debug("***calls executeUpdate after (Writer for clob <- ) wrtr.flush() and wrtr.close() ***");
            writer.flush();
            writer.close();
           
            /* Code modification to make it compatible with oracle - Ends -----*/
           
            ps.executeUpdate();
        }
        catch (final Exception e)
        {
            throw new XlobException
                ("update failure for "+fei, e);
        }
        finally
        {
            close(ps);
            releaseConnection();
        }
    }

    public void remove (final FlowExpressionId fei)
        throws XlobException
    {
        java.sql.PreparedStatement ps = null;
        log.debug("In remove(FlowExpressionId fei)");
        log.debug("fei = " + fei);
       
        try
        {
            ps = prepareStatement("DELETE ", fei);

            ps.executeUpdate();
        }
        catch (final Throwable t)
        {
            throw new XlobException("removal failure", t);
        }
        finally
        {
            close(ps);
            releaseConnection();
        }
    }

    public org.jdom.Document find (final FlowExpressionId fei)
        throws XlobException
    {
        java.sql.ResultSet rs = null;
        log.debug("In find(FlowExpressionId fei)");
        log.debug("fei = " + fei);
       
        try
        {
            rs = doQuery(null, fei);

            toNext(rs);

            return extract(rs);
        }
        finally
        {
            close(rs);
            releaseConnection();
        }
    }

    public Object findBean (final FlowExpressionId fei)
        throws XlobException
    {
        try
        {
            return XmlCoder.decode(find(fei).getRootElement());
        }
        catch (final XmlCodingException xce)
        {
            throw new XlobException("decoding failure", xce);
        }
    }

    public boolean isStored (final FlowExpressionId fei)
        throws XlobException
    {
        java.sql.ResultSet rs = null;

        try
        {
            rs = doQuery("count(*)", fei);

            toNext(rs);

            return rs.getInt(1) > 0;
        }
        catch (final java.sql.SQLException se)
        {
            throw new XlobException("failure", se);
        }
        finally
        {
            close(rs);
            releaseConnection();
        }
    }

    public java.util.Set query (final FlowExpressionId fei)
        throws XlobException
    {
        return query(fei, -1);
    }

    public java.util.Set query (FlowExpressionId fei, int limit)
        throws XlobException
    {
        java.sql.ResultSet rs = null;

        try
        {
            rs = doQuery(null, fei);

            final java.util.Set set = new java.util.HashSet();

            int count = 0;

            while (toNext(rs))
            {
                if (limit > 0 && count >= limit) break;

                set.add(extract(rs));

                count++;
            }

            return set;
        }
        finally
        {
            close(rs);
            releaseConnection();
        }
    }

    public java.util.Set queryBeans (final FlowExpressionId fei)
        throws XlobException
    {
        return queryBeans(fei, -1);
    }

    public java.util.Set queryBeans
        (final FlowExpressionId fei, final int limit)
    throws
        XlobException
    {
        java.sql.ResultSet rs = null;

        try
        {
            rs = doQuery(null, fei);

            final java.util.Set set = new java.util.HashSet();

            int count = 0;

            while (toNext(rs))
            {
                if (limit > 0 && count >= limit) break;

                set.add(extractBean(rs));

                count++;
            }

            return set;
        }
        finally
        {
            close(rs);
            releaseConnection();
        }
    }

    public int count (FlowExpressionId fei)
        throws XlobException
    {
        java.sql.ResultSet rs = null;

        if (fei == null) fei = new FlowExpressionId();

        try
        {
            rs = doQuery("count(*)", fei);

            toNext(rs);

            return rs.getInt(1);
        }
        catch (final java.sql.SQLException se)
        {
            throw new XlobException("count failure", se);
        }
        finally
        {
            close(rs);
            releaseConnection();
        }
    }

    public java.util.List listExpressionIds ()
        throws XlobException
    {
        final StringBuffer sb = new StringBuffer();

        sb
            .append("SELECT ")
            .append(FEI_FIELDS)
            .append(" FROM xldocument")
            .append(" WHERE prefix = ?");

        java.sql.PreparedStatement st = null;
        java.sql.ResultSet rs = null;

        log.debug("In listExpressionIds()");
        log.debug("Query for prepared stmt = " + sb.toString());
        log.debug("prefix = " +  this.prefix);
       
        try
        {
            final java.util.List result = new java.util.ArrayList(50);

            st = getConnection().prepareStatement(sb.toString());

            st.setString(1, getPrefix());

            rs = st.executeQuery();

            while (rs.next())
                result.add(extractExpressionId(rs));

            return result;
        }
        catch (final java.sql.SQLException e)
        {
            throw new XlobException
                ("failed to list expression ids", e);
        }
        finally
        {
            close(rs);
            close(st);
            releaseConnection();
        }
    }

    public String getPrefix ()
    {
        return this.prefix;
    }

    public java.util.List listPrefixes ()
        throws XlobException
    {
        java.sql.Statement st = null;
        java.sql.ResultSet rs = null;

        try
        {
            st = getConnection().createStatement();
           
            log.debug("Executing SELECT prefix FROM xldocument");
           
            rs = st.executeQuery("SELECT prefix FROM xldocument");

            final java.util.List l = new java.util.ArrayList(20);

            while (rs.next())
            {
                l.add(rs.getString(1));
            }

            return l;
        }
        catch (final java.sql.SQLException se)
        {
            throw new XlobException
                ("failed to list prefixes", se);
        }
        finally
        {
            close(st);
            close(rs);
            releaseConnection();
        }
    }

    //
    // STATIC METHODS

    public static XlobSession getXlobSession
        (final java.sql.Connection con, final String prefix)
    {
        final XlobSession s = new XlobSessionImpl();
        s.init(con, prefix);

        return s;
    }

    public static XlobSession getXlobSession
        (final OwfeDataSource ds, final String prefix)
    {
        final XlobSession s = new XlobSessionImpl();
        s.init(ds, prefix);

        return s;
    }

    public static XlobSession createSession
        (final ApplicationContext context,
         final java.util.Map serviceParams)
    throws
        ServiceException
    {
        final OwfeDataSource ds =
            SqlUtils.lookupDataSource(context, serviceParams);

        final String prefix = MapUtils
            .getAsString(serviceParams, P_PREFIX, context.getName());

        return getXlobSession(ds, prefix);
    }

    public static XlobSession createSession
        (final String prefix,
         final ApplicationContext context,
         final java.util.Map serviceParams)
    throws
        ServiceException
    {
        final OwfeDataSource ds =
            SqlUtils.lookupDataSource(context, serviceParams);

        return getXlobSession(ds, prefix);
    }

}

Included oracle jdbc libraries in OPENWFE_HOME\jars folder
----------------------------------------------------------
Do not keep classes111.jar, here Oracle's PreparedStament doesn't support
setClob(java.sql.Clob) method.

(I have included below jars - I guess classes12.jar is enough but not tested)

classes12.jar,classes12dms.jar,ocrs12.jar,ojdbc14.jar,ojdbc14_g.jar,
nls_charset12.jar,classes12.zip,nls_charset12.zip,ocrs12.zip

  THIS MADE ME TO CONNECT WITH ORACLE AND I TRIED SIMPLE PROCESS DEF. WHICH
  RAN FINE

----- ***** -----

[chprvkmr]

[CONTD.]

I updated  owfehistory as per OPENWFE_HOME\sql\xlob\mssql_xlob.sql

    create table owfehistory
    (
        date_ varchar2(8) not null,
        engineid varchar2(255) not null,
        wfdname varchar2(255) not null,
        wfdrevision varchar2(255) not null,
        wfdid varchar2(50) not null,
        expression varchar2(255) not null,
        eventcode char(7) not null,
        participantname varchar2(255),
        message varchar2(255) not null,
        primary key (date_, engineid, wfdid, expression)
    );

---- ***** ----

To map authorization service to database, I followed below steps
----------------------------------------------------------------

Made below entry in db-worklist-configuration.xml and
db-engine-configuration.xml and commented already existing
PolicyService, which maps to xml file (passwd.xml)

     <!-- Authentication and authorization service -->
        <service
            name="policyService"
            class="openwfe.org.auth.PolicyService"
        >
            <param>
                <param-name>passwdCodec</param-name>
                <param-value>openwfe.org.auth.sql.SqlPasswdCodec</param-value>
            </param>
            <param>
                <param-name>dataSource</param-name>
                <param-value>dataSource</param-value>
            </param>
            <param>
                <param-name>refreshEachTime</param-name>
                <param-value>true</param-value>
            </param>
        </service>

Modified openwfe.org.sql.SqlParameters
---------------------------------------

As I got below exception I modified openwfe.org.sql.SqlParameters
    -----
    2005-12-14 20:48:26,882 [main] WARN  openwfe.org.sql.SqlParameters - Failed to extract parameters from the database with query
    SELECT pid FROM pparameter WHERE pgroup = 'principal' AND pkey = 'name';
    java.sql.SQLException: ORA-00911: invalid character

            at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
            at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    -----

/*
* Copyright (c) 2005, John Mettraux, OpenWFE.org
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
*
* . Redistributions of source code must retain the above copyright notice, this
*   list of conditions and the following disclaimer. 
*
* . Redistributions in binary form must reproduce the above copyright notice,
*   this list of conditions and the following disclaimer in the documentation
*   and/or other materials provided with the distribution.
*
* . Neither the name of the "OpenWFE" nor the names of its contributors may be
*   used to endorse or promote products derived from this software without
*   specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
* ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE
* LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*
* $Id: SqlParameters.java,v 1.9 2005/06/14 13:17:26 jmettraux Exp $
*/

//
// SqlParameters.java
//
// john.mettraux@openwfe.org
//
// generated with
// jtmpl 1.1.01 2004/05/19 (john.mettraux@openwfe.org)
//

package openwfe.org.sql;

import java.util.Collections;

public class SqlParameters
{

    private final static org.apache.log4j.Logger log = org.apache.log4j.Logger
        .getLogger(SqlParameters.class.getName());

    //
    // CONSTANTS & co

    //
    // FIELDS

    private final String paramTableName;
    private final String idColName;
    private final String groupColName;
    private final String keyColName;
    private final String valueColName;

    //
    // CONSTRUCTORS

    public SqlParameters
        (final String paramTableName,
         final String idColName,
         final String groupColName,
         final String keyColName,
         final String valueColName)
    {
        this.paramTableName = paramTableName;
        this.idColName = idColName;
        this.groupColName = groupColName;
        this.keyColName = keyColName;
        this.valueColName = valueColName;
    }

    //
    // METHODS

    public java.util.List extractParameters
        (final java.sql.Connection con,
         final String group,
         final String key)
    {
        java.util.List result = new java.util.ArrayList();
       
        int id = -1;

        String query = null;
        java.sql.Statement st = null;
        java.sql.ResultSet rs = null;
        try
        {
            //
            // extract id
           
            query = buildQuery(group, key, null);

            st = con.createStatement();
            rs = st.executeQuery(query);

            while (rs.next())
            {
                id = rs.getInt(1);

                //
                // extract parameters

                result.add(extractParameters(con, id));
            }
        }
        catch (java.sql.SQLException se)
        {
            log.warn
                ("Failed to extract parameters from the database with query\n"+
                 query, se);
            return result;
        }
        finally
        {
            SqlUtils.closeStatement(st, rs);
        }

        return result;
    }

    public java.util.Map extractParameters
        (final java.sql.Connection con,
         final String group,
         final String key,
         final String value)
    {
        //
        // extract id
       
        int id = -1;

        String query = null;
        java.sql.Statement st = null;
        java.sql.ResultSet rs = null;
        try
        {
            query = buildQuery(group, key, value);

            st = con.createStatement();
            rs = st.executeQuery(query);

            if ( ! rs.next())
            {
                log.debug("extracParameters() no id found");
                return null;
            }

            id = rs.getInt(1);
        }
        catch (java.sql.SQLException se)
        {
            log.warn
                ("Failed to extract parameters from the database with query\n"+
                 query, se);
        }
        finally
        {
            SqlUtils.closeStatement(st, rs);
        }

        //
        // extract parameters

        return extractParameters(con, id);
    }

    public java.util.Map extractParameters
        (final java.sql.Connection con,
         final int id)
    {
        String query = null;
        java.sql.Statement st = null;
        java.sql.ResultSet rs = null;
        try
        {
            query = buildQuery(id);

            st = con.createStatement();
            rs = st.executeQuery(query);

            java.util.Map result = new java.util.HashMap();

            while (rs.next())
            {
                final String key = rs.getString(1);
                final String value = rs.getString(2);

                result.put(key, value);
            }

            return Collections.unmodifiableMap(result);
        }
        catch (java.sql.SQLException se)
        {
            log.warn
                ("Failed to extract parameters from the database with query\n"+
                 query, se);
            return null;
        }
        finally
        {
            SqlUtils.closeStatement(st, rs);
        }
    }

    /* *
     * removes every parameter from the db table. Use with Caution !!
     *
    public void cleanParamTable ()
    {
        java.sql.Statement st = null;
        try
        {
            st.execute("DELETE FROM "+this.paramTableName);
        }
        catch (java.sql.SQLException se)
        {
            log.warn
                ("Failed to clean param table", se);
        }
        finally
        {
            SqlUtils.closeStatement(st);
        }
    }
     *
     */

    public long insertParams
        (final java.sql.Connection con,
         final String group,
         final java.util.Map params)
    throws
        java.sql.SQLException
    {
        final long id = SqlUtils.getNewInsertId
            (con, this.paramTableName, this.idColName);

        java.util.Iterator it = params.keySet().iterator();
        while (it.hasNext())
        {
            String key = (String)it.next();
            String val = (String)params.get(key);

            insertParam(con, id, group, key, val);
        }

        return id;
    }

    public void insertParam
        (final java.sql.Connection con,
         final long id,
         final String group,
         final String key,
         final String value)
    throws
        java.sql.SQLException
    {
        String sInsert = null;
        java.sql.Statement st = null;
        try
        {
            StringBuffer sb = new StringBuffer();

            sb.append("INSERT INTO ");
            sb.append(this.paramTableName);
            sb.append(" ( ");
            sb.append(this.idColName); sb.append(", ");
            sb.append(this.groupColName); sb.append(", ");
            sb.append(this.keyColName); sb.append(", ");
            sb.append(this.valueColName);
            sb.append(" ) VALUES ( ");
            sb.append(id); sb.append(", ");
            sb.append(SqlUtils.prepareString(group)); sb.append(", ");
            sb.append(SqlUtils.prepareString(key)); sb.append(", ");
            sb.append(SqlUtils.prepareString(value));
            /* To make compatible with Oracle - Starts */
            //sb.append(");");
            sb.append(")");
            /* To make compatible with Oracle - Ends */

            sInsert = sb.toString();

            st = con.createStatement();

            st.execute(sInsert);
        }
        //catch (java.sql.SQLException se)
        //{
        //    log.warn
        //      ("Failed to insert param with \n"+sInsert, se);
        //}
        finally
        {
            SqlUtils.closeStatement(st);
        }
    }

    //
    // PRIVATE METHODS

    /*
     * builds a query for determining the id, given
     * one group name and one key name
     */
    private String buildQuery
        (final String group,
         final String key,
         final String value)
    {
        final StringBuffer sb = new StringBuffer();

        sb.append("SELECT ");
        sb.append(this.idColName);

        sb.append(" FROM ");
        sb.append(this.paramTableName);

        sb.append(" WHERE ");
        sb.append(this.groupColName);
        sb.append(" = ");
        sb.append(SqlUtils.prepareString(group));
        sb.append(" AND ");
        sb.append(this.keyColName);
        sb.append(" = ");
        sb.append(SqlUtils.prepareString(key));

        if (value != null)
        {
            sb.append(" AND ");
            sb.append(this.valueColName);
            sb.append(" = ");
            sb.append(SqlUtils.prepareString(value));
        }
        /* To make compatible with Oracle - Starts */
        //sb.append(";");
        /* To make compatible with Oracle - Ends */
       

        return sb.toString();
    }

    /*
     * builds a query for finding all the records with the same id
     */
    private String buildQuery
        (final int id)
    {
        final StringBuffer sb = new StringBuffer();

        sb.append("SELECT ");
        sb.append(this.keyColName); sb.append(", ");
        sb.append(this.valueColName);

        sb.append(" FROM ");
        sb.append(this.paramTableName);

        sb.append(" WHERE ");
        sb.append(this.idColName);
        sb.append(" = ");
        sb.append(id);
       
        /* To make compatible with Oracle - Starts */
        //sb.append(";");
        /* To make compatible with Oracle - Ends */

        return sb.toString();
    }

    //
    // STATIC METHODS

}

  IT SEEMS TO BE WORKING AS OF NOW

  ----- ***** -----

[chprvkmr]

Hi,

   Now Iam posting some issues in this regard.

   1) It seems all workflow instances details are stored as xml files
      in xlob column of xldocument table. Can it degrade performance?

   2) To make it compatible I modified datatype of 'date_' column in
      owfehistory from 'date' to 'varchar2' (Bcoz, java.sql.Timestamp value is
      stored directly in date_ column, for this oracle throwed 'unmatched literal Exception'.
      Making date_ as varchar is feasible or not?

   3) After mapping openWFE with Oracle, webclient & uman became slow. Any
      steps to be taken for boosting performance?

Thanks in advance,
Praveen Kumar.

       

[John Mettraux]

Hi Praveen,

1). yes of course, storing in a database is always more expensive than storing in a filesystem (with the exception of carefully tuned databases).

2). if it works for you, then, it's perfect (we'll see what Sébastien comes up with)

3). this is related to question 1). but, it shouldn't happen for webclient, you should not feel any impression of slowness.
For the 'uman' stuff, there is a bug in the SqlPasswd that makes it slow.
You should measure this performance loss in some way. For the moment, it seems you just have an 'impression'.

Best regards,

John

[chprvkmr]

Sorry small correction,
Read point 1 as follows

1) It seems all workflow instances details are stored as xml files in xldoc column of xldocument table. Can it degrade performance?

[chprvkmr]

Hi friends,

   Iam getting some errors in this approach. May be I missed out something (in the bove config. steps that I listed out). Its posted in thread, http://sourceforge.net/forum/forum.php?thread_id=1407511\&amp;forum_id=426779

Regards,
Praveen.