Support Oracle Service Name connection option

[klauern]

I am trying to use Metabase, and noticed that it started supporting Oracle drivers. I have been able to get it started with the ojdbc7.jar, and it doesn't appear to support Service Names, opting only to provide support for SID's:

java.sql.SQLException: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

All of our databases are behind service names, and from what I have been able to figure out, the URL needs to be different: http://stackoverflow.com/questions/4832056/java-jdbc-how-to-connect-to-oracle-using-service-name-instead-of-sid#4832116

[alee792]

@klauern Did you happen to find a workaround? I'm currently scrounging around for SIDs, but if you discovered an alternative that would be very helpful.

[camsaul]

We could probably add a field for Additional JDBC connection string options like we have for Postgres and a few other drivers

@klauern @alee792 would that solve the issue for you?

[alee792]

Yes, if it's like the Postgres page, I should be able to just toss "/[servicename]" on the end of the JDBC string to connect.

Out of curiosity, is there any possibility of having a generic JDBC string field to attach databases? Love the platform by the way, you guys have done a great job!

[camsaul]

@klauern @alee792 I've opened PR #4923 that lets you supply additional JDBC connection string options. Please give it a try and let me know if it works for you. You'll want to put something like

serviceName=myservicename

in the input box (/myservicename won't work because our code is automatically set to treat that field as URL parameters and thus tries to insert ? beforehand).

If that works I'll merge this into our upcoming release.

[camsaul]

Also @alee792 allowing generic JDBC connection strings is an option we're considering. There are some issues with that approach, however:

• passwords with special characters don't seem to work properly as part of a JDBC connection string (even when URL-escaped) in some drivers (this seems to be the case for the jTDS SQL Server driver we're using). Breaking things out into separate fields lets us set the properties with a Properties object
• We'd have to make sure the JDBC driver you're trying to use is one we support
• We'd have to make sure the driver is the right one for a given DB; this could be confusing in some cases where there are multiple JDBC drivers for a given database (e.g. jTDS or Microsoft for SQL Server, Thin vs. OCI for Oracle). These are Metabase implementation details so it's not something we'd want users to have to know to configure a DB.

We've added the "additional connection string options" for a few databases now and it seems to most if not all of the use cases for a raw JDBC connection string that I've seen so far.

[alee792]

Thank you for all the help @camsaul. It seems to be working on my end. When is the next release slated for?

[camsaul]

@alee792 we're planning on shipping it in the next week or so

[camsaul]

Implemented by #4923

[alee792]

@camsaul, turns out I didn't test it adequately enough :(. When attempting to connect to an Oracle DB with only the service name provided and no SID, I receive the same exception:

java.sql.SQLException: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

I've never worked with clojure, but from the PR, it looks like sql/handle-additional-options appends the JDBC string options to the end as "host:port:sid/serviceName". Thus, when a an sid is not provided it ends up looking like host:port:/serviceName, resulting in the exception above.

Not being super familiar with Oracle DBs, I'm not sure if every DB necessitates an SID (our DBAs can't seem to find them if we do...), but we are definitely using service names. Based on @klauren's post, this "service name only" config seems pretty common.

Apologies for my oversight. Could it be changed to remove the final colon when an SID is not provided? I'm working on my end with DBAs to track down the SIDs to make sure that it actually is functional when an SID and/or service name is provided. I will get back when I get that figured out. Again, thanks for all the help!

Edit: Can confirm that we only use service name and not SID.

[klauern]

I won't have an opportunity to test this until at least June. I'm currently out of the state on leave for some other work, and my time is completely wrapped up in what I'm doing there. I'll make a note to check this out when I get back, though, as I would like to get this working on some databases that have a litany of tables that I could be gathering information out of.

[camsaul]

@alee792 it should be a simple fix to make it work without SIDs

[camsaul]

Ok @alee792 I just made the SID optional in #4948