019. iReport: CREATE NEW REPORT

We have to read and understand 013. SETTING DATABASE CONNECTION.
This tutorial using iReport 4.1.1.

1. Create New File...
   
2. Set Report Name (example: rptDDA)
   
3. We will see a dialog that we have created it.
   
4. Click Query Button
   
5. Unceklis Automatically Retrieve Fields
   
   
   
6. Write this sql:
   SELECT C.* FROM SCH.CHILD C
   
7. Click Read Fields button. The fields will be shown below.
8. Click OK
9. All of Fields will be shown in Report Inspector tab -> Fields node.
   
10. Select all fields, then drag them to Detail band.
    
11. Fit the Detail height, by double click the bottom line of Detail band.
    
12. Select all object within Detail band and Column Header band.
    
13. Right click them, select Padding and Borders.
    
14. Set the Line width, then OK.
15. Click Preview button.
    

We know, it doesn't look like an interesting report. Remember, these steps are for white-belt.

MISC: SETTING HUAWEI (CE 0682 - E220) MODEM IN UBUNTU 10.04

Setting

1. Plug modem
2. Edit Connection
3. Choose country (ex. Indonesia)
4. Choose I can't Find Provider
5. Set Provider (example: 3)
6. Choose My Plan isnot listed
7. APN = 3gprs
8. Number = *99#
9. User = 3gprs, Password=3gprs
10. Check Connect Auto
11. Check Available to All user
    

To establish connection

1. Plug modem
2. Wait until modem being detected
3. In Terminal, type: sudo eject /dev/sr1
4. Restart Computer (not shutdown)
5. Connection is ready.

I hope it work.

018. JAVADB-DERBY: CREATE PROCEDURE (PART 2)

• Previously, create .jar file. Example:
  
  package procedure;
  
  public class procfortrigger {
   
       public static void procInOut(String prm, long[] result){
          try{
              Connection conn = DriverManager.getConnection("jdbc:default:connection");
              Statement st=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                      ResultSet.CONCUR_READ_ONLY);
  
              String strSQL="SELECT VAL FROM MYSCHEMA.MYTABLE "
                      + "WHERE LOWER(MYFIELD)='" + prm.toLowerCase() + "'";
              ResultSet rs=st.executeQuery(strSQL);
  
              long val=0L;
              long addVal = val + 1;
  
              if(rs.next()) {
                  val=rs.getLong(1);
                  addVal = val + 1;
                  String updVal="UPDATE MYSCHEMA.MYTABLE SET VAL=" + addVal
                      + " WHERE LOWER(MYFIELD)='" + prm.toLowerCase() + "'";
                  conn.prepareStatement(updVal).execute();
  
              } else {
                  String insSeq="INSERT INTO MYSCHEMA.MYTABLE (MYFIELD, VAL) VALUES ('"
                          + prm.toLowerCase() + "', "
                          + addVal + ")";
                  conn.prepareStatement(insSeq).execute();
              }
              rs.close();
              st.close();
              rs=null;
              st=null;
  
              conn.close();
              conn=null;
             
              result[0] = addVal;
             
          }catch (Exception ex){
              result[0]=0;
          }
      }
  
  
  }
  
  Compile to .jar file (example: DDA_derby.jar).
  
  
  
• Create procedure:
  CREATE PROCEDURE APP.PROCINOUT(IN PRM1 VARCHAR(200), OUT RESULT BIGINT)
  PARAMETER STYLE JAVA
  MODIFIES SQL DATA
  LANGUAGE JAVA
  EXTERNAL NAME 'procedure.procfortrigger.procInOut';
  
  GRANT EXECUTE ON PROCEDURE APP.PROCINOUT TO other_user;
  

Test:
    public long intSeq(Connection dnCon, String dnSeqName){
        try{
            CallableStatement cs = dnCon.prepareCall("{call APP.PROCINOUT(?,?)}");
            cs.setString(1, dnSeqName);
            cs.registerOutParameter(2, Types.BIGINT);
            cs.execute();

            long num_ = cs.getLong(2);
            cs.close();
            cs=null;

            return num_;

        }catch (Exception ex){
            System.out.println("err-" + dnSeqName + ": " + ex);
            return 0L;
        }
    }




Tired? Confuse? May be we would better drink a glass of coffee. Just for refreshing...

017. JAVADB-DERBY: CREATE TRIGGER

 Assume: We have read and understood JAVADB-DERBY: CREATE PROCEDURE

• Create trigger after insert:
  CREATE TRIGGER APP.TRG_AFTERINS
  AFTER INSERT ON MYSCHEMA.MYOTHERTABLE
  REFERENCING NEW AS NEW
  FOR EACH ROW
  CALL APP.APROCFORTRIGGER_AFTERINS(CAST (NEW.OTHERFIELD1  AS VARCHAR(20),
       CAST (NEW.OTHERFIELD2  AS VARCHAR(20));
  
  
• Create trigger after update:
  CREATE TRIGGER APP.TRG_AFTERUPD
  AFTER UPDATE ON MYSCHEMA.MYOTHERTABLE
  REFERENCING OLD AS OLD NEW AS NEW
  FOR EACH ROW
  CALL APP.APROCFORTRIGGER_AFTERINS(CAST (NEW.OTHERFIELD1  AS VARCHAR(20),
       CAST (OLD.OTHERFIELD2  AS VARCHAR(20));
  
  
• Create trigger after delete:
  CREATE TRIGGER APP.TRG_AFTERDEL
  AFTER DELETE ON MYSCHEMA.MYTABLE
  REFERENCING OLD AS OLD
  FOR EACH ROW
  DELETE FROM MYSCHEMA.MYOTHERTABLE WHERE OTHERFIELD1=OLD.MYFIELD1 AND OTHERFIELD2=OLD.MYFIELD2;
  
  

Do you really want to try?

016. JAVADB-DERBY: CREATE PROCEDURE (PART 1)

  Trust me, it is still complicated. But, it's cool.

• Previously, create .jar file. Example:
  
  package procedure;
  
  public class procfortrigger {
   
       public static void aprocfortrigger_afterins(String prm1, String prm2){
          String sql="INSERT INTO MYSCHEMA.MYTABLE (MYFIELD1, MYFIELD2) "
                  + "VALUES('" + prm1
                  + "', '" + prm2
                  + "')";
  
          try{
              Connection conn = DriverManager.getConnection("jdbc:default:connection");
              conn.prepareStatement(sql).execute();
  
          }catch (Exception ex){
              System.out.println("err: " + ex);
          }
      }
  }
  
  Compile to .jar file (example: DDA_derby.jar).
  
  
• Register that .jar to Derby:
  Using Derby GUI, run query:
  CALL sqlj.install_jar ('C:/myfolder/DDA_derby.jar', 'APP.DDA_derby', 0);
  
  
• Create classpath:
  CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', 'APP.DDA_derby');
  
  
• Create procedure:
  CREATE PROCEDURE APP.APROCFORTRIGGER_AFTERINS(
          IN PRM1 VARCHAR(10), IN PRM2 CHAR(10))
  PARAMETER STYLE JAVA
  MODIFIES SQL DATA
  LANGUAGE JAVA
  EXTERNAL NAME 'procedure.procfortrigger.aprocfortrigger_afterins';
  

Deleting .jar:
CALL sqlj.remove_jar ('APP.DDA_derby', 0);

Replacing .jar:
CALL sqlj.replace_jar('C:/myfolder/DDA_derby.jar', 'APP.DDA_derby');


Test:
CALL APP.APROCFORTRIGGER_AFTERINS('123', 'DANIANI');



Do you still want to try?