Examples with SQLServerConnection com.microsoft.sqlserver.jdbc.SQLServerConnection used on opensource projects

Example 6 with SQLServerConnection

use of com.microsoft.sqlserver.jdbc.SQLServerConnection in project mssql-jdbc by Microsoft.

the class ConnectionDriverTest method testDeadConnection.

@Test
public void testDeadConnection() throws SQLException {
    assumeTrue(!DBConnection.isSqlAzure(DriverManager.getConnection(connectionString)), "Skipping test case on Azure SQL.");
    try (SQLServerConnection conn = (SQLServerConnection) DriverManager.getConnection(connectionString + ";responseBuffering=adaptive")) {
        Statement stmt = null;
        String tableName = RandomUtil.getIdentifier("Table");
        tableName = DBTable.escapeIdentifier(tableName);
        conn.setAutoCommit(false);
        stmt = conn.createStatement();
        stmt.executeUpdate("CREATE TABLE " + tableName + " (col1 int primary key)");
        for (int i = 0; i < 80; i++) {
            stmt.executeUpdate("INSERT INTO " + tableName + "(col1) values (" + i + ")");
        }
        conn.commit();
        try {
            stmt.execute("SELECT x1.col1 as foo, x2.col1 as bar, x1.col1 as eeep FROM " + tableName + " as x1, " + tableName + " as x2; RAISERROR ('Oops', 21, 42) WITH LOG");
        } catch (SQLException e) {
            assertEquals(e.getMessage(), "Connection reset", "Unknown Exception");
        } finally {
            DriverManager.getConnection(connectionString).createStatement().execute("drop table " + tableName);
        }
        assertEquals(conn.isValid(5), false, "Dead connection should be invalid");
    }
}

Example 7 with SQLServerConnection

use of com.microsoft.sqlserver.jdbc.SQLServerConnection in project mssql-jdbc by Microsoft.

the class ConnectionDriverTest method testGetSchema.

@Test
public void testGetSchema() throws SQLException {
    SQLServerConnection conn = (SQLServerConnection) DriverManager.getConnection(connectionString);
    conn.getSchema();
}

Example 8 with SQLServerConnection

use of com.microsoft.sqlserver.jdbc.SQLServerConnection in project mssql-jdbc by Microsoft.

the class TimeoutTest method testQueryTimeout.

/**
 * When query timeout occurs, the connection is still usable.
 * @throws Exception
 */
@Test
public void testQueryTimeout() throws Exception {
    SQLServerConnection conn = (SQLServerConnection) DriverManager.getConnection(connectionString);
    dropWaitForDelayProcedure(conn);
    createWaitForDelayPreocedure(conn);
    conn = (SQLServerConnection) DriverManager.getConnection(connectionString + ";queryTimeout=" + (waitForDelaySeconds / 2) + ";");
    try {
        conn.createStatement().execute("exec " + waitForDelaySPName);
        throw new Exception("Exception for queryTimeout is not thrown.");
    } catch (Exception e) {
        if (!(e instanceof java.sql.SQLTimeoutException)) {
            throw e;
        }
        assertEquals(e.getMessage(), "The query has timed out.", "Invalid exception message");
    }
    try {
        conn.createStatement().execute("SELECT @@version");
    } catch (Exception e) {
        fail("Unexpected error message occured! " + e.toString());
    }
}

Example 9 with SQLServerConnection

use of com.microsoft.sqlserver.jdbc.SQLServerConnection in project mssql-jdbc by Microsoft.

the class PreparedStatementTest method testStatementPoolingPreparedStatementExecAndUnprepareConfig.

/**
 * Test handling of the two configuration knobs related to prepared statement handling.
 *
 * @throws SQLException
 */
@Test
public void testStatementPoolingPreparedStatementExecAndUnprepareConfig() throws SQLException {
    // Test Data Source properties
    SQLServerDataSource dataSource = new SQLServerDataSource();
    dataSource.setURL(connectionString);
    // Verify defaults.
    assertTrue(0 == dataSource.getStatementPoolingCacheSize());
    // Verify change
    dataSource.setStatementPoolingCacheSize(0);
    assertSame(0, dataSource.getStatementPoolingCacheSize());
    dataSource.setEnablePrepareOnFirstPreparedStatementCall(!dataSource.getEnablePrepareOnFirstPreparedStatementCall());
    dataSource.setServerPreparedStatementDiscardThreshold(dataSource.getServerPreparedStatementDiscardThreshold() + 1);
    // Verify connection from data source has same parameters.
    SQLServerConnection connDataSource = (SQLServerConnection) dataSource.getConnection();
    assertSame(dataSource.getStatementPoolingCacheSize(), connDataSource.getStatementPoolingCacheSize());
    assertSame(dataSource.getEnablePrepareOnFirstPreparedStatementCall(), connDataSource.getEnablePrepareOnFirstPreparedStatementCall());
    assertSame(dataSource.getServerPreparedStatementDiscardThreshold(), connDataSource.getServerPreparedStatementDiscardThreshold());
    // Test connection string properties.
    // Test disableStatementPooling
    String connectionStringDisableStatementPooling = connectionString + ";disableStatementPooling=true;";
    SQLServerConnection connectionDisableStatementPooling = (SQLServerConnection) DriverManager.getConnection(connectionStringDisableStatementPooling);
    // to turn on caching and check if disableStatementPooling is true, even setting cachesize won't matter and will disable it.
    connectionDisableStatementPooling.setStatementPoolingCacheSize(10);
    assertSame(10, connectionDisableStatementPooling.getStatementPoolingCacheSize());
    assertTrue(!connectionDisableStatementPooling.isStatementPoolingEnabled());
    String connectionStringEnableStatementPooling = connectionString + ";disableStatementPooling=false;";
    SQLServerConnection connectionEnableStatementPooling = (SQLServerConnection) DriverManager.getConnection(connectionStringEnableStatementPooling);
    // to turn on caching.
    connectionEnableStatementPooling.setStatementPoolingCacheSize(10);
    // for now, it won't affect if disable is false or true. Since statementPoolingCacheSize is set to 0 as default.
    assertTrue(0 < connectionEnableStatementPooling.getStatementPoolingCacheSize());
    // If only disableStatementPooling is set to true, it makes sure that statementPoolingCacheSize is zero, thus disabling the prepared statement metadata caching.
    assertTrue(connectionEnableStatementPooling.isStatementPoolingEnabled());
    String connectionPropertyStringEnableStatementPooling = connectionString + ";disableStatementPooling=false;statementPoolingCacheSize=10";
    SQLServerConnection connectionPropertyEnableStatementPooling = (SQLServerConnection) DriverManager.getConnection(connectionPropertyStringEnableStatementPooling);
    // for now, it won't affect if disable is false or true. Since statementPoolingCacheSize is set to 0 as default.
    assertTrue(0 < connectionPropertyEnableStatementPooling.getStatementPoolingCacheSize());
    // If only disableStatementPooling is set to true, it makes sure that statementPoolingCacheSize is zero, thus disabling the prepared statement metadata caching.
    assertTrue(connectionPropertyEnableStatementPooling.isStatementPoolingEnabled());
    String connectionPropertyStringDisableStatementPooling = connectionString + ";disableStatementPooling=true;statementPoolingCacheSize=10";
    SQLServerConnection connectionPropertyDisableStatementPooling = (SQLServerConnection) DriverManager.getConnection(connectionPropertyStringDisableStatementPooling);
    // for now, it won't affect if disable is false or true. Since statementPoolingCacheSize is set to 0 as default.
    assertTrue(0 < connectionPropertyDisableStatementPooling.getStatementPoolingCacheSize());
    // If only disableStatementPooling is set to true, it makes sure that statementPoolingCacheSize is zero, thus disabling the prepared statement metadata caching.
    assertTrue(!connectionPropertyDisableStatementPooling.isStatementPoolingEnabled());
    String connectionPropertyStringDisableStatementPooling2 = connectionString + ";disableStatementPooling=false;statementPoolingCacheSize=0";
    SQLServerConnection connectionPropertyDisableStatementPooling2 = (SQLServerConnection) DriverManager.getConnection(connectionPropertyStringDisableStatementPooling2);
    // for now, it won't affect if disable is false or true. Since statementPoolingCacheSize is set to 0 as default.
    assertTrue(0 == connectionPropertyDisableStatementPooling2.getStatementPoolingCacheSize());
    // If only disableStatementPooling is set to true, it makes sure that statementPoolingCacheSize is zero, thus disabling the prepared statement metadata caching.
    assertTrue(!connectionPropertyDisableStatementPooling2.isStatementPoolingEnabled());
    // Test EnablePrepareOnFirstPreparedStatementCall
    String connectionStringNoExecuteSQL = connectionString + ";enablePrepareOnFirstPreparedStatementCall=true;";
    SQLServerConnection connectionNoExecuteSQL = (SQLServerConnection) DriverManager.getConnection(connectionStringNoExecuteSQL);
    assertSame(true, connectionNoExecuteSQL.getEnablePrepareOnFirstPreparedStatementCall());
    // Test ServerPreparedStatementDiscardThreshold
    String connectionStringThreshold3 = connectionString + ";ServerPreparedStatementDiscardThreshold=3;";
    SQLServerConnection connectionThreshold3 = (SQLServerConnection) DriverManager.getConnection(connectionStringThreshold3);
    assertSame(3, connectionThreshold3.getServerPreparedStatementDiscardThreshold());
    // Test combination of EnablePrepareOnFirstPreparedStatementCall and ServerPreparedStatementDiscardThreshold
    String connectionStringThresholdAndNoExecuteSQL = connectionString + ";ServerPreparedStatementDiscardThreshold=3;enablePrepareOnFirstPreparedStatementCall=true;";
    SQLServerConnection connectionThresholdAndNoExecuteSQL = (SQLServerConnection) DriverManager.getConnection(connectionStringThresholdAndNoExecuteSQL);
    assertSame(true, connectionThresholdAndNoExecuteSQL.getEnablePrepareOnFirstPreparedStatementCall());
    assertSame(3, connectionThresholdAndNoExecuteSQL.getServerPreparedStatementDiscardThreshold());
    // Test that an error is thrown for invalid connection string property values (non int/bool).
    try {
        String connectionStringThresholdError = connectionString + ";ServerPreparedStatementDiscardThreshold=hej;";
        DriverManager.getConnection(connectionStringThresholdError);
        fail("Error for invalid ServerPreparedStatementDiscardThresholdexpected.");
    } catch (SQLException e) {
    // Good!
    }
    try {
        String connectionStringNoExecuteSQLError = connectionString + ";enablePrepareOnFirstPreparedStatementCall=dobidoo;";
        DriverManager.getConnection(connectionStringNoExecuteSQLError);
        fail("Error for invalid enablePrepareOnFirstPreparedStatementCall expected.");
    } catch (SQLException e) {
    // Good!
    }
    // Verify instance setting is followed.
    try (SQLServerConnection con = (SQLServerConnection) DriverManager.getConnection(connectionString)) {
        // Turn off use of prepared statement cache.
        con.setStatementPoolingCacheSize(0);
        String query = "/*unprepSettingsTest*/SELECT * FROM sys.objects;";
        // Verify initial default is not serial:
        assertTrue(1 < con.getServerPreparedStatementDiscardThreshold());
        // Verify first use is batched.
        try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement(query)) {
            // sp_executesql
            pstmt.execute();
            // sp_prepexec
            pstmt.execute();
        }
        // Verify that the un-prepare action was not handled immediately.
        assertSame(1, con.getDiscardedServerPreparedStatementCount());
        // Force un-prepares.
        con.closeUnreferencedPreparedStatementHandles();
        // Verify that queue is now empty.
        assertSame(0, con.getDiscardedServerPreparedStatementCount());
        // Set instance setting to serial execution of un-prepare actions.
        con.setServerPreparedStatementDiscardThreshold(1);
        try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement(query)) {
            pstmt.execute();
        }
        // Verify that the un-prepare action was handled immediately.
        assertSame(0, con.getDiscardedServerPreparedStatementCount());
    }
}

Example 10 with SQLServerConnection

use of com.microsoft.sqlserver.jdbc.SQLServerConnection in project mssql-jdbc by Microsoft.

the class PreparedStatementTest method testBatchedUnprepare.

/**
 * Test handling of unpreparing prepared statements.
 *
 * @throws SQLException
 */
@Test
public void testBatchedUnprepare() throws SQLException {
    SQLServerConnection conOuter = null;
    try (SQLServerConnection con = (SQLServerConnection) DriverManager.getConnection(connectionString)) {
        conOuter = con;
        // Turn off use of prepared statement cache.
        con.setStatementPoolingCacheSize(0);
        // Clean-up proc cache
        this.executeSQL(con, "DBCC FREEPROCCACHE;");
        String lookupUniqueifier = UUID.randomUUID().toString();
        String queryCacheLookup = String.format("%%/*unpreparetest_%s%%*/SELECT * FROM sys.tables;", lookupUniqueifier);
        String query = String.format("/*unpreparetest_%s only sp_executesql*/SELECT * FROM sys.tables;", lookupUniqueifier);
        // Verify nothing in cache.
        String verifyTotalCacheUsesQuery = String.format("SELECT CAST(ISNULL(SUM(usecounts), 0) AS INT) FROM sys.dm_exec_cached_plans AS p CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS s WHERE s.text LIKE '%s'", queryCacheLookup);
        assertSame(0, executeSQLReturnFirstInt(con, verifyTotalCacheUsesQuery));
        int iterations = 25;
        query = String.format("/*unpreparetest_%s, sp_executesql->sp_prepexec->sp_execute- batched sp_unprepare*/SELECT * FROM sys.tables;", lookupUniqueifier);
        int prevDiscardActionCount = 0;
        // Now verify unprepares are needed.
        for (int i = 0; i < iterations; ++i) {
            // Verify current queue depth is expected.
            assertSame(prevDiscardActionCount, con.getDiscardedServerPreparedStatementCount());
            try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement(String.format("%s--%s", query, i))) {
                // sp_executesql
                pstmt.execute();
                // sp_prepexec
                pstmt.execute();
                ++prevDiscardActionCount;
                // sp_execute
                pstmt.execute();
            }
            // Verify clean-up is happening as expected.
            if (prevDiscardActionCount > con.getServerPreparedStatementDiscardThreshold()) {
                prevDiscardActionCount = 0;
            }
            assertSame(prevDiscardActionCount, con.getDiscardedServerPreparedStatementCount());
        }
    // Skipped for now due to unexpected failures. Not functional so not critical.
    /*
            // Verify total cache use.
            int expectedCacheHits = iterations * 4;
            int allowedDiscrepency = 20;
            // Allow some discrepency in number of cache hits to not fail test (
            // TODO: Follow up on why there is sometimes a discrepency in number of cache hits (less than expected).
            assertTrue(expectedCacheHits >= executeSQLReturnFirstInt(con, verifyTotalCacheUsesQuery));              
            assertTrue(expectedCacheHits - allowedDiscrepency < executeSQLReturnFirstInt(con, verifyTotalCacheUsesQuery));              
            */
    }
    // Verify clean-up happened on connection close.
    assertSame(0, conOuter.getDiscardedServerPreparedStatementCount());
}