jvanzyl 01/09/26 09:10:24
Added: src/tdk/task/org/apache/tdk SQLExec.java
Log:
- taken from the 1.5 ant sources as the updated sql task allows the
insertion of <filesets> which is exactly what i need for inserting
an arbitrary set of generated sql files keyed off the project name.
i have asked on the ant list if this functionality will be backported
and release in subsequent revisions of 1.4 but i need this now to be
able to support multiple dbs easily. so i will keep this task here
and use it until it is in a released version of ant. can't force users
to install an alpha version of ant just for the <sql> task.
Revision Changes Path
1.1 jakarta-turbine-tdk/src/tdk/task/org/apache/tdk/SQLExec.java
Index: SQLExec.java
===================================================================
/*
* 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", "Ant", 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 [EMAIL PROTECTED]
*
* 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;
package org.apache.tdk.task;
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.
* Comments may be created with REM -- or //.
*
* @author <a href="mailto:[EMAIL PROTECTED]";>Jeff Martin</a>
* @author <A href="mailto:[EMAIL PROTECTED]";>Michael McCallum</A>
* @author <A href="mailto:[EMAIL PROTECTED]";>Tim Stephenson</A>
*/
public class SQLExec extends Task {
static public class DelimiterType extends EnumeratedAttribute {
static public final String NORMAL = "normal";
static public final String ROW = "row";
public String[] getValues() {
return new String[] {NORMAL, ROW};
}
}
private int goodSql = 0, totalSql = 0;
private Path classpath;
private AntClassLoader loader;
private Vector filesets = new Vector();
/**
* 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();
/**
* SQL Statement delimiter
*/
private String delimiter = ";";
/**
* The delimiter type indicating whether the delimiter will
* only be recognized on a line by itself
*/
private String delimiterType = DelimiterType.NORMAL;
/**
* 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";
/**
* Encoding to use when reading SQL statements from a file
*/
private String encoding = null;
/**
* 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 sql command to execute
*/
public void addText(String sql) {
this.sqlCommand += sql;
}
/**
* Adds a set of files (nested fileset attribute).
*/
public void addFileset(FileSet set) {
filesets.addElement(set);
}
/**
* 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 file encoding to use on the sql files read in
*
* @param encoding the encoding to use on the files
*/
public void setEncoding(String encoding) {
this.encoding = encoding;
}
/**
* 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 statement delimiter.
*
* <p>For example, set this to "go" and delimitertype to "ROW" for
* Sybase ASE or MS SQL Server.</p>
*/
public void setDelimiter(String delimiter) {
this.delimiter = delimiter;
}
/**
* Set the Delimiter type for this sql task. The delimiter type takes
* two values - normal and row. Normal means that any occurence of the delimiter
* terminate the SQL command whereas with row, only a line containing just the
* delimiter is recognized as the end of the command.
*/
public void setDelimiterType(DelimiterType delimiterType) {
this.delimiterType = delimiterType.getValue();
}
/**
* 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 && filesets.isEmpty()) {
if (transactions.size() == 0) {
throw new BuildException("Source file or fileset, transactions or
sql statement must be set!", location);
}
} else {
// deal with the filesets
for (int i=0; i<filesets.size(); i++) {
FileSet fs = (FileSet) filesets.elementAt(i);
DirectoryScanner ds = fs.getDirectoryScanner(project);
File srcDir = fs.getDir(project);
String[] srcFiles = ds.getIncludedFiles();
// Make a transaction for each file
for ( int j=0 ; j<srcFiles.length ; j++ ) {
Transaction t = createTransaction();
t.setSrc(new File(srcDir, srcFiles[j]));
}
}
// 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);
}
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);
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);
try{
while ((line=in.readLine()) != null){
line = line.trim();
line = ProjectHelper.replaceProperties(project, line,
project.getProperties());
if (line.startsWith("//")) continue;
if (line.startsWith("--")) continue;
if (line.length() > 2 &&
line.substring(0,3).equalsIgnoreCase("REM")) 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 (delimiterType.equals(DelimiterType.NORMAL) &&
sql.endsWith(delimiter) ||
delimiterType.equals(DelimiterType.ROW) &&
line.equals(delimiter)) {
log("SQL: " + sql, Project.MSG_VERBOSE);
execSQL(sql.substring(0, sql.length() - delimiter.length()),
out);
sql = "";
}
}
// Catch any statements not followed by ;
if(!sql.equals("")){
execSQL(sql, out);
}
}catch(SQLException e){
throw e;
}
}
/**
* 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);
}
else {
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);
Reader reader = (encoding == null) ? new FileReader(tSrcFile)
: new InputStreamReader(new
FileInputStream(tSrcFile), encoding);
runStatements(reader, out);
reader.close();
}
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
