DBCP Connection Pooling using multiple OJDBC Drivers

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

DBCP Connection Pooling using multiple OJDBC Drivers

Mohammed Nadeem
I'm building a custom processor where i need to execute PL/SQL Procedures
with the help of DBCP Connection Pooling Controller Service. The custom
processor which executes PL/SQL Procedures needs to connect to different
Oracle Databases like 11g and Oracle 8i.

The Problem i'm facing here is that these oracle databases needs different
ojdbc jars . For example Oracle 11g needs ojdbc7.jar and Oracle 8i needs
ojdbc14.jar . The Custom processor needs ojdbc7.jar as maven dependency to
execute complex Oracle jdbc types such as ARRAY ,STRUCT etc.  When I load
two dbcp controller services which uses different ojdbc.jar's for the same
custom processor it is working for one oracle database but not for other.

Detail Description.

If I connect to Oracle Database 11g where i give diver location as
ojdbc7.jar in dbcp controller service then its throwing an error saying "
java.sql.Exception : can't wrapped connection to requested interface".
To resolve this issue i added ojdbc7.jar in nifi lib folder and the error
went.

Now, when i connect to Oracle 8i with ojdbc14.jar in dbcp controller
service.. It is throwing an error saying " ArrayOutOfBound Exception 7" . I
guess it is trying to use incompatible jar which was given in the lib folder
(ojdbc7.jar) . If I add ojdbc14.jar in the lib then earlier one is not
working giving same error "  java.sql.Exception : can't wrapped connection
to requested interface".

Could you please help me out there.. Not Sure how nifi classloader works ..

Thanks  in advance....


Regards,
Nadeem

 



--
Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: DBCP Connection Pooling using multiple OJDBC Drivers

Milan Das
Hi Nadeem,
I think you can do very similar to Kafka implementation. For different version NIFI have two different processor for Kafka 10 and 11  “PublishKafka_0_10” and “PublishKafka_0_11”

Regards,


Milan Das
Sr. System Architect
email: [hidden email]
<https://www.linkedin.com/in/milandas/>
www.interset.com <http://www.interset.com/>
 


On 12/27/17, 7:08 AM, "Mohammed Nadeem" <[hidden email]> wrote:

    I'm building a custom processor where i need to execute PL/SQL Procedures
    with the help of DBCP Connection Pooling Controller Service. The custom
    processor which executes PL/SQL Procedures needs to connect to different
    Oracle Databases like 11g and Oracle 8i.
   
    The Problem i'm facing here is that these oracle databases needs different
    ojdbc jars . For example Oracle 11g needs ojdbc7.jar and Oracle 8i needs
    ojdbc14.jar . The Custom processor needs ojdbc7.jar as maven dependency to
    execute complex Oracle jdbc types such as ARRAY ,STRUCT etc.  When I load
    two dbcp controller services which uses different ojdbc.jar's for the same
    custom processor it is working for one oracle database but not for other.
   
    Detail Description.
   
    If I connect to Oracle Database 11g where i give diver location as
    ojdbc7.jar in dbcp controller service then its throwing an error saying "
    java.sql.Exception : can't wrapped connection to requested interface".
    To resolve this issue i added ojdbc7.jar in nifi lib folder and the error
    went.
   
    Now, when i connect to Oracle 8i with ojdbc14.jar in dbcp controller
    service.. It is throwing an error saying " ArrayOutOfBound Exception 7" . I
    guess it is trying to use incompatible jar which was given in the lib folder
    (ojdbc7.jar) . If I add ojdbc14.jar in the lib then earlier one is not
    working giving same error "  java.sql.Exception : can't wrapped connection
    to requested interface".
   
    Could you please help me out there.. Not Sure how nifi classloader works ..
   
    Thanks  in advance....
   
   
    Regards,
    Nadeem
   
     
   
   
   
    --
    Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
   


Reply | Threaded
Open this post in threaded view
|

Re: DBCP Connection Pooling using multiple OJDBC Drivers

Mohammed Nadeem
Thanks Milan, I have doubt here.

When i execute my custom processor keeping ojdbc14.jar for Oracle 8i in my
nifi-lib folder then custom processor works fine, but when i execute
processor for Oracle 11 g then its throwing me an error "NoSuchMethodError
Oracle.jdbc.OracleConnection.createOracleArray()" though i have ojdbc7.jar
as my dependency in my maven setup. I think it is trying to load only
ojdbc14.jar from where its not able to find that method ( I decompiled the
ojdbc14.jar and it doesn't support that method) only ojdbc7.jar supports. If
do reverse where i place ojdbc7.jar first then oracle 8i is throwing error.

I feel like dbcp connection pool is loading only one driver which will be
enabled first. if ojdbc7.jar is enabled then only that will be used through
out Nifi instance.. Loading other ojdbc driver will be useless i feel. Not
Sure whether im correct or not.

Could you please help me here.



--
Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: DBCP Connection Pooling using multiple OJDBC Drivers

Mark Payne
Nadeem,

You should not be placing any jar file into nifi’s lib directory. Doing so will load the jar into all classloaders in the system. Instead, you should place the jar outside of the lib/ directory and then configure the controller service to point to the jar file, wherever it is placed.

Thanks
-Mark

Sent from my iPhone

> On Dec 27, 2017, at 9:33 AM, Mohammed Nadeem <[hidden email]> wrote:
>
> Thanks Milan, I have doubt here.
>
> When i execute my custom processor keeping ojdbc14.jar for Oracle 8i in my
> nifi-lib folder then custom processor works fine, but when i execute
> processor for Oracle 11 g then its throwing me an error "NoSuchMethodError
> Oracle.jdbc.OracleConnection.createOracleArray()" though i have ojdbc7.jar
> as my dependency in my maven setup. I think it is trying to load only
> ojdbc14.jar from where its not able to find that method ( I decompiled the
> ojdbc14.jar and it doesn't support that method) only ojdbc7.jar supports. If
> do reverse where i place ojdbc7.jar first then oracle 8i is throwing error.
>
> I feel like dbcp connection pool is loading only one driver which will be
> enabled first. if ojdbc7.jar is enabled then only that will be used through
> out Nifi instance.. Loading other ojdbc driver will be useless i feel. Not
> Sure whether im correct or not.
>
> Could you please help me here.
>
>
>
> --
> Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: DBCP Connection Pooling using multiple OJDBC Drivers

Mohammed Nadeem
Thanks Mark that really helps. But I'm facing below issue

As you said i loaded drivers externally and it was successful for oracle8i
where i executed simple stored procedure and it is working fine but when i
try to execute stored procedure which has array type for Oracle 11g then im
getting this error ( can't wrap connection to oracle connection )

java.lang.AbstractMethodError: null
        at
org.apache.commons.dbcp.DelegatingConnection.unwrap(DelegatingConnection.java:553)
~[na:na]
        at
org.apache.commons.dbcp.DelegatingConnection.unwrap(DelegatingConnection.java:553)
~[na:na]
        at
ExecuteProcedure.executeStoredProcedure(GE_Scon_ExecuteProcedure.java:584)
~[na:na]
        at ExecuteProcedure.onTrigger(GE_Scon_ExecuteProcedure.java:382) ~[na:na]

It can't unwrap the connection to oracle connection . Earlier i had resolved
this issue by placing ojdbc7.jar in my nifi-lib folder then it started to
work.. But as you said we should not place any jars in the nifi lib folder
..
So now its giving this error. Please help here

Thanks,
Nadeem
Software Engineering Specialist



--
Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: DBCP Connection Pooling using multiple OJDBC Drivers

Koji Kawamura-2
Hi Nadeem,

Did you try specifying a external directory instead of an exact jar
location, then put multiple versions of Jar there? This way
DBCPConnectionPool can utilize multiple jars. This would provide the
similar effect with putting one in NiFi lib dir.

If that doesn't work, an alternative approach would be setup different
DBCPConnectionPool instance per required JDBC driver version then
switching those by some condition.

I haven't tested above myself, so no guarantee, but just wanted to
share my thoughts.

Thanks,
Koji

On Thu, Dec 28, 2017 at 12:14 AM, Mohammed Nadeem <[hidden email]> wrote:

> Thanks Mark that really helps. But I'm facing below issue
>
> As you said i loaded drivers externally and it was successful for oracle8i
> where i executed simple stored procedure and it is working fine but when i
> try to execute stored procedure which has array type for Oracle 11g then im
> getting this error ( can't wrap connection to oracle connection )
>
> java.lang.AbstractMethodError: null
>         at
> org.apache.commons.dbcp.DelegatingConnection.unwrap(DelegatingConnection.java:553)
> ~[na:na]
>         at
> org.apache.commons.dbcp.DelegatingConnection.unwrap(DelegatingConnection.java:553)
> ~[na:na]
>         at
> ExecuteProcedure.executeStoredProcedure(GE_Scon_ExecuteProcedure.java:584)
> ~[na:na]
>         at ExecuteProcedure.onTrigger(GE_Scon_ExecuteProcedure.java:382) ~[na:na]
>
> It can't unwrap the connection to oracle connection . Earlier i had resolved
> this issue by placing ojdbc7.jar in my nifi-lib folder then it started to
> work.. But as you said we should not place any jars in the nifi lib folder
> ..
> So now its giving this error. Please help here
>
> Thanks,
> Nadeem
> Software Engineering Specialist
>
>
>
> --
> Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: DBCP Connection Pooling using multiple OJDBC Drivers

Mohammed Nadeem
Thanks Koji. But giving a directory of multiple drivers is throwing an error
"can't load database driver " and i verified that with dbcp code and it
specifically checks for ".jar extension".

                final ClassLoader classLoader =
ClassLoaderUtils.getCustomClassLoader(
                        locationString,
                        this.getClass().getClassLoader(),
                        (dir, name) -> name != null && name.endsWith(".jar")
                );
                               
Your alternative approach is what the problem i wrote earlier creating
multiple instances of dbcp service and making each point to different
version of drivers. The issue was below error for ojdbc7.jar ( oracle 11g)..

*java.sql.SQLException: Object does not wrap anything with requested
interface*
        at oracle.jdbc.driver.OracleConnection.unwrap(OracleConnection.java:268)
        at
org.apache.commons.dbcp.DelegatingConnection.unwrap(DelegatingConnection.java:553)
        at
org.apache.commons.dbcp.DelegatingConnection.unwrap(DelegatingConnection.java:553)

It is not able to unwrap this connection to oracle.jdbc.OracleConnection (
*because im using createOracleArray method of this OracleConnection.class*
). For this i added dependent ojdbc7.jar into my maven build pom.xml . Still
I'm facing this issue....
Though it was working with junit testcases where i used dbcp as connection
service.

What could be the reason.. Please help



--
Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: DBCP Connection Pooling using multiple OJDBC Drivers

Chia Chang
In reply to this post by Mohammed Nadeem
Try to avoid registering more than one connector, the issue was obviously
caused by the duplicate connections which by default oracle will reuse the
existing registered connector.

Register the driver at runtime will solve the issue.

Ex:
// Oracle 7 connection
File chia = new File("/path/to/oracle7.jar");
URL[] cp = new URL[1];
cp[0] = chia.toURI().toURL();
URLClassLoader ora8loader = new URLClassLoader(cp,
ClassLoader.getSystemClassLoader());
Class drvClass = ora8loader.loadClass("oracle.jdbc.driver.OracleDriver");
Driver ora8driver = (Driver)drvClass.newInstance();

Properties props = new Properties();
props.setProperty("user", "chiablahblah");
props.setProperty("password", "mybankaccountpwd");
Connection ora8conn =
ora8driver.connect("jdbc:oracle:thin:@host:port:sid",props);

****/

Repeat the same blah above for 11g and you should be good.

/chia



 



--
Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/