Home » Archived » BIRT » How to script a Stored Procedure on beforeOpen
|
| Re: How to script a Stored Procedure on beforeOpen [message #883126 is a reply to message #882951] |
Thu, 07 June 2012 21:58   |
|
Can you try creating both datasets and then drag the first one
(userclient one) onto the canvas, then nest the second dataset/table
into the first one. This should cause your first dataset to be called
and then your second one.
Jason
On 6/7/2012 10:10 AM, Daniel C wrote:
> Hey guys,
> I need to be able to execute a stored procedure to access user@client in
> an oracle database, as follows -
>
> {call SI0WPR_SET_IDENTIFIER (?,?,?)}
> where it takes the following parameters - user, client and desc_client
> (output).
>
> database procedure -
> PROCEDURE "PE0WPR_SET_IDENTIFIER" ( in_userId varchar2 ,in_client
> varchar2 ,description out varchar2 ) AS
>
> BEGIN
>
> dbms_session.set_identifier(in_userId||'@'||in_client);
> select distinct desc_client into description from sipe0wclienti where
> cod_client=in_client;
>
>
> END PE0WPR_SET_IDENTIFIER;
> The problem is that I need to execute this SP before a dataset as the
> dataset calls a parameter that needs the SP to gain user access.
> Is there any way to script the SP and initialise the report with the
> user access already granted to the database, or before the dataset?
>
> Any advice will be much appreciated
>
> Thanks
|
|
|
| Re: How to script a Stored Procedure on beforeOpen [message #883127 is a reply to message #883126] |
Thu, 07 June 2012 21:59 |
|
this is an older video and uses normal datasets, but it should give you
the idea.
http://www.eclipse.org/birt/phoenix/examples/reports/birt2.1/subreport/index.php
Jason
On 6/7/2012 5:58 PM, Jason Weathersby wrote:
>
> Can you try creating both datasets and then drag the first one
> (userclient one) onto the canvas, then nest the second dataset/table
> into the first one. This should cause your first dataset to be called
> and then your second one.
>
> Jason
>
> On 6/7/2012 10:10 AM, Daniel C wrote:
>> Hey guys,
>> I need to be able to execute a stored procedure to access user@client in
>> an oracle database, as follows -
>>
>> {call SI0WPR_SET_IDENTIFIER (?,?,?)}
>> where it takes the following parameters - user, client and desc_client
>> (output).
>>
>> database procedure -
>> PROCEDURE "PE0WPR_SET_IDENTIFIER" ( in_userId varchar2 ,in_client
>> varchar2 ,description out varchar2 ) AS
>>
>> BEGIN
>>
>> dbms_session.set_identifier(in_userId||'@'||in_client);
>> select distinct desc_client into description from sipe0wclienti where
>> cod_client=in_client;
>>
>>
>> END PE0WPR_SET_IDENTIFIER;
>> The problem is that I need to execute this SP before a dataset as the
>> dataset calls a parameter that needs the SP to gain user access.
>> Is there any way to script the SP and initialise the report with the
>> user access already granted to the database, or before the dataset?
>>
>> Any advice will be much appreciated
>>
>> Thanks
>
|
|
| |
| Re: How to script a Stored Procedure on beforeOpen [message #883504 is a reply to message #883318] |
Fri, 08 June 2012 17:18 |
|
Daniel
I think for the table to show up when dragging, the sp must return a row
set. Another option may be to use the data engine api in script to call
the stored proc. Take a look at the attached example. You will not be
able to run it but you can look at the script in the beforeFactory.
Jason
On 6/8/2012 5:34 AM, Daniel C wrote:
> Hi jason,
> Thanks for your quick reply.
>
> Please excuse my ignorance but i'm new to Birt so i'm still trying to figure things out :).
>
> I tried to follow the method of doing the inner table parameter bind but eclipse did not allow me to drag the SQL Stored Procedure dataset (SP) into the canvas (nor the table), so i binded a table dataset to the SP and inserted the second table inside this table linking the parameters. This method still does not display the dynamic list of data rows (parameter) i need.
> From what i understand the parameter report executes the parameters before the report content, therefore needing asking the parameter to retrieve data before recognising the nested table. I would of thought scripting the SP on beforeOpen before the report parameter would gain it access but your method seems much more logical, but i can't get it to work.
>
> I'll show you an example maybe to explain myself better. In this example paramInterviews should return a dynamic list of interviews that needs user@client access (the SP), how can i nest the SP outside the table/parameter to enable the parameter to work?
>
> Thanks again for your help
>
|
|
|
| Re: How to script a Stored Procedure on beforeOpen [message #883507 is a reply to message #883318] |
Fri, 08 June 2012 17:18 |
|
Daniel
I think for the table to show up when dragging, the sp must return a row
set. Another option may be to use the data engine api in script to call
the stored proc. Take a look at the attached example. You will not be
able to run it but you can look at the script in the beforeFactory.
Jason
On 6/8/2012 5:34 AM, Daniel C wrote:
> Hi jason,
> Thanks for your quick reply.
>
> Please excuse my ignorance but i'm new to Birt so i'm still trying to figure things out :).
>
> I tried to follow the method of doing the inner table parameter bind but eclipse did not allow me to drag the SQL Stored Procedure dataset (SP) into the canvas (nor the table), so i binded a table dataset to the SP and inserted the second table inside this table linking the parameters. This method still does not display the dynamic list of data rows (parameter) i need.
> From what i understand the parameter report executes the parameters before the report content, therefore needing asking the parameter to retrieve data before recognising the nested table. I would of thought scripting the SP on beforeOpen before the report parameter would gain it access but your method seems much more logical, but i can't get it to work.
>
> I'll show you an example maybe to explain myself better. In this example paramInterviews should return a dynamic list of interviews that needs user@client access (the SP), how can i nest the SP outside the table/parameter to enable the parameter to work?
>
> Thanks again for your help
>
|
|
|
| Re: How to script a Stored Procedure on beforeOpen [message #883509 is a reply to message #883318] |
Fri, 08 June 2012 17:18 |
|
Daniel
I think for the table to show up when dragging, the sp must return a row
set. Another option may be to use the data engine api in script to call
the stored proc. Take a look at the attached example. You will not be
able to run it but you can look at the script in the beforeFactory.
Jason
On 6/8/2012 5:34 AM, Daniel C wrote:
> Hi jason,
> Thanks for your quick reply.
>
> Please excuse my ignorance but i'm new to Birt so i'm still trying to figure things out :).
>
> I tried to follow the method of doing the inner table parameter bind but eclipse did not allow me to drag the SQL Stored Procedure dataset (SP) into the canvas (nor the table), so i binded a table dataset to the SP and inserted the second table inside this table linking the parameters. This method still does not display the dynamic list of data rows (parameter) i need.
> From what i understand the parameter report executes the parameters before the report content, therefore needing asking the parameter to retrieve data before recognising the nested table. I would of thought scripting the SP on beforeOpen before the report parameter would gain it access but your method seems much more logical, but i can't get it to work.
>
> I'll show you an example maybe to explain myself better. In this example paramInterviews should return a dynamic list of interviews that needs user@client access (the SP), how can i nest the SP outside the table/parameter to enable the parameter to work?
>
> Thanks again for your help
>
|
|
|
| Re: How to script a Stored Procedure on beforeOpen [message #883511 is a reply to message #883318] |
Fri, 08 June 2012 17:18 |
|
Daniel
I think for the table to show up when dragging, the sp must return a row
set. Another option may be to use the data engine api in script to call
the stored proc. Take a look at the attached example. You will not be
able to run it but you can look at the script in the beforeFactory.
Jason
On 6/8/2012 5:34 AM, Daniel C wrote:
> Hi jason,
> Thanks for your quick reply.
>
> Please excuse my ignorance but i'm new to Birt so i'm still trying to figure things out :).
>
> I tried to follow the method of doing the inner table parameter bind but eclipse did not allow me to drag the SQL Stored Procedure dataset (SP) into the canvas (nor the table), so i binded a table dataset to the SP and inserted the second table inside this table linking the parameters. This method still does not display the dynamic list of data rows (parameter) i need.
> From what i understand the parameter report executes the parameters before the report content, therefore needing asking the parameter to retrieve data before recognising the nested table. I would of thought scripting the SP on beforeOpen before the report parameter would gain it access but your method seems much more logical, but i can't get it to work.
>
> I'll show you an example maybe to explain myself better. In this example paramInterviews should return a dynamic list of interviews that needs user@client access (the SP), how can i nest the SP outside the table/parameter to enable the parameter to work?
>
> Thanks again for your help
>
|
|
|
| Re: How to script a Stored Procedure on beforeOpen [message #883514 is a reply to message #883318] |
Fri, 08 June 2012 17:18 |
|
Daniel
I think for the table to show up when dragging, the sp must return a row
set. Another option may be to use the data engine api in script to call
the stored proc. Take a look at the attached example. You will not be
able to run it but you can look at the script in the beforeFactory.
Jason
On 6/8/2012 5:34 AM, Daniel C wrote:
> Hi jason,
> Thanks for your quick reply.
>
> Please excuse my ignorance but i'm new to Birt so i'm still trying to figure things out :).
>
> I tried to follow the method of doing the inner table parameter bind but eclipse did not allow me to drag the SQL Stored Procedure dataset (SP) into the canvas (nor the table), so i binded a table dataset to the SP and inserted the second table inside this table linking the parameters. This method still does not display the dynamic list of data rows (parameter) i need.
> From what i understand the parameter report executes the parameters before the report content, therefore needing asking the parameter to retrieve data before recognising the nested table. I would of thought scripting the SP on beforeOpen before the report parameter would gain it access but your method seems much more logical, but i can't get it to work.
>
> I'll show you an example maybe to explain myself better. In this example paramInterviews should return a dynamic list of interviews that needs user@client access (the SP), how can i nest the SP outside the table/parameter to enable the parameter to work?
>
> Thanks again for your help
>
|
|
|
| Re: How to script a Stored Procedure on beforeOpen [message #883519 is a reply to message #883318] |
Fri, 08 June 2012 17:18 |
|
Daniel
I think for the table to show up when dragging, the sp must return a row
set. Another option may be to use the data engine api in script to call
the stored proc. Take a look at the attached example. You will not be
able to run it but you can look at the script in the beforeFactory.
Jason
On 6/8/2012 5:34 AM, Daniel C wrote:
> Hi jason,
> Thanks for your quick reply.
>
> Please excuse my ignorance but i'm new to Birt so i'm still trying to figure things out :).
>
> I tried to follow the method of doing the inner table parameter bind but eclipse did not allow me to drag the SQL Stored Procedure dataset (SP) into the canvas (nor the table), so i binded a table dataset to the SP and inserted the second table inside this table linking the parameters. This method still does not display the dynamic list of data rows (parameter) i need.
> From what i understand the parameter report executes the parameters before the report content, therefore needing asking the parameter to retrieve data before recognising the nested table. I would of thought scripting the SP on beforeOpen before the report parameter would gain it access but your method seems much more logical, but i can't get it to work.
>
> I'll show you an example maybe to explain myself better. In this example paramInterviews should return a dynamic list of interviews that needs user@client access (the SP), how can i nest the SP outside the table/parameter to enable the parameter to work?
>
> Thanks again for your help
>
|
|
|
| Re: How to script a Stored Procedure on beforeOpen [message #883521 is a reply to message #883318] |
Fri, 08 June 2012 17:18 |
|
Daniel
I think for the table to show up when dragging, the sp must return a row
set. Another option may be to use the data engine api in script to call
the stored proc. Take a look at the attached example. You will not be
able to run it but you can look at the script in the beforeFactory.
Jason
On 6/8/2012 5:34 AM, Daniel C wrote:
> Hi jason,
> Thanks for your quick reply.
>
> Please excuse my ignorance but i'm new to Birt so i'm still trying to figure things out :).
>
> I tried to follow the method of doing the inner table parameter bind but eclipse did not allow me to drag the SQL Stored Procedure dataset (SP) into the canvas (nor the table), so i binded a table dataset to the SP and inserted the second table inside this table linking the parameters. This method still does not display the dynamic list of data rows (parameter) i need.
> From what i understand the parameter report executes the parameters before the report content, therefore needing asking the parameter to retrieve data before recognising the nested table. I would of thought scripting the SP on beforeOpen before the report parameter would gain it access but your method seems much more logical, but i can't get it to work.
>
> I'll show you an example maybe to explain myself better. In this example paramInterviews should return a dynamic list of interviews that needs user@client access (the SP), how can i nest the SP outside the table/parameter to enable the parameter to work?
>
> Thanks again for your help
>
|
|
|
| Re: How to script a Stored Procedure on beforeOpen [message #883524 is a reply to message #883318] |
Fri, 08 June 2012 17:18 |
|
Daniel
I think for the table to show up when dragging, the sp must return a row
set. Another option may be to use the data engine api in script to call
the stored proc. Take a look at the attached example. You will not be
able to run it but you can look at the script in the beforeFactory.
Jason
On 6/8/2012 5:34 AM, Daniel C wrote:
> Hi jason,
> Thanks for your quick reply.
>
> Please excuse my ignorance but i'm new to Birt so i'm still trying to figure things out :).
>
> I tried to follow the method of doing the inner table parameter bind but eclipse did not allow me to drag the SQL Stored Procedure dataset (SP) into the canvas (nor the table), so i binded a table dataset to the SP and inserted the second table inside this table linking the parameters. This method still does not display the dynamic list of data rows (parameter) i need.
> From what i understand the parameter report executes the parameters before the report content, therefore needing asking the parameter to retrieve data before recognising the nested table. I would of thought scripting the SP on beforeOpen before the report parameter would gain it access but your method seems much more logical, but i can't get it to work.
>
> I'll show you an example maybe to explain myself better. In this example paramInterviews should return a dynamic list of interviews that needs user@client access (the SP), how can i nest the SP outside the table/parameter to enable the parameter to work?
>
> Thanks again for your help
>
|
|
|
| Re: How to script a Stored Procedure on beforeOpen [message #883526 is a reply to message #883318] |
Fri, 08 June 2012 17:18 |
|
Daniel
I think for the table to show up when dragging, the sp must return a row
set. Another option may be to use the data engine api in script to call
the stored proc. Take a look at the attached example. You will not be
able to run it but you can look at the script in the beforeFactory.
Jason
On 6/8/2012 5:34 AM, Daniel C wrote:
> Hi jason,
> Thanks for your quick reply.
>
> Please excuse my ignorance but i'm new to Birt so i'm still trying to figure things out :).
>
> I tried to follow the method of doing the inner table parameter bind but eclipse did not allow me to drag the SQL Stored Procedure dataset (SP) into the canvas (nor the table), so i binded a table dataset to the SP and inserted the second table inside this table linking the parameters. This method still does not display the dynamic list of data rows (parameter) i need.
> From what i understand the parameter report executes the parameters before the report content, therefore needing asking the parameter to retrieve data before recognising the nested table. I would of thought scripting the SP on beforeOpen before the report parameter would gain it access but your method seems much more logical, but i can't get it to work.
>
> I'll show you an example maybe to explain myself better. In this example paramInterviews should return a dynamic list of interviews that needs user@client access (the SP), how can i nest the SP outside the table/parameter to enable the parameter to work?
>
> Thanks again for your help
>
|
|
|
| Re: How to script a Stored Procedure on beforeOpen [message #884553 is a reply to message #883504] |
Mon, 11 June 2012 10:29 |
 Daniel C
Messages: 4
Registered: June 2012 |
Junior Member |
|
|
Thanks Jason,
I had a go at incorporating the scripted SP in the beforeFactory, but its still not returning the executed SP before the dynamic list parameter. It seems strange that BIRT does not support this type of parameter functionality, unless i'm missing something (which is most probably the case). It's basically a case of prioritising the SP before the parameter execution since the SP works perfectly without the parameter...
Would you be able to tell me if this is on the right track?
I altered the script as such:
importPackage( Packages.org.eclipse.birt.report.model.api );
importPackage(Packages.java.lang);
importPackage(Packages.java.util);
importPackage(Packages.org.eclipse.birt.data.engine.api);
importPackage(Packages.org.eclipse.birt.report.model.api);
importPackage(Packages.org.eclipse.birt.data.engine.api.querydefn);
importPackage(Packages.org.eclipse.birt.data.engine.core);
importPackage(Packages.org.eclipse.birt.core.data);
rowcount=0;
var myconfig = reportContext.getReportRunnable().getReportEngine().getConfig();
var de = DataEngine.newDataEngine( myconfig, null );
var dsrc = reportContext.getDesignHandle().findDataSource("HRPO_STANDARD");
var odaDataSource = new OdaDataSourceDesign( "Test Data Source" );
odaDataSource.setExtensionID( "org.eclipse.birt.report.data.oda.jdbc" );
odaDataSource.addPublicProperty( "odaURL", dsrc.getProperty("odaURL").toString() );
odaDataSource.addPublicProperty( "odaDriverClass", dsrc.getProperty("odaDriverClass").toString());
odaDataSource.addPublicProperty( "odaUser", dsrc.getProperty("odaUser").toString() );
odaDataSource.addPublicProperty( "odaPassword", "/*db password*/" );
queryText = "{call SI0WPR_SET_IDENTIFIER (?,?,?)}";
var dset = new OdaDataSetDesign( "authenticateSP" );
dset.setDataSource( odaDataSource.getName( ));
dset.setQueryText( queryText );
dset.setExtensionID( "org.eclipse.birt.report.data.oda.jdbc.SPSelectDataSet" );
inputParamDefn = new ParameterDefinition( "user1",DataType.STRING_TYPE );
inputParamDefn.setInputMode( true );
inputParamDefn.setPosition( 1 );
inputParamDefn.setDefaultInputValue( "ADMIN" );
dset.addParameter( inputParamDefn );
inputParamDefn2 = new ParameterDefinition( "client1",DataType.STRING_TYPE );
inputParamDefn.setInputMode( true );
inputParamDefn.setPosition( 2 );
inputParamDefn.setDefaultInputValue( "DEMO" );
dset.addParameter( inputParamDefn2 );
outputParm = new ParameterDefinition( "desc_cliente",DataType.STRING_TYPE,false,true );
outputParm.setPosition( 3 );
dset.addParameter( outputParm );
paramBinding = new InputParameterBinding( "user1",new ScriptExpression( params["__userAppl"]) );
paramBinding2 = new InputParameterBinding( "client1",new ScriptExpression(params["__clienteAppl"]) );
de.defineDataSource( odaDataSource );
de.defineDataSet( dset );
queryDefinition = new QueryDefinition( );
queryDefinition.setDataSetName( dset.getName() );
queryDefinition.addInputParamBinding( paramBinding );
queryDefinition.addInputParamBinding( paramBinding2 );
queryDefinition.setAutoBinding(true);
colBinding = new Binding( "myoutputparm" );
colBinding.setExpression( new ScriptExpression( "outputParams[\"desc_cliente\"]" ) );
queryDefinition.addBinding( colBinding );
var pq = de.prepare( queryDefinition );
var qr = pq.execute( null );
var ri = qr.getResultIterator( );
while ( ri.next( ) )
{
rowcount++
ob = ri.getValue( "myoutputparm" );
}
ri.close( );
qr.close( );
de.shutdown( );
|
|
|
| Re: How to script a Stored Procedure on beforeOpen [message #884646 is a reply to message #884553] |
Mon, 11 June 2012 14:44 |
|
Daniel,
Maybe I am missing something. Is the data set you want to run after the
SP tied to a report parameter dynamic list?
Jason
On 6/11/2012 6:29 AM, Daniel C wrote:
> Thanks Jason,
> I had a go at incorporating the scripted SP in the beforeFactory, but
> its still not returning the executed SP before the dynamic list
> parameter. It seems strange that BIRT does not support this type of
> parameter functionality, unless i'm missing something (which is most
> probably the case). It's basically a case of prioritising the SP before
> the parameter execution since the SP works perfectly without the
> parameter...
> Would you be able to tell me if this is on the right track?
> I altered the script as such:
>
>
> importPackage( Packages.org.eclipse.birt.report.model.api );
> importPackage(Packages.java.lang);
> importPackage(Packages.java.util);
> importPackage(Packages.org.eclipse.birt.data.engine.api);
> importPackage(Packages.org.eclipse.birt.report.model.api);
> importPackage(Packages.org.eclipse.birt.data.engine.api.querydefn);
> importPackage(Packages.org.eclipse.birt.data.engine.core);
> importPackage(Packages.org.eclipse.birt.core.data);
>
>
> rowcount=0;
>
> var myconfig =
> reportContext.getReportRunnable().getReportEngine().getConfig();
> var de = DataEngine.newDataEngine( myconfig, null );
>
> var dsrc = reportContext.getDesignHandle().findDataSource("HRPO_STANDARD");
>
> var odaDataSource = new OdaDataSourceDesign( "Test Data Source" );
> odaDataSource.setExtensionID( "org.eclipse.birt.report.data.oda.jdbc" );
> odaDataSource.addPublicProperty( "odaURL",
> dsrc.getProperty("odaURL").toString() );
> odaDataSource.addPublicProperty( "odaDriverClass",
> dsrc.getProperty("odaDriverClass").toString());
> odaDataSource.addPublicProperty( "odaUser",
> dsrc.getProperty("odaUser").toString() );
> odaDataSource.addPublicProperty( "odaPassword", "/*db password*/" );
>
> queryText = "{call SI0WPR_SET_IDENTIFIER (?,?,?)}";
> var dset = new OdaDataSetDesign( "authenticateSP" );
> dset.setDataSource( odaDataSource.getName( ));
> dset.setQueryText( queryText );
> dset.setExtensionID(
> "org.eclipse.birt.report.data.oda.jdbc.SPSelectDataSet" );
>
> inputParamDefn = new ParameterDefinition( "user1",DataType.STRING_TYPE );
> inputParamDefn.setInputMode( true );
> inputParamDefn.setPosition( 1 );
> inputParamDefn.setDefaultInputValue( "ADMIN" );
> dset.addParameter( inputParamDefn );
>
> inputParamDefn2 = new ParameterDefinition(
> "client1",DataType.STRING_TYPE );
> inputParamDefn.setInputMode( true );
> inputParamDefn.setPosition( 2 );
> inputParamDefn.setDefaultInputValue( "DEMO" );
> dset.addParameter( inputParamDefn2 );
>
> outputParm = new ParameterDefinition(
> "desc_cliente",DataType.STRING_TYPE,false,true );
> outputParm.setPosition( 3 );
> dset.addParameter( outputParm );
>
> paramBinding = new InputParameterBinding( "user1",new ScriptExpression(
> params["__userAppl"]) );
> paramBinding2 = new InputParameterBinding( "client1",new
> ScriptExpression(params["__clienteAppl"]) );
>
> de.defineDataSource( odaDataSource );
> de.defineDataSet( dset );
>
> queryDefinition = new QueryDefinition( );
> queryDefinition.setDataSetName( dset.getName() );
> queryDefinition.addInputParamBinding( paramBinding );
> queryDefinition.addInputParamBinding( paramBinding2 );
> queryDefinition.setAutoBinding(true);
>
>
>
> colBinding = new Binding( "myoutputparm" );
> colBinding.setExpression( new ScriptExpression(
> "outputParams[\"desc_cliente\"]" ) );
> queryDefinition.addBinding( colBinding );
>
> var pq = de.prepare( queryDefinition );
>
> var qr = pq.execute( null );
>
>
> var ri = qr.getResultIterator( );
> while ( ri.next( ) )
> {
> rowcount++
> ob = ri.getValue( "myoutputparm" );
>
> }
>
> ri.close( );
> qr.close( );
> de.shutdown( );
>
|
|
|
Goto Forum:
Current Time: Thu Apr 25 05:00:38 GMT 2024
Powered by FUDForum. Page generated in 0.03950 seconds |
] 
Search
Help
Register
Login
Home
.
