nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

JCLI sqlException

Mike Smith

We are testing the JCLI in our environment. Our JIRA instance is connected to an SQL Server 2008 database instance.

When we try to run the "runFromSql" action, we get an SQLException. Initially, because of the error reported, we felt that we were doing something wrong syntactically -- but we are not.

./jira.sh --action runFromSql --sql 'select * from JIRA50SCHEMA.projectrole' --driver 'jtds' --url 'jdbc:jtds:sqlserver://machinename:port;DatabaseName=databaseInstance' --database 'databaseInstance' --dbUser 'userId' --dbPassword 'password'

Of course, we do have the requisite driver installed in lib/jdbc. However, the query yields the following result:

Run: --custom "'NAME:Users','DESCRIPTION:A project role that represents read-only users ','ID:10000'"

Error: Parameter 'action' is required.

Client error: 1 actions failed

It is very strange; it seems as if the resultset returned is fed again into jira.sh for execution. I cannot tell where we went wrong, but we haven't been able to identify the issue.

HELP!

Accepted answers

All comments

Nic Brough -Adaptavist-

Well, the output looks ok, it's executing that command and getting the response I'd expect. But then it looks like it's running again, like you suggest.

Could you have a look in the jira.sh script to see/check if that is running anything extra?

Norman Abramovitz

Is jira.sh your shell script? If so, can you post it? Which shell are you using? Sometimes -- can be taken as stop processing command line options. Have you run the shell script with the -v (verbose) option, so you can see what actually gets generated?

Nic Brough -Adaptavist-

Mmm, I was assuming the jira.sh file was the wrapper script provided in the CLI, not another one written by Mike.

The one I've got on hand as below (I usually do as it suggests and hard code a few connection things so I don't have to think about it again):

#!/bin/bash

# Comments
# - Customize for your installation, for instance you might want to add default parameters like the following:
# java -jar `dirname $0`/lib/jira-cli-2.6.0.jar --server http://my-server --user automation --password automation "$@

java -jar `dirname $0`/lib/jira-cli-2.6.0.jar $@"

Mike Smith

Here is what we are using, pretty basic.

java -jar `dirname $0`/lib/jira-cli-3.8.0.jar --server http://jira-test--user userId --password password "$@"

Nic Brough -Adaptavist-

Ok, as Norman says, what's the first line - the one that tells it what shell to use (the default is #!/bin/bash)

Norman Abramovitz

It was the description in the output of what the sql was doing and nothing on the input side that matched the description that got me thinking it was home-brew script..

Nic Brough -Adaptavist-

Right, I'm sorry, I've completely failed you on this one.

I was concentrating on parameters and formats, not what you're trying to do and how the CLI works.

The runFromSql action is there to extract data from a databse (like Jira) and then perform actions upon the result. You are getting an error because it's getting the first line of data and then you are not telling it what to do with that data. That's why it looks like it's being passed through jira.sh again - it isn't quite doing that, but it is looking for a command to execute against the results of the SQL.

Here's a really quick example:

jira --action runFromSql --common "--action getWatcherList" --sql "select '--issue ' || pkey from jiraissue where assignee = 'nic'"

That fetches the watchers from all the issues I'm assigned to. Note that I've stripped the database connection stuff etc.

Bob Swift

Just looking at this now and not sure I followed all the comments. Lets just step back a bit and talk about concepts and what you are trying to do. The CLI runs actions against JIRA, things like creating issues, adding attachments, progressing issues, and various others. The various run actions allow you to run a bunch of other actions together. In the case of CSV (runFromCsv) or SQL (runFromSql), it allows you to construct those actions based on data from those sources. The data that needs to be provided must be either a complete command line or columns of data representing parameters that the CLI understands. It puts the data into a command line and runs it as a CLI command and it does that for each line/row of data provided. The CLI command that is run is logged together with its output or error information. Unless --continue parameter is used, it will stop processing on the first error encountered.

So back to your situation. What JIRA action are you trying to run? The error you are getting says that you did not provide a required parameter - specifically action. The columns returned by your SQL statement do NOT seem to be JCLI parameter names, so they are being interpreted as custom field names which is why you are seeing --custom in the error log.

A standard use case example is to create an issue for each line/row of data. In that case, an example would be:

--action runFromSql --common "--action createIssue" ... &lt;all your other db parameters&gt;

where the SQL would be something like:

select column1 as "summary", column2 as "type", column3 as "description"

Hope this help.

Mike Smith

Yes, that does help, kind of. Very good explanation, it actually will cut steps out of the script I was writing -- IF i can get it working.

I'm trying to add users to a group, but now the SQL is incorrect. I'm not sure how the single quotes and double quotes work with constants inside of the SQL statement, because it isn't behaving as it normally would from a database client.

The latest script is as follows:

./jira.sh --action runFromSql --common "--action addUserToGroup"  --sql 'select lower_user_name as "userId", 'alm-member-alm' as "group" from JIRA50SCHEMA.cwd_user where lower_user_name in (select lower_child_name from JIRA50SCHEMA.cwd_membership where lower_parent_name = 'alm-admin-aris') order by lower_user_name' --driver 'jtds' --url 'jdbc:jtds:sqlserver://n060sqlt04:1675;DatabaseName=JIRAData' --database 'JIRAData' --dbUser 'userId' --dbPassword 'password'

and the result is as follows:

Client error: SQL reported: java.sql.SQLException: Invalid column name 'alm'.

When executing the script with bash -xv, I can clearly see that 'alm-member-alm' as "group" is not accepted correctly, because the single quotes are removed, and double quotes are treated as a column name.

This is the last step! Once I make it past here, I'm home free...

Bob Swift

You are in quoting h*ll . The tips section covers this. In general, you have 2 choices. Be very careful and escape all the inner double quotes properly or use a special character for the inner double quotes.

In your case, start with --sql " ... " and then escape the inner double quotes (I only see 4: around the userId and group fields)

Mike Smith

Hi Bob,

quoting h*ll is right! I still don't understand how I can use normal SQL with runFromSql, without damaging some brain cells in the process.

Here is my latest iteration. I am trying to read the pkey from the projects table, prepend the results with a string, and create the group name based on that.

Normal SQL looks like this:

select 'prj-admin-' + pkey as 'group' from project

Pretty innocuous query, which produces the following as a result set:

prj-admin-abc
prj-admin-cab
prj-admin-acb
prj-admin-bac
prj-admin-cba
prj-admin-bca

When I use the query with runFromSql, the quotes are really getting in my way.

./jira.sh --action runFromSql --common "--action addGroup'" --sql 'select \"prj-admin-\" + pkey as "group" from project' --driver 'jtds' --url 'jdbc:jtds:sqlserver://n060sqlt04:1675;DatabaseName=JIRAData' --database 'JiraData' --dbUser 'userId' --dbPassword 'password'

The result of the command executed with bash -xv:

java -jar `dirname $0`/lib/jira-cli-3.8.0.jar --server http://jira-test --user userId--password password "$@"
dirname $0
++ dirname ./jira.sh
+ java -jar ./lib/jira-cli-3.8.0.jar --server http://jira-test --user userId --password password --action runFromSql --common '--action addGroup'\''' --sql 'select \"prj-admin-\" + pkey as "group" from project' --driver jtds --url 'jdbc:jtds:sqlserver://n060sqlt04:1675;DatabaseName=JIRAData' --database JiraData --dbUser userId --dbPassword password

Client error: SQL reported: java.sql.SQLException: Incorrect syntax near '+'.

I also tried escaping the + sign, as well as many different combinations of quotes, without success. Seems like it shouldn't be this difficult...

What am I doing wrong?

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

app