Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > jdbcapi > LOBTest


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.LOBTest
4
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to You under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11
12       http://www.apache.org/licenses/LICENSE-2.0
13
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19
20  */
21
22 package org.apache.derbyTesting.functionTests.tests.jdbcapi;
23
24 import java.sql.DriverManager ;
25 import java.sql.Connection ;
26 import java.sql.Statement ;
27 import java.sql.ResultSet ;
28 import java.sql.ResultSetMetaData ;
29 import java.sql.SQLException ;
30 import java.sql.Array ;
31 import java.io.InputStream ;
32 import java.math.BigDecimal ;
33 import java.sql.Blob ;
34 import java.sql.Clob ;
35 import java.io.Reader ;
36 import java.sql.Date ;
37 import java.sql.Time ;
38 import java.sql.Timestamp ;
39 import java.sql.Ref ;
40 import java.net.URL ;
41 import java.sql.PreparedStatement ;
42 import org.apache.derby.tools.ij;
43 import org.apache.derbyTesting.functionTests.util.TestUtil;
44 import org.apache.derby.tools.JDBCDisplayUtil;
45
46 /**
47  * @author Jonas S Karlsson
48  */
49
50 public class LOBTest {
51     /* the default framework is embedded*/
52     public static final String driver = "org.apache.derby.jdbc.EmbeddedDriver";
53     public static final String protocol = "jdbc:derby:";
54         
55         
56     public static Connection connectAndCreate(String dbname) throws Exception {
57         // connect and create db
58 Class.forName(driver).newInstance(); // load driver
59 Connection conn = DriverManager.getConnection(protocol+dbname
60                                                       +";create=true");
61
62         conn.setAutoCommit(false);
63         return conn;
64     }
65     public static void disconnect(Connection conn) throws Exception {
66         conn.commit();
67         conn.close();
68     }
69     public static void printSQLError(SQLException e) {
70         while (e != null) {
71             System.out.print("\t");
72             JDBCDisplayUtil.ShowSQLException(System.out, e);
73             e = e.getNextException();
74         }
75     }
76     //////////////////////////////////////////////////////////////////////
77 public static void largeTest(String [] args) throws Exception {
78         System.out.println("connecting");
79         Connection conn = connectAndCreate("LOBdb");
80         Statement s = conn.createStatement();
81
82         try {
83             System.out.println("dropping");
84             s.executeUpdate("DROP TABLE atable");
85         } catch (Exception e) {
86         }
87
88         System.out.println("creating");
89         s.executeUpdate("CREATE TABLE atable (a INT, b LONG VARCHAR FOR BIT DATA)");
90         conn.commit();
91         java.io.File file = new java.io.File ("short.utf");
92         int fileLength = (int) file.length();
93
94         // first, create an input stream
95 java.io.InputStream fin = new java.io.FileInputStream (file);
96         PreparedStatement ps = conn.prepareStatement("INSERT INTO atable VALUES (?, ?)");
97         ps.setInt(1, 1);
98
99         // set the value of the input parameter to the input stream
100 // ps.setBinaryStream(2, fin, fileLength);
101 ps.setBinaryStream(2, fin, -1);
102         System.out.println("inserting");
103         ps.execute();
104         conn.commit();
105
106         // reading the columns
107 System.out.println("reading");
108         ResultSet rs = s.executeQuery("SELECT b, octet_length(b) FROM atable WHERE a = 1");
109         while (rs.next()) {
110             java.sql.Clob aclob = rs.getClob(1);
111             java.io.InputStream ip = rs.getAsciiStream(1);
112             System.out.println("octet_length = "+rs.getInt(2));
113         }
114
115         System.out.println("disconnecting");
116         disconnect(conn);
117     }
118
119     public static void typeTest(String [] args) throws Exception {
120
121         // use the ij utility to read the property file and
122 // make the initial connection.
123 ij.getPropertyArg(args);
124         Connection conn = ij.startJBMS();
125         
126         // old bit datatype, converted later to char () for bit data
127 new LOBTester(conn, "bit", "(8 )").test();
128         new LOBTester(conn, "bit", "(8 )").test();
129
130         new LOBTester(conn, "blob", "(2 M)").test();
131         new LOBTester(conn, "blob", "(2 K)").test();
132         new LOBTester(conn, "blob", "(64 )").test();
133
134         new LOBTester(conn, "clob", "(2 K)").test();
135         new LOBTester(conn, "clob", "(64 )").test();
136
137
138         new LOBTester(conn, "blob", "(2 M)").testBlobInsert();
139         disconnect(conn);
140     }
141     public static void main(String [] args) {
142         try {
143 // if (args.length > 0) {
144 // largeTest(args);
145 // } else {
146 typeTest(args);
147 // }
148 }
149         catch (Throwable e) {
150             LOBTest.printException(e);
151         }
152     }
153
154     public static void printException(Throwable e) {
155         //System.out.println("\t Exception thrown:");
156 if (e instanceof SQLException )
157             printSQLError((SQLException )e);
158         else
159             e.printStackTrace();
160     }
161 }
162
163
164 class LOBTester {
165     String typeName;
166     String typeSpec;
167     String table;
168     String [] colNames;
169     String [] colTypes;
170     int columns;
171     String [] colData;
172
173     Connection conn;
174     Statement st;
175
176     String [] typeNames = { "int", "char(10)", "varchar(80)", "long varchar", "char(10) for bit data", "long varchar for bit data", "blob(80)" };
177
178     static int BIT_OFFSET = 4;
179     static int LONG_VARBINARY_OFFSET = 5;
180     static int BLOB_OFFSET = 6;
181     static int TYPE_COL_OFFSET= 7;
182
183     public LOBTester(Connection c, String typeName, String typeSpec) throws SQLException {
184
185         this.conn = c;
186         this.typeName = typeName;
187         this.typeSpec = typeSpec;
188         this.table = typeName+"_table";
189         this.st = this.conn.createStatement();
190
191         columns = typeNames.length+1;
192         this.colNames = new String [columns];
193         this.colTypes = new String [columns];
194         for(int i=0; i<columns-1; i++) {
195             String colName = "col_"+i;
196             colNames[i] = colName;
197             colTypes[i] = typeNames[i];
198         }
199         colNames[columns-1] = "typecol";
200     String tmpTypeNameSpec;
201         if (typeName.equals("bit"))
202             tmpTypeNameSpec="char" +" "+typeSpec + " for bit data";
203         else
204             tmpTypeNameSpec=typeName+" "+typeSpec;
205         colTypes[columns-1] = tmpTypeNameSpec;
206         colData = new String [] { "100","'101'","'102'", "'103'",
207                                  TestUtil.stringToHexLiteral("104"),
208                                  TestUtil.stringToHexLiteral("105"),
209                                  "CAST (" +TestUtil.stringToHexLiteral("106") +" AS " +
210                                  colTypes[BLOB_OFFSET] +")",
211                                  "CAST (" +TestUtil.stringToHexLiteral("107") +" AS " +
212                                  tmpTypeNameSpec + ")" };
213         
214     }
215     public static void printResultSet(ResultSet rs) throws SQLException {
216         if (rs==null) return;
217         ResultSetMetaData md = rs.getMetaData();
218         int cols = md.getColumnCount();
219
220         boolean hasNext = true;
221         // according to javadoc, rs already points to first
222 // row, but it won't work if we don't call next()!
223
224         // print some metadata
225 for(int col=1; col<=cols; col++) {
226             System.out.println("\t---- "+col);
227             System.out.println("\tColumn : "+md.getColumnName(col));
228             System.out.println("\tType : "+md.getColumnType(col));
229             System.out.println("\tTypeName : "+md.getColumnTypeName(col));
230             System.out.println("\tClassName : "+md.getColumnClassName(col));
231             System.out.println("\tLabel : "+md.getColumnLabel(col));
232             System.out.println("\tDisplaySz : "+md.getColumnDisplaySize(col));
233             System.out.println("\tPrecision : "+md.getPrecision(col));
234             System.out.println("\tScale : "+md.getScale(col));
235             System.out.println("\tisCurrency: "+md.isCurrency(col));
236             System.out.println("\tisCaseSens: "+md.isCaseSensitive(col));
237             System.out.println("\tisDefWrite: "+md.isDefinitelyWritable(col));
238             System.out.println("\tisWrite : "+md.isWritable(col));
239             System.out.println("\tisSearchab: "+md.isSearchable(col));
240 // System.out.println("\tSchemaName: "+md.getSchemaName(col));
241 System.out.print("\n");
242         }
243
244         // print actual data
245 while (rs.next()) { // for each row
246 for(int col=1; col<=cols; col++) {
247                 Object c = rs.getObject(col);
248                 if (c==null)
249                     System.out.println("\tOUT = NULL");
250                 else {
251                     // fixup if it contains classname (remove "random" part after @)
252 String v = c.toString();
253                     if (v.indexOf('@') != -1) {
254                         v = v.substring(0, v.indexOf('@')+1);
255                         System.out.println("\tOUT = Object : "+prettyType(c));
256                     } else
257                         System.out.println("\tOUT = '"+v+"' : "+c.getClass().getName());
258                 }
259             }
260         }
261     }
262     public ResultSet X(String sql) throws SQLException {
263         try {
264             System.out.println("\n"+sql);
265             // cercumwait stupid executeQuery which can't take non-selects...
266 boolean result = ( (sql.charAt(0) == 'S') || (sql.charAt(0) == 's')); // detect "select" which returns result..
267 if (!result) {
268                 st.execute(sql);
269             } else {
270                 return st.executeQuery(sql);
271             }
272         } catch (Throwable e) {
273             LOBTest.printException(e);
274         }
275         return null;
276     }
277     public void Xprint(String sql) {
278         try {
279             ResultSet rs = X(sql);
280             printResultSet(rs);
281         } catch (Throwable e) {
282             LOBTest.printException(e);
283         }
284     }
285     static String [] getterName = {
286         "getObject", "getArray", "getAsciiStream", // 2
287 "getBigDecimal", "getBinaryStream", "getBlob", // 5
288 "getBoolean", "getByte", "getBytes", // 8
289 "getCharacterStream", "getClob", "getDate", // 11
290 "getDouble", "getFloat", "getInt", "getLong", // 15
291 "getRef", "getShort", "getString", "getTime", // 19
292 "getTimeStamp", "getURL" // 21
293 };
294
295     // getter takes a RESULTSET and uses GETTER on COLumn
296 // getters numbered 0..N-1, for N-1 null is returned
297 // otherwise descriptive string is returned
298 // if the getter throws exception the string says so
299 public static String getter(ResultSet rs, int getter, int col) {
300         Object o = "-NO VALUE-";
301         String s = "";
302         try {
303             if (getter < getterName.length) { // avoid array exception
304 s = getterName[getter];
305                 for(int i=s.length(); i<20; i++) s+=' ';
306                 s += " ->";
307             }
308
309             switch(getter) {
310                 case 0: {o = rs.getObject(col); break;}
311                 case 1: {Array v=rs.getArray(col);o=v;break;}
312                 case 2: {InputStream v=rs.getAsciiStream(col);o=v;break;}
313                 case 3: {BigDecimal v=rs.getBigDecimal(col);o=v;break;}
314                 case 4: {InputStream v=rs.getBinaryStream(col);o=v;break;}
315                 case 5: {Blob v=rs.getBlob(col);o=v;break;}
316                 case 6: {boolean v=rs.getBoolean(col);o=new Boolean (v);break;}
317                 case 7: {byte v=rs.getByte(col);o=new Byte (v);break;}
318                 case 8: {byte[] v=rs.getBytes(col);o=v;break;}
319                 case 9: {Reader v=rs.getCharacterStream(col);o=v;break;}
320                 case 10:{Clob v=rs.getClob(col);o=v;break;}
321                 case 11:{Date v=rs.getDate(col);o=v; break;}
322                 case 12:{double v=rs.getDouble(col);o=new Double (v);break;}
323                 case 13:{float v=rs.getFloat(col);o=new Float (v);break;}
324                 case 14:{int v=rs.getInt(col);o=new Integer (v);break;}
325                 case 15:{long v=rs.getLong(col);o=new Long (v);break;}
326                 case 16:{Ref v=rs.getRef(col);o=v;break;}
327                 case 17:{short v=rs.getShort(col);o=new Short (v);break;}
328                 case 18:{String v=rs.getString(col);o=v;break;}
329                 case 19:{Time v=rs.getTime(col);o=v;break;}
330                 case 20:{Timestamp v=rs.getTimestamp(col);o=v;break;}
331 // case 21:{URL v=rs.getURL(col);o=v;break;}
332 default: return null;
333             }
334             // fixup if it contains classname (remove "random" part after @)
335 String v = o.toString();
336             if (v.indexOf('@') != -1) { // non standard java object.
337 s += "Object' \t: "+prettyType(o);
338             } else {
339                 // default stringifier...
340 s += "'"+v+"' \t: "+o.getClass().getName();
341             }
342         } catch (Throwable e) {
343             s += "\t\tEXCEPTION ("+e.getMessage()+")";
344         }
345         return s;
346     }
347     static public String prettyType(Object o) {
348         if (o instanceof java.sql.Blob ) return "java.sql.Blob";
349         if (o instanceof java.sql.Clob ) return "java.sql.Clob";
350         if (o instanceof java.io.InputStream ) return "java.io.InputStream";
351         if (o instanceof java.io.Reader ) return "java.io.Reader";
352         if (o instanceof byte[]) return "byte[]";
353         return "Unknown type - "+o.getClass().getName();
354     }
355     public void testGetters() throws SQLException {
356         for(int i=0; i<columns; i++) {
357             System.out.println("\n\n=== Columntype "+colTypes[i]);
358         
359             String s =
360         "select "+
361         colNames[i] + " as " + colNames[i] + "_1, " +
362         colNames[i] + " as " + colNames[i] + "_2, " +
363         colNames[i] + " as " + colNames[i] + "_3, " +
364         colNames[i] + " as " + colNames[i] + "_4, " +
365         colNames[i] + " as " + colNames[i] + "_5, " +
366         colNames[i] + " as " + colNames[i] + "_6, " +
367         colNames[i] + " as " + colNames[i] + "_7, " +
368         colNames[i] + " as " + colNames[i] + "_8, " +
369         colNames[i] + " as " + colNames[i] + "_9, " +
370         colNames[i] + " as " + colNames[i] + "_10, " +
371         colNames[i] + " as " + colNames[i] + "_11, " +
372         colNames[i] + " as " + colNames[i] + "_12, " +
373         colNames[i] + " as " + colNames[i] + "_13, " +
374         colNames[i] + " as " + colNames[i] + "_14, " +
375         colNames[i] + " as " + colNames[i] + "_15, " +
376         colNames[i] + " as " + colNames[i] + "_16, " +
377         colNames[i] + " as " + colNames[i] + "_17, " +
378         colNames[i] + " as " + colNames[i] + "_18, " +
379         colNames[i] + " as " + colNames[i] + "_19, " +
380         colNames[i] + " as " + colNames[i] + "_20, " +
381         colNames[i] + " as " + colNames[i] + "_21 " +
382         "from "+
383         table;
384         
385             ResultSet rs = X(s);
386             rs.next(); // goto first
387 int getno = 0;
388             String r;
389             while(null!=(r = getter(rs, getno, getno + 1 ))) {
390                 System.out.println("\t"+i+" "+r);
391                 getno++;
392             }
393         }
394     }
395     public void testMetaData() {
396         System.out.println("\n\n---< METADATA TESTS\n");
397         // plain select
398 for(int i=0; i<columns; i++) {
399             String s = "select "+colNames[i]+" from "+table;
400             Xprint(s);
401         }
402     }
403     public void testCastTo() {
404         System.out.println("\n\n---< type CAST TO types: METADATA TESTS\n");
405         // CAST ( column TO types )
406 for(int i=0; i<columns; i++) {
407             String s;
408             if (colTypes[i].startsWith("bit"))
409                 s = "select cast( typecol as char (8) for bit data) from "+table;
410             else
411                 s = "select cast( typecol as "+colTypes[i]+" ) from "+table;
412             Xprint(s);
413         }
414     }
415     public void testCastFrom() {
416         System.out.println("\n\n---< columns CAST TO type: METADATA TESTS\n");
417         // CAST ( coltypes TO type )
418 for(int i=0; i<columns; i++) {
419             String s;
420             if (typeName.startsWith("bit"))
421         {
422             s = "select cast( "+colNames[i]+" as char (8) for bit data ) from "+table;
423         }
424             else
425                 s = "select cast( "+colNames[i]+" as "+typeName+typeSpec+" ) from "+table;
426             Xprint(s);
427         }
428     }
429     public void testBlobInsert() {
430
431         System.out.println("\n\n---< BLOB Insertion Tests\n");
432         // create table for testing
433 {
434             Xprint("create table blobCheck (bl blob(80)) ");
435         }
436
437         // test insertion of literals.
438 for (int i=0; i < columns; i++) {
439
440             if (colTypes[i].indexOf("blob") == -1)
441                 continue;
442
443             // Check char literals.
444 // (fail)
445 String insert = "insert into blobCheck (bl" +
446                 " ) values ('string' )";
447             Xprint(insert);
448             // (succeed)
449 insert = "insert into blobCheck (bl" +
450                 " ) values (cast (" +
451                 TestUtil.stringToHexLiteral("string") +
452                 " as blob(80)) )";
453             Xprint(insert);
454             // Check bit literals.
455 // (fail)
456 insert = "insert into blobCheck (bl" +
457                 " ) values (X'48' )";
458             Xprint(insert);
459             // old CS compatible value: ( b'01001' )
460 // (succeed)
461 insert = "insert into blobCheck (bl" +
462                 " ) values (cast (X'C8' as blob(80)) )";
463             Xprint(insert);
464             // Check hex literals.
465 // (fail)
466 insert = "insert into blobCheck (bl" +
467                 " ) values ( X'a78a' )";
468             Xprint(insert);
469             // (succeed)
470 insert = "insert into blobCheck (bl" +
471                 " ) values (cast (X'a78a' as blob(80)) )";
472             Xprint(insert);
473         }
474             Xprint("drop table blobCheck");
475     }
476     public void test() throws SQLException {
477         // create table for testing
478 {
479             String create = "create table "+table+" ( dummy int ";
480             for(int i=0; i<columns; i++) {
481                 create += ", "+colNames[i]+" "+colTypes[i];
482             }
483             create += " )";
484             Xprint(create); //st.execute(create);
485 }
486         // insert one row of numbers in string format if possible.
487 {
488             String insert = "insert into "+table+" values ( 45 ";
489                 for(int i=0; i<columns; i++) {
490                     insert += "," + colData[i] ;
491                 }
492             insert += " )";
493             Xprint(insert);
494         }
495
496         // insert various data in various columns, some will fail (int)
497 {
498             for(int i=0; i<columns; i++) {
499                 String insert = "insert into "+table+" ( "+colNames[i];
500
501                 if (isBitColumn(i))
502                 // have to cast for blob columns.
503 {
504                     insert += " ) values cast ( " +
505                         TestUtil.stringToHexLiteral("true") +
506                         " AS " + colTypes[i] + ")";
507     }
508                 else
509                     insert += " ) values ( 'true' )";
510                 Xprint(insert);
511             }
512         }
513
514         // run tests
515 testGetters();
516         testMetaData();
517         testCastFrom();
518         testCastTo();
519
520         // cleanup
521 Xprint("drop table "+table); //st.execute("drop table "+table);
522 }
523
524     private boolean isBitColumn(int offset)
525     {
526         return ((offset == BLOB_OFFSET) ||
527                  (offset == BIT_OFFSET) ||
528                  (offset == LONG_VARBINARY_OFFSET) ||
529                  (offset == TYPE_COL_OFFSET)
530                  );
531     }
532 }
533

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags