groovy and jdbc connection with Jaspersoft Studio

Question

I try to use groovy for my report to connect a subdataset to an Oracle database.

-> In report > properties> language : groovy

-> in my dataset i choose "use an other connection" with the expression :

Sql.newInstance(
                    "jdbc:oracle:thin:@ip:1521:XE",
                    "user",
                    "pw",
                    "oracle.jdbc.driver.OracleDriver"
                )

My parameters (ip, port, user, password) are corrects, i already did this java and it works but i need to do this with groovy :(

When i preview the report (using groovy) i get this following error :

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression :
    Source text : Sql.newInstance(

                    "jdbc:oracle:thin:@ip:1521:XE",

                    "user",

                    "pw",

                    "oracle.jdbc.driver.OracleDriver"

                )

    at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:466)

    at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$18(ReportControler.java:441)

    at com.jaspersoft.studio.editor.preview.view.control.ReportControler$4.run(ReportControler.java:333)

    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:53)

Caused by: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression :
    Source text : Sql.newInstance(

                    "jdbc:oracle:thin:@ip:1521:XE",

                    "user",

                    "pw",

                    "oracle.jdbc.driver.OracleDriver"

                )

    at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:263)

    at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:611)

    at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:579)

    at net.sf.jasperreports.engine.fill.JRFillElement.evaluateExpression(JRFillElement.java:1016)

    at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateSubreport(JRFillSubreport.java:392)

    at net.sf.jasperreports.components.table.fill.FillTableSubreport.evaluateSubreport(FillTableSubreport.java:92)

    at net.sf.jasperreports.components.table.fill.FillTable.evaluate(FillTable.java:118)

    at net.sf.jasperreports.engine.fill.JRFillComponentElement.evaluate(JRFillComponentElement.java:110)

    at net.sf.jasperreports.engine.fill.JRFillElementContainer.evaluate(JRFillElementContainer.java:259)

    at net.sf.jasperreports.engine.fill.JRFillBand.evaluate(JRFillBand.java:456)

    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2067)

    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:788)

    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:298)

    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:152)

    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:963)

    at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120)

    at java.lang.Thread.run(Unknown Source)

Caused by: groovy.lang.MissingPropertyException: No such property: Sql for class: reportFacturation_1406640568882_851084

    at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:50)

    at org.codehaus.groovy.runtime.callsite.GetEffectivePogoPropertySite.getProperty(GetEffectivePogoPropertySite.java:86)

    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callGroovyObjectGetProperty(AbstractCallSite.java:231)

    at reportFacturation_1406640568882_851084.evaluate(calculator_reportFacturation_1406640568882_851084:263)

    at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:250)

    ... 16 more

First of all, thank you for your replies.

groovy.sql.Sql.newInstance("jdbc:oracle:thin:@ip:1521:XE", "user", "pw", "oracle.jdbc.driver.OracleDriver")

seems to be the good expression : it works when i try to connect to my database with groovyconsole.bat.

But i'm always failling to connect to my oracle database from jasperstudio and i dont know why.

i had ojdbc14-1.0.jar to my build path (same jar i used with java jdbc connection and it was working)

i had groovy-sql-2.3.6.jar to my build path ( then all .jar from groovy cause i was "hopeless" but it didnt change anything)

-> In report > properties> language : groovy

-> in my dataset i choose "use an other connection" with the expression :

groovy.sql.Sql.newInstance("jdbc:oracle:thin:@ip:1521:XE", "user", "pw", "oracle.jdbc.driver.OracleDriver")

It still doesn't work i get the following error at the stage "filling report" after preview :

    net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression : 
    Source text : groovy.sql.Sql.newInstance("jdbc:oracle:thin:@ip:1521:XE", "user", "pw", "oracle.jdbc.driver.OracleDriver")

    at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:466)

    at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$18(ReportControler.java:441)

    at com.jaspersoft.studio.editor.preview.view.control.ReportControler$4.run(ReportControler.java:333)

    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:53)

Caused by: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression : 
    Source text : groovy.sql.Sql.newInstance("jdbc:oracle:thin:@ip:1521:XE", "user", "pw", "oracle.jdbc.driver.OracleDriver")

    at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:263)

    at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:611)

    at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:579)

    at net.sf.jasperreports.engine.fill.JRFillElement.evaluateExpression(JRFillElement.java:1016)

    at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateSubreport(JRFillSubreport.java:392)

    at net.sf.jasperreports.components.table.fill.FillTableSubreport.evaluateSubreport(FillTableSubreport.java:92)

    at net.sf.jasperreports.components.table.fill.FillTable.evaluate(FillTable.java:118)

    at net.sf.jasperreports.engine.fill.JRFillComponentElement.evaluate(JRFillComponentElement.java:110)

    at net.sf.jasperreports.engine.fill.JRFillElementContainer.evaluate(JRFillElementContainer.java:259)

    at net.sf.jasperreports.engine.fill.JRFillBand.evaluate(JRFillBand.java:456)

    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2067)

    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:788)

    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:298)

    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:152)

    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:963)

    at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120)

    at java.lang.Thread.run(Unknown Source)

Caused by: java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:@ip:1521:XE

    at java.sql.DriverManager.getConnection(Unknown Source)

    at java.sql.DriverManager.getConnection(Unknown Source)

    at groovy.sql.Sql.newInstance(Sql.java:398)

    at groovy.sql.Sql.newInstance(Sql.java:442)

    at groovy.sql.Sql$newInstance.call(Unknown Source)

    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:45)

    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108)

    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:128)

    at reportFacturation_1406728137596_951810.evaluate(calculator_reportFacturation_1406728137596_951810:263)

    at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:250)

    ... 16 more

I also try with "/XE" but it doesn't work either.

I dont know what i'm doing wrong, any ideas ?

Thanks for help ! :)

Answer 1

Replace this:

Sql.newInstance(
                "jdbc:oracle:thin:@ip:1521:XE",
                "user",
                "pw",
                "oracle.jdbc.driver.OracleDriver"
               )

with this:

groovy.sql.Sql.newInstance("jdbc:oracle:thin:@ip:1521:XE", "user", "pw", 
        "oracle.jdbc.driver.OracleDriver")