001 /*
002 * CRIMSON
003 * Copyright (c) 2006, Stephen Fisher, Susan Davidson, and Junhyong Kim,
004 * University of Pennsylvania.
005 *
006 * This program is free software; you can redistribute it and/or
007 * modify it under the terms of the GNU General Public License as
008 * published by the Free Software Foundation; either version 2 of the
009 * License, or (at your option) any later version.
010 *
011 * This program is distributed in the hope that it will be useful, but
012 * WITHOUT ANY WARRANTY; without even the implied warranty of
013 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
014 * General Public License for more details.
015 *
016 * You should have received a copy of the GNU General Public License
017 * along with this program; if not, write to the Free Software
018 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
019 * 02110-1301 USA.
020 *
021 * @(#)Database.java
022 */
023
024 package edu.upenn.crimson.io;
025
026 import edu.upenn.crimson.*;
027 import java.util.ArrayList;
028 import java.util.HashMap;
029 import java.util.HashSet;
030 import java.util.Scanner;
031 import java.util.Iterator;
032 import java.sql.*;
033 import java.net.*;
034 import java.io.*;
035
036 /**
037 * Functions related to opening, closing, and quering a Database.
038 *
039 * @XXX Should see if can optimize db access by maintaining one
040 * Statement that is created every time a database connection is made.
041 *
042 * @author Stephen Fisher
043 * @version $Id: Database.java,v 1.33 2009/07/16 16:19:52 fisher Exp $
044 */
045
046 public class Database {
047
048 /** This is a reference to the actual database connection. */
049 public static Connection dbConnection = null;
050
051 /** Need to store this in the case of using a web proxy. */
052 public static String password = "";
053
054 //--------------------------------------------------------------------------
055 // Getters and Setters
056
057 /** Get the DBConnection. */
058 public static Connection getConnection() { return dbConnection; }
059
060 /** Get a the database Statement object necessary to perform a
061 database query. Don't use the default statement because then
062 every time the statement is used, it would over write the
063 previous results.
064 */
065 public static Statement getStatement() {
066 try {
067 // create a statement
068 return dbConnection.createStatement();
069 } catch (SQLException e) {
070 CrimsonUtils.printError("Can't create a new database SQL statement.");
071 CrimsonUtils.printError(e.getMessage());
072 return null;
073 }
074 }
075
076 //--------------------------------------------------------------------------
077 // Connectivity Methods
078
079 /**
080 * Returns true if a database connection exists or using proxy
081 * server and false otherwise.
082 */
083 public static boolean isOpen() {
084 try {
085 if (((dbConnection == null) || dbConnection.isClosed()) && (! isProxy())) return false;
086 else return true;
087 } catch (SQLException e) {
088 CrimsonUtils.printError("Unable to test status of database connection.");
089 CrimsonUtils.printError(e.getMessage());
090 return false;
091 }
092 }
093
094 /** Reconnects to the existing database. */
095 /*
096 public static void reopen() {
097 if (dbSource == null) {
098 CrimsonUtils.printError("Can only reconnect to a database that was previously opened.");
099 return;
100 }
101
102 if (isOpen()) {
103 CrimsonUtils.printError("Must close database connection because it can be reopened.");
104 return;
105 }
106
107 try {
108 dbConnection = dbSource.getConnection();
109 } catch (SQLException e) {
110 CrimsonUtils.printError("Unable to connect to database.");
111 CrimsonUtils.printError(e.getMessage());
112 return;
113 }
114
115 // rebuild the table lists
116 ObjectHandles.buildLists();
117
118 CrimsonUtils.printMsg("Database re-opened.");
119 }
120 */
121
122 /**
123 * Opens a connection to the database, using the default username
124 * and password.
125 * @XXX This is a very insecure way of handling the password.
126 */
127 public static void open(String password) {
128 open(CrimsonUtils.getUsername(), password, CrimsonUtils.getServer(),
129 CrimsonUtils.getPort(), CrimsonUtils.getDatabase());
130 }
131
132 /**
133 * Opens a connection to the database. The default username and
134 * database will be set to the values provided here.
135 * @XXX This is a very insecure way of handling the password.
136 */
137 public static void open(String username, String password, String server,
138 int port, String database) {
139 // make sure database is closed
140 if (dbConnection != null) close();
141 if (dbConnection != null) {
142 CrimsonUtils.printError("Can't close database, prior to opening new database.");
143 return;
144 }
145
146 // if web proxy then don't open connection
147 if (! server.toUpperCase().startsWith("HTTP")) {
148 String url = SQL.dbURL() + server;
149 if (port > 0) url += ":" + Integer.toString(port);
150 // "database" is actually the SID
151 if (! CrimsonUtils.isEmpty(database)) url += "/" + database;
152
153 try {
154 dbConnection = DriverManager.getConnection(url, username, password);
155 } catch (SQLException e) {
156 CrimsonUtils.printError("Unable to connect to database.");
157 CrimsonUtils.printError(e.getMessage());
158 return;
159 }
160
161 // set the connection reference in CrimsonMain
162 CrimsonMain.setConnection(dbConnection);
163 } else {
164 // web proxy needs the password. We are storing it here so it
165 // will not persist when the application is closed.
166 Database.password = password;
167 }
168
169 // save the values the user entered, for 'username' and
170 // 'database,' so they can be used as the defaults the next
171 // time the user opens a connection
172 CrimsonUtils.setUsername(username);
173 CrimsonUtils.setServer(server);
174 CrimsonUtils.setPort(port);
175 CrimsonUtils.setDatabase(database);
176
177 // create the TREES and QUERIES tables if necessary. Need to
178 // update the CrimsonUtils values so that isProxy() is valid.
179 if (! tableExists("MODELS")) {
180 if (isProxy()) {
181 CrimsonUtils.printError("Database missing MODELS table. Can't connect via proxy.");
182 return;
183 } else createTable("MODELS");
184 }
185 if (! tableExists("TREES")) {
186 if (isProxy()) {
187 CrimsonUtils.printError("Database missing TREES table. Can't connect via proxy.");
188 return;
189 } else createTable("TREES");
190 }
191 if (! tableExists("PARTITIONS")) {
192 if (isProxy()) {
193 CrimsonUtils.printError("Database missing PARTITIONS table. Can't connect via proxy.");
194 return;
195 } else createTable("PARTITIONS");
196 }
197 if (! tableExists("PART_DATA")) {
198 if (isProxy()) {
199 CrimsonUtils.printError("Database missing PART_DATA table. Can't connect via proxy.");
200 return;
201 } else createTable("PART_DATA");
202 }
203 if (! tableExists("QUERIES")) {
204 if (isProxy()) {
205 CrimsonUtils.printError("Database missing QUERIES table. Can't connect via proxy.");
206 return;
207 } else createTable("QUERIES");
208 }
209
210 // build the list of tables
211 ObjectHandles.buildLists();
212
213 CrimsonUtils.printMsg("Database opened.");
214 }
215
216 /**
217 * Closes the connection to the database and sets dbConnection to
218 * null. dbSource isn't set to null so that the user can easily
219 * reconnect to the existing database if desired.
220 */
221 public static void close() {
222 // if a connection exists, then close the connection
223 if (dbConnection != null) {
224 try {
225 dbConnection.close();
226 // this simplifies the test of an open database when
227 // changing the DB type or opening a new database.
228 dbConnection = null;
229 } catch (SQLException e) {
230 CrimsonUtils.printError("Unable to close connection to database.");
231 CrimsonUtils.printError(e.getMessage());
232 return;
233 }
234 }
235
236 // closed database so reset table lists
237 ObjectHandles.clearLists();
238
239 // remove the connection reference in CrimsonMain
240 CrimsonMain.setConnection(null);
241
242 CrimsonUtils.printMsg("Database closed.");
243 }
244
245 /**
246 * This will perform a simple query of the database to test if the
247 * database connection is functional.
248 */
249 public static void testConnection() {
250 if (! isOpen()) {
251 CrimsonUtils.printError("Database connection test failed.");
252 CrimsonUtils.printError("No database connection.");
253 return;
254 }
255
256 ArrayList out = Database.sqlToArray(SQL.getDate());
257 if (CrimsonUtils.isEmpty(out)) {
258 CrimsonUtils.printError("Database connection test failed.");
259 } else {
260 CrimsonUtils.printMsg("The database date is " + String.valueOf(out.get(0)));
261 CrimsonUtils.printMsg("Database connection test succeeded.");
262 }
263 }
264
265 /** This will return true if using a web proxy. */
266 public static boolean isProxy() {
267 return CrimsonUtils.getServer().toUpperCase().startsWith("HTTP");
268 }
269
270
271 //--------------------------------------------------------------------------
272 // Table Methods
273
274 /**
275 * Test if the table exists in the current database. If no
276 * database is open then this will return false.
277 */
278 public static boolean tableExists(String table) {
279 if (! isOpen()) {
280 CrimsonUtils.printError("Can't test if table (" + table + ") exists since no database is open.");
281 return false;
282 }
283
284 ArrayList out = Database.sqlToArray(SQL.tableExists(table.toUpperCase()));
285
286 // if table does exist then 'out' will contain the name of the
287 // table, otherwise 'out' will be empty
288 if (CrimsonUtils.isEmpty(out)) return false;
289 else return true;
290 }
291
292 /**
293 * This will create a table in the current database. This only
294 * needs to be done once for each database.
295 */
296 public static void createTable(String table) {
297 if (! isOpen()) {
298 CrimsonUtils.printError("Must open a database before creating tables.");
299 return;
300 }
301
302 // test if table already exists
303 if (tableExists(table)) {
304 CrimsonUtils.printError("The table already exists in this database.");
305 return;
306 }
307
308 // get statement for creating MODELS table, that is
309 // appropriate to the type of database that's open
310 String sql = SQL.createTable(table);
311
312 // perform the table creation
313 if ((! CrimsonUtils.isEmpty(sql)) && execUpdate(sql)) {
314 CrimsonUtils.printMsg("Table (" + table + ") did not exist and was created.");
315 } else {
316 CrimsonUtils.printError("Unable to create table (" + table + ").");
317 }
318 }
319
320 /**
321 * Removes a record from the table. This returns true if it
322 * completes without error.
323 */
324 public static boolean delete(String table, String id) {
325 if (! isOpen()) {
326 CrimsonUtils.printError("Can not delete " + id + ", no open database.");
327 return false;
328 }
329
330 if (! execUpdate("DELETE FROM " + table + " WHERE id = '" + id.toUpperCase() + "'")) {
331 CrimsonUtils.printError("Error deleting " + id + " from " + table);
332 return false;
333 }
334
335 return true;
336 }
337
338 /**
339 * This will load the specified CLOB and return the CLOB data as a
340 * String. This throws the exception to allow for error handling
341 * by the calling method. This allows for inputs that are either
342 * String or Clob, to simplify proxy coding which returns a String
343 * instead of a Clob.
344 */
345 public static String readClob(Object object) throws SQLException {
346 // if this is a string, then just return the string
347 if (object instanceof String) {
348 String sObj = (String) object;
349 // test if empty_clob
350 if (sObj.equals("EMPTY_CLOB()")) return "";
351 else return sObj;
352 } else if (object instanceof Clob) {
353 Clob clob = (Clob) object;
354
355 StringBuffer sb = new StringBuffer();
356 if ((clob != null) && (clob.length() > 0)) {
357 try {
358 Reader reader = clob.getCharacterStream();
359 BufferedReader bReader = new BufferedReader(reader);
360
361 // append each line to the StringBuffer
362 String line;
363 // while ((line = bReader.readLine()) != null) sb.append(line + "\n");
364 while ((line = bReader.readLine()) != null) sb.append(line);
365
366 // close the reader
367 bReader.close();
368 reader.close();
369 } catch (IOException e) {
370 CrimsonUtils.printError("Error with BufferedReader while loading CLOB.");
371 CrimsonUtils.printError(e.getMessage());
372 return "";
373 }
374 }
375 return sb.toString();
376 } else {
377 CrimsonUtils.printError("Invalid object for Database.readClob().");
378 return "";
379 }
380 }
381
382 /**
383 * This will load the specified CLOB and return the CLOB data as a
384 * HashSet. This allows for inputs that are either String or
385 * Clob, to simplify proxy coding which returns a String instead
386 * of a Clob.
387 */
388 public static HashSet readClobSet(Object object) throws SQLException {
389 // if this is a string, then just return the string
390 if (object instanceof String) {
391 HashSet out = new HashSet();
392 String sObj = (String) object;
393
394 // test if empty_clob
395 if (sObj.equals("EMPTY_CLOB()")) return out;
396
397 Scanner scanner = new Scanner(sObj).useDelimiter(",");
398 while (scanner.hasNext()) out.add(scanner.next().trim());
399 return out;
400 } else if (object instanceof Clob) {
401 Clob clob = (Clob) object;
402
403 HashSet value = new HashSet();
404 if ((clob != null) && (clob.length() > 0)) {
405 try {
406 StringBuffer sb = new StringBuffer();
407 Reader reader = clob.getCharacterStream();
408 BufferedReader bReader = new BufferedReader(reader);
409 String line;
410 while ((line = bReader.readLine()) != null) sb.append(line);
411 Scanner scanner = new Scanner(sb.toString()).useDelimiter(",");
412 while (scanner.hasNext()) value.add(scanner.next().trim());
413
414 // close the reader
415 bReader.close();
416 reader.close();
417 } catch (IOException e) {
418 CrimsonUtils.printError("Error with BufferedReader while loading CLOB.");
419 CrimsonUtils.printError(e.getMessage());
420 return null;
421 }
422 }
423 return value;
424 } else {
425 CrimsonUtils.printError("Database.readClobSet() requires Clob object.");
426 return null;
427 }
428 }
429
430 /** This will store 'value' as a CLOB in 'id'. */
431 public static boolean writeClob(String table, String id, String field, String value) {
432 if (CrimsonUtils.isEmpty(value)) {
433 // need to empty out the CLOB
434 String sValue = "";
435 if (SQL.isOracle()) sValue = "empty_clob()";
436 if (SQL.isMySQL()) sValue = "''";
437 String sql = "UPDATE " + table + " SET " + field + " = " + sValue + " ";
438 sql += " WHERE id = '" + id.toUpperCase() + "'";
439
440 if (execUpdate(sql)) return true;
441 else return false;
442 }
443
444 // while MySQL does read clobs, it doesn't seem to write
445 // CLOBs, so we treat MySQL differently
446 if (SQL.isMySQL()) {
447 String sql = "UPDATE " + table + " SET " + field + " = '" + value + "' ";
448 sql += " WHERE id = '" + id.toUpperCase() + "'";
449 if (execUpdate(sql)) return true;
450 else return false;
451 }
452
453 // get CLOB
454 String sql = "SELECT " + field + " FROM " + table + " WHERE id = '" + id.toUpperCase() + "' FOR UPDATE";
455 Clob clob = sqlToClob(sql);
456
457 // if CLOB null then error
458 if (clob == null) {
459 CrimsonUtils.printError("No empty CLOB to update (id = " + id + ", table = " + table + ").");
460 return false;
461 } else {
462 try {
463 clob.setString(1, value);
464 } catch (SQLException e) {
465 CrimsonUtils.printError("Error with SQL while saving " + table + " CLOB.");
466 CrimsonUtils.printError(e.getMessage());
467 return false;
468 }
469 }
470
471 return true;
472 }
473
474 /** This will store 'value' as a CLOB in 'id'. */
475 public static boolean writeClobSet(String table, String id, String field, HashSet value) {
476 if (CrimsonUtils.isEmpty(value)) {
477 // need to empty out the CLOB
478 String sValue = "";
479 if (SQL.isOracle()) sValue = "empty_clob()";
480 if (SQL.isMySQL()) sValue = "''";
481 String sql = "UPDATE " + table + " SET " + field + " = " + sValue + " ";
482 sql += " WHERE id = '" + id.toUpperCase() + "'";
483
484 if (execUpdate(sql)) return true;
485 else return false;
486 }
487
488 // while MySQL does read clobs, it doesn't seem to write
489 // CLOBs, so we treat MySQL differently
490 if (SQL.isMySQL()) {
491 String sValue = "";
492 Iterator i = value.iterator();
493 sValue += (String) i.next();
494 while (i.hasNext()) sValue += ", " + i.next();
495 String sql = "UPDATE " + table + " SET " + field + " = '" + sValue + "' ";
496 sql += " WHERE id = '" + id.toUpperCase() + "'";
497 if (execUpdate(sql)) return true;
498 else return false;
499 }
500
501 // get CLOB
502 String sql = "SELECT " + field + " FROM " + table + " WHERE id = '" + id.toUpperCase() + "' FOR UPDATE";
503 Clob clob = sqlToClob(sql);
504
505 // if clob null then error
506 if (clob == null) {
507 CrimsonUtils.printError("No empty CLOB to update (id = " + id + ", table = " + table + ").");
508 return false;
509 } else {
510 try {
511 // write string of items separated by ';'
512 Writer writer = clob.setCharacterStream(0);
513 BufferedWriter bWriter = new BufferedWriter(writer);
514 Iterator i = value.iterator();
515 bWriter.write((String) i.next());
516 while (i.hasNext()) bWriter.write(", " + i.next());
517
518 // close the writer
519 bWriter.close();
520 writer.close();
521 } catch (IOException e) {
522 CrimsonUtils.printError("Error in BufferedWriter while saving " + table + " CLOB.");
523 CrimsonUtils.printError(e.getMessage());
524 return false;
525 } catch (SQLException e) {
526 CrimsonUtils.printError("Error with SQL while saving " + table + " CLOB.");
527 CrimsonUtils.printError(e.getMessage());
528 return false;
529 }
530
531 return true;
532 }
533 }
534
535 /**
536 * This will store 'value' as a CLOB in the PART_DATA table.
537 * @XXX The partitionID and speciesID must be upper case. We
538 * don't do that here because it's more computationally efficient
539 * to make them caps in the routine that is calling this method.
540 */
541 public static boolean writeDataClob(String partitionID, String speciesID, String field, String value) {
542 if (CrimsonUtils.isEmpty(value)) {
543 // need to empty out the CLOB
544 String sValue = "";
545 if (SQL.isOracle()) sValue = "empty_clob()";
546 if (SQL.isMySQL()) sValue = "''";
547 String sql = "UPDATE part_data SET " + field + " = " + sValue + " ";
548 sql += " WHERE partition_id = '" + partitionID + "' and species_id = '" + speciesID + "'";
549
550 if (execUpdate(sql)) return true;
551 else return false;
552 }
553
554 // while MySQL does read clobs, it doesn't seem to write
555 // CLOBs, so we treat MySQL differently
556 if (SQL.isMySQL()) {
557 String sql = "UPDATE part_data SET " + field + " = '" + value + "' ";
558 sql += " WHERE partition_id = '" + partitionID + "' and species_id = '" + speciesID + "'";
559 if (execUpdate(sql)) return true;
560 else return false;
561 }
562
563 // get CLOB
564 String sql = "SELECT " + field + " FROM part_data ";
565 sql += "WHERE partition_id = '" + partitionID + "' and species_id = '" + speciesID + "' FOR UPDATE";
566 Clob clob = sqlToClob(sql);
567
568 // if CLOB null then error
569 if (clob == null) {
570 CrimsonUtils.printError("No empty CLOB to update (partition = " + partitionID + ", species = " + speciesID + ").");
571 return false;
572 } else {
573 try {
574 // write string of items separated by ';'
575 Writer writer = clob.setCharacterStream(0);
576 BufferedWriter bWriter = new BufferedWriter(writer);
577 bWriter.write(value);
578
579 // close the writer
580 bWriter.close();
581 writer.close();
582 } catch (IOException e) {
583 CrimsonUtils.printError("Error in BufferedWriter while saving " + field + " CLOB.");
584 CrimsonUtils.printError(e.getMessage());
585 return false;
586 } catch (SQLException e) {
587 CrimsonUtils.printError("Error with SQL while saving " + field + " CLOB.");
588 CrimsonUtils.printError(e.getMessage());
589 return false;
590 }
591
592 return true;
593 }
594 }
595
596 //--------------------------------------------------------------------------
597 // SQL Methods
598
599 /**
600 * This will execute SQL queries of the database. The output will
601 * be returned as an ArrayList with each line being a different
602 * element in the list. If the query fails, then will return null.
603 * If only one column then each array element will be an Object.
604 * If more than one column then each array element will be an
605 * array of Objects. Note that this will append a ';' to the end
606 * of the sql command being submitted to the proxy server.
607 */
608 public static ArrayList sqlToArray(String sql) {
609 if (! isOpen()) {
610 CrimsonUtils.printError("No open database.");
611 return null;
612 }
613
614 if (isProxy()) return proxyToArray(sql);
615 else return dbToArray(sql);
616 }
617
618 public static ArrayList proxyToArray(String sql) {
619 try {
620 // construct string to connect to proxy server
621 String data = URLEncoder.encode("sid", "UTF-8") + "=" + URLEncoder.encode(CrimsonUtils.getDatabase(), "UTF-8");
622 data += "&" + URLEncoder.encode("user", "UTF-8") + "=" + URLEncoder.encode(CrimsonUtils.getUsername(), "UTF-8");
623 data += "&" + URLEncoder.encode("passwd", "UTF-8") + "=" + URLEncoder.encode(Database.password, "UTF-8");
624 data += "&" + URLEncoder.encode("sql", "UTF-8") + "=" + URLEncoder.encode(sql+";", "UTF-8");
625
626 // open connection to proxy
627 URL url = new URL(CrimsonUtils.getServer());
628 URLConnection conn = url.openConnection();
629 conn.setDoOutput(true);
630
631 // send data string and close connection
632 OutputStreamWriter wr = new OutputStreamWriter(conn.getOutputStream());
633 wr.write(data);
634 wr.flush();
635 wr.close();
636
637 // get the response. we expect each line to be a ";"
638 // delimited list of field values
639 BufferedReader rd = new BufferedReader(new InputStreamReader(conn.getInputStream()));
640
641 // process first row separately, to allow for computing
642 // number of columns
643 String line = rd.readLine();
644 ArrayList out = new ArrayList();
645 if (line == null) return out;
646
647 // use StringTokenizer to count number of columns
648 Scanner scanner = new Scanner(line).useDelimiter(";");
649 int numCols = 0;
650 while (scanner.hasNext()) {
651 numCols++;
652 scanner.next();
653 }
654
655 scanner = new Scanner(line).useDelimiter(";");
656 if (numCols == 1) {
657 // process first row
658 out.add(scanner.next());
659
660 // process remaining rows
661 while ((line = rd.readLine()) != null) {
662 // XXX we could trim ';' more efficiently
663 scanner = new Scanner(line).useDelimiter(";");
664 out.add(scanner.next());
665 }
666 } else {
667 // process first row
668 ArrayList row = new ArrayList();
669 while (scanner.hasNext()) row.add(scanner.next());
670 out.add(row);
671
672 // process remaining rows
673 while ((line = rd.readLine()) != null) {
674 scanner = new Scanner(line).useDelimiter(";");
675 row = new ArrayList();
676 while (scanner.hasNext()) row.add(scanner.next());
677 out.add(row);
678 }
679 }
680
681 // close connection
682 rd.close();
683
684 return out;
685 } catch (Exception e) {
686 CrimsonUtils.printError("Error running SQL query: \n " + sql);
687 CrimsonUtils.printError(e.getMessage());
688 return null;
689 }
690 }
691
692 public static ArrayList dbToArray(String sql) {
693 try {
694 // execute the query
695 Statement statement = getStatement();
696 ResultSet results = statement.executeQuery(sql);
697
698 // if the query failed then just exit
699 if (results == null) return null;
700
701 // compute number of columns in output
702 ResultSetMetaData rsmd = results.getMetaData();
703 int cols = rsmd.getColumnCount();
704
705 ArrayList out = new ArrayList();
706 if (cols == 1) {
707 // only 1 column
708 while (results.next()) out.add(results.getObject(1));
709 } else {
710 while (results.next()) {
711 ArrayList row = new ArrayList();
712 for (int i = 1; i <= cols; i++) row.add(results.getObject(i));
713 out.add(row);
714 }
715 }
716
717 // close the statement, which will close the ResultSet
718 statement.close();
719 return out;
720 } catch (SQLException e) {
721 CrimsonUtils.printError("Error running SQL query: \n " + sql);
722 CrimsonUtils.printError(e.getMessage());
723 return null;
724 }
725 }
726
727 /**
728 * This will execute SQL queries of the database. The output will
729 * be returned as a single Clob. If the query fails, then will
730 * return null.
731 * @XXX This will only work for results with only 1 column and 1
732 * row (ie a single field/column from a single table record).
733 */
734 public static Clob sqlToClob(String sql) {
735 try {
736 // execute the query
737 Statement statement = getStatement();
738 ResultSet results = statement.executeQuery(sql);
739
740 // if the query failed then just exit
741 if (results == null) return null;
742
743 // move to the first record in the results, all following
744 // records will be ignored
745 results.next();
746 Clob clob = results.getClob(1);
747
748 statement.close();
749 return clob;
750 } catch (SQLException e) {
751 CrimsonUtils.printError("Error getting CLOB results from SQL query: \n " + sql);
752 CrimsonUtils.printError(e.getMessage());
753 return null;
754 }
755 }
756
757 /**
758 * This will execute SQL queries of the database. This can be
759 * used, for example, to run Oracle 'SELECT' statements. The
760 * output is returned in an ArrayList containing HashMaps, with
761 * each map corresponding to one row of data. The keys of the map
762 * are the column names.
763 */
764 public static ArrayList sqlToHash(String sql) {
765 try {
766 // execute the query
767 Statement statement = getStatement();
768 ResultSet results = statement.executeQuery(sql);
769
770 // if the query failed then just exit
771 if (results == null) return null;
772
773 // compute number of columns in output
774 ResultSetMetaData rsmd = results.getMetaData();
775 int cols = rsmd.getColumnCount();
776
777 // build a 2D array containing the output
778 ArrayList out = new ArrayList();
779 while (results.next()) {
780 HashMap row = new HashMap();
781 for (int i = 1; i <= cols; i++) {
782 String cName = rsmd.getColumnName(i);
783 row.put(cName, results.getObject(i));
784 }
785 out.add(row);
786 }
787
788 // close the statement, which will close the ResultSet
789 statement.close();
790 return out;
791 } catch (SQLException e) {
792 CrimsonUtils.printError("SQL query failed: \n " + sql);
793 CrimsonUtils.printError(e.getMessage());
794 return null;
795 }
796 }
797
798 /**
799 * This is a specialized version of proxyToArray() to import
800 * queries.
801 */
802 public static StringBuilder ImportQuery(String sql) {
803 try {
804 // construct string to connect to proxy server
805 String data = URLEncoder.encode("sid", "UTF-8") + "=" + URLEncoder.encode(CrimsonUtils.getDatabase(), "UTF-8");
806 data += "&" + URLEncoder.encode("user", "UTF-8") + "=" + URLEncoder.encode(CrimsonUtils.getUsername(), "UTF-8");
807 data += "&" + URLEncoder.encode("passwd", "UTF-8") + "=" + URLEncoder.encode(Database.password, "UTF-8");
808 data += "&" + URLEncoder.encode("sql", "UTF-8") + "=" + URLEncoder.encode(sql+";", "UTF-8");
809
810 // open connection to proxy
811 URL url = new URL(CrimsonUtils.getServer());
812 URLConnection conn = url.openConnection();
813 conn.setDoOutput(true);
814
815 // send data string and close connection
816 OutputStreamWriter wr = new OutputStreamWriter(conn.getOutputStream());
817 wr.write(data);
818 wr.flush();
819 wr.close();
820
821 // get the response. we expect each line to be a ";"
822 // delimited list of field values
823 BufferedReader rd = new BufferedReader(new InputStreamReader(conn.getInputStream()));
824
825 String line;
826 // this should be large enough to load 3,600 simple queries
827 StringBuilder sb = new StringBuilder(250000);
828 while ((line = rd.readLine()) != null) {
829 sb.append(line);
830 }
831
832 // close connection
833 rd.close();
834
835 return sb;
836 } catch (Exception e) {
837 CrimsonUtils.printError("Error running SQL query: \n " + sql);
838 CrimsonUtils.printError(e.getMessage());
839 return null;
840 }
841 }
842
843 /**
844 * This will execute SQL commands that update the database (ie
845 * CREATE, DROP, INSERT, DELETE, etc). This will return true if it
846 * completes or false on error. COMMIT will automatically be run
847 * after the execution of the sql statement.
848 */
849 public static boolean execUpdate(String sql) {
850 if (! isOpen()) {
851 CrimsonUtils.printError("Must open a database before running a query.");
852 return false;
853 }
854
855 if (isProxy()) {
856 CrimsonUtils.printError("Can't publish to database when using proxy server.");
857 return false;
858 }
859
860 // to avoid potential problems with case, we force everything
861 // to be upper case
862 sql = sql.toUpperCase();
863
864 try {
865 // create a statement
866 Statement statement = getStatement();
867
868 // execute the query
869 statement.executeUpdate(sql);
870
871 // execute COMMIT for ORACLE
872 if (SQL.isOracle()) commit();
873
874 statement.close();
875 return true;
876 } catch (SQLException e) {
877 CrimsonUtils.printError("SQL query failed: \n " + sql);
878 CrimsonUtils.printError(e.getMessage());
879 return false;
880 }
881 }
882
883 /** This will for a commit. */
884 public static boolean commit() {
885 try {
886 dbConnection.commit();
887 } catch (SQLException e) {
888 CrimsonUtils.printError("SQL commit failed.");
889 CrimsonUtils.printError(e.getMessage());
890 return false;
891 }
892
893 return true;
894 }
895
896 } // Database.java