/* Copyright (c) 1997 Oracle Corporation */
import sqlj.runtime.profile.util.DataCustomizer;
import sqlj.runtime.profile.util.CustomizerHarness;
import sqlj.runtime.profile.util.ProfileCustomizer;
import sqlj.runtime.profile.Customization;
import sqlj.runtime.profile.Profile;
import sqlj.runtime.profile.DefaultLoader;
import sqlj.runtime.profile.util.ProfilePrinter;
import sqlj.runtime.profile.ProfileData;
import sqlj.runtime.profile.EntryInfo;
import sqlj.runtime.profile.TypeInfo;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.DriverManager;
import java.io.FileOutputStream;
import java.io.ObjectOutputStream;
import java.io.FileInputStream;
import java.util.Enumeration;
import sqlj.framework.error.ErrorLog;
import sqlj.framework.error.JSError;
import sqlj.framework.error.Info;
/**
The PLSQL customizer is a profile customization utility.
It creates and installs PLSQL stored procedures in the database for
each SQL operation in a profile. It also creates and installs a
customization that will make calls to the created stored procedures rather
than dynamic SQL calls to the original operations.
<p>This class derives from the data customizer class, which is a common
pattern when only the profile data object needs to be customized. Note
that <code>customizeSQLString<code> is the only method that this class
overrides, so the rest of the profile data attributes will be unchanged.
<p>Note: This implementation is meant as an example of how profile
customization might be done. As sample code, it omits many
implementation details and is not meant for use as production-quality
code.
**/
public class PLSQLCustomizer extends DataCustomizer
implements ProfileCustomizer
{
private Connection m_conn = null;
private ErrorLog m_log = null;
public boolean acceptsConnection(Connection conn)
{
if (conn == null) return false;
try {
DatabaseMetaData dbmd = conn.getMetaData();
String dbName = dbmd.getDatabaseProductName();
return dbName.equalsIgnoreCase("ORACLE");
} catch (SQLException e) {
return false;
}
}
/**
Installs a profile customization for the provided profile. If the
profile does not contain any customizable entries, a customization is
not created, and false is returned. PLSQL stored procedures are
created for the non-query entries of the profile. The customization
installed creates connected profiles which call the procedures created
by this routine. Any previous installations of PLSQL customizations
are deregistered.
@return true if the profile was customized, false otherwise
**/
public boolean customize(Profile profile, Connection conn, ErrorLog log)
{
m_conn = conn;
m_log = log;
Enumeration enum = profile.getCustomizations();
while (enum.hasMoreElements()) {
Customization c = (Customization)enum.nextElement();
if (c instanceof PLSQLCustomization) {
profile.deregisterCustomization(c);
}
}
String oldFilename = log.getFileName();
log.setFileName(profile.getProfileData().getSourceFile());
ProfileData customData = customizeData(profile.getProfileData());
log.setFileName(oldFilename);
if (customData != profile.getProfileData()) {
Customization cust = new PLSQLCustomization(customData);
profile.registerCustomization(cust);
return true;
} else {
return false;
}
}
/**
Adds an informational message to the log using the line number of the
passed entry info object.
**/
private void addInfo(String mesg, EntryInfo entry)
{
m_log.addEntry(new Info(mesg), entry.getLineNumber(), 0);
}
/**
Adds an error message to the log using the line number of the
passed entry info object.
**/
private void addError(String mesg, EntryInfo entry)
{
m_log.addEntry(new JSError(mesg), entry.getLineNumber(), 0);
}
/**
Creates a custom version of the sql string which will call a PLSQL
stored procedure instead of executing the original operation. Only
non-query operations are customized. This method first attempts to
create and install a PLSQL stored procedure whose body is the
operation originally stored in the profile entry to customize. If the
procedure is successfully created and installed, the customized SQL
string that calls the new procedure is created and returned.
**/
public String customizeSQLString(ProfileData data,
EntryInfo entry, int entryNum)
{
if (entry.getExecuteType() != EntryInfo.EXECUTE_UPDATE) {
return entry.getSQLString();
}
String procName = data.getProfile().getProfileName() + "_" + entryNum;
String sqlText = entry.getSQLString();
String procText = createProcedureText(procName, entry, sqlText);
try {
addInfo("Creating procedure: " + procText, entry);
PreparedStatement stmt = m_conn.prepareStatement(procText);
stmt.executeUpdate();
stmt.close();
sqlText = createProcedureCall(procName, entry);
addInfo("called using: " + sqlText, entry);
} catch (SQLException e) {
addError(e.toString(), entry);
}
return sqlText;
}
private static final String PARAM_PREFIX = "jSP";
/**
Returns a string whose contents represent a PLSQL stored procedure
whose body is the original SQL operation, as given by "origText". The
number, type and modality of the procedure parameters is determined by
the type info object for each parameter in the passed entry info
object.
**/
private String createProcedureText(String procName, EntryInfo entry,
String origText)
{
StringBuffer proc = new StringBuffer();
proc.append("CREATE OR REPLACE PROCEDURE ");
proc.append(procName);
int numParams = entry.getParamCount();
if (numParams > 0) {
proc.append("(");
for (int i = 1; ; i++) {
proc.append(PARAM_PREFIX);
proc.append(i);
proc.append(" ");
TypeInfo paramInfo = entry.getParamInfo(i);
addMode(proc, paramInfo.getMode());
addType(proc,paramInfo.getSQLType());
if (i == numParams) break;
proc.append(", ");
}
proc.append(")");
}
proc.append(" AS BEGIN ");
addProcBody(proc, origText, entry);
proc.append(" END ");
proc.append(procName);
proc.append(";");
return proc.toString();
}
/**
Appends a string representation of the passed modality to the
passed procedure string buffer.
**/
private void addMode(StringBuffer proc, int mode)
{
switch(mode) {
case TypeInfo.IN:
proc.append("IN ");
break;
case TypeInfo.OUT:
proc.append("OUT ");
break;
case TypeInfo.INOUT:
proc.append("IN OUT ");
break;
}
}
/**
Appends a string representation of the passed sqlType to the
passed procedure string buffer. Note that the passed sqlType
must be mapped from a JDBC type to a type known by Oracle PLSQL.
**/
private void addType(StringBuffer proc, int sqlType)
{
switch(sqlType) {
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
case Types.BIGINT:
case Types.REAL:
case Types.FLOAT:
case Types.DOUBLE:
case Types.DECIMAL:
case Types.NUMERIC:
case Types.BIT:
proc.append("NUMBER");
break;
case Types.CHAR:
proc.append("CHAR");
break;
case Types.VARCHAR:
proc.append("VARCHAR");
break;
case Types.LONGVARCHAR:
proc.append("LONG");
break;
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
proc.append("RAW");
break;
case Types.DATE:
case Types.TIME:
case Types.TIMESTAMP:
proc.append("DATE");
break;
}
}
/**
Appends the procedure body to the passed procedure string buffer. The
procedure body is created by replacing all bind variable references
(?) in the passed original sql text with references to stored
procedure parameters.
**/
private void addProcBody(StringBuffer proc, String origText,
EntryInfo entry)
{
int ind = 0;
int lastInd = 0;
int numParams = entry.getParamCount();
for (int i = 1; i <= numParams; i++) {
ind = entry.getParamInfo(i).getMarkerIndex();
proc.append(origText.substring(lastInd, ind));
proc.append(PARAM_PREFIX);
proc.append(i);
lastInd = ind + 1;
}
proc.append(origText.substring(lastInd));
if (!origText.trim().endsWith(";")) {
proc.append(";");
}
}
/**
Returns a string whose text represents a call to a stored procedure
with the passed name. The number of parameters to the procedure is
found in the passed entry info object.
**/
private String createProcedureCall(String procName, EntryInfo entry)
{
StringBuffer call = new StringBuffer();
call.append("{ CALL ");
call.append(procName);
int numParams = entry.getParamCount();
if (numParams > 0) {
call.append("(");
for (int i = 1; ; i++) {
call.append("?");
if (i == numParams) break;
call.append(",");
}
call.append(")");
}
call.append(" }");
return call.toString();
}
/**
Main entry point for the customization installer.
This routine defers to the main method of the CustomizerHarness
class, passing itself as the hardcoded customizer option.
**/
public static void main(String[] args)
{
String[] newArgs = new String[args.length+1];
newArgs[0] = "-customizer=PLSQLCustomizer";
System.arraycopy(args,0,newArgs,1,args.length);
CustomizerHarness.main(newArgs);
}
}
|