20170219

diff --git a/com/rapide_act/CmnProperty.java b/com/rapide_act/CmnProperty.java
index f5a94d4..14517b0 100644 (file)
--- a/com/rapide_act/CmnProperty.java
+++ b/com/rapide_act/CmnProperty.java
@@ -31,11 +31,11 @@ public class CmnProperty{
        protected String database = null;
        protected static final int DB_TYPE_ORACLE = 0;
        protected static final int DB_TYPE_SQLSERVER = 1;
-       protected static final int DB_TYPE_DB2 = 2;
-       protected static final int DB_TYPE_MYSQL = 3;
-       protected static final int DB_TYPE_POSTGRESQL = 4;
+       protected static final int DB_TYPE_MYSQL = 2;
+//     protected static final int DB_TYPE_DB2 = 3;
+//     protected static final int DB_TYPE_POSTGRESQL = 4;
        protected static final int DB_TYPE_UNKNOWN = -1;
-       protected static final String [] DB_TYPE_NAME = {"ORACLE", "SQLSERVER", "DB2", "MYSQL", "POSTGRESQL"};
+       protected static final String [] DB_TYPE_NAME = {"ORACLE", "SQLSERVER", "MYSQL"};
        protected static final int MASK_PTN_ALL = 0;
        protected static final int MASK_PTN_ALT = 1;
        protected static final int MASK_PTN_EDGE = 2;
@@ -83,7 +83,12 @@ public class CmnProperty{
                "select "
                + " name as table_name "
                + " from sys.tables "
-               + " order by name"
+               + " order by name",
+               "select "
+               + " table_name "
+               + " from information_schema.TABLES "
+               + " where TABLE_TYPE = 'BASE TABLE' and TABLE_SCHEMA = database() "
+               + " order by table_name"
        };
        protected static String [] tkSqlArray = {
                "select "
@@ -114,7 +119,25 @@ public class CmnProperty{
                + "LEFT OUTER JOIN sys.columns AS D "
                + "ON C.object_id = D.object_id "
                + " AND C.column_id = D.column_id "
-               + "order by A.name,C.key_ordinal"
+               + "order by A.name,C.key_ordinal",
+               "SELECT "
+               + " a.TABLE_NAME, "
+               + " b.COLUMN_NAME "
+               + " from "
+               + " ( "
+               + " select "
+               + " TABLE_SCHEMA, "
+               + " TABLE_NAME "
+               + " from "
+               + " information_schema.TABLES "
+               + " where TABLE_TYPE = 'BASE TABLE' and TABLE_SCHEMA = database()"
+               + " ) a "
+               + " left outer join information_schema.KEY_COLUMN_USAGE b "
+               + " on a.TABLE_SCHEMA = b.TABLE_SCHEMA "
+               + "  and a.TABLE_NAME = b.TABLE_NAME "
+               + "  and b.CONSTRAINT_NAME = 'PRIMARY' "
+               + " order by "
+               + "  a.table_name,b.ORDINAL_POSITION"
        };
        
        protected String user = null;

diff --git a/com/rapide_act/CmnUtils.java b/com/rapide_act/CmnUtils.java
index f7bc7e4..76249d6 100644 (file)
--- a/com/rapide_act/CmnUtils.java
+++ b/com/rapide_act/CmnUtils.java
@@ -28,6 +28,7 @@ import java.sql.Timestamp;
 import java.sql.Blob;
 import java.sql.Clob;
 import java.math.BigDecimal;
+import java.math.BigInteger;
 import java.util.regex.Pattern;
 import org.apache.commons.codec.binary.Base64;
 
@@ -80,12 +81,22 @@ public class CmnUtils{
                }
        }
 
+       protected static void deepPrint(String _message) throws Exception{
+               if(System.getProperty("deep")!=null && System.getProperty("deep").toUpperCase().equals("Y"))System.out.println(new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(new Date()) + " [DEBUG] " + _message);
+       }
+
        protected static void debugPrint(String _message) throws Exception{
-               if(System.getProperty("debug")!=null)System.out.println(new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(new Date()) + " [DEBUG] " + _message);
+               if(
+                       (System.getProperty("deep")!=null && System.getProperty("deep").toUpperCase().equals("Y")) || 
+                       (System.getProperty("debug")!=null && System.getProperty("debug").toUpperCase().equals("Y"))
+               )System.out.println(new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(new Date()) + " [DEBUG] " + _message);
        }
 
        protected static void debugPrinting(String _message) throws Exception{
-               if(System.getProperty("debug")!=null)System.out.print(new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(new Date()) + " [DEBUG] " + _message);
+               if(
+                       (System.getProperty("deep")!=null && System.getProperty("deep").toUpperCase().equals("Y")) || 
+                       (System.getProperty("debug")!=null && System.getProperty("debug").toUpperCase().equals("Y"))
+               )System.out.print(new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(new Date()) + " [DEBUG] " + _message);
        }
 
        protected static void infoPrint(String _message) throws Exception{
@@ -290,9 +301,7 @@ public class CmnUtils{
                        _str.toUpperCase().equals("VARCHAR2") ||
                        _str.toUpperCase().equals("NVARCHAR") ||
                        _str.toUpperCase().equals("NVARCHAR2") ||
-                       _str.toUpperCase().equals("MVARCHAR") ||
-                       _str.toUpperCase().equals("BIGINT") ||
-                       _str.toUpperCase().equals("LONG")){
+                       _str.toUpperCase().equals("MVARCHAR")){
                        return true;
                } else {
                        return false;
@@ -308,6 +317,15 @@ public class CmnUtils{
                }
        }
 
+       protected static boolean isColYear(String _str) throws Exception{
+               if(
+               _str.toUpperCase().equals("YEAR")){
+                       return true;
+               } else {
+                       return false;
+               }
+       }
+
        protected static boolean isColTime(String _str) throws Exception{
                if(
                        _str.toUpperCase().equals("TIME")){
@@ -345,6 +363,7 @@ public class CmnUtils{
        protected static boolean isColShort(String _str) throws Exception{
                if(
                        _str.toUpperCase().equals("TINYINT") ||
+                       _str.toUpperCase().equals("TINYINT UNSIGNED") ||
                        _str.toUpperCase().equals("SMALLINT")){
                        return true;
                } else {
@@ -355,6 +374,8 @@ public class CmnUtils{
        protected static boolean isColInt(String _str) throws Exception{
                if(
                        _str.toUpperCase().equals("INTEGER") ||
+                       _str.toUpperCase().equals("SMALLINT UNSIGNED") ||
+                       _str.toUpperCase().equals("MEDIUMINT") ||
                        _str.toUpperCase().equals("INT")){
                        return true;
                } else {
@@ -362,6 +383,19 @@ public class CmnUtils{
                }
        }
 
+       protected static boolean isColLong(String _str) throws Exception{
+               if(
+                       _str.toUpperCase().equals("INTEGER UNSIGNED") ||
+                       _str.toUpperCase().equals("MEDIUMINT UNSIGNED") ||
+                       _str.toUpperCase().equals("BIGINT UNSIGNED") ||
+                       _str.toUpperCase().equals("BIGINT") ||
+                       _str.toUpperCase().equals("LONG")){
+                       return true;
+               } else {
+                       return false;
+               }
+       }
+
        protected static boolean isColFloat(String _str) throws Exception{
                if(
                        _str.toUpperCase().equals("REAL") ||
@@ -385,6 +419,7 @@ public class CmnUtils{
        protected static boolean isColBytes(String _str) throws Exception{
                if(
                        _str.toUpperCase().equals("RAW") ||
+                       _str.toUpperCase().equals("GEOMETRY") ||
                        _str.toUpperCase().equals("BINARY")){
                        return true;
                } else {

diff --git a/com/rapide_act/DataAccessObjects.java b/com/rapide_act/DataAccessObjects.java
index 5ad5d61..ec458c3 100644 (file)
--- a/com/rapide_act/DataAccessObjects.java
+++ b/com/rapide_act/DataAccessObjects.java
@@ -23,6 +23,7 @@ import java.io.PrintWriter;
 import java.util.Date;
 import java.util.ArrayList;
 import java.util.Iterator;
+import java.math.BigInteger;
 import java.math.BigDecimal;
 
 public class DataAccessObjects{
@@ -52,12 +53,14 @@ public class DataAccessObjects{
        private String colString = null;
        private BigDecimal colBigDecimal = null;
        private Integer colInt = null;
+       private Long colLong = null;
        private Short colShort = null;
        private Float colFloat = null;
        private Double colDouble = null;
        private java.sql.Timestamp colTimestamp = null;
        private java.sql.Time colTime = null;
        private java.sql.Date colDate = null;
+       private java.sql.Date colYear = null;
        private byte [] colBytes = null;
        private Blob colBlob = null;
        private Clob colClob = null;
@@ -270,6 +273,7 @@ public class DataAccessObjects{
                                colString = null;
                                for(int i=0;i<ColumnCount;i++){
                                        colString = getColString(rst, i + 1, colTypeName[i], isNullable[i], false);
+                                       CmnUtils.debugPrint("col=" + colString);
                                        if (colString != null) {
                                                alData.add(colString);
                                        } else {
@@ -374,18 +378,20 @@ public class DataAccessObjects{
        }
 
        private String getColString(ResultSet _rst, int _rec_cnt, String _colTypeName, Integer _isNullable, Boolean _colMask) throws Exception{
-               String colString = null;
-               java.sql.Timestamp colTimestamp = null;
-               java.sql.Time colTime = null;
-               java.sql.Date colDate = null;
-               BigDecimal colBigDecimal = null;
-               Integer colInt = null;
-               Short colShort = null;
-               Float colFloat = null;
-               Double colDouble = null;
-               byte [] colBytes = null;
-               Blob colBlob = null;
-               Clob colClob = null;
+               colString = null;
+               colTimestamp = null;
+               colTime = null;
+               colDate = null;
+               colYear = null;
+               colBigDecimal = null;
+               colInt = null;
+               colLong = null;
+               colShort = null;
+               colFloat = null;
+               colDouble = null;
+               colBytes = null;
+               colBlob = null;
+               colClob = null;
                ByteArrayOutputStream baos = null;
                InputStream is = null;
 
@@ -427,6 +433,11 @@ public class DataAccessObjects{
                        if (colDate != null) {
                                colString = colDate.toString();
                        }
+               } else if (CmnUtils.isColYear(_colTypeName)) {
+                       colYear = _rst.getDate(_rec_cnt);
+                       if (colYear != null) {
+                               colString = colYear.toString().substring(0, 4);
+                       }
                } else if (CmnUtils.isColTimestamp(_colTypeName)) {
                        colTimestamp = _rst.getTimestamp(_rec_cnt);
                        if (colTimestamp != null) {
@@ -452,6 +463,11 @@ public class DataAccessObjects{
                        if (colInt != null) {
                                colString = colInt.toString();
                        }
+               } else if (CmnUtils.isColLong(_colTypeName)) {
+                       colLong = _rst.getLong(_rec_cnt);
+                       if (colLong != null) {
+                               colString = colLong.toString();
+                       }
                } else if (CmnUtils.isColFloat(_colTypeName)) {
                        colFloat = _rst.getFloat(_rec_cnt);
                        if (colFloat != null) {
@@ -490,7 +506,6 @@ public class DataAccessObjects{
                                colString = CmnUtils.clobToString(colClob);
                        }
                } else {
-                       CmnUtils.errorPrint("unmatch column type=" + _colTypeName);
                        throw new Exception("unmatch column type=" + _colTypeName);
                }
                return colString;

diff --git a/com/rapide_act/RapideLoader.java b/com/rapide_act/RapideLoader.java
index ef9b889..5f013ea 100644 (file)
--- a/com/rapide_act/RapideLoader.java
+++ b/com/rapide_act/RapideLoader.java
@@ -26,6 +26,7 @@ import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
 import java.sql.Timestamp;
 import java.math.BigDecimal;
+import java.math.BigInteger;
 
 public class RapideLoader{
        private String database = null;
@@ -121,7 +122,7 @@ public class RapideLoader{
                                        StringBuffer sbColumnName = null;
                                        TABLE_LOOP:
                                        for(int i=0;i<alData.size();i++){
-                                               if(tbName.equals(alData.get(i))){
+                                               if(tbName.toUpperCase().equals(alData.get(i).toUpperCase())){
                                                        if(cp.isInclTables && !CmnUtils.isMatch(cp.inclTablesArray,tbName.toUpperCase())){
                                                                CmnUtils.debugPrint("cp.inclTables=" + cp.inclTables);
                                                                CmnUtils.debugPrint("table=" + tbName + "," +CmnUtils.isMatch(cp.inclTablesArray,tbName.toUpperCase()));
@@ -208,9 +209,9 @@ public class RapideLoader{
                                                                                        if(colName[l].equals(colData[j].replaceAll("\"",""))){
                                                                                                if (colIncl[tb_fl_match_count]) {
                                                                                                        if (ins_rec_count == 0){
-                                                                                                               sql_insert += "\"" + colName[l] + "\"";
+                                                                                                               sql_insert += colName[l];
                                                                                                        } else {
-                                                                                                               sql_insert += "," + "\"" + colName[l] + "\"";
+                                                                                                               sql_insert += "," + colName[l];
                                                                                                        }
                                                                                                        tb_col_seq[ins_rec_count]=l;
                                                                                                        fl_col_seq[ins_rec_count]=j;
@@ -305,14 +306,24 @@ public class RapideLoader{
                                                                                for(int j=0;j<ins_rec_count;j++){
                                                                                        if(j==0){
                                                                                                if(cp.isQuotation){
-                                                                                                       colData[fl_col_seq[j]]=colData[fl_col_seq[j]].substring(1,colData[fl_col_seq[j]].length());
+                                                                                                       if(colData[fl_col_seq[j]].length()>1){
+                                                                                                               colData[fl_col_seq[j]]=colData[fl_col_seq[j]].substring(1,colData[fl_col_seq[j]].length());
+                                                                                                       } else {
+                                                                                                               colData[fl_col_seq[j]]=colData[fl_col_seq[j]].substring(1,colData[fl_col_seq[j]].length()).replaceAll("\"","");
+                                                                                                       }
                                                                                                }
                                                                                        }
                                                                                        if(j==ins_rec_count-1){
                                                                                                if(cp.isQuotation){
-                                                                                                       colData[fl_col_seq[j]]=colData[fl_col_seq[j]].substring(0,colData[fl_col_seq[j]].length()-1);
+                                                                                                       if(colData[fl_col_seq[j]].length()>1){
+                                                                                                               colData[fl_col_seq[j]]=colData[fl_col_seq[j]].substring(0,colData[fl_col_seq[j]].length()-1);
+                                                                                                       } else {
+                                                                                                               colData[fl_col_seq[j]]=colData[fl_col_seq[j]].substring(0,colData[fl_col_seq[j]].length()).replaceAll("\"","");
+                                                                                                       }
                                                                                                }
                                                                                        }
+                                                                                       
+                                                                                       CmnUtils.deepPrint("[" + fl_rec_count + "]" + "[" + j + "]" + colName[tb_col_seq[j]].toUpperCase() + "." + colTypeName[tb_col_seq[j]] + "='" + colData[fl_col_seq[j]] + "'");
                                                                                        if (CmnUtils.isColString(colTypeName[tb_col_seq[j]])){
                                                                                                if(cp.isMask){
                                                                                                        if(
@@ -370,6 +381,12 @@ public class RapideLoader{
                                                                                                } else {
                                                                                                        stmt_ins.setDate(j+1,null);
                                                                                                }
+                                                                                       } else if (CmnUtils.isColYear(colTypeName[tb_col_seq[j]])){
+                                                                                               if (!colData[fl_col_seq[j]].equals("")){
+                                                                                                       stmt_ins.setString(j+1,colData[fl_col_seq[j]]);
+                                                                                               } else {
+                                                                                                       stmt_ins.setDate(j+1,null);
+                                                                                               }
                                                                                        } else if (CmnUtils.isColTime(colTypeName[tb_col_seq[j]])){
                                                                                                if (!colData[fl_col_seq[j]].equals("")){
                                                                                                        stmt_ins.setTime(j+1,java.sql.Time.valueOf(colData[fl_col_seq[j]]));
@@ -379,6 +396,7 @@ public class RapideLoader{
                                                                                        } else if (CmnUtils.isColTimestamp(colTypeName[tb_col_seq[j]])){
                                                                                                if (!colData[fl_col_seq[j]].equals("")){
                                                                                                        stmt_ins.setTimestamp(j+1,java.sql.Timestamp.valueOf(colData[fl_col_seq[j]]));
+                                                                                                       //CmnUtils.debugPrint("Timestamp=" + colData[fl_col_seq[j]]);
                                                                                                } else {
                                                                                                        stmt_ins.setTimestamp(j+1,null);
                                                                                                }
@@ -400,6 +418,12 @@ public class RapideLoader{
                                                                                                } else {
                                                                                                        stmt_ins.setNull(j+1,java.sql.Types.INTEGER);
                                                                                                }
+                                                                                       } else if (CmnUtils.isColLong(colTypeName[tb_col_seq[j]])){
+                                                                                               if (!colData[fl_col_seq[j]].equals("")){
+                                                                                                       stmt_ins.setLong(j+1,Long.parseLong(colData[fl_col_seq[j]]));
+                                                                                               } else {
+                                                                                                       stmt_ins.setNull(j+1,java.sql.Types.BIGINT);
+                                                                                               }
                                                                                        } else if (CmnUtils.isColFloat(colTypeName[tb_col_seq[j]])){
                                                                                                if (!colData[fl_col_seq[j]].equals("")){
                                                                                                        stmt_ins.setFloat(j+1,Float.parseFloat(colData[fl_col_seq[j]]));
@@ -450,6 +474,7 @@ public class RapideLoader{
                                                                        CmnUtils.debugPrint("insert record=" + fl_rec_count);
                                                                }
                                                        }
+                                                       CmnUtils.debugPrint("sql_insert=" + sql_insert);
                                                        if(!isLob)stmt_ins.executeBatch();
                                                        dao.commit();
                                                        System.out.println(String.format("%1$10d",tb_rec_count) + " \8ds \83\8d\81[\83h\82³\82ê\82Ü\82µ\82½\81B");