FileDocCategorySizeDatePackage
PLSQLCustomizer.javaAPI DocExample9757Wed Nov 10 18:34:12 GMT 1999None

PLSQLCustomizer

public class PLSQLCustomizer extends sqlj.runtime.profile.util.DataCustomizer implements sqlj.runtime.profile.util.ProfileCustomizer
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.

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 customizeSQLString is the only method that this class overrides, so the rest of the profile data attributes will be unchanged.

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.

Fields Summary
private Connection
m_conn
private sqlj.framework.error.ErrorLog
m_log
private static final String
PARAM_PREFIX
Constructors Summary
Methods Summary
public booleanacceptsConnection(java.sql.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;
    }
private voidaddError(java.lang.String mesg, sqlj.runtime.profile.EntryInfo entry)
Adds an error message to the log using the line number of the passed entry info object.

    m_log.addEntry(new JSError(mesg), entry.getLineNumber(), 0);
private voidaddInfo(java.lang.String mesg, sqlj.runtime.profile.EntryInfo entry)
Adds an informational message to the log using the line number of the passed entry info object.

    m_log.addEntry(new Info(mesg), entry.getLineNumber(), 0);
private voidaddMode(java.lang.StringBuffer proc, int mode)
Appends a string representation of the passed modality to the passed procedure string buffer.

    switch(mode) {
    case TypeInfo.IN:
      proc.append("IN ");
      break;
    case TypeInfo.OUT:
      proc.append("OUT ");
      break;
    case TypeInfo.INOUT:
      proc.append("IN OUT ");
      break;
    }
private voidaddProcBody(java.lang.StringBuffer proc, java.lang.String origText, sqlj.runtime.profile.EntryInfo entry)
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.

    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(";");
    }
private voidaddType(java.lang.StringBuffer proc, int sqlType)
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.

    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;
    }
private java.lang.StringcreateProcedureCall(java.lang.String procName, sqlj.runtime.profile.EntryInfo entry)
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.

    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();
private java.lang.StringcreateProcedureText(java.lang.String procName, sqlj.runtime.profile.EntryInfo entry, java.lang.String origText)
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.


                                                                    
       
				     
  
    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();
public booleancustomize(sqlj.runtime.profile.Profile profile, java.sql.Connection conn, sqlj.framework.error.ErrorLog log)
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

    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;
    }
public java.lang.StringcustomizeSQLString(sqlj.runtime.profile.ProfileData data, sqlj.runtime.profile.EntryInfo entry, int entryNum)
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.

    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;
public static voidmain(java.lang.String[] args)
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.

    String[] newArgs = new String[args.length+1];
    newArgs[0] = "-customizer=PLSQLCustomizer";
    System.arraycopy(args,0,newArgs,1,args.length);
    CustomizerHarness.main(newArgs);