Ok, so it appears that DBCP does not like you to get access to the underlying
connection without additional configuration, but Java does since
java.sql.Connection extends java.sql.Wrapper from which you can do something
like this:
Connection dbConnection= d.getConnection();
OracleConnection orcConnection = null;
if (dbConnection.isWrapperFor(OracleConnection.class)) {
orcConnection = dbConnection.unwrap(OracleConnection.class);
}
. . .
Let me know
Cheers
Oleg
On Mar 15, 2017, at 1:33 PM, Anil Rai
<anilrain...@gmail.com<mailto:anilrain...@gmail.com>> wrote:
Hi Oleg, we tried and no luck. This is another example where we are seeing
similar issue. Attached txt has the java code as well as the log.
Thanks in advance
Anil
On Wed, Mar 15, 2017 at 10:02 AM, Anil Rai
<anilrain...@gmail.com<mailto:anilrain...@gmail.com>> wrote:
Thanks Oleg. Makes sense. Will try and keep you posted.
Regards
Anil
On Wed, Mar 15, 2017 at 9:56 AM, Oleg Zhurakousky
<ozhurakou...@hortonworks.com<mailto:ozhurakou...@hortonworks.com>> 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
> <anilrain...@gmail.com<mailto:anilrain...@gmail.com>> 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<http://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 <
> ozhurakou...@hortonworks.com<mailto:ozhurakou...@hortonworks.com>> 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
>> <anilrain...@gmail.com<mailto:anilrain...@gmail.com><mailto:
>> anilrain...@gmail.com<mailto:anilrain...@gmail.com>>> 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 <
>> ozhurakou...@hortonworks.com<mailto:ozhurakou...@hortonworks.com><mailto:ozhurakou...@hortonworks.com<mailto:ozhurakou...@hortonworks.com>>>
>> 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
>>> <anilrain...@gmail.com<mailto:anilrain...@gmail.com><mailto:
>> anilrain...@gmail.com<mailto:anilrain...@gmail.com>>> 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><http:/
>> /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 <
>>> ozhurakou...@hortonworks.com<mailto:ozhurakou...@hortonworks.com><mailto:ozhurakou...@hortonworks.com<mailto:ozhurakou...@hortonworks.com>>>
>> 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
>>>>> <anilrain...@gmail.com<mailto:anilrain...@gmail.com><mailto:
>> anilrain...@gmail.com<mailto:anilrain...@gmail.com>>> 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 <
>> r...@windofkeltia.com<mailto:r...@windofkeltia.com><mailto:r...@windofkeltia.com<mailto:r...@windofkeltia.com>>>
>>>>> 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><http://jdbc<http://jdbc/>:
>> oracle:thin:@xxx-yyy.zzz.com:1521/DB1<http://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
>>>>>>>
>>>>>>>
>>>>>>
>>>>
>>>>
>>
>>
>>
>>
<oracle_error.txt>
