//
// Examine BLOBs and CLOBs.
//
private void vetLargeObjects(
Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList)
throws Exception {
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE t (id INT PRIMARY KEY, " + "b BLOB(10), c CLOB(10))");
stmt.execute(
"INSERT INTO t (id, b, c) VALUES (1, "
+ "CAST ("
+ TestUtil.stringToHexLiteral("101010001101")
+ "AS BLOB(10)), CAST ('hello' AS CLOB(10)))");
ResultSet rs = stmt.executeQuery("SELECT id, b, c FROM t");
rs.next();
Blob blob = rs.getBlob(2);
Clob clob = rs.getClob(3);
vetObject(blob, unsupportedList, notUnderstoodList);
vetObject(clob, unsupportedList, notUnderstoodList);
stmt.close();
conn.rollback();
}
public void setValueAt(Object value, int row, int col) { // are you editing?
/*prepare the query*/
/*you have to change the query to adapt it to your table*/
if (col == 0) kolon = "english";
if (col == 1) kolon = "turkish";
String s =
"update soz set "
+ kolon
+ " = '"
+ (String) value
+ "' where "
+ kolon
+ " = '"
+ ((String[]) cache.elementAt(row))[col]
+ "'";
System.out.println(s);
/*excecute the query*/
try {
statement.execute(s);
} catch (Exception e) {
System.out.println("Could not updated");
}
((String[]) cache.elementAt(row))[col] = (String) value;
fireTableCellUpdated(row, col); // also update the table
}
// execute and get results
private void execute(Connection conn, String text, Writer writer, boolean commaSeparator)
throws SQLException {
BufferedWriter buffer = new BufferedWriter(writer);
Statement stmt = conn.createStatement();
stmt.execute(text);
ResultSet rs = stmt.getResultSet();
ResultSetMetaData metadata = rs.getMetaData();
int nbCols = metadata.getColumnCount();
String[] labels = new String[nbCols];
int[] colwidths = new int[nbCols];
int[] colpos = new int[nbCols];
int linewidth = 1;
// read each occurrence
try {
while (rs.next()) {
for (int i = 0; i < nbCols; i++) {
Object value = rs.getObject(i + 1);
if (value != null) {
buffer.write(value.toString());
if (commaSeparator) buffer.write(",");
}
}
}
buffer.flush();
rs.close();
} catch (IOException ex) {
if (Debug.isDebug()) ex.printStackTrace();
// ok, exit from the loop
} catch (SQLException ex) {
if (Debug.isDebug()) ex.printStackTrace();
}
}
/**
* Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
String username = request.getParameter("username");
String password = request.getParameter("password");
Statement stmt;
ResultSet rs;
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String connectionUrl = "jdbc:mysql://localhost/myflickr?" + "user=root&password=123456";
con = DriverManager.getConnection(connectionUrl);
if (con != null) {
System.out.println("connected to mysql");
}
} catch (SQLException e) {
System.out.println("SQL Exception: " + e.toString());
} catch (ClassNotFoundException cE) {
System.out.println("Class Not Found Exception: " + cE.toString());
}
try {
stmt = con.createStatement();
System.out.println("SELECT * FROM flickrusers WHERE name='" + username + "'");
rs = stmt.executeQuery("SELECT * FROM flickrusers WHERE name='" + username + "'");
while (rs.next()) {
if (rs.getObject(1).toString().equals(username)) {
out.println("<h1>To username pou epileksate uparxei hdh</h1>");
out.println("<a href=\"project3.html\">parakalw dokimaste kapoio allo.</a>");
stmt.close();
rs.close();
return;
}
}
stmt.close();
rs.close();
stmt = con.createStatement();
if (!stmt.execute("INSERT INTO flickrusers VALUES('" + username + "', '" + password + "')")) {
out.println("<h1>Your registration is completed " + username + "</h1>");
out.println("<a href=\"index.jsp\">go to the login menu</a>");
registerListener.Register(username);
} else {
out.println("<h1>To username pou epileksate uparxei hdh</h1>");
out.println("<a href=\"project3.html\">Register</a>");
}
} catch (SQLException e) {
throw new ServletException("Servlet Could not display records.", e);
}
}
/** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
// TODO Auto-generated method stub
HttpSession session = request.getSession();
request.setCharacterEncoding("UTF-8");
BufferedInputStream fileIn = new BufferedInputStream(request.getInputStream());
String fn = request.getParameter("fileName");
byte[] buf = new byte[1024];
File file = new File("/var/www/uploadres/" + session.getAttribute("username") + fn);
BufferedOutputStream fileOut = new BufferedOutputStream(new FileOutputStream(file));
while (true) {
int bytesIn = fileIn.read(buf, 0, 1024);
if (bytesIn == -1) break;
else fileOut.write(buf, 0, bytesIn);
}
fileOut.flush();
fileOut.close();
System.out.println(file.getAbsolutePath());
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
try {
Connection conn = DriverManager.getConnection(url, user, pwd);
Statement stmt = conn.createStatement();
String sql =
"UPDATE Users SET photo = '"
+ file.getName()
+ "' WHERE username='******'";
stmt.execute(sql);
// PreparedStatement pstmt = conn.prepareStatement("UPDATE Users SET photo = ? WHERE
// username='******'");
// InputStream inp = new BufferedInputStream(new FileInputStream(file));
// pstmt.setBinaryStream(1, inp, (int)file.length());
// pstmt.executeUpdate();
System.out.println("OK");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/** Business logic to execute. */
public VOResponse deleteCharges(ArrayList list, String serverLanguageId, String username)
throws Throwable {
Statement stmt = null;
Connection conn = null;
try {
if (this.conn == null) conn = getConn();
else conn = this.conn;
stmt = conn.createStatement();
ChargeVO vo = null;
for (int i = 0; i < list.size(); i++) {
// logically delete the record in SAL06...
vo = (ChargeVO) list.get(i);
stmt.execute(
"update SAL06_CHARGES set ENABLED='N' where COMPANY_CODE_SYS01='"
+ vo.getCompanyCodeSys01SAL06()
+ "' and CHARGE_CODE='"
+ vo.getChargeCodeSAL06()
+ "'");
}
return new VOResponse(new Boolean(true));
} catch (Throwable ex) {
Logger.error(
username,
this.getClass().getName(),
"executeCommand",
"Error while deleting existing charges",
ex);
try {
if (this.conn == null && conn != null)
// rollback only local connection
conn.rollback();
} catch (Exception ex3) {
}
throw new Exception(ex.getMessage());
} finally {
try {
stmt.close();
} catch (Exception exx) {
}
try {
if (this.conn == null && conn != null) {
// close only local connection
conn.commit();
conn.close();
}
} catch (Exception exx) {
}
}
}
public Variable execute(
ArrayList Parameters, ArrayList ParentTable, Linker MyLinker, boolean function, boolean loop)
throws Exception {
try {
MyModel.incrementIterationCounter(getLine());
if (MyModel.getExceptionRaised() != null) throw (MyModel.getExceptionRaised());
HashMap MyTable = new HashMap();
ParentTable.add(MyTable);
if (!MyModel.getRunAllCode()) {
if (Statements.size() > 0)
while (Comparison.evaluate(ParentTable, MyLinker, function, loop).truthValue()) {
for (int i = 0; i < Statements.size(); i++) {
Statement TempStatement = (Statement) Statements.get(i);
TempStatement.execute(Parameters, ParentTable, MyLinker, function, true);
}
}
} else { // We must be attempting to run all code.
Comparison.evaluate(ParentTable, MyLinker, function, loop);
for (int i = 0; i < Statements.size(); i++) {
Statement TempStatement = (Statement) Statements.get(i);
TempStatement.execute(Parameters, ParentTable, MyLinker, function, loop);
}
}
ParentTable.remove(ParentTable.size() - 1);
} catch (Exception e) {
if (e instanceof ModelReturn) // Pop from the stack if we're here.
ParentTable.remove(ParentTable.size() - 1);
if (e instanceof ModelBreak) return (null);
if (!(e instanceof ModelError))
throw (new ModelError("Runtime error on line " + getLine() + " when running while loop"));
else throw (e);
}
return (null);
}
static void truncateTable(String strTable) {
String DBMS = "";
try {
DatabaseMetaData metaData = conn.getMetaData();
DBMS = metaData.getDatabaseProductName().toLowerCase();
} catch (SQLException e) {
System.out.println("Problem determining database product name: " + e);
}
System.out.println("Truncating '" + strTable + "' ...");
try {
if (DBMS.startsWith("db2")) {
stmt.execute("TRUNCATE TABLE " + strTable + " IMMEDIATE");
} else {
stmt.execute("TRUNCATE TABLE " + strTable);
}
transCommit();
} catch (SQLException se) {
System.out.println(se.getMessage());
transRollback();
}
}
public final LentilCursor read(final String sql) {
final ResultSet results;
try {
final Statement stmt = getConnection().createStatement();
stmt.execute(sql);
results = stmt.getResultSet();
} catch (SQLException e) {
throw new IllegalStateException(e);
}
return new LentilCursor(results);
}
public final int write(final String sql) {
final int count;
try {
final Statement stmt = getConnection().createStatement();
stmt.execute(sql);
count = stmt.getUpdateCount();
stmt.close();
} catch (SQLException e) {
throw new IllegalStateException(e);
}
return count;
}
public String execute() {
HttpSession session = ServletActionContext.getRequest().getSession();
if (photo == null) return "setinfo";
String uploadPath = "/var/www/uploadres";
String photoName = session.getAttribute("username") + this.getPhotoFileName();
File toPhotoFile = new File(new File(uploadPath), photoName);
if (!toPhotoFile.getParentFile().exists()) toPhotoFile.getParentFile().mkdirs();
try {
FileUtils.copyFile(photo, toPhotoFile);
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
try {
Connection conn = DriverManager.getConnection(url, user, pwd);
Statement stmt = conn.createStatement();
String sql =
"UPDATE Users SET photo = '"
+ photoName
+ "' WHERE username='******'";
stmt.execute(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return "setinfo";
}
public static void main(String args[]) throws IOException {
try {
Scanner in = args.length == 0 ? new Scanner(System.in) : new Scanner(Paths.get(args[0]));
try (Connection conn = getConnection()) {
Statement stat = conn.createStatement();
while (true) {
if (args.length == 0) System.out.println("Enter command or EXIT to exit:");
if (!in.hasNextLine()) return;
String line = in.nextLine();
if (line.equalsIgnoreCase("EXIT")) return;
if (line.trim().endsWith(";")) // remove trailing semicolon
{
line = line.trim();
line = line.substring(0, line.length() - 1);
}
try {
boolean isResult = stat.execute(line);
if (isResult) {
ResultSet rs = stat.getResultSet();
showResultSet(rs);
} else {
int updateCount = stat.getUpdateCount();
System.out.println(updateCount + " rows updated");
}
} catch (SQLException ex) {
for (Throwable e : ex) e.printStackTrace();
}
}
}
} catch (SQLException e) {
for (Throwable t : e) t.printStackTrace();
}
}
/**
* Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
String username = request.getParameter("username");
String password = request.getParameter("password");
Statement stmt;
ResultSet rs;
if (username == null || password == null) {
out.println("<h1>Invalid Register Request</h1>");
out.println("<a href=\"register.html\">Register</a>");
return;
}
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String connectionUrl = "jdbc:mysql://localhost/project3?" + "user=root&password=marouli";
con = DriverManager.getConnection(connectionUrl);
if (con != null) {
System.out.println("Ola ok me mysql");
}
} catch (SQLException e) {
System.out.println("SQL Exception: " + e.toString());
} catch (ClassNotFoundException cE) {
System.out.println("Class Not Found Exception: " + cE.toString());
}
try {
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT * FROM users WHERE username='******'");
if (rs.next()) {
out.println("<h1>Username exists</h1>");
out.println("<a href=\"register.html\">Register</a>");
stmt.close();
rs.close();
con.close();
return;
}
stmt.close();
rs.close();
stmt = con.createStatement();
if (!stmt.execute("INSERT INTO users VALUES('" + username + "', '" + password + "')")) {
out.println("<h1>You are now registered " + username + "</h1>");
out.println("<a href=\"index.jsp\">Login</a>");
int i;
for (i = 0; i < listeners.size(); i++) listeners.get(i).UserRegistered(username);
} else {
out.println("<h1>Could not add your username to the db</h1>");
out.println("<a href=\"register.html\">Register</a>");
}
stmt.close();
con.close();
} catch (SQLException e) {
throw new ServletException("Servlet Could not display records.", e);
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// Variable initializations.
HttpSession session = request.getSession();
FileItem image_file = null;
int record_id = 0;
int image_id;
// Check if a record ID has been entered.
if (request.getParameter("recordID") == null || request.getParameter("recordID").equals("")) {
// If no ID has been entered, send message to jsp.
response_message =
"<p><font color=FF0000>No Record ID Detected, Please Enter One.</font></p>";
session.setAttribute("msg", response_message);
response.sendRedirect("UploadImage.jsp");
}
try {
// Parse the HTTP request to get the image stream.
DiskFileUpload fu = new DiskFileUpload();
// Will get multiple image files if that happens and can be accessed through FileItems.
List<FileItem> FileItems = fu.parseRequest(request);
// Connect to the database and create a statement.
conn = getConnected(drivername, dbstring, username, password);
stmt = conn.createStatement();
// Process the uploaded items, assuming only 1 image file uploaded.
Iterator<FileItem> i = FileItems.iterator();
while (i.hasNext()) {
FileItem item = (FileItem) i.next();
// Test if item is a form field and matches recordID.
if (item.isFormField()) {
if (item.getFieldName().equals("recordID")) {
// Covert record id from string to integer.
record_id = Integer.parseInt(item.getString());
String sql = "select count(*) from radiology_record where record_id = " + record_id;
int count = 0;
try {
rset = stmt.executeQuery(sql);
while (rset != null && rset.next()) {
count = (rset.getInt(1));
}
} catch (SQLException e) {
response_message = e.getMessage();
}
// Check if recordID is in the database.
if (count == 0) {
// Invalid recordID, send message to jsp.
response_message =
"<p><font color=FF0000>Record ID Does Not Exist In Database.</font></p>";
session.setAttribute("msg", response_message);
// Close connection.
conn.close();
response.sendRedirect("UploadImage.jsp");
}
}
} else {
image_file = item;
if (image_file.getName().equals("")) {
// No file, send message to jsp.
response_message = "<p><font color=FF0000>No File Selected For Record ID.</font></p>";
session.setAttribute("msg", response_message);
// Close connection.
conn.close();
response.sendRedirect("UploadImage.jsp");
}
}
}
// Get the image stream.
InputStream instream = image_file.getInputStream();
BufferedImage full_image = ImageIO.read(instream);
BufferedImage thumbnail = shrink(full_image, 10);
BufferedImage regular_image = shrink(full_image, 5);
// First, to generate a unique img_id using an SQL sequence.
rset1 = stmt.executeQuery("SELECT image_id_sequence.nextval from dual");
rset1.next();
image_id = rset1.getInt(1);
// Insert an empty blob into the table first. Note that you have to
// use the Oracle specific function empty_blob() to create an empty blob.
stmt.execute(
"INSERT INTO pacs_images VALUES("
+ record_id
+ ","
+ image_id
+ ", empty_blob(), empty_blob(), empty_blob())");
// to retrieve the lob_locator
// Note that you must use "FOR UPDATE" in the select statement
String cmd = "SELECT * FROM pacs_images WHERE image_id = " + image_id + " FOR UPDATE";
rset = stmt.executeQuery(cmd);
rset.next();
BLOB myblobFull = ((OracleResultSet) rset).getBLOB(5);
BLOB myblobThumb = ((OracleResultSet) rset).getBLOB(3);
BLOB myblobRegular = ((OracleResultSet) rset).getBLOB(4);
// Write the full size image to the blob object.
OutputStream fullOutstream = myblobFull.getBinaryOutputStream();
ImageIO.write(full_image, "jpg", fullOutstream);
// Write the thumbnail size image to the blob object.
OutputStream thumbOutstream = myblobThumb.getBinaryOutputStream();
ImageIO.write(thumbnail, "jpg", thumbOutstream);
// Write the regular size image to the blob object.
OutputStream regularOutstream = myblobRegular.getBinaryOutputStream();
ImageIO.write(regular_image, "jpg", regularOutstream);
// Commit the changes to database.
stmt.executeUpdate("commit");
response_message = "<p><font color=00CC00>Upload Successful.</font></p>";
session.setAttribute("msg", response_message);
instream.close();
fullOutstream.close();
thumbOutstream.close();
regularOutstream.close();
// Close connection.
conn.close();
response.sendRedirect("UploadImage.jsp");
instream.close();
fullOutstream.close();
thumbOutstream.close();
regularOutstream.close();
// Close connection.
conn.close();
} catch (Exception ex) {
response_message = ex.getMessage();
}
}
/** Business logic to execute. */
public final Response executeCommand(
Object inputPar,
UserSessionParameters userSessionPars,
HttpServletRequest request,
HttpServletResponse response,
HttpSession userSession,
ServletContext context) {
String serverLanguageId = ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId();
Connection conn = null;
Statement stmt = null;
try {
conn = ConnectionManager.getConnection(context);
// fires the GenericEvent.CONNECTION_CREATED event...
EventsManager.getInstance()
.processEvent(
new GenericEvent(
this,
getRequestName(),
GenericEvent.CONNECTION_CREATED,
(JAIOUserSessionParameters) userSessionPars,
request,
response,
userSession,
context,
conn,
inputPar,
null));
java.util.List list = (ArrayList) inputPar;
HierarItemDiscountVO vo = null;
ResultSet rset = null;
stmt = conn.createStatement();
for (int i = 0; i < list.size(); i++) {
vo = (HierarItemDiscountVO) list.get(i);
vo.setDiscountTypeSAL03(ApplicationConsts.DISCOUNT_CUSTOMER);
// retrieve COMPANY_CODE from progressiveHIE01...
rset =
stmt.executeQuery(
"select COMPANY_CODE_SYS01 from ITM02_ITEM_TYPES where PROGRESSIVE_HIE02 in "
+ "(select PROGRESSIVE_HIE02 from HIE01_LEVELS where PROGRESSIVE="
+ vo.getProgressiveHie01SAL05()
+ ")");
if (rset.next()) vo.setCompanyCodeSys01SAL03(rset.getString(1));
else {
rset.close();
conn.rollback();
return new ErrorResponse("Item hierarchy not found.");
}
rset.close();
DiscountBean.insertDiscount(conn, vo);
stmt.execute(
"insert into SAL05_ITEM_HIERAR_DISCOUNTS(COMPANY_CODE_SYS01,PROGRESSIVE_HIE01,DISCOUNT_CODE_SAL03) "
+ "values('"
+ vo.getCompanyCodeSys01SAL03()
+ "',"
+ vo.getProgressiveHie01SAL05()
+ ",'"
+ vo.getDiscountCodeSAL03()
+ "')");
}
Response answer = new VOListResponse(list, false, list.size());
// fires the GenericEvent.BEFORE_COMMIT event...
EventsManager.getInstance()
.processEvent(
new GenericEvent(
this,
getRequestName(),
GenericEvent.BEFORE_COMMIT,
(JAIOUserSessionParameters) userSessionPars,
request,
response,
userSession,
context,
conn,
inputPar,
answer));
conn.commit();
// fires the GenericEvent.AFTER_COMMIT event...
EventsManager.getInstance()
.processEvent(
new GenericEvent(
this,
getRequestName(),
GenericEvent.AFTER_COMMIT,
(JAIOUserSessionParameters) userSessionPars,
request,
response,
userSession,
context,
conn,
inputPar,
answer));
return answer;
} catch (Throwable ex) {
Logger.error(
userSessionPars.getUsername(),
this.getClass().getName(),
"executeCommand",
"Error while inserting hierarchy item discounts",
ex);
try {
conn.rollback();
} catch (Exception ex3) {
}
return new ErrorResponse(ex.getMessage());
} finally {
try {
stmt.close();
} catch (Exception ex2) {
}
try {
ConnectionManager.releaseConnection(conn, context);
} catch (Exception ex1) {
}
}
}
/**
* 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();
}
}
public static int executeSQL(Connection conn, String strSQL) throws SQLException {
String method = "executeSQL";
int location = 1000;
if (strSQL == null) strSQL = "";
try {
location = 2000;
int numRows = 0;
location = 2100;
String SQL = "";
location = 2150;
strSQL = strSQL.trim();
location = 2200;
int endPosition = strSQL.indexOf(";");
location = 2300;
Statement stmt = conn.createStatement();
location = 2400;
if (endPosition > -1) {
location = 2500;
while (endPosition > -1) {
location = 2600;
SQL = strSQL.substring(0, endPosition);
if (debug) Logger.printMsg("Executing sql: " + SQL);
// if select, execute query else execute update
if ((strSQL.toUpperCase()).startsWith("SELECT")) {
location = 2700;
stmt.execute(SQL);
} else {
location = 2900;
numRows = numRows + stmt.executeUpdate(SQL);
}
location = 3100;
strSQL = strSQL.substring(endPosition + 1).trim();
location = 3200;
endPosition = strSQL.indexOf(";");
}
} else if (strSQL.length() > 0) {
location = 4000;
if (debug) Logger.printMsg("Executing sql: " + SQL);
// if select, execute query else execute update
if ((strSQL.toUpperCase()).startsWith("SELECT")) {
location = 4200;
stmt.execute(strSQL);
// discard results
} else {
location = 4300;
numRows = stmt.executeUpdate(strSQL);
}
}
location = 5000;
return numRows;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
