ant-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject [Bug 273] New - Request for added functionallity for SqlExec task BugRat Report#514
Date Thu, 01 Feb 2001 09:43:01 GMT
http://nagoya.apache.org/bugzilla/show_bug.cgi?id=273

*** shadow/273	Thu Feb  1 01:43:01 2001
--- shadow/273.tmp.8581	Thu Feb  1 01:43:01 2001
***************
*** 0 ****
--- 1,747 ----
+ +============================================================================+
+ | Request for added functionallity for SqlExec task BugRat Report#514        |
+ +----------------------------------------------------------------------------+
+ |        Bug #: 273                         Product: Ant                     |
+ |       Status: UNCONFIRMED                 Version: 1.2                     |
+ |   Resolution:                            Platform: All                     |
+ |     Severity: Normal                   OS/Version: All                     |
+ |     Priority: High                      Component: Main                    |
+ +----------------------------------------------------------------------------+
+ |  Assigned To: ant-dev@jakarta.apache.org                                   |
+ |  Reported By: johan.adelow@corustechnologies.com                           |
+ |      CC list: Cc:                                                          |
+ +----------------------------------------------------------------------------+
+ |          URL:                                                              |
+ +============================================================================+
+ |                              DESCRIPTION                                   |
+ I order to be able to launch trigger code, stored procedures and other code that includes
delimiters like ";" into an Oracle database. 
+ There is a need for another type of delimiter since the sql code otherwise will be executed
when hitting the first delimiter.
+ 
+ I have solved this by modifying the SqlExec task, inventing two new properties,delimiter
and delimiter_type
+ 
+ The delimiter is used to set the delimiter, that is for instance  ";" or "/" default is
";".
+ 
+ The delimiter type is either "normal or "row"
+ With the row delimiter I can use a build file like this 
+ 
+ <sql 
+ driver="${oracle_driver}" 
+ url="${oracle_service}" 
+ userid="${db_user}"
+ password="${db_passwd}" 
+ src="${exec_file}" 
+ print="yes" 
+ output="${tmplogfile}"
+ delimiter="/"
+ delimiter_type="row"/>
+ 
+ to launch for instance an sql file like this:
+ 
+ create or replace TRIGGER x_ord_customer before insert or update or delete  
+ on customer
+ for each row
+ begin
+  if INSERTING then
+     ORD_PAD.ORD_CUSTOMER_INS( :new.id,
+                              :new.name,
+                              :new.mail_address); 
+  elsif UPDATING then
+     ORD_PAD.ORD_CUSTOMER_UPD (:new.id,
+                              :new.name,
+                               1,
+                               :new.mail_address,
+                               1);
+  elsif DELETING then
+     ORD_PAD.ORD_CUSTOMER_DEL (:old.id);
+  end if;
+ end;
+ /   
+ 
+ 
+ Is it of any use for the ant project to add functionallity like this or could you add some
functionality to make it possible to lauch code into the database?
+ 
+ The modified SqlExec task:
+ 
+ /*
+  * The Apache Software License, Version 1.1
+  *
+  * Copyright (c) 1999 The Apache Software Foundation.  All rights 
+  * reserved.
+  *
+  * Redistribution and use in source and binary forms, with or without
+  * modification, are permitted provided that the following conditions
+  * are met:
+  *
+  * 1. Redistributions of source code must retain the above copyright
+  *    notice, this list of conditions and the following disclaimer. 
+  *
+  * 2. Redistributions in binary form must reproduce the above copyright
+  *    notice, this list of conditions and the following disclaimer in
+  *    the documentation and/or other materials provided with the
+  *    distribution.
+  *
+  * 3. The end-user documentation included with the redistribution, if
+  *    any, must include the following acknowlegement:  
+  *       "This product includes software developed by the 
+  *        Apache Software Foundation (http://www.apache.org/)."
+  *    Alternately, this acknowlegement may appear in the software itself,
+  *    if and wherever such third-party acknowlegements normally appear.
+  *
+  * 4. The names "The Jakarta Project", "Tomcat", and "Apache Software
+  *    Foundation" must not be used to endorse or promote products derived
+  *    from this software without prior written permission. For written 
+  *    permission, please contact apache@apache.org.
+  *
+  * 5. Products derived from this software may not be called "Apache"
+  *    nor may "Apache" appear in their names without prior written
+  *    permission of the Apache Group.
+  *
+  * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
+  * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
+  * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
+  * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
+  * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
+  * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
+  * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
+  * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
+  * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
+  * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
+  * SUCH DAMAGE.
+  * ====================================================================
+  *
+  * This software consists of voluntary contributions made by many
+  * individuals on behalf of the Apache Software Foundation.  For more
+  * information on the Apache Software Foundation, please see
+  * <http://www.apache.org/>.
+  */
+ 
+ package org.apache.tools.ant.taskdefs;
+ 
+ import org.apache.tools.ant.*;
+ import org.apache.tools.ant.types.*;
+ 
+ import java.io.*;
+ import java.util.Enumeration;
+ import java.util.StringTokenizer;
+ import java.util.Vector;
+ import java.util.Properties;
+ import java.util.zip.*;
+ import java.sql.*;
+ 
+ /**
+  * Reads in a text file containing SQL statements seperated with semicolons
+  * and executes it in a given db.
+  * Both -- and // maybe used as comments.
+  * 
+  * @author <a href="mailto:jeff@custommonkey.org">Jeff Martin</a>
+  */
+ public class SQLExec extends Task {
+     
+     private int goodSql = 0, totalSql = 0;
+ 
+     private Path classpath;
+ 
+     private AntClassLoader loader;
+ 
+     
+     /**
+      * SQL delimiter
+      * Added 001122			
+      */
+     private String delimiter = ";";
+ 
+     /**
+      * SQL delimiter_type
+      * Added 001122
+      * To swich between a normal delimiter and a row delimiter ("normal" or "row")			
+      */ 
+     private String delimiter_type = "normal";
+ 
+ 
+ 
+      /**
+      * Database connection
+      */
+     private Connection conn = null;
+     
+     /**
+      * Autocommit flag. Default value is false
+      */
+     private boolean autocommit=false;
+     
+     /**
+      * SQL statement
+      */
+     private Statement statement = null;
+ 
+     /**
+      * DB driver.
+      */
+     private String driver = null;
+ 
+     /**
+      * DB url.
+      */
+     private String url = null;
+ 
+     /**
+      * User name.
+      */
+     private String userId = null;
+ 
+     /**
+      * Password
+      */
+     private String password = null;
+ 
+     /**
+      * SQL input file
+      */
+     private File srcFile = null;
+ 
+     /**
+      * SQL input command
+      */
+     private String sqlCommand = "";
+ 
+     /**
+      * SQL transactions to perform
+      */
+     private Vector transactions = new Vector();
+ 
+     /**
+      * Print SQL results.
+      */
+     private boolean print = false;
+ 
+     /**
+      * Print header columns.
+      */
+     private boolean showheaders = true;
+ 
+     /**
+      * Results Output file.
+      */
+     private File output = null;
+ 
+     /**
+      * RDBMS Product needed for this SQL.
+      **/
+     private String rdbms = null;
+ 
+     /**
+      * RDBMS Version needed for this SQL.
+      **/
+     private String version = null;
+ 
+     /**
+      * Action to perform if an error is found
+      **/
+     private String onError = "abort";
+ 
+     /**
+      * Set the classpath for loading the driver.
+      */
+     public void setClasspath(Path classpath) {
+         if (this.classpath == null) {
+             this.classpath = classpath;
+         } else {
+             this.classpath.append(classpath);
+         }
+     }
+ 
+     /**
+      * Create the classpath for loading the driver.
+      */
+     public Path createClasspath() {
+         if (this.classpath == null) {
+             this.classpath = new Path(project);
+         }
+         return this.classpath.createPath();
+     }
+ 
+     /**
+      * Set the classpath for loading the driver using the classpath reference.
+      */
+     public void setClasspathRef(Reference r) {
+         createClasspath().setRefid(r);
+     }
+     
+     /**
+      * Set the name of the sql file to be run.
+      */
+     public void setSrc(File srcFile) {
+         this.srcFile = srcFile;
+     }
+     
+     /**
+      * Set the delimiter.
+      * Added 001122 
+      */
+     public void setDelimiter(String delimiter) {
+         this.delimiter = delimiter;
+     }
+ 
+      /**
+      * Set the delimiter_type
+      * Added 001122 
+      */
+         public void setDelimiter_type(String delimiter_type) {
+         this.delimiter_type = delimiter_type;
+     }
+ 
+ 
+     /**
+      * Set the sql command to execute
+      */
+     public void addText(String sql) {
+         this.sqlCommand += sql;
+     }
+     
+     /**
+      * Set the sql command to execute
+      */
+     public Transaction createTransaction() {
+       Transaction t = new Transaction();
+       transactions.addElement(t);
+       return t;
+     }
+     
+     /**
+      * Set the JDBC driver to be used.
+      */
+     public void setDriver(String driver) {
+         this.driver = driver;
+     }
+     
+     /**
+      * Set the DB connection url.
+      */
+     public void setUrl(String url) {
+         this.url = url;
+     }
+     
+     /**
+      * Set the user name for the DB connection.
+      */
+     public void setUserid(String userId) {
+         this.userId = userId;
+     }
+     
+     /**
+      * Set the password for the DB connection.
+      */
+     public void setPassword(String password) {
+         this.password = password;
+     }
+     
+     /**
+      * Set the autocommit flag for the DB connection.
+      */
+     public void setAutocommit(boolean autocommit) {
+         this.autocommit = autocommit;
+     }
+ 
+     /**
+      * Set the print flag.
+      */
+     public void setPrint(boolean print) {
+         this.print = print;
+     }
+     
+     /**
+      * Set the showheaders flag.
+      */
+     public void setShowheaders(boolean showheaders) {
+         this.showheaders = showheaders;
+     }
+ 
+     /**
+      * Set the output file.
+      */
+     public void setOutput(File output) {
+         this.output = output;
+     }
+ 
+     /**
+      * Set the rdbms required
+      */
+     public void setRdbms(String vendor) {
+         this.rdbms = vendor.toLowerCase();
+     }
+ 
+     /**
+      * Set the version required
+      */
+     public void setVersion(String version) {
+         this.version = version.toLowerCase();
+     }
+ 
+     /**
+      * Set the action to perform onerror
+      */
+     public void setOnerror(OnError action) {
+         this.onError = action.getValue();
+     }
+ 
+     /**
+      * Load the sql file and then execute it
+      */
+         public void execute() throws BuildException {
+         sqlCommand = sqlCommand.trim();
+ 
+         if (srcFile == null && sqlCommand.length() == 0) { 
+             if (transactions.size() == 0) {
+                 throw new BuildException("Source file, transactions or sql statement must
be set!", location);
+             }
+         } else {
+             // Make a transaction group for the outer command
+             Transaction t = createTransaction();
+             t.setSrc(srcFile);
+             t.addText(sqlCommand);
+         }
+ 
+         if (driver == null) {
+             throw new BuildException("Driver attribute must be set!", location);
+         }
+         if (userId == null) {
+             throw new BuildException("User Id attribute must be set!", location);
+         }
+         if (password == null) {
+             throw new BuildException("Password attribute must be set!", location);
+         }
+         if (url == null) {
+             throw new BuildException("Url attribute must be set!", location);
+         }
+         if (srcFile != null && !srcFile.exists()) {
+             throw new BuildException("Source file does not exist!", location);
+         }
+ 
+ 	if ( delimiter_type != null ) {
+ 		
+ 	    if ( !(delimiter_type.compareTo("row") == 0) && !(delimiter_type.compareTo("normal")
== 0))
+             throw new BuildException("delimiter_type is must be either row or normal! Is
now: <" + delimiter_type + ">" , location);
+ 	}//if
+ 
+         Driver driverInstance = null;
+         // Load the driver using the 
+         try {
+             Class dc;
+             if (classpath != null) {
+ 		log("Loading " + driver + " using AntClassLoader with classpath " + classpath, Project.MSG_VERBOSE);
+                 loader = new AntClassLoader(project, classpath, false);
+                 dc = loader.loadClass(driver);
+             }
+             else {
+ 		log("Loading " + driver + " using system loader.", Project.MSG_VERBOSE);
+                 dc = Class.forName(driver);
+             }
+             driverInstance = (Driver) dc.newInstance();
+         }catch(ClassNotFoundException e){
+             throw new BuildException("Class Not Found: JDBC driver " + driver + " could
not be loaded", location);
+         }catch(IllegalAccessException e){
+             throw new BuildException("Illegal Access: JDBC driver " + driver + " could not
be loaded", location);
+         }catch(InstantiationException e) {
+             throw new BuildException("Instantiation Exception: JDBC driver " + driver +
" could not be loaded", location);
+         }
+ 
+         try{
+             log("connecting to " + url, Project.MSG_VERBOSE );
+             Properties info = new Properties();
+             info.put("user", userId);
+             info.put("password", password);
+             conn = driverInstance.connect(url, info);
+ 
+             if (conn == null) {
+                 // Driver doesn't understand the URL
+                 throw new SQLException("No suitable Driver for "+url);
+             }
+ 
+             if (!isValidRdbms(conn)) return;
+ 
+             conn.setAutoCommit(autocommit);
+ 
+             statement = conn.createStatement();
+ 
+             
+             PrintStream out = System.out;
+             try {
+                 if (output != null) {
+         	    log("Opening PrintStream to output file " + output, Project.MSG_VERBOSE);
+                     out = new PrintStream(new BufferedOutputStream(new FileOutputStream(output)));
+                 }
+                         
+                 // Process all transactions
+                 for (Enumeration e = transactions.elements(); 
+                      e.hasMoreElements();) {
+                        
+                     ((Transaction) e.nextElement()).runTransaction(out);
+                     if (!autocommit) {
+                         log("Commiting transaction", Project.MSG_VERBOSE);
+                         conn.commit();
+                     }
+                 }
+             }
+             finally {
+                 if (out != null && out != System.out) {
+                     out.close();
+                 }
+             }
+         } catch(IOException e){
+             if (!autocommit && conn != null && onError.equals("abort"))
{
+                 try {
+                     conn.rollback();
+                 } catch (SQLException ex) {}
+             }
+             throw new BuildException(e, location);
+         } catch(SQLException e){
+             if (!autocommit && conn != null && onError.equals("abort"))
{
+                 try {
+                     conn.rollback();
+                 } catch (SQLException ex) {}
+             }
+             throw new BuildException(e, location);
+         }
+         finally {
+             try {
+                 if (statement != null) {
+                     statement.close();
+                 }
+                 if (conn != null) {
+                     conn.close();
+                 }
+             }
+             catch (SQLException e) {}
+         }
+           
+         log(goodSql + " of " + totalSql + 
+             " SQL statements executed successfully");
+     }
+ 
+     protected void runStatements(Reader reader, PrintStream out) throws SQLException, IOException
{
+         String sql = "";
+         String line = "";
+  
+         BufferedReader in = new BufferedReader(reader);
+  	
+ 
+ 
+ 	if ( delimiter_type.compareTo ("normal") == 0 ) {
+             try{
+                 while ((line=in.readLine()) != null){
+                     if (line.trim().startsWith("//")) continue;
+                     if (line.trim().startsWith("--")) continue;
+ 
+                     sql += " " + line;
+                     sql = sql.trim();
+ 
+                     // SQL defines "--" as a comment to EOL
+                     // and in Oracle it may contain a hint
+                     // so we cannot just remove it, instead we must end it
+                     if (line.indexOf("--") >= 0) sql += "\n";
+ 
+                     if (sql.endsWith(delimiter)){
+                         log("SQL: " + sql, Project.MSG_VERBOSE);
+                         execSQL(sql.substring(0, sql.length()-1), out);
+                         sql = "";
+                     }
+                 }
+  
+                 // Catch any statements not followed by delimiter
+                 if(!sql.equals("")){
+                     execSQL(sql, out);
+                 }
+             }//try
+ 	    catch(SQLException e){
+                 throw e;
+             }//catch
+ 	}//if
+ 
+ 	else if ( delimiter_type.compareTo ("row") == 0 ){
+ 	    try{
+                 while ((line=in.readLine()) != null){
+ 		    
+ 		    if ( line.trim().compareTo(delimiter) == 0 ){
+                         log("SQL: " + sql, Project.MSG_VERBOSE);
+                         execSQL(sql, out);
+                         sql = "";
+                     }
+ 		    else {
+ 			sql += " \n" + line;
+ 	            }//else
+                 }//while
+ 	    }//try
+         
+ 	    catch(SQLException e){
+                 throw e;
+             }
+ 	}//else if
+     }//runStatements
+  
+ 
+ 
+ 
+     /**
+      * Verify if connected to the correct RDBMS
+      **/
+     protected boolean isValidRdbms(Connection conn) {
+         if (rdbms == null && version == null)
+             return true;
+         
+         try {
+             DatabaseMetaData dmd = conn.getMetaData();
+             
+             if (rdbms != null) {
+                 String theVendor = dmd.getDatabaseProductName().toLowerCase();
+                 
+                 log("RDBMS = " + theVendor, Project.MSG_VERBOSE);
+                 if (theVendor == null || theVendor.indexOf(rdbms) < 0) {
+                     log("Not the required RDBMS: "+rdbms, Project.MSG_VERBOSE);
+                     return false;
+                 }
+             }
+             
+             if (version != null) {
+                 String theVersion = dmd.getDatabaseProductVersion().toLowerCase();
+                 
+                 log("Version = " + theVersion, Project.MSG_VERBOSE);
+                 if (theVersion == null || 
+                     !(theVersion.startsWith(version) || 
+                       theVersion.indexOf(" " + version) >= 0)) {
+                     log("Not the required version: \""+ version +"\"", Project.MSG_VERBOSE);
+                     return false;
+                 }
+             }
+         }
+         catch (SQLException e) {
+             // Could not get the required information
+             log("Failed to obtain required RDBMS information", Project.MSG_ERR);
+             return false;
+         }
+         
+         return true;
+     }
+     
+     /**
+      * Exec the sql statement.
+      */
+     protected void execSQL(String sql, PrintStream out) throws SQLException {
+         // Check and ignore empty statements
+         if ("".equals(sql.trim())) return;
+         
+         try {  
+             totalSql++;
+             if (!statement.execute(sql)) {
+                 log(statement.getUpdateCount()+" rows affected", 
+                     Project.MSG_VERBOSE);
+             }
+             
+             if (print) {
+                 printResults(out);
+             }
+             
+             SQLWarning warning = conn.getWarnings();
+             while(warning!=null){
+                 log(warning + " sql warning", Project.MSG_VERBOSE);
+                 warning=warning.getNextWarning();
+             }
+             conn.clearWarnings();
+             goodSql++;
+         }
+         catch (SQLException e) {
+             log("Failed to execute: " + sql, Project.MSG_ERR);
+             if (!onError.equals("continue")) throw e;
+             log(e.toString(), Project.MSG_ERR);
+         }
+     }
+     
+     /**
+      * print any results in the statement.
+      */
+     protected void printResults(PrintStream out) throws java.sql.SQLException {
+         ResultSet rs = null;
+         do {
+             rs = statement.getResultSet();
+             if (rs != null) {
+       	        log("Processing new result set.", Project.MSG_VERBOSE);
+                 ResultSetMetaData md = rs.getMetaData();
+                 int columnCount = md.getColumnCount();
+                 StringBuffer line = new StringBuffer();
+                 if (showheaders) {
+                     for (int col = 1; col < columnCount; col++) {
+                          line.append(md.getColumnName(col));
+                          line.append(",");
+                     }
+                     line.append(md.getColumnName(columnCount));
+                     out.println(line);
+                     line.setLength(0);
+                 }
+                 while (rs.next()) {
+                     boolean first = true;
+                     for (int col = 1; col <= columnCount; col++) {
+                         String columnValue = rs.getString(col);
+                         if (columnValue != null) {
+                             columnValue = columnValue.trim();
+                         }
+                          
+                         if (first) {
+                             first = false;
+                         }
+                         else {
+                             line.append(",");
+                         }
+                         line.append(columnValue);
+                     }
+                     out.println(line);
+                     line.setLength(0);
+                 }
+             }
+         }
+         while (statement.getMoreResults());
+         out.println();
+     }
+ 
+     /**
+      * Enumerated attribute with the values "continue", "stop" and "abort"
+      * for the onerror attribute.  
+      */
+     public static class OnError extends EnumeratedAttribute {
+         public String[] getValues() {
+             return new String[] {"continue", "stop", "abort"};
+         }
+     }
+ 
+     /**
+      * Contains the definition of a new transaction element.
+      * Transactions allow several files or blocks of statements
+      * to be executed using the same JDBC connection and commit
+      * operation in between.
+      */
+     public class Transaction {
+         private File tSrcFile = null;
+         private String tSqlCommand = "";
+ 
+         public void setSrc(File src) {
+             this.tSrcFile = src;
+         }
+ 
+         public void addText(String sql) {
+             this.tSqlCommand += sql;
+         }
+ 
+         private void runTransaction(PrintStream out) throws IOException, SQLException {
+             if (tSqlCommand.length() != 0) {
+                 log("Executing commands", Project.MSG_INFO);
+                 runStatements(new StringReader(tSqlCommand), out);
+             }
+       
+             if (tSrcFile != null) {
+                 log("Executing file: " + tSrcFile.getAbsolutePath(), 
+                     Project.MSG_INFO);
+                 runStatements(new FileReader(tSrcFile), out);
+             }
+         }
+     }
+ 
+ }  

Mime
View raw message