How to execute SQL statement in Pipeline script of Jenkins?

Question

I have created on pipeline and I want to execute one sql query in it. I have written following statements(Only two Lines of code, no imports/Class etc.) and throws error while executing it.

import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:mysql://myIP:3306/dbName", "uname","password", "com.mysql.jdbc.Driver")
sql.execute "select count(*) from TableName"

I am getting this error

org.jenkinsci.plugins.scriptsecurity.sandbox.RejectedAccessException: Scripts not permitted to use staticMethod groovy.sql.Sql newInstance java.lang.String java.lang.String java.lang.String java.lang.String
    at org.jenkinsci.plugins.scriptsecurity.sandbox.whitelists.StaticWhitelist.rejectStaticMethod(StaticWhitelist.java:174)
    at org.jenkinsci.plugins.scriptsecurity.sandbox.groovy.SandboxInterceptor.onStaticCall(SandboxInterceptor.java:142)
    at org.kohsuke.groovy.sandbox.impl.Checker$2.call(Checker.java:180)
    at org.kohsuke.groovy.sandbox.impl.Checker.checkedStaticCall(Checker.java:177)
    at org.kohsuke.groovy.sandbox.impl.Checker.checkedCall(Checker.java:91)
    at com.cloudbees.groovy.cps.sandbox.SandboxInvoker.methodCall(SandboxInvoker.java:15)
    at WorkflowScript.run(WorkflowScript:3)
    at ___cps.transform___(Native Method)
    at com.cloudbees.groovy.cps.impl.ContinuationGroup.methodCall(ContinuationGroup.java:55)
    at com.cloudbees.groovy.cps.impl.FunctionCallBlock$ContinuationImpl.dispatchOrArg(FunctionCallBlock.java:106)
    at com.cloudbees.groovy.cps.impl.FunctionCallBlock$ContinuationImpl.fixArg(FunctionCallBlock.java:79)
    at sun.reflect.GeneratedMethodAccessor841.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at com.cloudbees.groovy.cps.impl.ContinuationPtr$ContinuationImpl.receive(ContinuationPtr.java:72)
    at com.cloudbees.groovy.cps.impl.ConstantBlock.eval(ConstantBlock.java:21)
    at com.cloudbees.groovy.cps.Next.step(Next.java:58)
    at com.cloudbees.groovy.cps.Continuable.run0(Continuable.java:154)
    at org.jenkinsci.plugins.workflow.cps.SandboxContinuable.access$001(SandboxContinuable.java:19)
    at org.jenkinsci.plugins.workflow.cps.SandboxContinuable$1.call(SandboxContinuable.java:33)
    at org.jenkinsci.plugins.workflow.cps.SandboxContinuable$1.call(SandboxContinuable.java:30)
    at org.jenkinsci.plugins.scriptsecurity.sandbox.groovy.GroovySandbox.runInSandbox(GroovySandbox.java:108)
    at org.jenkinsci.plugins.workflow.cps.SandboxContinuable.run0(SandboxContinuable.java:30)
    at org.jenkinsci.plugins.workflow.cps.CpsThread.runNextChunk(CpsThread.java:164)
    at org.jenkinsci.plugins.workflow.cps.CpsThreadGroup.run(CpsThreadGroup.java:277)
    at org.jenkinsci.plugins.workflow.cps.CpsThreadGroup.access$000(CpsThreadGroup.java:77)
    at org.jenkinsci.plugins.workflow.cps.CpsThreadGroup$2.call(CpsThreadGroup.java:186)
    at org.jenkinsci.plugins.workflow.cps.CpsThreadGroup$2.call(CpsThreadGroup.java:184)
    at org.jenkinsci.plugins.workflow.cps.CpsVmExecutorService$2.call(CpsVmExecutorService.java:47)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at hudson.remoting.SingleLaneExecutorService$1.run(SingleLaneExecutorService.java:112)
    at jenkins.util.ContextResettingExecutorService$1.run(ContextResettingExecutorService.java:28)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
Finished: FAILURE

Pls help. Thanks in Advance.

Solution

From the error it looks like you are running the script in sandbox mode, in which case there are a lot of limitation what you can execute in the script. For example the following wont work neither:

echo binding.hasVariable("test")

The things you can do:

• If you are not hosting the Jenkins yourself you cannot do much from the pipeline script itself, you have to create a separate process to do the select you want to do and pass back the result to the pipeline script. You can do this by adding a groovy script to the slave and executing it from the pipeline, for example.

All scenarios below assume that you are hosing Jenkins yourself

• If you are using Pipeline script definition you have the option to run the script not in sandbox mode (uncheck Use Groovy Sandbox in the job configuration page). In this case if you are an admin it should work just fine. If you are not an admin follow the advice below

• If you are using Pipeline script from SCM definition, then the script will be executed in sandbox mode and you will also encounter the error you posted. Then an admin needs to go to Manage Jenkins » In-process Script Approval and approve the method call that was denied (just press the Approve button)

Note: The above was tested on Jenkins version: 2.7.1

Comments

[Peter]

Thanks for the reply Gergely. but I m not that much familiar with Jenkins. I just created on Pipeline > Navigate to 'Configure' page > Add above code in 'Pipeline - Script' > Apply and Build it. - Groovy Sandbox option in the job configuration page is already unchecked. - I have approved Manage Jenkins » In-process Script Approval method calls. While running using (Build Now) it gives this error java.lang.ClassNotFoundException: com.mysql.jdbc.Driver