How to call Installed query with SET<STRING> as parameter

GSQL
1

I have below Installed query

CREATE DISTRIBUTED QUERY getLocationPrimaryKeys(SET nameList) FOR GRAPH DEV{
SetAccum @@data;
FOREACH name IN nameList DO
RESULT = select loc from SiteLocation:loc where loc.name== name
ACCUM @@data += loc.name+ “,” + loc.sai_id;
END;
PRINT @@data as data;
}

Basically I need to get the primary keys from TG for a list of names.

It is working fine when run from UI. But I am not able to call it from Java using tigergraph-jdbc-driver. Could anyone please help with the code snippet to call from jdbc.

2

Couple quick thoughts:

  1. if the nameList is a set of strings, I would change the parameter to SET<STRING> nameList
  2. you dont actually need the FOREACH loop, since you can use the IN predicate. RESULT = select loc from SiteLocation:loc where loc.name in nameList
  3. as far as the code snippet, you don’t say what issue you are having, but one possible suggestion is that when you are calling a query, you can pass repeated values for the same parameter name, and they will be pass as a list. i.e. nameList=val1&nameList=val2 etc.
3

public List getLocationPrimaryKeys(List locations) {
List locationPrimaryKeys = new ArrayList<>();
PreparedStatement pstmt = null;
try {
Connection con = tigerGraphConnectionManager.createConnection();
String queryInner = “RUN getLocationPrimaryKeys(nameList=?)”;
pstmt = con.prepareStatement(queryInner);
for (LocationDAO location : locations) {
pstmt.setString(1,location.getName());
}

		java.sql.ResultSet rs = pstmt.executeQuery();
		while(rs.next()) {
			Object object = rs.getObject(1);
			log.info("result {} ", object.toString());
		}
		rs.close();
	} catch (SQLException e) {
		e.printStackTrace();
		log.error("Error while setting primary key for Locations {} {}", e.getCause(), e.getMessage());
	} finally {
		if(pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				log.error("Error while closing prepared statement {} {}", e.getCause(), e.getMessage());
			}
		}
	}
	return locationPrimaryKeys;
}

Using above code, but getting result []

4

If you could please share java code showing how you are setting more than one value for the input, it would really help me. Thank you in advance.

5

I would not do it the way that you have it - but I admit that I have generally called installed queries from python and not Java. Perhaps @Jon_Herke can comment on a better approach. The main issue I have is that you shouldn’t have to prepare an SQL statement to run an installed TigerGraph query.

Also, if you use HttpRequest, you could specify the query string to include nameList=val1&nameList=val2