[jira] [Commented] (OPENJPA-2131) Missing IN or OUT parameter exception with OracleDictionary

["Zilin Chen (Commented) (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/OPENJPA-2131?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13208795#comment-13208795 ] 

Zilin Chen commented on OPENJPA-2131:
-------------------------------------

Here is code snapshot of our work around

1, in the method below of org.apache.openjpa.jdbc.sql.OracleDictionary.java  (branch 2.1.x)

protected SQLBuffer getSelects(Select sel, boolean distinctIdentifiers,
        boolean forUpdate) { 
....
            if (alias instanceof SQLBuffer){
                asString = ((SQLBuffer) alias).getSQL();   
                selectSQL.appendParamOnly((SQLBuffer) alias); //add line to set parameters
            }else{
                asString = alias.toString();
            }


2, org.apache.openjpa.jdbc.sql.SQLBuffer.java  (branch 2.1.x)

>From line 110 - 136
 add a work around method appendParamOnly() and overload method append() to call updated append(SQLBuffer buf, int sqlIndex, int paramIndex,
        boolean subsels, boolean paramOnly)  which will append _sql based on passin boolean paramOnly.
  
    /** 
     * Append parameters only of the given buffer at the given positions.
     */
    public SQLBuffer appendParamOnly(SQLBuffer buf) {
    	append(buf, _sql.length(), (_params == null) ? 0 : _params.size(),
                true, true);
        return this;
    }
    
    /**
     * Append all SQL and parameters of the given buffer at the given positions.
     */
    private void append(SQLBuffer buf, int sqlIndex, int paramIndex,
        boolean subsels) {
    	append(buf, sqlIndex, paramIndex, subsels, false);
    }

    /**
     * Append parameters and/or SQL of the given buffer at the given positions.
     */
    private void append(SQLBuffer buf, int sqlIndex, int paramIndex,
        boolean subsels, boolean paramOnly) {
        if (subsels) {
            // only allow appending of buffers with subselects, not insertion
            if (_subsels != null && !_subsels.isEmpty()
                && sqlIndex != _sql.length())
                throw new IllegalStateException();
            if (buf._subsels != null && !buf._subsels.isEmpty()) {
                if (sqlIndex != _sql.length())
                    throw new IllegalStateException();
                if (_subsels == null)
                    _subsels = new ArrayList(buf._subsels.size());
                for (int i = 0; i < buf._subsels.size(); i++)
                    _subsels.add(((Subselect) buf._subsels.get(i)).
                        clone(sqlIndex, paramIndex));
            }
        }
        if(!paramOnly){
	        if (sqlIndex == _sql.length())
	            _sql.append(buf._sql.toString());
	        else
	            _sql.insert(sqlIndex, buf._sql.toString());
        }
.....
                
> Missing IN or OUT parameter exception with OracleDictionary
> -----------------------------------------------------------
>
>                 Key: OPENJPA-2131
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2131
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc
>    Affects Versions: 2.1.1
>         Environment: Windows with Oracle 11g
>            Reporter: Zilin Chen
>
> We have generate query with 
> Query q = em.createQuery("select e._name, e._id, sum(case when e._status=:pending then e._count else 0 end) from Exceptions e, Historty h where e._guid = :guid and .....
> then set paramenters for both :pending (in select) and :guid (in where), and call q.setMaxResults(100);
> with DB2, everything works fine, and DBDictionary.getSelects() and SQLBuffer.append() will invoke correctly and set parameter of :pending
> with Oracle implementation, OracleDictionary.getSelects(Select sel, boolean distinctIdentifiers, boolean forUpdate) will invoke and SQLBuffer.getSQL() get to call (as two if checks are not satisfied and gos to create a new SQLBuffer instance), but this time :pending parameter will missing to add to _param List,
> Eventually, we'll get Missing IN or OUT parameter exception from to execute query by preparedStatement 
> This bug exist in both this branch and trunk.
> We have a work around, after SQLBuffer.getSQL() call, add some code to reset _param list.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira