// Takes and image_id, pulls in the image from cache, and goes about
// encoding it to put it into the database in a transaction. The last
// query in the transaction records the image having been stored.
protected void storeImage(int image_id) throws Exception {
PhotoImage p = new PhotoImage(image_id);
SpyDB pdb = getDB();
Connection db = null;
Statement st = null;
Vector v = p.getImage();
System.err.println(
"Storer: Got image for " + image_id + " " + v.size() + " lines of data to store.");
try {
int i = 0, n = 0;
db = pdb.getConn();
db.setAutoCommit(false);
st = db.createStatement();
BASE64Encoder base64 = new BASE64Encoder();
String data = "";
for (; i < v.size(); i++) {
String tmp = base64.encodeBuffer((byte[]) v.elementAt(i));
tmp = tmp.trim();
if (data.length() < 2048) {
data += tmp + "\n";
} else {
storeQuery(image_id, n, st, data);
data = tmp;
n++;
}
}
// OK, this is sick, but another one right now for the spare.
if (data.length() > 0) {
System.err.println("Storer: Storing spare.");
storeQuery(image_id, n, st, data);
n++;
}
System.err.println("Storer: Stored " + n + " lines of data for " + image_id + ".");
st.executeUpdate(
"update upload_log set stored=datetime(now())\n" + "\twhere photo_id = " + image_id);
db.commit();
// Go ahead and generate a thumbnail.
p.getThumbnail();
} catch (Exception e) {
// If anything happens, roll it back.
if (st != null) {
try {
db.rollback();
} catch (Exception e3) {
// Nothing
}
}
} finally {
if (db != null) {
try {
db.setAutoCommit(true);
} catch (Exception e) {
System.err.println("Error: " + e);
}
}
pdb.freeDBConn();
}
}
public void executeBatch(String sql, Object args[][]) throws SQLException, FileNotFoundException {
PreparedStatement prep = connection.prepareStatement(sql);
int i;
for (Object[] a : args) {
i = 1;
for (Object b : a) {
if (b instanceof String) {
prep.setString(i, (String) b);
}
if (b instanceof Integer) {
prep.setInt(i, (Integer) b);
}
if (b instanceof File) {
FileInputStream fis = new FileInputStream((File) b);
prep.setBinaryStream(i, fis);
}
if (b instanceof FileInputStream) {
prep.setBinaryStream(i, (InputStream) b);
}
if (b instanceof BufferedImage) {
byte[] buffer =
((DataBufferByte) ((BufferedImage) b).getRaster().getDataBuffer()).getData();
InputStream ist = new ByteArrayInputStream(buffer);
prep.setBinaryStream(i, ist);
}
i++;
}
prep.addBatch();
}
connection.setAutoCommit(false);
prep.executeBatch();
connection.setAutoCommit(true);
}
public void doSQL() throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists people;");
stat.executeUpdate("create table people (name, occupation);");
PreparedStatement prep = conn.prepareStatement("insert into people values (?, ?);");
prep.setString(1, "Gandhi");
prep.setString(2, "politics");
prep.addBatch();
prep.setString(1, "Turing");
prep.setString(2, "computers");
prep.addBatch();
prep.setString(1, "Wittgenstein");
prep.setString(2, "smartypants");
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
ResultSet rs = stat.executeQuery("select * from people;");
while (rs.next()) {
System.out.println("name = " + rs.getString("name"));
System.out.println("job = " + rs.getString("occupation"));
}
rs.close();
conn.close();
}
private void createConnection() throws SQLException {
connectionProvider = ConnectionProviderFactory.newConnectionProvider(properties);
connection = connectionProvider.getConnection();
if (!connection.getAutoCommit()) {
connection.commit();
connection.setAutoCommit(true);
}
}
//
// Examine Savepoints.
//
private void vetSavepoint(
Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList)
throws Exception {
conn.setAutoCommit(false);
Savepoint sp = conn.setSavepoint();
vetObject(sp, unsupportedList, notUnderstoodList);
conn.releaseSavepoint(sp);
}
public void open() {
try {
m_conn = DriverManager.getConnection(getJdbcUrl());
} catch (SQLException e) {
throw new LentilConnectionException(e);
}
try {
m_conn.setReadOnly(true);
m_conn.setAutoCommit(false);
} catch (SQLException e) {
throw new IllegalStateException(e);
}
}
/** Create local connection */
public Connection getConn() throws Exception {
Connection c = dataSource.getConnection();
c.setAutoCommit(false);
return c;
}
public static void main(String[] args) {
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
;
Connection conn =
DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.117.110:1521:WFZF", "qzkj", "qzkj");
conn.setAutoCommit(false);
;
BLOB blob = null;
PreparedStatement pstmt =
conn.prepareStatement("insert into sys_file(uuid,content,name) values(?,empty_blob(),?)");
String uuid = Tool.getStringUUid();
pstmt.setString(1, uuid);
pstmt.setString(2, "test.flv");
pstmt.executeUpdate();
pstmt.close();
pstmt = conn.prepareStatement("select content from sys_file where uuid= ? for update");
pstmt.setString(1, uuid);
ResultSet rset = pstmt.executeQuery();
;
if (rset.next()) blob = (BLOB) rset.getBlob(1);
;
String fileName = "test.flv";
File f =
new File(
"C://Documents and Settings//Administrator//桌面//FlvPlayer201002//FlvPlayer201002//"
+ fileName);
FileInputStream fin = new FileInputStream(f);
;
System.out.println("file size = " + fin.available());
pstmt = conn.prepareStatement("update sys_file set content=? where uuid=?");
;
OutputStream out = blob.getBinaryOutputStream();
;
int count = -1, total = 0;
byte[] data = new byte[(int) fin.available()];
fin.read(data);
;
out.write(data);
;
/*
byte[] data = new byte[blob.getBufferSize();]; 另一种实现方法,节省内存
while ((count = fin.read(data);); != -1); {
total += count;
out.write(data, 0, count);;
}
*/
fin.close();
;
out.close();
;
pstmt.setBlob(1, blob);
;
pstmt.setString(2, uuid);
pstmt.executeUpdate();
;
pstmt.close();
;
conn.commit();
;
conn.close();
;
} catch (SQLException e) {
System.err.println(e.getMessage());
;
e.printStackTrace();
} catch (IOException e) {
System.err.println(e.getMessage());
;
}
}
public static void main(
String args[]) { // String args[] ist ein Feld von Stringreferenzen, die beim Programmaufruf
// erhalten werden k�nnen (siehe auch:
// http://www.gailer-net.de/tutorials/java/Notes/chap49B/ch49B_9.html)
boolean fertig = false; // beendet switch-case Anweisung
String name = "dbsys34"; // Variablen f�r Name und Passwort
String passwd = "spion!";
BufferedReader in =
new BufferedReader(new InputStreamReader(System.in)); // zum Einlesen von Tastatur n�tig
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
String line = null; // Variable f�r Benutzereingabe
int wahl = 0; // Variable f�r SwitchCase
// AUSGABE auf die Konsole
System.out.println(""); // Leerzeile ausgeben
System.out.println("--- Dies ist Datenbank-Java-Aufgabe ---");
System.out.println("");
// --- DIALOG mit Benutzer ---
/* try {
System.out.print("Benutzername: ");
name = in.readLine(); // Einlesen der Eingabe "Benutzername"
System.out.print("Passwort: ");
passwd = in.readLine(); // Einlesen der Eingabe "Passwort"
}
// --- Abfangen des m�glichen Fehlers ---
catch (IOException e) {
System.out.println("Fehler beim Lesen der Eingabe: " + e);
System.exit(-1);
}*/
// --- Block f�r Verbindung mit Datenbank ---
try {
DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); // Treiber laden
String url = "jdbc:oracle:thin:@rzdb01.fh-konstanz.de:1521:o9204"; // String f�r DB-Connection
// (vgl.Skript)
conn = DriverManager.getConnection(url, name, passwd); // Verbindung erstellen
conn.setTransactionIsolation(
conn.TRANSACTION_SERIALIZABLE); // Transaction Isolations-Level setzen: Serialisierbarkeit
// als Korrektheitskriterium
conn.setAutoCommit(false); // Kein automatisches Commit nach jedem SQL statement
System.out.println("... zur Datenbank verbunden!"); // Ausgabe f�r den Benutzer
conn.setAutoCommit(true); // Automatisches speichern aktivieren
stmt = conn.createStatement(); // Statement-Objekt erzeugen
while (!fertig) {
System.out.println("\nHallo Benutzer! Was wollen Sie tun?");
System.out.println("1: Kunde einfuegen");
System.out.println("2: Kunde suchen");
System.out.println("3: Ferienwohnung einfuegen");
System.out.println("4: Ferienwohnung suchen");
System.out.println("5: Ferienwohnung buchen");
System.out.println("Zum Beenden bitte \"0\" druecken");
// Einlesen der Benutzerwahl
try {
line = in.readLine();
}
// Fehler beim Einlesen
catch (IOException ioe) {
System.out.println("Fehler beim Lesen der Eingabe: " + ioe);
fertig = true;
continue;
}
// Konvertierung in int-Wert
try {
wahl = Integer.parseInt(line);
} catch (NumberFormatException nfe) {
System.out.println("Falsche Eingabe!");
continue;
}
switch (wahl) {
case 0:
fertig = true;
break;
// {{{ Kunde einfuegen
case 1:
String vorname = null,
nachname = null,
plz = null,
stadt = null,
strasse = null,
hsnr = null,
blz = null,
kontonr = null,
landcode = null;
try {
System.out.println("Bitte Daten des Kunden eingeben.");
System.out.print("Vorname: ");
vorname = in.readLine();
System.out.print("Nachname: ");
nachname = in.readLine();
System.out.print("PLZ (max. 5 Stellen): ");
plz = in.readLine();
System.out.print("Stadt: ");
stadt = in.readLine();
System.out.print("Strasse: ");
strasse = in.readLine();
System.out.print("Hausnr.: ");
hsnr = in.readLine();
System.out.print("BLZ: ");
blz = in.readLine();
System.out.print("Kontonr.: ");
kontonr = in.readLine();
System.out.print("Landkuerzel (de, fr, it, ch): ");
landcode = in.readLine();
} catch (IOException e) {
System.out.println("Fehler beim Lesen der Eingabe: " + e);
System.exit(-1);
}
// Daten einfuegen
stmt.executeUpdate(
"INSERT INTO kunde "
+ "VALUES ( (SELECT MAX(kdnr)+1 FROM kunde), '"
+ vorname
+ "', "
+ " '"
+ nachname
+ "', "
+ " "
+ Integer.parseInt(plz)
+ ", "
+ " '"
+ stadt
+ "', "
+ " '"
+ strasse
+ "', "
+ " '"
+ hsnr
+ "', "
+ " "
+ Integer.parseInt(blz)
+ ", "
+ " "
+ Integer.parseInt(kontonr)
+ ", "
+ " '"
+ landcode
+ "' )");
System.out.println("Der Kunde wurde erfolgreich eingefuegt!");
break;
// }}}
// {{{ Kunde suchen
case 2:
String suchVorname = null, suchNachname = null;
try {
System.out.println("Bitte Namen des zu suchenden Kunden eingeben.");
System.out.print("Vorname: ");
suchVorname = in.readLine();
System.out.print("Nachname: ");
suchNachname = in.readLine();
} catch (IOException e) {
System.out.println("Fehler beim Lesen der Eingabe: " + e);
System.exit(-1);
}
// ... und Abfrage senden
String sSql =
"SELECT * FROM kunde "
+ "WHERE vorname LIKE '%"
+ suchVorname
+ "%' AND "
+ "nachname LIKE '%"
+ suchNachname
+ "%'";
rset = stmt.executeQuery(sSql);
System.out.println("[DEBUG] " + sSql);
System.out.println("Suchergebnisse: ");
while (rset.next()) {
System.out.println(
rset.getInt("kdnr")
+ " "
+ rset.getString("vorname")
+ " "
+ rset.getString("nachname")
+ " "
+ rset.getInt("zip")
+ " "
+ rset.getString("stadt")
+ " "
+ rset.getString("strasse")
+ " "
+ rset.getString("hsnr")
+ " "
+ rset.getInt("blz")
+ " "
+ rset.getInt("kontonr")
+ " "
+ rset.getString("landcode"));
/*
System.out.format("5%d 25%s 25%s 5%d 25%s 25%s 4%s 15%d 15%d 2%s",
rset.getInt("kdnr"),
rset.getString("vorname"),
rset.getString("nachname"),
rset.getInt("zip"),
rset.getString("stadt"),
rset.getString("strasse"),
rset.getString("hsnr"),
rset.getInt("blz"),
rset.getInt("kontonr"),
rset.getString("landcode"));
*/
}
break;
// }}}
// {{{ Wohnung einfuegen
case 3:
String zimmer = null, groesse = null, preis = null, sauna = null, schwbad = null;
plz = null;
landcode = null;
try {
System.out.println("Bitte Daten der Wohnung eingeben.");
System.out.print("Anzahl Zimmer: ");
zimmer = in.readLine();
System.out.print("Groesse [qm]: ");
groesse = in.readLine();
System.out.print("Preis/Tag [EUR]: ");
preis = in.readLine();
System.out.print("Landkuerzel (de, fr, it, ch): ");
landcode = in.readLine();
System.out.print("Sauna? [0 = nein, 1 = ja]: ");
sauna = in.readLine();
System.out.print("Schwimmbad? [0 = nein, 1 = ja]: ");
sauna = in.readLine();
} catch (IOException e) {
System.out.println("Fehler beim Lesen der Eingabe: " + e);
System.exit(-1);
}
// Daten einfuegen
stmt.executeUpdate(
"INSERT INTO ferienwohnung "
+ "VALUES ( (SELECT MAX(fwnr)+1 FROM ferienwohnung), "
+ " "
+ Integer.parseInt(zimmer)
+ ", "
+ " "
+ Integer.parseInt(groesse)
+ ", "
+ " "
+ Integer.parseInt(plz)
+ ", "
+ " "
+ Integer.parseInt(preis)
+ ", "
+ " '"
+ landcode
+ "', "
+ " "
+ Integer.parseInt(sauna)
+ ", "
+ " "
+ Integer.parseInt(schwbad)
+ ")");
System.out.println("Die Wohnung wurde erfolgreich eingefuegt!");
break;
// }}}
// {{{ Wohnung suchen
case 4:
String datumAnreise = null, datumAbreise = null;
zimmer = null;
try {
System.out.println("Bitte Daten der zu suchenden Wohnung eingeben.");
System.out.print("Landkuerzel (de, fr, it, ch): ");
landcode = in.readLine();
System.out.print("Anzahl Zimmer: ");
zimmer = in.readLine();
System.out.print("Anreisedatum [YYYY-MM-DD]: ");
datumAnreise = in.readLine();
System.out.print("Abreisedatum [YYYY-MM-DD]: ");
datumAbreise = in.readLine();
} catch (IOException e) {
System.out.println("Fehler beim Lesen der Eingabe: " + e);
System.exit(-1);
}
// Abfrage senden
sSql =
"SELECT f.* FROM ferienwohnung f "
+ "LEFT OUTER JOIN buchung b "
+ "ON ( b.fwnr = f.fwnr ) "
+ "WHERE "
+ "("
+ " ( b.von < TO_DATE('"
+ datumAnreise
+ "', 'YYYY-MM-DD') "
+ " AND b.bis < TO_DATE('"
+ datumAbreise
+ "', 'YYYY-MM-DD') ) "
+ " OR "
+ " ( b.von > TO_DATE('"
+ datumAnreise
+ "', 'YYYY-MM-DD') "
+ " AND b.bis > TO_DATE('"
+ datumAbreise
+ "', 'YYYY-MM-DD') ) "
+ " OR "
+ " ( b.bunr IS NULL ) "
+ ")"
+ "AND f.anz_zimmer = "
+ Integer.parseInt(zimmer);
rset = stmt.executeQuery(sSql);
System.out.println("[DEBUG] " + sSql);
System.out.println("Suchergebnisse: ");
while (rset.next()) {
System.out.println(
rset.getInt("fwnr")
+ " "
+ rset.getString("anz_zimmer")
+ " "
+ rset.getString("groesse")
+ " "
+ rset.getInt("preis")
+ " "
+ rset.getString("landcode")
+ " "
+ rset.getString("hat_sauna")
+ " "
+ rset.getString("hat_schwb"));
}
break;
// }}}
// {{{ Wohnung buchen
case 5:
String kdnr = null, fwnr = null;
datumAnreise = null;
datumAbreise = null;
try {
System.out.println("Bitte Daten der Buchung eingeben.");
System.out.print("Kunden-Nr.: ");
kdnr = in.readLine();
System.out.print("Ferienwohnung-Nr.: ");
fwnr = in.readLine();
System.out.print("Anreisedatum [YYYY-MM-DD]: ");
datumAnreise = in.readLine();
System.out.print("Abreisedatum [YYYY-MM-DD]: ");
datumAbreise = in.readLine();
} catch (IOException e) {
System.out.println("Fehler beim Lesen der Eingabe: " + e);
System.exit(-1);
}
// Pruefen, ob Kunde/Fw existent?
// Daten einfuegen
stmt.executeUpdate(
"INSERT INTO buchung "
+ "VALUES ( (SELECT MAX(bunr)+1 FROM buchung), "
+ " SYSDATE, "
+ " TO_DATE('"
+ datumAnreise
+ "', 'YYYY-MM-DD'), "
+ " TO_DATE('"
+ datumAbreise
+ "', 'YYYY-MM-DD'), "
+ " "
+ Integer.parseInt(kdnr)
+ ", "
+ " "
+ Integer.parseInt(fwnr)
+ " )");
System.out.println("Die Buchung wurde erfolgreich eingefuegt!");
break;
// }}}
}
}
// Verbindung trennen
stmt.close();
conn.close();
} catch (SQLException se) // Fehler abfangen
{
System.out.println("");
System.out.println(
"SQL Exception occurred while establishing connection to DBS: " + se.getMessage());
System.out.println("- SQL state : " + se.getSQLState());
System.out.println("- Message : " + se.getMessage());
System.out.println("- Vendor code: " + se.getErrorCode());
System.out.println("");
System.out.println("EXITING WITH FAILURE ... !!!");
System.out.println("");
System.exit(-1);
}
System.out.println("");
System.out.println("PROGRAM FINISHED!!!");
}
public void init() throws Exception {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:metaquery", "", "");
con.setAutoCommit(true);
}
static void initJDBC() {
try {
// load the ini file
Properties ini = new Properties();
ini.load(new FileInputStream(System.getProperty("prop")));
// display the values we need
System.out.println("driver=" + ini.getProperty("driver"));
System.out.println("conn=" + ini.getProperty("conn"));
System.out.println("user="******"user"));
System.out.println("password=******");
name = ini.getProperty("name");
// Register jdbcDriver
Class.forName(ini.getProperty("driver"));
// make connection
conn =
DriverManager.getConnection(
ini.getProperty("conn"), ini.getProperty("user"), ini.getProperty("password"));
conn.setAutoCommit(false);
// Create Statement
stmt = conn.createStatement();
distPrepStmt =
conn.prepareStatement(
"INSERT INTO district "
+ " (d_id, d_w_id, d_ytd, d_tax, d_next_o_id, d_name, d_street_1, d_street_2, d_city, d_state, d_zip) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
itemPrepStmt =
conn.prepareStatement(
"INSERT INTO item "
+ " (i_id, i_name, i_price, i_data, i_im_id) "
+ "VALUES (?, ?, ?, ?, ?)");
custPrepStmt =
conn.prepareStatement(
"INSERT INTO customer "
+ " (c_id, c_d_id, c_w_id, "
+ "c_discount, c_credit, c_last, c_first, c_credit_lim, "
+ "c_balance, c_ytd_payment, c_payment_cnt, c_delivery_cnt, "
+ "c_street_1, c_street_2, c_city, c_state, c_zip, "
+ "c_phone, c_since, c_middle, c_data) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
histPrepStmt =
conn.prepareStatement(
"INSERT INTO history "
+ " (h_c_id, h_c_d_id, h_c_w_id, "
+ "h_d_id, h_w_id, "
+ "h_date, h_amount, h_data) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
ordrPrepStmt =
conn.prepareStatement(
"INSERT INTO oorder "
+ " (o_id, o_w_id, o_d_id, o_c_id, "
+ "o_carrier_id, o_ol_cnt, o_all_local, o_entry_d) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
orlnPrepStmt =
conn.prepareStatement(
"INSERT INTO order_line "
+ " (ol_w_id, ol_d_id, ol_o_id, "
+ "ol_number, ol_i_id, ol_delivery_d, "
+ "ol_amount, ol_supply_w_id, ol_quantity, ol_dist_info) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
nworPrepStmt =
conn.prepareStatement(
"INSERT INTO new_order " + " (no_w_id, no_d_id, no_o_id) " + "VALUES (?, ?, ?)");
stckPrepStmt =
conn.prepareStatement(
"INSERT INTO stock "
+ " (s_i_id, s_w_id, s_quantity, s_ytd, s_order_cnt, s_remote_cnt, s_data, "
+ "s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, "
+ "s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
whsePrepStmt =
conn.prepareStatement(
"INSERT INTO warehouse "
+ " (w_id, w_ytd, w_tax, w_name, w_street_1, w_street_2, w_city, w_state, w_zip) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
} catch (SQLException se) {
System.out.println(se.getMessage());
transRollback();
} catch (Exception e) {
e.printStackTrace();
transRollback();
} // end try
} // end initJDBC()
/**
* Saves the templates to the database.
*
* @throws java.sql.SQLException Thrown on sql error.
*/
public void saveToDatabase() throws java.sql.SQLException {
setProgressIndeterminate(true);
setMessage("Saving Templates");
ArrayList templates = getTemplates();
Connection oracleConnection = getDataSource().getConnection();
try {
oracleConnection.setAutoCommit(false);
Statement query = oracleConnection.createStatement();
try {
int templateCount = templates.size();
// First remove any existing entries.
StringBuffer sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_SGNL_FLD");
StringBuffer whereClause = new StringBuffer(" WHERE TMPL_ID IN (");
for (int i = 0; i < templateCount; i++) {
if (i > 0) whereClause.append(", ");
whereClause.append("'");
whereClause.append(((Template) templates.get(i)).getID());
whereClause.append("'");
}
whereClause.append(")");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_MACRO");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_SGNL_REC");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_SGNL_FLD");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TEMPLATE");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ_GRP");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ_GRP_ARCH_REQ");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TEMPLATE (TMPL_ID, TMPL_DESC, EXT_SRC_FILE_NM, EXT_SRC_FILE_MOD_DTE) VALUES (?, ?, ?, ?)");
PreparedStatement templateInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_MACRO (TMPL_ID, MACRO_ID) VALUES (?, ?)");
PreparedStatement macroInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TMPL_SGNL_REC (TMPL_ID, TMPL_SGNL_ID, REC_TYPE_ID, ARCH_IND, ARCH_FREQ, ARCH_TYPE) VALUES (?, ?, ?, ?, ?, ?)");
PreparedStatement signalInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TMPL_SGNL_FLD (TMPL_ID, TMPL_SGNL_ID, FLD_ID, REC_TYPE_ID, VAL) VALUES (?, ?, ?, ?, ?)");
PreparedStatement fieldInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ (TMPL_ID, ARCH_REQ_FILE_NM) VALUES (?, ?)");
PreparedStatement requestInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ_GRP (TMPL_ID, ARCH_REQ_GRP_FILE_NM) VALUES (?, ?)");
PreparedStatement groupInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TMPL_ARCH_REQ_GRP_ARCH_REQ (TMPL_ID, ARCH_REQ_GRP_FILE_NM, ARCH_REQ_FILE_NM) VALUES (?, ?, ?)");
PreparedStatement requestGroupInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("UPDATE ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TMPL_SGNL_REC SET ARCH_IND = ?, ARCH_FREQ = ?, ARCH_TYPE = ?, ARCH_REQ_FILE = ? WHERE TMPL_ID = ? AND TMPL_SGNL_ID = ? AND REC_TYPE_ID = ?");
PreparedStatement signalUpdateStatement =
oracleConnection.prepareStatement(sql.toString());
try {
int progress = 0;
setProgressMaximum(importedFieldCount + importedMacroCount);
setProgressValue(0);
setProgressIndeterminate(false);
for (int templateIndex = 0;
templateIndex < templateCount;
templateIndex++) {
Template currentTemplate = (Template) templates.get(templateIndex);
String currentTemplateID = currentTemplate.getID();
templateInsertStatement.setString(1, currentTemplateID);
String currentDescription = currentTemplate.getDescription();
if (currentDescription == null)
templateInsertStatement.setNull(2, Types.VARCHAR);
else templateInsertStatement.setString(2, currentDescription);
templateInsertStatement.setString(3, currentTemplate.getFileName());
templateInsertStatement.setTimestamp(
4, currentTemplate.getFileModifiedDate());
templateInsertStatement.execute();
// Need to insert macros.
int macroCount = currentTemplate.getMacroCount();
for (int macroIndex = 0; macroIndex < macroCount; macroIndex++) {
macroInsertStatement.setString(1, currentTemplateID);
String currentMacro = currentTemplate.getMacroAt(macroIndex);
macroInsertStatement.setString(2, currentMacro);
macroInsertStatement.execute();
setProgressValue(++progress);
}
int signalCount = currentTemplate.getSignalCount();
for (int signalIndex = 0; signalIndex < signalCount; signalIndex++) {
Signal currentSignal = currentTemplate.getSignalAt(signalIndex);
String currentSignalID = currentSignal.getID();
String currentRecordTypeID =
currentSignal.getType().getRecordType().getID();
signalInsertStatement.setString(1, currentTemplateID);
signalInsertStatement.setString(2, currentSignalID);
signalInsertStatement.setString(3, currentRecordTypeID);
signalInsertStatement.setString(4, currentSignal.getArchiveIndicator());
BigDecimal currentFrequency = currentSignal.getArchiveFrequency();
if (currentFrequency == null)
currentFrequency = new BigDecimal("60"); // 60 default in RDB
signalInsertStatement.setBigDecimal(5, currentFrequency);
String currentType = currentSignal.getArchiveType();
if (currentType == null)
currentType = "Monitor"; // 'Monitor' default in RDB
signalInsertStatement.setString(6, currentType);
signalInsertStatement.execute();
int fieldCount = currentSignal.getFieldCount();
for (int fieldIndex = 0; fieldIndex < fieldCount; fieldIndex++) {
SignalField currentField = currentSignal.getFieldAt(fieldIndex);
fieldInsertStatement.setString(1, currentTemplateID);
fieldInsertStatement.setString(2, currentSignalID);
fieldInsertStatement.setString(3, currentField.getType().getID());
fieldInsertStatement.setString(4, currentRecordTypeID);
fieldInsertStatement.setString(5, currentField.getValue());
fieldInsertStatement.execute();
if (isParseCanceled()) {
oracleConnection.rollback();
return;
}
setProgressValue(++progress);
}
}
// Insert archive requests.
int requestCount = currentTemplate.getArchiveRequestCount();
for (int requestIndex = 0; requestIndex < requestCount; requestIndex++) {
ArchiveRequest currentRequest =
currentTemplate.getArchiveRequestAt(requestIndex);
String currentRequestFileName = currentRequest.getFileName();
requestInsertStatement.setString(1, currentTemplateID);
requestInsertStatement.setString(2, currentRequestFileName);
requestInsertStatement.execute();
signalCount = currentRequest.getSignalCount();
for (int signalIndex = 0; signalIndex < signalCount; signalIndex++) {
Signal currentSignal = currentRequest.getSignalAt(signalIndex);
String currentSignalID = currentSignal.getID();
signalUpdateStatement.setString(
1, currentSignal.getArchiveIndicator());
signalUpdateStatement.setBigDecimal(
2, currentSignal.getArchiveFrequency());
signalUpdateStatement.setString(3, currentSignal.getArchiveType());
signalUpdateStatement.setString(4, currentRequestFileName);
signalUpdateStatement.setString(5, currentTemplateID);
signalUpdateStatement.setString(6, currentSignalID);
signalUpdateStatement.setString(
7, currentSignal.getType().getRecordType().getID());
signalUpdateStatement.execute();
int fieldCount = currentSignal.getFieldCount();
}
}
// Insert archive groups.
int groupCount = currentTemplate.getArchiveGroupCount();
for (int groupIndex = 0; groupIndex < groupCount; groupIndex++) {
ArchiveGroup currentGroup =
currentTemplate.getArchiveGroupAt(groupIndex);
groupInsertStatement.setString(1, currentTemplateID);
String currentGroupFileName = currentGroup.getFileName();
groupInsertStatement.setString(2, currentGroupFileName);
groupInsertStatement.execute();
requestCount = currentGroup.getArchiveRequestCount();
for (int requestIndex = 0;
requestIndex < requestCount;
requestIndex++) {
ArchiveRequest currentRequest =
currentGroup.getArchiveRequestAt(requestIndex);
String currentRequestFileName = currentRequest.getFileName();
requestGroupInsertStatement.setString(1, currentTemplateID);
requestGroupInsertStatement.setString(2, currentGroupFileName);
requestGroupInsertStatement.setString(
3, currentRequest.getFileName());
}
}
}
} finally {
signalUpdateStatement.close();
}
} finally {
requestGroupInsertStatement.close();
}
} finally {
groupInsertStatement.close();
}
} finally {
requestInsertStatement.close();
}
} finally {
fieldInsertStatement.close();
}
} finally {
signalInsertStatement.close();
}
} finally {
macroInsertStatement.close();
}
} finally {
templateInsertStatement.close();
}
} catch (java.sql.SQLException ex) {
oracleConnection.rollback();
throw ex;
} finally {
query.close();
}
if (isParseCanceled()) oracleConnection.rollback();
else oracleConnection.commit();
} finally {
oracleConnection.close();
}
}
