November 21, 2006 by burton.

         I recently had the need to have my Jdev project apply sql packages and code to the server. The first place I looked was the Ant 1.2.6 source code, since ant has a sql task I thought it would be simple copy. While Ant can apply sql, it will tell you if the compile of the package or procedure went wrong. This article will present the class I created to apply sql to the server and track any errors produced. In the grand scheme of things this class will be implemented into our code building system that we are developing in house.

     The area of concern is what database connection to use. The easiest way is to use the connection that your application module is using, however in my case I needed the ability to create a connection to any of our databases so I used the following code:

public boolean getConn(String connectionString, String user,
String password) throws SQLException {
boolean status = false;
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
this.conn =
DriverManager.getConnection(connectionString, user, password);
if (this.conn != null) {
status = true;
} else {
status = false;
}
return status;
}

 

 

The connectionString, user and Password are private variables in the class that are set at runtime.

 

 

     Once we have established a connection, we have to parse the SQL that is passed into the class. This code is based on the Ant 1.2.6 code. I foolishly thought I could just pass the whole ball of sql to the instance and it work itself out, not the case, you have to parse and call in chunks.

 

protected void runStatements(Reader reader) throws SQLException,
IOException {
StringBuffer sql = new StringBuffer();
String line = “”;

BufferedReader in = new BufferedReader(reader);

while ((line = in.readLine()) != null) {

if (packageName == null) {
if (!line.trim().startsWith((”–”)) &&
(line.toUpperCase().contains(”CREATE OR REPLACE”) ||
line.toUpperCase().contains(”CREATE”))) {
setPackageName(findObjectName(line));
}
}

sql.append(”n” + line);
if (line.equals(delimiter)) {
execSQL(sql.substring(0, sql.length() - delimiter.length()));
sql.replace(0, sql.length(), “”);
setPackageName(null);
}
}
// Catch any statements not followed by /
if (!sql.equals(”")) {
execSQL(sql.toString());
}
}

 

 

     The main part of the code that I had to add was the error checking part, after we run the sql to create a package or other code entity, we need to check the status of that package. By looking into the user_errors table, you can find out if there were any compile errors:

 

private Integer checkSQLErrors() {
Integer numOfErrors = 0;
try {
String SQL =
“Select count(*) as errors from user_errors where upper(name) = ‘” +
getPackageName() + “‘”;
estatement = conn.createStatement();
ResultSet myResults = estatement.executeQuery(SQL);
while (myResults.next()) {
numOfErrors = myResults.getInt(”errors”);
}

} catch (SQLException s) {
System.out.println(s.getMessage());
} finally {
try {
if (estatement != null) {
estatement.close();
}
} catch (java.sql.SQLException s2) {
estatement = null;
}
}
return numOfErrors;
}

If there are errors, the code pushes them into a string array for the user to do with as they please. For my purposes, the errors are attached to the original file for later analysis. This code might still be rough around the edges, as it is still a work in progress. But duing my initial testing it works great for applying sql code to a box. Below is the full source text and the test code to run it.

 

 

package yourcompany.yourapp.model.custom.spray.tools;
import lmit.wots.model.custom.spray.tools.inteferaces.toolsInterface;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.Iterator;
public class buildSQL {
private Connection conn = null;
private Statement statement = null;
private Statement estatement = null;
private String delimiter = “/”;
private String fileName = null;
private File sourceFile = null;
private String packageName = null;
private Integer errorCount = 0;
private String connectionString = null;
private String passWord = null;
private String userName = null;
private ArrayList Errors = new ArrayList();
public buildSQL() {

}

public ArrayList getErrors() {
return this.Errors;
}

public Iterator ErrorIterator() {
return this.Errors.iterator();
}

private void addError(String ErrMessage) {
Errors.add(ErrMessage);
}

public void setConnectionString(String myConn, String myName,
String myPass) {
this.connectionString = myConn;
this.userName = myName;
this.passWord = myPass;
}

private void incErrorCount(Integer addTo) {
if (addTo != null || addTo > 0)
errorCount = errorCount + addTo;
}

private void incErrorCount() {
errorCount = errorCount + 1;
}
public Integer ErrorCount() {
return errorCount;
}

public boolean getConn(String connectionString, String user,
String password) throws SQLException {
boolean status = false;
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
this.conn =
DriverManager.getConnection(connectionString, user, password);
if (this.conn != null) {
status = true;
} else {
status = false;
}
return status;
}
/**
* Add a SQL transaction to execute
*/
public void setFileName(String NameFile) {
this.fileName = NameFile;
}

public String getFileName() {
return this.fileName;
}

public void FileName(String _fileName) {
this.fileName = _fileName;
}
public String FileName(){
return this.fileName;
}
public void setPackageName(String pName) {
this.packageName = pName;
}

public String getPackageName() {
return this.packageName;
}

private Integer checkSQLErrors() {
Integer numOfErrors = 0;
try {
String SQL =
“Select count(*) as errors from user_errors where upper(name) = ‘” +
getPackageName() + “‘”;
estatement = conn.createStatement();
ResultSet myResults = estatement.executeQuery(SQL);
while (myResults.next()) {
numOfErrors = myResults.getInt(”errors”);
}

} catch (SQLException s) {
System.out.println(s.getMessage());
} finally {
try {
if (estatement != null) {
estatement.close();
}
} catch (java.sql.SQLException s2) {
estatement = null;
}
}
return numOfErrors;
}

public void getSQLErrors() {
Statement st = null;
String eName = null;
String eType = null;
String eSeq = null;
String eLine = null;
String ePos = null;
String eText = null;
String errText = “”;
try {
String SQL =
“Select * from user_errors where upper(name) = ‘” + getPackageName() +
“‘ order by SEQUENCE”;
statement = this.conn.createStatement();
ResultSet myResults = statement.executeQuery(SQL);
while (myResults.next()) {
errText = “”;
eName = myResults.getString(”NAME”);
eType = myResults.getString(”TYPE”);
eSeq = myResults.getString(”SEQUENCE”);
eLine = myResults.getString(”LINE”);
ePos = myResults.getString(”POSITION”);
eText = myResults.getString(”TEXT”);

errText = errText.concat(eType + “->” + eName + “n”);
errText =
errText.concat(”Seq->” + eSeq + ” Line->” + eLine + ” Position->” +
ePos + “n”);
errText = errText.concat(eText + “nn”);
addError(errText);
}

} catch (SQLException s) {
System.out.println(s.getMessage());
} finally {
try {
if (st != null) {
st.close();
}
} catch (java.sql.SQLException s2) {
st = null;
}
}
}

protected String findObjectName(String SQL) {
String objectName = null;
String token = SQL;
if (token.toUpperCase().contains(”CREATE OR REPLACE”) ||
token.toUpperCase().contains(”CREATE”)) {
objectName = token.toUpperCase();
objectName = objectName.replace(”CREATE OR REPLACE”, “”);
objectName = objectName.replace(”CREATE”, “”);
objectName = objectName.replace(”PACKAGE BODY”, “”);
objectName = objectName.replace(”PACKAGE”, “”);
objectName = objectName.replace(”FUNCTION”, “”);
objectName = objectName.replace(”PROCEDURE”, “”);
objectName = objectName.replace(”TABLE”, “”);
objectName = objectName.replace(”VIEW”, “”);
objectName = objectName.replace(”SEQUENCE”, “”);
objectName = objectName.replace(”TRIGGER”, “”);
objectName = objectName.replace(”INDEX”, “”);
objectName = objectName.replace(”IS”, “”);
objectName = objectName.trim();
}
return objectName;
}
/**
* read in lines and execute them
*/
protected void runStatements(Reader reader) throws SQLException,
IOException {
StringBuffer sql = new StringBuffer();
String line = “”;

BufferedReader in = new BufferedReader(reader);

while ((line = in.readLine()) != null) {

if (packageName == null) {
if (!line.trim().startsWith((”–”)) &&
(line.toUpperCase().contains(”CREATE OR REPLACE”) ||
line.toUpperCase().contains(”CREATE”))) {
setPackageName(findObjectName(line));
}
}

sql.append(”n” + line);
if (line.equals(delimiter)) {
execSQL(sql.substring(0, sql.length() - delimiter.length()));
sql.replace(0, sql.length(), “”);
setPackageName(null);
}
}
// Catch any statements not followed by /
if (!sql.equals(”")) {
execSQL(sql.toString());
}
}

/**
* Exec the sql statement.
*/
protected void execSQL(String sql) throws SQLException {
// Check and ignore empty statements
if (”".equals(sql.trim())) {
return;
}
System.out.println(”Compiling ->” + getPackageName());
ResultSet resultSet = null;
try {

boolean ret;

ret = statement.execute(sql);
SQLWarning warning = conn.getWarnings();
while (warning != null) {
System.out.println(warning + ” sql warning”);
warning = warning.getNextWarning();
}
conn.clearWarnings();
incErrorCount(checkSQLErrors());
if (checkSQLErrors() > 0) {
getSQLErrors();
System.out.println((”Failed to Compile: “));
}
} catch (SQLException e) {
System.out.println(”Failed to execute: ” + sql);
System.out.println(e.getMessage());
} finally {
if (resultSet != null) {
resultSet.close();
}
}
}

private void runSQL() throws IOException, SQLException {
if (sourceFile != null) {
System.out.println(”Executing file: ” +
sourceFile.getAbsolutePath());
Reader reader = new FileReader(sourceFile);
try {
runStatements(reader);
} finally {
reader.close();
}
}
}
public void build() {
try {
File dir = new File(fileName);
sourceFile = dir;
} catch (Exception e) {
// System.out.println(e.getMessage());
}

boolean connected;
try {
connected = getConn(connectionString, userName, passWord);
statement = conn.createStatement();
statement.setEscapeProcessing(true);

runSQL();

} catch (SQLException e) {
} catch (IOException e) {
System.out.println(”File does not Exist”);
incErrorCount();
}
}

}

The test code to give it a whirl:

package yourCompany.yourApp.model.custom.spray.tools.test;

import java.util.ArrayList;
import java.util.Iterator;

import lmit.wots.model.custom.spray.tools.buildSQL;

public class testBuildSQL {
public testBuildSQL() {
}

public static void main(String[] args) {
testBuildSQL testBuildSQL = new testBuildSQL();

ArrayList myFiles = new ArrayList();

buildSQL testCase = new buildSQL();
testCase.setConnectionString(”jdbc:oracle:thin:@server:PORT:SID”, “username”, “password”);
testCase.setFileName(”C:/tbuild/spec/naf_int_elements.pks”);

myFiles.add(testCase);

buildSQL testCase2 = new buildSQL();
testCase2.setConnectionString(”jdbc:oracle:thin:@server:PORT:SID”,
“username”, “password”);
testCase2.setFileName(”C:/tbuild/body/civ_naf_int_ar_out_pkg.pkb”);

myFiles.add(testCase2);

buildSQL currFile;
Iterator c = myFiles.iterator();
while (c.hasNext()) {
currFile = (buildSQL)c.next();
currFile.build();
if (currFile.ErrorCount() > 0) {
Iterator i = currFile.ErrorIterator();
while (i.hasNext()) {
System.out.println(i.next());
}
} else {
System.out.println(”No errors!”);
}
}
}
}

I welcome any comment, or even better improvements to the above code.