Not all variable bind in HAVING sub query

[Ooi Leng Chye <ol...@silicomp.com.my>]

Hi Everyone,

    I have encounter a problem when I write and try to execute a query
such as following. I get a "ORA-01008: not all variables bound" error, I
have tested both 1.0rc2 as well as 1.0rc3.

   Criteria c = new Criteria();
   c.addIn(Shipment.F_AWB, Arrays.asList(awbs));

   Criteria cH = new Criteria();
   cH.addGreaterThan("count(*)", new Integer(1));
		
   ReportQueryByCriteria q =
QueryFactory.newReportQuery(ShipmentImpl.class, c);
   q.setColumns(new String[] {"awbNo"});
   q.addGroupBy(Shipment.F_AWB);
   q.setHavingCriteria(cH);
		
   Criteria sC = new Criteria();
   sC.addIn("awbNo", q);		
   sC.addEqualToField(Shipment.F_AWB, "allPieceInShipment.trackingNo");
   ReportQueryByCriteria sQ =
QueryFactory.newReportQuery(ShipmentImpl.class, sC);
   sQ.setColumns(new String[]
{Shipment.F_SHIPMENT_OID,Shipment.F_DEST_LOCATION_CD,Shipment.F_AWB,"allPieceInShipment.pieceRouteLeg.routeDate","allPieceInShipment.pieceRouteLeg.routeNo"});
		
   Iterator it = pb.getReportQueryIteratorByQuery(sQ);


    I suspect this has to do with the HavingCriteria that I include in the
subquery. After carry out a debugging process, I found that the same piece
of code that I have written will work perfectly fine if I modify the method
  
org.apache.ojb.broker.accesslayer.StatementManager#bindStatementValue(PreparedStatement
stmt, int index, Object value, ClassDescriptor cld, FieldDescriptor fld)
throws SQLException

to the following

private int bindStatementValue(PreparedStatement stmt, int index, Object
value, ClassDescriptor cld, FieldDescriptor fld) throws SQLException
{
	// if value is a subQuery bind it
	if (value instanceof Query)
	{
		Query subQuery = (Query) value;
		if (subQuery.getCriteria() != null && subQuery.getCriteria().isEmpty())
		{
			index = bindStatement(stmt, subQuery.getCriteria(),
cld.getRepository().getDescriptorFor(subQuery.getSearchClass()), index);
		}
	
		// olc - 13 May 2003
		if(subQuery.getHavingCriteria()!=null &&
!subQuery.getHavingCriteria().isEmpty()) {
			index = bindStatement(stmt, subQuery.getHavingCriteria(),
cld.getRepository().getDescriptorFor(subQuery.getSearchClass()), index);
		}
		return index;
		}

		// if query has criteria, bind them
		if (fld != null)
		{
			// BRJ: use field conversions and platform
		        if (value != null)
			{
				m_platform.setObjectForStatement(stmt, index,
fld.getFieldConversion().javaToSql(value), fld.getColumnJdbcType());
			}
			else
			{
				m_platform.setNullForStatement(stmt, index, fld.getColumnJdbcType());
			}
		}
		else
		{
			// BRJ: No FieldDescriptor found
			if (value != null)
			{
				stmt.setObject(index, value);
			}
			else
			{
				stmt.setNull(index, Types.NULL);
			}
		}

		return ++index; // increment before return
	}


    Can anyone please veryfy this? Thank you!


Cheers,
Leng Chye, Ooi
Silicomp Malaysia Sdn Bhd

SELECT 
   A0.SHIPMENT_OID_NBR,
   A0.DEST_LOCATION_CD,
   A0.AWB_NBR 
FROM 
   ENT.SHIPMENT A0
WHERE 
   A0.AWB_NBR IN 
(
SELECT
   A0.AWB_NBR 
FROM
   ENT.SHIPMENT A0 
WHERE 
   A0.AWB_NBR IN ( ? , ? , ? , ? , ? , ? ) 
GROUP BY 
   A0.AWB_NBR HAVING count(*) =  ? 
)

SELECT
   A0.SHIPMENT_OID_NBR,
   A0.DEST_LOCATION_CD,
   A0.AWB_NBR
FROM
   ENT.SHIPMENT A0 
WHERE
   A0.AWB_NBR IN  
(
SELECT
   A0.AWB_NBR 
FROM
   ENT.SHIPMENT A0 
WHERE
   A0.AWB_NBR IN ( '840670042656' , '840669067472' , '840670042645' ,
'840669091249' , '840669091411' , '11123423232' )
GROUP BY
   A0.AWB_NBR 
HAVING count(*) =  null 
) 

[org.apache.ojb.broker.accesslayer.JdbcAccessImpl] ERROR: SQLException
during the execution of the query (for a com.fedex.data.ShipmentImpl):
ORA-01008: not all variables bound

ORA-01008: not all variables bound

java.sql.SQLException: ORA-01008: not all variables bound

	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
	at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
	at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
	at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
	at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831)
	at
oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496)
	at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840)
	at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
	at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536)
	at
com.p6spy.engine.logging.P6LogPreparedStatement.executeQuery(P6LogPreparedStatement.java:171)
	at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(Unknown
Source)
	at org.apache.ojb.broker.accesslayer.RsIterator.<init>(Unknown Source)
	at org.apache.ojb.broker.accesslayer.ReportQueryRsIterator.<init>(Unknown
Source)
	at
org.apache.ojb.broker.core.ReportRsIteratorFactoryImpl.createRsIterator(Unknown
Source)
	at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(Unknown
Source)
	at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorFromQuery(Unknown
Source)
	at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorByQuery(Unknown
Source)
	at
org.apache.ojb.broker.core.DelegatingPersistenceBroker.getReportQueryIteratorByQuery(Unknown
Source)
	at com.fedex.cih.ejb.LookupBean.getSingleShipmentList(LookupBean.java:136)
	at com.fedex.cih.util.TestDuplicatedAWB.main(TestDuplicatedAWB.java:59)
Exception in thread "main" java.sql.SQLException: ORA-01008: not all
variables bound
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
	at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
	at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
	at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
	at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831)
	at
oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496)
	at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840)
	at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
	at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536)
	at
com.p6spy.engine.logging.P6LogPreparedStatement.executeQuery(P6LogPreparedStatement.java:171)
	at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(Unknown
Source)
	at org.apache.ojb.broker.accesslayer.RsIterator.<init>(Unknown Source)
	at org.apache.ojb.broker.accesslayer.ReportQueryRsIterator.<init>(Unknown
Source)
	at
org.apache.ojb.broker.core.ReportRsIteratorFactoryImpl.createRsIterator(Unknown
Source)
	at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(Unknown
Source)
	at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorFromQuery(Unknown
Source)
	at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorByQuery(Unknown
Source)
	at
org.apache.ojb.broker.core.DelegatingPersistenceBroker.getReportQueryIteratorByQuery(Unknown
Source)
	at com.fedex.cih.ejb.LookupBean.getSingleShipmentList(LookupBean.java:136)
	at com.fedex.cih.util.TestDuplicatedAWB.main(TestDuplicatedAWB.java:59)
rethrown as org.apache.ojb.broker.PersistenceBrokerSQLException:
ORA-01008: not all variables bound
	at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(Unknown
Source)
	at org.apache.ojb.broker.accesslayer.RsIterator.<init>(Unknown Source)
	at org.apache.ojb.broker.accesslayer.ReportQueryRsIterator.<init>(Unknown
Source)
	at
org.apache.ojb.broker.core.ReportRsIteratorFactoryImpl.createRsIterator(Unknown
Source)
	at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(Unknown
Source)
	at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorFromQuery(Unknown
Source)
	at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorByQuery(Unknown
Source)
	at
org.apache.ojb.broker.core.DelegatingPersistenceBroker.getReportQueryIteratorByQuery(Unknown
Source)
	at com.fedex.cih.ejb.LookupBean.getSingleShipmentList(LookupBean.java:136)
	at com.fedex.cih.util.TestDuplicatedAWB.main(TestDuplicatedAWB.java:59)
Caused by: java.sql.SQLException: ORA-01008: not all variables bound
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
	at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
	at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
	at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
	at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831)
	at
oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496)
	at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840)
	at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
	at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536)
	at
com.p6spy.engine.logging.P6LogPreparedStatement.executeQuery(P6LogPreparedStatement.java:171)
	... 10 more