Jasper (plugin) error message interpretation

Classic Servoy
1

How to interpret jasper (plugin) error messages? I get the following error, but I don’t know what is wrong. Four times: Error executing SQL statement for : boss_charterfaktura_document_list_commission, but no information what is wrong with SQL statement.

Exception Object: java.lang.Exception: net.sf.jasperreports.engine.JRException: Cause: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : boss_charterfaktura_document_list_commission
Message:  net.sf.jasperreports.engine.JRException: Error executing SQL statement for : boss_charterfaktura_document_list_commission
MSG: net.sf.jasperreports.engine.JRException: Cause: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : boss_charterfaktura_document_list_commission
Message:  net.sf.jasperreports.engine.JRException: Error executing SQL statement for : boss_charterfaktura_document_list_commission
<null>
Cause: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : boss_charterfaktura_document_list_commission
Message:  net.sf.jasperreports.engine.JRException: Error executing SQL statement for : boss_charterfaktura_document_list_commission
Wrapped java.lang.Exception: net.sf.jasperreports.engine.JRException: Cause: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : boss_charterfaktura_document_list_commission
Message:  net.sf.jasperreports.engine.JRException: Error executing SQL statement for : boss_charterfaktura_document_list_commission (H:\servoy_workspace_git\boss\br_boss_chart_faktura\forms\boss_char_charterfaktura_document_list.js#299)
	at H:\servoy_workspace_git\boss\br_boss_chart_faktura\forms\boss_char_charterfaktura_document_list.js:299 (createDocumentList)
	at H:\servoy_workspace_git\boss\mod_dialog\globals.js:142 (showDialog)
	at H:\servoy_workspace_git\boss\mod_dialog\globals.js:360
	at H:\servoy_workspace_git\boss\br_boss_chart_faktura\forms\boss_char_charterfaktura_base_dtl.js:872 (showDocumentListCreateForm)

In iReport the report compiles without problems. Are the error message thrown by the jasper plugin or by jasper itself?

2

Seems like you’re using found sets to provide the data to your report.
Are you calling the jasper report using the correct input type?

3

mboegem:
Seems like you’re using found sets to provide the data to your report.
Are you calling the jasper report using the correct input type?

No, I don’t pass foundsets. I pass an object with the params used in iReport for the SQL query.

/** @type { { clientNo:Number, customerNo:Number, documentClassSelection:String, exchangeRateTiming:String, from:String, paidDocuments:String, sortColumn:String, to:String, vesselNo:Number } } */
var reportParams = { };
plugins.jasperPluginRMI.runReport( 'boss_db', 'boss_charterfaktura_document_list.jasper', null, "view", reportParams, 'en' );
4

Hi Sebastian.

I think the problem is in the query it self.

Can you paste the SQL sentence into your DB manager and run it?

I also see that you are sending null parameters. Is that in this example? Probably the parameters default values are messing your SQL.

Cheers,

5

jasantana:
Can you paste the SQL sentence into your DB manager and run it?

Hello Juan, thanks that was a good hint.

jasantana:
I think the problem is in the query it self.

Yes, I found the problem.

select ifnull (sum (commissionDocument.netto), 0) as commissionCreditNoteNetValue, ( select ifnull (sum (commissionDocument.netto), 0) from tckopf as commissionDocument where commissionDocument.referenzid = $P{document_id} and commissionDocument.btyp = 7) as commissionInvoiceNetValue
from tckopf as commissionDocument
where commissionDocument.referenzid = $P{document_id} and commissionDocument.btyp = 8

ifnull is the problem. In iReport it works, but not in the SQL Explorer in Servoy. Why it does not work in Servoy?

jasantana:
I also see that you are sending null parameters. Is that in this example? Probably the parameters default values are messing your SQL.

Where do you see that?

6 
/** @type { { clientNo:Number, customerNo:Number, documentClassSelection:String, exchangeRateTiming:String, from:String, paidDocuments:String, sortColumn:String, to:String, vesselNo:Number } } */
var reportParams = { };
plugins.jasperPluginRMI.runReport( 'boss_db', 'boss_charterfaktura_document_list.jasper', null, "view", reportParams, 'en');

reportParams is an empty object

7

jasantana:
reportParams is an empty object

No, it is a driver bug. The DBF JDBC driver version, that we use has a problem with IFNULL.