/*
 * Microsoft JDBC Driver for SQL Server
 * 
 * Copyright(c) Microsoft Corporation All rights reserved.
 * 
 * This program is made available under the terms of the MIT License. See the LICENSE file in the project root for more information.
 */
package com.microsoft.sqlserver.jdbc.unit.statement;

import static org.junit.jupiter.api.Assertions.assertEquals;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.Test;
import org.junit.platform.runner.JUnitPlatform;
import org.junit.runner.RunWith;

import com.microsoft.sqlserver.jdbc.SQLServerConnection;
import com.microsoft.sqlserver.testframework.AbstractTest;
import com.microsoft.sqlserver.testframework.DBConnection;

@RunWith(JUnitPlatform.class)
public class RegressionTest extends AbstractTest {
    private static String tableName = "[ServerCursorPStmt]";
    private static String procName = "[ServerCursorProc]";

    /**
     * Tests select into stored proc
     * 
     * @throws SQLException
     */
    @Test
    public void testServerCursorPStmt() throws SQLException {

        SQLServerConnection con = (SQLServerConnection) DriverManager.getConnection(connectionString);
        Statement stmt = con.createStatement();

        PreparedStatement pstmt = null;
        ResultSet rs = null;

        // expected values
        int numRowsInResult = 1;
        String col3Value = "India";
        String col3Lookup = "IN";

        stmt.executeUpdate("CREATE TABLE " + tableName + " (col1 int primary key, col2 varchar(3), col3 varchar(128))");
        stmt.executeUpdate("INSERT INTO " + tableName + " VALUES (1, 'CAN', 'Canada')");
        stmt.executeUpdate("INSERT INTO " + tableName + " VALUES (2, 'USA', 'United States of America')");
        stmt.executeUpdate("INSERT INTO " + tableName + " VALUES (3, 'JPN', 'Japan')");
        stmt.executeUpdate("INSERT INTO " + tableName + " VALUES (4, '" + col3Lookup + "', '" + col3Value + "')");

        // create stored proc
        String storedProcString;

        if (DBConnection.isSqlAzure(con)) {
            // On SQL Azure, 'SELECT INTO' is not supported. So do not use it.
            storedProcString = "CREATE PROCEDURE " + procName + " @param varchar(3) AS SELECT col3 FROM " + tableName + " WHERE col2 = @param";
        }
        else {
            // On SQL Server
            storedProcString = "CREATE PROCEDURE " + procName + " @param varchar(3) AS SELECT col3 INTO #TMPTABLE FROM " + tableName
                    + " WHERE col2 = @param SELECT col3 FROM #TMPTABLE";
        }

        stmt.executeUpdate(storedProcString);

        // execute stored proc via pstmt
        pstmt = con.prepareStatement("EXEC " + procName + " ?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        pstmt.setString(1, col3Lookup);

        // should return 1 row
        rs = pstmt.executeQuery();
        rs.last();
        assertEquals(rs.getRow(), numRowsInResult, "getRow mismatch");
        rs.beforeFirst();
        while (rs.next()) {
            assertEquals(rs.getString(1), col3Value, "Value mismatch");
        }

        if (null != stmt)
            stmt.close();
        if (null != con)
            con.close();
    }

    @AfterAll
    public static void terminate() throws SQLException {
        SQLServerConnection con = (SQLServerConnection) DriverManager.getConnection(connectionString);
        Statement stmt = con.createStatement();
        stmt.executeUpdate("drop table if exists" + tableName);
        stmt.executeUpdate("IF EXISTS (select * from sysobjects where id = object_id(N'" + procName + "') and OBJECTPROPERTY(id, N'IsProcedure') = 1)"
                + " DROP PROCEDURE " + procName);

    }

}

@@ -3592,28 +3592,159 @@
 te(%22
-drop table if exists
+IF EXISTS (select * from sysobjects where id = object_id(N'%22 + tableName + %22') and OBJECTPROPERTY(id, N'IsTable') = 1)%22%0A                + %22 DROP TABLE 
 %22 + 

tweak RegressionTest for SQL_2014