PASSING A SQL SENTENCE To A REPORT by PARAMET

[ktrinad]

By: marí®- jjmarin

PASSING A SQL SENTENCE To A REPORT by PARAMET

2003-11-07 03:50

Hello we are already here again with another problem, I before nothing forgive my groins. You are that I need to substitute the SQL sentence that takes a report, pass it from JAVA thanks to the JASPERREPORT bookstores. I would like to know if one could carry out this operation without having to carry out the change physically on the XML file, by PARAMETERS, and so speed up the JAVA code and give you better humility to the report that I am carrying out.

 

it is say, what I need am pass the SQL sentence via parameter to the [report].

 

Or the only way is modifying the XML file and the label< [queryString]> substitute it for the SQL sentence so that everything works how it is owed.

 

Thank you and pardon for my groins

 

 

 

 

By: Mark Rhodes - mrhodes2

RE: PASSING A SQL SENTENCE To A REPORT by PARAMET

2003-11-07 11:22

Setup your SQL statement as a parameter, then pass the entire statement in.

 

 

 

 

By: Giulio Toffoli - gt78

RE: PASSING A SQL SENTENCE To A REPORT by PARAMET

2003-11-08 04:01

The syntax of your SQL must be something like this:

 

!P{MySQLQueryParameter}

 

Note the exclamation mark (!) before the parameter!

 

Giulio

 

 

 

 

 

 

By: marí®- jjmarin

RE: PASSING A SQL SENTENCE To A REPORT by PARAMET

2003-11-10 01:03

I could not have passed the SQL sentence for parameter.

The forms that I have used are:

 

<queryString><![CDATA[!P{cSQL}]]></queryString>

 

<queryString>!P{cSQL}></queryString>

 

The truth is that they give me the same error:

 

java.sql.SQLException: Non supported SQL92 token at position: 7: cSQL

 

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)

 

at oracle.jdbc.driver.OracleSql.handleToken(OracleSql.java)

 

at oracle.jdbc.driver.OracleSql.handleODBC(OracleSql.java)

 

at oracle.jdbc.driver.OracleSql.parse(OracleSql.java)

 

at oracle.jdbc.driver.OracleConnection.nativeSQL(OracleConnection.java)

 

at oracle.jdbc.driver.OracleStatement.parseSqlKind(OracleStatement.java)

 

at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java)

 

at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java)

 

at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java)

 

at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java)

 

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.java:209)

 

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.java:142)

 

at dori.jasper.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:423)

 

at dori.jasper.engine.fill.JRFiller.fillReport(JRFiller.java:119)

 

at dori.jasper.engine.JasperFillManager.fillReport(JasperFillManager.java:219)

 

at dori.jasper.engine.JasperFillManager.fillReportToFile(JasperFillManager.java:140)

 

at dori.jasper.engine.JasperFillManager.fillReportToFile(JasperFillManager.java:108)

 

at jasperreports.list_incide.list_incide.main(list_incide.java:109)

 

 

NESTED BY :

 

java.sql.SQLException: Non supported SQL92 token at position: 7: cSQL

 

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)

 

at oracle.jdbc.driver.OracleSql.handleToken(OracleSql.java)

 

at oracle.jdbc.driver.OracleSql.handleODBC(OracleSql.java)

 

at oracle.jdbc.driver.OracleSql.parse(OracleSql.java)

 

at oracle.jdbc.driver.OracleConnection.nativeSQL(OracleConnection.java)

 

at oracle.jdbc.driver.OracleStatement.parseSqlKind(OracleStatement.java)

 

at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java)

 

at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java)

 

at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java)

 

at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java)

 

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.java:209)

 

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.java:142)

 

at dori.jasper.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:423)

 

at dori.jasper.engine.fill.JRFiller.fillReport(JRFiller.java:119)

 

at dori.jasper.engine.JasperFillManager.fillReport(JasperFillManager.java:219)

 

at dori.jasper.engine.JasperFillManager.fillReportToFile(JasperFillManager.java:140)

 

at dori.jasper.engine.JasperFillManager.fillReportToFile(JasperFillManager.java:108)

 

at jasperreports.list_incide.list_incide.main(list_incide.java:109)

 

 

NESTED BY :

 

dori.jasper.engine.JRException: Error executing report query :

 

!P{cSQL}

 

 

 

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.java:379)

 

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.java:142)

 

at dori.jasper.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:423)

 

at dori.jasper.engine.fill.JRFiller.fillReport(JRFiller.java:119)

 

at dori.jasper.engine.JasperFillManager.fillReport(JasperFillManager.java:219)

 

at dori.jasper.engine.JasperFillManager.fillReportToFile(JasperFillManager.java:140)

 

at dori.jasper.engine.JasperFillManager.fillReportToFile(JasperFillManager.java:108)

 

at jasperreports.list_incide.list_incide.main(list_incide.java:109)

 

Caused by: java.sql.SQLException: Non supported SQL92 token at position: 7: cSQL

 

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)

 

at oracle.jdbc.driver.OracleSql.handleToken(OracleSql.java)

 

at oracle.jdbc.driver.OracleSql.handleODBC(OracleSql.java)

 

at oracle.jdbc.driver.OracleSql.parse(OracleSql.java)

 

at oracle.jdbc.driver.OracleConnection.nativeSQL(OracleConnection.java)

 

at oracle.jdbc.driver.OracleStatement.parseSqlKind(OracleStatement.java)

 

at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java)

 

at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java)

 

at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java)

 

at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java)

 

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.java:209)

 

 

I would like to know if somebody has had this problem before. Maybe I am not good programmer but sincerely the step of [parameters] everybody could make. I already used this technique for the CRISTALREPORT and was me very well.

 

Maybe it is necessary to of declaring the parameter in a different way in XML file.

 

<parameter name="cSQL" isForPrompting="true" class="java.lang.String"/>

 

 

or one is passing wrong from the JAVA file.

 

parameters.put("cSQL", cFrasSQL);

 

JasperFillManager.fillReportToFile(fileName, parameters, getConnection());

 

 

 

 

FORGIVE MI GROINS And THANK YOU FOR their TIME

 

 

 

 

 

By: Sachin Aggarwal - sk_aggarwal

RE: PASSING A SQL SENTENCE To A REPORT by PARAMET

2003-11-10 01:27

Hi,

You can not pass a Query string as a parameter which in turn contains parametes. I guess you can replace all the sub-parameters and construct a complete sql query and then pass it as a parameter.

Hope this works. I suggest you refer to "jasper report the ultimate guide" for more info on this.

 

Sachin

 

 

 

 

By: marí®- jjmarin

RE: PASSING A SQL SENTENCE To A REPORT by PARAMET

2003-11-10 02:04

I have already tried to leave the SQL sentence and create the sentence with parameters, but it don't work.

 

I suppose that JASPERREPORT compiles the SQL sentence that goes in the XML file and if it doesn't have logic the sentence. It don't continue compiling neither wait to that it finish the step of parameters.

 

What yes it have worked me, it is pass the values of some fields that form the WHERE condition in the SQL sentence and so go alternating statements values.

 

I don't know how I am going to comment this with my boss, I have already traveled all the forums that I know, I have asked to the GOOGLE god and I don't succeed in seeing the light.

 

Thank you.

 

 

 

 

By: Mark Rhodes - mrhodes2

RE: PASSING A SQL SENTENCE To A REPORT by PARAMET

2003-11-10 10:06

you need $ for a parameter

 

<queryString><![CDATA[$P{cSQL}]]></queryString>

 

and you need to define the parameter

 

eg:

<parameter name="cSQL" isForPrompting="true" class="java.lang.String">

<defaultValueExpression><![CDATA[new String("select * from table")]]></defaultValueExpression>

</parameter>

 

The defaultValue is not mandatory, but it sure makes testing a report inside iReports much easier.

 

It sounds like your trying to compile and view a report with a parameter inside iReport. When you compile you must clear the query string field on the iReport compiler tool window. This tell iReport to resolve the parameters internally, which is why I always have a default value in my parameters.

 

Lastly do you really need to pass the entire SQL statement, or just parameters. Eg:

 

select * from sales where salesdate between '1/1/03' and '1/31/03.

 

If do do the following;

 

 

<parameter name="pstartdate" isForPrompting="true" class="java.lang.String">

<defaultValueExpression><![CDATA[new String("05/08/03")]]></defaultValueExpression>

</parameter>

 

 

 

<parameter name="penddate" isForPrompting="true" class="java.lang.String">

<defaultValueExpression><![CDATA[new String("05/21/03")]]></defaultValueExpression>

</parameter>

 

<queryString><![CDATA[

Select * from sales where salesdate between $P{pstartdate} and $P{penddate}]]></queryString>

 

 

Notice I defaulted the dates inside iReport, but I can override in my webapp with user defined dates.

 

 

 

 

By: marí®- jjmarin

RE: PASSING A SQL SENTENCE To A REPORT by PARAMET

2003-11-11 01:04

Thanks to all, thank you. But lament communicate that I don't still get my objective, and I have continued the steps that he indicate Mark Rhodes, but I don't succeed in passing the SQL sentence. I don't mean that wrong is what he indicate Mr. Mark Rhodes, if it work well with step of parameters or conditions to the SQL sentence, but it don't permit the step of the complete SQL sentence.

 

On the other hand I have found a problem with the step of parameters because it only permit a maximum longitude of 46 digits. It is say, when I pass you a parameter with a maximum longitude to her permitted it truncate me it and it then don't permit the step of long sentences, only small comments.

 

I would like to know if some of you have succeeded in passing a SQL sentence, because I begin to think that this generator of report doesn't admit that operation, And I don't BELIEVE THAT it IS The ÚŽICO I GET MARRIED The MIO, since it is interesting pass the SQL sentence for the topic of the temporary charts or views that they are created in order to carry out calculations with the SQL sentences.

 

Good companions hope to don't bother and they forgive my groins. Thank you.

 

 

 

 

By: marí®- jjmarin

Forgive

2003-11-11 01:24

Forgive, they forgive my expression; when I refer to the groin.

In fact I meant that they forgive the orthographic lacks and the expression.