You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@groovy.apache.org by "Ryan Mills (JIRA)" <ji...@apache.org> on 2016/03/09 06:22:40 UTC
[jira] [Comment Edited] (GROOVY-7768) groovy.sql.Sql
callWithAllRows returns blank result when passing params
[ 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)