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

/**
 * 
 */

/**
 * Test case for MySQL CursorFetch bug
 */
public class CursorFetchTest
{
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String CONNECT_URI = "jdbc:mysql://192.168.201.2:3306/test?useCursorFetch=true&useServerPrepStmts=true";
    private static final String USER = "user";
    private static final String PASSWORD = "password";

    public static void main(String[] args) throws Exception
    {
        Class.forName(DRIVER);
        Connection conn = DriverManager.getConnection(CONNECT_URI, USER, PASSWORD);
        try
        {
            // Create test table
            Statement stmtWrite = conn.createStatement();
            stmtWrite.executeUpdate("create table foo (fkey int, fdate date, fprice decimal(15, 2), fdiscount decimal(5,3))");
            try
            {
                Statement stmtRead = conn.createStatement();
                stmtRead.setFetchSize(1000);
                // Insert test data
                stmtWrite.executeUpdate("insert into foo values (1, '2007-02-23', 99.9, 0.02)");
                // Execute test query
                ResultSet rs = stmtRead.executeQuery("select extract(year from fdate) as fyear, fprice * (1 - fdiscount) as fvalue from foo");
                try
                {
                    // Dump results
                    while (rs.next())
                    {
                        System.out.printf("%s\t%s\n", new Object[] {rs.getString(1), rs.getString(2)});
                    }
                }
                finally
                {
                    rs.close();
                }
            }
            finally
            {
                // Clean up
                stmtWrite.executeUpdate("drop table foo");
            }
        }
        finally
        {
            conn.close();
        }
    }
}