Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Error java.sql.SQLExceptionPyRaisable: java.sql.SQLException: No suitable driver found #290

Closed
alexisrolland opened this issue Mar 6, 2018 · 14 comments
Closed

Error java.sql.SQLExceptionPyRaisable: java.sql.SQLException: No suitable driver found #290

alexisrolland opened this issue Mar 6, 2018 · 14 comments

Comments

@alexisrolland
Copy link

Hello,
I have the following function to connect to a Teradata database:

def fGetJDBCConnexion():
        oCipherKey = fGetParameter(pFileName='global_parameter.ini', pSectionName='AUDIT', pParameterName='oCipherKey').encode("UTF-8")
        oCipher = Fernet(oCipherKey)
        oPasswordEncrypted = fGetParameter(pFileName='global_parameter.ini', pSectionName='AUDIT', pParameterName='oPassword').encode("UTF-8")
        oPassword = oCipher.decrypt(oPasswordEncrypted).decode("UTF-8")

        # Retrieve audit database name and connexion string from global parameter file
        oDataBase = fGetParameter(pFileName='global_parameter.ini', pSectionName='AUDIT', pParameterName='oDataBase')
        oUserName = fGetParameter(pFileName='global_parameter.ini', pSectionName='AUDIT', pParameterName='oUserName')
        oConnexionString = fGetParameter(pFileName='global_parameter.ini', pSectionName='AUDIT', pParameterName='oConnexionStringJDBC')

        # Connect
        oConnexion = jaydebeapi.connect(
            'com.teradata.jdbc.TeraDriver',
            oConnexionString,
            {'user': oUserName, 'password': oPassword, 'tmode': 'TERA', 'charset': 'UTF8'},
            os.path.dirname(__file__) + '/teradataDriverJdbc.jar')
        return(oConnexion, oDataBase)

This function works well when doing unit tests. It returns both the connection object and my database name as expected.

However, this function is called as part of a bigger Flask API built with Flask-Restplus, and the function returns the following error message when it's executed in that context:

--------------------------------------------------------------------------------
ERROR in app [/home/alexis/dq/lib/python3.5/site-packages/flask/app.py:1587]:
Exception on /dataqualityframework/api/v1/batchowners [GET]
--------------------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 1639, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/alexis/dq/lib/python3.5/site-packages/flask_restplus/api.py", line 313, in wrapper
    resp = resource(*args, **kwargs)
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/views.py", line 84, in view
    return self.dispatch_request(*args, **kwargs)
  File "/home/alexis/dq/lib/python3.5/site-packages/flask_restplus/resource.py", line 44, in dispatch_request
    resp = meth(*args, **kwargs)
  File "api.py", line 152, in get
    oStatus, oBatchOwners = batch_owner.fGetBatchOwner()
  File "/home/alexis/data-quality/api/batch_owner.py", line 125, in fGetBatchOwner
    oData = fGetBatchOwnerData()
  File "/home/alexis/data-quality/api/batch_owner.py", line 107, in fGetBatchOwnerData
    oConnexion, oQueryParameter['DATABASE'] = global_function_api.fGetJDBCConnexion()
  File "/home/alexis/data-quality/api/global_function_api.py", line 133, in fGetJDBCConnexion
    os.path.dirname(__file__) + '/teradataDriverJdbc.jar')
  File "/home/alexis/dq/lib/python3.5/site-packages/jaydebeapi/__init__.py", line 381, in connect
    jconn = _jdbc_connect(jclassname, url, driver_args, jars, libs)
  File "/home/alexis/dq/lib/python3.5/site-packages/jaydebeapi/__init__.py", line 199, in _jdbc_connect_jpype
    return jpype.java.sql.DriverManager.getConnection(url, *dargs)
jpype._jexception.java.sql.SQLExceptionPyRaisable: java.sql.SQLException: No suitable driver found for jdbc:teradata://server-name.company.org

I have tried this workardound:

import jpype
if not jpype.isThreadAttachedToJVM():
        jpype.attachThreadToJVM()

As described in: baztian/jaydebeapi#14
But unfortunately it triggers a segmentation fault error.

On Stackoverflow where I also created this issue: https://stackoverflow.com/questions/49105504/jaydebeapi-triggers-java-sql-sqlexception-no-suitable-driver-found
@baztian redirect me here as it seems to be something related to jPype rather thand JayDeBeAPI. Would you please have any idea how to solve this issue?

Thanks
@marscher
Copy link
Member

marscher commented Mar 6, 2018 via email

@alexisrolland
Copy link
Author

Thanks @marscher
The class path is good because the function works fine when I execute it manually / in unit test. It only fails when it's called automatically within my Flask application. Could it be because multi-threading is not properly managed?

I'll try to test the development version and will let you know. I just need to figure out how to install it (I've never done it before).

Cheers

@marscher
Copy link
Member

marscher commented Mar 6, 2018 via email

@alexisrolland
Copy link
Author

Well, I have tried this workardound:

import jpype
if not jpype.isThreadAttachedToJVM():
        jpype.attachThreadToJVM()

As described in: baztian/jaydebeapi#14
But unfortunately it triggers a segmentation fault error.

@marscher
Copy link
Member

marscher commented Mar 6, 2018 via email

@alexisrolland
Copy link
Author

alexisrolland commented Mar 8, 2018 •
edited

Here is a minimal working example to reproduce the issue.
In a terminal Window, type the following commands to setup your workstation:

# Install JVM
sudo apt-get install default-jre

# Create Python virtual environment
sudo apt-get install python3-venv
python3 -m venv jdbc
source jdbc/bin/activate

# Install Python packages in virtual environment
pip3 install --upgrade pip
pip3 install jaydebeapi
pip3 install flask

Create a file app.py with the following content:

from flask import Flask
import jaydebeapi

app = Flask(__name__)


def get_jdbc_connection():
    connection = jaydebeapi.connect(
        'com.teradata.jdbc.TeraDriver',
        'jdbc:teradata://edw-dev.company.org',
        {'user': 'LOGIN', 'password': 'PASSWORD', 'tmode': 'TERA', 'charset': 'UTF8'},
        '/home/alexis/teradataDriverJdbc.jar')
    return connection


@app.route('/hello/')
def hello_world():
    print('Init second connection')
    get_jdbc_connection()
    print('Success')
    return 'Hello world!'


if __name__ == '__main__':
    print('Init connection')
    test_connection = get_jdbc_connection()
    test_connection.close()
    print('Init connection closed')
    app.run(host='localhost', port=5000, threaded=True, debug=True)

In your terminal windows with the virtual environment activated, type the following:

python3
>>> import app
>>> app.get_jdbc_connection()
<jaydebeapi.Connection object at 0x7f28ecb2ebe0>

The snippet above works and the method get_jdbc_connection() returns a connection object.
Now try running it with Flask. In a terminal window with the virtual environment activated, type the following:

Init connection
Init connection closed
 * Running on http://localhost:5000/ (Press CTRL+C to quit)
 * Restarting with stat
Init connection
Init connection closed
 * Debugger is active!
 * Debugger PIN: 301-429-987

In your browser, navigate to http://localhost:5000/hello/
You will get the following error message which is the same as the one I pasted in my first message above:

image

Init second connection
127.0.0.1 - - [08/Mar/2018 16:00:30] "GET /hello/ HTTP/1.1" 500 -
Traceback (most recent call last):
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 2000, in __call__
    return self.wsgi_app(environ, start_response)
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 1991, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 1567, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/_compat.py", line 33, in reraise
    raise value
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 1988, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 1641, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 1544, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/_compat.py", line 33, in reraise
    raise value
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 1639, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/alexis/app.py", line 19, in hello_world
    get_jdbc_connection()
  File "/home/alexis/app.py", line 12, in get_jdbc_connection
    '/home/alexis/teradataDriverJdbc.jar')
  File "/home/alexis/dq/lib/python3.5/site-packages/jaydebeapi/__init__.py", line 381, in connect
    jconn = _jdbc_connect(jclassname, url, driver_args, jars, libs)
  File "/home/alexis/dq/lib/python3.5/site-packages/jaydebeapi/__init__.py", line 199, in _jdbc_connect_jpype
    return jpype.java.sql.DriverManager.getConnection(url, *dargs)
jpype._jexception.java.sql.SQLExceptionPyRaisable: java.sql.SQLException: No suitable driver found for jdbc:teradata://edw-dev.company.org

@maxze
Copy link

maxze commented Aug 22, 2018

@alexisrolland , were you ever able to fix this issue? I'm currently running into the same error and every search leads me to you.

My situation is a function working completely fine the first time I access it via a Flask API, but it throws that error when I try to execute it a second time.

Were you able to fix this or did you just give up at some point?

@alexisrolland
Copy link
Author

Hi @maxze
Unfortunately I was not able to solve the issue. I gave up on jaydebeapi. In the context of Teradata we’ve been using Teradata REST API instead. For other databases we are using pyodbc. Sorry.

@maxze
Copy link

maxze commented Aug 23, 2018 •
edited

@alexisrolland Thanks for the reply. I was actually able to fix it using this workaround:

import jpype
if jpype.isJVMStarted() and not jpype.isThreadAttachedToJVM():
	jpype.attachThreadToJVM()
	jpype.java.lang.Thread.currentThread().setContextClassLoader(jpype.java.lang.ClassLoader.getSystemClassLoader())

Everything works fine (for now)!

@alexisrolland
Copy link
Author

Awesome.
I'm closing the issue then.
Thanks

@ghost
Copy link

ghost commented Nov 29, 2018

Hi @maxze
Unfortunately I was not able to solve the issue. I gave up on jaydebeapi. In the context of Teradata we’ve been using Teradata REST API instead. For other databases we are using pyodbc. Sorry.

Sorry to resurrect this and go on a tangent, but how has threading pyodbc been working for you? I tried threading pyodbc with different connections in each thread but it seems to fumble when I put them under heavy load. I have been unable to find any concrete info on threading with pyodbc so any help is appreciated!

This was referenced Aug 28, 2019
Closed
Open
@theglob
Copy link

theglob commented Dec 16, 2020

@alexisrolland Thanks for the reply. I was actually able to fix it using this workaround:

import jpype
if jpype.isJVMStarted() and not jpype.isThreadAttachedToJVM():
	jpype.attachThreadToJVM()
	jpype.java.lang.Thread.currentThread().setContextClassLoader(jpype.java.lang.ClassLoader.getSystemClassLoader())

Everything works fine (for now)!

This actually works. Amazing

@Thrameos
Copy link
Contributor

This fix is only workable for old JPype versions, and will create a new bug in future versions. Attaching a thread manually, will make the thread hold the JVM open preventing exiting unless that thread is terminated prior to exit. I strongly encourage you to move to JPype 1.2.0 as threads automatically connect safely there, and there are many memory leak fixes.

Also it should be noted that jpype.dbapi2 which is include in recent jpype releases is about twice as fast as jaydebeapi. It also has the added benefit that I will deal with bugs resulting from its use, unlike jaydebeapi which I leave to its author.

@theglob
Copy link

theglob commented Dec 17, 2020

This fix is only workable for old JPype versions, and will create a new bug in future versions. Attaching a thread manually, will make the thread hold the JVM open preventing exiting unless that thread is terminated prior to exit. I strongly encourage you to move to JPype 1.2.0 as threads automatically connect safely there, and there are many memory leak fixes.

Also it should be noted that jpype.dbapi2 which is include in recent jpype releases is about twice as fast as jaydebeapi. It also has the added benefit that I will deal with bugs resulting from its use, unlike jaydebeapi which I leave to its author.

Ok, i tried that but the thing is i am using jaydebeapi which has had other issues with some older jpype versions.
Unfortunately with the current jpype 1.2.0 these issues came back. I just created an issue in their github related to that.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@marscher @theglob @alexisrolland @maxze @Thrameos