Community
Participate
Working Groups
Build Identifier: 20100617-1415 For example create a new datasource against the sample db. Create a new dataset with the following query: select * from orderdetails where ordernumber = ? This will automatically create a dataset parameter. You can link this dataset parameter to a report parameter by editing the dataset and selecting the parameters tab. In sample db all right, but in the connection with oracle.jdbc does not work. Reproducible: Always Steps to Reproduce: 1.Create new dataSource Ex Driver Class: oracle.jdbc.OracleDriver (v1.0) >> Data Base: Url jdbc:oracle:thin:@192.168.112.5:1521:ORCL. 2.Create new dataSet: Query text . Select * from pais where cdpais = ?. 3.Click finish. 4.Check if the parameters were created.
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
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
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.
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.
