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]

One person's nightmare....

Here I just whipped this up (took about 15 minutes)

import com.onresolve.scriptrunner.runner.customisers.PluginModule
import com.onresolve.scriptrunner.runner.customisers.WithPlugin
import com.riadalabs.jira.plugins.insight.channel.external.api.facade.ObjectFacade
import com.riadalabs.jira.plugins.insight.services.model.ObjectBean

@WithPlugin('com.riadalabs.jira.plugins.insight')
@PluginModule ObjectFacade objectFacade

def dateFieldName = 'Scheduled Start'
def insightFieldName = 'unknown'
def startAttribute = 'Change Freeze Start'
def endAttribute = 'Change Freeze End'


def dateValue = cfValues[dateFieldName] as Date
def insightObjValue = cfValues[insightFieldName] as List<ObjectBean>


if(!dateValue || !insightObjValue){
    return //missing some info to compare
}

//insight custom field always return an array even if they only allow single value. Assume there is only one
insightObjValue = insightObjValue.first()
def freezeStartAttr = objectFacade.loadObjectAttributeBean(insightObjValue.id, startAttribute)
def freezeEndAttr = objectFacade.loadObjectAttributeBean(insightObjValue.id, endAttribute)
def freezeStart = freezeStartAttr?.objectAttributeValueBeans && freezeStartAttr.objectAttributeValueBeans[0].dateValue
def freezeEnd = freezeEndAttr?.objectAttributeValueBeans && freezeEndAttr.objectAttributeValueBeans[0].dateValue

if(dateValue < freezeStart){
    return false
}
if(dateValue > freezeEnd){
    return false
}
return true

Other than adjusting the last 7 lines according to your actual requirement, this should get you close.

[Steve Letch]

Awesome thanks, will tweak and give it some testing Tuesday :)

[Steve Letch]

Testing results, seems to have a problem with the cfvalue

import com.onresolve.scriptrunner.runner.customisers.PluginModule
import com.onresolve.scriptrunner.runner.customisers.WithPlugin
import com.riadalabs.jira.plugins.insight.channel.external.api.facade.ObjectFacade
import com.riadalabs.jira.plugins.insight.services.model.ObjectBean

@WithPlugin('com.riadalabs.jira.plugins.insight')
@PluginModule ObjectFacade objectFacade

def dateFieldName = 'Scheduled Start'
def insightFieldName = 'Change Freeze Status'
def startAttribute = 'Change Freeze Start'
def endAttribute = 'Change Freeze End'


def dateValue = cfValues[dateFieldName] as Date
def insightObjValue = cfValues[insightFieldName] as List<ObjectBean>


if(!dateValue || !insightObjValue){
return //missing some info to compare
}

//insight custom field always return an array even if they only allow single value. Assume there is only one
insightObjValue = insightObjValue.first()
def freezeStartAttr = objectFacade.loadObjectAttributeBean(insightObjValue.id, startAttribute)
def freezeEndAttr = objectFacade.loadObjectAttributeBean(insightObjValue.id, endAttribute)
def freezeStart = freezeStartAttr?.objectAttributeValueBeans && freezeStartAttr.objectAttributeValueBeans[0].dateValue
def freezeEnd = freezeEndAttr?.objectAttributeValueBeans && freezeEndAttr.objectAttributeValueBeans[0].dateValue

if(dateValue < freezeStart){
return false
}
if(dateValue > freezeEnd){
return false
}
return true

There was an error during the execution of your script against issue CIT-836

Message:

groovy.lang.MissingPropertyException: No such property: cfValues for class: script_e508d908ba07d40a9f5832eac77c6894

Stack:

org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:65)
org.codehaus.groovy.runtime.callsite.PogoGetPropertySite.getProperty(PogoGetPropertySite.java:51)
org.codehaus.groovy.runtime.callsite.AbstractCallSite.callGroovyObjectGetProperty(AbstractCallSite.java:309)
script_e508d908ba07d40a9f5832eac77c6894.run(script_e508d908ba07d40a9f5832eac77c6894.groovy:15)

The Scheduled start definitely has a value, but it may be because it's a Date time picker field, it would be nice if it could actually work with a Datetime Insight value and a Date Time field as I know these CAB guys have things they'd want to kick in at say 8pm and end at 7am.

Additionally, could you add a clause for the Scheduled End field and the scheduled end not being during the change freeze too?

Thanks

[PD Sheehan]

You have to use "simple scripted condition" to be able to leverage the cfValues.

Here is the same script with both the start and end date.

import com.onresolve.scriptrunner.runner.customisers.PluginModule
import com.onresolve.scriptrunner.runner.customisers.WithPlugin
import com.riadalabs.jira.plugins.insight.channel.external.api.facade.ObjectFacade
import com.riadalabs.jira.plugins.insight.services.model.ObjectBean

@WithPlugin('com.riadalabs.jira.plugins.insight')
@PluginModule ObjectFacade objectFacade

def startDateName = 'Scheduled Start'
def endDateName = 'Scheduled End'
def insightFieldName = 'Change Freeze Status'
def startAttribute = 'Change Freeze Start'
def endAttribute = 'Change Freeze End'


def startDateValue  = cfValues[startDateName] as Date
def endDateValue    = cfValues[endDateName] as Date
def insightObjValue = cfValues[insightFieldName] as List<ObjectBean>


if(!startDateValue  || !endDateValue || !insightObjValue){
    return false//missing some info to compare
}

//insight custom field always return an array even if they only allow single value. Assume there is only one
insightObjValue = insightObjValue.first()
def freezeStartAttr = objectFacade.loadObjectAttributeBean(insightObjValue.id, startAttribute)
def freezeEndAttr = objectFacade.loadObjectAttributeBean(insightObjValue.id, endAttribute)
def freezeStart = freezeStartAttr?.objectAttributeValueBeans && freezeStartAttr.objectAttributeValueBeans[0].dateValue
def freezeEnd = freezeEndAttr?.objectAttributeValueBeans && freezeEndAttr.objectAttributeValueBeans[0].dateValue

if(startDateValue <= freezeStart || startDateValue >= freezeEnd){
    //start date is outside freeze window
    return false
}
if(endDateValue <= freezeStart || endDateValue >= freezeEnd){
    //end date is outside freeze window
    return false
}
return true