How to display SQL results using groovy

[Vineela Durbha]

I am trying to display sql query results  as below

sql.eachRow(select * from issue)
{row->
// log.error("loggggg" +row.values().toString());

}

But it is not displaying results. Am i doing something wrong?

can someone help on this

[Tuncay Senturk _Snapbytes_ · Accepted solution]

Hello Vineela,

The name of the table should be jiraissue.

[Vineela Durbha]

Even i tried with jiraissue .but no use

[Tuncay Senturk _Snapbytes_]

How is your whole code?

it should be "select * from jiraissue" with quotes.

[Vineela Durbha]

Yah it is "select * from jiraissue" with quotes

try {

sql = new Sql(conn);

def sqlQueryREC ="select * from jiraissue";

sql.eachRow(sqlQueryREC )

{row->

log.error("results")

}

Nothing is being printed from that log.error. Is my sql.eachRow method syntax correct?

If so how should i display all the rows and what is the syntax for it?

[Tuncay Senturk _Snapbytes_]

log.error("results") only logs results string if that's the one you see in logs.

However, if you do not see anything in the log file, just go to script console and try below code, you should see all issues in console, but jiraissue table may contain lots of issues so trying with another table should be wise (like issuestatus)

Try this one for jiraissue

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");
Connection conn = ConnectionFactory.getConnection(helperName);
Sql sql = new Sql(conn)
StringBuffer sb = new StringBuffer()
try {
  sql = new Sql(conn);
  sql.eachRow("select * from jiraissue") {
     log.warn("${it}")
     sb << "$it.id $it.summary"
  }
  return sb
} finally {
  sql.close()
}

[Vineela Durbha]

 I am getting below error

BUG! exception in phase 'class generation' in source unit 'file:/var/lib/jira/atlassian/application-data/jira/scripts/Listener/SWPRCreateReleaseListener.groovy' Trying to access private constant field [com.atlassian.jira.event.issue.AbstractIssueEventListener#log] from inner class

[Tuncay Senturk _Snapbytes_]

So you're using this in issue listener. And in AbstractIssueEventListener there is also log instance variable.

You should define your own log variable to use it. 

[Vineela Durbha]

log issue is now resolved but nothing is being displayed which is in try block

[Vineela Durbha]

Hi @Tuncay Senturk _Snapbytes_ 

I am trying to extract id value from sql query 

Connection conn = ConnectionFactory.getConnection(helperName);
log.debug("connection succees")

Sql sql = new Sql(conn)
def row;
StringBuffer sb = new StringBuffer()

try {
sql.eachRow("""select ji.id as ID from jiraissue ji
left join project p with(nolock)
on ji.project = p.id
where p.pkey='SWRM' and ji.summary='19.10'""") {
log.warn("${it}")

sb << "${it}"
}
sb.toString()

return sb
} finally {
sql.close()
}

sb value is returning as [id:873319], how to just extract the ID from this result as I need to pass this id in my code

[Tuncay Senturk _Snapbytes_]

then you can return sb.ID

[Vineela Durbha]

It is throwing error "No such property id for class"

ANy other way to extract the id value

[Tuncay Senturk _Snapbytes_]

It is case sensitive, if you used above query (as ID), you should use sb.ID otherwise sb.id

[Vineela Durbha]

It didn't work both ways

[Tuncay Senturk _Snapbytes_]

Hey,

I couldn't notice your code.

You are using below statement

sb << "${it}"

I think you should use below to get only id

sb << "${it.ID}"

and

return sb.toString()

But if there are multiple rows (e.g. id:100, id:101, id:102) it will produce the result as 100101102 as I mentioned before.

[Vineela Durbha]

My result would return only one row and your code above is still throwing an error

[Tuncay Senturk _Snapbytes_]

I don't know your latest code but I tried below code

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");
Connection conn = ConnectionFactory.getConnection(helperName);
Sql sql = new Sql(conn)
def resultId
try {
   sql = new Sql(conn);
   sql.eachRow("select * from jiraissue where id = 10000") {
   resultId = "${it.ID}"
}
   return resultId
} finally {
   sql.close()
}

And here is the result

[Tuncay Senturk _Snapbytes_]

So, were you able to achieve?

[Vineela Durbha]

As shown in the above screenshot I am even getting error on ${it.ID} as No such property ID for class.

How to resolve it?

[Tuncay Senturk _Snapbytes_]

Where is screenshot? What is your latest code? I'm blind as a bat, how can I help in this way?

[Vineela Durbha]

I said about the screenshot you have attached.. error in the screenshot of yours

[Tuncay Senturk _Snapbytes_]

Oohh, you can skip that error, that's just visual. 

The context in compile time and runtime are different.

Did you see the Result in my screenshot, it works and returns 10000.

[Tuncay Senturk _Snapbytes_]

@Vineela Durbha I am curious whether it worked or not 

[Vineela Durbha]

@Tuncay Senturk _Snapbytes_ 

Sorry I forgot to update here, Yes it worked and thanks a lot for your help :)

[Tuncay Senturk _Snapbytes_]

Pleasure! I'm glad that it worked ;)

[Tuncay Senturk _Snapbytes_]

And also please bear in mind that there is a loop here, so it might not have single result. In my code, I was concatenating all id values into stringbuilder.