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!

[Bob Swift (personal)]

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" ... <all your other db parameters>

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 (personal)]

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?

[Bob Swift (personal)]

Same as the other one, you want to use double quotes for the sql parameter: --sql "..." so you don't have to escape the single quotes in your sql, then you only have to escape any double quotes. When I say double quotes, I mean a double-quote character ;).

[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.

[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..

[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]

So where is that description coming from in the output? I did not see that on the input side.

Just verifying the shell script is only two lines long.

[Mike Smith]

First line is:

#!/bin/bash

[Mike Smith]

This is what I get straight from the database, without the JCLI:

1	SENCHA	50	12	/alm/tools/licenses/sencha.license
5	DBVISUALIZER	149	34	/alm/tools/licenses/dbvis.license
7	JREBEL	50	12	alm/tools/licenses/jrebel.license

[Nic Brough -Adaptavist-]

Hmm, that one is picking up a load of parameters and attemting to use the incorrectly. Without knowing the exact command you ran there, we can't tell what is internal or external

Could we:

1. go back to the first query (as the results are more simple and look sane at first)
2. run it with -v
3. get a copy of the complete jira.sh that you're running (extracting one line at a time by questioning you directly is a bit inefficient, the whole script would be quicker)

[Mike Smith]

OK so I ran the very same query from my OP and got the very same results. Everything is right there. Nothing extra.

When I ran the query directly against the database, this is what I got:

10000	Users	A project role that represents read-only users 
10002	Administrators	Project administrators which may include release managers and project managers
10010	Tester	A project role that represents QA testers in a project
10031	Member	A role that represents developers and analysts on a project

[Mike Smith]

That's in the database itself.

I ran a different query with -v, here is the result:

Server address: http://jira-test/rpc/soap/jirasoapservice-v2
Successful login to: http://jira-test/rpc/soap/jirasoapservice-v2 by user: userId.

Run: --lIC_LOC "/alm/tools/licenses/sencha.license" --lIC_CNT "50" --lIC_ID "1" --lIC_AVAIL "12" --lIC_TYP "SENCHA"

Error: Unknown flag 'lIC_LOC'.
Error: Unexpected argument: /alm/tools/licenses/sencha.license
Error: Unknown flag 'lIC_CNT'.
Error: Unexpected argument: 50
Error: Unknown flag 'lIC_ID'.
Error: Unexpected argument: 1
Error: Unknown flag 'lIC_AVAIL'.
Error: Unexpected argument: 12
Error: Unknown flag 'lIC_TYP'.
Error: Unexpected argument: SENCHA
Error: Parameter 'action' is required.
23444258249f94fdfd2ee84f488839a4fe0e5a3a logged out.

Client error: 1 actions failed
org.swift.common.cli.CliClient$ClientException: 1 actions failed
        at org.swift.common.cli.CliClient.runFromSql(CliClient.java:1198)
        at org.swift.jira.cli.JiraClient.handleRequest(JiraClient.java:609)
        at org.swift.common.cli.AbstractRemoteClient.process(AbstractRemoteClient.java:124)
        at org.swift.common.cli.CliClient.doWork(CliClient.java:388)
        at org.swift.jira.cli.JiraClient.main(JiraClient.java:182)

We have a couple of small tables in the JIRA database that we use for licenses, so I queried one of them.

[Norman Abramovitz]

My bad, it should be the -x parameter that you want

bash -xv jira.sh ...

or use the set -xv in the script.

[Norman Abramovitz]

The output should include the input lines being echoed and the generated execution line with plus sign in front of the line. Given the extra parameters that should not be there, I am wondering if you some aliases or other shell scripts named java.sh or java in the path.

[Mike Smith]

Result:

++ dirname ./jira.sh
+ java -jar ./lib/jira-cli-3.8.0.jar --server http://jira-test --user userId --password password --action runFromSql --sql 'select * from JIRA50SCHEMA.projectrole' --driver jtds --url 'jdbc:jtds:sqlserver://n060sqlt04:1675;DatabaseName=JIRAData' --database JIRAData --dbUser userId --dbPassword password

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

[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?