Bug 351693

Comment 1 Xiaoxiao Wu 2011-07-26 04:56:32 EDT

Hi Fernando,

The issue you mentioned, I suggest, might not be a bug. Actually we'll automatically create a data set parameter only when we can get the correct ParameterMetaData information from the driver. The oracle driver, however, does not return the expected ParameterMetaData to BIRT, which causes the failure of the automatical creation of the corresponding data set parameter. 

As for sample db, since it uses the derby driver which does tell BIRT the correct ParameterMetaData according to the query text, so it works well.

And this issue does not exist in most of other drivers such as DB2 driver. So it is not a bug of BIRT and I suggest  you manually create the corresponding parameter.

Thanks,
Xiaoxiao

Comment 2 Xiaoxiao Wu 2011-07-27 03:14:27 EDT

Since it is not a bug of BIRT, I'm about to close it now. Please feel free to add comments or reopen the bug if you have any confusion.

Thanks,
Xiaoxiao

Comment 3 john mcteague 2011-08-18 04:34:50 EDT

While this may not be BIRT's fault, it has significant knock on effects for running reports against oracle's db.

If I have a report with an optional date parameter which I wish to leave blank, because we cannot obtain the ParameterMetaData from the statement as we cannot determine what type to use when calling statement.setNull() (see org.eclipse.birt.report.data.oda.jdbc.Statement.setNull(int) ).

This method assumes that if the parametermetadata is null we can use the OTHER type, but with oracle (as of least 11.2 jdbc) parametermetadata is not null BUT getParameterType throws an UnsupportedOperation exception, meaning we cannot set a null date.

Comment 4 john mcteague 2011-08-18 04:51:59 EDT

it's worth noting that I have attempted a patch but get stuck at continual oracle issues:

public void setNull( int parameterId ) throws OdaException
{
	assertNotNull( preStat );
	try
	{
		java.sql.ParameterMetaData pm = this.preStat.getParameterMetaData( );
		if ( pm == null )
		{
			this.preStat.setNull( parameterId, java.sql.Types.OTHER );
			addLog( "setNull", parameterId, "null" );
		}
		else
		{
			try{
				this.preStat.setNull( parameterId,
						pm.getParameterType( parameterId ) );
			} catch(SQLException sqle){
				this.preStat.setNull( parameterId, java.sql.Types.OTHER );
				addLog( "setNull", parameterId, "null" );
			}
		}
	}
	catch ( SQLException e )
	{
		throw new JDBCException( ResourceConstants.PREPARESTATEMENT_CANNOT_SET_NULL_VALUE,
				e );
	}
}

Unfortunately using the OTHER type when setting null on oracle also leads to:
java.sql.SQLException: Invalid column type: 1111

The Statement object needs to be aware of the types of parameters the user defined so when the driver cannot figure out which datatype to use when setting null, it can use the dataset parameters to guess at the type.

For my short term issue I patched the Statement class to do the above but instead of using OTHER, I used VARCHAR, which works for Oracle but I have no guarantees it would work for other database types.