Thanks Oleg. Makes sense. Will try and keep you posted. Regards Anil
On Wed, Mar 15, 2017 at 9:56 AM, Oleg Zhurakousky < [email protected]> wrote: > Anil > > Thank you for details. That does help a lot. > > First, I want to make sure that it is clear that this is not a NiFi issue, > since the problem is specific to the combination of DBCP and Oracle and the > expectations between the two. > > Seems like Oracle JDBC connection is wrapped in an implementation specific > class (DBCP in this case I assume). > It is my believe that you need to obtain reference to native Oracle > connection to avoid "JMS-112: Connection is invalid". > So, I think you need to try to cast your Connection object to DBCPs > DelegatingConnection and then do something like this: > > DelegatingConnection wrappedConn = (DelegatingConnection)con; > OracleConnection ocon = null ; > if (wrappedConn != null) > ocon = (OracleConnection) wrappedConn.getDelegate(); > . . . > > Let me know how it goes > > Cheers > Oleg > > > On Mar 15, 2017, at 9:20 AM, Anil Rai <[email protected]> wrote: > > > > 2017-03-14 16:50:42,312 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) > > 2017-03-14 16:50:42,313 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) > > 2017-03-14 16:50:42,313 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > java.util.concurrent.ScheduledThreadPoolExecutor$ > ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) > > 2017-03-14 16:50:42,313 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > java.util.concurrent.ScheduledThreadPoolExecutor$ > ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) > > 2017-03-14 16:50:42,313 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > java.util.concurrent.ThreadPoolExecutor.runWorker( > ThreadPoolExecutor.java:1142) > > 2017-03-14 16:50:42,313 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > java.util.concurrent.ThreadPoolExecutor$Worker.run( > ThreadPoolExecutor.java:617) > > 2017-03-14 16:50:42,313 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at java.lang.Thread.run(Thread.java:745) > > 2017-03-14 16:50:43,567 INFO [NiFi logging handler] > org.apache.nifi.StdOut > > Databse Connection :- jdbc:oracle:thin:@xxxog.yy.com:1521/DB1, > > UserName=user, Oracle JDBC driver > > 2017-03-14 16:50:43,567 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > oracle.AQ.AQException: JMS-112: Connection is invalid > > 2017-03-14 16:50:43,567 INFO [NiFi logging handler] > org.apache.nifi.StdOut > > AQ Driver Class ---> oracle.AQ.AQOracleDriver > > 2017-03-14 16:50:43,567 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > 2017-03-14 16:50:43,568 INFO [NiFi logging handler] > org.apache.nifi.StdOut > > Aq Sesssion ---> null > > 2017-03-14 16:50:43,568 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > 2017-03-14 16:50:43,568 INFO [NiFi logging handler] > org.apache.nifi.StdOut > > Queue Owner ---> APPS > > 2017-03-14 16:50:43,568 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at oracle.AQ.AQDriverManager.createAQSession( > AQDriverManager.java:193) > > 2017-03-14 16:50:43,569 INFO [NiFi logging handler] > org.apache.nifi.StdOut > > QueueName ---> WF_BPEL_Q > > 2017-03-14 16:50:43,569 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > com.oracle.xx.connector.processors.xx_SCon_ConsumeAQ.xx_Scon_ConsumeAQ. > createSession(xx_Scon_ConsumeAQ.java:183) > > 2017-03-14 16:50:43,569 INFO [NiFi logging handler] > org.apache.nifi.StdOut > > EventName ---> oracle.apps.ar.hz.CustAcctSite.update > > 2017-03-14 16:50:43,569 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > com.oracle.xx.connector.processors.XX_SCon_ConsumeAQ. > XX_Scon_ConsumeAQ.onTrigger(XX_Scon_ConsumeAQ.java:254) > > 2017-03-14 16:50:43,569 INFO [NiFi logging handler] > org.apache.nifi.StdOut > > Consumer Name ---> ORA_69242HHG6SR4CGPI6KSJ8DI36T > > 2017-03-14 16:50:43,569 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > org.apache.nifi.processor.AbstractProcessor.onTrigger( > AbstractProcessor.java:27) > > 2017-03-14 16:50:43,569 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > org.apache.nifi.controller.StandardProcessorNode.onTrigger( > StandardProcessorNode.java:1099) > > 2017-03-14 16:50:43,569 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call( > ContinuallyRunProcessorTask.java:136) > > 2017-03-14 16:50:43,570 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call( > ContinuallyRunProcessorTask.java:47) > > 2017-03-14 16:50:43,570 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run( > TimerDrivenSchedulingAgent.java:132) > > 2017-03-14 16:50:43,570 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) > > 2017-03-14 16:50:43,570 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) > > 2017-03-14 16:50:43,570 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > java.util.concurrent.ScheduledThreadPoolExecutor$ > ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) > > 2017-03-14 16:50:43,570 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > java.util.concurrent.ScheduledThreadPoolExecutor$ > ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) > > 2017-03-14 16:50:43,570 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > java.util.concurrent.ThreadPoolExecutor.runWorker( > ThreadPoolExecutor.java:1142) > > 2017-03-14 16:50:43,570 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at > > java.util.concurrent.ThreadPoolExecutor$Worker.run( > ThreadPoolExecutor.java:617) > > 2017-03-14 16:50:43,570 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > at java.lang.Thread.run(Thread.java:745) > > 2017-03-14 16:50:43,570 ERROR [NiFi logging handler] > org.apache.nifi.StdErr > > java.lang.NullPointerException > > > > > > On Wed, Mar 15, 2017 at 9:09 AM, Oleg Zhurakousky < > > [email protected]> wrote: > > > >> Anil > >> > >> Unfortunately the attachment didn’t come thru. Perhaps you can just > paste > >> the relevant part of the exception. > >> > >> Cheers > >> Oleg > >> > >> On Mar 15, 2017, at 8:58 AM, Anil Rai <[email protected]<mailto: > >> [email protected]>> wrote: > >> > >> Hi Oleg, Thanks. attached is the log. Let me know if you want us to > change > >> the log levels and re-run and send you additional logs? > >> > >> > >> On Tue, Mar 14, 2017 at 5:12 PM, Oleg Zhurakousky < > >> [email protected]<mailto:[email protected]>> > wrote: > >> Anil > >> > >> I understand that you are having an issue and we are here to help, but > we > >> can only do this if you help us just a little more, so it would be very > >> helpful if you provided a stack trace (I understand if you have to mask > >> sensitive information). > >> The “. . .fails saying cannot create AQSession. . .” could be due to > >> various reasons and until we see the stack trace everything here would > be > >> speculation. > >> I hope you understand > >> > >> Cheers > >> Oleg > >> > >>> On Mar 14, 2017, at 4:59 PM, Anil Rai <[email protected]<mailto: > >> [email protected]>> wrote: > >>> > >>> Here is the behaviour that we have seen so for....hope this helps > >>> > >>> 1. When we run the java code in eclipse, it works and this is the > >>> connection object that is printed -> > >>> oracle.jdbc.driver.T4CConnection@6f75e721 > >>> 2. When we hard code all the values as mentioned in my first email in > a > >>> custom processor, deploy that. It works as well. The above connection > >>> object gets printed. > >>> 3. When we change the code in the custom processor to use the DBCP > >>> connection service, deploy that. The connection object that gets > >> printed is > >>> jdbc:oracle:thin:@oged-scan.og.ge.com:1521/ORPOGPB1<http:/ > >> /jdbc:oracle:thin:@oged-scan.og.ge.com:1521/ORPOGPB1> and this does not > >>> work. aqSession = AQDriverManager.createAQSession(connection) fails > >>> saying cannot create AQSession. > >>> > >>> Thanks > >>> Anil > >>> > >>> > >>> On Tue, Mar 14, 2017 at 4:13 PM, Oleg Zhurakousky < > >>> [email protected]<mailto:[email protected]>> > >> wrote: > >>> > >>>> Anil > >>>> > >>>> When you say "it does not like the connection object. . .” what do you > >>>> mean by that? > >>>> Can you please provide stack trace or some other details? > >>>> > >>>> Cheers > >>>> Oleg > >>>> > >>>>> On Mar 14, 2017, at 4:06 PM, Anil Rai <[email protected]<mailto: > >> [email protected]>> wrote: > >>>>> > >>>>> Thanks Russ. Yes, we are doing exactly the same thing. > >>>>> > >>>>> driverClass = context.getProperty(DRIVER_CLASS).getValue(); > >>>>> queueName = context.getProperty(QUEUE_NAME).getValue(); > >>>>> databaseSchema = context.getProperty(DATABASE_ > >> SCHEMA).getValue(); > >>>>> consumerName = context.getProperty(CONSUMER_NAME).getValue(); > >>>>> eventName = context.getProperty(EVENT_NAME).getValue(); > >>>>> DBCPService connection = > >>>>> context.getProperty(JDBC_CONNECTION_POOL).asControllerService( > >>>> DBCPService.class); > >>>>> Connection conn = connection.getConnection(); > >>>>> * aqSession = AQDriverManager.createAQSession(connection);* > >>>>> > >>>>> The underlined code above fails as it does not like the connection > >> object > >>>>> that is returned by the DBCPService. > >>>>> > >>>>> Regards > >>>>> Anil > >>>>> > >>>>> > >>>>> On Tue, Mar 14, 2017 at 2:43 PM, Russell Bateman < > >> [email protected]<mailto:[email protected]>> > >>>>> wrote: > >>>>> > >>>>>> Anil, > >>>>>> > >>>>>> Typically, your custom processor should have a property, something > >> like > >>>>>> > >>>>>> public static final PropertyDescriptor DBCP_SERVICE = new > >>>>>> PropertyDescriptor.Builder() > >>>>>> .name("Database Connection Pooling Service") > >>>>>> .description("The Controller Service that is used to obtain > >>>>>> connection to database") > >>>>>> .required(true) > >>>>>> .identifiesControllerService(DBCPService.class) > >>>>>> .build(); > >>>>>> > >>>>>> When your NiFi user sets up the flow, he or she puts a reference to > >>>> NiFi's > >>>>>> DBCPConnectionPool in it. In configuring that (a ControllerService, > >> you > >>>>>> tell it that it's Oracle, location, etc.) > >>>>>> > >>>>>> Then, your onTrigger() code would do something like this: > >>>>>> > >>>>>> final DBCPService dbcpService = context.getProperty(DBCP_SERVI > >>>>>> CE).asControllerService(DBCPService.class); > >>>>>> > >>>>>> > >>>>>> and later... > >>>>>> > >>>>>> > >>>>>> try (final Connection connection = dbcpService.getConnection()) > >>>>>> { > >>>>>> try (final Statement stmt = connection.createStatement( > >>>> ResultSet.TYPE_FORWARD_ONLY, > >>>>>> ResultSet.CONCUR_READ_ONLY)) > >>>>>> > >>>>>> etc. > >>>>>> > >>>>>> Does this help? > >>>>>> > >>>>>> Russ > >>>>>> > >>>>>> > >>>>>> > >>>>>> On 03/14/2017 11:54 AM, Anil Rai wrote: > >>>>>> > >>>>>>> We have a use case to connect to oracle database and subscribe to > >>>> Advanced > >>>>>>> Queuing (https://docs.oracle.com/cd/A58617_01/server.804/a58241/ > >>>> ch_aq.htm > >>>>>>> ). > >>>>>>> Below is the java snippet to establish this connection from a java > >>>> client. > >>>>>>> We can run this in eclipse and consume message from the advanced > >> queue. > >>>>>>> ************** > >>>>>>> Class.forName("oracle.jdbc.driver.OracleDriver"); > >>>>>>> connection = DriverManager.getConnection(" > >>>>>>> jdbc:oracle:thin:@xxx-yyy.zzz.com:1521/DB1<http://jdbc: > >> oracle:thin:@xxx-yyy.zzz.com:1521/DB1>","user", "pwd"); > >>>>>>> connection.setAutoCommit(true); > >>>>>>> Class.forName("oracle.AQ.AQOracleDriver"); > >>>>>>> aqSession = AQDriverManager.createAQSession(connection); > >>>>>>> System.out.println("AQ Session --->" + aqSession); > >>>>>>> ******************** > >>>>>>> > >>>>>>> We have created a custom processor in Nifi. This processor is > getting > >>>> the > >>>>>>> connection string using getConnection function of Standard DBCP > >>>> service. > >>>>>>> The problem is, the connection object that is retrieved from > eclipse > >>>>>>> versus > >>>>>>> what is returned from DBCP service is different. We have made sure > we > >>>> are > >>>>>>> referring to the same jar both in eclipse and Nifi (ojdbc7.jar) > >>>>>>> It fails @ aqSession = AQDriverManager. > createAQSession(connection); > >>>>>>> The connection object that comes from DBCP is not what is expected > by > >>>>>>> AQDriverManager. > >>>>>>> > >>>>>>> Any help is greatly appreciated. > >>>>>>> > >>>>>>> Thanks > >>>>>>> Anil > >>>>>>> > >>>>>>> > >>>>>> > >>>> > >>>> > >> > >> > >> > >> > >
