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_ · Accepted solution]

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 

[Vlad Fed · Accepted solution]

Hi @Ram Kumar Aravindakshan _Adaptavist_ thanks a lot for your help.

I made all the changes for my script but it throws an error on custom fields. I want to execute this script for JSM.

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Vlad Fed

You can execute it in JSM but you will need to upgrade your ScriptRunner plugin to the latest release, i.e. 8.10.0 or at least 7.11.0 to be able to use the HAPI feature.

The HAPI feature is required for the code to work.

From what I noticed in the latest screenshot you havr provided, you are using an older version of ScriptRunner. That's the main cause of the code failing.

Thank you and Kind regards,

Ram

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Vlad Fed

Were you able to upgrade your ScriptRunner plugin?

If yes, were you able to test the code that I provided and is it working as expected?

I am looking forward to your feedback.

Thank you and Kind regards,

Ram

[Vlad Fed]

Hi @Ram Kumar Aravindakshan _Adaptavist_ 
I apologize for the long response.
It worked, thanks a lot!

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Vlad Fed

Great to hear the solution worked for you. :-)

Kindly accept the answer.

Thank you and Kind regards,

Ram