/* Copyright (c) 2001-2010, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */


/*
 * TestSubQueriesInPreparedStatements.java
 *
 * Created on July 9, 2003, 4:03 PM
 */
package org.hsqldb.test;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 *
 * @author boucherb@users
 */
public class TestSubQueriesInPreparedStatements {

    public static void main(String[] args) throws Exception {
        test();
    }

    public static void test() throws Exception {

        Connection        conn;
        Statement         stmnt;
        PreparedStatement pstmnt;
        Driver            driver;

        driver =
            (Driver) Class.forName("org.hsqldb.jdbc.JDBCDriver").newInstance();

        DriverManager.registerDriver(driver);

        conn = DriverManager.getConnection("jdbc:hsqldb:mem:test", "sa", "");
        stmnt  = conn.createStatement();
        pstmnt = conn.prepareStatement("drop table t if exists");

        boolean result = pstmnt.execute();

        pstmnt = conn.prepareStatement("create table t(i decimal)");

        int updatecount = pstmnt.executeUpdate();

        pstmnt = conn.prepareStatement("insert into t values(?)");

        for (int i = 0; i < 100; i++) {
            pstmnt.setInt(1, i);
            pstmnt.executeUpdate();
        }

        pstmnt = conn.prepareStatement(
            "select * from (select * from t where i < ?)");

        System.out.println("Expecting: 0..3");
        pstmnt.setInt(1, 4);

        ResultSet rs = pstmnt.executeQuery();

        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }

        System.out.println("Expecting: 0..4");
        pstmnt.setInt(1, 5);

        rs = pstmnt.executeQuery();

        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }

        pstmnt = conn.prepareStatement(
            "select sum(i) from (select i from t where i between ? and ?)");

        System.out.println("Expecting: 9");
        pstmnt.setInt(1, 4);
        pstmnt.setInt(2, 5);

        rs = pstmnt.executeQuery();

        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }

        System.out.println("Expecting: 15");
        pstmnt.setInt(2, 6);

        rs = pstmnt.executeQuery();

        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }

        pstmnt = conn.prepareStatement(
            "select * from (select i as c1 from t where i < ?) a, (select i as c2 from t where i < ?) b");

        System.out.println("Expecting: (0,0)");
        pstmnt.setInt(1, 1);
        pstmnt.setInt(2, 1);

        rs = pstmnt.executeQuery();

        while (rs.next()) {
            System.out.println("(" + rs.getInt(1) + "," + rs.getInt(2) + ")");
        }

        System.out.println("Expecting: ((0,0), (0,1), (1,0), (1,1)");
        pstmnt.setInt(1, 2);
        pstmnt.setInt(2, 2);

        rs = pstmnt.executeQuery();

        while (rs.next()) {
            System.out.println("(" + rs.getInt(1) + "," + rs.getInt(2) + ")");
        }

        System.out.println("Expecting: ((0,0) .. (3,3)");
        pstmnt.setInt(1, 4);
        pstmnt.setInt(2, 4);

        rs = pstmnt.executeQuery();

        while (rs.next()) {
            System.out.println("(" + rs.getInt(1) + "," + rs.getInt(2) + ")");
        }
    }
}