Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » 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 21:35 Go to next message
Brad Luce is currently offline Brad Luce
Messages: 5
Registered: July 2012
Junior Member
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
Re: BIRT report w/ parameter fails when ? placed in SQL where clause [message #894887 is a reply to message #894878] Tue, 10 July 2012 22:38 Go to previous messageGo to next message
Michael Williams is currently offline Michael Williams
Messages: 1639
Registered: July 2009
Senior Member

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.

Michael

Add interactivity, scheduling, sharing, live Excel export, and JS integration to your BIRT app with the new, free BIRT server, BIRT iHub F-Type.
Re: BIRT report w/ parameter fails when ? placed in SQL where clause [message #894902 is a reply to message #894887] Wed, 11 July 2012 02:41 Go to previous messageGo to next message
Brad Luce is currently offline Brad Luce
Messages: 5
Registered: July 2012
Junior Member
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 10:59 Go to previous messageGo to next message
Dnns . is currently offline Dnns .
Messages: 30
Registered: May 2012
Member
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 18:31 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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 19:39 Go to previous messageGo to next message
Brad Luce is currently offline Brad Luce
Messages: 5
Registered: July 2012
Junior Member
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 20:43 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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 21:13 Go to previous messageGo to next message
Brad Luce is currently offline Brad Luce
Messages: 5
Registered: July 2012
Junior Member
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 23:34 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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 12:23 Go to previous messageGo to next message
Brad Luce is currently offline Brad Luce
Messages: 5
Registered: July 2012
Junior Member
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 17:24 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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 08:22 Go to previous messageGo to next message
Blaine Mising name is currently offline Blaine Mising name
Messages: 25
Registered: December 2010
Junior Member
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 08:25]

Report message to a 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 17:45 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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 17:53 Go to previous message
Steve Yates is currently offline Steve Yates
Messages: 1
Registered: February 2013
Junior Member
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:
  


Current Time: Wed Oct 22 15:35:23 GMT 2014

Powered by FUDForum. Page generated in 0.02938 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software