001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.LOBTest
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.jdbcapi;
023:
024: import java.sql.DriverManager;
025: import java.sql.Connection;
026: import java.sql.Statement;
027: import java.sql.ResultSet;
028: import java.sql.ResultSetMetaData;
029: import java.sql.SQLException;
030: import java.sql.Array;
031: import java.io.InputStream;
032: import java.math.BigDecimal;
033: import java.sql.Blob;
034: import java.sql.Clob;
035: import java.io.Reader;
036: import java.sql.Date;
037: import java.sql.Time;
038: import java.sql.Timestamp;
039: import java.sql.Ref;
040: import java.net.URL;
041: import java.sql.PreparedStatement;
042: import org.apache.derby.tools.ij;
043: import org.apache.derbyTesting.functionTests.util.TestUtil;
044: import org.apache.derby.tools.JDBCDisplayUtil;
045:
046: /**
047: * @author Jonas S Karlsson
048: */
049:
050: public class LOBTest {
051: /* the default framework is embedded*/
052: public static final String driver = "org.apache.derby.jdbc.EmbeddedDriver";
053: public static final String protocol = "jdbc:derby:";
054:
055: public static Connection connectAndCreate(String dbname)
056: throws Exception {
057: // connect and create db
058: Class.forName(driver).newInstance(); // load driver
059: Connection conn = DriverManager.getConnection(protocol + dbname
060: + ";create=true");
061:
062: conn.setAutoCommit(false);
063: return conn;
064: }
065:
066: public static void disconnect(Connection conn) throws Exception {
067: conn.commit();
068: conn.close();
069: }
070:
071: public static void printSQLError(SQLException e) {
072: while (e != null) {
073: System.out.print("\t");
074: JDBCDisplayUtil.ShowSQLException(System.out, e);
075: e = e.getNextException();
076: }
077: }
078:
079: //////////////////////////////////////////////////////////////////////
080: public static void largeTest(String[] args) throws Exception {
081: System.out.println("connecting");
082: Connection conn = connectAndCreate("LOBdb");
083: Statement s = conn.createStatement();
084:
085: try {
086: System.out.println("dropping");
087: s.executeUpdate("DROP TABLE atable");
088: } catch (Exception e) {
089: }
090:
091: System.out.println("creating");
092: s
093: .executeUpdate("CREATE TABLE atable (a INT, b LONG VARCHAR FOR BIT DATA)");
094: conn.commit();
095: java.io.File file = new java.io.File("short.utf");
096: int fileLength = (int) file.length();
097:
098: // first, create an input stream
099: java.io.InputStream fin = new java.io.FileInputStream(file);
100: PreparedStatement ps = conn
101: .prepareStatement("INSERT INTO atable VALUES (?, ?)");
102: ps.setInt(1, 1);
103:
104: // set the value of the input parameter to the input stream
105: // ps.setBinaryStream(2, fin, fileLength);
106: ps.setBinaryStream(2, fin, -1);
107: System.out.println("inserting");
108: ps.execute();
109: conn.commit();
110:
111: // reading the columns
112: System.out.println("reading");
113: ResultSet rs = s
114: .executeQuery("SELECT b, octet_length(b) FROM atable WHERE a = 1");
115: while (rs.next()) {
116: java.sql.Clob aclob = rs.getClob(1);
117: java.io.InputStream ip = rs.getAsciiStream(1);
118: System.out.println("octet_length = " + rs.getInt(2));
119: }
120:
121: System.out.println("disconnecting");
122: disconnect(conn);
123: }
124:
125: public static void typeTest(String[] args) throws Exception {
126:
127: // use the ij utility to read the property file and
128: // make the initial connection.
129: ij.getPropertyArg(args);
130: Connection conn = ij.startJBMS();
131:
132: // old bit datatype, converted later to char () for bit data
133: new LOBTester(conn, "bit", "(8 )").test();
134: new LOBTester(conn, "bit", "(8 )").test();
135:
136: new LOBTester(conn, "blob", "(2 M)").test();
137: new LOBTester(conn, "blob", "(2 K)").test();
138: new LOBTester(conn, "blob", "(64 )").test();
139:
140: new LOBTester(conn, "clob", "(2 K)").test();
141: new LOBTester(conn, "clob", "(64 )").test();
142:
143: new LOBTester(conn, "blob", "(2 M)").testBlobInsert();
144: disconnect(conn);
145: }
146:
147: public static void main(String[] args) {
148: try {
149: // if (args.length > 0) {
150: // largeTest(args);
151: // } else {
152: typeTest(args);
153: // }
154: } catch (Throwable e) {
155: LOBTest.printException(e);
156: }
157: }
158:
159: public static void printException(Throwable e) {
160: //System.out.println("\t Exception thrown:");
161: if (e instanceof SQLException)
162: printSQLError((SQLException) e);
163: else
164: e.printStackTrace();
165: }
166: }
167:
168: class LOBTester {
169: String typeName;
170: String typeSpec;
171: String table;
172: String[] colNames;
173: String[] colTypes;
174: int columns;
175: String[] colData;
176:
177: Connection conn;
178: Statement st;
179:
180: String[] typeNames = { "int", "char(10)", "varchar(80)",
181: "long varchar", "char(10) for bit data",
182: "long varchar for bit data", "blob(80)" };
183:
184: static int BIT_OFFSET = 4;
185: static int LONG_VARBINARY_OFFSET = 5;
186: static int BLOB_OFFSET = 6;
187: static int TYPE_COL_OFFSET = 7;
188:
189: public LOBTester(Connection c, String typeName, String typeSpec)
190: throws SQLException {
191:
192: this .conn = c;
193: this .typeName = typeName;
194: this .typeSpec = typeSpec;
195: this .table = typeName + "_table";
196: this .st = this .conn.createStatement();
197:
198: columns = typeNames.length + 1;
199: this .colNames = new String[columns];
200: this .colTypes = new String[columns];
201: for (int i = 0; i < columns - 1; i++) {
202: String colName = "col_" + i;
203: colNames[i] = colName;
204: colTypes[i] = typeNames[i];
205: }
206: colNames[columns - 1] = "typecol";
207: String tmpTypeNameSpec;
208: if (typeName.equals("bit"))
209: tmpTypeNameSpec = "char" + " " + typeSpec + " for bit data";
210: else
211: tmpTypeNameSpec = typeName + " " + typeSpec;
212: colTypes[columns - 1] = tmpTypeNameSpec;
213: colData = new String[] {
214: "100",
215: "'101'",
216: "'102'",
217: "'103'",
218: TestUtil.stringToHexLiteral("104"),
219: TestUtil.stringToHexLiteral("105"),
220: "CAST (" + TestUtil.stringToHexLiteral("106") + " AS "
221: + colTypes[BLOB_OFFSET] + ")",
222: "CAST (" + TestUtil.stringToHexLiteral("107") + " AS "
223: + tmpTypeNameSpec + ")" };
224:
225: }
226:
227: public static void printResultSet(ResultSet rs) throws SQLException {
228: if (rs == null)
229: return;
230: ResultSetMetaData md = rs.getMetaData();
231: int cols = md.getColumnCount();
232:
233: boolean hasNext = true;
234: // according to javadoc, rs already points to first
235: // row, but it won't work if we don't call next()!
236:
237: // print some metadata
238: for (int col = 1; col <= cols; col++) {
239: System.out.println("\t---- " + col);
240: System.out
241: .println("\tColumn : " + md.getColumnName(col));
242: System.out
243: .println("\tType : " + md.getColumnType(col));
244: System.out.println("\tTypeName : "
245: + md.getColumnTypeName(col));
246: System.out.println("\tClassName : "
247: + md.getColumnClassName(col));
248: System.out.println("\tLabel : "
249: + md.getColumnLabel(col));
250: System.out.println("\tDisplaySz : "
251: + md.getColumnDisplaySize(col));
252: System.out.println("\tPrecision : " + md.getPrecision(col));
253: System.out.println("\tScale : " + md.getScale(col));
254: System.out.println("\tisCurrency: " + md.isCurrency(col));
255: System.out.println("\tisCaseSens: "
256: + md.isCaseSensitive(col));
257: System.out.println("\tisDefWrite: "
258: + md.isDefinitelyWritable(col));
259: System.out.println("\tisWrite : " + md.isWritable(col));
260: System.out.println("\tisSearchab: " + md.isSearchable(col));
261: // System.out.println("\tSchemaName: "+md.getSchemaName(col));
262: System.out.print("\n");
263: }
264:
265: // print actual data
266: while (rs.next()) { // for each row
267: for (int col = 1; col <= cols; col++) {
268: Object c = rs.getObject(col);
269: if (c == null)
270: System.out.println("\tOUT = NULL");
271: else {
272: // fixup if it contains classname (remove "random" part after @)
273: String v = c.toString();
274: if (v.indexOf('@') != -1) {
275: v = v.substring(0, v.indexOf('@') + 1);
276: System.out.println("\tOUT = Object : "
277: + prettyType(c));
278: } else
279: System.out.println("\tOUT = '" + v + "' : "
280: + c.getClass().getName());
281: }
282: }
283: }
284: }
285:
286: public ResultSet X(String sql) throws SQLException {
287: try {
288: System.out.println("\n" + sql);
289: // cercumwait stupid executeQuery which can't take non-selects...
290: boolean result = ((sql.charAt(0) == 'S') || (sql.charAt(0) == 's')); // detect "select" which returns result..
291: if (!result) {
292: st.execute(sql);
293: } else {
294: return st.executeQuery(sql);
295: }
296: } catch (Throwable e) {
297: LOBTest.printException(e);
298: }
299: return null;
300: }
301:
302: public void Xprint(String sql) {
303: try {
304: ResultSet rs = X(sql);
305: printResultSet(rs);
306: } catch (Throwable e) {
307: LOBTest.printException(e);
308: }
309: }
310:
311: static String[] getterName = { "getObject", "getArray",
312: "getAsciiStream", // 2
313: "getBigDecimal", "getBinaryStream", "getBlob", // 5
314: "getBoolean", "getByte", "getBytes", // 8
315: "getCharacterStream", "getClob", "getDate", // 11
316: "getDouble", "getFloat", "getInt", "getLong", // 15
317: "getRef", "getShort", "getString", "getTime", // 19
318: "getTimeStamp", "getURL" // 21
319: };
320:
321: // getter takes a RESULTSET and uses GETTER on COLumn
322: // getters numbered 0..N-1, for N-1 null is returned
323: // otherwise descriptive string is returned
324: // if the getter throws exception the string says so
325: public static String getter(ResultSet rs, int getter, int col) {
326: Object o = "-NO VALUE-";
327: String s = "";
328: try {
329: if (getter < getterName.length) { // avoid array exception
330: s = getterName[getter];
331: for (int i = s.length(); i < 20; i++)
332: s += ' ';
333: s += " ->";
334: }
335:
336: switch (getter) {
337: case 0: {
338: o = rs.getObject(col);
339: break;
340: }
341: case 1: {
342: Array v = rs.getArray(col);
343: o = v;
344: break;
345: }
346: case 2: {
347: InputStream v = rs.getAsciiStream(col);
348: o = v;
349: break;
350: }
351: case 3: {
352: BigDecimal v = rs.getBigDecimal(col);
353: o = v;
354: break;
355: }
356: case 4: {
357: InputStream v = rs.getBinaryStream(col);
358: o = v;
359: break;
360: }
361: case 5: {
362: Blob v = rs.getBlob(col);
363: o = v;
364: break;
365: }
366: case 6: {
367: boolean v = rs.getBoolean(col);
368: o = new Boolean(v);
369: break;
370: }
371: case 7: {
372: byte v = rs.getByte(col);
373: o = new Byte(v);
374: break;
375: }
376: case 8: {
377: byte[] v = rs.getBytes(col);
378: o = v;
379: break;
380: }
381: case 9: {
382: Reader v = rs.getCharacterStream(col);
383: o = v;
384: break;
385: }
386: case 10: {
387: Clob v = rs.getClob(col);
388: o = v;
389: break;
390: }
391: case 11: {
392: Date v = rs.getDate(col);
393: o = v;
394: break;
395: }
396: case 12: {
397: double v = rs.getDouble(col);
398: o = new Double(v);
399: break;
400: }
401: case 13: {
402: float v = rs.getFloat(col);
403: o = new Float(v);
404: break;
405: }
406: case 14: {
407: int v = rs.getInt(col);
408: o = new Integer(v);
409: break;
410: }
411: case 15: {
412: long v = rs.getLong(col);
413: o = new Long(v);
414: break;
415: }
416: case 16: {
417: Ref v = rs.getRef(col);
418: o = v;
419: break;
420: }
421: case 17: {
422: short v = rs.getShort(col);
423: o = new Short(v);
424: break;
425: }
426: case 18: {
427: String v = rs.getString(col);
428: o = v;
429: break;
430: }
431: case 19: {
432: Time v = rs.getTime(col);
433: o = v;
434: break;
435: }
436: case 20: {
437: Timestamp v = rs.getTimestamp(col);
438: o = v;
439: break;
440: }
441: // case 21:{URL v=rs.getURL(col);o=v;break;}
442: default:
443: return null;
444: }
445: // fixup if it contains classname (remove "random" part after @)
446: String v = o.toString();
447: if (v.indexOf('@') != -1) { // non standard java object.
448: s += "Object' \t: " + prettyType(o);
449: } else {
450: // default stringifier...
451: s += "'" + v + "' \t: " + o.getClass().getName();
452: }
453: } catch (Throwable e) {
454: s += "\t\tEXCEPTION (" + e.getMessage() + ")";
455: }
456: return s;
457: }
458:
459: static public String prettyType(Object o) {
460: if (o instanceof java.sql.Blob)
461: return "java.sql.Blob";
462: if (o instanceof java.sql.Clob)
463: return "java.sql.Clob";
464: if (o instanceof java.io.InputStream)
465: return "java.io.InputStream";
466: if (o instanceof java.io.Reader)
467: return "java.io.Reader";
468: if (o instanceof byte[])
469: return "byte[]";
470: return "Unknown type - " + o.getClass().getName();
471: }
472:
473: public void testGetters() throws SQLException {
474: for (int i = 0; i < columns; i++) {
475: System.out.println("\n\n=== Columntype " + colTypes[i]);
476:
477: String s = "select " + colNames[i] + " as " + colNames[i]
478: + "_1, " + colNames[i] + " as " + colNames[i]
479: + "_2, " + colNames[i] + " as " + colNames[i]
480: + "_3, " + colNames[i] + " as " + colNames[i]
481: + "_4, " + colNames[i] + " as " + colNames[i]
482: + "_5, " + colNames[i] + " as " + colNames[i]
483: + "_6, " + colNames[i] + " as " + colNames[i]
484: + "_7, " + colNames[i] + " as " + colNames[i]
485: + "_8, " + colNames[i] + " as " + colNames[i]
486: + "_9, " + colNames[i] + " as " + colNames[i]
487: + "_10, " + colNames[i] + " as " + colNames[i]
488: + "_11, " + colNames[i] + " as " + colNames[i]
489: + "_12, " + colNames[i] + " as " + colNames[i]
490: + "_13, " + colNames[i] + " as " + colNames[i]
491: + "_14, " + colNames[i] + " as " + colNames[i]
492: + "_15, " + colNames[i] + " as " + colNames[i]
493: + "_16, " + colNames[i] + " as " + colNames[i]
494: + "_17, " + colNames[i] + " as " + colNames[i]
495: + "_18, " + colNames[i] + " as " + colNames[i]
496: + "_19, " + colNames[i] + " as " + colNames[i]
497: + "_20, " + colNames[i] + " as " + colNames[i]
498: + "_21 " + "from " + table;
499:
500: ResultSet rs = X(s);
501: rs.next(); // goto first
502: int getno = 0;
503: String r;
504: while (null != (r = getter(rs, getno, getno + 1))) {
505: System.out.println("\t" + i + " " + r);
506: getno++;
507: }
508: }
509: }
510:
511: public void testMetaData() {
512: System.out.println("\n\n---< METADATA TESTS\n");
513: // plain select
514: for (int i = 0; i < columns; i++) {
515: String s = "select " + colNames[i] + " from " + table;
516: Xprint(s);
517: }
518: }
519:
520: public void testCastTo() {
521: System.out
522: .println("\n\n---< type CAST TO types: METADATA TESTS\n");
523: // CAST ( column TO types )
524: for (int i = 0; i < columns; i++) {
525: String s;
526: if (colTypes[i].startsWith("bit"))
527: s = "select cast( typecol as char (8) for bit data) from "
528: + table;
529: else
530: s = "select cast( typecol as " + colTypes[i]
531: + " ) from " + table;
532: Xprint(s);
533: }
534: }
535:
536: public void testCastFrom() {
537: System.out
538: .println("\n\n---< columns CAST TO type: METADATA TESTS\n");
539: // CAST ( coltypes TO type )
540: for (int i = 0; i < columns; i++) {
541: String s;
542: if (typeName.startsWith("bit")) {
543: s = "select cast( " + colNames[i]
544: + " as char (8) for bit data ) from " + table;
545: } else
546: s = "select cast( " + colNames[i] + " as " + typeName
547: + typeSpec + " ) from " + table;
548: Xprint(s);
549: }
550: }
551:
552: public void testBlobInsert() {
553:
554: System.out.println("\n\n---< BLOB Insertion Tests\n");
555: // create table for testing
556: {
557: Xprint("create table blobCheck (bl blob(80)) ");
558: }
559:
560: // test insertion of literals.
561: for (int i = 0; i < columns; i++) {
562:
563: if (colTypes[i].indexOf("blob") == -1)
564: continue;
565:
566: // Check char literals.
567: // (fail)
568: String insert = "insert into blobCheck (bl"
569: + " ) values ('string' )";
570: Xprint(insert);
571: // (succeed)
572: insert = "insert into blobCheck (bl" + " ) values (cast ("
573: + TestUtil.stringToHexLiteral("string")
574: + " as blob(80)) )";
575: Xprint(insert);
576: // Check bit literals.
577: // (fail)
578: insert = "insert into blobCheck (bl" + " ) values (X'48' )";
579: Xprint(insert);
580: // old CS compatible value: ( b'01001' )
581: // (succeed)
582: insert = "insert into blobCheck (bl"
583: + " ) values (cast (X'C8' as blob(80)) )";
584: Xprint(insert);
585: // Check hex literals.
586: // (fail)
587: insert = "insert into blobCheck (bl"
588: + " ) values ( X'a78a' )";
589: Xprint(insert);
590: // (succeed)
591: insert = "insert into blobCheck (bl"
592: + " ) values (cast (X'a78a' as blob(80)) )";
593: Xprint(insert);
594: }
595: Xprint("drop table blobCheck");
596: }
597:
598: public void test() throws SQLException {
599: // create table for testing
600: {
601: String create = "create table " + table + " ( dummy int ";
602: for (int i = 0; i < columns; i++) {
603: create += ", " + colNames[i] + " " + colTypes[i];
604: }
605: create += " )";
606: Xprint(create); //st.execute(create);
607: }
608: // insert one row of numbers in string format if possible.
609: {
610: String insert = "insert into " + table + " values ( 45 ";
611: for (int i = 0; i < columns; i++) {
612: insert += "," + colData[i];
613: }
614: insert += " )";
615: Xprint(insert);
616: }
617:
618: // insert various data in various columns, some will fail (int)
619: {
620: for (int i = 0; i < columns; i++) {
621: String insert = "insert into " + table + " ( "
622: + colNames[i];
623:
624: if (isBitColumn(i))
625: // have to cast for blob columns.
626: {
627: insert += " ) values cast ( "
628: + TestUtil.stringToHexLiteral("true")
629: + " AS " + colTypes[i] + ")";
630: } else
631: insert += " ) values ( 'true' )";
632: Xprint(insert);
633: }
634: }
635:
636: // run tests
637: testGetters();
638: testMetaData();
639: testCastFrom();
640: testCastTo();
641:
642: // cleanup
643: Xprint("drop table " + table); //st.execute("drop table "+table);
644: }
645:
646: private boolean isBitColumn(int offset) {
647: return ((offset == BLOB_OFFSET) || (offset == BIT_OFFSET)
648: || (offset == LONG_VARBINARY_OFFSET) || (offset == TYPE_COL_OFFSET));
649: }
650: }
|