<%@ page import = "java.io.*" %>
<%@ page import = "java.sql.*" %>
<%@ page import = "javax.activation.MimetypesFileTypeMap" %>
<%@ page import = "com.stx.common.interfaces.DBConnect" %>
 
<%
 
    String errMsg = "";
 
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    OutputStream outstream  = null;
    FileInputStream finstream  = null;
 
    try
    {
        String sId_fnd_lobs = "638484"; // select fnd_lobs_s.nextval from fnd_lobs
        String sId_document_id = "122181826"; // select fnd_documents_s.nextval from dual
        String sId_ATTACHED_DOCUMENT_ID = "122330159"; // select fnd_attached_documents_s.nextval from fnd_attached_documents
 
        String fileName = "20100112_연차사용촉진 공지.XLS"; // DB에 Insert할 파일명 (테스트를 위해 임의로 지정)
        // *** 파일명에 '&' 등 문자가 포함되어 있으면 변경해야 함
        System.out.println("fileName = " + fileName);
 
        // FILE
        File file = new File("C:\\Interstage\\J2EE\\var\\deployment\\ijserver\\ematrix\\apps\\ematrix\\stxcentral\\" + fileName);
        System.out.println("file ok.......");
 
        // DB Connection
        conn = DBConnect.getDBConnection("ERP_APPS");
        System.out.println("db connected.......");
 
        /* 1. fnd_lobs ********************************************************/
 
        // INSERT
        String sSql = "insert into fnd_lobs ";
        sSql += "(FILE_ID, FILE_NAME, FILE_CONTENT_TYPE, UPLOAD_DATE, PROGRAM_NAME, LANGUAGE, ORACLE_CHARSET, FILE_FORMAT) ";
        sSql += "values(" + sId_fnd_lobs + ", '" + fileName + "', '" + new MimetypesFileTypeMap().getContentType(file) + "', ";
        sSql += "sysdate, 'FNDATTCH', 'KO', 'UTF8', 'BINARY')";
        System.out.println("sSql = " + sSql);
        stmt = conn.createStatement();
        stmt.executeUpdate(sSql);
        stmt.close();
        System.out.println("insert ok.......");
 
        // EMPTY_BLOB() 처리
        // : empty_blob()로 초기화되지 않은 경우 초기화 필요...
        sSql = "update fnd_lobs set FILE_DATA = empty_blob() where FILE_ID = " + sId_fnd_lobs + "";
        stmt = conn.createStatement();
        stmt.executeUpdate(sSql);
        stmt.close();
        System.out.println("empty_blob ok.......");
 
        // GET EMPTY BLOB
        Blob emptyBlob  = null;
        sSql = "select FILE_DATA from fnd_lobs where FILE_ID = " + sId_fnd_lobs;
        stmt = conn.createStatement();
        rs = stmt.executeQuery(sSql);
        if (rs.next()) emptyBlob = rs.getBlob(1);
        System.out.println("select ok.......");
 
        // SET BLOB FIELD
        oracle.sql.BLOB blob = (oracle.sql.BLOB)emptyBlob;
        outstream = blob.getBinaryOutputStream();
        int size = blob.getBufferSize();
        finstream = new FileInputStream(file);
 
        byte[] buffer = new byte[size];
        int length = -1;
        while ((length = finstream.read(buffer)) != -1) {
            outstream.write(buffer, 0, length);
        }
 
        if (finstream != null) finstream.close();
        if (outstream != null) outstream.close(); 
 
        /* 2. fnd_documents ***************************************************/
        sSql = "insert into fnd_documents ";
        sSql += "(DOCUMENT_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, ";
        sSql += "LAST_UPDATE_LOGIN, DATATYPE_ID, CATEGORY_ID, SECURITY_TYPE, ";
        sSql += "SECURITY_ID, PUBLISH_FLAG, USAGE_TYPE) ";
        sSql += "values(" + sId_document_id + ", sysdate, 2374, sysdate, 2374, 13820282, 6, 35, 1, 0, 'Y', 'O')";
        System.out.println("sSql = " + sSql);
        stmt = conn.createStatement();
        stmt.executeUpdate(sSql);
        stmt.close();
        System.out.println("insert ok.......");
 
        /* 3. fnd_documents_tl ************************************************/
        sSql = "insert into fnd_documents_tl ";
        sSql += "(DOCUMENT_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, ";
        sSql += "LAST_UPDATE_LOGIN, LANGUAGE, DESCRIPTION, FILE_NAME, MEDIA_ID, SOURCE_LANG) ";
        sSql += "values(" + sId_document_id + ", sysdate, 2374, sysdate, 2374, 13820282, 'KO', '스펙첨부', ";
        sSql += "'" + fileName + "', 638484, 'KO')";
        System.out.println("sSql = " + sSql);
        stmt = conn.createStatement();
        stmt.executeUpdate(sSql);
        stmt.close();
        System.out.println("insert ok.......");
 
        sSql = "insert into fnd_documents_tl ";
        sSql += "(DOCUMENT_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, ";
        sSql += "LAST_UPDATE_LOGIN, LANGUAGE, DESCRIPTION, FILE_NAME, MEDIA_ID, SOURCE_LANG) ";
        sSql += "values(" + sId_document_id + ", sysdate, 2374, sysdate, 2374, 13820282, 'US', '스펙첨부', "; // 위의 Insert와 'US' 부분만 다름
        sSql += "'" + fileName + "', 638484, 'KO')";
        System.out.println("sSql = " + sSql);
        stmt = conn.createStatement();
        stmt.executeUpdate(sSql);
        stmt.close();
        System.out.println("insert ok.......");
 
        /* 4. fnd_attached_documents ******************************************/
        sSql = "insert into fnd_attached_documents ";
        sSql += "(ATTACHED_DOCUMENT_ID, DOCUMENT_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, ";
        sSql += "SEQ_NUM, ENTITY_NAME, PK1_VALUE, AUTOMATICALLY_ADDED_FLAG) ";
        sSql += "values(" + sId_ATTACHED_DOCUMENT_ID + ", " + sId_document_id + ", sysdate, 2374, sysdate, 2374, 13820282, ";
        sSql += "10, 'REQ_HEADERS', '253076', 'N')";
        System.out.println("sSql = " + sSql);
        stmt = conn.createStatement();
        stmt.executeUpdate(sSql);
        //stmt.close();
        System.out.println("insert ok.......");
 
        DBConnect.commitJDBCTransaction(conn);
        System.out.println("success.......");
    }
    catch (Exception e)
    {
        errMsg = e.toString();
        System.out.println(errMsg);
        if (conn != null) DBConnect.rollbackJDBCTransaction(conn);
    }
    finally
    {
        System.out.println("success2.......");
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
        System.out.println("success2-1.......");
        DBConnect.closeConnection(conn);
        System.out.println("success3.......");
    }
 
%>
 
<script language="JavaScript">
 
    <% if (errMsg.equals("")) { %>
        alert("Success....");
    <% } else { %>
        alert('<%=errMsg%>');
    <% } %>
 
</script>