"net.sourceforge.jtds.jdbc.ClobImpl " in output of groovy querying Clob

Jira Development Jira Server
, , , ,
1

Trying to run below query in Script console, doesn’t retrieve the value of clob type of values

select DESCRIPTOR from jiraworkflows

Shows output as below

[DESCRIPTOR:net.sourceforge.jtds.jdbc.ClobImpl@4deda340];[DESCRIPTOR:net.sourceforge.jtds.jdbc.ClobImpl@18c59ec6];

Environment Details
MS SQL DB Version : 2012
jtds driver: jtds-1.3.1.jar in D:\Jira\lib
Expected is something as below

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE workflow PUBLIC "-//Op....

Would there be anyway to get such output ?

2

This is confusing. You’ve tagged with atlassian-connect-dev, but posted to JIRA Server category. Is this a general SQL question or specific to add-on development?

1 Like
3

Hi David,

Sorry about that. Not sure, where to tag. Appreciate if any thoughts

Wondering where this would need to go. Thanks in advance

4

This seems more about hot to use the jtds library than a JIRA issue, maybe below link can help you achieve your goal:

Converting nvarchar(max) data type to string in Java

PS: The syntax might be little difference since your are using Groovy

Regards,
Italo Qualisoni

5

Thanks Italo. Managed to get run, but now limitation as below. Not sure if there’s any workaround

The size (8001) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).

We have clob with size upto 43680 chars. Need we to convert to any other charset ?

6

Probably you will need to cast the column in your SQL statement

7

Hi Italo,

Sorry to ask. Not sure how to cast.
How this can be done ?

Appreciate kind advise.

Thanks much in advance

8

Do you know the workflow ID? Maybe you can achieve this using some SQL client too?

You can try this code below by updating the select to match to your case:
(I’ve tested using script console of Script Runner with a JIRA instance with MSSQL database)

import com.atlassian.jira.component.ComponentAccessor
import groovy.sql.Sql
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface

import java.sql.Connection

def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)
String helperName = delegator.getGroupHelperName("default");

def sqlStmt = "select * from jiraworkflows where id = 11034"

Connection conn = ConnectionFactory.getConnection(helperName);
Sql sql = new Sql(conn)

try {
    StringBuffer sb = new StringBuffer()
    sql.eachRow(sqlStmt) {
        //sb << "${it}\n"
        sb << "${it.DESCRIPTOR.getAsciiStream().getText()}\n"
    }
    log.error sb.toString()
}
finally {
    sql.close()
}
9

Thanks, that was helpful, Italo

Querying for one workflow’s DESCRIPTOR is not showing complete XML, truncating as below

Start of logs truncated as they exceeded 300 lines.

Believe need to check with Adaptavist for this.

Meanwhile, is there a way to get query output exported into a file ?

10

Firstly why don’t you use some SQL client to get this value? You could use HeidiSQL(https://www.heidisql.com/)

You will need access to the JIRA server to pick the file, please check below code updated to create a file with the content from the SQL

import com.atlassian.jira.component.ComponentAccessor
import groovy.sql.Sql
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface

import java.sql.Connection

def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)
String helperName = delegator.getGroupHelperName("default");
//Create a file "outputWorkflowDescriptor.txt" into /tmp/ folder
def inputFile = new File("/tmp/outputWorkflowDescriptor.txt")

def sqlStmt = "select * from jiraworkflows where id = 11034"

Connection conn = ConnectionFactory.getConnection(helperName);
Sql sql = new Sql(conn)

try {
    sql.eachRow(sqlStmt) {
        inputFile.append("${it.DESCRIPTOR.getAsciiStream().getText()}",'UTF-8')    
    }
}
finally {
    sql.close()
}
11

Our Jira runs on Windows, so having path as follows

def inputFile = new File("D:\\Atlassian\\ApplicationData\\JIRA\\export\\outputWorkflowDescriptor.txt")

or as follows

def inputFile = new File("outputWorkflowDescriptor.txt")

both results in error as follows

2017-06-16 11:46:49,956 ERROR [runner.ScriptRunnerImpl]:

Any thoughts, please ?

Reason for unable to query directly to db is restrictions in our environment. So, need to get such data by script console

12

Hi,

Can you check if the file was created?

You can use as below with single “/”

def inputFile = new File("D:/Atlassian/ApplicationData/JIRA/export/outputWorkflowDescriptor.txt")

I think that the error is related with this code line that should have been commented

log.error sb.toString()

I’ve edited my previously comment to remove the StringBuilder and the log.error;

13

Thanks a lot, that works.

1 Like
14

Hi,

Below in above script returns DESCRIPTOR column

inputFile.append("${it.DESCRIPTOR.getAsciiStream().getText()}",'UTF-8')

If we need another column containing text, above line needs to changes ?

For example below query, returns 2 columns

  select workflowname, DESCRIPTOR from jiraworkflows;

As above line returns CLOB data, how to modify it to get text data plus CLOB data ?

Also, as all the rows are joined to together in the output, unable to find out which is next record/row.
Would there be a way to add newline at the end of each line output ?