Home » Archived » BIRT » How to script a Stored Procedure on beforeOpen
How to script a Stored Procedure on beforeOpen [message #882951] |
Thu, 07 June 2012 10:10  |
Eclipse User |
|
|
|
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 #883126 is a reply to message #882951] |
Thu, 07 June 2012 17:58   |
Eclipse User |
|
|
|
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 17:59   |
Eclipse User |
|
|
|
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 #884553 is a reply to message #883504] |
Mon, 11 June 2012 06:29   |
Eclipse User |
|
|
|
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 10:44  |
Eclipse User |
|
|
|
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: Fri Jul 04 15:20:12 EDT 2025
Powered by FUDForum. Page generated in 0.05325 seconds
|