In a Flask app with flask-sqlalchemy, I am trying to initialize a connection to Db2 Warehouse on Cloud by setting SQLALCHEMY_DATABASE_URI to one of the parts provided in the service credentials. In the past, going with the uri component worked fine, but my new service has SSL connections only.
app.config['SQLALCHEMY_DATABASE_URI']=dbInfo['uri']
This results in connection errors
File "/home/vcap/deps/0/python/lib/python3.6/site-packages/ibm_db_dbi.py", line 592, in connect
conn = ibm_db.connect(dsn, '', '', conn_options)
Exception: [IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "52.117.199.197". Communication function detecting the error: "recv". Protocol specific error code(s): "104", "*", "0". SQLSTATE=08001 SQLCODE=-30081 During handling of the above exception, another exception occurred:
It seems that the driver is not accepting the ssl=true option specified in the URI string. What parts of the service credentials should I use? Would I need to build the URI string manually?
This is only a partial answer because of a workaround. I am using the port information from the service credentials to modify the connection URI:
if dbInfo['port']==50001:
# if we are on the SSL port, add additional parameter for the driver
app.config['SQLALCHEMY_DATABASE_URI']=dbInfo['uri']+"Security=SSL;"
else:
app.config['SQLALCHEMY_DATABASE_URI']=dbInfo['uri']
By adding Security=SSL to the uri, the driver picks up the info on SSL and uses the correct settings to connect to Db2.