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.
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.
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.
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...
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)
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?
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 ;).
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?
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 "$@"
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
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
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.
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.
++ 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
Badges are a great way to show off community activity, whether you’re a newbie or a Champion.Learn more
@Rachel Wright (Jira Genie), @Billy Poggi (AUG NOVA, DC), and @Dana Jansen (Confluence Queen) are just some of the folks that lead one of the world's most active Atlassian User Group (AUG)....
Connect with like-minded Atlassian users at free events near you!Find a group
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG
You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs