[Ils-source] r1405 - in branches/1.6: . sql webapp
scribe at crosswire.org
scribe at crosswire.org
Wed Mar 2 13:15:26 MST 2016
Author: scribe
Date: 2016-03-02 13:15:26 -0700 (Wed, 02 Mar 2016)
New Revision: 1405
Modified:
branches/1.6/
branches/1.6/sql/importusers.properties
branches/1.6/webapp/SQL.jsp
Log:
fix for SQL tool and user import scripts
Property changes on: branches/1.6
___________________________________________________________________
Modified: svn:mergeinfo
- /trunk:796-1303,1306-1307,1309-1311,1319-1320,1322-1323,1325,1328-1330,1332,1334-1335,1337,1339,1349-1355,1360-1365,1380-1383,1391,1393
+ /trunk:796-1303,1306-1307,1309-1311,1319-1320,1322-1323,1325,1328-1330,1332,1334-1335,1337,1339,1349-1355,1360-1365,1380-1383,1391,1393,1402-1404
Modified: branches/1.6/sql/importusers.properties
===================================================================
--- branches/1.6/sql/importusers.properties 2016-03-02 20:12:05 UTC (rev 1404)
+++ branches/1.6/sql/importusers.properties 2016-03-02 20:15:26 UTC (rev 1405)
@@ -58,3 +58,9 @@
ImportFile11=users.csv|INSERT INTO USERGROUP (COMPANYID, ILSGROUPID, USERPRID) select {CompanyID}, (select ilsgroupid from ilsgroup where companyid={CompanyID} and groupname='{14}' and grouptypeid=1), userprid from userprofile where companyid={CompanyID} and userprnum='{0}'
SQL11=NA
+Heading12=Assign Managers
+ImportDupCheck12=SELECT 1 FROM MANAGERGROUP, USERPROFILE, ILSGROUP where MANAGERGROUP.COMPANYID={CompanyID} and USERPROFILE.COMPANYID={CompanyID} and ILSGROUP.COMPANYID={CompanyID} and USERPROFILE.USERPRID=USERGROUP.USERPRID AND ILSGROUP.ILSGROUPID=MANAGERGROUP.ILSGROUPID AND ILSGROUP.GROUPNAME='{15}' AND USERPROFILE.USERPRNUM='{0}' AND ILSGROUP.GROUPTYPEID=2 UNION SELECT 1 WHERE '{15}' = ''
+ImportFile12=users.csv|INSERT INTO MANAGERGROUP (COMPANYID, ILSGROUPID, USERPRID) select {CompanyID}, (select ilsgroupid from ilsgroup where companyid={CompanyID} and groupname='{15}' and grouptypeid=2), userprid from userprofile where companyid={CompanyID} and userprnum='{0}'
+SQL12=NA
+
+
Modified: branches/1.6/webapp/SQL.jsp
===================================================================
--- branches/1.6/webapp/SQL.jsp 2016-03-02 20:12:05 UTC (rev 1404)
+++ branches/1.6/webapp/SQL.jsp 2016-03-02 20:15:26 UTC (rev 1405)
@@ -225,6 +225,9 @@
importDupCheck = replaceSessionTags(session, importDupCheck);
}
int retVal = 0;
+ int goodCount = 0;
+ int badCount = 0;
+ int skipCount = 0;
for (boolean more = fin.begin(); more; more = fin.next()) {
retVal++;
String colVals = "";
@@ -281,23 +284,31 @@
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) {
+ boolean execute = true;
+ String dcSQL = null;
+ if (importDupCheck != null) {
+ MessageFormat format = new MessageFormat(importDupCheck);
+ 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);
- }
+ int result = runSQL(rSQL, linkColumn, num, i + 1, out);
+ if (result == 0) ++goodCount;
+ else ++badCount;
+ }
+ else {
+ if (outputLevel >= 2) out.write("<table border=\"1\"> <tr> <th>Duplicate entry determined from SQL: </th> <td>" + dcSQL + "</td> </tr></table>");
+ ++skipCount;
+ }
}
if (error != 0) {
out.write("<h2>ERROR: Verse Out Of Bounds: "+loopEnd+"</h2><p>" + insertSQL + "</p>");
}
}
+ if (outputLevel >= 3) out.write("<table border=\"1\"> <tr> <th>Total Records</th><td>" + (goodCount + badCount + skipCount) + "</td> </tr><tr><td>Good</td><td>"+goodCount+"</td></td></tr><tr><td>Skipped</td><td>"+skipCount+"</td></td></tr><tr><td>Bad</td><td>"+badCount+"</td></td></tr></table>");
}
catch (Exception e) { e.printStackTrace(); }
@@ -314,7 +325,7 @@
}
try {
int rows = importSQL(sourceSQL, iSQL, importDupCheck, out, outputLevel, ignoreException);
- if (outputLevel >= 1) out.println("<h3>Total Records: " + rows + "</h3>");
+ if (outputLevel >= 3) out.println("<h3>Total Records: " + rows + "</h3>");
}
catch (Exception e) { e.printStackTrace(); }
@@ -337,7 +348,7 @@
}
try {
int rows = import2LSQL(sourceSQL, source2SQL, iSQL, importDupCheck, out, outputLevel, ignoreException);
- if (outputLevel >= 1) out.println("<h3>Total Records: " + rows + "</h3>");
+ if (outputLevel >= 3) out.println("<h3>Total Records: " + rows + "</h3>");
}
catch (Exception e) { e.printStackTrace(); }
@@ -575,128 +586,128 @@
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("<br>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("<table border=\"1\"> <tr> <th>Executing SQL</th> <td>" + testSQL + "</td> </tr>");
- else if (outputLevel < 6) outputTick(out);
- if (rs == null) {
- if (resultException != null && outputLevel < 6) out.write("<br/>");
- if (outputLevel >=9) out.println("<tr> <td>Result</td> <td>" + result + "</td> </tr>");
- else if (outputLevel >= 6 || resultException != null) out.println("<b>Result:</b> " + result + " <b>SQL:</b> " + testSQL + "<br/>");
- if (resultException != null) {
- if (outputLevel >= 9) out.println("<tr> <td>Exception</td> <td>");
- out.println(getAllSQLExceptions(resultException));
- if (outputLevel >= 9) out.println("</td> </tr>");
- else out.println("<br/>");
- }
- }
- if (outputLevel >= 9) out.println("</table><br />");
- if (rs != null) {
- ResultSetMetaData meta = rs.getMetaData();
- int colCount = meta.getColumnCount();
- if (outputLevel >= 7) {
- out.println("<table border=\"1\"><tr><th></th>");
- for (int i = 0; i < colCount; i++) {
- String colName = meta.getColumnName(i + 1);
- out.println("<th>" + colName + "</th>");
- }
- out.println("</tr>");
- }
- if (outputLevel >= 5) {
- while (rs.next()) {
- ++retVal;
- if (outputLevel > 5) {
- if (outputLevel >= 7) out.print("<tr> <th>" + rs.getRow() + "</th>");
- else if (outputLevel >= 5) out.print("<b>" + rs.getRow() + "</b>");
- 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("<td>");
- if (meta.getColumnName(i + 1).equalsIgnoreCase(linkColumn)) {
- out.print("<a href=\"SQL.jsp?exec=" + exec + "&stage=" + stage + "&val0=" + URLEncoder.encode(colVal) + colVals + "\">" + colVal + "</a>");
- }
- else {
- out.println(colVal);
- }
- if (outputLevel >= 7) out.print("</td>");
- }
- if (outputLevel >= 7) out.print("</tr>");
- else if (outputLevel >= 5) out.print("<br/>");
- }
- else outputTick(out);
- }
- if (outputLevel >= 7) out.println("</table>");
- }
- else {
- while (rs.next()) {
- ++retVal;
+ 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("<br>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("<table border=\"1\"> <tr> <th>Executing SQL</th> <td>" + testSQL + "</td> </tr>");
+ else if (outputLevel < 6) outputTick(out);
+ if (rs == null) {
+ if (resultException != null && outputLevel < 6) out.write("<br/>");
+ if (outputLevel >=9) out.println("<tr> <td>Result</td> <td>" + result + "</td> </tr>");
+ else if (outputLevel >= 6 || resultException != null) out.println("<b>Result:</b> " + result + " <b>SQL:</b> " + testSQL + "<br/>");
+ if (resultException != null) {
+ if (outputLevel >= 9) out.println("<tr> <td>Exception</td> <td>");
+ out.println(getAllSQLExceptions(resultException));
+ if (outputLevel >= 9) out.println("</td> </tr>");
+ else out.println("<br/>");
+ }
+ }
+ if (outputLevel >= 9) out.println("</table><br />");
+ if (rs != null) {
+ ResultSetMetaData meta = rs.getMetaData();
+ int colCount = meta.getColumnCount();
+ if (outputLevel >= 7) {
+ out.println("<table border=\"1\"><tr><th></th>");
+ for (int i = 0; i < colCount; i++) {
+ String colName = meta.getColumnName(i + 1);
+ out.println("<th>" + colName + "</th>");
+ }
+ out.println("</tr>");
+ }
+ if (outputLevel >= 5) {
+ while (rs.next()) {
+ ++retVal;
+ if (outputLevel > 5) {
+ if (outputLevel >= 7) out.print("<tr> <th>" + rs.getRow() + "</th>");
+ else if (outputLevel >= 5) out.print("<b>" + rs.getRow() + "</b>");
+ 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("<td>");
+ if (meta.getColumnName(i + 1).equalsIgnoreCase(linkColumn)) {
+ out.print("<a href=\"SQL.jsp?exec=" + exec + "&stage=" + stage + "&val0=" + URLEncoder.encode(colVal) + colVals + "\">" + colVal + "</a>");
+ }
+ else {
+ out.println(colVal);
+ }
+ if (outputLevel >= 7) out.print("</td>");
+ }
+ if (outputLevel >= 7) out.print("</tr>");
+ else if (outputLevel >= 5) out.print("<br/>");
+ }
+ else outputTick(out);
+ }
+ if (outputLevel >= 7) out.println("</table>");
+ }
+ else {
+ while (rs.next()) {
+ ++retVal;
+ }
+ }
+ }
+ } // out != null
+ else {
+ while (rs.next()) {
+ ++retVal;
+ }
+ }
}
- }
- } // out != null
- else {
- while (rs.next()) {
- ++retVal;
+ catch (Exception e) {
+ e.printStackTrace();
+ }
+ finally {
+ try {
+ if (stmt != null) {
+ stmt.close();
+ }
+ if (c != null) {
+ c.close();
+ }
}
+ catch (Exception e) {
+ e.printStackTrace();
+ }
}
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- finally {
- try {
- if (stmt != null) {
- stmt.close();
- }
- if (c != null) {
- c.close();
- }
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- }
- return retVal;
- }
+ return retVal;
+ }
private static int exportSQL(String testSQL, FileWriter out, JspWriter errOut) {
More information about the Ils-source
mailing list