1 33 package smallsql.junit; 34 35 import junit.framework.*; 36 import java.sql.*; 37 import java.math.*; 38 39 public class TestDataTypes extends BasicTestCase{ 40 41 static final String [] DATATYPES = { "varchar(100)", 42 "varchar2(130)", "nvarchar(137)", "nvarchar2(137)", "sysname", 43 "char(100)", "CHARACTER(99)", 44 "nchar(80)", 45 "int", "smallint", "tinyint", "bigint", "byte", 46 "real", "float", "double", 47 "bit", "Boolean", 48 "binary( 125 )", "varbinary(57)", "raw(88)", 49 "java_object", "sql_variant", 50 "image", "LONGvarbinary", "long raw", 51 "blob", "clob","nclob", 52 "text", "ntext", "LongVarchar", "long", 53 "time", "date", "datetime", "timestamp", "SMALLDATETIME", 54 "UNIQUEIDENTIFIER", 55 "numeric(28,4)", "decimal(29,4)","number(29,4)", "varnum(29,4)", 56 "COUNTER", 57 "money", "smallmoney"}; 58 59 private static final String table = "table_datatypes"; 60 61 private String datatype; 62 63 TestDataTypes( String datatype ){ 64 super( datatype ); 65 this.datatype = datatype; 66 } 67 68 public void tearDown(){ 69 try{ 70 Connection con = AllTests.getConnection(); 71 Statement st = con.createStatement(); 72 st.execute("drop table " + table); 73 st.close(); 74 }catch(Throwable e){ 75 } 77 } 78 79 public void setUp(){ 80 tearDown(); 81 } 82 83 public void runTest() throws Throwable { 84 Connection con = AllTests.getConnection(); 85 Statement st = con.createStatement(); 86 st.execute("Create Table " + table +"(abc " + datatype + ")"); 87 String name = "abc"; 88 89 Object [] values = null; 90 String quote = ""; 91 String escape1 = ""; 92 String escape2 = ""; 93 boolean needTrim = false; 94 95 ResultSet rs = st.executeQuery("SELECT * From " + table); 96 ResultSetMetaData md = rs.getMetaData(); 97 switch(md.getColumnType(1)){ 98 case Types.CHAR: 99 needTrim = true; 100 case Types.VARCHAR: 101 case Types.LONGVARCHAR: 102 case Types.CLOB: 103 values = new Object []{null,"qwert", "asdfg", "hjhjhj", "1234567890 qwertzuiop 1234567890 asdfghjklö 1234567890 yxcvbnm,.- 1234567890 "}; 104 quote = "\'"; 105 break; 106 case Types.BIGINT: 107 values = new Object []{null,new Long (123), new Long (-2123), new Long (392839283)}; 108 break; 109 case Types.INTEGER: 110 values = new Object []{null,new Integer (123), new Integer (-2123), new Integer (392839283)}; 111 break; 112 case Types.SMALLINT: 113 values = new Object []{null,new Integer (123), new Integer (-2123), new Integer (32000)}; 114 break; 115 case Types.TINYINT: 116 values = new Object []{null,new Integer (0), new Integer (12), new Integer (228)}; 117 break; 118 case Types.REAL: 119 values = new Object []{null,new Float (0.0), new Float (-12.123), new Float (22812345234.9)}; 120 break; 121 case Types.FLOAT: 122 case Types.DOUBLE: 123 values = new Object []{null,new Double (0.0), new Double (-12.123), new Double (22812345234.9)}; 124 break; 125 case Types.NUMERIC: 126 case Types.DECIMAL: 127 needTrim = true; 128 if(md.getPrecision(1)<16){ values = new Object []{null,new BigDecimal("0.0"), new BigDecimal("-2"), new BigDecimal("-12.123")}; 130 132 }else{ 133 values = new Object []{null,new BigDecimal("0.0"), new BigDecimal("-2"), new BigDecimal("-12.123"), new BigDecimal("22812345234.9")}; 134 } 135 break; 136 case Types.BIT: 137 case Types.BOOLEAN: 138 values = new Object []{null, Boolean.TRUE, Boolean.FALSE}; 139 break; 140 case Types.TIME: 141 values = new Object []{null, new Time(10,17,56), new Time(0,0,0),new Time(23,59,59)}; 142 escape1 = "{t '"; 143 escape2 = "'}"; 144 break; 145 case Types.DATE: 146 values = new Object []{null, new java.sql.Date (10,10,1), new java.sql.Date (0,0,1),new java.sql.Date (70,0,1)}; 147 escape1 = "{d '"; 148 escape2 = "'}"; 149 break; 150 case Types.TIMESTAMP: 151 if(md.getPrecision(1) >16) 152 values = new Object []{null, new Timestamp(10,10,1, 10,17,56, 0), new Timestamp(0,0,1, 0,0,0, 0),new Timestamp( 120,1,1, 23,59,59, 500000000),new Timestamp(0),new Timestamp( -120,1,1, 23,59,59, 500000000)}; 153 else values = new Object []{null, new Timestamp(10,10,1, 10,17,0, 0), new Timestamp(0,0,1, 0,0,0, 0),new Timestamp(0)}; 155 escape1 = "{ts '"; 156 escape2 = "'}"; 157 break; 158 case Types.BINARY: 159 needTrim = true; 160 case Types.VARBINARY: 161 case Types.LONGVARBINARY: 162 case Types.BLOB: 163 values = new Object []{null, new byte[]{1, 127, -23}}; 164 break; 165 case Types.JAVA_OBJECT: 166 values = new Object []{null, new Integer (-123), new Double (1.2), new byte[]{1, 127, -23}}; 167 break; 168 case -11: values = new Object []{null, "342734E3-D9AC-408F-8724-B7A257C4529E", "342734E3-D9AC-408F-8724-B7A257C4529E"}; 170 quote = "\'"; 171 break; 172 default: fail("Unknown column type: " + rs.getMetaData().getColumnType(1)); 173 } 174 rs.close(); 175 176 con.close(); 178 con = AllTests.getConnection(); 179 st = con.createStatement(); 180 181 for(int i=0; i<values.length; i++){ 182 Object val = values[i]; 183 String q = (val == null) ? "" : quote; 184 String e1 = (val == null) ? "" : escape1; 185 String e2 = (val == null) ? "" : escape2; 186 if(val instanceof byte[]){ 187 StringBuffer buf = new StringBuffer ( "0x" ); 188 for(int k=0; k<((byte[])val).length; k++){ 189 String digit = "0" + Integer.toHexString( ((byte[])val)[k] ); 190 buf.append( digit.substring( digit.length()-2 ) ); 191 } 192 val = buf.toString(); 193 } 194 st.execute("Insert into " + table + "(abc) Values(" + e1 + q + val + q + e2 + ")"); 195 } 196 checkValues( st, values, needTrim); 197 198 st.execute("Delete From "+ table); 199 CallableStatement cal = con.prepareCall("Insert Into " + table + "(abc) Values(?)"); 200 for(int i=0; i<values.length; i++){ 201 Object val = values[i]; 202 cal.setObject( 1, val); 203 cal.execute(); 204 } 205 cal.close(); 206 checkValues( st, values, needTrim); 207 208 st.execute("Delete From "+ table); 209 cal = con.prepareCall("Insert Into " + table + "(abc) Values(?)"); 210 for(int i=0; i<values.length; i++){ 211 Object val = values[i]; 212 if(val == null){ 213 cal.setNull( 1, Types.NULL ); 214 }else 215 if(val instanceof Time){ 216 cal.setTime( 1, (Time)val ); 217 }else 218 if(val instanceof Timestamp){ 219 cal.setTimestamp( 1, (Timestamp)val ); 220 }else 221 if(val instanceof Date){ 222 cal.setDate( 1, (Date)val ); 223 }else 224 if(val instanceof String ){ 225 cal.setString( 1, (String )val ); 226 }else 227 if(val instanceof Boolean ){ 228 cal.setBoolean( 1, ((Boolean )val).booleanValue() ); 229 }else 230 if(val instanceof Byte ){ 231 cal.setByte( 1, ((Byte )val).byteValue() ); 232 }else 233 if(val instanceof Short ){ 234 cal.setShort( 1, ((Short )val).shortValue() ); 235 }else 236 if(val instanceof Integer ){ 237 cal.setInt( 1, ((Integer )val).intValue() ); 238 }else 239 if(val instanceof Long ){ 240 cal.setLong( 1, ((Long )val).longValue() ); 241 }else 242 if(val instanceof Float ){ 243 cal.setFloat( 1, ((Float )val).floatValue() ); 244 }else 245 if(val instanceof Double ){ 246 cal.setDouble( 1, ((Double )val).doubleValue() ); 247 }else 248 if(val instanceof BigDecimal){ 249 cal.setBigDecimal( 1, (BigDecimal)val ); 250 }else 251 if(val instanceof byte[]){ 252 cal.setBytes( 1, (byte[])val ); 253 } 254 cal.execute(); 255 } 256 cal.close(); 257 checkValues( st, values, needTrim); 258 259 260 st.execute("Delete From "+ table); 261 Statement st2 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); 262 ResultSet rs2 = st2.executeQuery("SELECT * From " + table); 263 for(int i=0; i<values.length; i++){ 264 rs2.moveToInsertRow(); 265 Object val = values[i]; 266 if(val == null){ 267 rs2.updateNull( name ); 268 }else 269 if(val instanceof Time){ 270 rs2.updateTime( name, (Time)val ); 271 }else 272 if(val instanceof Timestamp){ 273 rs2.updateTimestamp( name, (Timestamp)val ); 274 }else 275 if(val instanceof Date){ 276 rs2.updateDate( name, (Date)val ); 277 }else 278 if(val instanceof String ){ 279 rs2.updateString( name, (String )val ); 280 }else 281 if(val instanceof Boolean ){ 282 rs2.updateBoolean( name, ((Boolean )val).booleanValue() ); 283 }else 284 if(val instanceof Byte ){ 285 rs2.updateByte( name, ((Byte )val).byteValue() ); 286 }else 287 if(val instanceof Short ){ 288 rs2.updateShort( name, ((Short )val).shortValue() ); 289 }else 290 if(val instanceof Integer ){ 291 rs2.updateInt( name, ((Integer )val).intValue() ); 292 }else 293 if(val instanceof Long ){ 294 rs2.updateLong( name, ((Long )val).longValue() ); 295 }else 296 if(val instanceof Float ){ 297 rs2.updateFloat( name, ((Float )val).floatValue() ); 298 }else 299 if(val instanceof Double ){ 300 rs2.updateDouble( name, ((Double )val).doubleValue() ); 301 }else 302 if(val instanceof BigDecimal){ 303 rs2.updateBigDecimal( name, (BigDecimal)val ); 304 }else 305 if(val instanceof byte[]){ 306 rs2.updateBytes( name, (byte[])val ); 307 } 308 rs2.insertRow(); 309 } 310 st2.close(); 311 checkValues( st, values, needTrim); 312 } 313 314 315 private void checkValues(Statement st, Object [] values, boolean needTrim) throws Exception { 316 ResultSet rs = st.executeQuery("SELECT * From " + table); 317 318 int i = 0; 319 while(rs.next()){ 320 assertEqualsRsValue(values[i], rs, needTrim); 321 i++; 322 } 323 rs.close(); 324 } 325 326 327 public static Test suite() throws Exception { 328 TestSuite theSuite = new TestSuite("Data Types"); 329 for(int i=0; i<DATATYPES.length; i++){ 330 theSuite.addTest(new TestDataTypes( DATATYPES[i] ) ); 331 } 332 return theSuite; 333 } 334 335 public static void main(String [] argv) { 336 junit.swingui.TestRunner.main(new String []{TestDataTypes.class.getName()}); 337 } 338 } | Popular Tags |