<%@page import="java.util.Collections"%> <%@page import="java.util.Arrays"%> <%@page contentType="text/html; charset=UTF-8"%> <%@page import="java.io.File"%> <%@page import="java.io.FileReader"%> <%@page import="java.io.BufferedReader"%> <%@page import="java.io.FileInputStream"%> <%@page import="java.io.FileWriter"%> <%@page import="java.io.FilenameFilter"%> <%@page import="java.util.Properties"%> <%@page import="java.util.Hashtable"%> <%@page import="java.util.Enumeration"%> <%@page import="java.util.Vector"%> <%@page import="java.net.URLEncoder"%> <%@page import="java.sql.Connection"%> <%@page import="java.sql.PreparedStatement"%> <%@page import="java.sql.ResultSet"%> <%@page import="java.sql.SQLException"%> <%@page import="java.sql.ResultSetMetaData"%> <%@page import="javax.servlet.jsp.JspWriter"%> <%@page import="java.text.MessageFormat"%> <%@page import="org.crosswire.data.CSVFileReader"%> <%@page import="org.crosswire.sword.keys.VerseKey"%> <%@page import="org.crosswire.data.DataObject"%> <%@page import="org.crosswire.utils.HTTPUtils"%> <%@page import="java.net.URLDecoder"%> SQL Test Harness

SQL Test Harness

<% String sqlPath = request.getRealPath("sql"); try { javax.naming.Context ctx = new javax.naming.InitialContext(); if (ctx == null) throw new Exception("No Context"); sqlPath = (java.lang.String) ctx.lookup("java:comp/env/testSQLPath"); } catch (Exception e) {} String catTreeChange = request.getParameter("close"); if (catTreeChange != null) { session.setAttribute("catExp", ""); } catTreeChange = request.getParameter("open"); if (catTreeChange != null) { session.setAttribute("catExp", catTreeChange); } File directory = new File(sqlPath); File[] sqlFiles = directory.listFiles( new FilenameFilter() { public boolean accept(File dir, String name) { return name.endsWith(".properties"); } }); int fileCount = (sqlFiles == null) ? 0 : sqlFiles.length; Object[] sortedkeys = null; Hashtable files = new Hashtable(); Hashtable provides = new Hashtable(); %>

Directory Read: <%= directory.getCanonicalPath() %>
Total SQL Set Count: <%= fileCount %>

<%if (fileCount > 0) {%>
<% for (int i = 0; i < fileCount; i++) { try { Properties sql = new Properties(); sql.load(new FileInputStream(sqlFiles[i])); String testCat = sql.getProperty("Category"); String testName = sql.getProperty("Name"); files.put(testCat + testName, sql); } catch (Exception e) {e.printStackTrace();} // don't care if we couldn't open one entry } Properties sql = null; Enumeration keys = files.keys(); String curCat = ""; Vector sorted = new Vector(); while (keys.hasMoreElements()) { sorted.add(keys.nextElement()); } sortedkeys = sorted.toArray(); java.util.Arrays.sort(sortedkeys); boolean expandCat = false; boolean ulOpen = false; String catExp = (String) session.getAttribute("catExp"); for (int i = 0; i < sortedkeys.length; i++) { sql = (Properties) files.get(sortedkeys[i]); String testCat = sql.getProperty("Category"); String testName = sql.getProperty("Name"); String provide = sql.getProperty("Provides"); if (provide != null) provides.put(provide, new Integer(i)); if ((testCat != null) && (!testCat.equalsIgnoreCase(curCat))) { if (ulOpen) out.println(""); expandCat = (testCat.equals(catExp)); out.println("

[ " + ((expandCat) ? "-" : "+") + " ] " + testCat + "

"); curCat = testCat; out.println("
    "); ulOpen = true; } if (!expandCat) continue; %>
  • <%= testName %>
    • <% } if (ulOpen) out.println("
"); } String numStr = request.getParameter("exec"); String stageStr = request.getParameter("stage"); if (numStr != null) { int num = Integer.parseInt(numStr); int stage = (stageStr != null) ? Integer.parseInt(stageStr) : 1; if (num < sqlFiles.length) { Properties sqlTest = (Properties) files.get(sortedkeys[num]); String require = sqlTest.getProperty("Requires"); if ((require != null) && (session.getAttribute(require) == null)) { Integer reqExec = (Integer)provides.get(require); if (reqExec != null) sqlTest = (Properties) files.get(sortedkeys[reqExec.intValue()]); } String testName = sqlTest.getProperty("Name"); String testDesc = sqlTest.getProperty("Description"); %>
Executing <%= testName %>
Description <%= testDesc %>
<% Vector props = new Vector(); String prop = null; int j = 0; do { //out.println("

Trying to get val" + Integer.toString(j)); prop = request.getParameter("val" + Integer.toString(j)); if (prop != null) { props.add(prop); //out.println("

Adding: " + prop + "

"); } j++; } while (prop != null); Object[] params = props.toArray(); String testSQL = ""; int outputLevel = 10; boolean didSomething = true; for (int i = stage; didSomething; i++) { didSomething = false; String suffix = ((i < 2) ? "" : Integer.toString(i)); testSQL = sqlTest.getProperty("SQL" + suffix); if (testSQL != null) { testSQL = testSQL.replaceAll("\'", "\'\'"); } String heading = sqlTest.getProperty("Heading" + suffix); String linkColumn = sqlTest.getProperty("LinkColumn" + suffix); String importFile = sqlTest.getProperty("ImportFile" + suffix); String sqlExecuteFile = sqlTest.getProperty("SQLExecuteFile" + suffix); String importSQL = sqlTest.getProperty("ImportSQL" + suffix); String import2LSQL = sqlTest.getProperty("Import2LSQL" + suffix); String importDupCheck = sqlTest.getProperty("ImportDupCheck" + suffix); String ignoreException = sqlTest.getProperty("IgnoreException" + suffix); String exportFile = sqlTest.getProperty("ExportFile" + suffix); String formField = sqlTest.getProperty("Form" + suffix + ".0"); String sessionField = sqlTest.getProperty("SetSession" + suffix + ".0"); try { outputLevel = Integer.parseInt(sqlTest.getProperty("OutputLevel" + suffix)); } catch (Exception e) {} // see if we have a form if (heading != null) { %>

<%= heading %>

<% } if (sqlExecuteFile != null) { FileReader fin = new FileReader(sqlPath+"/"+sqlExecuteFile); BufferedReader bis = new BufferedReader(fin); String execLine = ""; for (String line = bis.readLine(); line != null; line = bis.readLine()) { line = line.trim(); if (line.length() > 1) { execLine += line; if (execLine.endsWith(";")) { runSQL(execLine, null, 0, 0, out); execLine = ""; } } } bis.close(); fin.close(); } if (importFile != null) { didSomething = true; String fileName = importFile; String iSQL = ""; int separator = fileName.indexOf('|'); if (separator > -1) { fileName = importFile.substring(0, separator); iSQL = importFile.substring(separator + 1); } try { CSVFileReader fin = new CSVFileReader(new File(sqlPath+"/"+fileName), '|', false); iSQL = iSQL.replaceAll("\'", "\'\'"); iSQL = replaceSessionTags(session, iSQL); if (importDupCheck != null) { importDupCheck = importDupCheck.replaceAll("\'", "\'\'"); importDupCheck = replaceSessionTags(session, importDupCheck); } int retVal = 0; for (boolean more = fin.begin(); more; more = fin.next()) { retVal++; String colVals = ""; Vector fields = new Vector(Arrays.asList(fin.getFields())); String insertSQL = iSQL.trim(); int loopStart = -1; int loopEnd = -1; Vector loopValues = null; boolean useVerseKey = false; if (insertSQL.startsWith("[")) { if (insertSQL.startsWith("[v")) { useVerseKey = true; } int offset = insertSQL.indexOf("]"); if (offset > 0) { String loopString = insertSQL.substring((useVerseKey)?2:1,offset); insertSQL = insertSQL.substring(offset+1); MessageFormat format = new MessageFormat(loopString); loopString = format.format(fields.toArray()); offset = loopString.indexOf(".."); if (offset > 0) { try { loopStart = Integer.parseInt(loopString.substring(0,offset)); } catch (Exception e) {}; loopString = loopString.substring(offset+2); try { loopEnd = Integer.parseInt(loopString); } catch (Exception e) {}; fields.add(0,Integer.toString(loopStart)); } else { offset = loopString.indexOf("/"); if (offset > 0) { loopValues = new Vector(); String loopValue = loopString.substring(0,offset); String vsep = loopString.substring(offset+1); offset = 0; while (offset >= 0) { int last = offset; offset = loopValue.indexOf(vsep, last); loopValues.add((offset>0)?loopValue.substring(last, offset) : loopValue.substring(last)); if (offset >= 0) offset++; } fields.add(0,loopValues.size() > 0 ? loopValues.get(0) : ""); loopStart = 0; loopEnd = loopValues.size()-1; } } } } VerseKey myKey = new VerseKey(); myKey.setAutoNormalize(false); char error = 0; if (useVerseKey) myKey.setHashNumber(loopStart); Object dummy = null; for (; ((useVerseKey && myKey.getHashNumber() <= loopEnd && myKey.popError()==0) || (!useVerseKey && loopStart <= loopEnd)); myKey.increment(),loopStart = (useVerseKey)? myKey.getHashNumber() : loopStart + 1) { if (loopEnd != -1) fields.set(0, loopValues != null ? loopValues.get(loopStart) : Integer.toString(loopStart)); boolean execute = true; if (importDupCheck != null) { MessageFormat format = new MessageFormat(importDupCheck); String dcSQL = format.format(fields.toArray()); execute = (runSQL(dcSQL, linkColumn, num, i + 1, null) == 0); } if (execute) { String rSQL = specialFunctions(insertSQL, fields.toArray()); MessageFormat format = new MessageFormat(rSQL); rSQL = format.format(fields.toArray()); runSQL(rSQL, linkColumn, num, i + 1, out); } } if (error != 0) { out.write("

ERROR: Verse Out Of Bounds: "+loopEnd+"

" + insertSQL + "

"); } } } catch (Exception e) { e.printStackTrace(); } } if (importSQL != null) { didSomething = true; String sourceSQL = importSQL; String iSQL = ""; int separator = sourceSQL.indexOf('|'); if (separator > -1) { sourceSQL = importSQL.substring(0, separator); iSQL = importSQL.substring(separator + 1); } try { int rows = importSQL(sourceSQL, iSQL, importDupCheck, out, outputLevel, ignoreException); if (outputLevel >= 1) out.println("

Total Records: " + rows + "

"); } catch (Exception e) { e.printStackTrace(); } } if (import2LSQL != null) { didSomething = true; String sourceSQL = import2LSQL; String source2SQL = import2LSQL; String iSQL = ""; int separator = sourceSQL.indexOf('|'); if (separator > -1) { source2SQL = sourceSQL.substring(separator + 1); sourceSQL = sourceSQL.substring(0, separator); } separator = source2SQL.indexOf('|'); if (separator > -1) { iSQL = source2SQL.substring(separator + 1); source2SQL = source2SQL.substring(0, separator); } try { int rows = import2LSQL(sourceSQL, source2SQL, iSQL, importDupCheck, out, outputLevel, ignoreException); if (outputLevel >= 1) out.println("

Total Records: " + rows + "

"); } catch (Exception e) { e.printStackTrace(); } } if (exportFile != null) { didSomething = true; String fileName = exportFile; String iSQL = ""; int separator = fileName.indexOf('|'); if (separator > -1) { fileName = exportFile.substring(0, separator); iSQL = exportFile.substring(separator + 1); } try { FileWriter fout = new FileWriter(new File(sqlPath+"/"+fileName)); iSQL = replaceSessionTags(session, iSQL); exportSQL(iSQL, fout, out); fout.close(); } catch (Exception e) { e.printStackTrace(); } } if (sessionField != null) { didSomething = true; j = 0; do { sessionField = replaceSessionTags(session, sessionField); MessageFormat format = new MessageFormat(sessionField); sessionField = format.format(params); String fieldName = sessionField; String fieldValue = ""; int separator = sessionField.indexOf('|'); if (separator > -1) { fieldName = sessionField.substring(0, separator); fieldValue = sessionField.substring(separator + 1); } session.setAttribute(fieldName, fieldValue); sessionField = sqlTest.getProperty("SetSession" + suffix + "." + Integer.toString(++j)); } while (sessionField != null); } if (formField != null) { didSomething = true; %>
<% j = 0; do { formField = replaceSessionTags(session, formField); MessageFormat format = new MessageFormat(formField); formField = format.format(params); String fieldHeading = formField; String fieldDefault = ""; String fieldType = "text"; int separator = formField.indexOf('|'); if (separator > -1) { fieldHeading = formField.substring(0, separator); fieldDefault = formField.substring(separator + 1); } separator = fieldDefault.indexOf('|'); if (separator > -1) { fieldType = fieldDefault.substring(separator + 1); fieldDefault = fieldDefault.substring(0, separator); } %> <% formField = sqlTest.getProperty("Form" + suffix + "." + Integer.toString(++j)); } while (formField != null); %>
<%= fieldHeading %>
<% break; } // assert we have an SQL String to execute if (testSQL == null) continue; didSomething = true; testSQL = replaceSessionTags(session, testSQL); testSQL = specialFunctions(testSQL, params); // excape any remaining {} for MesageFormat testSQL = testSQL.replace("{", "'{'"); testSQL = testSQL.replace("}", "'}'"); MessageFormat format = new MessageFormat(testSQL); testSQL = format.format(params); runSQL(testSQL, linkColumn, num, i + 1, out); if (linkColumn != null) { break; } String nextStage = sqlTest.getProperty("Next" + suffix); try { i = Integer.parseInt(nextStage) - 1; } catch (Exception e) {e.printStackTrace(); } String newZero = sqlTest.getProperty("NewZero" + suffix); try { params[0] = params[Integer.parseInt(newZero)]; } catch (Exception e) {e.printStackTrace();} } } } %>
<%! private static String specialFunctions(String buf, Object fields[]) { //System.out.println("buf in: "+buf); int offset = -1; buf.indexOf("[r1:"); int offsetEnd = -1; boolean more = true; while (more) { offset = buf.indexOf("[r1:"); if (offset > -1) { offsetEnd = buf.indexOf("]", offset+1); if (offsetEnd > -1) { String romanText = buf.substring(offset+4, offsetEnd); MessageFormat format = new MessageFormat(romanText); romanText = format.format(fields); int slash = romanText.indexOf("/"); if (slash > -1) romanText = romanText.substring(0, slash); int romanValue = VerseKey.fromRoman(romanText) * 100; romanValue -= 100; buf = buf.substring(0,offset) + romanValue + buf.substring(offsetEnd+1); continue; } } offset = buf.indexOf("[r2:"); offsetEnd = -1; if (offset > -1) { offsetEnd = buf.indexOf("]", offset+1); if (offsetEnd > -1) { String romanText = buf.substring(offset+4, offsetEnd); MessageFormat format = new MessageFormat(romanText); romanText = format.format(fields); int slash = romanText.indexOf("/"); if (slash > -1) romanText = romanText.substring(slash+1); int romanValue = VerseKey.fromRoman(romanText) * 100; romanValue -= 1; buf = buf.substring(0,offset) + romanValue + buf.substring(offsetEnd+1); continue; } } more = false; } //System.out.println("buf out: "+buf); return buf; } static int tickCount = 0; private static void outputTick(JspWriter out) throws Exception { out.print("."); ++tickCount; if (tickCount%1000 == 0) { out.print("
"); tickCount = 0; } } // Display an SQLException which has occured in this application. private static String getAllSQLExceptions(java.sql.SQLException e) { StringBuffer retVal = new StringBuffer(); java.sql.SQLException next = e; while (next != null) { try { retVal.append(next.getMessage()+" "); retVal.append("Error Code: " + next.getErrorCode() + " "); retVal.append("SQL State: " + next.getSQLState() + " "); } catch (Exception e1) { e1.printStackTrace(); } next = next.getNextException(); } return retVal.toString(); } private static String replaceSessionTags(javax.servlet.http.HttpSession session, String inString) { int i = 1; for (int offset = inString.indexOf('{', i); offset > -1; offset = inString.indexOf('{', i)) { if (offset == 0 || inString.charAt(offset-1) !='\\') { int end = inString.indexOf('}', offset); if ((end > -1) && (!Character.isDigit(inString.charAt(offset+1)))) { String sessionAtt = inString.substring(offset+1, end); String sessionVal = (String)session.getAttribute(sessionAtt); inString = inString.substring(0, offset) + ((sessionVal == null) ? "":sessionVal) + inString.substring(end+1); } } else inString = inString.substring(0,offset-1) + inString.substring(offset); i = offset+1; } System.out.println("inString: " + inString); return inString; } private synchronized static Connection getDBConnection(JspWriter out) { javax.sql.DataSource ds = null; Connection connRSFind = null; try { javax.naming.Context ctx = new javax.naming.InitialContext(); if (ctx == null) throw new Exception("Boom - No Context"); ds = (javax.sql.DataSource) ctx.lookup("java:comp/env/storeDB"); try { connRSFind = ds.getConnection(); } catch (java.sql.SQLException e) { System.out.println("getting new data source connection failed! Error: " + e + "\n"); } } catch (Exception e) { System.out.println("Could Not get data source, error: " + e + "\n"); } return connRSFind; } private static int runSQL(String testSQL, String linkColumn, int exec, int stage, JspWriter out) { return runSQL(testSQL, linkColumn, exec, stage, out, 10, null); } private static int runSQL(String testSQL, String linkColumn, int exec, int stage, JspWriter out, int outputLevel, String ignoreException) { Connection c = null; PreparedStatement stmt = null; ResultSet rs = null; int retVal = 0; String result = "Success"; SQLException resultException = null; try { c = getDBConnection(out); if (c == null) { out.println("
couldn't get DB connection"); return 0; } try { stmt = c.prepareStatement(testSQL); if ((testSQL+" ").substring(0, 10).toUpperCase().trim().startsWith("SELECT")) { rs = stmt.executeQuery(); } else { int r = stmt.executeUpdate(); result = (r == 0) ? "Success" : Integer.toString(r); } } catch (SQLException e) { result = "Exception"; if (ignoreException == null || getAllSQLExceptions(e).indexOf(ignoreException) < 0) resultException = e; } catch (Exception e1) { // if (outputLevel >= 9) { e1.printStackTrace(); // } result = e1.toString(); } if (out != null) { if (outputLevel >= 9) out.write(""); else if (outputLevel < 6) outputTick(out); if (rs == null) { if (resultException != null && outputLevel < 6) out.write("
"); if (outputLevel >=9) out.println(""); else if (outputLevel >= 6 || resultException != null) out.println("Result: " + result + " SQL: " + testSQL + "
"); if (resultException != null) { if (outputLevel >= 9) out.println(""); else out.println("
"); } } if (outputLevel >= 9) out.println("
Executing SQL " + testSQL + "
Result " + result + "
Exception "); out.println(getAllSQLExceptions(resultException)); if (outputLevel >= 9) out.println("

"); if (rs != null) { ResultSetMetaData meta = rs.getMetaData(); int colCount = meta.getColumnCount(); if (outputLevel >= 7) { out.println(""); for (int i = 0; i < colCount; i++) { String colName = meta.getColumnName(i + 1); out.println(""); } out.println(""); } if (outputLevel >= 5) { while (rs.next()) { if (outputLevel > 5) { retVal++; if (outputLevel >= 7) out.print(""); else if (outputLevel >= 5) out.print("" + rs.getRow() + ""); String colVals = ""; for (int i = 1; i < colCount + 1; i++) { Object o = rs.getObject(i); if (o == null) o = ""; colVals += "&val" + Integer.toString(i) + "=" + URLEncoder.encode(o.toString()); } for (int i = 0; i < colCount; i++) { Object o = rs.getObject(i + 1); String colVal = (o != null) ? o.toString() : ""; if (outputLevel >= 7) out.print(""); } if (outputLevel >= 7) out.print(""); else if (outputLevel >= 5) out.print("
"); } else outputTick(out); } if (outputLevel >= 7) out.println("
" + colName + "
" + rs.getRow() + ""); if (meta.getColumnName(i + 1).equalsIgnoreCase(linkColumn)) { out.print("" + colVal + ""); } else { out.println(colVal); } if (outputLevel >= 7) out.print("
"); } } } // out != null } catch (Exception e) { e.printStackTrace(); } finally { try { if (stmt != null) { stmt.close(); } if (c != null) { c.close(); } } catch (Exception e) { e.printStackTrace(); } } return retVal; } private static int exportSQL(String testSQL, FileWriter out, JspWriter errOut) { Connection c = null; PreparedStatement stmt = null; ResultSet rs = null; int retVal = 0; String result = "Success"; SQLException resultException = null; try { c = getDBConnection(errOut); if (c == null) { errOut.println("
couldn't get DB connection"); return 0; } try { stmt = c.prepareStatement(testSQL); if ((testSQL+" ").substring(0, 10).toUpperCase().trim().startsWith("SELECT")) { rs = stmt.executeQuery(); } else { int r = stmt.executeUpdate(); result = (r == 0) ? "Success" : Integer.toString(r); } } catch (SQLException e) { result = "Exception"; resultException = e; } catch (Exception e1) { result = e1.toString(); e1.printStackTrace(); } if (errOut != null) { errOut.write(""); if (rs == null) { errOut.println(""); if (resultException != null) { errOut.println(""); } } errOut.println("
Executing SQL " + testSQL + "
Result " + result + "
Exception "); errOut.println(getAllSQLExceptions(resultException)); errOut.println("

"); } if (rs != null) { ResultSetMetaData meta = rs.getMetaData(); int colCount = meta.getColumnCount(); if (out != null) { out.write("# "); for (int i = 0; i < colCount; i++) { String colName = meta.getColumnName(i + 1); out.write(colName); if (i + 1 < colCount) out.write("|"); } out.write("\n"); } while (rs.next()) { retVal++; if (out != null) { String colVals = ""; for (int i = 0; i < colCount; i++) { Object o = rs.getObject(i + 1); String colVal = (o != null) ? o.toString() : ""; // colVal = URLEncoder.encode(colVal, "UTF-8"); out.write(colVal); if (i + 1 < colCount) out.write("|"); } out.write("\n"); } } errOut.write(" ResultExported Users: " + retVal + ""); } errOut.println("
"); } catch (Exception e) { e.printStackTrace(); } finally { try { if (stmt != null) { stmt.close(); } if (c != null) { c.close(); } } catch (Exception e) { e.printStackTrace(); } } return retVal; } private static int importSQL(String sourceSQL, String iSQL, String importDupCheck, JspWriter errOut, int outputLevel, String ignoreException) { Connection c = null; PreparedStatement stmt = null; ResultSet rs = null; int retVal = 0; String result = "Success"; SQLException resultException = null; // sourceSQL = sourceSQL.replaceAll("\'", "\'\'"); iSQL = iSQL.replaceAll("\'", "\'\'"); try { c = getDBConnection(errOut); if (c == null) { errOut.println("
couldn't get DB connection"); return 0; } try { stmt = c.prepareStatement(sourceSQL); if ((sourceSQL+" ").substring(0, 10).toUpperCase().trim().startsWith("SELECT")) { rs = stmt.executeQuery(); } else { int r = stmt.executeUpdate(); result = (r == 0) ? "Success" : Integer.toString(r); } } catch (SQLException e) { result = "Exception"; if (ignoreException == null || getAllSQLExceptions(e).indexOf(ignoreException) < 0) resultException = e; } catch (Exception e1) { result = e1.toString(); if (outputLevel >= 9) e1.printStackTrace(); } if (errOut != null) { if (outputLevel >= 10) errOut.write(""); else if (outputLevel < 7) outputTick(errOut); if (rs == null) { if (resultException != null && outputLevel < 7) errOut.write("
"); if (outputLevel >= 10) errOut.println(""); else if (outputLevel >= 7 || resultException != null) errOut.println("Result: " + result + " SQL: "+sourceSQL+"
"); if (resultException != null) { if (outputLevel >= 10) errOut.println(""); else errOut.println("
"); } } if (outputLevel >= 10) errOut.println("
Executing SQL " + sourceSQL + "
Result " + result + "
Exception "); errOut.println(getAllSQLExceptions(resultException)); if (outputLevel >= 10) errOut.println("

"); } if (rs != null) { ResultSetMetaData meta = rs.getMetaData(); int colCount = meta.getColumnCount(); while (rs.next()) { retVal++; String insertSQLs[] = iSQL.trim().split(";\\|"); for (String insertSQL : insertSQLs) { String colVals = ""; Vector fields = new Vector(); for (int i = 0; i < colCount; i++) { Object o = rs.getObject(i + 1); String colVal = (o != null) ? o.toString() : ""; fields.add(colVal.replaceAll("\'", "\'\'")); // colVal = URLEncoder.encode(colVal, "UTF-8"); } insertSQL = insertSQL.trim(); int loopStart = -1; int loopEnd = -1; boolean useVerseKey = false; if (insertSQL.startsWith("[")) { if (insertSQL.startsWith("[v")) { useVerseKey = true; } int offset = insertSQL.indexOf("]"); if (offset > 0) { String loopString = insertSQL.substring((useVerseKey)?2:1,offset); insertSQL = insertSQL.substring(offset+1); MessageFormat format = new MessageFormat(loopString); loopString = format.format(fields.toArray()); offset = loopString.indexOf(".."); if (offset > 0) { try { loopStart = Integer.parseInt(loopString.substring(0,offset)); } catch (Exception e) {}; loopString = loopString.substring(offset+2); } try { loopEnd = Integer.parseInt(loopString); } catch (Exception e) {}; fields.add(0,Integer.toString(loopStart)); } } VerseKey myKey = new VerseKey(); myKey.setAutoNormalize(false); char error = 0; if (useVerseKey) myKey.setHashNumber(loopStart); Object dummy = null; for (; ((useVerseKey && myKey.getHashNumber() <= loopEnd && myKey.popError()==0) || (!useVerseKey && loopStart <= loopEnd)); myKey.increment(),loopStart = (useVerseKey)? myKey.getHashNumber() : loopStart + 1) { if (loopEnd != -1) fields.set(0, Integer.toString(loopStart)); boolean execute = true; if (importDupCheck != null) { MessageFormat format = new MessageFormat(importDupCheck); String dcSQL = format.format(fields.toArray()); execute = (runSQL(dcSQL, null, 0, 0, null) == 0); } if (execute) { String rSQL = specialFunctions(insertSQL, fields.toArray()); MessageFormat format = new MessageFormat(rSQL); rSQL = format.format(fields.toArray()); runSQL(rSQL, null, 0, 0, errOut, outputLevel, ignoreException); } } if (error != 0) { if (errOut != null) errOut.write("

ERROR: Verse Out Of Bounds: "+loopEnd+"

" + insertSQL + "

"); } } } } } catch (Exception e) { e.printStackTrace(); } finally { try { if (stmt != null) { stmt.close(); } if (c != null) { c.close(); } } catch (Exception e) { e.printStackTrace(); } } return retVal; } private static int import2LSQL(String sourceSQL, String source2SQL, String iSQL, String importDupCheck, JspWriter errOut, int outputLevel, String ignoreException) { Connection c = null; PreparedStatement stmt = null; ResultSet rs = null; int retVal = 0; String result = "Success"; SQLException resultException = null; source2SQL = source2SQL.replaceAll("\'", "\'\'"); try { c = getDBConnection(errOut); if (c == null) { errOut.println("
couldn't get DB connection"); return 0; } try { stmt = c.prepareStatement(sourceSQL); if ((sourceSQL+" ").substring(0, 10).toUpperCase().trim().startsWith("SELECT")) { rs = stmt.executeQuery(); } else { int r = stmt.executeUpdate(); result = (r == 0) ? "Success" : Integer.toString(r); } } catch (SQLException e) { result = "Exception"; if (ignoreException == null || getAllSQLExceptions(e).indexOf(ignoreException) < 0) resultException = e; } catch (Exception e1) { result = e1.toString(); if (outputLevel >= 9) e1.printStackTrace(); } if (errOut != null) { if (outputLevel >= 9) errOut.write(""); else if (outputLevel < 6) outputTick(errOut); if (rs == null) { if (resultException != null && outputLevel < 6) errOut.write("
"); if (outputLevel >= 9) errOut.println(""); else if (outputLevel >= 6 || resultException != null) errOut.println("Result: " + result + " SQL: "+sourceSQL+"
"); if (resultException != null) { if (outputLevel >= 9) errOut.println(""); else errOut.println("
"); } } if (outputLevel >= 9) errOut.println("
Executing SQL " + sourceSQL + "
Result " + result + "
Exception "); errOut.println(getAllSQLExceptions(resultException)); if (outputLevel >= 9) errOut.println("

"); } if (rs != null) { ResultSetMetaData meta = rs.getMetaData(); int colCount = meta.getColumnCount(); while (rs.next()) { String colVals = ""; Vector fields = new Vector(); for (int i = 0; i < colCount; i++) { Object o = rs.getObject(i + 1); String colVal = (o != null) ? o.toString() : ""; fields.add(colVal.replaceAll("\'", "\'\'")); colVal = URLEncoder.encode(colVal, "UTF-8"); } MessageFormat format = new MessageFormat(source2SQL); String rSQL = format.format(fields.toArray()); retVal += importSQL(rSQL, iSQL, importDupCheck, errOut, outputLevel-1, ignoreException); } } } catch (Exception e) { e.printStackTrace(); } finally { try { if (stmt != null) { stmt.close(); } if (c != null) { c.close(); } } catch (Exception e) { e.printStackTrace(); } } return retVal; } %>