Servoy Forum

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.

Code: Select all

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?

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?

mboegem wrote: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.

Code: Select all

/** @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' );


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,

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

Hello Juan, thanks that was a good hint.

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

Yes, I found the problem.

Code: Select all

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 wrote: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?

Code: Select all

/** @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

jasantana wrote:reportParams is an empty object

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