org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of com.atlassian.

Vlad Fed

I am trying to set up a Scriptrunner script to access external pgsql database. I am using the script console to attempt to execute the following code:

import groovy.sql.Sql
import java.sql.Driver
import com.atlassian.jira.component.ComponentAccessor

def customFieldObjects = ComponentAccessor.customFieldManager.getCustomFieldObjects(issue)
def db = [url:'jdbc:postgresql:localhost:testdb', user:'', password:'', driver:'org.postgresql.jdbc.JDBCDriver']
def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)

 

def NameVM1 = customFieldObjects.findByName("NameVM")
def CPU1= customFieldObjects.findByName("CPU")
def RAM1 = customFieldObjects.findByName("RAM")
def HDD1 = customFieldObjects.findByName("HDD")


try {
sql.execute ('INSERT INTO customers (namevm, cpu, ram, hdd) values (?, ?.(), ?, ?)', NameVM1, CPU1, RAM1, HDD1)

} finally {
sql.close()

}

 But my scipt failed
2023-09-01 13:56:59,458 WARN [sql.Sql]: Failed to execute: INSERT INTO customers hdd values ? because: Can't infer the SQL type to use for an instance of com.atlassian.jira.issue.fields.ImmutableCustomField. Use setObject() with an explicit Types value to specify the type to use.

2023-09-01 13:56:59,462 ERROR [workflow.AbstractScriptWorkflowFunction]: Workflow script has failed for user 'ucbadmin'. View here: http://10.8.123.220:8080/secure/admin/workflows/ViewWorkflowTransition.jspa?workflowMode=live&workflowName=PTSD%3A+Service+Request+Fulfilment+workflow+for+JIRA+Service+Desk&descriptorTab=postfunctions&workflowTransition=1&highlight=1 org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of com.atlassian.jira.issue.fields.ImmutableCustomField. Use setObject() with an explicit Types value to specify the type to use. at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1051) at Script139.run(Script139.groovy:18)

I would be grateful if you could help me find the error.

Ram Kumar Aravindakshan _Adaptavist_

Hi @Vlad Fed

After going through your code, the approach you are taking to declare your SQL class doesn't seem to be correct.

Below is a sample working code for your reference:-

import groovy.sql.Sql
import java.sql.Driver

def props = new Properties()
props.setProperty('user', 'adaptavist')
props.setProperty('password', 'qwerty')

def driver = Class.forName('org.postgresql.Driver').newInstance() as Driver
def conn = driver.connect('jdbc:postgresql://localhost:5432/Jira9', props)
def sql = new Sql(conn)

def cpuValue = issue.getCustomFieldValue('CPU') as String
def ramValue = issue.getCustomFieldValue('RAM') as String
def hddValue = issue.getCustomFieldValue('HDD') as String

try {
 sql.execute("""
 INSERT INTO computer_details (cpu, ram, hdd) 
 values ('${cpuValue}', '${ramValue}', '${hddValue}')
 """.toString())
} finally {
 sql.close()
 conn.close()
}

Please note that the sample working code above is not 100% exact to your environment. Hence, you will need to modify it accordingly.

Below is a screenshot of the Post-Function configuration:-

Also, I suggest upgrading your ScriptRunner plugin if you haven't already to the latest release, i.e. 8.10.0, so you can use ScriptRunner's HAPI features to simplify your code.

Below are a couple of test screenshots for your reference:-

For this test case, I have used the Post-Function for the Create transition.

1. First, I create a new issue and add some values for the 3 fields CPU, RAM and HDD as shown in the screenshot below:-

 

2. Once the issue is created, I view it in the view screen, and all details have been entered as expected.

3. Next, I view the table in the Postgres Database and as expected, the details have been inserted to a new row as shown in the screenshot below:-

I hope this helps to solve your question. :-)

Thank you and Kind regards,

Ram