Ok, so after a few more research I think I got it working. The tricky was
the fact that I wasn't closing the connection hence exhausting the pool
very quickly. I didn't notice this before because most code I saw didn't
explicitly close the connection but once I did it my scripted processor
worked as expected. I'm just finishing some rough points and once I finish
it I'll share it with everyone in a gist.
Thanks everyone so far! =)
2017-11-16 13:49 GMT-02:00 Eric Chaves <e...@uolet.com>:
> Hi guys,
>
> I've made a lot of changes on my script processor and now I'm properly
> getting an instance of DBCPService however when I try to use the connection
> I got the error org.apache.commons.dbcp.SQLNestedException: Cannot get a
> connection, pool error Timeout waiting for idle object.
>
> I know the connection is ok because the upstream processor uses the same
> DBCP instance to access the same database.
>
> the code I'm using on my InvokeScriptedProcessor is:
>
> void onTrigger(ProcessContext context, ProcessSessionFactory
> sessionFactory) throws ProcessException {
> def session = sessionFactory.createSession()
> def flowFile = session.get()
> if(!flowFile) return
> def properties = context.getProperties()
> log.info("Properties {}", properties)
> def String lookupKey = context.getProperty(LOOKUP_FIELD)?.
> evaluateAttributeExpressions()?.getValue()
> log.info('Lookup key {}', lookupKey)
> def dbcpService = context.getProperty(DBCP_
> SERVICE).asControllerService(DBCPService)
> def Integer queryTimeout = context.getProperty(QUERY_
> TIMEOUT).asTimePeriod(TimeUnit.SECONDS).intValue()
> try
> {
> def con = dbcpService.getConnection()
> def st = con.createStatement()
> st.setQueryTimeout(queryTimeout)
> def selectQuery = "select * from PERSONS where id=${lookupKey}"
> log.info("Executing query {}", selectQuery)
> boolean results = st.execute(selectQuery)
> log.info("Results {}-{}", lookupKey, results)
> session.transfer(flowFile, REL_SUCCESS)
> session.commit()
> } catch (final Throwable t) {
> log.error('{} failed to process due to {}', [this, t] as Object[])
> session.rollback(true)
> throw t
> }
> }
>
> Any hints?
>
