Hi Kalpesh,
Your solution to address SQL differences between MS SQL and Oracle is
definitely 'brilliant'.
But passing ur programming overhead to JasperReports is not fair. We
have to do a bit of programming to encapsulate our business requirements
and make it easy for the reporting tool to do what it is good at.
My suggestion will be
a) Place ur queries in a XML file. E.g.,
Name ur files oraSQL.xml, msSQL.xml. Develop ur program to pick its
queries from a file called appQueries.xml. Copy oraSQL onto
appQueries.xml or msSQL.xml onto appQueries.xml based on which db u want
to use.
oraSQL.xml
<myQueries>
<query report_id="REP001" sql="select 'SUMMARY' from dual />
<!-- report_id could refer to a jasper report name or a short code,
which ever is convenient.
define the query directly or through a mapping in ur program
</myQueries>
oraSQL.xml
msSQL.xml
<myQueries>
<query report_id="REP001" sql="select 'SUMMARY' />
</myQueries>
b) In Jasper Reports pass the entire query string as a parameter. E.g.
<queryString>$P!{myQuery}</queryString>
c) Develop your program such that it fetches the query from
appQuery.xml and passes the string to JasperReports via the parameter
myQuery.
d) If u need custom sort order and group by conditions, you will have to
extend the thinking a little bit more and do something like this.
<query report_id="REP001" sql="select ?, sum(salary) from dept group by
? order by ?" />
In ur program that prepares the query replace the ? (which is a place
holder) with appropriate value.
Once the query string is ready, you can pass it to Jasper.
Do not expect Jasper to
* first replace $P!{myQuery} with your custom query and
* scan the query once again for any parameter values to be replaced
(this becomes a second scan of the query string, which I dont think
JasperReports will do.
Hope this clarifies.
Best Regards
Rao
Kalpesh Chordia wrote:
> Hello Teodor,
>
> One simple question.
>
> During Filling process, if the sql query fails then it throws
> java.sql.exception.
>
> Where I will get the complete sql query that is getting executed on
> the connection ?
>
> Is there any way to set the trace logging level so as to show all the
> logging stmts ?
>
> Thanks,
> Kalpesh
>
>
> -----Original Message-----
> From: Teodor Danciu [mailto:teodord@...]
> Sent: Thursday, December 09, 2004 2:13 PM
> To: Kalpesh Chordia
> Cc: 'ramohan@...';
> 'jasperreports-questions@...'
> Subject: Re: [jasperreports-questions] Passing SQL query in parameter
> map ?
>
>
>
> Hi,
>
> The <queryString> and the <defaultValueExpression> are very different
> things.
> The first introduces the SQL query and special syntax allows you to
> introduce
> parameters like in a PreparedStatement or to dynamically build the SQL
> command
> using the $P!{} syntax.
> The second however, is a Java expression that is evaluated at runtime.
> In your case, the default expression for the parameter is a harcoded
> text.
> No replacements are made.
>
> You could do this:
>
> <parameter name="SQLQUERY" isForPrompting="false"
> class="java.lang.String">
> <defaultValueExpression ><![CDATA["SELECT * FROM table_name
> WHERE RunId = " + $P{RUN_ID}]]></defaultValueExpression>
> </parameter>
> <queryString><![CDATA[$P!{SQLQUERY}]]> </queryString>
>
> Or this simply this, without any SQLQUERY parameters needed:
>
> <queryString><![CDATA[SELECT * FROM table_name WHERE RunId =
> $P{RUN_ID}]]> </queryString>
>
> I hope this helps.
> Teodor
>
> Kalpesh Chordia wrote:
>
> > Hello Rao,
> >
> > Sorry to trouble you again.
> >
> > I have one parameter run_id which I pass in parameter map to my master
> > design template.
> > Master design template has one subreport say summary.xml
> > I pass this run_id parameter to summary subreport.
> > In summary subreport, I use this run_id parameter in query in where by
> > clause.
> > (select * from table_name where run_id = $P{RUN_ID})
> >
> > As per your below approach, I have created one parameter say SQLQUERY
> > to store the complete sql query.
> > <parameter name="SQLQUERY" isForPrompting="false"
> > class="java.lang.String">
> > <defaultValueExpression ><![CDATA["SELECT * FROM table_name
> > WHERE RunId = $P{RUN_ID}"]]></defaultValueExpression>
> >
> > </parameter>
> > <queryString><![CDATA[$P!{SQLQUERY}]]> </queryString>
> >
> > During FILL time, JasperReports is not replacing $P{RUN_ID} with the
> > real value (number) passed to this summary subreport.
> >
> > It is throwing following java.sql.SQLException
> > [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1:
> > Incorrect syntax near 'parameter_RUN_ID'.
> >
> > How do I resolve this problem ?
> >
> > Also, is there any way where I can see the actual query getting
> > executed on the database ? Right now, JasperReports is not filling the
> > reports template by throwing the above sql exception. But, it is not
> > showing the exact sql query also.
> >
> > Please advise me as this is a major hurdle in my project.
> >
> > We at runtime, use parameters in where by clause and order by clause.
> >
> > Thanks in advance.
> >
> > Regards,
> > Kalpesh
> >
> >
> > -----Original Message-----
> > From: Rama Mohan Pacific Net [mailto:ramohan@...]
> > Sent: Thursday, November 18, 2004 7:03 PM
> > To: Kalpesh Chordia
> > Subject: Re: [jasperreports-questions] Passing SQL query in parameter
> > map ?
> >
> >
> > Hi Kalpesh,
> >
> > I dont think it is possible to implement ur idea in Jasper Reports. I
> > had wanted something similar in the past -- using an expression to
> > decide the background color of a row based on the row count. Jasper
> > does not support such flexibility, at least not yet.
> >
> > Since the tool is what it is we have to change. I.e.,
> > a) rewrite the application to pick up the queries from a config xml
> file
> > (we use this approach many places in our application.)
> > b) use OJB.
> >
> > Sorry, couldnt be much help.
> >
> > Regards
> > Rao
> >
> >
> > Kalpesh Chordia wrote:
> >
> > > Hello Rao,
> > >
> > > thanks a lot for your suggestions.
> > >
> > > If I have to pass sql as a parameter, then I will have to consider
> the
> > > db-type (ms-sql/oracle) before setting the sql string in the
> parameter
> > > map.
> > >
> > > My idea ->
> > > I will create two parameters in xml one for ms-sql and other for
> > > oracle sql query and depending on the db-type I will set proper
> > > parameter in the query string.
> > >
> > > But, I don't know how do I conditionally set the parameter in the
> > > query string section ?
> > > (print when expression for query string) ?
> > >
> > > OR
> > >
> > > Can I conditionally set the data in parameter ?
> > >
> > > example ->
> > > MS-SQL => select "summary"
> > > ORACLE => select "summary" from dual;
> > >
> > > $P{dbType} will hold either "MS-SQL" or "ORACLE" (will be passed in
> > > parameter map along with the connection)
> > >
> > > So depending upon the db-type, I want set MS-SQL or ORACLE sql string
> > > in $P{sqlQuery}
> > > if $P{dbType} == "MS-SQL" then $P{sqlQuery} = select "summary"
> > > if $P{dbType} == "ORACLE" then $P{sqlQuery} = select "summary from
> > dual;"
> > >
> > > <queryString><![CDATA[$P!{sqlQuery}]]> </queryString>
> > >
> > > Please advise.
> > >
> > > Thanks and Regards,
> > > Kalpesh
> > >
> > > -----Original Message-----
> > > From: Rama Mohan Pacific Net [mailto:ramohan@...]
> > > Sent: Monday, November 15, 2004 8:56 PM
> > > To: kchordia@...; jasperreports-questions@...
> > > Subject: Re: [jasperreports-questions] Passing SQL query in parameter
> > > map ?
> > >
> > >
> > > Hi Kalpesh,
> > >
> > > Firstly, JasperReports is 'Java'. I.e., u can specify all file paths
> > > using / (forward slash). UNIX or DOS.
> > > Secondly, there are a few ways to address SQL issues.
> > >
> > > 1) pass the SQL as a parameter and use the following
> > > <queryString><![CDATA[$P!{sqlQuery}]]> </queryString>
> > > 2) use ANSI-SQL and desist from using database specific extensions
> > > (e.g., ORACLE's + for outer join)
> > > 3) use a product like OJB to query data and pass the queried data
> as a
> > > Collection of Java Beans.
> > >
> > > Hope this helps.
> > >
> > > Best Regards
> > > Rao (Rama Mohan Rao)
> > >
> > > --- Original Message ----
> > >
> > > Hi,
> > >
> > > I pass SQL connection to jasper fillManager() which in turn executes
> > > the SQL
> > > queries written in query section of the template. The queries
> > written are
> > > MS-SQL specific. Now, I want to use same template for Oracle DB.
> > Now, the
> > > problem is SQL queries written in query section of template will
> > fail on
> > > Oracle database.
> > >
> > > So, can I pass query string in parameter map ? If yes, how to access
> > the
> > > parameter map in query section ?
> > >
> > > Current setup is Windows 2000 with MS-SQL 2000. So, all our templates
> > > have
> > > MS-SQL specific queries hard-coded in template design with subreports
> > > included in main templates and accessed like
> > > <subreportExpression class="java.io.File"><![CDATA[new
> > > File($V{BaseDir}+"\\reports\\"+$P{PARTITION_ID}+"\\templates\\12004",
> > > $P{SUBREPORT_SUMMARY_FILE_NAME})]]></subreportExpression>
> > >
> > > Now, we are moving towards SOLARIS - ORACLE combination. We do not
> > > want to
> > > keep two copies of template with only change in query section. This
> > will
> > > increase our maintenance. So, our aim is to create OS + DB
> independent
> > > template.
> > >
> > > Also, the path mentioned above uses back slash but Solaris needs
> front
> > > slash
> > > (I am not sure). How to achieve same in the template during design
> time
> > > only.
> > >
> > > All you suggestions/comments are most welcome.
> > >
> > > We want to submit the efforts required at earliest. So, please
> give me
> > > suggestions at earliest.
> > >
> > > Thanks and Regards,
> > > Kalpesh
> > >
> > >
> >
>
|
