001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.bug4356
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.lang;
023:
024: import java.sql.Connection;
025: import java.sql.DriverManager;
026: import java.sql.DatabaseMetaData;
027: import java.sql.ResultSet;
028: import java.sql.PreparedStatement;
029: import java.sql.Statement;
030: import java.sql.SQLException;
031:
032: import org.apache.derby.tools.ij;
033: import org.apache.derby.tools.JDBCDisplayUtil;
034:
035: /**
036: * Demonstrate subselect behavior with prepared statement.
037: */
038: public class bug4356 {
039:
040: public static void main(String args[]) {
041: try {
042: /* Load the JDBC Driver class */
043: // use the ij utility to read the property file and
044: // make the initial connection.
045: ij.getPropertyArg(args);
046: Connection conn = ij.startJBMS();
047:
048: createTables(conn);
049: doUpdates(conn);
050: dumpResult(conn);
051: conn.close();
052: } catch (Exception e) {
053: System.out.println("FAIL -- unexpected exception " + e);
054: JDBCDisplayUtil.ShowException(System.out, e);
055: e.printStackTrace();
056: }
057: }
058:
059: private static void createTables(Connection conn)
060: throws SQLException {
061: Statement stmt = conn.createStatement();
062: try {
063: stmt.executeUpdate("DROP TABLE T1");
064: stmt.executeUpdate("DROP TABLE T2");
065: } catch (Exception e) {
066: }
067:
068: System.out.print("Creating tables...");
069: stmt.executeUpdate("CREATE TABLE T1 (a integer, b integer)");
070: stmt.executeUpdate("CREATE TABLE T2 (a integer)");
071: stmt.executeUpdate("INSERT INTO T2 VALUES(1)");
072: System.out.println("done.");
073:
074: stmt.close();
075: }
076:
077: private static void doUpdates(Connection conn) throws SQLException {
078: int rc;
079: // bug only happens when autocommit is off
080: conn.setAutoCommit(false);
081: PreparedStatement stmt = conn
082: .prepareStatement("INSERT INTO T1 VALUES (?,(select count(*) from t2 where a = ?)) ");
083: stmt.setInt(1, 1);
084: stmt.setInt(2, 1);
085:
086: rc = stmt.executeUpdate();
087:
088: stmt.setInt(1, 2);
089: stmt.setInt(2, 2);
090: rc = stmt.executeUpdate();
091:
092: conn.commit();
093: stmt.close();
094: }
095:
096: private static void dumpResult(Connection conn) throws SQLException {
097: Statement stmt = conn.createStatement();
098: ResultSet rs = stmt.executeQuery("SELECT * FROM T1");
099: System.out.println("T1 contents:");
100: System.out.println("Second row should have a b value of 0");
101: while (rs.next()) {
102: System.out.println(rs.getInt(1) + " " + rs.getInt(2));
103: }
104: rs.close();
105: conn.commit();
106: stmt.close();
107: }
108:
109: }
|