001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.jdbc4.TestPreparedStatementMethods
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.jdbc4;
023:
024: import java.io.File;
025: import java.io.FileInputStream;
026: import java.io.FileNotFoundException;
027: import java.io.Reader;
028: import java.io.InputStream;
029: import java.io.IOException;
030: import java.security.*;
031: import java.sql.Blob;
032: import java.sql.Connection;
033: import java.sql.Clob;
034: import java.sql.DriverManager;
035: import java.sql.PreparedStatement;
036: import java.sql.SQLException;
037: import java.sql.NClob;
038: import java.sql.ResultSet;
039: import java.sql.SQLXML;
040: import java.sql.Statement;
041: import org.apache.derby.shared.common.reference.SQLState;
042: import org.apache.derby.tools.ij;
043: import org.apache.derby.iapi.error.StandardException;
044: import org.apache.derbyTesting.functionTests.util.SQLStateConstants;
045:
046: /**
047: * This class is used to test the implementations of the JDBC 4.0 methods
048: * in the PreparedStatement interface
049: */
050:
051: public class TestPreparedStatementMethods {
052:
053: static Connection conn = null;
054: PreparedStatement ps = null;
055:
056: String filepath;
057: String sep;
058: boolean exists;
059:
060: /*
061: * This function is used to build the path to the directory where the files
062: * that are used to create the test blob and clob are present
063: */
064: void buildFilePath(String filename) {
065: filepath = "extin";
066: sep = System.getProperty("file.separator");
067: exists = (new File("extin", filename)).exists();
068: if (!exists) {
069: String userDir = System.getProperty("user.dir");
070: filepath = userDir + sep + ".." + sep + filepath;
071: }
072: }
073:
074: void t_setNString() {
075: try {
076: ps.setNString(0, null);
077: System.out
078: .println("UnImplemented Exception not thrown in code");
079: } catch (SQLException e) {
080: if (SQLState.NOT_IMPLEMENTED.equals(e.getSQLState())) {
081: System.out.println("Unexpected SQLException" + e);
082: }
083:
084: } catch (Exception e) {
085: System.out.println("Unexpected exception thrown in method"
086: + e);
087: e.printStackTrace();
088: }
089: }
090:
091: void t_setNCharacterStream() {
092: try {
093: ps.setNCharacterStream(0, null, 0);
094: System.out
095: .println("UnImplemented Exception not thrown in code");
096: } catch (SQLException e) {
097: if (SQLState.NOT_IMPLEMENTED.equals(e.getSQLState())) {
098: System.out.println("Unexpected SQLException" + e);
099: }
100:
101: } catch (Exception e) {
102: System.out.println("Unexpected exception thrown in method"
103: + e);
104: e.printStackTrace();
105: }
106: }
107:
108: void t_setNClob1() {
109: try {
110: ps.setNClob(0, (NClob) null);
111: System.out
112: .println("UnImplemented Exception not thrown in code");
113: } catch (SQLException e) {
114: if (SQLState.NOT_IMPLEMENTED.equals(e.getSQLState())) {
115: System.out.println("Unexpected SQLException" + e);
116: }
117:
118: } catch (Exception e) {
119: System.out.println("Unexpected exception thrown in method"
120: + e);
121: e.printStackTrace();
122: }
123: }
124:
125: /*
126: * Compares the two clobs supplied to se if they are similar
127: * returns true if they are similar and false otherwise
128: */
129: boolean compareClob(Clob clob1, Clob clob2) {
130: int c1, c2;
131: InputStream is1 = null, is2 = null;
132: try {
133: is1 = clob1.getAsciiStream();
134: is2 = clob2.getAsciiStream();
135: if (clob1.length() != clob2.length())
136: return false;
137: } catch (SQLException sqle) {
138: sqle.printStackTrace();
139: }
140: try {
141: for (long i = 0; i < clob1.length(); i++) {
142: c1 = is1.read();
143: c2 = is2.read();
144: if (c1 != c2)
145: return false;
146: }
147: } catch (IOException e) {
148: e.printStackTrace();
149: } catch (SQLException e) {
150: e.printStackTrace();
151: }
152: return true;
153: }
154:
155: /*
156: * Compares the two blobs supplied to se if they are similar
157: * returns true if they are similar and false otherwise
158: */
159: boolean compareBlob(Blob blob1, Blob blob2) {
160: int c1, c2;
161: InputStream is1 = null, is2 = null;
162: try {
163: is1 = blob1.getBinaryStream();
164: is2 = blob2.getBinaryStream();
165: if (blob1.length() != blob2.length())
166: return false;
167: } catch (SQLException sqle) {
168: sqle.printStackTrace();
169: }
170: try {
171: for (long i = 0; i < blob1.length(); i++) {
172: c1 = is1.read();
173: c2 = is2.read();
174: if (c1 != c2)
175: return false;
176: }
177: } catch (IOException e) {
178: e.printStackTrace();
179: } catch (SQLException e) {
180: e.printStackTrace();
181: }
182: return true;
183: }
184:
185: /*
186: * Build the clob value to be inserted into the table and insert it using
187: * the setClob method in the PreparedStatement interface
188: */
189: Clob buildAndInsertClobValue(int n, String filename, Connection conn) {
190: int c;
191: byte[] fromFile = new byte[1024];
192: Clob clob = null;
193: try {
194: clob = conn.createClob();
195: java.io.OutputStream os = clob.setAsciiStream(1);
196: buildFilePath(filename);
197: File f = new File(filepath + sep + filename);
198: InputStream is = getInputStream(f);
199: c = is.read(fromFile);
200: while (c > 0) {
201: os.write(fromFile, 0, c);
202: c = is.read(fromFile);
203: }
204: PreparedStatement ps = conn
205: .prepareStatement("insert into clobtable3 values(?,?)");
206: ps.setInt(1, n);
207: ps.setClob(2, clob);
208: ps.executeUpdate();
209: } catch (IOException ioe) {
210: ioe.printStackTrace();
211: } catch (PrivilegedActionException pae) {
212: pae.printStackTrace();
213: } catch (SQLException sqle) {
214: sqle.printStackTrace();
215: }
216: return clob;
217: }
218:
219: /*
220: * Build the clob value to be inserted into the table and insert it using
221: * the setBlob method in the PreparedStatement interface
222: */
223: Blob buildAndInsertBlobValue(int n, String filename, Connection conn) {
224: int c;
225: byte[] fromFile = new byte[1024];
226: Blob blob = null;
227: try {
228: blob = conn.createBlob();
229: java.io.OutputStream os = blob.setBinaryStream(1);
230: buildFilePath(filename);
231: File f = new File(filepath + sep + filename);
232: InputStream is = getInputStream(f);
233: c = is.read(fromFile);
234: while (c > 0) {
235: os.write(fromFile, 0, c);
236: c = is.read(fromFile);
237: }
238: PreparedStatement ps = conn
239: .prepareStatement("insert into blobtable3 values(?,?)");
240: ps.setInt(1, n);
241: ps.setBlob(2, blob);
242: ps.executeUpdate();
243: } catch (IOException ioe) {
244: ioe.printStackTrace();
245: } catch (PrivilegedActionException pae) {
246: pae.printStackTrace();
247: } catch (SQLException sqle) {
248: sqle.printStackTrace();
249: }
250: return blob;
251: }
252:
253: /**
254: * May need to convert this into a privileged block for reading a file.
255: */
256: protected static FileInputStream getInputStream(final File f)
257: throws PrivilegedActionException, FileNotFoundException {
258: return (FileInputStream) AccessController
259: .doPrivileged(new PrivilegedExceptionAction<FileInputStream>() {
260: public FileInputStream run()
261: throws FileNotFoundException {
262: return new FileInputStream(f);
263: }
264: });
265: }
266:
267: /*
268: * 1) Insert the clob in to the clob table by calling the
269: * buildAndInsertClobValue function
270: * 2) Check whether the clob value has been correctly inserted in to the
271: * table by using the compareClob function
272: */
273: void t_setClob() {
274: try {
275: int c;
276: byte[] fromFile;
277: fromFile = new byte[1024];
278: Statement s = conn.createStatement();
279: s.execute("create table clobtable3(n int)");
280: s
281: .execute("alter table clobtable3 add column clobCol CLOB(1M)");
282: s.close();
283: Clob clob = buildAndInsertClobValue(0000, "short.txt", conn);
284: Clob clob1 = buildAndInsertClobValue(1000, "aclob.txt",
285: conn);
286: Clob clob2 = buildAndInsertClobValue(2000,
287: "littleclob.txt", conn);
288: PreparedStatement ps3 = conn
289: .prepareStatement("select * from "
290: + "clobtable3 where n=1000");
291: ResultSet rs3 = ps3.executeQuery();
292: rs3.next();
293: Clob clob3 = rs3.getClob(2);
294: if (!compareClob(clob1, clob3)) {
295: System.out
296: .println("Difference between the inserted and the "
297: + "queried Clob values");
298: }
299: PreparedStatement ps4 = conn
300: .prepareStatement("select * from " + "clobtable3");
301: ResultSet rs4 = ps4.executeQuery();
302: rs4.next();
303: Clob clob4 = rs4.getClob(2);
304: if (!compareClob(clob, clob4)) {
305: System.out
306: .println("Difference between the inserted and the "
307: + "queried Clob values");
308: }
309: rs4.next();
310: clob4 = rs4.getClob(2);
311: if (!compareClob(clob1, clob4)) {
312: System.out
313: .println("Difference between the inserted and the "
314: + "queried Clob values");
315: }
316: rs4.next();
317: clob4 = rs4.getClob(2);
318: if (!compareClob(clob2, clob4)) {
319: System.out
320: .println("Difference between the inserted and the "
321: + "queried Clob values");
322: }
323:
324: } catch (SQLException e) {
325: if (SQLState.NOT_IMPLEMENTED.equals(e.getSQLState())) {
326: e.printStackTrace();
327: }
328: } catch (Exception e) {
329: e.printStackTrace();
330: }
331: }
332:
333: /*
334: * 1) Insert the blob in to the blob table by calling the
335: * buildAndInsertBlobValue function
336: * 2) Check whether the blob value has been correctly inserted in to the
337: * table by using the compareBlob function
338: */
339: void t_setBlob() {
340: try {
341: int c;
342: byte[] fromFile;
343: fromFile = new byte[1024];
344: Statement s = conn.createStatement();
345: s.execute("create table blobtable3(n int)");
346: s
347: .execute("alter table blobtable3 add column blobCol BLOB(1M)");
348: s.close();
349: Blob blob = buildAndInsertBlobValue(0000, "short.txt", conn);
350: Blob blob1 = buildAndInsertBlobValue(1000, "aclob.txt",
351: conn);
352: Blob blob2 = buildAndInsertBlobValue(2000,
353: "littleclob.txt", conn);
354: PreparedStatement ps3 = conn
355: .prepareStatement("select * from "
356: + "blobtable3 where n=1000");
357: ResultSet rs3 = ps3.executeQuery();
358: rs3.next();
359: Blob blob3 = rs3.getBlob(2);
360: if (!compareBlob(blob1, blob3)) {
361: System.out
362: .println("Difference between the inserted and the "
363: + "queried Blob values");
364: }
365: PreparedStatement ps4 = conn
366: .prepareStatement("select * from blobtable3");
367: ResultSet rs4 = ps4.executeQuery();
368: rs4.next();
369: Blob blob4 = rs4.getBlob(2);
370: if (!compareBlob(blob, blob4)) {
371: System.out
372: .println("Difference between the inserted and the "
373: + "queried Blob values");
374: }
375: rs4.next();
376: blob4 = rs4.getBlob(2);
377: if (!compareBlob(blob1, blob4)) {
378: System.out
379: .println("Difference between the inserted and the "
380: + "queried Blob values");
381: }
382: rs4.next();
383: blob4 = rs4.getBlob(2);
384: if (!compareBlob(blob2, blob4)) {
385: System.out
386: .println("Difference between the inserted and the "
387: + "queried Blob values");
388: }
389:
390: } catch (SQLException e) {
391: if (SQLState.NOT_IMPLEMENTED.equals(e.getSQLState())) {
392: e.printStackTrace();
393: }
394: } catch (Exception e) {
395: e.printStackTrace();
396: }
397: }
398:
399: /*
400: * The setClob method on the embedded side. Here functionality has still not
401: * been added. It still throws a notImplemented exception only
402: */
403: void t_Clob_setMethods_Embedded(Connection conn) {
404: try {
405: Statement s = conn.createStatement();
406: ResultSet rs = s.executeQuery("select * from clobtable3");
407: rs.next();
408: Clob clob = rs.getClob(2);
409: PreparedStatement ps = conn
410: .prepareStatement("insert into clobtable3"
411: + " values(?,?)");
412: ps.setInt(1, 3000);
413: ps.setClob(2, clob);
414: ps.executeUpdate();
415: ps.close();
416: } catch (SQLException e) {
417: if (SQLState.NOT_IMPLEMENTED.equals(e.getSQLState())) {
418: System.out.println("Unexpected SQLException" + e);
419: e.printStackTrace();
420: }
421: }
422: }
423:
424: /*
425: * The setBlob method on the embedded side. Here functionality has still not
426: * been added. It still throws a notImplemented exception only
427: */
428: void t_Blob_setMethods_Embedded(Connection conn) {
429: try {
430: Statement s = conn.createStatement();
431: ResultSet rs = s.executeQuery("select * from blobtable3");
432: rs.next();
433: Blob blob = rs.getBlob(2);
434: PreparedStatement ps = conn
435: .prepareStatement("insert into blobtable3"
436: + " values(?,?)");
437: ps.setInt(1, 3000);
438: ps.setBlob(2, blob);
439: ps.executeUpdate();
440: ps.close();
441: } catch (SQLException e) {
442: if (SQLState.NOT_IMPLEMENTED.equals(e.getSQLState())) {
443: System.out.println("Unexpected SQLException" + e);
444: e.printStackTrace();
445: }
446: }
447: }
448:
449: void t_setNClob2() {
450: try {
451: ps.setNClob(0, null, 0);
452: System.out
453: .println("UnImplemented Exception not thrown in code");
454: } catch (SQLException e) {
455: if (SQLState.NOT_IMPLEMENTED.equals(e.getSQLState())) {
456: System.out.println("Unexpected SQLException" + e);
457: }
458:
459: } catch (Exception e) {
460: System.out.println("Unexpected exception thrown in method"
461: + e);
462: e.printStackTrace();
463: }
464: }
465:
466: void t_setSQLXML() {
467: try {
468: ps.setSQLXML(0, null);
469: System.out
470: .println("UnImplemented Exception not thrown in code");
471: } catch (SQLException e) {
472: if (SQLState.NOT_IMPLEMENTED.equals(e.getSQLState())) {
473: System.out.println("Unexpected SQLException" + e);
474: }
475:
476: } catch (Exception e) {
477: System.out.println("Unexpected exception thrown in method"
478: + e);
479: e.printStackTrace();
480: }
481: }
482:
483: void t_setPoolable() {
484: try {
485: // Set the poolable statement hint to false
486: ps.setPoolable(false);
487: if (ps.isPoolable())
488: System.out.println("Expected a non-poolable statement");
489: // Set the poolable statement hint to true
490: ps.setPoolable(true);
491: if (!ps.isPoolable())
492: System.out.println("Expected a poolable statement");
493: } catch (SQLException sqle) {
494: // Check which SQLException state we've got and if it is
495: // expected, do not print a stackTrace
496: // Embedded uses XJ012, client uses XCL31.
497: if (sqle.getSQLState().equals("XJ012")
498: || sqle.getSQLState().equals("XCL31")) {
499: // All is good and is expected
500: } else {
501: System.out.println("Unexpected SQLException " + sqle);
502: sqle.printStackTrace();
503: }
504: } catch (Exception e) {
505: System.out.println("Unexpected exception thrown in method "
506: + e);
507: e.printStackTrace();
508: }
509: }
510:
511: void t_isPoolable() {
512: try {
513: // By default a prepared statement is poolable
514: if (!ps.isPoolable())
515: System.out.println("Expected a poolable statement");
516: } catch (SQLException sqle) {
517: // Check which SQLException state we've got and if it is
518: // expected, do not print a stackTrace
519: // Embedded uses XJ012, client uses XCL31.
520: if (sqle.getSQLState().equals("XJ012")
521: || sqle.getSQLState().equals("XCL31")) {
522: // All is good and is expected
523: } else {
524: System.out.println("Unexpected SQLException " + sqle);
525: sqle.printStackTrace();
526: }
527: } catch (Exception e) {
528: System.out.println("Unexpected exception thrown in method "
529: + e);
530: e.printStackTrace();
531: }
532: }
533:
534: /**
535: * Tests the wrapper methods isWrapperFor and unwrap. There are two cases
536: * to be tested
537: * Case 1: isWrapperFor returns true and we call unwrap
538: * Case 2: isWrapperFor returns false and we call unwrap
539: */
540: void t_wrapper() {
541: Class<PreparedStatement> wrap_class = PreparedStatement.class;
542:
543: //The if method succeeds enabling us to call the unwrap method without
544: //throwing an exception
545: try {
546: if (ps.isWrapperFor(wrap_class)) {
547: PreparedStatement stmt1 = (PreparedStatement) ps
548: .unwrap(wrap_class);
549: } else {
550: System.out
551: .println("isWrapperFor wrongly returns false");
552: }
553: } catch (SQLException sqle) {
554: sqle.printStackTrace();
555: }
556:
557: //Begin test for case2
558: //test for the case when isWrapper returns false
559: //using some class that will return false when
560: //passed to isWrapperFor
561: Class<ResultSet> wrap_class1 = ResultSet.class;
562:
563: try {
564: //returning false is the correct behaviour in this case
565: //Generate a message if it returns true
566: if (ps.isWrapperFor(wrap_class1)) {
567: System.out.println("isWrapperFor wrongly returns true");
568: } else {
569: ResultSet rs1 = (ResultSet) ps.unwrap(wrap_class1);
570: System.out
571: .println("unwrap does not throw the expected "
572: + "exception");
573: }
574: } catch (SQLException sqle) {
575: //calling unwrap in this case throws an SQLException
576: //ensure that this SQLException has the correct SQLState
577: if (!SQLStateConstants.UNABLE_TO_UNWRAP.equals(sqle
578: .getSQLState())) {
579: sqle.printStackTrace();
580: }
581: }
582: }
583:
584: /*
585: * Start the tests for the JDBC4.0 methods on the client side
586: */
587: void startClientTestMethods(Connection conn_main) {
588: PreparedStatement ps_main = null;
589:
590: try {
591: ps_main = conn_main
592: .prepareStatement("select count(*) from "
593: + "sys.systables");
594: conn = conn_main;
595: ps = ps_main;
596: t_setNString();
597: t_setNCharacterStream();
598: t_setNClob1();
599: t_setClob();
600: t_setBlob();
601: t_setNClob2();
602: t_setSQLXML();
603: t_isPoolable();
604: t_setPoolable();
605: t_wrapper();
606: // Close the prepared statement and verify the poolable hint
607: // cannot be set or retrieved
608: ps.close();
609: t_isPoolable();
610: t_setPoolable();
611: } catch (SQLException sqle) {
612: sqle.printStackTrace();
613: } finally {
614: try {
615: conn_main.close();
616: } catch (SQLException sqle) {
617: sqle.printStackTrace();
618: }
619: }
620: }
621:
622: /*
623: * Start the tests for testing the JDBC4.0 methods on the embedded side
624: */
625: void startEmbeddedTestMethods(Connection conn_main) {
626: PreparedStatement ps_main = null;
627:
628: try {
629: Statement s = conn_main.createStatement();
630: s.execute("create table clobtable3 (n int,clobcol CLOB)");
631: File file = new File("extin/short.txt");
632: int fileLength = (int) file.length();
633: InputStream fin = getInputStream(file);
634: ps = conn_main.prepareStatement("INSERT INTO "
635: + "clobtable3 " + "VALUES (?, ?)");
636: ps.setInt(1, 1000);
637: ps.setAsciiStream(2, fin, fileLength);
638: ps.execute();
639: ps.close();
640:
641: Statement s1 = conn_main.createStatement();
642: s1.execute("create table blobtable3 (n int,blobcol BLOB)");
643: File file1 = new File("extin/short.txt");
644: int fileLength1 = (int) file1.length();
645: InputStream fin1 = getInputStream(file1);
646: PreparedStatement ps1 = conn_main
647: .prepareStatement("INSERT INTO " + "blobtable3 "
648: + "VALUES (?, ?)");
649: ps1.setInt(1, 1000);
650: ps1.setBinaryStream(2, fin1, fileLength1);
651: ps1.execute();
652:
653: conn_main.commit();
654: t_Clob_setMethods_Embedded(conn_main);
655: t_Blob_setMethods_Embedded(conn_main);
656: ps_main = conn_main
657: .prepareStatement("select count(*) from "
658: + "sys.systables");
659: conn = conn_main;
660: ps = ps_main;
661: t_setNString();
662: t_setNCharacterStream();
663: t_setNClob1();
664: t_setNClob2();
665: t_setSQLXML();
666: t_isPoolable();
667: t_setPoolable();
668: t_wrapper();
669: // Close the prepared statement and verify the poolable hint
670: // cannot be set or retrieved
671: ps.close();
672: t_isPoolable();
673: t_setPoolable();
674: } catch (SQLException sqle) {
675: sqle.printStackTrace();
676: } catch (PrivilegedActionException pae) {
677: pae.printStackTrace();
678: } catch (FileNotFoundException fne) {
679: fne.printStackTrace();
680: } finally {
681: try {
682: conn_main.close();
683: } catch (SQLException sqle) {
684: sqle.printStackTrace();
685: }
686: }
687: }
688:
689: /**
690: * <p>
691: * Return true if we're running under the embedded client.
692: * </p>
693: */
694: private static boolean usingEmbeddedClient() {
695: return "embedded".equals(System.getProperty("framework"));
696: }
697:
698: public static void main(String args[]) {
699: try {
700: // use the ij utility to read the property file and
701: // make the initial connection.
702: ij.getPropertyArg(args);
703:
704: Connection conn_main = ij.startJBMS();
705:
706: TestPreparedStatementMethods tpsm = new TestPreparedStatementMethods();
707:
708: if (usingEmbeddedClient()) {
709: tpsm.startEmbeddedTestMethods(conn_main);
710: } else // DerbyNetClient
711: {
712: tpsm.startClientTestMethods(conn_main);
713: }
714:
715: } catch (Exception e) {
716: printStackTrace(e);
717: }
718: }
719:
720: private static void printStackTrace(Throwable e) {
721: System.out.println("" + e);
722: e.printStackTrace();
723: }
724: }
|