Repository: microsoft/mssql-jdbc
Pull request #1719 (2021-12-17 17:48:28)

Added new connection string property prepareMethod to toggle use of sp_prepare

otbutz: I'd rather take the approach of jTDS as it allows to add different strategies in the future without breaking compatibility.
lilgreenbird: - can we also add a test that test the property in connection string? 
- we should add this property to updateDataSource in AbstractTest
- should add tests for this property when AE is enabled

Patch:
diff --git a/src/main/java/com/microsoft/sqlserver/jdbc/IOBuffer.java b/src/main/java/com/microsoft/sqlserver/jdbc/IOBuffer.java
index 1bb797c2..c0fe5b46 100644
--- a/src/main/java/com/microsoft/sqlserver/jdbc/IOBuffer.java
+++ b/src/main/java/com/microsoft/sqlserver/jdbc/IOBuffer.java
@@ -7154,13 +7154,13 @@ final class TDSReader implements Serializable {
      * @param valueLength
      * @throws SQLServerException
      */
-    final void readSkipBytes(long valueLength) throws SQLServerException {
-        for (long bytesSkipped = 0; bytesSkipped < valueLength;) {
+    final void readSkipBytes(int valueLength) throws SQLServerException {
+        for (int bytesSkipped = 0; bytesSkipped < valueLength;) {
             // Ensure that we have a packet to read from.
             if (!ensurePayload())
                 throwInvalidTDS();
 
-            long bytesToSkip = valueLength - bytesSkipped;
+            int bytesToSkip = valueLength - bytesSkipped;
             if (bytesToSkip > currentPacket.payloadLength - payloadOffset)
                 bytesToSkip = currentPacket.payloadLength - payloadOffset;
 
diff --git a/src/main/java/com/microsoft/sqlserver/jdbc/ISQLServerConnection.java b/src/main/java/com/microsoft/sqlserver/jdbc/ISQLServerConnection.java
index b3e0b249..a74a3bd1 100644
--- a/src/main/java/com/microsoft/sqlserver/jdbc/ISQLServerConnection.java
+++ b/src/main/java/com/microsoft/sqlserver/jdbc/ISQLServerConnection.java
@@ -278,11 +278,26 @@ public interface ISQLServerConnection extends java.sql.Connection {
      */
     void setEnablePrepareOnFirstPreparedStatementCall(boolean value);
 
+    /**
+     * Returns the behavior for a specific connection instance. {@link PrepareMethod}
+     *
+     * @return Returns current setting for prepareMethod connection property.
+     */
+    String getPrepareMethod();
+
+    /**
+     * Sets the behavior for the prepare method. {@link PrepareMethod}
+     *
+     * @param prepareMethod
+     *        Changes the setting as per description
+     */
+    void setPrepareMethod(String prepareMethod);
+
     /**
      * Returns the behavior for a specific connection instance. This setting controls how many outstanding prepared
      * statement discard actions (sp_unprepare) can be outstanding per connection before a call to clean-up the
      * outstanding handles on the server is executed. If the setting is {@literal <=} 1, unprepare actions will be
-     * executed immedietely on prepared statement close. If it is set to {@literal >} 1, these calls will be batched
+     * executed immediately on prepared statement close. If it is set to {@literal >} 1, these calls will be batched
      * together to avoid overhead of calling sp_unprepare too often. The default for this option can be changed by
      * calling getDefaultServerPreparedStatementDiscardThreshold().
      * 
diff --git a/src/main/java/com/microsoft/sqlserver/jdbc/ISQLServerDataSource.java b/src/main/java/com/microsoft/sqlserver/jdbc/ISQLServerDataSource.java
index 0ecbada3..76db4be5 100644
--- a/src/main/java/com/microsoft/sqlserver/jdbc/ISQLServerDataSource.java
+++ b/src/main/java/com/microsoft/sqlserver/jdbc/ISQLServerDataSource.java
@@ -1038,7 +1038,7 @@ public interface ISQLServerDataSource extends javax.sql.CommonDataSource {
 
     /**
      * Returns the current flag for value sendTemporalDataTypesAsStringForBulkCopy
-     * 
+     *
      * @return 'sendTemporalDataTypesAsStringForBulkCopy' property value.
      */
     boolean getSendTemporalDataTypesAsStringForBulkCopy();
@@ -1126,4 +1126,19 @@ public interface ISQLServerDataSource extends javax.sql.CommonDataSource {
      * @return interval in seconds
      */
     int getConnectRetryInterval();
+
+    /**
+     * Sets the behavior for the prepare method. {@link PrepareMethod}
+     *
+     * @param prepareMethod
+     *        Changes the setting as per description
+     */
+    void setPrepareMethod(String prepareMethod);
+
+    /**
+     * Returns the value indicating the prepare method. {@link PrepareMethod}
+     *
+     * @return prepare method
+     */
+    String getPrepareMethod();
 }
diff --git a/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerConnection.java b/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerConnection.java
index 970eff29..1171a613 100644
--- a/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerConnection.java
+++ b/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerConnection.java
@@ -123,6 +123,8 @@ public class SQLServerConnection implements ISQLServerConnection, java.io.Serial
     /** Current limit for this particular connection. */
     private Boolean enablePrepareOnFirstPreparedStatementCall = null;
 
+    private String prepareMethod = null;
+
     /** Handle the actual queue of discarded prepared statements. */
     private ConcurrentLinkedQueue<PreparedStatementHandle> discardedPreparedStatementHandles = new ConcurrentLinkedQueue<>();
 
@@ -2043,6 +2045,14 @@ public class SQLServerConnection implements ISQLServerConnection, java.io.Serial
                 }
                 transparentNetworkIPResolution = isBooleanPropertyOn(sPropKey, sPropValue);
 
+                sPropKey = SQLServerDriverStringProperty.PREPARE_METHOD.toString();
+                sPropValue = activeConnectionProperties.getProperty(sPropKey);
+                if (null == sPropValue) {
+                    sPropValue = SQLServerDriverStringProperty.PREPARE_METHOD.getDefaultValue();
+                    activeConnectionProperties.setProperty(sPropKey, sPropValue);
+                }
+                setPrepareMethod(PrepareMethod.valueOfString(sPropValue).toString());
+
                 sPropKey = SQLServerDriverBooleanProperty.ENCRYPT.toString();
                 sPropValue = activeConnectionProperties.getProperty(sPropKey);
                 if (null == sPropValue) {
@@ -5328,6 +5338,10 @@ public class SQLServerConnection implements ISQLServerConnection, java.io.Serial
                 break;
             } else if (authenticationString
                     .equalsIgnoreCase(SqlAuthentication.ActiveDirectoryServicePrincipal.toString())) {
+                if (!msalContextExists()) {
+                    MessageFormat form = new MessageFormat(SQLServerException.getErrString("R_MSALMissing"));
+                    throw new SQLServerException(form.format(new Object[] {authenticationString}), null, 0, null);
+                }
 
                 // aadPrincipalID and aadPrincipalSecret is deprecated replaced by username and password
                 if (aadPrincipalID != null && !aadPrincipalID.isEmpty() && aadPrincipalSecret != null
@@ -7219,6 +7233,20 @@ public class SQLServerConnection implements ISQLServerConnection, java.io.Serial
         this.enablePrepareOnFirstPreparedStatementCall = value;
     }
 
+    @Override
+    public String getPrepareMethod() {
+        if (null == this.prepareMethod) {
+            return SQLServerDriverStringProperty.PREPARE_METHOD.getDefaultValue();
+        }
+
+        return this.prepareMethod;
+    }
+
+    @Override
+    public void setPrepareMethod(String prepareMethod) {
+        this.prepareMethod = prepareMethod;
+    }
+
     @Override
     public int getServerPreparedStatementDiscardThreshold() {
         if (0 > this.serverPreparedStatementDiscardThreshold)
diff --git a/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerConnectionPoolProxy.java b/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerConnectionPoolProxy.java
index d31a429c..91678f39 100644
--- a/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerConnectionPoolProxy.java
+++ b/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerConnectionPoolProxy.java
@@ -550,6 +550,16 @@ class SQLServerConnectionPoolProxy implements ISQLServerConnection, java.io.Seri
         wrappedConnection.setEnablePrepareOnFirstPreparedStatementCall(value);
     }
 
+    @Override
+    public String getPrepareMethod() {
+        return wrappedConnection.getPrepareMethod();
+    }
+
+    @Override
+    public void setPrepareMethod(String prepareMethod) {
+        wrappedConnection.setPrepareMethod(prepareMethod);
+    }
+
     @Override
     public int getServerPreparedStatementDiscardThreshold() {
         return wrappedConnection.getServerPreparedStatementDiscardThreshold();
diff --git a/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerDataSource.java b/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerDataSource.java
index b51b6fce..817236ea 100644
--- a/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerDataSource.java
+++ b/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerDataSource.java
@@ -1125,6 +1125,17 @@ public class SQLServerDataSource
                 SQLServerDriverIntProperty.CONNECT_RETRY_INTERVAL.getDefaultValue());
     }
 
+    @Override
+    public void setPrepareMethod(String prepareMethod) {
+        setStringProperty(connectionProps, SQLServerDriverStringProperty.PREPARE_METHOD.toString(), prepareMethod);
+    }
+
+    @Override
+    public String getPrepareMethod() {
+        return getStringProperty(connectionProps, SQLServerDriverStringProperty.PREPARE_METHOD.toString(),
+                SQLServerDriverStringProperty.PREPARE_METHOD.getDefaultValue());
+    }
+
     /**
      * Sets a property string value.
      *
diff --git a/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerDriver.java b/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerDriver.java
index ab93a1d8..3168091b 100644
--- a/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerDriver.java
+++ b/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerDriver.java
@@ -334,10 +334,40 @@ enum SQLServerDriverObjectProperty {
     }
 }
 
+enum PrepareMethod {
+    PREPEXEC("prepexec"), //sp_prepexec, default prepare method
+    PREPARE("prepare");
+
+    private final String value;
+
+    private PrepareMethod(String value) {
+        this.value = value;
+    }
+
+    @Override
+    public String toString() {
+        return value;
+    }
+
+    static PrepareMethod valueOfString(String value) throws SQLServerException {
+        assert value != null;
+
+        for (PrepareMethod prepareMethod : PrepareMethod.values()) {
+            if (prepareMethod.toString().equals(value)) {
+                return prepareMethod;
+            }
+        }
+
+        MessageFormat form = new MessageFormat(SQLServerException.getErrString("R_InvalidConnectionSetting"));
+        Object[] msgArgs = {SQLServerDriverStringProperty.PREPARE_METHOD.toString(), value};
+        throw new SQLServerException(form.format(msgArgs), null);
+    }
+}
 
 enum SQLServerDriverStringProperty {
     APPLICATION_INTENT("applicationIntent", ApplicationIntent.READ_WRITE.toString()),
     APPLICATION_NAME("applicationName", SQLServerDriver.DEFAULT_APP_NAME),
+    PREPARE_METHOD("prepareMethod", PrepareMethod.PREPEXEC.toString()),
     DATABASE_NAME("databaseName", ""),
     FAILOVER_PARTNER("failoverPartner", ""),
     HOSTNAME_IN_CERTIFICATE("hostNameInCertificate", ""),
@@ -515,6 +545,9 @@ public final class SQLServerDriver implements java.sql.Driver {
             new SQLServerDriverPropertyInfo(SQLServerDriverBooleanProperty.DISABLE_STATEMENT_POOLING.toString(),
                     Boolean.toString(SQLServerDriverBooleanProperty.DISABLE_STATEMENT_POOLING.getDefaultValue()), false,
                     new String[] {"true"}),
+            new SQLServerDriverPropertyInfo(SQLServerDriverStringProperty.PREPARE_METHOD.toString(),
+                    SQLServerDriverStringProperty.PREPARE_METHOD.getDefaultValue(),false,
+                    new String[] {PrepareMethod.PREPEXEC.toString(), PrepareMethod.PREPARE.toString()}),
             new SQLServerDriverPropertyInfo(SQLServerDriverBooleanProperty.ENCRYPT.toString(),
                     Boolean.toString(SQLServerDriverBooleanProperty.ENCRYPT.getDefaultValue()), false, TRUE_FALSE),
             new SQLServerDriverPropertyInfo(SQLServerDriverStringProperty.FAILOVER_PARTNER.toString(),
diff --git a/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerPreparedStatement.java b/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerPreparedStatement.java
index 17bd4a5f..af01c707 100644
--- a/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerPreparedStatement.java
+++ b/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerPreparedStatement.java
@@ -75,6 +75,9 @@ public class SQLServerPreparedStatement extends SQLServerStatement implements IS
     /** True if this execute has been called for this statement at least once */
     private boolean isExecutedAtLeastOnce = false;
 
+    /** True if sp_prepare was called **/
+    private boolean isSpPrepareExecuted = false;
+
     /** Reference to cache item for statement handle pooling. Only used to decrement ref count on statement close. */
     private PreparedStatementHandle cachedPreparedStatementHandle;
 
@@ -608,7 +611,7 @@ public class SQLServerPreparedStatement extends SQLServerStatement implements IS
                 // continue using it after we return.
                 TDSWriter tdsWriter = command.startRequest(TDS.PKT_RPC);
 
-                needsPrepare = doPrepExec(tdsWriter, inOutParam, hasNewTypeDefinitions, hasExistingTypeDefinitions);
+                needsPrepare = doPrepExec(tdsWriter, inOutParam, hasNewTypeDefinitions, hasExistingTypeDefinitions, command);
 
                 ensureExecuteResultsReader(command.startResponse(getIsResponseBufferingAdaptive()));
                 startResults();
@@ -760,6 +763,31 @@ public class SQLServerPreparedStatement extends SQLServerStatement implements IS
         tdsWriter.writeRPCInt(null, 0, true);
     }
 
+    private void buildPrepParams(TDSWriter tdsWriter) throws SQLServerException {
+        if (getStatementLogger().isLoggable(java.util.logging.Level.FINE))
+            getStatementLogger().fine(toString() + ": calling sp_prepare: PreparedHandle:"
+                    + getPreparedStatementHandle() + ", SQL:" + preparedSQL);
+
+        expectPrepStmtHandle = true;
+        executedSqlDirectly = false;
+        expectCursorOutParams = false;
+        outParamIndexAdjustment = 4;
+
+        tdsWriter.writeShort((short) 0xFFFF); // procedure name length -> use ProcIDs
+        tdsWriter.writeShort(TDS.PROCID_SP_PREPARE);
+        tdsWriter.writeByte((byte) 0);
+        tdsWriter.writeByte((byte) 0);
+        tdsWriter.sendEnclavePackage(preparedSQL, enclaveCEKs);
+
+        tdsWriter.writeRPCInt(null, getPreparedStatementHandle(), true);
+        resetPrepStmtHandle(false);
+
+        tdsWriter.writeRPCStringUnicode((preparedTypeDefinitions.length() > 0) ? preparedTypeDefinitions : null);
+
+        tdsWriter.writeRPCStringUnicode(preparedSQL);
+        tdsWriter.writeRPCInt(null, 1, false);
+    }
+
     private void buildPrepExecParams(TDSWriter tdsWriter) throws SQLServerException {
         if (getStatementLogger().isLoggable(java.util.logging.Level.FINE))
             getStatementLogger().fine(toString() + ": calling sp_prepexec: PreparedHandle:"
@@ -1050,29 +1078,43 @@ public class SQLServerPreparedStatement extends SQLServerStatement implements IS
     private ArrayList<byte[]> enclaveCEKs;
 
     private boolean doPrepExec(TDSWriter tdsWriter, Parameter[] params, boolean hasNewTypeDefinitions,
-            boolean hasExistingTypeDefinitions) throws SQLServerException {
+            boolean hasExistingTypeDefinitions, TDSCommand command) throws SQLServerException {
 
         boolean needsPrepare = (hasNewTypeDefinitions && hasExistingTypeDefinitions) || !hasPreparedStatementHandle();
+        boolean isPrepareMethodSpPrepExec = connection.getPrepareMethod().equals(PrepareMethod.PREPEXEC.toString());
 
         // Cursors don't use statement pooling.
         if (isCursorable(executeMethod)) {
-
             if (needsPrepare)
                 buildServerCursorPrepExecParams(tdsWriter);
             else
                 buildServerCursorExecParams(tdsWriter);
         } else {
             // Move overhead of needing to do prepare & unprepare to only use cases that need more than one execution.
-            // First execution, use sp_executesql, optimizing for asumption we will not re-use statement.
+            // First execution, use sp_executesql, optimizing for assumption we will not re-use statement.
             if (needsPrepare && !connection.getEnablePrepareOnFirstPreparedStatementCall() && !isExecutedAtLeastOnce) {
                 buildExecSQLParams(tdsWriter);
                 isExecutedAtLeastOnce = true;
-            }
-            // Second execution, use prepared statements since we seem to be re-using it.
-            else if (needsPrepare)
-                buildPrepExecParams(tdsWriter);
-            else
+            } else if (needsPrepare) { // Second execution, use prepared statements since we seem to be re-using it.
+                if (isPrepareMethodSpPrepExec) { // If true, we're using sp_prepexec.
+                    buildPrepExecParams(tdsWriter);
+                } else { // Otherwise, we're using sp_prepare instead of sp_prepexec.
+                    isSpPrepareExecuted = true;
+                    // If we're preparing for a statement in a batch we just need to call sp_prepare because in the
+                    // "batching" code it will start another tds request to execute the statement after preparing.
+                    if (executeMethod == EXECUTE_BATCH) {
+                        buildPrepParams(tdsWriter);
+                        return needsPrepare;
+                    } else { // Otherwise, if it is not a batch query, then prepare and start new TDS request to execute the statement.
+                        isSpPrepareExecuted = false;
+                        doPrep(tdsWriter, command);
+                        command.startRequest(TDS.PKT_RPC);
+                        buildExecParams(tdsWriter);
+                    }
+                }
+            } else {
                 buildExecParams(tdsWriter);
+            }
         }
 
         sendParamsByRPC(tdsWriter, params);
@@ -1080,6 +1122,18 @@ public class SQLServerPreparedStatement extends SQLServerStatement implements IS
         return needsPrepare;
     }
 
+    /**
+     * Executes sp_prepare to prepare a parameterized statement and sets the prepared statement handle
+     *
+     * @param tdsWriter TDS writer to write sp_prepare params to
+     * @throws SQLServerException
+     */
+    private void doPrep(TDSWriter tdsWriter, TDSCommand command) throws SQLServerException {
+        buildPrepParams(tdsWriter);
+        ensureExecuteResultsReader(command.startResponse(getIsResponseBufferingAdaptive()));
+        command.processResponse(resultsReader());
+    }
+
     @Override
     public final java.sql.ResultSetMetaData getMetaData() throws SQLServerException, SQLTimeoutException {
         loggerExternal.entering(getClassNameLogging(), "getMetaData");
@@ -2718,7 +2772,6 @@ public class SQLServerPreparedStatement extends SQLServerStatement implements IS
 
     final void doExecutePreparedStatementBatch(PrepStmtBatchExecCmd batchCommand) throws SQLServerException {
         executeMethod = EXECUTE_BATCH;
-
         batchCommand.batchException = null;
         final int numBatches = batchParamValues.size();
         batchCommand.updateCounts = new long[numBatches];
@@ -2826,7 +2879,7 @@ public class SQLServerPreparedStatement extends SQLServerStatement implements IS
                     // the size of a batch's string parameter values changes such
                     // that repreparation is necessary.
                     ++numBatchesPrepared;
-                    needsPrepare = doPrepExec(tdsWriter, batchParam, hasNewTypeDefinitions, hasExistingTypeDefinitions);
+                    needsPrepare = doPrepExec(tdsWriter, batchParam, hasNewTypeDefinitions, hasExistingTypeDefinitions, batchCommand);
                     if (needsPrepare || numBatchesPrepared == numBatches) {
                         ensureExecuteResultsReader(batchCommand.startResponse(getIsResponseBufferingAdaptive()));
 
@@ -2845,6 +2898,18 @@ public class SQLServerPreparedStatement extends SQLServerStatement implements IS
                                 if (!getNextResult(true))
                                     return;
 
+                                if (isSpPrepareExecuted) {
+                                    isSpPrepareExecuted = false;
+                                    resetForReexecute();
+                                    tdsWriter = batchCommand.startRequest(TDS.PKT_RPC);
+                                    buildExecParams(tdsWriter);
+                                    sendParamsByRPC(tdsWriter, batchParam);
+                                    ensureExecuteResultsReader(batchCommand.startResponse(getIsResponseBufferingAdaptive()));
+                                    startResults();
+                                    if (!getNextResult(true))
+                                        return;
+                                }
+
                                 // If the result is a ResultSet (rather than an update count) then throw an
                                 // exception for this result. The exception gets caught immediately below and
                                 // translated into (or added to) a BatchUpdateException.
diff --git a/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerResource.java b/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerResource.java
index b1ed0b12..94cde8fc 100644
--- a/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerResource.java
+++ b/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerResource.java
@@ -192,6 +192,7 @@ public final class SQLServerResource extends ListResourceBundle {
         {"R_failoverPartnerPropertyDescription", "The name of the failover server used in a database mirroring configuration."},
         {"R_packetSizePropertyDescription", "The network packet size used to communicate with SQL Server."},
         {"R_encryptPropertyDescription", "Determines if Secure Sockets Layer (SSL) encryption should be used between the client and the server."},
+        {"R_prepareMethodPropertyDescription", "Determines the prepare method used in the driver."},
         {"R_socketFactoryClassPropertyDescription", "The class to instantiate as the SocketFactory for connections"},
         {"R_socketFactoryConstructorArgPropertyDescription", "The optional argument to pass to the constructor specified by socketFactoryClass"},
         {"R_trustServerCertificatePropertyDescription", "Determines if the driver should validate the SQL Server Secure Sockets Layer (SSL) certificate."},


Test patch:
diff --git a/src/test/java/com/microsoft/sqlserver/jdbc/AlwaysEncrypted/CallableStatementTest.java b/src/test/java/com/microsoft/sqlserver/jdbc/AlwaysEncrypted/CallableStatementTest.java
index 7fd94b49..2f812f09 100644
--- a/src/test/java/com/microsoft/sqlserver/jdbc/AlwaysEncrypted/CallableStatementTest.java
+++ b/src/test/java/com/microsoft/sqlserver/jdbc/AlwaysEncrypted/CallableStatementTest.java
@@ -12,6 +12,7 @@ import java.math.BigDecimal;
 import java.sql.Connection;
 import java.sql.Date;
 import java.sql.PreparedStatement;
+import java.sql.CallableStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
@@ -22,6 +23,7 @@ import java.util.LinkedList;
 import org.junit.jupiter.api.AfterAll;
 import org.junit.jupiter.api.BeforeAll;
 import org.junit.jupiter.api.Tag;
+import org.junit.jupiter.api.Test;
 import org.junit.jupiter.params.ParameterizedTest;
 import org.junit.jupiter.params.provider.MethodSource;
 import org.junit.runner.RunWith;
@@ -89,6 +91,8 @@ public class CallableStatementTest extends AESetup {
             .escapeIdentifier(RandomUtil.getIdentifier("mixedProcedure3"));
     private static String mixedProcedureNumericPrecisionScale = AbstractSQLGenerator
             .escapeIdentifier(RandomUtil.getIdentifier("mixedProcedureNumericPrecisionScale"));
+    private static String prepareMethodProcedure = AbstractSQLGenerator
+            .escapeIdentifier(RandomUtil.getIdentifier("prepareMethodProcedure"));
 
     private static String table1 = TestUtils.escapeSingleQuotes(
             AbstractSQLGenerator.escapeIdentifier(RandomUtil.getIdentifier("StoredProcedure_table1")));
@@ -308,6 +312,28 @@ public class CallableStatementTest extends AESetup {
         createOutputProcedure4();
     }
 
+    @Test
+    public void testCallableStatementSpPrepareAE() throws SQLException {
+        AETestConnectionString += "prepareMethod=prepare;";
+
+        try (Statement statement = PrepUtil.getConnection(AETestConnectionString, AEInfo).createStatement();) {
+            statement.executeUpdate("create procedure " + prepareMethodProcedure + " as select 1 --");
+
+            try (CallableStatement callableStatement = PrepUtil.getConnection(AETestConnectionString, AEInfo)
+                    .prepareCall("{call " + prepareMethodProcedure + "}")) {
+                try (ResultSet rs = callableStatement.executeQuery()) { // Takes sp_executesql path
+                    rs.next();
+                    assertEquals(1, rs.getInt(1), TestResource.getResource("R_setDataNotEqual"));
+                }
+
+                try (ResultSet rs = callableStatement.executeQuery()) { // Takes sp_prepare path
+                    rs.next();
+                    assertEquals(1, rs.getInt(1), TestResource.getResource("R_setDataNotEqual"));
+                }
+            }
+        }
+    }
+
     private static void dropProcedures() throws SQLException {
         try (Statement stmt = connection.createStatement()) {
             TestUtils.dropProcedureIfExists(multiStatementsProcedure, stmt);
diff --git a/src/test/java/com/microsoft/sqlserver/jdbc/SQLServerConnectionTest.java b/src/test/java/com/microsoft/sqlserver/jdbc/SQLServerConnectionTest.java
index 5330d3db..e91984c0 100644
--- a/src/test/java/com/microsoft/sqlserver/jdbc/SQLServerConnectionTest.java
+++ b/src/test/java/com/microsoft/sqlserver/jdbc/SQLServerConnectionTest.java
@@ -162,6 +162,9 @@ public class SQLServerConnectionTest extends AbstractTest {
         ds.setEncrypt(booleanPropValue);
         assertEquals(booleanPropValue, ds.getEncrypt(), TestResource.getResource("R_valuesAreDifferent"));
 
+        ds.setPrepareMethod(stringPropValue);
+        assertEquals(stringPropValue, ds.getPrepareMethod(), TestResource.getResource("R_valuesAreDifferent"));
+
         ds.setHostNameInCertificate(stringPropValue);
         assertEquals(stringPropValue, ds.getHostNameInCertificate(), TestResource.getResource("R_valuesAreDifferent"));
 
@@ -310,7 +313,7 @@ public class SQLServerConnectionTest extends AbstractTest {
     /**
      * Attach the Event listener and listen for connection events, fatal errors should not close the pooled connection
      * objects
-     * 
+     *
      * @throws SQLException
      */
     @Test
@@ -784,7 +787,7 @@ public class SQLServerConnectionTest extends AbstractTest {
 
     /**
      * Test thread's interrupt status is not cleared.
-     * 
+     *
      * @throws InterruptedException
      */
     @Test
diff --git a/src/test/java/com/microsoft/sqlserver/jdbc/callablestatement/CallableStatementTest.java b/src/test/java/com/microsoft/sqlserver/jdbc/callablestatement/CallableStatementTest.java
index 44621f40..ea02ae37 100644
--- a/src/test/java/com/microsoft/sqlserver/jdbc/callablestatement/CallableStatementTest.java
+++ b/src/test/java/com/microsoft/sqlserver/jdbc/callablestatement/CallableStatementTest.java
@@ -49,6 +49,7 @@ public class CallableStatementTest extends AbstractTest {
     private static String inputParamsProcedureName = RandomUtil.getIdentifier("CallableStatementTest_inputParams_SP");
     private static String getObjectLocalDateTimeProcedureName = RandomUtil.getIdentifier("CallableStatementTest_getObjectLocalDateTime_SP");
     private static String getObjectOffsetDateTimeProcedureName = RandomUtil.getIdentifier("CallableStatementTest_getObjectOffsetDateTime_SP");
+    private static String procName = RandomUtil.getIdentifier("procedureTestCallableStatementSpPrepare");
 
     /**
      * Setup before test
@@ -77,6 +78,28 @@ public class CallableStatementTest extends AbstractTest {
         }
     }
 
+    @Test
+    public void testCallableStatementSpPrepare() throws SQLException {
+        connection.setPrepareMethod("prepare");
+
+        try (Statement statement = connection.createStatement();) {
+            statement.executeUpdate("create procedure " + AbstractSQLGenerator.escapeIdentifier(procName) + " as select 1 --");
+
+            try (CallableStatement callableStatement = connection.prepareCall(
+                    "{call " + AbstractSQLGenerator.escapeIdentifier(procName) + "}")) {
+                try (ResultSet rs = callableStatement.executeQuery()) { // Takes sp_executesql path
+                    rs.next();
+                    assertEquals(1, rs.getInt(1), TestResource.getResource("R_setDataNotEqual"));
+                }
+
+                try (ResultSet rs = callableStatement.executeQuery()) { // Takes sp_prepare path
+                    rs.next();
+                    assertEquals(1, rs.getInt(1), TestResource.getResource("R_setDataNotEqual"));
+                }
+            }
+        }
+    }
+
     /**
      * Tests CallableStatement.getString() with uniqueidentifier parameter
      * 
diff --git a/src/test/java/com/microsoft/sqlserver/jdbc/connection/RequestBoundaryMethodsTest.java b/src/test/java/com/microsoft/sqlserver/jdbc/connection/RequestBoundaryMethodsTest.java
index 187da9dc..dab2329c 100644
--- a/src/test/java/com/microsoft/sqlserver/jdbc/connection/RequestBoundaryMethodsTest.java
+++ b/src/test/java/com/microsoft/sqlserver/jdbc/connection/RequestBoundaryMethodsTest.java
@@ -496,6 +496,8 @@ public class RequestBoundaryMethodsTest extends AbstractTest {
         verifiedMethodNames.add("setUseFmtOnly");
         verifiedMethodNames.add("setDelayLoadingLobs");
         verifiedMethodNames.add("registerColumnEncryptionKeyStoreProvidersOnConnection");
+        verifiedMethodNames.add("getPrepareMethod");
+        verifiedMethodNames.add("setPrepareMethod");
 
         return verifiedMethodNames;
     }
diff --git a/src/test/java/com/microsoft/sqlserver/jdbc/unit/statement/BatchExecutionTest.java b/src/test/java/com/microsoft/sqlserver/jdbc/unit/statement/BatchExecutionTest.java
index 8cb258a0..718dc801 100644
--- a/src/test/java/com/microsoft/sqlserver/jdbc/unit/statement/BatchExecutionTest.java
+++ b/src/test/java/com/microsoft/sqlserver/jdbc/unit/statement/BatchExecutionTest.java
@@ -60,6 +60,15 @@ public class BatchExecutionTest extends AbstractTest {
         testExecuteBatch1UseBulkCopyAPI();
     }
 
+    @Test
+    public void testBatchSpPrepare() throws Exception {
+        connectionString += "prepareMethod=prepare;";
+        testAddBatch1();
+        testExecuteBatch1();
+        testAddBatch1UseBulkCopyAPI();
+        testExecuteBatch1UseBulkCopyAPI();
+    }
+
     /**
      * Get a PreparedStatement object and call the addBatch() method with 3 SQL statements and call the executeBatch()
      * method and it should return array of Integer values of length 3
diff --git a/src/test/java/com/microsoft/sqlserver/jdbc/unit/statement/PreparedStatementTest.java b/src/test/java/com/microsoft/sqlserver/jdbc/unit/statement/PreparedStatementTest.java
index baddd452..061f1468 100644
--- a/src/test/java/com/microsoft/sqlserver/jdbc/unit/statement/PreparedStatementTest.java
+++ b/src/test/java/com/microsoft/sqlserver/jdbc/unit/statement/PreparedStatementTest.java
@@ -4,6 +4,7 @@
  */
 package com.microsoft.sqlserver.jdbc.unit.statement;
 
+import static org.junit.jupiter.api.Assertions.assertEquals;
 import static org.junit.jupiter.api.Assertions.assertNotSame;
 import static org.junit.jupiter.api.Assertions.assertNull;
 import static org.junit.jupiter.api.Assertions.assertSame;
@@ -15,6 +16,7 @@ import java.sql.BatchUpdateException;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
+import java.sql.PreparedStatement;
 import java.util.Random;
 import java.util.UUID;
 import java.util.concurrent.CountDownLatch;
@@ -43,8 +45,9 @@ import com.microsoft.sqlserver.testframework.PrepUtil;
 @RunWith(JUnitPlatform.class)
 public class PreparedStatementTest extends AbstractTest {
 
-    final String tableName = RandomUtil.getIdentifier("#update1");
-    final String tableName2 = RandomUtil.getIdentifier("#update2");
+    final String tableName = RandomUtil.getIdentifier("tableTestStatementPoolingInternal1");
+    final String tableName2 = RandomUtil.getIdentifier("tableTestStatementPoolingInternal2");
+    final String tableName3 = RandomUtil.getIdentifier("tableTestPreparedStatementWithSpPrepare");
 
     @BeforeAll
     public static void setupTests() throws Exception {
@@ -70,6 +73,73 @@ public class PreparedStatementTest extends AbstractTest {
         }
     }
 
+    @Test
+    public void testSpPrepareConfigurationConnectionPropValues() throws SQLException {
+        String connectionStringPrepare = connectionString + ";prepareMethod=prepare;";
+        try (SQLServerConnection conn = (SQLServerConnection) PrepUtil.getConnection(connectionStringPrepare)) {
+            assertEquals("prepare", conn.getPrepareMethod());
+        }
+
+        try (SQLServerConnection conn = (SQLServerConnection) getConnection()) {
+            assertEquals("prepexec", conn.getPrepareMethod()); // default is prepexec
+        }
+    }
+
+    @Test
+    public void testPreparedStatementWithSpPrepare() throws SQLException {
+        String sql = "insert into " + AbstractSQLGenerator.escapeIdentifier(tableName3)
+                + " (c1_nchar, c2_int) values (?, ?)";
+
+        try (SQLServerConnection con = (SQLServerConnection) getConnection()) {
+            con.setPrepareMethod("prepare"); // Use sp_prepare rather than sp_prepexec
+
+            executeSQL(con, "create table " + AbstractSQLGenerator.escapeIdentifier(tableName3)
+                    + " (c1_nchar nchar(512), c2_int integer)");
+
+            try (SQLServerPreparedStatement ps = (SQLServerPreparedStatement) con.prepareStatement(sql)) {
+                ps.setString(1, "test");
+                ps.setInt(2, 0);
+                ps.executeUpdate();
+                ps.executeUpdate(); // Takes sp_prepare path
+                ps.executeUpdate();
+            }
+        }
+    }
+
+    @Test
+    public void testPreparedStatementPoolEvictionWithSpPrepare() throws SQLException {
+        int cacheSize = 3;
+        int discardStatementCount = 3;
+
+        try (SQLServerConnection con = (SQLServerConnection) getConnection()) {
+
+            con.setPrepareMethod("prepare"); // Use sp_prepare rather than sp_prepexec
+            con.setDisableStatementPooling(false);
+            con.setStatementPoolingCacheSize(cacheSize);
+            con.setServerPreparedStatementDiscardThreshold(discardStatementCount);
+
+            String query = "select 1 --";
+
+            for (int i = 0; i < cacheSize; i++) {
+                try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con
+                        .prepareStatement(query + i)) {
+                    pstmt.execute(); // sp_executesql
+                    pstmt.execute(); // sp_prepare and sp_execute, handle created and cached
+                }
+                // No handles in discard queue
+                assertSame(0, con.getDiscardedServerPreparedStatementCount());
+            }
+
+            try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con
+                    .prepareStatement(query + cacheSize)) {
+                pstmt.execute(); // sp_executesql
+                pstmt.execute(); // sp_prepare and sp_execute, handle created and cached
+            }
+            // Handle should be discarded
+            assertSame(1, con.getDiscardedServerPreparedStatementCount());
+        }
+    }
+
     /**
      * Test handling of unpreparing prepared statements.
      * 
diff --git a/src/test/java/com/microsoft/sqlserver/testframework/AbstractTest.java b/src/test/java/com/microsoft/sqlserver/testframework/AbstractTest.java
index 496cfa69..7a563b93 100644
--- a/src/test/java/com/microsoft/sqlserver/testframework/AbstractTest.java
+++ b/src/test/java/com/microsoft/sqlserver/testframework/AbstractTest.java
@@ -377,6 +377,9 @@ public abstract class AbstractTest {
                         case Constants.CONNECT_RETRY_INTERVAL:
                             ds.setConnectRetryInterval(Integer.parseInt(value));
                             break;
+                        case Constants.PREPARE_METHOD:
+                            ds.setPrepareMethod(value);
+                            break;
                         default:
                             break;
                     }
diff --git a/src/test/java/com/microsoft/sqlserver/testframework/Constants.java b/src/test/java/com/microsoft/sqlserver/testframework/Constants.java
index acac806c..00ba22ed 100644
--- a/src/test/java/com/microsoft/sqlserver/testframework/Constants.java
+++ b/src/test/java/com/microsoft/sqlserver/testframework/Constants.java
@@ -167,6 +167,7 @@ public final class Constants {
 
     public static final String CLIENT_KEY_PASSWORD = "CLIENTKEYPASSWORD";
     public static final String SEND_TEMPORAL_DATATYPES_AS_STRING_FOR_BULK_COPY = "SENDTEMPORALDATATYPESASSTRINGFORBULKCOPY";
+    public static final String PREPARE_METHOD = "PREPAREMETHOD";
     public static final String CONFIG_PROPERTIES_FILE = "config.properties";
 
     public enum LOB {