package demo;

/**
* <p>Title: </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) </p>
* <p>Company: </p>
* @author not attributable
* @version 1.0
*/

import joyistar.eip.bs.*;
import joyistar.eip.vo.*;
import java.sql.*;

public class Simple_ResultSet extends BusinessObjectBean {
    String drivers="sun.jdbc.odbc.JdbcOdbcDriver";
    String username="";
    String password="";
    String url="jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=demo.mdb";
    
    public Simple_ResultSet() {
    }
    
    public joyistar.eip.vo.XmlRequest query_XML(joyistar.eip.vo.XmlRequest xmlRequest) throws Exception {
        Connection conn = null;
        String sql = "";
        java.sql.Statement stmt = null;
        ResultSet rs = null;
        ResultSetMetaData  rsmd= null;
        int rowCount;
        int maxRow;
        int recNo;
        try{
            System.setProperty("jdbc.drivers",drivers);
            conn=DriverManager.getConnection(url,username,password);
            maxRow=xmlRequest.MaxRows;
            recNo=xmlRequest.RecNo;
            sql="select * from PRODUCT";
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            rs = stmt.executeQuery(sql);
            rsmd=rs.getMetaData();
            rs.last();
            rowCount=rs.getRow();
            xmlRequest.SetRecordCount(rowCount);
            for(int i=1;i<=rsmd.getColumnCount();i++) {
                if(rsmd.getColumnTypeName(i).equals("STRING")) {
                    xmlRequest.AddField(rsmd.getColumnName(i),rsmd.getColumnTypeName(i),rsmd.getPrecision(i));
                }
                else {
                    xmlRequest.AddField(rsmd.getColumnName(i),rsmd.getColumnTypeName(i));
                }
            }
            for(int i=1;i<=maxRow;i++) {
                if(recNo+i>rowCount) {
                    break;
                }
                else {
                    rs.absolute(recNo+i);
                    xmlRequest.Append();
                    for(int j=1;j<=rsmd.getColumnCount();j++) {
                        xmlRequest.SetValue(rsmd.getColumnName(j), rs.getString(j));
                    }
                }
            }
         } catch(Exception e) {
            xmlRequest.Error=e.getMessage();
        } finally {
            if(rs != null)
            rs.close();
            if(stmt != null)
            stmt.close();
            if(conn != null)
            conn.close();
        }  
        return xmlRequest;  
    }
    

    
    public joyistar.eip.vo.XmlPost update_XML(joyistar.eip.vo.XmlPost xmlPost) throws Exception {
        Connection conn = null;
        java.sql.Statement stmt = null;
        ResultSet rs = null;
        ResultSetMetaData  rsmd  =null;
        String acttype = null;                       //action type
        XmlAction action = null;                     //an action
        String sql = null;
        String where = null;
        try {
            System.setProperty("jdbc.drivers",drivers);
            conn=DriverManager.getConnection(url,username,password);
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            conn.setAutoCommit(false);                        
            for(int i = 0;i<xmlPost.Actions.size();i++) {
                action  = xmlPost.GetAction(i);
                acttype = action.ActionType;
                if(acttype.equals("old")) {  //where acton, then update or delete action after it;
                    where = " where ";
                    where = where+"PRODUCT_ID='"+action.getField("PRODUCT_ID").Value+"'";
                }
                if(acttype.equals("update")){
                    String set = " set ";
                    for(int j=0;j<action.getFieldCount();j++) {
                        if(action.getField(j).Name.equals("PRODUCT_PRICE")) {
                            set = set+action.getField(j).Name+"="+action.getField(j).Value;
                        }
                        else {
                            set = set+action.getField(j).Name+"='"+action.getField(j).Value+"'";
                        }
                        if(j<action.getFieldCount()-1) {
                            set = set+ "," ;
                        }
                    }
                    sql="update product"+set+where;
                    stmt.executeUpdate(sql);
                }
                if(acttype.equals("delete")){
                    where = " where ";
                    where = where+action.getField("PRODUCT_ID").Name+"='"+action.getField("PRODUCT_ID").Value+"'";
                    sql = "delete from product"+where;
                            stmt.executeUpdate(sql);
                }
                if(acttype.equals("insert")){
                String field = new String();
                    String value = new String();
                    for(int j=0;j<action.getFieldCount();j++) {
                        field = field+action.getField(j).Name;
                        if(action.getField(j).Name.equals("PRODUCT_PRICE")) {
                            value = value+action.getField(j).Value;
                        }
                        else {
                            value=value+"'"+action.getField(j).Value+"'";
                        }
                        if(j<action.getFieldCount()-1) {
                            field = field+ "," ;
                            value = value+",";
                        }
                    }
                    sql="insert into product ("+field+") values ("+value+")";
                    stmt.executeUpdate(sql);
                }
            }
            conn.commit();                             
        } catch(Exception e) {
            xmlPost.Error=e.getMessage();
        } finally {
            if(rs != null)
            rs.close();
            if(stmt != null)
            stmt.close();
            if(conn != null)
            conn.close();
        }
        return xmlPost;
    }
    
}
