0001: /*
0002:
0003: Derby - Class org.apache.derbyTesting.functionTests.tests.derbynet.prepStmt
0004:
0005: Licensed to the Apache Software Foundation (ASF) under one or more
0006: contributor license agreements. See the NOTICE file distributed with
0007: this work for additional information regarding copyright ownership.
0008: The ASF licenses this file to You under the Apache License, Version 2.0
0009: (the "License"); you may not use this file except in compliance with
0010: the License. You may obtain a copy of the License at
0011:
0012: http://www.apache.org/licenses/LICENSE-2.0
0013:
0014: Unless required by applicable law or agreed to in writing, software
0015: distributed under the License is distributed on an "AS IS" BASIS,
0016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0017: See the License for the specific language governing permissions and
0018: limitations under the License.
0019:
0020: */
0021:
0022: package org.apache.derbyTesting.functionTests.tests.derbynet;
0023:
0024: import java.sql.Connection;
0025: import java.sql.DriverManager;
0026: import java.sql.PreparedStatement;
0027: import java.sql.Statement;
0028: import java.sql.ResultSet;
0029: import java.math.BigDecimal;
0030: import java.sql.Date;
0031: import java.sql.Time;
0032: import java.sql.Timestamp;
0033: import java.sql.Types;
0034: import java.sql.SQLException;
0035: import java.sql.BatchUpdateException;
0036: import java.io.ByteArrayInputStream;
0037: import java.io.InputStreamReader;
0038: import org.apache.derbyTesting.functionTests.util.TestUtil;
0039: import org.apache.derby.tools.ij;
0040: import org.apache.derby.tools.JDBCDisplayUtil;
0041:
0042: /**
0043: This test tests the JDBC PreparedStatement.
0044: */
0045:
0046: public class prepStmt {
0047: private static Connection conn = null;
0048:
0049: private static String[] testObjects = // string array for cleaning up
0050: {
0051: "table t1",
0052: "table tab1",
0053: "table t2",
0054: "table bigtab",
0055: "table tstab",
0056: "table doubletab",
0057: "table numtab",
0058: "table Numeric_Tab",
0059: "table jira614",
0060: "table jira614_a",
0061: "table jira428",
0062: "table jira125",
0063: "table varcharclobtab",
0064: "table jira125125125125125125125125125125125125125125125125125125125125125125125125125125125125125125125",
0065: "table jira1533_a", "table jira1533_b", "table jira1454" };
0066:
0067: public static void main(String args[]) {
0068: try {
0069: System.out.println("prepStmt Test Starts");
0070: ij.getPropertyArg(args);
0071: conn = ij.startJBMS();
0072:
0073: if (conn == null) {
0074: System.out.println("conn didn't work");
0075: return;
0076: }
0077:
0078: Statement cleanstmt = conn.createStatement();
0079: TestUtil.cleanUpTest(cleanstmt, testObjects);
0080:
0081: PreparedStatement ps;
0082: ResultSet rs;
0083: boolean hasResultSet;
0084: int uc;
0085:
0086: // executeUpdate() without parameters
0087: System.out.println("executeUpdate() without parameters");
0088: ps = conn
0089: .prepareStatement("create table t1(c1 int, c2 int, c3 int)");
0090: uc = ps.executeUpdate();
0091: System.out.println("Update count is: " + uc);
0092:
0093: // executeUpdate() with parameters
0094: System.out.println("executeUpdate() with parameters");
0095: ps = conn
0096: .prepareStatement("insert into t1 values (?, 5, ?)");
0097: ps.setInt(1, 99);
0098: ps.setInt(2, 9);
0099: uc = ps.executeUpdate();
0100: System.out.println("Update count is: " + uc);
0101:
0102: // execute() with parameters, no result set returned
0103: System.out
0104: .println("execute() with parameters, no result set returned");
0105: ps = conn
0106: .prepareStatement("insert into t1 values (2, 6, ?), (?, 5, 8)");
0107: ps.setInt(1, 10);
0108: ps.setInt(2, 7);
0109: hasResultSet = ps.execute();
0110: while (hasResultSet) {
0111: rs = ps.getResultSet();
0112: while (rs.next())
0113: System.out.println("ERROR: should not get here!");
0114: hasResultSet = ps.getMoreResults();
0115: }
0116: uc = ps.getUpdateCount();
0117: if (uc != -1)
0118: System.out.println("Update count is: " + uc);
0119:
0120: // executeQuery() without parameters
0121: System.out.println("executQuery() without parameters");
0122: ps = conn.prepareStatement("select * from t1");
0123: rs = ps.executeQuery();
0124: while (rs.next())
0125: System.out.println("got row: " + " " + rs.getInt(1)
0126: + " " + rs.getInt(2) + " " + rs.getInt(3));
0127: System.out.println("end of rows");
0128:
0129: // executeQuery() with parameters
0130: System.out.println("executQuery() with parameters");
0131: ps = conn.prepareStatement("select * from t1 where c2 = ?");
0132: ps.setInt(1, 5);
0133: rs = ps.executeQuery();
0134: while (rs.next())
0135: System.out.println("got row: " + " " + rs.getInt(1)
0136: + " " + rs.getInt(2) + " " + rs.getInt(3));
0137: System.out.println("end of rows");
0138:
0139: // execute() with parameters, with result set returned
0140: System.out
0141: .println("execute() with parameters with result set returned");
0142: ps = conn.prepareStatement("select * from t1 where c2 = ?");
0143: ps.setInt(1, 5);
0144: hasResultSet = ps.execute();
0145: while (hasResultSet) {
0146: rs = ps.getResultSet();
0147: while (rs.next())
0148: System.out.println("got row: " + " " + rs.getInt(1)
0149: + " " + rs.getInt(2) + " " + rs.getInt(3));
0150: hasResultSet = ps.getMoreResults();
0151: }
0152: System.out.println("end of rows");
0153: uc = ps.getUpdateCount();
0154: if (uc != -1)
0155: System.out.println("Update count is: " + uc);
0156:
0157: // test different data types for input parameters of a PreparedStatement
0158: System.out
0159: .println("test different data types for input parameters of a Prepared Statement");
0160: ps = conn
0161: .prepareStatement("create table t2(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)");
0162: uc = ps.executeUpdate();
0163: System.out.println("Update count is: " + uc);
0164:
0165: // byte array for binary values.
0166: byte[] ba = new byte[] { 0x00, 0x1, 0x2, 0x3, 0x4, 0x5,
0167: 0x6, 0x7, 0x8, 0x9, 0xa, 0xb, 0xc, 0xd, 0xe, 0xf,
0168: 0x10, 0x11, 0x12, 0x13 };
0169:
0170: ps = conn
0171: .prepareStatement("insert into t2 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,? , ?)");
0172: ps.setShort(1, (short) 1);
0173: ps.setInt(2, 2);
0174: ps.setLong(3, 3);
0175: ps.setFloat(4, (float) 4.0);
0176: ps.setDouble(5, 5.0);
0177: ps.setDouble(6, 6.0);
0178: ps.setBigDecimal(7, new BigDecimal("77.77"));
0179: ps.setBigDecimal(8, new BigDecimal("8.1"));
0180: ps.setString(9, "column9string");
0181: byte[] c10ba = new String("column10vcstring")
0182: .getBytes("UTF-8");
0183: int len = c10ba.length;
0184: ps.setAsciiStream(10, new ByteArrayInputStream(c10ba), len);
0185: byte[] c11ba = new String("column11lvcstring")
0186: .getBytes("UTF-8");
0187: len = c11ba.length;
0188: ps.setCharacterStream(11, new InputStreamReader(
0189: new ByteArrayInputStream(c11ba), "UTF-8"), len);
0190: ps.setBytes(12, ba);
0191: // Calling setBytes on the varchar for bit data type because it
0192: // Appears DB2 UDB accepts this only for the BLOB data type...
0193: // ps.setBinaryStream(13, new ByteArrayInputStream(ba), ba.length);
0194: ps.setBytes(13, ba);
0195: ps.setBytes(14, ba);
0196: ps.setDate(15, Date.valueOf("2002-04-12"));
0197: ps.setTime(16, Time.valueOf("11:44:30"));
0198: ps.setTimestamp(17, Timestamp
0199: .valueOf("2002-04-12 11:44:30.000000000"));
0200: uc = ps.executeUpdate();
0201: System.out.println("Update count is: " + uc);
0202:
0203: // test setObject on different datatypes of the input parameters of
0204: // PreparedStatement
0205: System.out
0206: .println("test setObject on different data types for input parameters of a Prepared Statement");
0207: ps = conn
0208: .prepareStatement("insert into t2 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,? , ?)");
0209: ps.setObject(1, new Integer(1));
0210: ps.setObject(2, new Integer(2));
0211: ps.setObject(3, new Long(3));
0212: ps.setObject(4, new Float(4.0));
0213: ps.setObject(5, new Double(5.0));
0214: ps.setObject(6, new Double(6.0));
0215: ps.setObject(7, new BigDecimal("77.77"));
0216: ps.setObject(8, new BigDecimal("8.1"));
0217: ps.setObject(9, "column11string");
0218: ps.setObject(10, "column10vcstring");
0219: ps.setObject(11, "column11lvcstring");
0220: ps.setObject(12, ba);
0221: ps.setObject(13, ba);
0222: ps.setObject(14, ba);
0223: ps.setObject(15, Date.valueOf("2002-04-12"));
0224: ps.setObject(16, Time.valueOf("11:44:30"));
0225: ps.setObject(17, Timestamp
0226: .valueOf("2002-04-12 11:44:30.000000000"));
0227: uc = ps.executeUpdate();
0228: System.out.println("Update count is: " + uc);
0229:
0230: // test setNull on different datatypes of the input parameters of PreparedStatement
0231: System.out
0232: .println("test setNull on different data types for input parameters of a Prepared Statement");
0233: ps = conn
0234: .prepareStatement("insert into t2 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,? , ?)");
0235: ps.setNull(1, java.sql.Types.SMALLINT);
0236: ps.setNull(2, java.sql.Types.INTEGER);
0237: ps.setNull(3, java.sql.Types.BIGINT);
0238: ps.setNull(4, java.sql.Types.REAL);
0239: ps.setNull(5, java.sql.Types.FLOAT);
0240: ps.setNull(6, java.sql.Types.DOUBLE);
0241: ps.setNull(7, java.sql.Types.NUMERIC);
0242: ps.setNull(8, java.sql.Types.DECIMAL);
0243: ps.setNull(9, java.sql.Types.CHAR);
0244: ps.setNull(10, java.sql.Types.VARCHAR);
0245: ps.setNull(11, java.sql.Types.LONGVARCHAR);
0246: ps.setNull(12, java.sql.Types.BINARY);
0247: ps.setNull(13, java.sql.Types.VARBINARY);
0248: ps.setNull(14, java.sql.Types.LONGVARBINARY);
0249: ps.setNull(15, java.sql.Types.DATE);
0250: ps.setNull(16, java.sql.Types.TIME);
0251:
0252: ps.setTimestamp(17, Timestamp
0253: .valueOf("2002-04-12 11:44:31.000000000")); //slightly after
0254: hasResultSet = ps.execute();
0255: uc = ps.getUpdateCount();
0256: if (uc != -1)
0257: System.out.println("Update count is: " + uc);
0258:
0259: ps = conn.prepareStatement("select * from t2");
0260: rs = ps.executeQuery();
0261: while (rs.next()) {
0262: System.out.println("got row: " + " " + rs.getShort(1)
0263: + " " + rs.getInt(2) + " " + rs.getLong(3)
0264: + " " + rs.getFloat(4) + " " + rs.getDouble(5)
0265: + " " + rs.getDouble(6) + " "
0266: + rs.getBigDecimal(7) + " "
0267: + rs.getBigDecimal(8) + " " + rs.getString(9)
0268: + " " + rs.getString(10) + " "
0269: + rs.getString(11) + " "
0270: + bytesToString(rs.getBytes(12)) + " "
0271: + bytesToString(rs.getBytes(13)) + " "
0272: + bytesToString(rs.getBytes(14)) + " "
0273: + rs.getDate(15) + " " + rs.getTime(16) + " "
0274: + rs.getTimestamp(17));
0275: Timestamp ts = rs.getTimestamp(17);
0276: Timestamp temp = Timestamp
0277: .valueOf("2002-04-12 11:44:30.000000000");
0278: if (ts.after(temp))
0279: System.out.println("After first Timestamp!");
0280: else if (ts.before(temp))
0281: System.out.println("Before first Timestamp!");
0282: else
0283: System.out.println("Timestamp match!");
0284: }
0285: System.out.println("end of rows");
0286:
0287: try {
0288: ps = conn
0289: .prepareStatement("select * from t2 where i = ?");
0290: rs = ps.executeQuery();
0291: } catch (SQLException e) {
0292: System.out.println("SQLState: " + e.getSQLState()
0293: + " message: " + e.getMessage());
0294: }
0295: try {
0296: ps = conn
0297: .prepareStatement("insert into t2 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
0298: ps.executeUpdate();
0299: } catch (SQLException e) {
0300: System.out.println("SQLState: " + e.getSQLState()
0301: + " message: " + e.getMessage());
0302: }
0303: try {
0304: int tabSize = 1000;
0305: String createBigTabSql = "create table bigtab (";
0306: for (int i = 1; i <= tabSize; i++) {
0307: createBigTabSql += "c" + i + " int";
0308: if (i != tabSize)
0309: createBigTabSql += ", ";
0310: else
0311: createBigTabSql += " )";
0312: }
0313: //System.out.println(createBigTabSql);
0314: ps = conn.prepareStatement(createBigTabSql);
0315: uc = ps.executeUpdate();
0316:
0317: insertTab(conn, "bigtab", 50);
0318: insertTab(conn, "bigtab", 200);
0319: insertTab(conn, "bigtab", 300);
0320: insertTab(conn, "bigtab", 500);
0321: // prepared Statement with many params (bug 4863)
0322: insertTab(conn, "bigtab", 1000);
0323: selectFromBigTab(conn);
0324: // Negative Cases
0325: System.out.println("Insert wrong column name");
0326: insertTab(conn, "bigtab", 1001);
0327: // this one will give a sytax error
0328: System.out.println("Expected Syntax error ");
0329: insertTab(conn, "bigtab", 0);
0330: // table doesn't exist
0331: System.out.println("Expected Table does not exist ");
0332: insertTab(conn, "wrongtab", 1000);
0333: } catch (SQLException e) {
0334: System.out.println("SQLState: " + e.getSQLState()
0335: + " message: " + e.getMessage());
0336: }
0337: rs.close();
0338: ps.close();
0339:
0340: testBigDecimalSetObject(conn);
0341: testBigDecimalSetObjectWithScale(conn);
0342:
0343: if (!TestUtil.isJCCFramework()) {
0344: testVaryingClientParameterTypeBatch(conn);
0345: }
0346:
0347: test4975(conn);
0348: test5130(conn);
0349: test5172(conn);
0350: jira614Test(conn);
0351: jira614Test_a(conn);
0352: jira170Test(conn);
0353: jira125Test(conn);
0354: jira428Test(conn);
0355: jira1454Test(conn);
0356: jira1533Test_a(conn);
0357: jira1533Test_b(conn);
0358: conn.close();
0359: // refresh conn before cleaning up
0360: conn = ij.startJBMS();
0361: cleanstmt = conn.createStatement();
0362: TestUtil.cleanUpTest(cleanstmt, testObjects);
0363: cleanstmt.close();
0364: conn.close();
0365: System.out.println("prepStmt Test Ends");
0366: } catch (Exception e) {
0367: e.printStackTrace();
0368: }
0369: }
0370:
0371: // Test execution of batch update where the type of
0372: // a parameter varies for difference entries in the batch.
0373: private static void testVaryingClientParameterTypeBatch(
0374: Connection conn) throws Exception {
0375: Statement stmt = conn.createStatement();
0376:
0377: try {
0378: stmt.execute("drop table varcharclobtab");
0379: } catch (Throwable t) {
0380: }
0381: stmt
0382: .execute("create table varcharclobtab (c1 varchar(100), c2 clob)");
0383: stmt.close();
0384:
0385: PreparedStatement pStmt = conn
0386: .prepareStatement("insert into varcharclobtab VALUES(?,?)");
0387:
0388: pStmt.setNull(1, Types.VARCHAR);
0389: pStmt.setString(2, "clob");
0390: pStmt.addBatch();
0391:
0392: pStmt.setString(1, "varchar");
0393: pStmt.setNull(2, Types.CLOB);
0394: pStmt.addBatch();
0395:
0396: // The following statement should not throw an exception.
0397: try {
0398: pStmt.executeBatch();
0399: } catch (ClassCastException e) {
0400: System.out
0401: .println("FAIL: ClassCastException thrown by testVaryingClientParameterTypeBatch test.");
0402: throw e;
0403: }
0404: pStmt.close();
0405: }
0406:
0407: // Test creation and execution of many Prepared Statements
0408: // Beetle 5130
0409: private static void test5130(Connection conn) throws Exception {
0410: int numOfPreparedStatement = 500;
0411:
0412: PreparedStatement[] tempPreparedStatement = new PreparedStatement[numOfPreparedStatement];
0413: ResultSet rs;
0414: String[] tableName = new String[numOfPreparedStatement];
0415: for (int i = 0; i < numOfPreparedStatement; i++) {
0416: tempPreparedStatement[i] = conn.prepareStatement(
0417: "SELECT COUNT(*) from SYS.SYSTABLES",
0418: ResultSet.TYPE_SCROLL_INSENSITIVE,
0419: ResultSet.CONCUR_READ_ONLY);
0420: rs = tempPreparedStatement[i].executeQuery();
0421: rs.close();
0422: }
0423: for (int i = 0; i < numOfPreparedStatement; i++)
0424: tempPreparedStatement[i].close();
0425:
0426: }
0427:
0428: private static void test5172(Connection conn) throws Exception {
0429:
0430: Statement stmt = conn.createStatement();
0431:
0432: try {
0433: stmt.executeUpdate("drop table tab1");
0434: } catch (SQLException se) {
0435: }
0436:
0437: stmt
0438: .executeUpdate("CREATE TABLE TSTAB (I int, STATUS_TS Timestamp, PROPERTY_TS Timestamp)");
0439: stmt
0440: .executeUpdate("INSERT INTO TSTAB VALUES(1 , '2003-08-15 21:20:00','2003-08-15 21:20:00')");
0441: stmt
0442: .executeUpdate("INSERT INTO TSTAB VALUES(2 , '1969-12-31 16:00:00.0', '2003-08-15 21:20:00')");
0443:
0444: stmt.close();
0445:
0446: String timestamp = "20";
0447: String query = "select STATUS_TS " + "from TSTAB "
0448: + "where (STATUS_TS >= ? or "
0449: + " PROPERTY_TS<?)";
0450:
0451: System.out
0452: .println("Negative test setString with Invalid Timestamp:"
0453: + timestamp);
0454:
0455: PreparedStatement ps = conn.prepareStatement(query);
0456: ps.setString(1, timestamp);
0457: ps.setString(2, timestamp);
0458: try {
0459: ResultSet rs = ps.executeQuery();
0460: } catch (SQLException e) {
0461: System.out.println("SQLState: " + e.getSQLState()
0462: + " message: " + e.getMessage());
0463: }
0464:
0465: }
0466:
0467: private static void test4975(Connection conn) throws Exception {
0468: BigDecimal minBigDecimalVal = null;
0469: BigDecimal rBigDecimalVal = null;
0470: String sminBigDecimalVal = null;
0471:
0472: PreparedStatement pstmt = null;
0473: ResultSet rs = null;
0474: Statement stmt = null;
0475:
0476: try {
0477: stmt = conn.createStatement();
0478: String createTableSQL = "create table Numeric_Tab (MAX_VAL NUMERIC(30,15), MIN_VAL NUMERIC(30,15), NULL_VAL NUMERIC(30,15) DEFAULT NULL)";
0479: // to create the Numeric Table
0480: stmt.executeUpdate(createTableSQL);
0481:
0482: String insertSQL = "insert into Numeric_Tab values(999999999999999,0.000000000000001, null)";
0483: stmt.executeUpdate(insertSQL);
0484:
0485: //to extract the Maximum Value of BigDecimal to be Updated
0486: sminBigDecimalVal = "0.000000000000001";
0487: minBigDecimalVal = new BigDecimal(sminBigDecimalVal);
0488: logMsg("Minimum BigDecimal Value: " + minBigDecimalVal);
0489:
0490: // to update Null value column with Minimum value
0491: String sPrepStmt = "update Numeric_Tab set NULL_VAL=?";
0492:
0493: // Uncomment and prepare the below statement instead to see JCC bug on setObject for decimal
0494: //String sPrepStmt = "update Numeric_Tab set NULL_VAL="+sminBigDecimalVal+" where 0.0 != ?";
0495: logMsg("Prepared Statement String: " + sPrepStmt);
0496:
0497: // get the PreparedStatement object
0498: pstmt = conn.prepareStatement(sPrepStmt);
0499: pstmt.setObject(1, minBigDecimalVal);
0500: pstmt.executeUpdate();
0501:
0502: //to query from the database to check the call of pstmt.executeUpdate
0503: //to get the query string
0504: String Null_Val_Query = "Select NULL_VAL from Numeric_Tab";
0505: logMsg(Null_Val_Query);
0506: rs = stmt.executeQuery(Null_Val_Query);
0507: rs.next();
0508:
0509: rBigDecimalVal = (BigDecimal) rs.getObject(1);
0510: logMsg("Returned BigDecimal Value after Updation: "
0511: + rBigDecimalVal);
0512: logMsg("Value returned from stmt: " + minBigDecimalVal);
0513:
0514: if (rBigDecimalVal.compareTo(minBigDecimalVal) == 0) {
0515: logMsg("setObject Method sets the designated parameter with the Object");
0516: } else {
0517: logErr("setObject Method does not set the designated parameter with the Object");
0518: throw new Exception(
0519: "Call to setObject Method is Failed!");
0520: }
0521: } catch (SQLException sqle) {
0522: logErr("SQL Exception: " + sqle.getMessage());
0523: throw new Exception("Call to setObject is Failed!");
0524: } catch (Exception e) {
0525: logErr("Unexpected Exception: " + e.getMessage());
0526: throw new Exception("Call to setObject is Failed!");
0527: }
0528:
0529: finally {
0530: try {
0531: if (rs != null) {
0532: rs.close();
0533: rs = null;
0534: }
0535: if (pstmt != null) {
0536: pstmt.close();
0537: pstmt = null;
0538: }
0539: stmt.executeUpdate("drop table Numeric_Tab");
0540: if (stmt != null) {
0541: stmt.close();
0542: stmt = null;
0543: }
0544: } catch (Exception e) {
0545: }
0546: }
0547: }
0548:
0549: private static void logErr(String s) {
0550: System.err.println(s);
0551: }
0552:
0553: private static void logMsg(String s) {
0554: System.out.println(s);
0555: }
0556:
0557: private static void insertTab(Connection conn, String tabname,
0558: int numCols) throws SQLException {
0559: PreparedStatement ps = null;
0560: System.out.println("insertTab ( " + tabname + "," + numCols
0561: + ")");
0562: String insertSql = "insert into " + tabname + "(";
0563: for (int i = 1; i <= numCols; i++) {
0564: insertSql += " c" + i;
0565: if (i != numCols)
0566: insertSql += ", ";
0567: else
0568: insertSql += ")";
0569: }
0570: insertSql += " values (";
0571: for (int i = 1; i <= numCols; i++) {
0572: insertSql += "?";
0573: if (i != numCols)
0574: insertSql += ", ";
0575: else
0576: insertSql += " )";
0577: }
0578:
0579: try {
0580: ps = conn.prepareStatement(insertSql);
0581:
0582: for (int i = 1; i <= numCols; i++)
0583: ps.setInt(i, i);
0584: ps.executeUpdate();
0585: } catch (SQLException e) {
0586: System.out.println("SQLState: " + e.getSQLState()
0587: + " message: " + e.getMessage());
0588: }
0589:
0590: }
0591:
0592: private static void selectFromBigTab(Connection conn)
0593: throws SQLException {
0594: PreparedStatement ps = null;
0595: ResultSet rs = null;
0596:
0597: String selectSQL = "select * from bigtab";
0598: System.out.println(selectSQL);
0599: ps = conn.prepareStatement(selectSQL);
0600: rs = ps.executeQuery();
0601: while (rs.next()) {
0602: System.out.println("Col # 500 = " + rs.getObject(500)
0603: + " Col 1000 = " + rs.getObject(1000));
0604: }
0605:
0606: rs.close();
0607: ps.close();
0608:
0609: }
0610:
0611: private static void testBigDecimalSetObject(Connection conn)
0612: throws SQLException {
0613: setupDoubleTab(conn);
0614: testBigDecimalToDoubleConversion(conn);
0615: }
0616:
0617: private static void setupDoubleTab(Connection conn)
0618: throws SQLException {
0619: String sql;
0620: Statement stmt = conn.createStatement();
0621: try {
0622: stmt.executeUpdate("DROP TABLE doubletab");
0623: } catch (SQLException se) {
0624: //System.out.println("Table doubletab not dropped. " + se.getMessage());
0625:
0626: }
0627:
0628: sql = "CREATE TABLE doubletab (i int, doubleVal DOUBLE)";
0629:
0630: System.out.println(sql);
0631: stmt.executeUpdate(sql);
0632: conn.commit();
0633:
0634: }
0635:
0636: private static void testBigDecimalToDoubleConversion(Connection conn)
0637: throws SQLException {
0638: System.out.println("\n\ntestBigDecimalToDoubleConversion().");
0639: System.out
0640: .println(" Check that values are preserved when BigDecimal \n values which have more than 31 digits are converted \n to Double with setObject");
0641: ResultSet rs = null;
0642: // Insert various double values
0643: double[] doubleVals = { 1.0E-130, 1.0E125, 0, -1.0E124 };
0644: //BigDecimal[] bigDecimalVals = new BigDecimal[doubleVals.length];
0645: BigDecimal[] bigDecimalVals = { new BigDecimal(1.0E-130),
0646: new BigDecimal(1.0E125), new BigDecimal(-1.0E124),
0647: new BigDecimal("12345678901234567890123456789012"),
0648: new BigDecimal("1.2345678901234567890123456789012") };
0649:
0650: String isql = "INSERT INTO doubletab VALUES (?, ?)";
0651: //System.out.println("conn.prepareStatement(" + isql +")");
0652: PreparedStatement insPs = conn.prepareStatement(isql);
0653: String ssql = "SELECT doubleVal FROM doubletab";
0654: PreparedStatement selPs = conn.prepareStatement(ssql);
0655: String dsql = "DELETE FROM doubletab";
0656: PreparedStatement delPs = conn.prepareStatement(dsql);
0657: for (int i = 0; i < bigDecimalVals.length; i++) {
0658: BigDecimal bd = bigDecimalVals[i];
0659: // Insert value
0660: //System.out.println("ps.setObject(1," + bd + ",java.sql.Types.DOUBLE)");
0661: insPs.setInt(1, i);
0662: insPs.setObject(2, bd, java.sql.Types.DOUBLE);
0663: insPs.executeUpdate();
0664: // Check Value
0665: rs = selPs.executeQuery();
0666: rs.next();
0667: checkDoubleMatch(bd.doubleValue(), rs.getDouble(1));
0668: // Clear out the table;
0669: delPs.executeUpdate();
0670: }
0671: insPs.close();
0672: selPs.close();
0673: delPs.close();
0674: rs.close();
0675: conn.commit();
0676: }
0677:
0678: static void testBigDecimalSetObjectWithScale(Connection conn)
0679: throws Exception {
0680: Statement stmt = conn.createStatement();
0681: String sql = null;
0682:
0683: System.out
0684: .println("\n\ntestBigDecimalSetObjectWithScale(). \nPass scale parameter of setObject");
0685:
0686: try {
0687: stmt.executeUpdate("DROP TABLE numtab");
0688: } catch (SQLException se) {
0689: //System.out.println("Table numtab not dropped. " + se.getMessage());
0690: }
0691: sql = "CREATE TABLE numtab (num NUMERIC(10,6))";
0692: stmt.executeUpdate(sql);
0693:
0694: // make a big decimal from string
0695: BigDecimal bdFromString = new BigDecimal("2.33333333");
0696:
0697: // prepare a statement which updates the third column of the table with
0698: // the DOUBLE columns
0699: sql = "INSERT INTO numtab VALUES(?)";
0700: PreparedStatement ps = conn.prepareStatement(sql);
0701: // setObject using the big decimal value
0702: //System.out.println("ps.setObject(1," + bdFromString + * ",java.sql.Types.DECIMAL,2)");
0703: int scale = 2;
0704: ps.setObject(1, bdFromString, java.sql.Types.DECIMAL, scale);
0705: ps.executeUpdate();
0706: // check the value
0707: sql = "SELECT num FROM numtab";
0708: ResultSet rs = stmt.executeQuery(sql);
0709: rs.next();
0710: // Check that the correct scale was set
0711: checkBigDecimalMatch(bdFromString.setScale(scale,
0712: BigDecimal.ROUND_DOWN), (BigDecimal) rs.getObject(1));
0713: rs.close();
0714: ps.close();
0715: stmt.close();
0716:
0717: conn.commit();
0718: }
0719:
0720: private static void checkDoubleMatch(double expectedValue,
0721: double actualValue) {
0722: if (actualValue == expectedValue)
0723: System.out.println("PASS: Actual value " + actualValue
0724: + " matches expected value: " + expectedValue);
0725: else
0726: new Exception("FAIL: Actual value: " + actualValue
0727: + " does not match expected value:" + expectedValue)
0728: .printStackTrace();
0729:
0730: }
0731:
0732: private static void checkBigDecimalMatch(BigDecimal expectedValue,
0733: BigDecimal actualValue) {
0734: if (actualValue == expectedValue
0735: || (actualValue.compareTo(expectedValue) == 0))
0736: System.out.println("PASS: Actual value " + actualValue
0737: + " matches expected value: " + expectedValue);
0738: else
0739: new Exception("FAIL: Actual value: " + actualValue
0740: + " does not match expected value:" + expectedValue)
0741: .printStackTrace();
0742:
0743: }
0744:
0745: private static String bytesToString(byte[] ba) {
0746: String s = null;
0747: if (ba == null)
0748: return s;
0749: s = new String();
0750: for (int i = 0; i < ba.length; i++)
0751: s += (Integer.toHexString(ba[i] & 0x00ff));
0752: return s;
0753: }
0754:
0755: // Derby bug 614 has to do with how the server responds when the
0756: // client closes the statement in between split QRYDTA blocks. We
0757: // have to cause a split QRYDTA block, which we can do by having a
0758: // bunch of moderately-sized rows which mostly fill a 32K block
0759: // followed by a single giant row which overflows the block. Then,
0760: // we fetch some of the rows, then close the result set.
0761: private static void jira614Test(Connection conn) throws Exception {
0762: Statement stmt = conn.createStatement();
0763: PreparedStatement ps;
0764: try {
0765: stmt.execute("drop table jira614");
0766: } catch (Throwable t) {
0767: }
0768: ps = conn
0769: .prepareStatement("create table jira614 (c1 varchar(10000))");
0770: ps.executeUpdate();
0771: String workString = genString("a", 150);
0772: ps = conn.prepareStatement("insert into jira614 values (?)");
0773: ps.setString(1, workString);
0774: for (int row = 0; row < 210; row++)
0775: ps.executeUpdate();
0776: workString = genString("b", 10000);
0777: ps.setString(1, workString);
0778: ps.executeUpdate();
0779: ps = conn.prepareStatement("select * from jira614");
0780: ResultSet rs = ps.executeQuery();
0781:
0782: int rowNum = 0;
0783: while (rs.next()) {
0784: rowNum++;
0785: if (rowNum == 26)
0786: break;
0787: }
0788: rs.close(); // This statement actually triggers the bug.
0789: System.out
0790: .println("Test jira614 completed successfully -- no Distributed Protocol Exception occurred");
0791: }
0792:
0793: private static String genString(String c, int howMany) {
0794: StringBuffer buf = new StringBuffer();
0795: for (int i = 0; i < howMany; i++)
0796: buf.append(c);
0797: return buf.toString();
0798: }
0799:
0800: // Part two of the regression test for bug 614 verifies that the
0801: // server-side statement state is cleaned up when a statement is
0802: // re-used. Specifically, we set up a statement which has a non-null
0803: // splitQRYDTA value, then we close that statement and re-use it for
0804: // a totally unrelated query. If the splitQRYDTA wasn't cleaned up
0805: // properly, it comes flooding back as the response to that unrelated
0806: // query, causing a protocol parsing exception on the client.
0807: private static void jira614Test_a(Connection conn) throws Exception {
0808: // 1: set up a second table to use for an unrelated query:
0809: Statement stmt = conn.createStatement();
0810: PreparedStatement ps;
0811: try {
0812: stmt.execute("drop table jira614_a");
0813: } catch (Throwable t) {
0814: }
0815: stmt.execute("create table jira614_a (c1 int)");
0816: ps = conn.prepareStatement("insert into jira614_a values (?)");
0817: for (int row = 1; row <= 5; row++) {
0818: ps.setInt(1, row);
0819: ps.executeUpdate();
0820: }
0821:
0822: // 2: get the first statement into a splitQRYDTA state:
0823: ResultSet rs = stmt.executeQuery("select * from jira614");
0824: int rowNum = 0;
0825: while (rs.next()) {
0826: rowNum++;
0827: if (rowNum == 26)
0828: break;
0829: }
0830: // 3: Now re-use the statement for some totally different purpose:
0831: stmt.close();
0832: stmt = conn.createStatement();
0833: rs = stmt.executeQuery("select * from jira614_a");
0834: while (rs.next())
0835: ;
0836: ps.close();
0837: rs.close();
0838: stmt.close();
0839: }
0840:
0841: // Jira-170 has to do with how the server handles re-synchronization of
0842: // the data stream when an enormous parameter value follows a failed
0843: // prepare statement. Note that it is deliberate here that we are preparing
0844: // a statement referring to a non-existing table.
0845: private static void jira170Test(Connection conn) throws Exception {
0846: Statement stmt = conn.createStatement();
0847: PreparedStatement ps = null;
0848: try {
0849: stmt.execute("drop table jira170");
0850: } catch (Throwable t) {
0851: }
0852: // Create a huge array of chars to be used as the input parameter
0853: char[] cData = new char[1000000];
0854: for (int i = 0; i < cData.length; i++)
0855: cData[i] = Character.forDigit(i % 10, 10);
0856: // The behavior of this test program depends on how the JDBC driver
0857: // handles statement prepares. The DB2 Universal JDBC driver implements
0858: // something called "deferred prepares" by default. This means that it
0859: // doesn't do the prepare of the statement until the statement is
0860: // actually executed. Other drivers, such as the standard Derby client
0861: // driver, do the prepare at the time of the prepare. This means that,
0862: // depending on which driver we're using and what the driver's
0863: // configuration is, we'll get the "table not found" error either on
0864: // the prepare or on the execute. It doesn't really matter for the
0865: // purposes of the test, because the whole point is that we *dont*
0866: // get a DRDA Protocol Exception, but rather a table-not-found
0867: // exception.
0868: try {
0869: ps = conn
0870: .prepareStatement("insert into jira170 values (?)");
0871: ps.setString(1, new String(cData));
0872: ps.execute();
0873: System.out
0874: .println("Test Jira170 failed: no exception when trying to execute a failed prepare with an enormous parameter");
0875: } catch (SQLException e) {
0876: if (e.getSQLState().equals("42X05"))
0877: System.out
0878: .println("Jira170: caught expected table not found");
0879: else
0880: e.printStackTrace();
0881: }
0882: }
0883:
0884: /**
0885: * Jira-1454 is an off-by-one bug in the splitQRYDTA processing in the
0886: * Network Server writeQRYDTA code, and is related to previous bugs
0887: * 614, 170, 491, and 492. The issue is that if the DSS block is exactly
0888: * the maximum DSS length (32767), then the writeQRYDTA code erroneously
0889: * thinks the DSS needs to be split when in fact it doesn't.
0890: *
0891: * The repro case sets up the boundary scenario; we run the case three
0892: * times, once with the value 1 less than the max DSS, once with the
0893: * value 1 greater than the max DSS, and once with the exact DSS length.
0894: * Only the third case triggers the JIRA-1454 bug; the other two tests
0895: * are for completeness.
0896: */
0897: private static void jira1454Test(Connection conn) throws Exception {
0898: tickleDSSLength(conn, 12748);
0899: tickleDSSLength(conn, 12750);
0900: tickleDSSLength(conn, 12749);
0901: }
0902:
0903: private static void tickleDSSLength(Connection conn, int c2Len)
0904: throws Exception {
0905: System.out.println("JIRA-1454 repro with c2 len=" + c2Len);
0906: Statement st = conn.createStatement();
0907:
0908: try {
0909: st.execute("drop table jira1454");
0910: } catch (SQLException se) {
0911: }
0912: st
0913: .execute("create table jira1454(c1 varchar(20000),c2 varchar(30000))");
0914:
0915: char[] c1 = new char[20000];
0916: for (int i = 0; i < c1.length; i++)
0917: c1[i] = Character.forDigit(i % 10, 10);
0918: char[] c2 = new char[30000];
0919: for (int i = 0; i < c2Len; i++)
0920: c2[i] = Character.forDigit(i % 10, 10);
0921:
0922: PreparedStatement pSt = conn
0923: .prepareStatement("insert into jira1454 values (?,?)");
0924: pSt.setString(1, new String(c1));
0925: pSt.setString(2, new String(c2, 0, c2Len));
0926:
0927: pSt.execute();
0928: pSt.close();
0929: ResultSet rs = st.executeQuery("select * from jira1454");
0930: while (rs.next())
0931: System.out.println("Fetched a row, c2.length="
0932: + rs.getString("c2").length());
0933: rs.close();
0934: st.close();
0935: }
0936:
0937: /**
0938: * Jira-125 has to do with proper use of continuation headers
0939: * for very large reply messages, such as the SQLDARD which is
0940: * returned for a prepared statement with an enormous number of
0941: * parameter markers. This test generates a multi-segment SQLDARD
0942: * response message from the server, to verify that the code in
0943: * DDMWriter.finalizeDSSLength is executed.
0944: *
0945: * Repro for DERBY-125 off-by-one error. This repro runs in
0946: * two iterations. The first iteration, we use a table name
0947: * and a column name that are extra long, so that the server-
0948: * side buffer has more data in it. The second iteration, we
0949: * use simpler names for the table and column, which take up
0950: * less space in the server buffer. Then, since the server-
0951: * side bytes array was previously used for a larger amount of
0952: * data, then the unused bytes contain old data. Since we
0953: * intentionally put the "larger amount of data" into the buffer
0954: * during the first iteration, we know what the old data bytes
0955: * are going to be. Thus, by using specific lengths for the
0956: * table and column names, we can 'shift' the old data until we
0957: * reach a point where the off-by-one error manifests itself:
0958: * namely, we end up incorrectly leaving a non-zero data byte
0959: * in the last position of the current server buffer, which
0960: * is wrong.
0961: */
0962:
0963: private static void jira125Test(Connection conn) throws Exception {
0964: jira125Test_a(conn);
0965: jira125Test_b(conn);
0966: }
0967:
0968: private static void jira125Test_b(Connection conn) throws Exception {
0969: Statement stmt = conn.createStatement();
0970: PreparedStatement ps;
0971: try {
0972: stmt.execute("drop table jira125");
0973: } catch (Throwable t) {
0974: }
0975: try {
0976: stmt.execute("create table jira125 (id integer)");
0977: stmt.execute("insert into jira125 values 1, 2, 3");
0978: } catch (Throwable t) {
0979: }
0980: StringBuffer buf = new StringBuffer();
0981: buf.append("SELECT id FROM jira125 WHERE id IN ( ");
0982:
0983: // Must have at least 551 columns here, in order to force
0984: // server buffer beyond 32k. NOTE: Changing this number
0985: // could cause the test to "pass" even if a regression
0986: // occurs--so only change it if needed!
0987: int nCols = 556;
0988: for (int i = 0; i < nCols; i++)
0989: buf.append("?,");
0990: buf.append("?)");
0991: ps = conn.prepareStatement(buf.toString());
0992: // Note that we actually have nCols+1 parameter markers
0993: for (int i = 0; i <= nCols; i++)
0994: ps.setInt(i + 1, 1);
0995: ResultSet rs = ps.executeQuery();
0996: while (rs.next())
0997: ;
0998: System.out
0999: .println("Test jira125 successful: "
1000: + (nCols + 1)
1001: + " parameter markers successfully prepared and executed.");
1002: }
1003:
1004: private static void jira125Test_a(Connection conn) throws Exception {
1005: Statement stmt = conn.createStatement();
1006:
1007: // Build a column name that is 99 characters long;
1008: // the length of the column name and the length of
1009: // the table name are important to the repro--so
1010: // do not change these unless you can confirm that
1011: // the new values will behave in the same way.
1012: StringBuffer id = new StringBuffer();
1013: for (int i = 0; i < 49; i++)
1014: id.append("id");
1015: id.append("i");
1016:
1017: // Build a table name that is 97 characters long;
1018: // the length of the column name and the length of
1019: // the table name are important to the repro--so
1020: // do not change these unless you can confirm that
1021: // the new values will behave in the same way.
1022: StringBuffer tabName = new StringBuffer("jira");
1023: for (int i = 0; i < 31; i++)
1024: tabName.append("125");
1025:
1026: try {
1027: stmt.execute("drop table " + tabName.toString());
1028: } catch (Throwable t) {
1029: }
1030: try {
1031: stmt.execute("create table " + tabName.toString() + " ("
1032: + id.toString() + " integer)");
1033: stmt.execute("insert into " + tabName.toString()
1034: + " values 1, 2, 3");
1035: } catch (Throwable t) {
1036: }
1037:
1038: PreparedStatement ps;
1039: StringBuffer buf = new StringBuffer();
1040: buf.append("SELECT " + id.toString() + " FROM "
1041: + tabName.toString() + " WHERE " + id.toString()
1042: + " IN ( ");
1043:
1044: // Must have at least 551 columns here, in order to force
1045: // server buffer beyond 32k. NOTE: Changing this number
1046: // could cause the test to "pass" even if a regression
1047: // occurs--so only change it if needed!
1048: int nCols = 554;
1049: for (int i = 0; i < nCols; i++)
1050: buf.append("?,");
1051: buf.append("?)");
1052: ps = conn.prepareStatement(buf.toString());
1053: // Note that we actually have nCols+1 parameter markers
1054: for (int i = 0; i <= nCols; i++)
1055: ps.setInt(i + 1, 1);
1056: ResultSet rs = ps.executeQuery();
1057: while (rs.next())
1058: ;
1059: System.out
1060: .println("Iteration 1 successful: "
1061: + (nCols + 1)
1062: + " parameter markers successfully prepared and executed.");
1063: }
1064:
1065: // Jira 1533 involves two different bugs regarding the handling of large
1066: // amounts of parameter data: first, the Network Server was incorrectly
1067: // handling the desegmentation of continued DSS segements, and second,
1068: // the Network Server was using the wrong heuristic to determine whether
1069: // long string data was being flowed in-line or externalized.
1070: //
1071: // Tests "a" and "b" provoke two different forms of this problem, one
1072: // with just a single continued segement, and one with several continuations
1073: private static void jira1533Test_a(Connection conn)
1074: throws Exception {
1075: Statement stmt = conn.createStatement();
1076: PreparedStatement ps;
1077: try {
1078: stmt.execute("drop table jira1533_a");
1079: } catch (Throwable t) {
1080: }
1081: stmt
1082: .execute("create table jira1533_a (aa BIGINT NOT NULL, "
1083: + "bbbbbb BIGINT DEFAULT 0 NOT NULL,"
1084: + " cccc VARCHAR(40), ddddddddddd BIGINT, eeeeee VARCHAR(128),"
1085: + " ffffffffffffffffff VARCHAR(128),"
1086: + "ggggggggg BLOB(2G), hhhhhhhhh VARCHAR(128), "
1087: + "iiiiiiii VARCHAR(128), jjjjjjjjjjjjjj BIGINT,"
1088: + "kkkkkkkk CHAR(1) DEFAULT 'f', "
1089: + "llllllll CHAR(1) DEFAULT 'f', "
1090: + "mmmmmmmmmmmmm CHAR(1) DEFAULT 'f')");
1091: stmt.close();
1092: ps = conn
1093: .prepareStatement("INSERT INTO jira1533_a (aa,bbbbbb,cccc,ddddddddddd,eeeeee,"
1094: + " ffffffffffffffffff,"
1095: + "ggggggggg,hhhhhhhhh,iiiiiiii,jjjjjjjjjjjjjj,kkkkkkkk,"
1096: + "llllllll,mmmmmmmmmmmmm)"
1097: + " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)");
1098: String blobStr = makeString(32584);
1099: ps.setLong(1, 5);
1100: ps.setLong(2, 1);
1101: ps.setString(3, "AAAAAAAAAAA");
1102: ps.setLong(4, 30000);
1103: ps.setString(5,
1104: "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
1105: ps.setString(6, "AAAAAAAAAAA");
1106: ps.setBytes(7, blobStr.getBytes());
1107: ps.setString(8,
1108: "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
1109: ps.setString(9,
1110: "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
1111: ps.setLong(10, 1);
1112: ps.setString(11, "1");
1113: ps.setString(12, "1");
1114: ps.setString(13, "1");
1115: ps.execute();
1116: ps.close();
1117: System.out
1118: .println("JIRA Test 1533(a) successful (no exception)");
1119: }
1120:
1121: private static void jira1533Test_b(Connection conn)
1122: throws Exception {
1123: Statement stmt = conn.createStatement();
1124: PreparedStatement ps;
1125: try {
1126: stmt.execute("drop table jira1533_b");
1127: } catch (Throwable t) {
1128: }
1129: stmt
1130: .execute("create table jira1533_b (aa BIGINT NOT NULL, "
1131: + "bbbbbb BIGINT DEFAULT 0 NOT NULL,"
1132: + " cccc VARCHAR(40), ddddddddddd BIGINT, eeeeee VARCHAR(128),"
1133: + " ffffffffffffffffff VARCHAR(128),"
1134: + "g1 BLOB(2G),g2 BLOB(2G),g3 BLOB(2G),g4 BLOB(2G),"
1135: + "ggggggggg BLOB(2G), hhhhhhhhh VARCHAR(128), "
1136: + "iiiiiiii VARCHAR(128), jjjjjjjjjjjjjj BIGINT,"
1137: + "kkkkkkkk CHAR(1) DEFAULT 'f', "
1138: + "llllllll CHAR(1) DEFAULT 'f', "
1139: + "mmmmmmmmmmmmm CHAR(1) DEFAULT 'f')");
1140: stmt.close();
1141: ps = conn
1142: .prepareStatement("INSERT INTO jira1533_b (aa,bbbbbb,cccc,ddddddddddd,eeeeee,"
1143: + " ffffffffffffffffff,"
1144: + "g1,g2,g3,g4,"
1145: + "ggggggggg,hhhhhhhhh,iiiiiiii,jjjjjjjjjjjjjj,kkkkkkkk,"
1146: + "llllllll,mmmmmmmmmmmmm)"
1147: + " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
1148: String blobStr = makeString(32584);
1149: ps.setLong(1, 5);
1150: ps.setLong(2, 1);
1151: ps.setString(3, "AAAAAAAAAAA");
1152: ps.setLong(4, 30000);
1153: ps.setString(5,
1154: "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
1155: ps.setString(6, "AAAAAAAAAAA");
1156: ps.setBytes(7, blobStr.getBytes());
1157: ps.setBytes(8, blobStr.getBytes());
1158: ps.setBytes(9, blobStr.getBytes());
1159: ps.setBytes(10, blobStr.getBytes());
1160: ps.setBytes(11, blobStr.getBytes());
1161: ps.setString(12,
1162: "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
1163: ps.setString(13,
1164: "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
1165: ps.setLong(14, 1);
1166: ps.setString(15, "1");
1167: ps.setString(16, "1");
1168: ps.setString(17, "1");
1169: ps.execute();
1170: ps.close();
1171: System.out
1172: .println("JIRA Test 1533(b) successful (no exception)");
1173: }
1174:
1175: private static String makeString(int length) {
1176: StringBuffer buf = new StringBuffer();
1177: for (int i = 0; i < length; i++)
1178: buf.append("X");
1179: return buf.toString();
1180: }
1181:
1182: // Jira 428 involves large batch sizes for Statement.addBatch and
1183: // Statement.executeBatch. Currently, there is a hard DRDA limit of
1184: // 65535 statements per batch (prior to DERBY-428, the server failed
1185: // at around 9000 statements). The different JDBC clients support slightly
1186: // lower limits: the Network Client supports 65534
1187: // statements in a single batch, the DB2JCC driver v2.4 supports
1188: // 65532 statements, the DB2JCC driver v2.6 supports 32765 statements.
1189: // This test just verifies that a batch of 32765 statements works,
1190: // and that a batch of 100000 statements
1191: // gets a BatchUpdateException from the Network Client.
1192: private static void jira428Test(Connection conn) throws Exception {
1193: Statement stmt = conn.createStatement();
1194: PreparedStatement ps;
1195: try {
1196: stmt.execute("drop table jira428");
1197: } catch (Throwable t) {
1198: }
1199: stmt.execute("create table jira428 (i integer)");
1200: boolean savedAutoCommit = conn.getAutoCommit();
1201: conn.setAutoCommit(false);
1202: ps = conn.prepareStatement("insert into jira428 values (?)");
1203: for (int i = 0; i < 32765; i++) {
1204: ps.setInt(1, i);
1205: ps.addBatch();
1206: }
1207: ps.executeBatch();
1208: conn.commit();
1209: // We don't run this part of the test for the JCC client because
1210: // the exception forces the connection closed. For DerbyNetClient, it's
1211: // a clean exception that we can catch and recover from, so we test
1212: // that code path:
1213: if (TestUtil.isDerbyNetClientFramework()) {
1214: ps = conn
1215: .prepareStatement("insert into jira428 values (?)");
1216: for (int i = 0; i < 100000; i++) {
1217: ps.setInt(1, i);
1218: ps.addBatch();
1219: }
1220: try {
1221: ps.executeBatch();
1222: System.out
1223: .println("JIRA428 FAILURE: expected an exception saying no more than 65534 statements in a single batch");
1224: } catch (BatchUpdateException bue) {
1225: // We don't print anything here because we use the same
1226: // master files for DerbyNet and DerbyNetClient, and we only
1227: // run this portion of the test for DerbyNetClient.
1228: // The exception that we get says "no more than 65534 stmts".
1229: }
1230: conn.commit();
1231: }
1232: conn.setAutoCommit(savedAutoCommit);
1233: }
1234: }
|