title: How to use 'DBCPConnectionPoolLookup' in 'ExecuteGroovyScript' processor?
body: <p>I want to use 'DBCPConnectionPoolLookup' controller service in 'ExecuteGroovyScript' processor. I set '<em>database.name</em>' But I get this Error:</p>
  
  <p><a href="https://i.stack.imgur.com/jvsaI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jvsaI.png" alt="enter image description here"></a></p>
  
  <p>This is 'ExecuteGroovyScript' configs:</p>
  
  <p><a href="https://i.stack.imgur.com/cdDoD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cdDoD.png" alt="enter image description here"></a>
  I found someone who had <a href="https://stackoverflow.com/questions/57883900/how-to-use-dbcpconnectionpoollookup-controllor-service-in-executegroovyscript">same problem</a> and solved it <a href="https://stackoverflow.com/questions/57888447/exception-cannot-get-a-connection-pool-error-timeout-waiting-for-idle-object">here</a>, but his approach to solve the problem is not complete.</p>
  
  <p>I think I should use CTL as described in answer part of <a href="https://stackoverflow.com/questions/57888447/exception-cannot-get-a-connection-pool-error-timeout-waiting-for-idle-object">this question</a> but I got this Error:</p>
  
  <p><a href="https://i.stack.imgur.com/r0DNx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/r0DNx.png" alt="enter image description here"></a></p>
  
  <p>This is my config:</p>
  
  <p><a href="https://i.stack.imgur.com/vuq23.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vuq23.png" alt="enter image description here"></a></p>
  
  <p>Please let me know what is the problem?</p>
  
  <p><strong>update</strong>:
  I removed try/catch from code and got these errors:</p>
  
  <p>Using <code>SQL.db</code>:</p>
  
  <p><a href="https://i.stack.imgur.com/Rkv2S.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Rkv2S.png" alt="enter image description here"></a></p>
  
  <p>Using <code>CTL.sql</code>:</p>
  
  <p><a href="https://i.stack.imgur.com/Qh556.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Qh556.png" alt="enter image description here"></a></p>
  
  <p>Full log:</p>
  
  <pre><code>2019-10-17 22:27:12,685 ERROR [Timer-Driven Process Thread-10] o.a.n.p.groovyx.ExecuteGroovyScript ExecuteGroovyScript[id=bb9d644c-016d-1000-5485-c915da52bbfb] org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object: org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
  org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
      at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:474)
      at org.apache.nifi.dbcp.DBCPService.getConnection(DBCPService.java:49)
      at sun.reflect.GeneratedMethodAccessor1514.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:87)
      at com.sun.proxy.$Proxy100.getConnection(Unknown Source)
      at org.apache.nifi.dbcp.DBCPService$getConnection.call(Unknown Source)
      at Scriptffffffffa8c699bc.run(Scriptffffffffa8c699bc.groovy:8)
      at org.apache.nifi.processors.groovyx.ExecuteGroovyScript.onTrigger(ExecuteGroovyScript.java:449)
      at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
      at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1162)
      at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:209)
      at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
      at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
      at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      at java.lang.Thread.run(Thread.java:748)
  Caused by: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
      at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:142)
      at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1563)
      at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:470)
      ... 21 common frames omitted
  Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
      at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:451)
      at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:365)
      at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:134)
      ... 23 common frames omitted
  2019-10-17 22:27:13,194 ERROR [Timer-Driven Process Thread-10] o.a.n.p.groovyx.ExecuteGroovyScript ExecuteGroovyScript[id=bb9d644c-016d-1000-5485-c915da52bbfb] org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object: org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
  org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
      at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:474)
      at org.apache.nifi.dbcp.DBCPService.getConnection(DBCPService.java:49)
      at sun.reflect.GeneratedMethodAccessor1514.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:87)
      at com.sun.proxy.$Proxy100.getConnection(Unknown Source)
      at org.apache.nifi.dbcp.DBCPService$getConnection.call(Unknown Source)
      at Scriptffffffffa8c699bc.run(Scriptffffffffa8c699bc.groovy:8)
      at org.apache.nifi.processors.groovyx.ExecuteGroovyScript.onTrigger(ExecuteGroovyScript.java:449)
      at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
      at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1162)
      at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:209)
      at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
      at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
      at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      at java.lang.Thread.run(Thread.java:748)
  Caused by: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
      at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:142)
      at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1563)
      at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:470)
      ... 21 common frames omitted
  Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
      at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:451)
      at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:365)
      at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:134)
      ... 23 common frames omitted
  2019-10-17 22:31:58,045 WARN [Timer-Driven Process Thread-4] o.a.n.controller.tasks.ConnectableTask Administratively Yielding ExecuteGroovyScript[id=bb9d644c-016d-1000-5485-c915da52bbfb] due to uncaught Exception: java.lang.ClassCastException: com.sun.proxy.$Proxy128 cannot be cast to org.apache.nifi.processors.groovyx.sql.OSql
  java.lang.ClassCastException: com.sun.proxy.$Proxy128 cannot be cast to org.apache.nifi.processors.groovyx.sql.OSql
      at org.apache.nifi.processors.groovyx.ExecuteGroovyScript.onFinitSQL(ExecuteGroovyScript.java:371)
      at org.apache.nifi.processors.groovyx.ExecuteGroovyScript.onTrigger(ExecuteGroovyScript.java:464)
      at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
      at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1162)
      at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:209)
      at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
      at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
      at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      at java.lang.Thread.run(Thread.java:748)
  2019-10-17 22:31:59,045 ERROR [Timer-Driven Process Thread-2] o.a.n.p.groovyx.ExecuteGroovyScript ExecuteGroovyScript[id=bb9d644c-016d-1000-5485-c915da52bbfb] org.apache.nifi.processor.exception.ProcessException: Attributes must contain an attribute name 'database.name': org.apache.nifi.processor.exception.ProcessException: Attributes must contain an attribute name 'database.name'
  org.apache.nifi.processor.exception.ProcessException: Attributes must contain an attribute name 'database.name'
      at org.apache.nifi.dbcp.DBCPConnectionPoolLookup.getConnection(DBCPConnectionPoolLookup.java:124)
      at sun.reflect.GeneratedMethodAccessor1514.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:87)
      at com.sun.proxy.$Proxy128.getConnection(Unknown Source)
      at org.apache.nifi.processors.groovyx.ExecuteGroovyScript.onInitSQL(ExecuteGroovyScript.java:339)
      at org.apache.nifi.processors.groovyx.ExecuteGroovyScript.onTrigger(ExecuteGroovyScript.java:439)
      at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
      at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1162)
      at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:209)
      at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
      at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
      at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      at java.lang.Thread.run(Thread.java:748)
  </code></pre>
  
  <p>Script Code for <code>SQL.db</code>:</p>
  
  <pre><code>flowFile = session.get()
  if(!flowFile) return
  def RawData =flowFile.read().getText("UTF-8")
  def JobId = flowFile.ExtractJobId
  def params = [RawData,JobId]
  def result = SQL.db.executeInsert("INSERT INTO ExtractFiles (RawData,JobId,CreateTimestamp,UpdateTimestamp) VALUES (?,?,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)",params)
  flowFile.'ExtractFileId' = result[0][0]
  REL_SUCCESS &lt;&lt; flowFile
  </code></pre>
  
  <p>Script code for <code>CTL.sql</code>:</p>
  
  <pre><code>import groovy.sql.Sql
  
  flowFile = session.get()
  if(!flowFile) return
  def RawData =flowFile.read().getText("UTF-8")
  def JobId = flowFile.ExtractJobId
  def params = [RawData,JobId]
  def myDbConnection = CTL.sql.getConnection(flowFile.getAttributes())
  def myClientDb = new Sql(myDbConnection)
  def result = myClientDb.executeInsert("INSERT INTO ExtractFiles (RawData,JobId,CreateTimestamp,UpdateTimestamp) VALUES (?,?,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)",params)
  flowFile.'ExtractFileId' = result[0][0]
  REL_SUCCESS &lt;&lt; flowFile
  </code></pre>