Zuckerreports Binding parameter in sugarcrm

[mrpele]

Hi,
(look for more information in next reply)
I have made reports in ireport and with parameter like $P{ACCOUNT_ID}.
Then I make a report in Zuckerreports in Sugarcrm 6.0.2 Community Edition and upload the relevant jrxml-file.
Then I bind the sugar-parameter ACCOUNT_ID to this report.
So far no problem.
BUT when I try to run this report I get error message like following.
I have tried to write the parameter in ireport in different ways: $P{ACCOUNT_ID}, $p{ACCOUNT_ID}, $p!{ACCOUNT_ID} and so on.
I have read manuals but cant figure out what is wrong with my sql-statement.
Is there any special trix to pass parameters to sugarcrm from ireport using zuckerreport?
Please help me solve this problem.
Best regards
Per


The error code:

cmdline: javaw -classpath "custom/ZuckerReports/resources/;custom/ZuckerReports/resources/account_enkel3_medklammeroutropsteck.jasper_files/;modules/ZuckerReports/jasper/ant-1.7.1.jar;modules/ZuckerReports/jasper/antlr-2.7.6.jar;modules/ZuckerReports/jasper/asm-attrs.jar;modules/ZuckerReports/jasper/asm.jar;modules/ZuckerReports/jasper/barbecue-1.5-beta1.jar;modules/ZuckerReports/jasper/barcode4j-2.0.jar;modules/ZuckerReports/jasper/batik-anim.jar;modules/ZuckerReports/jasper/batik-awt-util.jar;modules/ZuckerReports/jasper/batik-bridge.jar;modules/ZuckerReports/jasper/batik-css.jar;modules/ZuckerReports/jasper/batik-dom.jar;modules/ZuckerReports/jasper/batik-ext.jar;modules/ZuckerReports/jasper/batik-gvt.jar;modules/ZuckerReports/jasper/batik-parser.jar;modules/ZuckerReports/jasper/batik-script.jar;modules/ZuckerReports/jasper/batik-svg-dom.jar;modules/ZuckerReports/jasper/batik-svggen.jar;modules/ZuckerReports/jasper/batik-util.jar;modules/ZuckerReports/jasper/batik-xml.jar;modules/ZuckerReports/jasper/bcel-5.2.jar;modules/ZuckerReports/jasper/bsh-2.0b4.jar;modules/ZuckerReports/jasper/castor-1.2.jar;modules/ZuckerReports/jasper/cglib-2.1.jar;modules/ZuckerReports/jasper/cincom-jr-xmla.jar;modules/ZuckerReports/jasper/commons-beanutils-1.8.2.jar;modules/ZuckerReports/jasper/commons-collections-3.2.1.jar;modules/ZuckerReports/jasper/commons-dbcp-1.2.2.jar;modules/ZuckerReports/jasper/commons-digester-1.7.jar;modules/ZuckerReports/jasper/commons-javaflow-20060411.jar;modules/ZuckerReports/jasper/commons-logging-1.1.jar;modules/ZuckerReports/jasper/commons-math-1.0.jar;modules/ZuckerReports/jasper/commons-pool-1.3.jar;modules/ZuckerReports/jasper/commons-vfs-1.0.jar;modules/ZuckerReports/jasper/dom4j-1.6.jar;modules/ZuckerReports/jasper/ehcache-1.1.jar;modules/ZuckerReports/jasper/eigenbase-properties-1.1.0.10924.jar;modules/ZuckerReports/jasper/eigenbase-resgen-1.3.0.11873.jar;modules/ZuckerReports/jasper/eigenbase-xom-1.3.0.11999.jar;modules/ZuckerReports/jasper/ejb3-persistence.jar;modules/ZuckerReports/jasper/groovy-all-1.5.5.jar;modules/ZuckerReports/jasper/hibernate-annotations.jar;modules/ZuckerReports/jasper/hibernate-commons-annotations.jar;modules/ZuckerReports/jasper/hibernate3.jar;modules/ZuckerReports/jasper/hsqldb-1.8.0-10.jar;modules/ZuckerReports/jasper/iText-2.1.0.jar;modules/ZuckerReports/jasper/iTextAsian.jar;modules/ZuckerReports/jasper/jakarta-bcel-20050813.jar;modules/ZuckerReports/jasper/jasperreports-3.7.1.jar;modules/ZuckerReports/jasper/jasperreports-chart-themes-3.6.2.jar;modules/ZuckerReports/jasper/jasperreports-extensions-3.5.3.jar;modules/ZuckerReports/jasper/jasperreports-fonts-3.6.1.jar;modules/ZuckerReports/jasper/javacup.jar;modules/ZuckerReports/jasper/javassist-3.4.GA.jar;modules/ZuckerReports/jasper/jaxen-1.1.1.jar;modules/ZuckerReports/jasper/jcommon-1.0.15.jar;modules/ZuckerReports/jasper/jdt-compiler-3.1.1.jar;modules/ZuckerReports/jasper/jfreechart-1.0.12.jar;modules/ZuckerReports/jasper/jpa.jar;modules/ZuckerReports/jasper/js_activation-1.1.jar;modules/ZuckerReports/jasper/js_axis-1.4patched.jar;modules/ZuckerReports/jasper/js_commons-codec-1.3.jar;modules/ZuckerReports/jasper/js_commons-discovery-0.2.jar;modules/ZuckerReports/jasper/js_commons-httpclient-3.1.jar;modules/ZuckerReports/jasper/js_jasperserver-common-ws-3.5.0.jar;modules/ZuckerReports/jasper/js_jaxrpc.jar;modules/ZuckerReports/jasper/js_mail-1.4.jar;modules/ZuckerReports/jasper/js_saaj-api-1.3.jar;modules/ZuckerReports/jasper/js_wsdl4j-1.5.1.jar;modules/ZuckerReports/jasper/jta.jar;modules/ZuckerReports/jasper/jxl-2.6.jar;modules/ZuckerReports/jasper/log4j-1.2.15.jar;modules/ZuckerReports/jasper/mondrian-3.1.1.12687-Jaspersoft.jar;modules/ZuckerReports/jasper/mysql-connector-java-3.1.11-bin.jar;modules/ZuckerReports/jasper/olap4j-0.9.7.145.jar;modules/ZuckerReports/jasper/png-encoder-1.5.jar;modules/ZuckerReports/jasper/poi-3.2-FINAL-20081019.jar;modules/ZuckerReports/jasper/rex-20080421.jar;modules/ZuckerReports/jasper/rhino-1.7R1.jar;modules/ZuckerReports/jasper/saaj-api-1.3.jar;modules/ZuckerReports/jasper/slf4j-api.jar;modules/ZuckerReports/jasper/slf4j-log4j12.jar;modules/ZuckerReports/jasper/spring.jar;modules/ZuckerReports/jasper/sqleonardo-2007.03.jar;modules/ZuckerReports/jasper/swingx-2007_10_07.jar;modules/ZuckerReports/jasper/xml-apis-ext.jar;modules/ZuckerReports/jasper/xml-apis.jar;modules/ZuckerReports/jasper/zuckerreports-1.0.jar" at.go_mobile.zuckerreports.JasperBatchMain custom/ZuckerReports/temp/bd618fc5-36d6-7cc5-3175-4d64dcfbd489/cmd.properties 2>&1
JasperBatchMain :: loading jasper design custom/ZuckerReports/resources/account_enkel3_medklammeroutropsteck.jasper
JasperBatchMain :: getParameterValue(REPORT_PARAMETERS_MAP, java.util.Map) => null
JasperBatchMain :: getParameterValue(JASPER_REPORT, net.sf.jasperreports.engine.JasperReport) => null
JasperBatchMain :: getParameterValue(REPORT_CONNECTION, java.sql.Connection) => null
JasperBatchMain :: getParameterValue(REPORT_MAX_COUNT, java.lang.Integer) => null
JasperBatchMain :: getParameterValue(REPORT_DATA_SOURCE, net.sf.jasperreports.engine.JRDataSource) => null
JasperBatchMain :: getParameterValue(REPORT_SCRIPTLET, net.sf.jasperreports.engine.JRAbstractScriptlet) => null
JasperBatchMain :: getParameterValue(REPORT_LOCALE, java.util.Locale) => null
JasperBatchMain :: getParameterValue(REPORT_RESOURCE_BUNDLE, java.util.ResourceBundle) => null
JasperBatchMain :: getParameterValue(REPORT_TIME_ZONE, java.util.TimeZone) => null
JasperBatchMain :: getParameterValue(REPORT_FORMAT_FACTORY, net.sf.jasperreports.engine.util.FormatFactory) => null
JasperBatchMain :: getParameterValue(REPORT_CLASS_LOADER, java.lang.ClassLoader) => null
JasperBatchMain :: getParameterValue(REPORT_URL_HANDLER_FACTORY, java.net.URLStreamHandlerFactory) => null
JasperBatchMain :: getParameterValue(REPORT_FILE_RESOLVER, net.sf.jasperreports.engine.util.FileResolver) => null
JasperBatchMain :: getParameterValue(REPORT_VIRTUALIZER, net.sf.jasperreports.engine.JRVirtualizer) => null
JasperBatchMain :: getParameterValue(IS_IGNORE_PAGINATION, java.lang.Boolean) => null
JasperBatchMain :: getParameterValue(REPORT_TEMPLATES, java.util.Collection) => null
JasperBatchMain :: getParameterValue(ACCOUNT_ID, java.lang.String) => 111Företag
log4j:WARN No appenders could be found for logger (net.sf.jasperreports.extensions.ExtensionsEnviron ment).
log4j:WARN Please initialize the log4j system properly.
JasperBatchMain :: Error executing SQL statement for : account_enkel3
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : account_enkel3
at net.sf.jasperreports.engine.query.JRJdbcQueryExecu ter.createDatasource(JRJdbcQueryExecuter.java:141)
at net.sf.jasperreports.engine.fill.JRFillDataset.cre ateQueryDatasource(JRFillDataset.java:681)
at net.sf.jasperreports.engine.fill.JRFillDataset.ini tDatasource(JRFillDataset.java:601)
at net.sf.jasperreports.engine.fill.JRBaseFiller.setP arameters(JRBaseFiller.java:1247)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill (JRBaseFiller.java:877)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill (JRBaseFiller.java:826)
at net.sf.jasperreports.engine.fill.JRFiller.fillRepo rt(JRFiller.java:59)
at at.go_mobile.zuckerreports.JasperBatchMain.main(Ja sperBatchMain.java:126)
Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '�retag' at line 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:2926)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:15 71)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java :1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java: 2978)
at com.mysql.jdbc.Connection.execSQL(Connection.java: 2902)
at com.mysql.jdbc.PreparedStatement.executeInternal(P reparedStatement.java:933)
at com.mysql.jdbc.PreparedStatement.executeQuery(Prep aredStatement.java:1027)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecu ter.createDatasource(JRJdbcQueryExecuter.java:135)
... 7 more

[mrpele]

Here is more information about the problem. Maybe you should read this first.

I have succed to bind the ACCOUNT_ID parameter and run the report without error message.
BUT then the output (pdf-doc or html-file) is empty.
In this case I have made a similar report in ireport with a fixed parameter (WHERE id = 'aaf2-ssss-sssss-ssssetc') THen there is no problem to get an output in sugarcrm.

So maybe the problem is in the code generating the output in zuckerreport in sugarcrm.

Please give me an expert advice

//Pele

[mrpele]

I lift this thread because I think it is important and still unanswered.
Anyone out there knowhing this?

[Topas_C]

You got this error :"Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '�retag' at line 1"

Which Sql-query did you use in i Report?
I think, You should use "Account_Name" in you SQL-parametres.

[mrpele]

Thanks for your help.

If I make this query in iReport I will get no error in sugar:

SELECT * from Accounts WHERE name = $P{ACCOUNT_ID}

or

SELECT * from Accounts WHERE id = $P{ACCOUNT_ID}

After I upload the jrxml-file to Sugarcrm Jasper will compile it into a jasper-file that will be run by Zuckerreports.

When I run the report I can choose an account in the popup BUT the output will be blank.

The saved report-file in Sugar will also be blank.

I think maybe that Jasper compiles it into a format that is not compatible with mysql.

But it should not be soo, and I cant understand why.

Does this make any sense?

Pele

[agrayagray]

Pete,
I can't be much help to you with this problem but just wanted to let you know that I have been trying to solve the EXACT SAME PROBLEM for about three years now! Every now and then I post a question to the forum but nobody has been able to explain how to pass parameters from SugarCRM into a report (uploaded to Zucker).

If there is anyone out there who knows about these things please help.

Pete - if you find a solution please let me know at agray@conscious.co.uk
thanks.
ANDREW

[Topas_C]

You should correct your SQL_query:

SELECT * from Accounts WHERE id = '$P{ACCOUNT_ID}'