Issues with Setting Up Database Connection

[Stephen Higgins]

We are having issues with getting the Adaptavist Resources setup for one of our database connections and are unsure of how to resolve. Any help would be greatly appreciated. We are leveraging the server edition of JIRA.

We tried this connection string and included the jtds-1.3.1.jar driver and it is not working.

• NOTE: This connection string and driver work on our Confluence instance.

url jdbc:jtds:sqlserver://df2v-catsql-p01.ebsi.corp:1433/cornerstone_catalog;domain=EBSI;useNTLMv2=true

driver net.sourceforge.jtds.jdbc.Driver

driver file jtds-1.3.1.jar

Error:

2020-05-28 08:01:58,727-0500 https-jsse-nio-8443-exec-3 WARN dcorrigan 481x41945x1 bkgxw6 192.168.46.27,127.0.0.1 /rest/scriptrunner/latest/resources/com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection [c.z.hikari.util.DriverDataSource] Registered driver with driverClassName=net.sourceforge.jtds.jdbc.Driver was not found, trying direct instantiation.

2020-05-28 08:01:58,739-0500 https-jsse-nio-8443-exec-3 ERROR dcorrigan 481x41945x1 bkgxw6 192.168.46.27,127.0.0.1 /rest/scriptrunner/latest/resources/com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection [c.z.hikari.pool.PoolBase] 32b58ee3-46c6-41a1-9108-36e0f4d52482 - Failed to execute isValid() for connection, configure connection test query (null).

2020-05-28 08:01:58,745-0500 https-jsse-nio-8443-exec-3 ERROR dcorrigan 481x41945x1 bkgxw6 192.168.46.27,127.0.0.1 /rest/scriptrunner/latest/resources/com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: null

java.lang.AbstractMethodError

            at net.sourceforge.jtds.jdbc.JtdsConnection.isValid(JtdsConnection.java:2833)

------------------------------------------

We also tried this connection string which we believed should use an included driver and it is failing.

Included driver is mssql-jdbc-7.2.1.jre8.jar

We tried including sqljdbc42.jar as well, no dice.

jdbc:sqlserver://df2v-catsql-p01.ebsi.corp;databaseName=cornerstone_catalog;domain=EBSI;useNTLMv2=true

We also tried calling the drivers two different ways. 

com.microsoft.sqlserver.jdbc.SQLServerDriver

com.microsoft.jdbc.sqlserver.SQLServerDriver

2020-05-28 08:06:05,367-0500 https-jsse-nio-8443-exec-1 WARN dcorrigan 486x43711x1 bkgxw6 192.168.46.27,127.0.0.1 /rest/scriptrunner/latest/resources/com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection [c.z.hikari.util.DriverDataSource] Registered driver with driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver was not found, trying direct instantiation.

2020-05-28 08:06:06,391-0500 https-jsse-nio-8443-exec-1 ERROR dcorrigan 486x43711x1 bkgxw6 192.168.46.27,127.0.0.1 /rest/scriptrunner/latest/resources/com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection [c.z.hikari.pool.HikariPool] d3d6c4da-aec8-4a15-b633-48351caddd3e - Exception during pool initialization.

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'OUR USER IDl'. ClientConnectionId:df005aab-b901-4b7d-b0a7-bdd0a0210d77

            at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:256)

[PD Sheehan]

If your "Type" custom field is a select of some kind, you might want to try it like this:

def typeValue = (cfValues['Type'].value
(typeValue != 'Software') || (typeValue == 'Software' && cfValues['Test Criteria'])

That's because the cfValues data type will depend on the selected field. If it returns an "Option" object, then you have to look inside that object to get the text value (as oposed to the option Id).

Also, a bit of free advice... you might want to consider renaming that "Type" field to something more specific. Since "type" is a keword shortcut in JQL meaning the same as "issueType".

Now, if you try to write a JQL like

type = story 

You may have inconsistent behaviour, or you will be forced to use the "cf[12345] = Software." format.

[Matthew Mallard]

Final version:

import org.apache.log4j.Level
import org.apache.log4j.LogManager
import org.apache.log4j.Logger

Logger rootLogger = LogManager.rootLogger
rootLogger.level = Level.INFO

if (issue.issueType.name == 'Story') {
     def typeValue = cfValues['Type']?.value
     def testCriteria = cfValues['Test Criteria']?.value
     log.info("Type: ${typeValue}, Test Criteria: ${testCriteria as Boolean}")

     (typeValue != 'Software') || (typeValue == 'Software' && testCriteria)
} else {
     true
}