Example of Execute_SQL_Query with parameters

Script

 import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import javax.sql.rowset.WebRowSet;

import com.sun.rowset.WebRowSetImpl;



if ((driver == void) || (driver == null) || driver.equals("")) {
	throw new RuntimeException("The driver must be specified");
}

if ((url == void) || (url == null) || url.equals("")) {
	throw new RuntimeException("The url must be specified");
}

boolean provideXmlBoolean = ((provideXml != void) && (provideXml != null) && Boolean.valueOf(provideXml));

if ((params == void) || (params == null)) {
   params = new ArrayList();
}

if ((sql == void) || (sql == null) || sql.equals("")) {
	throw new RuntimeException("The sql must be specified");
}

Class c = Thread.currentThread().getContextClassLoader().loadClass(driver);

if (c == null) {
	throw new RuntimeException("Class " + driver + " not found");
}

Driver d = c.newInstance();
if (d == null) {
	throw new RuntimeException("Could not create instance of driver");
}

Properties p = new Properties();

if ((userid == void) || (userid == null)) {
	userid = "";
}

p.setProperty("user", userid);

if ((password == void) || (password == null)) {
	password = "";
}

p.setProperty("password", password);

Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {

	con = d.connect(url, p);
	ps = con.prepareStatement(sql);

	int paramSize = params.size();
	for (int i = 0; i < paramSize; i++) {
		ps.setObject(i + 1, params.get(i));
	}

	rs = ps.executeQuery();

	if (provideXmlBoolean) {
		WebRowSet webrs = new WebRowSetImpl();
		StringWriter sw = new StringWriter();
		webrs.writeXml(rs, sw);
		xmlresults = sw.toString();
	} else {
		xmlresults = "";
	}

	try {
		rs.beforeFirst();
	} catch (SQLException e) {
		// redo the query
		rs = ps.executeQuery();
	}

	ResultSetMetaData rsmd = rs.getMetaData();
	int numCols = rsmd.getColumnCount();
	resultList = new ArrayList();
	
	// put the results into the results list.
	while (rs.next()) {
		List row = new ArrayList(numCols);
		for (int i = 0; i < numCols; i++) {
			String str = rs.getString(i + 1); 
			row.add(str == null ? "null" : str); 
		}
		resultList.add(row);
	}
}
finally {
	if (rs != null) {
		rs.close();
	}
	if (ps != null) {
		ps.close();
	}
	if (con != null) {
		con.close();
	}
}
  

 

Value

Value

Value

Value

Script

outputlist=new ArrayList();
for (i = inputlist.iterator(); i.hasNext();) {
         ArrayList listtoadd=new ArrayList();
         listtoadd.add(i.next());
	outputlist.add(listtoadd);
}

Value