001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.derbynet.csPrepStmt
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.derbynet;
023:
024: import java.sql.Connection;
025: import java.sql.DriverManager;
026: import java.sql.PreparedStatement;
027: import java.sql.Statement;
028: import java.sql.ResultSet;
029: import java.math.BigDecimal;
030: import java.sql.Date;
031: import java.sql.Time;
032: import java.sql.Timestamp;
033: import java.sql.SQLException;
034: import java.io.ByteArrayInputStream;
035: import java.io.InputStreamReader;
036: import org.apache.derbyTesting.functionTests.util.TestUtil;
037: import org.apache.derby.tools.ij;
038:
039: /**
040: This test tests the JDBC PreparedStatement.
041: */
042:
043: public class csPrepStmt {
044: private static Connection conn = null;
045:
046: private static String[] testObjects = // string array for cleaning up
047: { "table t1", "table tab1", "table t2", "table bigtab",
048: "table tstab", "table Numeric_Tab", "table lobCheckOne",
049: "table lobCheckTwo" };
050:
051: public static void main(String args[]) {
052: try {
053: System.out.println("csPrepStmt Test Starts");
054: // Initialize JavaCommonClient Driver.
055: // Initialize JavaCommonClient Driver.
056: ij.getPropertyArg(args);
057: conn = ij.startJBMS();
058:
059: if (conn == null) {
060: System.out.println("conn didn't work");
061: return;
062: }
063:
064: Statement cleanstmt = conn.createStatement();
065: TestUtil.cleanUpTest(cleanstmt, testObjects);
066:
067: PreparedStatement ps;
068: ResultSet rs;
069: boolean hasResultSet;
070: int uc;
071:
072: // executeUpdate() without parameters
073: System.out.println("executeUpdate() without parameters");
074: ps = conn
075: .prepareStatement("create table t1(c1 int, c2 int, c3 int)");
076: uc = ps.executeUpdate();
077: System.out.println("Update count is: " + uc);
078:
079: // executeUpdate() with parameters
080: System.out.println("executeUpdate() with parameters");
081: ps = conn
082: .prepareStatement("insert into t1 values (?, 5, ?)");
083: ps.setInt(1, 99);
084: ps.setInt(2, 9);
085: uc = ps.executeUpdate();
086: System.out.println("Update count is: " + uc);
087:
088: // execute() with parameters, no result set returned
089: System.out
090: .println("execute() with parameters, no result set returned");
091: ps = conn
092: .prepareStatement("insert into t1 values (2, 6, ?), (?, 5, 8)");
093: ps.setInt(1, 10);
094: ps.setInt(2, 7);
095: hasResultSet = ps.execute();
096: while (hasResultSet) {
097: rs = ps.getResultSet();
098: while (rs.next())
099: System.out.println("ERROR: should not get here!");
100: hasResultSet = ps.getMoreResults();
101: }
102: uc = ps.getUpdateCount();
103: if (uc != -1)
104: System.out.println("Update count is: " + uc);
105:
106: // executeQuery() without parameters
107: System.out.println("executQuery() without parameters");
108: ps = conn.prepareStatement("select * from t1");
109: rs = ps.executeQuery();
110: while (rs.next())
111: System.out.println("got row: " + " " + rs.getInt(1)
112: + " " + rs.getInt(2) + " " + rs.getInt(3));
113: System.out.println("end of rows");
114:
115: // executeQuery() with parameters
116: System.out.println("executQuery() with parameters");
117: ps = conn.prepareStatement("select * from t1 where c2 = ?");
118: ps.setInt(1, 5);
119: rs = ps.executeQuery();
120: while (rs.next())
121: System.out.println("got row: " + " " + rs.getInt(1)
122: + " " + rs.getInt(2) + " " + rs.getInt(3));
123: System.out.println("end of rows");
124:
125: // execute() with parameters, with result set returned
126: System.out
127: .println("execute() with parameters with result set returned");
128: ps = conn.prepareStatement("select * from t1 where c2 = ?");
129: ps.setInt(1, 5);
130: hasResultSet = ps.execute();
131: while (hasResultSet) {
132: rs = ps.getResultSet();
133: while (rs.next())
134: System.out.println("got row: " + " " + rs.getInt(1)
135: + " " + rs.getInt(2) + " " + rs.getInt(3));
136: hasResultSet = ps.getMoreResults();
137: }
138: System.out.println("end of rows");
139: uc = ps.getUpdateCount();
140: if (uc != -1)
141: System.out.println("Update count is: " + uc);
142:
143: // test different data types for input parameters of a PreparedStatement
144: System.out
145: .println("test different data types for input parameters of a Prepared Statement");
146: ps = conn
147: .prepareStatement("create table t2(ti smallint, si smallint,i int, bi bigint, r real, f float, d double precision, n5_2 numeric(5,2), dec10_3 decimal(10,3), ch20 char(20),vc varchar(20), lvc long varchar,b20 char(23) for bit data, vb varchar(23) for bit data, lvb long varchar for bit data, dt date, tm time, ts timestamp not null)");
148: uc = ps.executeUpdate();
149: System.out.println("Update count is: " + uc);
150:
151: // byte array for binary values.
152: byte[] ba = new byte[] { 0x00, 0x1, 0x2, 0x3, 0x4, 0x5,
153: 0x6, 0x7, 0x8, 0x9, 0xa, 0xb, 0xc, 0xd, 0xe, 0xf,
154: 0x10, 0x11, 0x12, 0x13 };
155:
156: ps = conn
157: .prepareStatement("insert into t2 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,? , ?)");
158: ps.setByte(1, (byte) 1);
159: ps.setShort(2, (short) 2);
160: ps.setInt(3, 3);
161: ps.setLong(4, 4);
162: ps.setFloat(5, (float) 5.0);
163: ps.setDouble(6, 6.0);
164: ps.setDouble(7, 7.0);
165: ps.setBigDecimal(8, new BigDecimal("88.88"));
166: ps.setBigDecimal(9, new BigDecimal("99.1"));
167: ps.setString(10, "column11string");
168: byte[] c11ba = new String("column11vcstring")
169: .getBytes("UTF-8");
170: int len = c11ba.length;
171: ps.setAsciiStream(11, new ByteArrayInputStream(c11ba), len);
172: byte[] c12ba = new String("column12lvcstring")
173: .getBytes("UTF-8");
174: len = c12ba.length;
175: ps.setCharacterStream(12, new InputStreamReader(
176: new ByteArrayInputStream(c12ba), "UTF-8"), len);
177: ps.setBytes(13, ba);
178: ps.setBinaryStream(14, new ByteArrayInputStream(ba),
179: ba.length);
180: ps.setBytes(15, ba);
181: ps.setDate(16, Date.valueOf("2002-04-12"));
182: ps.setTime(17, Time.valueOf("11:44:30"));
183: ps.setTimestamp(18, Timestamp
184: .valueOf("2002-04-12 11:44:30.000000000"));
185: uc = ps.executeUpdate();
186: System.out.println("Update count is: " + uc);
187:
188: // test setObject on different datatypes of the input parameters of
189: // PreparedStatement
190: System.out
191: .println("test setObject on different data types for input parameters of a Prepared Statement");
192: ps = conn
193: .prepareStatement("insert into t2 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,? , ?)");
194: ps.setObject(1, new Byte((byte) 1));
195: ps.setObject(2, new Integer(2));
196: ps.setObject(3, new Integer(3));
197: ps.setObject(4, new Long(4));
198: ps.setObject(5, new Float(5.0));
199: ps.setObject(6, new Double(6.0));
200: ps.setObject(7, new Double(7.0));
201: ps.setObject(8, new BigDecimal("88.88"));
202: ps.setObject(9, new BigDecimal("99.1"));
203: ps.setObject(10, "column10string");
204: ps.setObject(11, "column11vcstring");
205: ps.setObject(12, "column12lvcstring");
206: ps.setObject(13, ba);
207: ps.setObject(14, ba);
208: ps.setObject(15, ba);
209: ps.setObject(16, Date.valueOf("2002-04-12"));
210: ps.setObject(17, Time.valueOf("11:44:30"));
211: ps.setObject(18, Timestamp
212: .valueOf("2002-04-12 11:44:30.000000000"));
213: uc = ps.executeUpdate();
214: System.out.println("Update count is: " + uc);
215:
216: // test setNull on different datatypes of the input parameters of PreparedStatement
217: System.out
218: .println("test setNull on different data types for input parameters of a Prepared Statement");
219: ps = conn
220: .prepareStatement("insert into t2 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,? , ?)");
221: ps.setNull(1, java.sql.Types.BIT);
222: ps.setNull(2, java.sql.Types.TINYINT);
223: ps.setNull(3, java.sql.Types.SMALLINT);
224: ps.setNull(4, java.sql.Types.INTEGER);
225: ps.setNull(5, java.sql.Types.BIGINT);
226: ps.setNull(6, java.sql.Types.REAL);
227: ps.setNull(7, java.sql.Types.FLOAT);
228: ps.setNull(8, java.sql.Types.DOUBLE);
229: ps.setNull(9, java.sql.Types.NUMERIC);
230: ps.setNull(10, java.sql.Types.DECIMAL);
231: ps.setNull(11, java.sql.Types.CHAR);
232: ps.setNull(12, java.sql.Types.VARCHAR);
233: ps.setNull(13, java.sql.Types.LONGVARCHAR);
234: ps.setNull(14, java.sql.Types.BINARY);
235: ps.setNull(15, java.sql.Types.VARBINARY);
236: ps.setNull(16, java.sql.Types.LONGVARBINARY);
237: ps.setNull(17, java.sql.Types.DATE);
238: ps.setNull(18, java.sql.Types.TIME);
239:
240: ps.setTimestamp(18, Timestamp
241: .valueOf("2002-04-12 11:44:31.000000000")); //slightly after
242: hasResultSet = ps.execute();
243: uc = ps.getUpdateCount();
244: if (uc != -1)
245: System.out.println("Update count is: " + uc);
246:
247: ps = conn.prepareStatement("select * from t2");
248: rs = ps.executeQuery();
249: while (rs.next()) {
250: System.out.println("got row: " + " " + " "
251: + rs.getByte(1) + " " + rs.getShort(2) + " "
252: + rs.getInt(3) + " " + rs.getLong(4) + " "
253: + rs.getFloat(5) + " " + rs.getDouble(6) + " "
254: + rs.getDouble(7) + " " + rs.getBigDecimal(8)
255: + " " + rs.getBigDecimal(9) + " "
256: + rs.getString(10) + " " + rs.getString(11)
257: + " " + rs.getString(12) + " "
258: + bytesToString(rs.getBytes(13)) + " "
259: + bytesToString(rs.getBytes(14)) + " "
260: + bytesToString(rs.getBytes(15)) + " "
261: + rs.getDate(16) + " " + rs.getTime(17) + " "
262: + rs.getTimestamp(18));
263: Timestamp ts = rs.getTimestamp(18);
264: Timestamp temp = Timestamp
265: .valueOf("2002-04-12 11:44:30.000000000");
266: if (ts.after(temp))
267: System.out.println("After first Timestamp!");
268: else if (ts.before(temp))
269: System.out.println("Before first Timestamp!");
270: else
271: System.out.println("Timestamp match!");
272: }
273: System.out.println("end of rows");
274:
275: try {
276: ps = conn
277: .prepareStatement("select * from t2 where i = ?");
278: rs = ps.executeQuery();
279: } catch (SQLException e) {
280: System.out.println("SQLState: " + e.getSQLState()
281: + " message: " + e.getMessage());
282: }
283: try {
284: ps = conn
285: .prepareStatement("insert into t2 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
286: ps.executeUpdate();
287: } catch (SQLException e) {
288: System.out.println("SQLState: " + e.getSQLState()
289: + " message: " + e.getMessage());
290: }
291: try {
292: int tabSize = 1000;
293: String createBigTabSql = "create table bigtab (";
294: for (int i = 1; i <= tabSize; i++) {
295: createBigTabSql += "c" + i + " int";
296: if (i != tabSize)
297: createBigTabSql += ", ";
298: else
299: createBigTabSql += " )";
300: }
301: //System.out.println(createBigTabSql);
302: ps = conn.prepareStatement(createBigTabSql);
303: uc = ps.executeUpdate();
304:
305: insertTab("bigtab", 50);
306: insertTab("bigtab", 200);
307: insertTab("bigtab", 300);
308: insertTab("bigtab", 500);
309: // prepared Statement with many params (bug 4863)
310: insertTab("bigtab", 1000);
311: selectFromBigTab();
312: // Negative Cases
313: System.out.println("Insert too many Columns");
314: insertTab("bigtab", 1001);
315: // this one will give a sytax error
316: System.out.println("Expected Syntax error ");
317: insertTab("bigtab", 0);
318: // table doesn't exist
319: System.out.println("Expected Table does not exist ");
320: insertTab("wrongtab", 1000);
321: } catch (SQLException e) {
322: System.out.println("SQLState: " + e.getSQLState()
323: + " message: " + e.getMessage());
324: } finally {
325: try {
326: if (rs != null) {
327: rs.close();
328: rs = null;
329: }
330: if (ps != null) {
331: ps.close();
332: ps = null;
333: }
334: } catch (Exception e) {
335: }
336: }
337:
338: test4975(conn);
339: test5130(conn);
340: test5172(conn);
341: testLobInRS(conn);
342:
343: conn.close();
344: // cleanup, first refresh conn
345: conn = ij.startJBMS();
346: cleanstmt = conn.createStatement();
347: TestUtil.cleanUpTest(cleanstmt, testObjects);
348: cleanstmt.close();
349: System.out.println("csPrepStmt Test Ends");
350: } catch (Exception e) {
351: e.printStackTrace();
352: }
353: }
354:
355: // Test creation and execution of many Prepared Statements
356: // Beetle 5130
357: private static void test5130(Connection conn) throws Exception {
358: int numOfPreparedStatement = 500;
359:
360: PreparedStatement[] tempPreparedStatement = new PreparedStatement[numOfPreparedStatement];
361: ResultSet rs;
362: String[] tableName = new String[numOfPreparedStatement];
363: for (int i = 0; i < numOfPreparedStatement; i++) {
364: tempPreparedStatement[i] = conn.prepareStatement(
365: "SELECT COUNT(*) from SYS.SYSTABLES",
366: ResultSet.TYPE_SCROLL_INSENSITIVE,
367: ResultSet.CONCUR_READ_ONLY);
368: rs = tempPreparedStatement[i].executeQuery();
369: rs.close();
370: }
371: for (int i = 0; i < numOfPreparedStatement; i++)
372: tempPreparedStatement[i].close();
373:
374: }
375:
376: private static void test5172(Connection conn) throws Exception {
377:
378: Statement stmt = conn.createStatement();
379:
380: try {
381: stmt.executeUpdate("drop table tab1");
382: } catch (SQLException se) {
383: }
384:
385: stmt
386: .executeUpdate("CREATE TABLE TSTAB (I int, STATUS_TS Timestamp, PROPERTY_TS Timestamp)");
387: stmt
388: .executeUpdate("INSERT INTO TSTAB VALUES(1 , '2003-08-15 21:20:00','2003-08-15 21:20:00')");
389: stmt
390: .executeUpdate("INSERT INTO TSTAB VALUES(2 , '1969-12-31 16:00:00.0', '2003-08-15 21:20:00')");
391:
392: stmt.close();
393:
394: String timestamp = "20";
395: String query = "select STATUS_TS " + "from TSTAB "
396: + "where (STATUS_TS >= ? or "
397: + " PROPERTY_TS<?)";
398:
399: System.out
400: .println("Negative test setString with Invalid Timestamp:"
401: + timestamp);
402:
403: PreparedStatement ps = conn.prepareStatement(query);
404: ps.setString(1, timestamp);
405: ps.setString(2, timestamp);
406: try {
407: ResultSet rs = ps.executeQuery();
408: rs.close();
409: ps.close();
410: } catch (SQLException e) {
411: System.out.println("SQLState: " + e.getSQLState()
412: + " message: " + e.getMessage());
413: }
414:
415: }
416:
417: private static void test4975(Connection conn) throws Exception {
418: BigDecimal minBigDecimalVal = null;
419: BigDecimal rBigDecimalVal = null;
420: String sminBigDecimalVal = null;
421:
422: PreparedStatement pstmt = null;
423: ResultSet rs = null;
424: Statement stmt = null;
425:
426: try {
427: stmt = conn.createStatement();
428: String createTableSQL = "create table Numeric_Tab (MAX_VAL NUMERIC(30,15), MIN_VAL NUMERIC(30,15), NULL_VAL NUMERIC(30,15))";
429: // to create the Numeric Table
430: stmt.executeUpdate(createTableSQL);
431:
432: String insertSQL = "insert into Numeric_Tab values(999999999999999,0.000000000000001, null)";
433: stmt.executeUpdate(insertSQL);
434:
435: //to extract the Maximum Value of BigDecimal to be Updated
436: sminBigDecimalVal = "0.000000000000001";
437: minBigDecimalVal = new BigDecimal(sminBigDecimalVal);
438: logMsg("Minimum BigDecimal Value: " + minBigDecimalVal);
439:
440: // to update Null value column with Minimum value
441: String sPrepStmt = "update Numeric_Tab set NULL_VAL=?";
442:
443: // Uncomment and prepare the below statement instead to see JCC bug on setObject for decimal
444: //String sPrepStmt = "update Numeric_Tab set NULL_VAL="+sminBigDecimalVal+" where 0.0 != ?";
445: logMsg("Prepared Statement String: " + sPrepStmt);
446:
447: // get the PreparedStatement object
448: pstmt = conn.prepareStatement(sPrepStmt);
449: pstmt.setObject(1, minBigDecimalVal);
450: pstmt.executeUpdate();
451:
452: //to query from the database to check the call of pstmt.executeUpdate
453: //to get the query string
454: String Null_Val_Query = "Select NULL_VAL from Numeric_Tab";
455: logMsg(Null_Val_Query);
456: rs = stmt.executeQuery(Null_Val_Query);
457: rs.next();
458:
459: rBigDecimalVal = (BigDecimal) rs.getObject(1);
460: logMsg("Returned BigDecimal Value after Updation: "
461: + rBigDecimalVal);
462: logMsg("Value returned from ctssql.stmt: "
463: + minBigDecimalVal);
464:
465: if (rBigDecimalVal.compareTo(minBigDecimalVal) == 0) {
466: logMsg("setObject Method sets the designated parameter with the Object");
467: } else {
468: logErr("setObject Method does not set the designated parameter with the Object");
469: throw new Exception(
470: "Call to setObject Method is Failed!");
471: }
472: } catch (SQLException sqle) {
473: logErr("SQL Exception: " + sqle.getMessage());
474: throw sqle;
475: } catch (Exception e) {
476: logErr("Unexpected Exception: " + e.getMessage());
477: throw e;
478: }
479:
480: finally {
481: try {
482: if (rs != null) {
483: rs.close();
484: rs = null;
485: }
486: if (pstmt != null) {
487: pstmt.close();
488: pstmt = null;
489: }
490: stmt.executeUpdate("drop table Numeric_Tab");
491: if (stmt != null) {
492: stmt.close();
493: stmt = null;
494: }
495: } catch (Exception e) {
496: }
497: }
498: }
499:
500: private static void logErr(String s) {
501: System.err.println(s);
502: }
503:
504: private static void logMsg(String s) {
505: System.out.println(s);
506: }
507:
508: private static void insertTab(String tabname, int numCols)
509: throws SQLException {
510: PreparedStatement ps;
511: System.out.println("insertTab ( " + tabname + "," + numCols
512: + ")");
513: String insertSql = "insert into " + tabname + "(";
514: int i;
515:
516: for (i = 1; i < numCols; i++)
517: insertSql += "c" + i + ", ";
518:
519: insertSql += "c" + i + ") values ( ";
520:
521: for (i = 1; i <= numCols; i++) {
522: insertSql += "?";
523: if (i != numCols)
524: insertSql += ", ";
525: else
526: insertSql += " )";
527: }
528:
529: try {
530: ps = conn.prepareStatement(insertSql);
531: //System.out.println("Prepared statement" + insertSql);
532: for (i = 1; i <= numCols; i++)
533: ps.setInt(i, i);
534: ps.executeUpdate();
535: ps.close();
536: } catch (SQLException e) {
537: System.out.println("SQLState: " + e.getSQLState()
538: + " message: " + e.getMessage());
539: //e.printStackTrace();
540: }
541:
542: }
543:
544: private static void selectFromBigTab() throws SQLException {
545: PreparedStatement ps = null;
546: ResultSet rs = null;
547:
548: String selectSQL = "select * from bigtab";
549: System.out.println(selectSQL);
550: ps = conn.prepareStatement(selectSQL);
551: rs = ps.executeQuery();
552: while (rs.next()) {
553: System.out.println("Col # 500 = " + rs.getObject(500)
554: + " Col 1000 = " + rs.getObject(1000));
555: }
556:
557: rs.close();
558: ps.close();
559:
560: }
561:
562: private static String bytesToString(byte[] ba) {
563: String s = null;
564: if (ba == null)
565: return s;
566: s = new String();
567: for (int i = 0; i < ba.length; i++)
568: s += (Integer.toHexString(ba[i] & 0x00ff));
569: return s;
570: }
571:
572: // Beetle 5292: Test for LOBs returned as part of a result set.
573:
574: static void testLobInRS(Connection conn) {
575:
576: // Create test objects.
577: try {
578: Statement st = conn.createStatement();
579: // Clob.
580: st.execute("create table lobCheckOne (c clob(30))");
581: st.execute("insert into lobCheckOne values (cast "
582: + "('yayorsomething' as clob(30)))");
583: // Blob.
584: st.execute("create table lobCheckTwo (b blob(30))");
585: st.execute("insert into lobCheckTwo values (cast " + "( "
586: + TestUtil.stringToHexLiteral("101010001101")
587: + " as blob(30)))");
588: } catch (SQLException e) {
589: System.out
590: .println("FAIL: Couldn't create required objects:");
591: e.printStackTrace();
592: return;
593: }
594:
595: try {
596:
597: // Clobs.
598:
599: System.out.println("CLOB result.");
600: Statement st = conn.createStatement();
601: ResultSet rs = st.executeQuery("select * from lobCheckOne");
602: if (rs.next())
603: System.out.println("GOT ROW: " + rs.getString(1));
604: else
605: System.out
606: .println("FAIL: Statement executed, but returned "
607: + "an empty result set.");
608:
609: // Blobs.
610:
611: System.out.println("BLOB result.");
612: st = conn.createStatement();
613: rs = st.executeQuery("select * from lobCheckTwo");
614: if (rs.next())
615: System.out.println("GOT ROW: " + rs.getString(1));
616: else
617: System.out
618: .println("FAIL: Statement executed, but returned "
619: + "an empty result set.");
620: rs.close();
621: st.close();
622: } catch (Exception e) {
623: System.out.println("FAIL: Encountered exception:");
624: e.printStackTrace();
625: return;
626: }
627:
628: return;
629:
630: }
631:
632: }
|