If you’re on an older version of JIRA with XMLRPC being the only option (since REST is not available), Atlassian’s XMLRPC APIs are pretty simple to use. For my use case, I wanted a listing of total issues in my filters with individual breakdowns as illustrated below. JIRA Filter (Total Count) : assignee_1(count) assignee_2(count) …

Tennis Project Open Jiras (4) : rfederer(1) rnadal(1) nole(1) andym(1)
NFL Project Open Items (0) :
Tennis Project FRs Ready for Verification (36) : andym(1) rfederer(17) nole(7) rnadal(11)
NFL Project FRs Fixed in Branch (25) : tbrady(5) manning(7) asmith(12) dbrees(1)

So I wrote some code for which I needed the following.

• XMLRPC libraries
• XTrustProvider class : to bypass the JRE certificate business
• Atlassian Docs on Creating an XML-RPC Client
• Atlassian XMLRPC javadocs

Then simply use the inefficient code below to get what you need.

package com.shivdev.jira;

import java.net.URL;
import java.util.HashMap;
import java.util.Map;
import java.util.Vector;

import javax.net.ssl.HostnameVerifier;
import javax.net.ssl.HttpsURLConnection;
import javax.net.ssl.SSLSession;

import org.apache.xmlrpc.client.XmlRpcClient;
import org.apache.xmlrpc.client.XmlRpcClientConfigImpl;

import com.shivdev.util.XTrustProvider;

public class JiraXMLRPC4Blog {
	// Our business logic would iterate over these filters and display the total counts and individual breakdowns 
	// To get the Filter IDs, navigate the filter from JIRA and copy the requestId  
	// https://jira.hostname.com:8443/secure/IssueNavigator.jspa?mode=hide&requestId=13611
	static String [][] FILTERS = new String[][] {
			{"13584", "Tennis Project Open Jiras"}, 
			{"13661", "NFL Project Open Items"},
			{"13611", "Tennis Project FRs Ready for Verification"}, 
			{"13583", "NFL Project FRs Fixed in Branch"}, 
	};
	
	// This hostname verification step can be omitted if your not on HTTPS
	static {
		HttpsURLConnection.setDefaultHostnameVerifier(new HostnameVerifier() {
			public boolean verify(String hostname, SSLSession session) {
				// Make sure that hostname is valid
				return true;
			}
		});
	}

	// Constants
	public static final String JIRA_URI = "https://jira.hostname.com:8443"; // JIRA hostname
	public static final String RPC_PATH = "/rpc/xmlrpc"; // JIRA path to xmlrpc
	public static final String METHOD = "getIssuesFromFilter"; // JIRA xmlrpc method
	
	public static void main(String[] args) {
		if (args.length == 2) {
			String user = args[0];
			String passwd = args[1];
			JiraXMLRPC4Blog.printIssueDetails(user, passwd);
		} else {
			System.err.print("Provide username and password as arguments.");
		}
	}
	
	// Business Logic - Inefficiently callJIRARPC - Just for Demo purpose
	// Ideally 1 login multiple RPCs and 1 logout should have been enough  
	public static void printIssueDetails(String user, String passwd) {
		
		// Go over all the filters
		for (int filterIdx = 0; filterIdx < FILTERS.length; filterIdx++) {
			String filterID = FILTERS[filterIdx][0];
			String filterName = FILTERS[filterIdx][1];
			
			Vector<String> params = new Vector<String>(0);
			params.add(filterID); 
			
			// Construct assignees and counts for each filter
			Map<String, Integer> assignees = new HashMap<String, Integer> ();
			Object issues[] = JiraXMLRPC.callJiraRPC(user, passwd, METHOD, params);
			if (issues != null) {
				for (int i = 0; i < issues.length; i++) {
					Map<String, Object> issue = (Map) issues[i];
					// summary, status, votes, assignee, customFieldValues, fixVersions, type, id, reporter, project, created, updated, description, priority, components, affectsVersions, key

					String assignee = (issue.get("assignee")).toString();
					Integer bugCount = assignees.get(assignee);
					assignees.put(assignee, bugCount == null ? 1 : bugCount + 1);
				}
			}
			
			// Print details
			StringBuffer buf = new StringBuffer();
			buf.append(filterName + " (" + issues.length + ") : ");
			for (String assignee : assignees.keySet()) {
				buf.append(assignee + "(" + assignees.get(assignee) + ") ");
			}
			log(buf.toString());
		}
	}
	
	// Actual Sample Code for making XMLRPC calls 
	public static Object[] callJiraRPC(String user, String passwd, String method, Vector methodParams) {
		try {
			XTrustProvider.install();
	
			XmlRpcClient rpcClient;
			XmlRpcClientConfigImpl config;
	
			config = new XmlRpcClientConfigImpl();
			config.setServerURL(new URL(JIRA_URI + RPC_PATH));
			rpcClient = new XmlRpcClient();
			rpcClient.setConfig(config);
			
			// login
			Vector<String> loginParams = new Vector<String>(2);
			loginParams.add(user);
			loginParams.add(passwd);
			String loginToken = (String) rpcClient.execute("jira1.login", loginParams);
			log("Logged in.");
			
			// Create the Authentication Token to be passed with every JiraXMLRPC call
			Vector<String> loginTokenVector = new Vector<String>(1);
			loginTokenVector.add(loginToken);
			
			// Create a list of loginToken + methodParams
			int size = 1 + ((methodParams != null) ? methodParams.size() : 0);  
			Vector paramsVector = new Vector(size);
			paramsVector.addAll(loginTokenVector);
			paramsVector.addAll(methodParams);
						
			// Make the RPC call
			Object[] result = (Object[]) rpcClient.execute( "jira1." + method, paramsVector);
			log(method + " sucess.");
			
			// Log out
			Boolean bool = (Boolean) rpcClient.execute("jira1.logout", loginTokenVector);
			log("Logged out.");
			
			// Return the result
			return result;
			
		} catch (Throwable e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	// Custom Logging Code
	public static void log(String msg) {
		System.out.println(msg);
	}
	
}

For, Oracle

-- This is Case Sensitive
SELECT column_name, data_type
FROM user_tab_cols
WHERE UPPER(table_name) = UPPER('MY_TABLE') 

For, MySQL, you can either do a desc my_table or

SHOW COLUMNS FROM my_table 

For SQL Server

SELECT * 
FROM information_schema.columns 
WHERE table_name = 'my_table'

Yeah! Might have to do something with the Audio Settings. Go to Tools -> Options -> Audio Settings and change the Speakers setting from Headphones to Speakers (or vice versa). In my case I was on a Docking station and had headset connected to it and I needed to have it set to speakers.

To Delete Recent Files (past hour, past day, past week, all)

System Settings -> Privacy -> Delete History

You can leave
Record Activity On/Off

Yeah, this is a tricky one. Is ResultSetMetaData.getColumnName(column) not returning the aliased column?

The simplest way to fix it to use ResultSetMetaData.getColumnLabel(column) which is apparently the “JDBC compliant” way of getting it.

I found the answer on this thread: Bug #43684: JDBC Metadata ColumnName Name is incorrect if using field alias

So, let’s look at this example.

-- Extremely Simplified Top N style nested SQL with aliasing
-- say "Name" and "Score" are two columns in the table "Scores"
-- With this type of SQL, I want to show there's no way to get to them
SELECT t.column1 "Name Alias", t.column2 "Score Alias"  -- alias them back
FROM 
    (SELECT name "column1", AVG(score) "column2"  -- temporary alias 
     FROM scores 
     GROUP BY name ORDER BY AVG(score) DESC) t
LIMIT 3

Now if you look at the following Java Code Snippet

ResultSet rs = stmt.executeQuery(sqlQueryShownAbove);
ResultSetMetaData rsmd = rs.getMetaData();
String firstColumnName = rsmd.getColumnName(1);   // returns "column1"
String firstColumnLabel = rsmd.getColumnLabel(1); // returns "Name Alias"

Well, it’s an API thing. The MySQL JDBC driver has ensured that you can get to the Original Column Name (getColumnName) as well as the Aliased Column Name (getColumnLabel) and has left it to you to select what you need. Just that – it’s not obvious. However in the above example, we’re not getting the Original Column Name at all, but that is because in this case a temporary table was created in the inner SQL which was aliasing the “real” original columns thereby making them the original columns of the temporary table.

In my case, the tricky part was that I was sending the ResultSet to a 3rd party library, which after decompiling seemed to use getColumnName(column). BOOM !!! hack hack hack away
Luckily I had a method in the 3rd party library to override the column labels (PHEW).

But if getColumnLabel not an option as it could have been in my case, you could also try to set ?useOldAliasMetadataBehavior=true in the JDBC connection URL as documented in the MySQL JDBC URL Format.

Here’s the source for com.mysql.jdbc.ResultSetMetaData. Look for the getColumnName(int column) and getColumnLabe(int column) implementations.

Personally, I would have expected or liked to see getOriginalColumnName to get the original column name, but that of course is another discussion.

If you need to search for a class (say HelloWorld) in a lib/ folder with several other jars just to see if it exists, here’s a simple command to confirm that.

find ./lib/*.jar -exec jar -tf ‘{}’ \; | grep HelloWorld.class

For a more detailed listing, you can use my findClass.sh script shown below.

#!/bin/sh

# Check usage
if [ $# -lt 2 ] ; then
    echo "Syntax: $0  <Class> <Folder>"
    echo "Example: $0 HelloWorld.class ./lib/a*.jar"
    exit 1
fi

PATTERN=$1
shift
FILES=$@

# Loop over files and print info for those that match
for f in $FILES
do
	LINES=`jar -tf $f | grep $PATTERN | wc -l`
	if [ $LINES -ge 1 ] ; then
		echo -e "\n\n**** Found match in:" $f " ****\n"	
		jar -tf $f | grep $PATTERN 
	fi
done