protected static void writeUniqueFieldName(
Writer writer, DatabaseField field, DatasourcePlatform platform) throws IOException {
// EMPLOYEE_EMP_ID
writer.write(field.getTable().getNameDelimited(platform));
writer.write("_");
writer.write(field.getNameDelimited(platform));
}
protected static void writeForAll(Writer writer, DatabaseField field, DatasourcePlatform platform)
throws IOException {
// FORALL i IN EMPLOYEE_EMP_ID_VAR.FIRST..EMPLOYEE_EMP_ID_VAR.LAST
writer.write(dbltab);
writer.write("FORALL i IN ");
writeVar(writer, field, platform);
writer.write(".FIRST..");
writeVar(writer, field, platform);
writer.write(".LAST\n");
}
protected static void writeDeclareTypeAndVar(
Writer writer, DatabaseField field, DatasourcePlatform platform) throws IOException {
// TYPE EMPLOYEE_EMP_ID_TYPE IS TABLE OF EMPLOYEE.EMP_ID%TYPE;
writer.write(tab);
writer.write("TYPE ");
writeType(writer, field, platform);
writer.write(" IS TABLE OF ");
writer.write(field.getQualifiedName());
writer.write("%TYPE;\n");
// EMPLOYEE_EMP_ID_VAR EMP_ID_TYPE;
writer.write(tab);
writeVar(writer, field, platform);
writer.write(" ");
writeType(writer, field, platform);
writer.write(";\n");
}
/** Append the string containing the SQL insert string for the given table. */
protected SQLCall buildCallWithoutReturning(AbstractSession session) {
SQLCall call = new SQLCall();
call.returnNothing();
Writer writer = new CharArrayWriter(200);
try {
writer.write("INSERT ");
if (getHintString() != null) {
writer.write(getHintString());
writer.write(" ");
}
writer.write("INTO ");
writer.write(getTable().getQualifiedNameDelimited(session.getPlatform()));
writer.write(" (");
Vector fieldsForTable = new Vector();
for (Enumeration fieldsEnum = getModifyRow().keys(); fieldsEnum.hasMoreElements(); ) {
DatabaseField field = (DatabaseField) fieldsEnum.nextElement();
if (field.getTable().equals(getTable()) || (!field.hasTableName())) {
fieldsForTable.addElement(field);
}
}
if (fieldsForTable.isEmpty()) {
throw QueryException.objectToInsertIsEmpty(getTable());
}
for (int i = 0; i < fieldsForTable.size(); i++) {
writer.write(
((DatabaseField) fieldsForTable.elementAt(i)).getNameDelimited(session.getPlatform()));
if ((i + 1) < fieldsForTable.size()) {
writer.write(", ");
}
}
writer.write(") VALUES (");
for (int i = 0; i < fieldsForTable.size(); i++) {
DatabaseField field = (DatabaseField) fieldsForTable.elementAt(i);
call.appendModify(writer, field);
if ((i + 1) < fieldsForTable.size()) {
writer.write(", ");
}
}
writer.write(")");
call.setSQLString(writer.toString());
} catch (IOException exception) {
throw ValidationException.fileError(exception);
}
return call;
}
/** Append the string containing the SQL insert string for the given table. */
public DatabaseCall buildCall(AbstractSession session) {
SQLCall call = new SQLCall();
call.returnNothing();
Writer writer = new CharArrayWriter(100);
Vector mainPrimaryKeys = new Vector();
mainPrimaryKeys.addAll((Collection) tablesToPrimaryKeyFields.get(table));
Vector allFields = (Vector) mainPrimaryKeys.clone();
Iterator itDatabaseFieldsToValues = tables_databaseFieldsToValues.values().iterator();
while (itDatabaseFieldsToValues.hasNext()) {
Iterator itDatabaseFields = ((HashMap) itDatabaseFieldsToValues.next()).keySet().iterator();
while (itDatabaseFields.hasNext()) {
allFields.addElement(itDatabaseFields.next());
}
}
try {
// DECLARE
writer.write("DECLARE\n");
for (int i = 0; i < allFields.size(); i++) {
writeDeclareTypeAndVar(
writer, (DatabaseField) allFields.elementAt(i), session.getPlatform());
}
// BEGIN
writer.write("BEGIN\n");
// select t0.emp_id, concat('Even', t0.f_name), t1.salary + 1000 BULK COLLECT into
// EMPLOYEEE_EMP_ID_VAR, EMPLOYEEE_F_NAME_VAR, SALARY_SALARY_VAR from employee t0, salary t1
// where t0.l_name like 'updateEmployeeTestUsingTempTable' and t0.f_name in ('0', '2') and
// t1.salary = 0 and t0.emp_id = t1.emp_id;
String selectStr = selectCall.getSQLString();
int index = selectStr.toUpperCase().indexOf(" FROM ");
String firstPart = selectStr.substring(0, index);
String secondPart = selectStr.substring(index, selectStr.length());
writer.write(tab);
writer.write(firstPart);
writer.write(" BULK COLLECT INTO ");
for (int i = 0; i < allFields.size(); i++) {
writeVar(writer, (DatabaseField) allFields.elementAt(i), session.getPlatform());
if (i < allFields.size() - 1) {
writer.write(", ");
}
}
writer.write(secondPart);
writer.write(";\n");
call.getParameters().addAll(selectCall.getParameters());
call.getParameterTypes().addAll(selectCall.getParameterTypes());
DatabaseField firstMainPrimaryKey = (DatabaseField) mainPrimaryKeys.firstElement();
writer.write(tab);
writer.write("IF ");
writeVar(writer, firstMainPrimaryKey, session.getPlatform());
writer.write(".COUNT > 0 THEN\n");
Iterator itEntries = tables_databaseFieldsToValues.entrySet().iterator();
while (itEntries.hasNext()) {
writeForAll(writer, firstMainPrimaryKey, session.getPlatform());
writer.write(trpltab);
writer.write("UPDATE ");
Map.Entry entry = (Map.Entry) itEntries.next();
DatabaseTable t = (DatabaseTable) entry.getKey();
writer.write(t.getQualifiedNameDelimited(session.getPlatform()));
writer.write(" SET ");
HashMap databaseFieldsToValues = (HashMap) entry.getValue();
int counter = 0;
Iterator itDatabaseFields = databaseFieldsToValues.keySet().iterator();
while (itDatabaseFields.hasNext()) {
counter++;
DatabaseField field = (DatabaseField) itDatabaseFields.next();
writer.write(field.getNameDelimited(session.getPlatform()));
writer.write(" = ");
writeVar(writer, field, session.getPlatform());
writer.write("(i)");
if (counter < databaseFieldsToValues.size()) {
writer.write(", ");
}
}
writer.write(" WHERE ");
Vector tablePrimaryKeys = new Vector();
tablePrimaryKeys.addAll((Collection) tablesToPrimaryKeyFields.get(t));
for (int i = 0; i < mainPrimaryKeys.size(); i++) {
DatabaseField tableField = (DatabaseField) tablePrimaryKeys.elementAt(i);
writer.write(tableField.getNameDelimited(session.getPlatform()));
writer.write(" = ");
DatabaseField mainField = (DatabaseField) mainPrimaryKeys.elementAt(i);
writeVar(writer, mainField, session.getPlatform());
writer.write("(i)");
if (i < mainPrimaryKeys.size() - 1) {
writer.write(" AND ");
} else {
writer.write(";\n");
}
}
}
writer.write(tab);
writer.write("END IF;\n");
writer.write(tab);
DatabaseField outField = new DatabaseField("ROW_COUNT");
outField.setType(Integer.class);
call.appendOut(writer, outField);
writer.write(" := ");
writeVar(writer, firstMainPrimaryKey, session.getPlatform());
writer.write(".COUNT;\n");
writer.write("END;");
call.setSQLString(writer.toString());
} catch (IOException exception) {
throw ValidationException.fileError(exception);
}
return call;
}
protected static void writeVar(Writer writer, DatabaseField field, DatasourcePlatform platform)
throws IOException {
// EMPLOYEE_EMP_ID_VAR
writeUniqueFieldName(writer, field, platform);
writer.write(varSuffix);
}
