Eclipse Community Forums: BIRT » BIRT report w/ parameter fails when ? placed in SQL where clause

Home » Archived » BIRT » BIRT report w/ parameter fails when ? placed in SQL where clause(Eclipse Juno, BIRT 4.2.0.v20120611, Oracle 10.2/11.1 JDBC datasource)

BIRT report w/ parameter fails when ? placed in SQL where clause [message #894878]

Tue, 10 July 2012 17:35

Eclipse User

Hello all!

I'm brand new to BIRT and am creating a simple report with a parameter for the user to enter an "Environment" string, I default the parameter to %. When I set the parameter up, there are no issues, but when I put "LIKE ?" in place of "LIKE '%'" in the where clause of the query, I get an exception when running the report, previewing the report or previewing the results in the dataset:

org.eclipse.birt.data.engine.core.DataException: Failed to prepare the query execution for the data set: t_host
Cannot get the type for parameter: 1.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot get parameter type.
SQL error #1:Unsupported feature
;
java.sql.SQLException: Unsupported feature

at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:345)

at org.eclipse.birt.data.engine.impl.PreparedQuery.doPrepare(PreparedQuery.java:455)

at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.produceQueryResults(PreparedDataSourceQuery.java:190)

at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:178)

at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery.execute(PreparedOdaDSQuery.java:145)

at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:159)

at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:142)

at org.eclipse.birt.report.data.adapter.impl.QueryExecutionHelper.executeQuery(QueryExecutionHelper.java:136)

at org.eclipse.birt.report.data.adapter.impl.QueryExecutionHelper.executeQuery(QueryExecutionHelper.java:118)

at org.eclipse.birt.report.data.adapter.impl.QueryExecutionHelper.executeQuery(QueryExecutionHelper.java:102)

at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getRuntimeMetaData(DataSetMetaDataHelper.java:194)

at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getRealMetaData(DataSetMetaDataHelper.java:153)

at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getDataSetMetaData(DataSetMetaDataHelper.java:115)

at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.refreshMetaData(DataSetMetaDataHelper.java:382)

at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.refreshMetaData(DataRequestSessionImpl.java:415)

at org.eclipse.birt.report.designer.data.ui.dataset.ExternalUIUtil.updateColumnCache(ExternalUIUtil.java:108)

at org.eclipse.birt.report.designer.data.ui.providers.DefaultDataServiceProvider.updateColumnCache(DefaultDataServiceProvider.java:114)

at org.eclipse.birt.report.designer.internal.ui.data.DataService.updateColumnCache(DataService.java:140)

at org.eclipse.birt.report.designer.data.ui.dataset.DataSetUIUtil.updateColumnCache(DataSetUIUtil.java:98)

at org.eclipse.birt.report.designer.data.ui.dataset.DataSetEditor.okPressed(DataSetEditor.java:685)

at org.eclipse.birt.report.designer.data.ui.property.AbstractPropertyDialog.buttonPressed(AbstractPropertyDialog.java:650)

at org.eclipse.jface.dialogs.Dialog$2.widgetSelected(Dialog.java:624)

at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:248)

at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)

at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)

at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4169)

at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3758)

at org.eclipse.jface.window.Window.runEventLoop(Window.java:825)

at org.eclipse.jface.window.Window.open(Window.java:801)

at org.eclipse.birt.report.designer.ui.dialogs.BaseDialog.open(BaseDialog.java:111)

at org.eclipse.birt.report.designer.data.ui.providers.EditableDataSetNodeProvider.performEdit(EditableDataSetNodeProvider.java:102)

at org.eclipse.birt.report.designer.internal.ui.views.DefaultNodeProvider.performRequest(DefaultNodeProvider.java:442)

at org.eclipse.birt.report.designer.internal.ui.views.actions.EditAction.doAction(EditAction.java:90)

at org.eclipse.birt.report.designer.internal.ui.views.actions.AbstractElementAction.run(AbstractElementAction.java:70)

at org.eclipse.birt.report.designer.internal.ui.views.RenameListener.doubleClick(RenameListener.java:214)

at org.eclipse.jface.viewers.StructuredViewer$1.run(StructuredViewer.java:845)

at org.eclipse.core.runtime.SafeRunner.run(SafeRunner.java:42)

at org.eclipse.ui.internal.JFaceUtil$1.run(JFaceUtil.java:49)

at org.eclipse.jface.util.SafeRunnable.run(SafeRunnable.java:175)

at org.eclipse.jface.viewers.StructuredViewer.fireDoubleClick(StructuredViewer.java:843)

at org.eclipse.jface.viewers.AbstractTreeViewer.handleDoubleSelect(AbstractTreeViewer.java:1477)

at org.eclipse.jface.viewers.StructuredViewer$4.widgetDefaultSelected(StructuredViewer.java:1246)

at org.eclipse.jface.util.OpenStrategy.fireDefaultSelectionEvent(OpenStrategy.java:249)

at org.eclipse.jface.util.OpenStrategy.access$0(OpenStrategy.java:246)

at org.eclipse.jface.util.OpenStrategy$1.handleEvent(OpenStrategy.java:307)

at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)

at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)

at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4169)

at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3758)

at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$9.run(PartRenderingEngine.java:1022)

at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)

at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:916)

at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:86)

at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:585)

at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)

at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:540)

at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:149)

at org.eclipse.ui.internal.ide.application.IDEApplication.start(IDEApplication.java:124)

at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)

at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)

at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)

at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:353)

at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:180)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:629)

at org.eclipse.equinox.launcher.Main.basicRun(Main.java:584)

at org.eclipse.equinox.launcher.Main.run(Main.java:1438)

Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot get the type for parameter: 1.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot get parameter type.
SQL error #1:Unsupported feature
;
java.sql.SQLException: Unsupported feature

at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:55)

at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)

at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:96)

at org.eclipse.birt.data.engine.odaconsumer.ParameterMetaData.getRuntimeParameterType(ParameterMetaData.java:237)

at org.eclipse.birt.data.engine.odaconsumer.ParameterMetaData.<init>(ParameterMetaData.java:97)

at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.mergeParamHintsWithMetaData(PreparedStatement.java:1674)

at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getParameterMetaData(PreparedStatement.java:1525)

at org.eclipse.birt.data.engine.executor.DataSourceQuery.setInputParameterBinding(DataSourceQuery.java:1103)

at org.eclipse.birt.data.engine.executor.DataSourceQuery.addParameterDefns(DataSourceQuery.java:574)

at org.eclipse.birt.data.engine.executor.DataSourceQuery.prepare(DataSourceQuery.java:290)

at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.prepareOdiQuery(PreparedOdaDSQuery.java:442)

at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:341)

... 69 more

Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot get parameter type.
SQL error #1:Unsupported feature
;
java.sql.SQLException: Unsupported feature

at org.eclipse.birt.report.data.oda.jdbc.ParameterMetaData.getParameterType(ParameterMetaData.java:161)

at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaParameterMetaData.getParameterType(OdaParameterMetaData.java:177)

at org.eclipse.birt.data.engine.odaconsumer.ParameterMetaData.getRuntimeParameterType(ParameterMetaData.java:233)

... 77 more

Caused by: java.sql.SQLException: Unsupported feature

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)

at oracle.jdbc.driver.DatabaseError.throwUnsupportedFeatureSqlException(DatabaseError.java:499)

at oracle.jdbc.driver.OracleParameterMetaData.getParameterType(OracleParameterMetaData.java:157)

at org.eclipse.birt.report.data.oda.jdbc.ParameterMetaData.getParameterType(ParameterMetaData.java:155)

... 79 more

config info:
eclipse.buildId=I20120608-1400
java.version=1.6.0_18
java.vendor=Sun Microsystems Inc.
BootLoader constants: OS=win32, ARCH=x86, WS=win32, NL=en_US
Framework arguments: -product org.eclipse.epp.package.reporting.product
Command-line arguments: -os win32 -ws win32 -arch x86 -product org.eclipse.epp.package.reporting.product -clean

Similar reports with parameters in our environment work just fine with Eclipse Indigo/BIRT 3.7.1/Oracle 10.2 JDBC, and when I open them in my instance of Eclipse/BIRT, the parameter problem appears.

Incidently, I created a datasource/dataset connected to a MySQL DB with my Eclipse Juno/BIRT 4.2.0/MySQL 5.0 JDBC and parameters worked with no problems.

I'm at a loss to figure out why parameters are not working while connected to an Oracle DB using either the 10.2 or 11.1 JDBC driver.

Thanks,
Brad

Attachment: new_report.rptdesign
(Size: 37.83KB, Downloaded 1213 times)

Re: BIRT report w/ parameter fails when ? placed in SQL where clause [message #894887 is a reply to message #894878]

Tue, 10 July 2012 18:38

Eclipse User

I don't have an Oracle DB to test this on, but one thing you could do, if you can't get any parameters working with Oracle is to build your where clause, using the queryText string, in your beforeOpen script.

Re: BIRT report w/ parameter fails when ? placed in SQL where clause [message #894902 is a reply to message #894887]

Tue, 10 July 2012 22:41

Eclipse User

Michael,

Referencing the parameter within the where clause from this.queryText in the dataset's before open property seems to work with an Oracle 11gR2 database on my home machine. I'll try again tomorrow on my work 11gR1 instance and see if it'll work there.

I'm still curious as to why BIRT 3.7.1 can use parameters and ? in the where clause with Oracle and 4.2.0 cannot. What's different in 4.2.0's interaction with Oracle via JDBC? I'd rather not modify the reports built in 3.7.1 to work seamlessly in 4.2.0, if I can help it.

Thanks,
Brad

Re: BIRT report w/ parameter fails when ? placed in SQL where clause [message #894990 is a reply to message #894902]

Wed, 11 July 2012 06:59

Eclipse User

Hi Brad,

I am happy, that there is somebody else, who has exactly the same issue =).

I created my Reports also with version 3.7. There all worked fine. And with the latest Version nothing works. I get the same Error Message. (Also Oracle DB)

Dnns

Re: BIRT report w/ parameter fails when ? placed in SQL where clause [message #895130 is a reply to message #894990]

Wed, 11 July 2012 14:31

Eclipse User

Brad,

What column type is env_name? And what table did it come out of?

Jason

On 7/11/12 6:59 AM, Dnns . wrote:
> Hi Brad,
>
> I am happy, that there is somebody else, who has exactly the same issue
> =).
>
> I created my Reports also with version 3.7. There all worked fine. And
> with the latest Version nothing works. I get the same Error Message.
> (Also Oracle DB)
>
>
> Dnns

Re: BIRT report w/ parameter fails when ? placed in SQL where clause [message #895145 is a reply to message #895130]

Wed, 11 July 2012 15:39

Eclipse User

Jason,

The env_name field is a VARCHAR2(30) field inside t_cie_env table.

--Brad

Re: BIRT report w/ parameter fails when ? placed in SQL where clause [message #895165 is a reply to message #895145]

Wed, 11 July 2012 16:43

Eclipse User

Brad,

I can not reproduce the issue. It works with sample db. Any chance you
could use the fully qualified column name and see if that works?

Jason

On 7/11/12 3:39 PM, Brad Luce wrote:
> Jason,
>
> The env_name field is a VARCHAR2(30) field inside t_cie_env table.
>
>
> --Brad

Re: BIRT report w/ parameter fails when ? placed in SQL where clause [message #895168 is a reply to message #895165]

Wed, 11 July 2012 17:13

Eclipse User

Jason,

The issue seems to be specific to Oracle datasources. I was able to successfully use a parameter and ? in the where clause when pointing to a MySQL datasource from the same 4.2.0 report. I use the free Oracle Express Edition 11gR2 (11.2.0.2) on Windows 32-bit as a test platform and the error is present with that DB as a datasource. In our real environment, where we have live data, we use Oracle Enterprise Edition 11gR1 (11.1.0.7). That's where I first experienced the problem.

--Brad

Re: BIRT report w/ parameter fails when ? placed in SQL where clause [message #895192 is a reply to message #895168]

Wed, 11 July 2012 19:34

Eclipse User

Brad,

Can you open a bugzilla entry for this and post it back here?

Jason

On 7/11/12 5:13 PM, Brad Luce wrote:
> Jason,
>
> The issue seems to be specific to Oracle datasources. I was able to
> successfully use a parameter and ? in the where clause when pointing to
> a MySQL datasource from the same 4.2.0 report. I use the free Oracle
> Express Edition 11gR2 (11.2.0.2) on Windows 32-bit as a test platform
> and the error is present with that DB as a datasource. In our real
> environment, where we have live data, we use Oracle Enterprise Edition
> 11gR1 (11.1.0.7). That's where I first experienced the problem.
>
> --Brad

Re: BIRT report w/ parameter fails when ? placed in SQL where clause [message #895307 is a reply to message #895192]

Thu, 12 July 2012 08:23

Eclipse User

Jason,

I created a bugzilla submission and its ID is 384925.

Thanks,
Brad

Re: BIRT report w/ parameter fails when ? placed in SQL where clause [message #895402 is a reply to message #895307]

Thu, 12 July 2012 13:24

Eclipse User

Thanks Brad

On 7/12/12 8:23 AM, Brad Luce wrote:
> Jason,
>
> I created a bugzilla submission and its ID is 384925.
>
>
> Thanks,
> Brad

Re: BIRT report w/ parameter fails when ? placed in SQL where clause [message #912268 is a reply to message #894878]

Thu, 13 September 2012 04:22

Eclipse User

I've the same problem.

I'm using Birt 4.2.0 with an Oracle 11 Database.

With Birt 3.7 same reports work fine.

When are they planning to fix it?

[Updated on: Thu, 13 September 2012 04:25] by Moderator

Re: BIRT report w/ parameter fails when ? placed in SQL where clause [message #912519 is a reply to message #912268]

Thu, 13 September 2012 13:45

Eclipse User

It is already fixed in the rc1 build:
http://download.eclipse.org/birt/downloads/build.php?build=M-R1-4.2.1RC1-201208201733

It should be released in a full build by Sept 28.

Jason

Re: BIRT report w/ parameter fails when ? placed in SQL where clause [message #1008721 is a reply to message #912519]

Wed, 13 February 2013 12:53

Eclipse User

I'm having this issue with Oracle 11 and am wondering how to get the fixed version. Can you advise? I believe I have the latest build.
Thanks!

Previous Topic:	Data Source issue which is not picking dynamically
Next Topic:	PDF Page Numbers

Goto Forum:

-=] Back to Top [=-

Current Time: Sun Mar 16 19:42:07 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter