Java: ResultSet on store procedure

Oracle

CREATE OR REPLACE PROCEDURE get_result (
    start_pattern IN VARCHAR2,
    end_pattern IN VARCHAR2,
    result_set OUT SYS_REFCURSOR
) IS
BEGIN
    OPEN result_set FOR
        SELECT
            sheet_id
        FROM checksheet_with_process
        WHERE
            sheet_id >= start_pattern
            AND sheet_id <= end_pattern
        ORDER BY sheet_id DESC;

END;

-- test 
SET SERVEROUTPUT ON;
DECLARE
result_set SYS_REFCURSOR;
sheet_id VARCHAR2(50);
BEGIN
    DBMS_OUTPUT.ENABLE;
    get_sheet_print_list('20100923','20101023',result_set);
    
    LOOP
        FETCH result_set INTO
            sheet_id
        ;
        EXIT WHEN result_set%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(sheet_id);
    END LOOP;
END;

ProcedureResult.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ProcedureResult {

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Connection conn = null;
        CallableStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection("connection string");
            stmt = conn.prepareCall("CALL get_result(?,?,?)");
            stmt.setString(1, "20101001");
            stmt.setString(2, "20101031");
            stmt.registerOutParameter(3, oracle.jdbc.driver.OracleTypes.CURSOR);
            stmt.execute();
            rs = (ResultSet) stmt.getObject(3);

            while (rs != null && rs.next()) {
                System.out.println(rs.getString("sheet_id"));
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null)
                try {
                    rs.close();
                } catch (SQLException e2) {
                    // TODO Auto-generated catch block
                    e2.printStackTrace();
                }
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
    }

}

Default checkbox renderer in JTabel

import java.awt.BorderLayout;
import java.awt.Dimension;
import java.awt.Toolkit;

import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

public class CheckBoxCellinTable extends JFrame {

    JTable table;

    public CheckBoxCellinTable() {

        this.setLayout(new BorderLayout());
        this.initTable();

        setSize(400, 300);
        Dimension sc = Toolkit.getDefaultToolkit().getScreenSize();
        setLocation((sc.width - getWidth()) / 2, (sc.height - getHeight()) / 2);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setVisible(true);
    }

    void initTable() {
        Object[] columnNames = { "First Name", "Last Name", "Married" };
        Object[][] data = { { "Harry", "Smith", true }, { "Sally", "Jones", false }, { "Bob", "Clark", true }, { "Eric", "Burke", true } };

        DefaultTableModel model = new DefaultTableModel(data, columnNames) {
            @Override
            public boolean isCellEditable(int row, int column) {
                return column == 2;
            }

            @Override
            public Class<?> getColumnClass(int columnIndex) {
                return columnIndex == 2 ? Boolean.class : super.getColumnClass(columnIndex);
            }
        };
        table = new JTable(model);

        this.add(new JScrollPane(table), BorderLayout.CENTER);
    }

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        new CheckBoxCellinTable();
    }

}

Cursor with parameter

SET SERVEROUTPUT ON;
DECLARE 
    id_start VARCHAR2(200);
    CURSOR get_old_id(id_pattern VARCHAR2) IS
        SELECT
        column1
        FROM table1
        WHERE column1 LIKE id_pattern
        ORDER BY column1 ASC
    ;

BEGIN
    DBMS_OUTPUT.ENABLE;
    OPEN get_old_id('2010%');
    FETCH get_old_id INTO id_start;
    DBMS_OUTPUT.PUT_LINE(id_start);
    CLOSE get_old_id;
END;

DBMS_OUTPUT in Oracle SQL Developer

SET SERVEROUTPUT ON FORMAT WRAPED;
BEGIN
    DBMS_OUTPUT.ENABLE;
    DBMS_OUTPUT.PUT_LINE('Hello oracle!');
END;

Cumulative sum

SELECT * FROM pg_account;
 
SELECT
    s.pg_id,
    s.pg_name,
    s.pg_entry,
    SUM(NVL(c.pg_entry,0)) cumulative,
    ROUND(s.pg_entry / (SELECT SUM(pg_entry) FROM pg_account)*100, 5) percentage,
    ROUND(SUM(NVL(c.pg_entry,0))/(SELECT SUM(pg_entry) FROM pg_account)*100,5) cumulative_percentage
FROM pg_account s, pg_account c
WHERE s.pg_id > c.pg_id OR (s.pg_id = c.pg_id AND s.pg_entry = c.pg_entry)
GROUP BY s.pg_id, s.pg_name, s.pg_entry
ORDER BY s.pg_id
;
 
SELECT
    s.pg_id,
    s.pg_name,
    s.pg_entry,
    COUNT(c.pg_id) ranking
FROM pg_account s, pg_account c
WHERE s.pg_entry < c.pg_entry OR (s.pg_id = c.pg_id AND s.pg_entry = c.pg_entry)
GROUP BY s.pg_id, s.pg_name, s.pg_entry, s.pg_entry
ORDER BY s.pg_entry DESC
;

PG_ID                  PG_NAME              PG_ENTRY               
---------------------- -------------------- ---------------------- 
1                      Bruce                22                     
2                      James                72                     
3                      Yilin                65                     
4                      Ted                  77                     
5                      Charles              35                     
6                      Sean                 43                     
7                      Paul                 57                     
8                      Ken                  35                     
8 個資料列已選取

PG_ID                  PG_NAME              PG_ENTRY               CUMULATIVE             PERCENTAGE             CUMULATIVE_PERCENTAGE  
---------------------- -------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
1                      Bruce                22                     22                     5.41872                5.41872                
2                      James                72                     94                     17.73399               23.15271               
3                      Yilin                65                     159                    16.00985               39.16256               
4                      Ted                  77                     236                    18.96552               58.12808               
5                      Charles              35                     271                    8.62069                66.74877               
6                      Sean                 43                     314                    10.59113               77.3399                
7                      Paul                 57                     371                    14.03941               91.37931               
8                      Ken                  35                     406                    8.62069                100                    
8 個資料列已選取

PG_ID                  PG_NAME              PG_ENTRY               RANKING                
---------------------- -------------------- ---------------------- ---------------------- 
4                      Ted                  77                     1                      
2                      James                72                     2                      
3                      Yilin                65                     3                      
7                      Paul                 57                     4                      
6                      Sean                 43                     5                      
5                      Charles              35                     6                      
8                      Ken                  35                     6                      
1                      Bruce                22                     8                      
8 個資料列已選取

Change network configuration (windows)

Manual setting

@ECHO OFF
set varAdapterName="Local Area Connection"
set varIP=192.168.1.68
set varMask=255.255.255.0
set varGateway=192.168.1.1
set varDNS1=192.168.1.1
set varDNS2=192.168.1.1

ECHO Setting IP Address and Subnet Mask
netsh int ip set address name = %varAdapterName% source = static addr = %varIP% mask = %varMask%

ECHO Setting Gateway
netsh int ip set address name = %varAdapterName% gateway = %varGateway% gwmetric = 1

ECHO Setting Primary DNS
netsh int ip set dns name = %varAdapterName% source = static addr = %varDNS1%

ECHO Setting Secondary DNS
netsh int ip add dns name = %varAdapterName% addr = %varDNS2%

DHCP

@set varAdapterName="Local Area Connection"

netsh interface ip set address name=%varAdapterName% source=dhcp
netsh interface ip set dns name=%varAdapterName% source=dhcp register=primary
netsh interface ip set wins name=%varAdapterName% source=dhcp

HTA

Introduction to HTML Applications (HTAs)

Select/update table where trigger against, ORA-04091 error occurs

CREATE OR REPLACE
TRIGGER udpate_sheet_processing 
AFTER INSERT ON sheet_item 
FOR EACH ROW


DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;  -- 自治事務 
    v_sheet_id VARCHAR2(50);
    v_processing_entry NUMBER(5);
    CURSOR get_processing_amount IS
        SELECT count(*)+1
        FROM sheet_item
        WHERE sheet_id = v_sheet_id
    ;
    
BEGIN
    v_sheet_id := :NEW.sheet_id;

    OPEN get_processing_amount;
    FETCH get_processing_amount INTO v_processing_entry;
    CLOSE get_processing_amount;

    UPDATE product_checksheet
    SET sheet_processing_entry = v_processing_entry
    WHERE sheet_id = v_sheet_id;
    
    COMMIT;  -- commit for update
END;

MySQL case sensitive/insensitive table names

my.cnf

lower_case_table_names = 1

8.2.2. Identifier Case Sensitivity

MySQL remote access by root

my.cnf

#bind-address = 127.0.0.1   (disable this setting for listening any address)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root_password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;