MyEclipse :: View topic - Select stored procedure option not showing in Data Set Type

MyEclipse: Select stored procedure option not showing in Data Set Type

sbergsveinsson - Oct 06, 2009 - 06:22 PM
Post subject: Select stored procedure option not showing in Data Set Type

Hi,
I have an Oracle 10g Database and I am trying to call the stored procedure with MyEclipse Reports, unfortunately it doesn't give me the option to select stored procedure in Data Set Type, since the only option that I have is SQL Select Query. How can I call the stored procedure, if I don't have the option in Data Set Type?
Any help would be much appreciated.
Thank you,
Sofia

support-nipun - Oct 07, 2009 - 06:17 PM
Post subject: RE: Select stored procedure option not showing in Data Set T

Sofia,
Can you go to MyEclipse > Installation Summary > Installation Details and paste them here for me. I will get some one from the dev team to look into this issue.

BTW I hope these are the steps you followed:-
http://www.birt-exchange.org/devshare/designing-birt-reports/447-using-stored-procedures-with-birt/#description

sbergsveinsson - Oct 07, 2009 - 06:24 PM
Post subject:

Hello,

Below are the installation details. I followed the steps in the link below and it didn't work.

*** Date:
Wednesday, October 7, 2009 1:20:34 PM EDT

** System properties:
OS=WindowsXP
OS version=5.1.0
Java version=1.6.0_13

*** MyEclipse details:
MyEclipse Enterprise Workbench
Version: 8.0M1
Build id: 8.0M1-20090924

*** Eclipse details:
MyEclipse Enterprise Workbench

Version: 8.0M1
Build id: 8.0M1-20090924

Eclipse startup command=-os
win32
-ws
win32
-arch
x86
-showsplash
-launcher
C:\Program Files\Genuitec\MyEclipse 8.x Latest\myeclipse.exe
-name
Myeclipse
--launcher.library
C:\Program Files\Genuitec\MyEclipse 8.x Latest\../Common/plugins/org.eclipse.equinox.launcher.win32.win32.x86_1.0.200.v20090519\eclipse_1206.dll
-startup
C:\Program Files\Genuitec\MyEclipse 8.x Latest\../Common/plugins/org.eclipse.equinox.launcher_1.0.200.v20090520.jar
-clean
-install
C:/Program Files/Genuitec/MyEclipse 8.x Latest
-configuration
configuration
-clean
-vm
C:/Program Files/Genuitec/Common/binary/com.sun.java.jdk.win32.x86_1.6.0.013/jre/bin/client/jvm.dll

Thanks,
Sofia

sbergsveinsson - Oct 08, 2009 - 04:34 PM
Post subject:

Any news to the above issue?

Thanks,
Sofia

support-nipun - Oct 08, 2009 - 08:32 PM
Post subject:

Sofia,
Im afraid the person who would be looking into this issue is on leave today. I will get him to reply to your post first thing tomorrow.

sbergsveinsson - Oct 12, 2009 - 06:59 PM
Post subject:

Hi Nipun,

Do you have any news about the issue?
I need to finish my project as soon as possible.
Thanks,
Sofia

Support-Brian - Oct 12, 2009 - 09:26 PM
Post subject:

Sofia,

Sorry for the delayed response.
I'm afraid that for this functionality, a special data set type - "The Stored Procedure Query" type is required, which is not currently support by the MyEclipse Data Source. I cannot recommend any workarounds right now, currently MyEclipse Reports will only support standard select JDBC.

I have filed a feature request to add support for this query type, but cannot commit to an availability date.

Please let us know if you have further queries or need assistance, sorry for the inconvenience caused.

sbergsveinsson - Oct 13, 2009 - 04:30 PM
Post subject:

Hi Brian,

Do you know if I can use another reporting tool that will work with oracle stored procedures? I know that Jasper doesn't support it either.
Do you know If I can use microsoft reporting services instead of birt and send the parameters from my jboss-seam application?

Thanks,
Sofia

sbergsveinsson - Oct 19, 2009 - 03:39 PM
Post subject:

Brian,

I managed to pull the data from the oracle stored procedure in the birt report by creating a stored function to retrieve the results of the stored procedure.
I have a question, how do you send the parameters and call the birt report from the jboss-seam application? Do you have an example?

Thanks,
Sofia

support-nipun - Oct 20, 2009 - 09:10 PM
Post subject:

Sofia,
We're consulting with someone with more experience in reporting to get an answer. We will get back to you with a reply asap.

Support-Brian - Oct 21, 2009 - 12:29 AM
Post subject:

Sofia,

Here are some documents that should help you get going from Seam.

Slides from JBossWorld
http://www.birt-exchange.org/devshare/deploying-birt-reports/1008-getting-birt-y-with-jboss/#description

BIRT Integration introduced
http://docs.jboss.org/tools/whatsnew/birt/birt-news-1.0.0.Alpha1.html

Reference Guide
http://docs.jboss.org/tools/3.0.1.GA/en/jboss_birt_plugin_ref_guide/pdf/Birt_Reference_Guide.pdf

You may also want to look at (older integration):
http://wiki.eclipse.org/Seam_%28BIRT%29_Recipe

Please let us know if you need further assistance, I hope this helps.

jbain - Feb 11, 2011 - 12:07 AM
Post subject:

Is there any timeframe of getting the Stored Procedure Query option in the Data Set Types back?
I know the option is there in Eclipse but not in MyEclipse.

jbain - Feb 11, 2011 - 04:55 PM
Post subject:

I am new to MyEclipse and have done my research looking for any means to call a stored procedue in Oracle.

I am currently using MyEclipse 8.6.1 and have even downloaded and configured MyEclipse 9.0 M2 to see if the functionality allowing me to choose the SQL Stored Procedure Query as an elibile data set type but it does not.

In my research I have downloaded Eclipse 3.3.2 with the BIRT 2.2.2 plugin the option to choose SQL Stored Procedure Query is there and it woks great. However, we are not using that tool.

Why would this functionality be there in an old version of Eclipse and when I am using MyEcllipse 9.0 M2 with an Eclipse Engine 3.6.1 the functionality is not there.

I have tried to get around this by configuring the Oracle OCI driver in MyEclipse (thinking that it would run native Oracle SQL server side) and it configured successfully but the Data Set functionality will still not allow me to call the function I created even within a SQL statement. It is as if it can not see the function.

If you can direct me in any way I would greatly appreciate it.

Take care,

John

jbain - Feb 11, 2011 - 04:56 PM
Post subject:

jbain - Feb 11, 2011 - 04:59 PM
Post subject:

jbain - Feb 11, 2011 - 05:00 PM
Post subject:

support-scott - Feb 16, 2011 - 08:57 PM
Post subject:

John,

I wanted to reply just so you knew you weren't being ignored. It's just taking a little time for some infrastructure setup with Oracle and all to be able for someone to dig into your question well. But, we are working on it. What we're going to investigate is that when creating a new Reports query - MyEclipse has a custom Data Source based on ME drivers (MyEclipse JDBC Data Source). It is likely that with this data source, the data set functionality you are looking for is not available but if you change the data source type to the DTP JDBC data source then it will be.

We'll continue to verify that but since you already have a setup working I wanted to let you know our thoughts in case you wanted to check into that yourself as well.

sofistek - Feb 18, 2011 - 09:13 AM
Post subject: Some Observations

As I have Oracle installed and both MyEclipse and standard eclipse installations, I thought I'd check this out, though I'm not very familiar with either BIRT or MyEclipse Reports.

I can confirm that the functionality is there with BIRT, using BIRT's JDBC Data Source but not with MyEclipse reports, using the MyEclipse JDBC Data Source. I couldn't get the OCI driver to work in MyEclipse (though I could in SQL Developer) so I couldn't confirm that the functionality wasn't there with OCI.

I noted this from the MyEclipse help (MyEclipse Learning Center > Web Development (AJAX, JSF, Struts, JSP, etc.) > MyEclipse Reports (Based on Eclipse BIRT) > Overview):

Quote:

MyEclipse Database Explorer has long provided a superset of the Eclipse DTP project features. Therefore MyEclipse does not directly support DTP functionality. Because of this BIRT DTP JDBC datasources is not supported MyEclipse Reports.

So, it may be that this particular functionality has not been carried across from the BIRT JDBC Data Source (which is the DTP ODA JDBC Data Source). This will be confusing since elsewhere in the help (MyEclipse Learning Center > Web Development (AJAX, JSF, Struts, JSP, etc.) > MyEclipse Reports (Based on Eclipse BIRT) > Getting Started > Retrieving Data > About data sets), it does mention how to use stored procedures. However, this section is largely pulled directly from the BIRT help screens, and may not have yet been finely edited to account for all differences.

I don't know if this helps except to confirm that the functionality is currently absent from the MyEclipse JDBC Data Source.

I haven't used stored procedures much; can they be executed from SQL to return a results set? If so, maybe you could try that route.

sofistek - Feb 19, 2011 - 10:58 AM
Post subject: More Digging

I did some more digging, which shows how much spare time I have! :-)

I tried using the other JDBC data source (JDBC Database Connection for Query Builder [prototype]). This was available for both BIRT and MyEclipse. However, this didn't offer the option of selecting an SQL Stored Procedure Query data set type in ether BIRT or MyEclipse.

Next, I tried the Classic Models Inc. Sample Database data source type. It was interesting. Both BIRT and MyEclipse did offer the stored procedure data set type with this data source, although it was useless as the data source was only connecting to a sample database. However, there were some differences related to the data source. In BIRT, it is possible to edit the data source after creating it. This actually allowed me to point to my database, instead of the sample database. In MyEclipse, however, I couldn't edit the data source as the edit window was corrupt and produced errors. There were not the same categories to select, as with BIRT; BIRT offered the BIRT JDBC Data Source, Connection Profile and Property Binding categories, whilst MyEclipse didn't offer the Connection Profile category and gave a blank details panel for the BIRT JDBC Data Source. Also, MyEclipse produced errors both when the Edit Data Source screen came up ("Problems occurred when invoking code" for the org.eclipse.jface plugin), and selecting Property Binding then pressing OK gave another error ("Unhandled event loop exception" in org.eclipse.ui), whilst cancelling out of the edit window gave a Problem Occured dialog.

I then noticed that the New Data Source dialog had this prompt text: "Provide all the settings for a new data source, or choose a pre-defined connection profile." which led me to think that something was missing from that dialog, since I could only choose from a list of types. When I looked at BIRT, that same dialog has an additional area below the prompt for selecting either a data source type, then entering the settings, or selecting a predefined connection profile. In BIRT, this latter option allows me to use the Sample Database profile but to modify it completely, using the BIRT JDBC data source. If this worked in MyEclipse, you might be able to select a stored procedure query when creating a data set.

It looks like the ability to select the SQL Stored Procedure Query functionality has been in BIRT since August 2005, so I would have expected the MyEclipse integration to include it. Looking further up this thread, I notice that the functionality was promised for some future version of the workbench but with no commitment given. So I thought I'd see if version 9M2 might have some improvement, since integration of BIRT 2.6 appears to be on the improvements list. However, even I don't have time to do that now although I intend to install 9M2 soon, anyway, and will look at it then.

sofistek - Feb 20, 2011 - 05:38 AM
Post subject: RE: More Digging

Just installed the MyEclipse development release. It still has the same errors with the New Data Source dialog (missing options) and the edit dialog when editing a <i>Classic Models</i> data source. There appears to be no way to switch to a BIRT JDBC connection.

support-scott - Feb 21, 2011 - 03:22 PM
Post subject: RE: More Digging

All,

What great interaction. I wanted to let everyone know that we verified John and sofistek's findings and have found that the limitation is caused by two bugs that we have filed and will target to fix for the MyEclipse 9.0 GA release. Thanks for the feedback so we could find this and get it fixed.