[ https://issues.apache.org/jira/browse/GROOVY-7768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15186530#comment-15186530 ]
Ryan Mills edited comment on GROOVY-7768 at 3/9/16 5:22 AM: ------------------------------------------------------------ public class SqlCall { public DataSource dataSource; public Sql sql; public void setDataSource(DataSource dataSource){ this.dataSource = dataSource this.sql = new Sql(this.dataSource) } public List<Map> call(sqlString){ List<Map> rows = sql.rows(sqlString); return rows; } public List<Map> call(sqlString, args){ List argsList = orderedParams(sqlString, args) String cleanSql = stripSqlString(sqlString) List<Map> rows = sql.rows(cleanSql, argsList); return rows; } /** * Standard callWithAll rows. Does not work with sprocs with params * @param sqlString * @param args * @return */ public List<List<Map>> multipleCall(sqlString, args){ List argsList = orderedParams(sqlString, args) String cleanSql = stripSqlString(sqlString) //println("sql:" + cleanSql); //println("args:" + argsList); List<List<Map>> rows = sql.callWithAllRows(cleanSql, argsList, {}) //println("rows:" + rows); return rows; } public List orderedParams(sqlString, args){ if (args instanceof List){ return args } List returnList = new ArrayList(); List valueList = new ArrayList<String>(); def m = namedParamMatches(sqlString) while (m.find()) { valueList.add(m.group().replace(" ", "").replace(":", "")) } for(String s: valueList ){ returnList.add(args.get(s)) } // using the sqlString :params, order the args to match, turn into ? return returnList } public String stripSqlString(inString){ def m = namedParamMatches(inString) while (m.find()) { inString = inString.replace(m.group(), "?") } return inString } def static Matcher namedParamMatches(inString) { Pattern p = Pattern.compile("(:[a-zA-Z]\\w+)"); //\w mean [a-zA-Z_0-9] Matcher m = p.matcher(inString); return m; //while m.find() {m.group()} } /** * Returns multiple result sets, supports params, sprocs * @param query * @param args * @return */ public List<List<Map>> rows(String query, args){ List argsList = orderedParams(query, args) String cleanSql = stripSqlString(query) List<List<Map>> rows = new ArrayList<List<Map>>(); int rowCounter = -1; List<Map> rowList = new ArrayList<Map>(); eachSprocRow(cleanSql, argsList) {rsCount, row -> //println "rsCount:" + rsCount //println "row:" + row if(rowCounter != rsCount ){ if(rowCounter > -1) { rows.add(rowList); // new result set now rowList = new ArrayList<Map>(); } rowCounter = rsCount } rowList.add(row); // add this row to the current list } rows.add(rowList); // catch the first return rows; } /** * Call with all rows does not work on compiled sprocs with params * * @param query * @param parameters * @param closure */ public void eachSprocRow(String query, List parameters, Closure closure) { sql.cacheConnection { Connection con -> CallableStatement proc = con.prepareCall(query) try { parameters.eachWithIndex { param, i -> proc.setObject(i+1, param) } //System.err.println "executing..." try{ // display = "executing..." boolean result = proc.execute() // display = "" }catch (Exception e){ System.err.println e // display = e.toString() return } //boolean moreResults = true boolean found = false int rsCount = 0; while (true) { //println result ResultSet rs = proc.getResultSet() if (rs != null) { //ResultSet rs = proc.getResultSet() ResultSetMetaData md = rs.getMetaData() int columnCount = md.getColumnCount() while (rs.next()) { Map row = new LinkedHashMap() for (int i = 0; i < columnCount; ++ i) { row[md.getColumnName(i+1)] = rs.getObject(i+1) } closure.call(rsCount, row) } found = true; } if((proc.getMoreResults() == false) && (proc.getUpdateCount() == -1)){ break; } rsCount++; } } finally { proc.close() } } } } was (Author: oniseijin): <code> public class SqlCall { public DataSource dataSource; public Sql sql; public void setDataSource(DataSource dataSource){ this.dataSource = dataSource this.sql = new Sql(this.dataSource) } public List<Map> call(sqlString){ List<Map> rows = sql.rows(sqlString); return rows; } public List<Map> call(sqlString, args){ List argsList = orderedParams(sqlString, args) String cleanSql = stripSqlString(sqlString) List<Map> rows = sql.rows(cleanSql, argsList); return rows; } /** * Standard callWithAll rows. Does not work with sprocs with params * @param sqlString * @param args * @return */ public List<List<Map>> multipleCall(sqlString, args){ List argsList = orderedParams(sqlString, args) String cleanSql = stripSqlString(sqlString) //println("sql:" + cleanSql); //println("args:" + argsList); List<List<Map>> rows = sql.callWithAllRows(cleanSql, argsList, {}) //println("rows:" + rows); return rows; } public List orderedParams(sqlString, args){ if (args instanceof List){ return args } List returnList = new ArrayList(); List valueList = new ArrayList<String>(); def m = namedParamMatches(sqlString) while (m.find()) { valueList.add(m.group().replace(" ", "").replace(":", "")) } for(String s: valueList ){ returnList.add(args.get(s)) } // using the sqlString :params, order the args to match, turn into ? return returnList } public String stripSqlString(inString){ def m = namedParamMatches(inString) while (m.find()) { inString = inString.replace(m.group(), "?") } return inString } def static Matcher namedParamMatches(inString) { Pattern p = Pattern.compile("(:[a-zA-Z]\\w+)"); //\w mean [a-zA-Z_0-9] Matcher m = p.matcher(inString); return m; //while m.find() {m.group()} } /** * Returns multiple result sets, supports params, sprocs * @param query * @param args * @return */ public List<List<Map>> rows(String query, args){ List argsList = orderedParams(query, args) String cleanSql = stripSqlString(query) List<List<Map>> rows = new ArrayList<List<Map>>(); int rowCounter = -1; List<Map> rowList = new ArrayList<Map>(); eachSprocRow(cleanSql, argsList) {rsCount, row -> //println "rsCount:" + rsCount //println "row:" + row if(rowCounter != rsCount ){ if(rowCounter > -1) { rows.add(rowList); // new result set now rowList = new ArrayList<Map>(); } rowCounter = rsCount } rowList.add(row); // add this row to the current list } rows.add(rowList); // catch the first return rows; } /** * Call with all rows does not work on compiled sprocs with params * * @param query * @param parameters * @param closure */ public void eachSprocRow(String query, List parameters, Closure closure) { sql.cacheConnection { Connection con -> CallableStatement proc = con.prepareCall(query) try { parameters.eachWithIndex { param, i -> proc.setObject(i+1, param) } //System.err.println "executing..." try{ // display = "executing..." boolean result = proc.execute() // display = "" }catch (Exception e){ System.err.println e // display = e.toString() return } //boolean moreResults = true boolean found = false int rsCount = 0; while (true) { //println result ResultSet rs = proc.getResultSet() if (rs != null) { //ResultSet rs = proc.getResultSet() ResultSetMetaData md = rs.getMetaData() int columnCount = md.getColumnCount() while (rs.next()) { Map row = new LinkedHashMap() for (int i = 0; i < columnCount; ++ i) { row[md.getColumnName(i+1)] = rs.getObject(i+1) } closure.call(rsCount, row) } found = true; } if((proc.getMoreResults() == false) && (proc.getUpdateCount() == -1)){ break; } rsCount++; } } finally { proc.close() } } } } </code> > groovy.sql.Sql callWithAllRows returns blank result when passing params > ----------------------------------------------------------------------- > > Key: GROOVY-7768 > URL: https://issues.apache.org/jira/browse/GROOVY-7768 > Project: Groovy > Issue Type: Bug > Components: SQL processing > Affects Versions: 2.4.0 > Reporter: Ryan Mills > Original Estimate: 5h > Remaining Estimate: 5h > > callWithAllRows works with regular sql, and an empty list. > call works with a map or list > Howver, callWithAllRows returns an empty list when using with params eg.) > List list = new ArrayList(); > l.add("myid"); > sql.callWithAllRows("sp_who ?", list, {}); > result is [] -- This message was sent by Atlassian JIRA (v6.3.4#6332)