0001: /**
0002: * This class connects to database using a System DSN and then you can keep sending SQLs
0003: *
0004: * It uses the JDBC driver.
0005: * @author Rahul Kumar, March 1999.
0006: * @version 1.0
0007: * RK modified on 20031229 15:52:46
0008: * Oracle's driver returns 93 for a date field which actually means TIMESTAMP.
0009: * Thus i have put another check.
0010: * Oracle driver doesnt also return a tablename with a column.
0011: * RK added on 20031229 23:57:04 - corrected paging in runSelect , now
0012: * do so for String return functiona also.
0013: */package isql;
0014:
0015: import java.sql.*;
0016: import java.util.*;
0017: import java.io.*;
0018: import javax.swing.table.DefaultTableModel;
0019: import javax.swing.event.TableModelListener;
0020: import javax.swing.event.TableModelEvent;
0021: import javax.swing.table.AbstractTableModel;
0022: import java.lang.reflect.*;
0023: import util.*;
0024: import db.*;
0025:
0026: public class SQLJDBC {
0027:
0028: public static final String P = "SQLJDBC";
0029:
0030: public Connection conn;
0031: String colSep = ", ";
0032: long lastProcessedRowCount = 0;
0033: private StringBuffer warningString = new StringBuffer();
0034: private StringBuffer exceptionString = new StringBuffer();
0035: DatabaseMetaData dma;
0036: String uid;
0037: String _databaseProductName;
0038: boolean driverNotCapable = false;
0039: SQLForm _sqlForm;
0040: String catalog = null;
0041: final String SPACES = " "; // 100 spaces
0042:
0043: // please use this constructor, the other one has not been tested
0044: // after including SQLForm
0045: public SQLJDBC(String dsn, String UID, String PWD,
0046: SQLForm tempForm, String driver) throws SQLException,
0047: ClassNotFoundException {
0048:
0049: System.out.println(dsn);
0050: System.out.println(UID);
0051: System.out.println(PWD);
0052: System.out.println(driver);
0053:
0054: // Load the Oracle JDBC driver
0055: //Class.forName ("oracle.jdbc.driver.OracleDriver");
0056: //Class.forName ("org.gjt.mm.mysql.Driver");
0057: Class.forName(driver);
0058: uid = UID;
0059: _sqlForm = tempForm;
0060: // The next block works, but is replaced with another block
0061: // to try to do this, in another thread
0062: // SINGLE THREADED VERSION START
0063: /*
0064: // Connect to the database
0065: conn =
0066: DriverManager.getConnection ("jdbc:odbc:" + dsn, UID, PWD);
0067: */
0068: // SINGLE THREADED VERSION END
0069: // THREADED VERSION START
0070: // We are sending this pointer, so that connection object can be updated.
0071: // We are sending SQLForm pointer, so that form can be updated.
0072: Thread connThread = new Thread(new ConnectorBG(dsn, UID, PWD,
0073: this , tempForm, driver));
0074: connThread.start();
0075: // Thread will update connection using a function getConn
0076: // THREADED VERSION END
0077:
0078: //setColSep(", ");
0079: setColSep("");
0080: System.err.println("Passed getConnection");
0081: }
0082:
0083: /** This method is called by the thread only
0084: */
0085: public void setConn(Connection newConn) {
0086: conn = newConn;
0087: }
0088:
0089: /** return metadata.
0090: * RK added on 20040201 11:52:17
0091: */
0092: public DatabaseMetaData getDma() {
0093: return dma;
0094: }
0095:
0096: public void setDma(DatabaseMetaData newDma) {
0097: dma = newDma;
0098: try {
0099: System.out.println("Connected to :" + dma.getURL());
0100: _databaseProductName = dma.getDatabaseProductName();
0101: System.out.print("DB Product :" + _databaseProductName);
0102: System.out.println(" DB Version :"
0103: + dma.getDatabaseProductVersion());
0104: System.out.print("Driver :" + dma.getDriverName());
0105: System.out.println(" Version :"
0106: + dma.getDriverVersion());
0107: setVendorSpecificConfigurations(_databaseProductName);
0108: //System.out.print("Catalog Term :" + dma.getCatalogTerm());
0109: //System.out.println(" Schema Term :" + dma.getSchemaTerm());
0110: } catch (SQLException ex) { //System.err.println(ex.getMessage());
0111: _sqlForm.setErrorArea("\nsetdma:" + ex.getMessage());
0112: }
0113: }
0114:
0115: public SQLJDBC() throws SQLException, ClassNotFoundException {
0116: this ("csk", "tmp", "tmp", null, null);
0117: }
0118:
0119: /** set vendor specific varaibles. Currently these are start and end
0120: * tags for printing fields of different datatypes
0121: * RK added on 20031229 14:13:04
0122: * added closing single quotes in fields.
0123: */
0124: public void setVendorSpecificConfigurations(
0125: String databaseProductName) {
0126: // other possible values are double,float,integer,boolean...
0127: System.out.println("settings for " + databaseProductName);
0128: if (databaseProductName.equalsIgnoreCase("oracle")) {
0129: _sqlForm.setAttribute("fs_date", "TO_DATE('");
0130: _sqlForm.setAttribute("fe_date", "','yyyy-MM-dd')");
0131: _sqlForm.setAttribute("fs_timestamp", "TO_DATE('");
0132: _sqlForm.setAttribute("fe_timestamp",
0133: "','yyyy-MM-dd hh:mi.ss')");
0134: _sqlForm.setAttribute("fs_time", "TO_DATE('");
0135: //_sqlForm.setAttribute("fe_time",",'yyyy-MM-dd hh:mi.ss')");
0136: //RK added on 20031229 14:12:14
0137: _sqlForm.setAttribute("fe_time", "','hh:mi.ss')");
0138: uid = uid.toUpperCase();
0139: }
0140: // the else will work for mysql
0141: else {
0142: _sqlForm.setAttribute("fs_date", "'");
0143: _sqlForm.setAttribute("fe_date", "'");
0144: _sqlForm.setAttribute("fs_timestamp", "'");
0145: _sqlForm.setAttribute("fe_timestamp", "'");
0146: _sqlForm.setAttribute("fs_time", "'");
0147: _sqlForm.setAttribute("fe_time", "'");
0148: }
0149: }
0150:
0151: /** This method is first called to run an SQL and return a string.
0152: * This decides how any rows to print.
0153: */
0154: public String runSQL(String SQLString) {
0155: int startrow = 0;
0156: int endrow = 499;
0157: if (SQLString.indexOf("/*=") > -1) {
0158: int where = SQLString.indexOf("/*=");
0159: int end = SQLString.indexOf("*/");
0160: if (end > -1 && end > where) {
0161: String s = SQLString.substring(where + 3, end);
0162: String rows[] = ArrayUtil.split(s, '-');
0163: try {
0164: startrow = Integer.parseInt(rows[0]);
0165: endrow = Integer.parseInt(rows[1]);
0166: } catch (Exception ex) {
0167: System.err.println(".runSQL:" + ex.toString());
0168: }
0169: }
0170: }
0171:
0172: return runSQL(SQLString, startrow, endrow);
0173: }
0174:
0175: /** runSQL - this is the method that executes the SQL string and puts
0176: * it into a String for caller.
0177: * If caller is appending the entire output to some textarea ( as
0178: * is the case here!) then
0179: * returning the entire result in the existing StringBuffer would involve
0180: * less
0181: * work.
0182: */
0183: public String runSQL(String SQLString, long startrow, long endrow) {
0184:
0185: if (conn == null) {
0186: System.err.println("Null connection !");
0187: // show throw our exception here
0188: //return (null);
0189: }
0190: if (SQLString == null || SQLString.equals("")) {
0191: System.err.println("Null string !");
0192: // show throw our exception here
0193: //return (null);
0194: }
0195: if (startrow > endrow)
0196: endrow = startrow + 50;
0197:
0198: // runMySQL (SQLString);
0199: int res = 0;
0200: int i = 0;
0201: long rc = 0; // row counter
0202: //Vector dVector = new Vector (100,50);
0203: int sbrowinit = 128;
0204: int sbresultinit = 1024;
0205: int colwidth = 0;
0206:
0207: try {
0208: sbresultinit = Integer.parseInt((String) _sqlForm
0209: .getAttribute("resultinitialcapacity", "1024"));
0210: } catch (Exception exc) {
0211: System.err.println("SQLJDBC 148:" + exc.toString());
0212: System.err.println("Using default value of :" + 1024);
0213: }
0214: try {
0215: sbrowinit = Integer.parseInt((String) _sqlForm
0216: .getAttribute("rowinitialcapacity", "128"));
0217: } catch (Exception exc) {
0218: System.err.println("SQLJDBC 148:" + exc.toString());
0219: System.err.println("Using default value of " + 64);
0220: }
0221: try {
0222: // colwidth = Integer.parseInt((String)_sqlForm.getAttribute("colwidth"));
0223: String tmp;
0224: if ((tmp = (String) _sqlForm.getAttribute("colwidth")) != null)
0225: colwidth = Integer.parseInt(tmp);
0226:
0227: } catch (Exception exc) {
0228: System.err.println("SQLJDBC 158:" + exc.toString());
0229: }
0230:
0231: StringBuffer result = new StringBuffer(sbresultinit);
0232: StringBuffer rowData = new StringBuffer(sbrowinit);
0233: warningString = new StringBuffer(64);
0234: exceptionString = new StringBuffer(64);
0235: boolean resultSetIsAvailable;
0236: boolean moreResultsAvailable;
0237: boolean iskipped = false;
0238: boolean bcolSep = false;
0239: colSep = (String) _sqlForm.getAttribute("colsep", "null"); // null added on 20011104
0240: if (colSep.equals("null"))
0241: colSep = null;
0242: if (colSep != null && colSep.length() != 0)
0243: bcolSep = true;
0244: // Create a Statement
0245: try {
0246: Statement stmt = conn.createStatement();
0247:
0248: // Run the query
0249:
0250: resultSetIsAvailable = stmt.execute(SQLString);
0251: ResultSet rs = null;
0252:
0253: for (moreResultsAvailable = true; moreResultsAvailable;) {
0254: checkForWarnings(conn.getWarnings());
0255: if (resultSetIsAvailable) {
0256: if ((rs = stmt.getResultSet()) != null) {
0257: checkForWarnings(conn.getWarnings());
0258: ResultSetMetaData rsmd = rs.getMetaData();
0259: int numCols = rsmd.getColumnCount();
0260: int colWidths[] = new int[numCols + 1];
0261: for (i = 1; i <= numCols; i++) {
0262: StringBuffer data = new StringBuffer(rsmd
0263: .getColumnLabel(i));
0264: //if (colSep != null)
0265: if (bcolSep) {
0266: if (i > 1)
0267: data.append(colSep);
0268: } else {
0269: // pad the string with spaces if no colsep
0270: int csize = rsmd
0271: .getColumnDisplaySize(i);
0272: //int dsize = data.trim().length();
0273:
0274: int dsize = data.length();
0275: if (colwidth == 0) {
0276: csize = (csize > dsize) ? csize
0277: : dsize;
0278: csize++;
0279: } else
0280: csize = colwidth;
0281:
0282: colWidths[i] = csize;
0283:
0284: int extraspaces = Math.min(90, csize
0285: - dsize);
0286: data.append(
0287: SPACES
0288: .substring(0,
0289: extraspaces))
0290: .append("| ");
0291: }
0292: rowData.append(data);
0293: }
0294: if ("on".equalsIgnoreCase((String) _sqlForm
0295: .getAttribute("header", "on")))
0296: result.append(rowData).append('\n');
0297:
0298: int collength = result.length();
0299: rowData = new StringBuffer(collength);
0300: StringBuffer data = new StringBuffer(30);
0301: //rowData = " ";
0302: while (rs.next()) {
0303:
0304: // RK added on 20031229 23:48:15
0305: // moved up so it will skip
0306: rc++;
0307: if (rc < startrow)
0308: continue; // check rowcount
0309:
0310: for (i = 1; i <= numCols; i++) {
0311: try {
0312: data.append(rs.getString(i));
0313: } catch (NullPointerException ex) {
0314: data.append("null ");
0315: }
0316: //if (colSep != null)
0317: //if (!colSep.equals(""))
0318: if (bcolSep) {
0319: //if (i > 1) rowData.append(colSep);
0320: if (i > 1)
0321: data.append(colSep);
0322: } else {
0323: int csize = Math.max(colWidths[i],
0324: data.length());
0325:
0326: // still wrong if value of 30 used
0327: // since static for all.
0328: int extraspaces = Math.min(90,
0329: csize - data.length());
0330: data.append(
0331: SPACES.substring(0,
0332: extraspaces))
0333: .append("| ");
0334: }
0335: rowData.append(data);
0336: data.delete(0, data.length());
0337: }
0338: //dVector.add (rowData);
0339: result.append(rowData).append('\n');
0340: //rowData = new StringBuffer(collength);
0341: rowData.delete(0, rowData.length());
0342: //rc++;
0343: //if (rc < startrow) continue; // check rowcount
0344: if (rc >= endrow) {
0345: iskipped = true;
0346: break;
0347: }
0348:
0349: } // next
0350: } // rs
0351: } //if resultset
0352: else {
0353: if ((res = stmt.getUpdateCount()) != -1) {
0354: result
0355: .append('\n' + res
0356: + " rows(s) affected.");
0357: } else {
0358: moreResultsAvailable = false;
0359: }
0360: }
0361: if (moreResultsAvailable) {
0362: resultSetIsAvailable = stmt.getMoreResults();
0363: }
0364: }
0365: if (rs != null)
0366: rs.close();
0367: stmt.close();
0368: }//try
0369: catch (SQLException ex) {
0370: //ex.printStackTrace(); Do this only when we can send to file
0371: exceptionString.append(ex.getMessage());
0372: //System.err.println (ex.getMessage());
0373: _sqlForm.setErrorArea("ERROR:runsql:" + ex.getMessage());
0374: new SQLExceptionPrint(ex);
0375: } catch (java.lang.Exception ex) {
0376: //ex.printStackTrace(); Do this only when we can send to file
0377: exceptionString.append(ex.getMessage());
0378: System.err.println("runsql:" + ex.getMessage());
0379: new SQLExceptionPrint(ex);
0380: }
0381: lastProcessedRowCount = rc;
0382: if ("on".equalsIgnoreCase((String) _sqlForm.getAttribute(
0383: "rowsprocessed", "on")))
0384: result
0385: .append(lastProcessedRowCount
0386: + " rows processed."
0387: + (iskipped ? " There could be more rows. Add /*=101-200*/ in your SQL statement,"
0388: : "") + '\n');
0389: return (result.toString());
0390:
0391: } // end of runSQL -- returns a string
0392:
0393: /** This method is called when you want to run an sql and get a
0394: * Tablemodel in return for displaying in JTable. It checks how many
0395: * rows to print.
0396: */
0397: public Object runSelect(String SQLString) throws Exception {
0398: int startrow = 0;
0399: int endrow = 500;
0400: if (SQLString.indexOf("/*=") > -1) {
0401: int where = SQLString.indexOf("/*=");
0402: int end = SQLString.indexOf("*/");
0403: if (end > -1 && end > where) {
0404: String s = SQLString.substring(where + 3, end);
0405: String rows[] = ArrayUtil.split(s, '-');
0406: try {
0407: startrow = Integer.parseInt(rows[0]);
0408: endrow = Integer.parseInt(rows[1]);
0409: } catch (Exception ex) {
0410: System.err.println("runSQL:" + ex.toString());
0411: }
0412: }
0413: // RK added on 20031229 23:01:08
0414: // remove the range, so i can later resend with a new range
0415: // programmatically - or should i do that then, to save
0416: // time.
0417: SQLString = SQLString.substring(0, where);
0418:
0419: }
0420:
0421: return runSelect(SQLString, startrow, endrow);
0422: }
0423:
0424: /** this returns a Model object to be used with JTables, and is only
0425: * for select statement. runSQL is to be used for all others
0426: * and for selects that should return a String.
0427: * Return an Integer for other operations - number of rows
0428: * affected.
0429: */
0430: public Object runSelect(String SQLString, long startrow, long endrow)
0431: throws Exception {
0432:
0433: if (conn == null) {
0434: System.err.println("Null connection !");
0435: // show throw our exception here
0436: return (null);
0437: }
0438: if (SQLString == null || SQLString.equals("")) {
0439: System.err.println("Null string !");
0440: // show throw our exception here
0441: return (null);
0442: }
0443: if (startrow > endrow)
0444: endrow = startrow + 50;
0445:
0446: if (SQLString.indexOf("${") > -1) {
0447: SQLString = processVariables(_sqlForm, SQLString);
0448: if (SQLString == null)
0449: return null;
0450: }
0451: if (SQLString.indexOf('#') > -1)
0452: SQLString = formatDates(SQLString);
0453:
0454: //System.out.println( "SQL:"+ SQLString);
0455:
0456: // runMySQL (SQLString);
0457: int res = 0;
0458: int i = 0;
0459: long rc = 0;
0460: List vTable = new ArrayList(100);
0461: List vRow = new ArrayList(16);
0462: List columns = new ArrayList(16);
0463: //StringBuffer rowData = new StringBuffer(64);
0464: warningString = new StringBuffer(64);
0465: exceptionString = new StringBuffer(64);
0466: boolean resultSetIsAvailable;
0467: boolean moreResultsAvailable;
0468: boolean iskipped = false;
0469: // Create a Statement
0470: int colWidths[] = null;
0471: int colTypes[] = null;
0472: String tableNames[] = null;
0473: long starttime = System.currentTimeMillis();
0474: try {
0475: Statement stmt = conn.createStatement();
0476:
0477: // Run the query
0478:
0479: resultSetIsAvailable = stmt.execute(SQLString);
0480: ResultSet rs = null;
0481:
0482: for (moreResultsAvailable = true; moreResultsAvailable;) {
0483: checkForWarnings(conn.getWarnings());
0484: if (resultSetIsAvailable) {
0485: if ((rs = stmt.getResultSet()) != null) {
0486: checkForWarnings(conn.getWarnings());
0487: ResultSetMetaData rsmd = rs.getMetaData();
0488: int numCols = rsmd.getColumnCount();
0489: colWidths = new int[numCols + 1];
0490: colTypes = new int[numCols + 1];
0491: tableNames = new String[numCols + 1];
0492: for (i = 1; i <= numCols; i++) {
0493: String data = rsmd.getColumnLabel(i);
0494: tableNames[i] = rsmd.getTableName(i);
0495: //if (colSep != null)
0496: // pad the string with spaces if no colsep
0497: int csize = rsmd.getColumnDisplaySize(i);
0498: int dsize = data.trim().length();
0499: csize = (csize > dsize) ? csize : dsize;
0500: csize++;
0501: colWidths[i] = csize;
0502: colTypes[i] = rsmd.getColumnType(i);
0503: // kludge for Oracle Driver
0504: if ("DATE"
0505: .equals(rsmd.getColumnTypeName(i)))
0506: colTypes[i] = java.sql.Types.DATE;
0507: for (int j = dsize; j < csize; j++) {
0508: //data += " ";
0509: data = data + " ";
0510: }
0511: columns.add(data);
0512: //rowData.append (data);
0513: }
0514: //dVector.add (rowData);
0515:
0516: //rowData = new StringBuffer(" "); // to change TODO
0517: //rowData = " ";
0518: while (rs.next()) {
0519: // RK added on 20031229 23:48:15
0520: // moved up so it will skip
0521: rc++;
0522: if (rc < startrow)
0523: continue; // check rowcount
0524:
0525: for (i = 1; i <= numCols; i++) {
0526: //StringBuffer data = new StringBuffer("null ");
0527: try {
0528: //data = new StringBuffer(rs.getString (i));
0529: //vRow.add (rs.getString (i));
0530: if (colTypes[i] == java.sql.Types.VARCHAR)
0531: vRow.add(rs.getString(i));
0532: else if (colTypes[i] == java.sql.Types.CHAR)
0533: vRow.add(rs.getString(i));
0534: else if (colTypes[i] == java.sql.Types.INTEGER)
0535: vRow.add(new Integer(rs
0536: .getInt(i)));
0537: else if (colTypes[i] == java.sql.Types.DATE) {
0538: vRow.add(rs.getDate(i));
0539: } else if (colTypes[i] == java.sql.Types.TIME)
0540: vRow.add(rs.getTime(i));
0541: else if (colTypes[i] == java.sql.Types.TIMESTAMP) {
0542: vRow.add(rs.getTimestamp(i));
0543: } else if (colTypes[i] == java.sql.Types.FLOAT)
0544: vRow.add(new Float(rs
0545: .getFloat(i)));
0546: else if (colTypes[i] == java.sql.Types.DOUBLE)
0547: vRow.add(new Double(rs
0548: .getDouble(i)));
0549: else
0550: vRow.add(rs.getObject(i));
0551: } catch (NullPointerException ex) {
0552: //data = new StringBuffer("null ");
0553: vRow.add("null ");
0554: }
0555: //if (colSep != null)
0556: //if (!colSep.equals(""))
0557:
0558: //int csize = colWidths[i];
0559: //int dsize = data.length();
0560:
0561: }
0562: vTable.add(vRow);
0563: vRow = new ArrayList(16);
0564: //rowData = new StringBuffer(" ");
0565: /*
0566: rc++;
0567: // RK added on 20031229 23:46:56
0568: // BUG this skips too late !!!
0569: if (rc < startrow) continue; // check rowcount
0570: else if (rc >= endrow){
0571: iskipped = true;
0572: break;
0573: }
0574: */
0575: if (rc >= endrow) {
0576: iskipped = true;
0577: break;
0578: }
0579:
0580: } // next
0581: } // rs
0582: } //if resultset
0583: else {
0584: if ((res = stmt.getUpdateCount()) != -1) {
0585: //dVector.add (res + " rows(s) affected.");
0586: return (new Integer(res));
0587: } else {
0588: moreResultsAvailable = false;
0589: }
0590: }
0591: if (moreResultsAvailable) {
0592: resultSetIsAvailable = stmt.getMoreResults();
0593: }
0594: }
0595: if (rs != null)
0596: rs.close();
0597: stmt.close();
0598: }//try
0599: catch (SQLException ex) {
0600: System.err.println(P + "580 ==> SQLException: ");
0601: System.out.println(" SQLString:" + SQLString);
0602: while (ex != null) {
0603: System.err.println(" Message: " + ex.getMessage());
0604: System.err.println(" SQLState: " + ex.getSQLState());
0605: System.err.println(" ErrorCode: " + ex.getErrorCode());
0606: exceptionString.append(ex.getMessage());
0607: ex = ex.getNextException();
0608: System.err.println("");
0609: }
0610:
0611: //ex.printStackTrace(); Do this only when we can send to file
0612: //System.err.println (ex.getMessage());
0613: _sqlForm.setErrorArea('\n' + "(L464) " + ex.toString()
0614: + "\n" + exceptionString);
0615: new SQLExceptionPrint(ex);
0616: throw ex;
0617: } catch (java.lang.Exception ex) {
0618: //ex.printStackTrace(); Do this only when we can send to file
0619: exceptionString.append(ex.getMessage());
0620: System.err.println(ex.getMessage());
0621: new SQLExceptionPrint(ex);
0622: throw ex;
0623: }
0624: long endtime = System.currentTimeMillis();
0625: lastProcessedRowCount = rc;
0626: //dVector.add (lastProcessedRowCount + " rows processed." + (iskipped? " There could be more rows. Add /*=101-200*/ in your SQL statement,":""));
0627: //return (dVector);
0628: //DefaultTableModel dModel = new DefaultTableModel(vTable, vRow);
0629: _sqlForm
0630: .setErrorArea("\n"
0631: + lastProcessedRowCount
0632: + " rows processed in "
0633: + (endtime - starttime)
0634: + " millis."
0635: + (iskipped ? " There could be more rows. Add /*=1000-1100*/ in your SQL statement or use next/prev keys,"
0636: : ""));
0637:
0638: TableMap dModel = new TableMap(columns, vTable);
0639: dModel.setWidths(colWidths);
0640: dModel.setTypes(colTypes);
0641: dModel.setTableNames(tableNames);
0642: dModel.setSQL(SQLString);
0643: dModel.setEndRow((int) lastProcessedRowCount);
0644: dModel.setRowCount((int) (lastProcessedRowCount - startrow));
0645: dModel.setStartRow((int) startrow);
0646: dModel.moreRows(iskipped);
0647: // RK added on 20040131 15:27:53
0648: dModel.setExecutionTime(endtime - starttime);
0649: return (dModel);
0650: } // end of runSelect
0651:
0652: /** format dates given in #x# and ##x## pattern.
0653: * single asterisks are replaced with a single date, double are
0654: * replaced with a range.
0655: * ranges are used when you want a full month or year, from start to
0656: * end.
0657: */
0658: public String formatDates(String SQLString) {
0659:
0660: int sanity_ctr = 0; // just to avoid some RE bug
0661: String[] match = PerlWrapper.perlMatch("##(\\S+)##", SQLString);
0662: while (match != null && sanity_ctr++ < 10) {
0663: String[] s = DateResolver.parseToDateRange(match[0]);
0664: String newstring = DateResolver.getDBString(s[0], _sqlForm
0665: .getParams())
0666: + " and "
0667: + DateResolver.getDBString(s[1], _sqlForm
0668: .getParams());
0669: SQLString = Util.replace("##" + match[0] + "##", newstring,
0670: SQLString);
0671: // remove since the match itself contains a plus sin which
0672: // bombs
0673: //SQLString = PerlWrapper.perlSubstitute( "s/##"+match[0]+"##/"+ newstring +"/g", SQLString);
0674:
0675: match = PerlWrapper.perlMatch("##(\\S+)##", SQLString);
0676: }
0677:
0678: match = PerlWrapper.perlMatch("#(\\S+)#", SQLString);
0679: sanity_ctr = 0;
0680: while (match != null && sanity_ctr++ < 10) {
0681: System.out.println("2 match :" + match[0] + "]");
0682: String s = DateResolver.parseToDateString(match[0]);
0683: String newstring = DateResolver.getDBString(s, _sqlForm
0684: .getParams());
0685: SQLString = Util.replace("#" + match[0] + "#", newstring,
0686: SQLString);
0687: //SQLString = PerlWrapper.perlSubstitute( "s/#"+match[0]+"#/"+ newstring +"/g", SQLString);
0688:
0689: match = PerlWrapper.perlMatch("#(\\S+)#", SQLString);
0690: }
0691: return SQLString;
0692: }
0693:
0694: /** prompts the user for variables in sql in the form of ${VAR}.
0695: * RK added on 20040202 12:33:58
0696: */
0697: public String processVariables(SQLForm _form, String SQLString) {
0698: int sanity_ctr = 0; // just to avoid some RE bug
0699: String[] match = PerlWrapper
0700: .perlMatch("\\${(\\S+)}", SQLString);
0701: while (match != null && sanity_ctr++ < 10) {
0702: List l = getRememberedValues(_form, match[0]);
0703: String s = null;
0704: if (l != null) {
0705: String lvals[] = ArrayUtil.toStringArray(l);
0706: // NOOO this forces me to select from list.
0707: //s = (String) _sqlForm.getInput("Enter a value for:"+ match[0], "Enter", lvals, lvals[0]);
0708: s = _form.getInputCombo(
0709: "Enter a value for:" + match[0], "Enter",
0710: lvals, lvals[0]);
0711: } else
0712:
0713: s = _sqlForm.getInput("Enter a value for:" + match[0]);
0714: // use cancelled
0715: if (s == null)
0716: return null;// so that he can cancel
0717: setRememberedValue(_form, match[0], s);
0718:
0719: SQLString = Util.replace("${" + match[0] + "}", s,
0720: SQLString);
0721: // remove since the match itself contains a plus sin which
0722: // bombs
0723: //SQLString = PerlWrapper.perlSubstitute( "s/##"+match[0]+"##/"+ newstring +"/g", SQLString);
0724:
0725: match = PerlWrapper.perlMatch("\\${(\\S+)}", SQLString);
0726: }
0727: return SQLString;
0728:
0729: }
0730:
0731: /** retrieve remembered values for a column.
0732: * THis should go into a class now.
0733: */
0734: public static List getRememberedValues(SQLForm _form, String column) {
0735: Map map = _form.htRemembered;
0736: if (map == null)
0737: return null;
0738: return (List) map.get(column);
0739: }
0740:
0741: /** Add a value to remembered values.
0742: * This should go into a class now.
0743: */
0744: public static void setRememberedValue(SQLForm _form, String column,
0745: String value) {
0746: Map map = _form.htRemembered;
0747: if (map == null) {
0748: _form.htRemembered = new HashMap();
0749: map = _form.htRemembered;
0750: }
0751: List l = (List) map.get(column);
0752: if (l == null)
0753: l = new ArrayList();
0754: l.add(value);
0755: map.put(column, l);
0756: }
0757:
0758: /**
0759: * @deprecated
0760: */
0761: private void runMySQL(String mySQL) {
0762:
0763: int numcols;
0764: // Run the query
0765: System.err.println("MySQL ************");
0766:
0767: try {
0768: Statement stmt = conn.createStatement();
0769: stmt.execute(mySQL);
0770: while (true) {
0771: int rowCount = stmt.getUpdateCount();
0772: System.err.println("MySQL 2");
0773: if (rowCount > 0) { // this is an update count
0774: System.out.println("Rows changed = " + rowCount);
0775: stmt.getMoreResults();
0776: continue;
0777: }
0778: if (rowCount == 0) { // DDL command or 0 updates
0779: System.out
0780: .println(" No rows changed or statement was DDL command");
0781: stmt.getMoreResults();
0782: continue;
0783: }
0784:
0785: // if we have gotten this far, we have either a result set
0786: // or no more results
0787: int i = 0;
0788: ResultSet rs = stmt.getResultSet();
0789: System.err.println("MySQL 3");
0790: if (rs != null) {
0791: // use metadata to get info about result set columns
0792: ResultSetMetaData rsmd = rs.getMetaData();
0793: int numCols = rsmd.getColumnCount();
0794: for (i = 1; i <= numCols; i++) {
0795: if (i > 1)
0796: System.out.print(",");
0797: System.out.print(rsmd.getColumnLabel(i));
0798: }
0799: System.out.println("");
0800: while (rs.next()) {
0801: // process results
0802:
0803: for (i = 1; i <= numCols; i++) {
0804: if (i > 1)
0805: System.out.print(",");
0806: System.out.print(rs.getString(i));
0807: }
0808: System.out.println("");
0809: }
0810: stmt.getMoreResults();
0811: continue;
0812: }
0813: break; // there are no more results
0814: } //while
0815:
0816: } // try
0817: catch (SQLException ex) {
0818: //ex.printStackTrace(); Do this only when we can send to file
0819: //exceptionString.append (ex.getMessage());
0820: System.err.println("mysq:" + ex.getMessage());
0821: //new SQLExceptionPrint(ex);
0822: } catch (java.lang.Exception ex) {
0823: //ex.printStackTrace(); Do this only when we can send to file
0824: //exceptionString.append (ex.getMessage());
0825: System.err.println("mysql:" + ex.getMessage());
0826: //new SQLExceptionPrint(ex);
0827: }
0828: System.err.println("MySQL end ******");
0829:
0830: } // runMySQL
0831:
0832: /** in case the user want to change the column separator
0833: */
0834: public void setColSep(String sColSep) {
0835: colSep = sColSep;
0836: }
0837:
0838: public long getLastRowCount() {
0839: return (lastProcessedRowCount);
0840: }
0841:
0842: private void checkForWarnings(SQLWarning warn) throws SQLException {
0843: while (warn != null) {
0844: //tp.appendErrorArea (warn);
0845: warningString.append(warn.toString() + "\n");
0846: System.err.println("Warning:");
0847: System.err.println(warn);
0848: warn = warn.getNextWarning();
0849: }
0850: }
0851:
0852: public String getWarningString() {
0853: return (warningString.toString());
0854: }
0855:
0856: /** returns exceptions if any.
0857: * RK added on 20040104 18:54:36
0858: * returns a null if no exception.
0859: */
0860: public String getErrorString() {
0861: return (exceptionString.toString());
0862: }
0863:
0864: public List SQLGetTables() {
0865: try {
0866: //String sql = "select * from user_tables ";
0867: //ResultSet rs = dma.getTables ("", uid , "%", null);
0868: ResultSet rs = dma.getTables(null, uid, "%", null);
0869: //ResultSet rs = dma.getTables (null, null , "%", null);
0870: ResultSetConverter rsc = new ResultSetConverter(rs, 1);
0871: return rsc.getDataVector();
0872: } catch (SQLException ex) {
0873: driverNotCapable = true;
0874: _sqlForm.setErrorArea("\n595" + ex.getMessage());
0875: System.err.println("getTables: " + ex.getMessage());
0876: }
0877: return (null);
0878: }
0879:
0880: public List SQLGetColumns(String tableName) {
0881: try {
0882: //String sql = "select * from user_tab_columns where table_name ='"+
0883: // tableName.trim() +"'";
0884: //ResultSet rs = dma.getColumns ("%", uid, tableName , "%");
0885: ResultSet rs = dma.getColumns(catalog, uid, tableName, "%");
0886: //ResultSet rs = dma.getColumns (null, null, tableName , "%");
0887: ResultSetConverter rsc = new ResultSetConverter(rs, 1);
0888: rs.close();
0889: return rsc.getDataVector();
0890: } catch (SQLException ex) {
0891: //ex.printStackTrace(); Do this only when we can send to file
0892: exceptionString.append(ex.getMessage());
0893: System.err.println(ex.getMessage());
0894: _sqlForm.setErrorArea("\n613" + ex.getMessage());
0895: new SQLExceptionPrint(ex);
0896: }
0897: return (null);
0898: }
0899:
0900: //public String[] getColumnNames(String tableName) throws SQLException
0901: public List getColumnNames(String tableName) throws SQLException {
0902: ResultSet rs = dma.getColumns(catalog, uid, tableName, "%");
0903: //ResultSet rs = dma.getColumns (null, null, tableName , "%");
0904: //String s[] = extractColumn(rs, 4);
0905: List s = extractColumn(rs, 4);
0906: rs.close();
0907: return s;
0908: }
0909:
0910: //public String[] getTableNames() throws SQLException
0911: /** returns a list of ALL tables
0912: */
0913: public List getTableNames() throws SQLException {
0914: return getTableNames("%");
0915: }
0916:
0917: /** returns a list of table for a given pattern, used while cacheing
0918: * column names for tab completion.
0919: */
0920: public List getTableNames(String patt) throws SQLException {
0921: System.out.println("catalog in getTables is:" + catalog + ".");
0922: System.out.println("uid in getTables is:" + uid + ".");
0923: //ResultSet rs = dma.getTables (catalog , uid , patt, null);
0924: ResultSet rs = dma.getTables("", uid, patt, null);
0925: //ResultSet rs = dma.getTables (null , null , patt, null);
0926: //String s[] = extractColumn(rs, 3);
0927: List s = extractColumn(rs, 3);
0928: rs.close();
0929: return s;
0930: }
0931:
0932: public List SQLGetImportedKeys(String tableName) {
0933: try {
0934: ResultSet rs = dma.getImportedKeys("%", uid, tableName);
0935: ResultSetConverter rsc = new ResultSetConverter(rs, 1);
0936: rs.close();
0937: return rsc.getDataVector();
0938: } catch (SQLException ex) {
0939: //ex.printStackTrace(); Do this only when we can send to file
0940: exceptionString.append(ex.getMessage());
0941: System.err.println(ex.getMessage());
0942: new SQLExceptionPrint(ex);
0943: }
0944: return (null);
0945: }
0946:
0947: public List SQLGetExportedKeys(String tableName) {
0948: try {
0949: ResultSet rs = dma.getExportedKeys("%", uid, tableName);
0950: ResultSetConverter rsc = new ResultSetConverter(rs, 1);
0951: return rsc.getDataVector();
0952: } catch (SQLException ex) {
0953: //ex.printStackTrace(); Do this only when we can send to file
0954: exceptionString.append(ex.getMessage());
0955: System.err.println(ex.getMessage());
0956: new SQLExceptionPrint(ex);
0957: }
0958: return (null);
0959: }
0960:
0961: public List SQLGetPrimaryKeys(String tableName) {
0962: try {
0963: ResultSet rs = dma.getPrimaryKeys(null, "", tableName);
0964: ResultSetConverter rsc = new ResultSetConverter(rs, 1);
0965: return rsc.getDataVector();
0966: } catch (SQLException ex) {
0967: //ex.printStackTrace(); Do this only when we can send to file
0968: exceptionString.append(ex.getMessage());
0969: System.err.println(ex.getMessage());
0970: new SQLExceptionPrint(ex);
0971: }
0972: return (null);
0973: }
0974:
0975: public List SQLGetIndexInfo(String tableName) {
0976: List v = new ArrayList();
0977: try {
0978: //String sql = "select a.index_name, column_name, column_position " +
0979: // " from user_indexes a, user_ind_columns b " +
0980: // " where a.index_name= b.index_name and a.table_name = '" + tableName + "'";
0981: //ResultSet rs = dma.getIndexInfo ("%", "%", tableName, false, true );
0982: ResultSet rs = dma.getIndexInfo(null, "", tableName, false,
0983: true);
0984: ResultSetConverter rsc = new ResultSetConverter(rs, 1);
0985: v = rsc.getDataVector();
0986: } catch (SQLException ex) {
0987: //ex.printStackTrace(); Do this only when we can send to file
0988: exceptionString.append(ex.getMessage());
0989: System.err.println(ex.getMessage());
0990: new SQLExceptionPrint(ex);
0991: }
0992: return (v);
0993: }
0994:
0995: public List SQLGetCatalogs() {
0996: List v = new ArrayList();
0997: try {
0998: ResultSet rs = dma.getCatalogs();
0999: ResultSetConverter rsc = new ResultSetConverter(rs, 1);
1000: v = rsc.getDataVector();
1001: } catch (SQLException ex) {
1002: //ex.printStackTrace(); Do this only when we can send to file
1003: exceptionString.append(ex.getMessage());
1004: System.err.println(ex.getMessage());
1005: new SQLExceptionPrint(ex);
1006: }
1007: return (v);
1008: }
1009:
1010: public List SQLGetSchemas() {
1011: List v = new ArrayList();
1012: ResultSetConverter rsc;
1013: try {
1014: ResultSet rs = dma.getSchemas();
1015: rsc = new ResultSetConverter(rs, 1);
1016: v = rsc.getDataVector();
1017: } catch (SQLException ex) {
1018: //ex.printStackTrace(); Do this only when we can send to file
1019: exceptionString.append(ex.getMessage());
1020: System.err.println(ex.getMessage());
1021: new SQLExceptionPrint(ex);
1022: }
1023: return (v);
1024: }
1025:
1026: public String refreshDatabase() {
1027:
1028: String headers = (String) _sqlForm
1029: .getAttribute("header", "off");
1030: String rowsproc = (String) _sqlForm.getAttribute(
1031: "rowsprocessed", "off");
1032: _sqlForm.setAttribute("header", "off");
1033: _sqlForm.setAttribute("rowsprocessed", "off");
1034: _sqlForm.setAttribute("colsep", ",");
1035: catalog = runSQL("select database()", 1, 1).replace(',', ' ')
1036: .trim();
1037: System.out.println("catalog:[" + catalog + ']');
1038: _sqlForm.setAttribute("header", headers);
1039: _sqlForm.setAttribute("rowsprocessed", rowsproc);
1040: return catalog;
1041: }
1042:
1043: public void closeConnection() {
1044: try {
1045: if (conn != null)
1046: conn.close();
1047: } catch (Exception ex) {
1048: System.err.println("Closing: " + ex.toString());
1049: }
1050: }
1051:
1052: //private String[] extractColumn(ResultSet rs, int coloff)
1053: private List extractColumn(ResultSet rs, int coloff) {
1054: List v = new ArrayList(50);
1055: try {
1056: ResultSetMetaData rsmd = rs.getMetaData();
1057:
1058: // Get the number of columns in the result set
1059: int numCols = rsmd.getColumnCount();
1060:
1061: // do some idiot proofing
1062: if (coloff > numCols) {
1063: System.err.println("Cols: " + numCols + " " + coloff);
1064: return null;
1065: }
1066:
1067: // Display data, fetching until end of the result set
1068:
1069: //rs.first(); // NOT IMPLEMENTED BY MYSQL
1070: //rs.beforeFirst();
1071: //rs.absolute(1);
1072: boolean more = rs.next();
1073: while (more) {
1074:
1075: // just get the relevant column
1076: v.add(rs.getString(coloff));
1077:
1078: // Fetch the next result set row
1079: more = rs.next();
1080: }
1081: } catch (SQLException ex) {
1082: System.err.println("EXCOL:" + ex.toString());
1083: }
1084:
1085: /*
1086: String arrs[] = new String[v.size()];
1087: v.copyInto (arrs);
1088: return arrs;
1089: */
1090: return v;
1091: }
1092:
1093: public int[] runBatch(String[] batch) throws SQLException {
1094: boolean sbu = false;
1095: try {
1096: sbu = dma.supportsBatchUpdates();
1097: } catch (Throwable e) {
1098: System.err
1099: .println("OUCH!!!:(probably not JDBC 2.0 compliant!!) "
1100: + e.toString());
1101: }
1102:
1103: if (sbu) {
1104: Statement st = conn.createStatement();
1105: for (int i = 0; i < batch.length; i++) {
1106: if (batch[i].trim().length() > 6)
1107: st.addBatch(batch[i]);
1108: }
1109: int[] ia = st.executeBatch();
1110: st.clearBatch();
1111: st.close();
1112: return ia;
1113: } else {
1114: System.err
1115: .println("This database/driver doesnt support batch updates. Get yourself a *real* database.\n Issuing statements individually.");
1116: int[] ia = new int[batch.length];
1117: for (int i = 0; i < batch.length; i++) {
1118: if (batch[i].trim().length() > 6) {
1119: System.out.println("batch " + i + ":" + batch[i]);
1120: try {
1121: ia[i] = ((Integer) runSelect(batch[i]))
1122: .intValue();
1123: } catch (Exception exc) {
1124: System.err.println(" L928 EXC:"
1125: + exc.toString());
1126: exc.printStackTrace();
1127: }
1128: }
1129: }
1130: return ia;
1131: }
1132: }
1133:
1134: /** invokes a method in DatabaseMetaDataClass
1135: */
1136: public Object reflectInvoke(String str) {
1137: System.out.println("reflectinvoke recvd:" + str);
1138: try {
1139: ReflectDataBase rdb = new ReflectDataBase(dma, str);
1140: Object o = rdb.getResult();
1141: if (o instanceof ResultSet) {
1142: ResultSetConverter rsc = new ResultSetConverter(
1143: (ResultSet) o, 2);
1144: TableMap tm = new TableMap(rsc.getColumnVector(), rsc
1145: .getDataVector());
1146: return tm;
1147: } else
1148: return o;
1149: } catch (Throwable exc) {
1150: System.err.println("ODB808:" + exc.toString());
1151: exc.printStackTrace();
1152: return exc.toString();
1153: }
1154: }
1155:
1156: /** creates an insert script based on data in table, and writes into
1157: * given file (appending). Also takes row to start with, and row to
1158: * end with
1159: */
1160: public int createInsertScript(String tname, String fname,
1161: long startrow, long endrow) {
1162:
1163: if (conn == null) {
1164: System.err.println("Null connection !");
1165: return 0;
1166: }
1167: if (tname == null || tname.equals("")) {
1168: System.err.println("Null string passed!");
1169: return 0;
1170: }
1171: if (startrow > endrow)
1172: endrow = startrow + 100;
1173:
1174: // runMySQL (SQLString);
1175: int res = 0;
1176: int i = 0;
1177: int rc = 0;
1178: int sbrowinit = 128;
1179: int sbresultinit = 1024;
1180: int writeafterbytes = 4096;
1181: int colwidth = 0;
1182:
1183: // dates have to be inserted with some vendor specific function
1184: // for some databases. I am picking the one to use which user
1185: // can set. This depends on target not source database.
1186: String datefuncsta = (String) _sqlForm.getAttribute(
1187: "datefuncsta", "");
1188: String datefuncend = (String) _sqlForm.getAttribute(
1189: "datefuncend", "");
1190: String timefuncsta = (String) _sqlForm.getAttribute(
1191: "timefuncsta", "");
1192: String timefuncend = (String) _sqlForm.getAttribute(
1193: "timefuncend", "");
1194: String timestampfuncsta = (String) _sqlForm.getAttribute(
1195: "timestampfuncsta", "");
1196: String timestampfuncend = (String) _sqlForm.getAttribute(
1197: "timestampfuncend", "");
1198:
1199: StringBuffer fullData = new StringBuffer(10240);
1200: warningString = new StringBuffer(64);
1201: exceptionString = new StringBuffer(64);
1202: boolean resultSetIsAvailable;
1203: boolean moreResultsAvailable;
1204: boolean iskipped = false;
1205: boolean bcolSep = false;
1206: // Create a Statement
1207: try {
1208: BufferedWriter bw = new BufferedWriter(new FileWriter(
1209: fname, true));
1210: String tmp = ("\n/* written by SQLMinus on "
1211: + new java.util.Date() + " */\n");
1212: bw.write(tmp, 0, tmp.length());
1213: tmp = null;
1214: Statement stmt = conn.createStatement();
1215:
1216: // Run the query
1217:
1218: resultSetIsAvailable = stmt.execute("select * from "
1219: + tname);
1220: ResultSet rs = null;
1221:
1222: if ((rs = stmt.getResultSet()) != null) {
1223: StringBuffer stub = new StringBuffer("insert into "
1224: + tname + "(");
1225: ResultSetMetaData rsmd = rs.getMetaData();
1226: int numCols = rsmd.getColumnCount();
1227: int colTypes[] = new int[numCols + 1]; // starts with one not 0.
1228: for (i = 1; i <= numCols; i++) {
1229: if (i > 1)
1230: stub.append(',');
1231: stub.append(rsmd.getColumnLabel(i));
1232: colTypes[i] = rsmd.getColumnType(i);
1233: }
1234: stub.append(") values (");
1235:
1236: StringBuffer data = new StringBuffer(256);
1237: //rowData = " ";
1238: while (rs.next()) {
1239: for (i = 1; i <= numCols; i++) {
1240: try {
1241: if (i > 1)
1242: data.append(',');
1243: if (colTypes[i] == java.sql.Types.DATE)
1244: data.append(datefuncsta).append('\'')
1245: .append(rs.getString(i))
1246: .append('\'').append(
1247: datefuncend);
1248: else if (colTypes[i] == java.sql.Types.TIME)
1249: data.append(timefuncsta).append('\'')
1250: .append(rs.getString(i))
1251: .append('\'').append(
1252: timefuncend);
1253: else if (colTypes[i] == java.sql.Types.TIMESTAMP)
1254: data.append(timestampfuncsta).append(
1255: '\'').append(rs.getString(i))
1256: .append('\'').append(
1257: timestampfuncend);
1258: else
1259: data.append('\'').append(
1260: rs.getString(i)).append('\'');
1261: } catch (NullPointerException ex) {
1262: data.append("null ");
1263: }
1264: }
1265: data.append(')');
1266: fullData.append(stub).append(data).append(';')
1267: .append('\n');
1268: // if buffer has crossed n bytes then dump
1269: if (fullData.length() > writeafterbytes) {
1270: bw.write(fullData.toString(), 0, fullData
1271: .length());
1272: bw.flush();
1273: fullData.delete(0, fullData.length());
1274: }
1275: data.delete(0, data.length());
1276: rc++;
1277: if (rc < startrow)
1278: continue; // check rowcount
1279: else if (rc >= endrow) {
1280: iskipped = true;
1281: break;
1282: }
1283: }
1284: }
1285: // dump whatevers left.
1286: fullData.append("\ncommit;\n");
1287: bw.write(fullData.toString(), 0, fullData.length());
1288:
1289: bw.close();
1290: }//try
1291: catch (SQLException ex) {
1292: //ex.printStackTrace(); Do this only when we can send to file
1293: exceptionString.append(ex.getMessage());
1294: System.err.println(ex.getMessage());
1295: new SQLExceptionPrint(ex);
1296: } catch (java.lang.Exception ex) {
1297: //ex.printStackTrace(); Do this only when we can send to file
1298: exceptionString.append(ex.getMessage());
1299: System.err.println(ex.getMessage());
1300: new SQLExceptionPrint(ex);
1301: }
1302: return (rc);
1303: }
1304:
1305: /** generate a create script for a table.
1306: * This doesnt create the primary key TODO using getImportedKeys.
1307: * if using mysql you can use "show create table mytable"
1308: */
1309: public void createCreateScript(String tname, String fname) {
1310: try {
1311: BufferedWriter bw = new BufferedWriter(new FileWriter(
1312: fname, true));
1313: String tmp = ("\n/* written by SQLMinus on "
1314: + new java.util.Date() + " */\n");
1315: bw.write(tmp, 0, tmp.length());
1316: tmp = null;
1317: ResultSet rs = dma.getColumns(catalog, uid, tname, "%");
1318: StringBuffer out = new StringBuffer(256);
1319: out.append(" CREATE TABLE ").append(tname).append("(\n");
1320: int ctr = 0;
1321: while (rs.next()) {
1322: if (ctr++ > 0)
1323: out.append(',');
1324: String colname = rs.getString(4);
1325: int itype = rs.getInt(5);
1326: String typename = rs.getString(6);
1327: int inull = rs.getInt(11);
1328: int isize = rs.getInt(7);
1329:
1330: out.append(colname).append(' ').append(typename);
1331: switch (itype) {
1332:
1333: case java.sql.Types.DOUBLE:
1334: case java.sql.Types.FLOAT:
1335: case java.sql.Types.REAL:
1336: case java.sql.Types.NUMERIC:
1337: int idec = rs.getInt(9);
1338: out.append('(').append(isize).append(',').append(
1339: idec).append(')');
1340: break;
1341: case java.sql.Types.INTEGER:
1342: case java.sql.Types.TINYINT:
1343: case java.sql.Types.BIGINT:
1344: case java.sql.Types.SMALLINT:
1345: out.append('(').append(isize).append(')');
1346: break;
1347:
1348: case java.sql.Types.CHAR:
1349: case java.sql.Types.VARCHAR:
1350: out.append('(').append(isize).append(')');
1351: break;
1352:
1353: case java.sql.Types.TIME:
1354: case java.sql.Types.DATE:
1355: case java.sql.Types.TIMESTAMP:
1356: break;
1357: } //switch datatype
1358: switch (inull) {
1359: case 0:
1360: out.append(" NOT NULL ");
1361: break;
1362: case 1:
1363: out.append(" NULL ");
1364: break;
1365: case 2:
1366: out.append(" NULL ");
1367: break; // dont know case
1368: } // switch
1369:
1370: out.append('\n');
1371: } //rs.next
1372:
1373: out.append(')').append('\n');
1374: bw.write(out.toString(), 0, out.length());
1375: bw.close();
1376: } catch (SQLException ex) {
1377: //ex.printStackTrace(); Do this only when we can send to file
1378: exceptionString.append(ex.getMessage());
1379: //System.err.println (ex.getMessage());
1380: new SQLExceptionPrint(ex);
1381: } catch (java.lang.Exception ex) {
1382: //ex.printStackTrace(); Do this only when we can send to file
1383: exceptionString.append(ex.getMessage());
1384: System.err.println(ex.getMessage());
1385: new SQLExceptionPrint(ex);
1386: }
1387: } // createCreateScript
1388:
1389: /** returns columninfo for a given table in a Vector array which can
1390: * then be displayed as output text or converted to TableMap and
1391: * put in table depending on how you want to display.
1392: */
1393: public List[] getColumnInfo(String tableName) {
1394: try {
1395: ResultSet rs = dma.getColumns(catalog, uid, tableName, "%");
1396: ResultSetConverter rsc = new ResultSetConverter(rs, 2);
1397: rs.close();
1398: return new List[] { rsc.getColumnVector(),
1399: rsc.getDataVector() };
1400: } catch (SQLException ex) {
1401: System.err.println(ex.toString());
1402: }
1403: return null;
1404: }
1405:
1406: public List[] getIndexInfo(String tableName) {
1407: try {
1408: // this line doesnt work on Oracle - gives invalid table
1409: ResultSet rs = dma.getIndexInfo(null, "", tableName, false,
1410: true);
1411: ResultSetConverter rsc = new ResultSetConverter(rs, 2);
1412: rs.close();
1413: return new List[] { rsc.getColumnVector(),
1414: rsc.getDataVector() };
1415: } catch (SQLException ex) {
1416: System.err.println(ex.toString());
1417: _sqlForm
1418: .Run("select a.table_name,b.column_name,a.index_type from user_indexes a, user_ind_columns b where a.table_name = '"
1419: + tableName
1420: + "' and a.index_name = b.index_name");
1421: }
1422: return null;
1423: }
1424:
1425: public List[] getPrimaryKeyInfo(String tableName) {
1426: try {
1427: ResultSet rs = dma.getPrimaryKeys(null, "", tableName);
1428: ResultSetConverter rsc = new ResultSetConverter(rs, 2);
1429: rs.close();
1430: return new List[] { rsc.getColumnVector(),
1431: rsc.getDataVector() };
1432: } catch (SQLException ex) {
1433: System.err.println(ex.toString());
1434: }
1435: return null;
1436: }
1437:
1438: // XXXXX
1439: public PrimaryKeyInfo getPrimaryKeyInfoX(String tableName) {
1440: PrimaryKeyInfo pkinfo = new PrimaryKeyInfo(conn, tableName);
1441: return pkinfo;
1442: }
1443:
1444: public static void main(String args[]) throws SQLException,
1445: ClassNotFoundException {
1446: //SQLJDBC myodbc = new SQLJDBC("csk", "tmp", "tmp" , null,null);
1447: //System.out.println( myodbc.runSQL("select * from Project"));
1448: }
1449:
1450: /**
1451: * Format a datatype as VARCHAR(2) or NUMBER(2,1) or INTEGER or
1452: * DATETIME
1453: */
1454: public static String formatDatatype(int java_sql_Types,
1455: String typename, int isize, int idec) {
1456: StringBuffer out = new StringBuffer(typename);
1457: switch (java_sql_Types) {
1458:
1459: case java.sql.Types.DOUBLE:
1460: case java.sql.Types.FLOAT:
1461: case java.sql.Types.REAL:
1462: case java.sql.Types.NUMERIC:
1463: out.append('(').append(isize).append(',').append(idec)
1464: .append(')');
1465: break;
1466: case java.sql.Types.INTEGER:
1467: case java.sql.Types.TINYINT:
1468: case java.sql.Types.BIGINT:
1469: case java.sql.Types.SMALLINT:
1470: out.append('(').append(isize).append(')');
1471: break;
1472:
1473: case java.sql.Types.CHAR:
1474: case java.sql.Types.VARCHAR:
1475: out.append('(').append(isize).append(')');
1476: break;
1477:
1478: case java.sql.Types.TIME:
1479: case java.sql.Types.DATE:
1480: case java.sql.Types.TIMESTAMP:
1481: break;
1482: } //switch datatype
1483: return out.toString();
1484:
1485: }
1486:
1487: /** given a table and column name get the user friendly datatype
1488: * name for it.
1489: * this was used in substituting $dt in sqlpattern for NOT NULL
1490: * cases.
1491: */
1492: public String getFormattedDatatypeFor(String table, String column) {
1493: String ret = null;
1494: try {
1495: ResultSet rs = dma.getColumns(catalog, uid, table, column);
1496: //ResultSet rs = dma.getColumns (catalog, uid, table , "%");
1497: if (rs.next()) {
1498: //String colname = rs.getString(4);
1499:
1500: int itype = rs.getInt(5);
1501: String typename = rs.getString(6);
1502: //int inull = rs.getInt(11);
1503: int isize = rs.getInt(7);
1504: int idec = rs.getInt(9);
1505: ret = formatDatatype(itype, typename, isize, idec);
1506: }
1507: rs.close();
1508: } catch (Exception exc) {
1509: System.err.println(" SQLJDBC L1245 EXC:" + exc.toString());
1510: exc.printStackTrace();
1511: }
1512: return ret;
1513: }
1514: } // class JDBC
1515:
1516: /** This class implements a database connection in the background
1517: * so that the application / user is free to edit while the conn takes place
1518: * Would be nice if the driver load could also be in the bg
1519: */
1520: class ConnectorBG implements Runnable {
1521:
1522: String URL, UID, PWD;
1523: SQLJDBC tempJDBC;
1524: SQLForm SQLFORM;
1525: String driver;
1526:
1527: public ConnectorBG(String mURL, String mUID, String mPWD,
1528: SQLJDBC mJDBC, SQLForm mSQLForm, String driver) {
1529: URL = mURL;
1530: UID = mUID;
1531: PWD = mPWD;
1532: tempJDBC = mJDBC;
1533: SQLFORM = mSQLForm; // this could be avoided, by calling a method in SQLJDBC
1534: this .driver = driver;
1535: }
1536:
1537: /** We are getting this connection in the background, so the
1538: * user does not need to wait, and also updating the
1539: * calling program.
1540: */
1541: public void run() {
1542: try {
1543:
1544: //DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
1545: //Class.forName ("oracle.jdbc.driver.OracleDriver");
1546: tempJDBC.closeConnection(); // close connection if existing. we can
1547: // create fresh connections with abandon
1548: Class.forName(driver);
1549: Connection conn = DriverManager
1550: .getConnection(URL, UID, PWD);
1551: //DriverManager.getConnection ("jdbc:mysql:" + URL, UID, PWD);
1552: if (conn == null)
1553: System.out.println(" NULL CONNECTION:" + URL + ":"
1554: + UID + ":" + PWD);
1555: else
1556: System.out.println(" Connection seems to be okay.");
1557: tempJDBC.setConn(conn);
1558: DatabaseMetaData dma = conn.getMetaData();
1559: tempJDBC.setDma(dma);
1560: if (SQLFORM != null)
1561: SQLFORM.doAfterConnection();
1562: System.out.println(" after doAfterConnection .");
1563: SQLFORM.setErrorArea('\n' + "Successful Connection to "
1564: + URL);
1565:
1566: } catch (SQLException ex) {
1567: System.err.println("SQLEXception in run of ConnThread"
1568: + ex.toString());
1569: SQLFORM.popup("Error in connect:" + ex.toString());
1570: } catch (ClassNotFoundException ex) {
1571: System.err.println("run:" + ex.toString());
1572: }
1573: }
1574: }
1575:
1576: /** This class converts a result sets columns and data to 2 vectors
1577: * These vectors can be used easily for display or as parameters to the JTable
1578: * constructor
1579: * Shoud this have been static ???
1580: * vData is a vector of vectors (depends on param)
1581: */
1582: class ResultSetConverter {
1583:
1584: List vData;
1585: List vColumns;
1586:
1587: /* This one creates a one dim List of data, or two dim depending on param */
1588: public ResultSetConverter(ResultSet rs, int dimension) {
1589:
1590: int i;
1591: vData = new ArrayList(100);
1592: vColumns = new ArrayList(50);
1593: List vRow = new ArrayList(50);
1594:
1595: // Get the ResultSetMetaData. This will be used for
1596: // the column headings
1597:
1598: try {
1599: ResultSetMetaData rsmd = rs.getMetaData();
1600:
1601: // Get the number of columns in the result set
1602:
1603: int numCols = rsmd.getColumnCount();
1604:
1605: // Display column headings
1606:
1607: for (i = 1; i <= numCols; i++) {
1608:
1609: vColumns.add(rsmd.getColumnLabel(i).toString());
1610: }
1611:
1612: // Display data, fetching until end of the result set
1613:
1614: boolean more = rs.next();
1615: while (more) {
1616:
1617: // Loop through each column, getting the
1618: // column data and displaying
1619:
1620: for (i = 1; i <= numCols; i++) {
1621:
1622: if (dimension == 2)
1623: vRow.add(rs.getString(i));
1624: else
1625: vData.add(rs.getString(i));
1626: }
1627:
1628: // Fetch the next result set row
1629: if (dimension == 2) {
1630: vData.add(vRow);
1631: vRow = new ArrayList(50);
1632: }
1633: more = rs.next();
1634: }
1635: } catch (SQLException ex) {
1636: }
1637: }
1638:
1639: List getDataVector() {
1640: return vData;
1641: }
1642:
1643: List getColumnVector() {
1644: return vColumns;
1645: }
1646: } // class ResultSetConverter
1647:
1648: class MyTableModelListener implements TableModelListener {
1649: //
1650: // Implementation of the TableModelListener interface,
1651: //
1652:
1653: AbstractTableModel _tm;
1654:
1655: public MyTableModelListener(AbstractTableModel tm) {
1656: _tm = tm;
1657: }
1658:
1659: // By default forward all events to all the listeners.
1660: public void tableChanged(TableModelEvent e) {
1661: _tm.fireTableChanged(e);
1662: }
1663: }
|