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

Example 11 with SQLServerConnection

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

the class PreparedStatementTest method testStatementPooling.

/**
 * Test handling of statement pooling for prepared statements.
 *
 * @throws SQLException
 */
@Test
@Tag("slow")
public void testStatementPooling() throws SQLException {
    // Test % handle re-use
    try (SQLServerConnection con = (SQLServerConnection) DriverManager.getConnection(connectionString)) {
        String query = String.format("/*statementpoolingtest_re-use_%s*/SELECT TOP(1) * FROM sys.tables;", UUID.randomUUID().toString());
        con.setStatementPoolingCacheSize(10);
        boolean[] prepOnFirstCalls = { false, true };
        for (boolean prepOnFirstCall : prepOnFirstCalls) {
            con.setEnablePrepareOnFirstPreparedStatementCall(prepOnFirstCall);
            int[] queryCounts = { 10, 20, 30, 40 };
            for (int queryCount : queryCounts) {
                String[] queries = new String[queryCount];
                for (int i = 0; i < queries.length; ++i) {
                    queries[i] = String.format("%s--%s--%s--%s", query, i, queryCount, prepOnFirstCall);
                }
                int testsWithHandleReuse = 0;
                final int testCount = 500;
                for (int i = 0; i < testCount; ++i) {
                    Random random = new Random();
                    int queryNumber = random.nextInt(queries.length);
                    try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement(queries[queryNumber])) {
                        pstmt.execute();
                        // Grab handle-reuse before it would be populated if initially created.
                        if (0 < pstmt.getPreparedStatementHandle())
                            testsWithHandleReuse++;
                        // Make sure handle is updated.
                        pstmt.getMoreResults();
                    }
                }
                System.out.println(String.format("Prep on first call: %s Query count:%s: %s of %s (%s)", prepOnFirstCall, queryCount, testsWithHandleReuse, testCount, (double) testsWithHandleReuse / (double) testCount));
            }
        }
    }
    try (SQLServerConnection con = (SQLServerConnection) DriverManager.getConnection(connectionString)) {
        // Test behvaior with statement pooling.
        con.setStatementPoolingCacheSize(10);
        this.executeSQL(con, "IF NOT EXISTS (SELECT * FROM sys.messages WHERE message_id = 99586) EXEC sp_addmessage 99586, 16, 'Prepared handle GAH!';");
        // Test with missing handle failures (fake).
        this.executeSQL(con, "CREATE TABLE #update1 (col INT);INSERT #update1 VALUES (1);");
        this.executeSQL(con, "CREATE PROC #updateProc1 AS UPDATE #update1 SET col += 1; IF EXISTS (SELECT * FROM #update1 WHERE col % 5 = 0) RAISERROR(99586,16,1);");
        try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement("#updateProc1")) {
            for (int i = 0; i < 100; ++i) {
                try {
                    assertSame(1, pstmt.executeUpdate());
                } catch (SQLException e) {
                    // Since the original "Could not find prepared statement with handle" error does not terminate the execution after it.
                    if (!e.getMessage().contains("Prepared handle GAH")) {
                        throw e;
                    }
                }
            }
        }
        // although executeUpdate() throws exception, update operation should be executed successfully.
        try (ResultSet rs = con.createStatement().executeQuery("select * from #update1")) {
            rs.next();
            assertSame(101, rs.getInt(1));
        }
        // Test batching with missing handle failures (fake).
        this.executeSQL(con, "IF NOT EXISTS (SELECT * FROM sys.messages WHERE message_id = 99586) EXEC sp_addmessage 99586, 16, 'Prepared handle GAH!';");
        this.executeSQL(con, "CREATE TABLE #update2 (col INT);INSERT #update2 VALUES (1);");
        this.executeSQL(con, "CREATE PROC #updateProc2 AS UPDATE #update2 SET col += 1; IF EXISTS (SELECT * FROM #update2 WHERE col % 5 = 0) RAISERROR(99586,16,1);");
        try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement("#updateProc2")) {
            for (int i = 0; i < 100; ++i) {
                pstmt.addBatch();
            }
            int[] updateCounts = null;
            try {
                updateCounts = pstmt.executeBatch();
            } catch (BatchUpdateException e) {
                // Since the original "Could not find prepared statement with handle" error does not terminate the execution after it.
                if (!e.getMessage().contains("Prepared handle GAH")) {
                    throw e;
                }
            }
            // since executeBatch() throws exception, it does not return anthing. So updateCounts is still null.
            assertSame(null, updateCounts);
            // although executeBatch() throws exception, update operation should be executed successfully.
            try (ResultSet rs = con.createStatement().executeQuery("select * from #update2")) {
                rs.next();
                assertSame(101, rs.getInt(1));
            }
        }
    }
    try (SQLServerConnection con = (SQLServerConnection) DriverManager.getConnection(connectionString)) {
        // Test behvaior with statement pooling.
        con.setDisableStatementPooling(false);
        con.setStatementPoolingCacheSize(10);
        String lookupUniqueifier = UUID.randomUUID().toString();
        String query = String.format("/*statementpoolingtest_%s*/SELECT * FROM sys.tables;", lookupUniqueifier);
        // Execute statement first, should create cache entry WITHOUT handle (since sp_executesql was used).
        try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement(query)) {
            // sp_executesql
            pstmt.execute();
            // Make sure handle is updated.
            pstmt.getMoreResults();
            assertSame(0, pstmt.getPreparedStatementHandle());
        }
        // Execute statement again, should now create handle.
        int handle = 0;
        try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement(query)) {
            // sp_prepexec
            pstmt.execute();
            // Make sure handle is updated.
            pstmt.getMoreResults();
            handle = pstmt.getPreparedStatementHandle();
            assertNotSame(0, handle);
        }
        // Execute statement again and verify same handle was used.
        try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement(query)) {
            // sp_execute
            pstmt.execute();
            // Make sure handle is updated.
            pstmt.getMoreResults();
            assertNotSame(0, pstmt.getPreparedStatementHandle());
            assertSame(handle, pstmt.getPreparedStatementHandle());
        }
        // Execute new statement with different SQL text and verify it does NOT get same handle (should now fall back to using sp_executesql).
        SQLServerPreparedStatement outer = null;
        try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement(query + ";")) {
            outer = pstmt;
            // sp_executesql
            pstmt.execute();
            // Make sure handle is updated.
            pstmt.getMoreResults();
            assertSame(0, pstmt.getPreparedStatementHandle());
            assertNotSame(handle, pstmt.getPreparedStatementHandle());
        }
        try {
            System.out.println(outer.getPreparedStatementHandle());
            fail("Error for invalid use of getPreparedStatementHandle() after statement close expected.");
        } catch (Exception e) {
        // Good!
        }
    }
}

Example 12 with SQLServerConnection

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

the class PreparedStatementTest method testStatementPoolingEviction.

/**
 * Test handling of eviction from statement pooling for prepared statements.
 *
 * @throws SQLException
 */
@Test
public void testStatementPoolingEviction() throws SQLException {
    for (int testNo = 0; testNo < 2; ++testNo) {
        try (SQLServerConnection con = (SQLServerConnection) DriverManager.getConnection(connectionString)) {
            int cacheSize = 10;
            int discardedStatementCount = testNo == 0 ? 5 : /*batched unprepares*/
            0;
            // enabling caching
            con.setDisableStatementPooling(false);
            con.setStatementPoolingCacheSize(cacheSize);
            con.setServerPreparedStatementDiscardThreshold(discardedStatementCount);
            String lookupUniqueifier = UUID.randomUUID().toString();
            String query = String.format("/*statementpoolingevictiontest_%s*/SELECT * FROM sys.tables; -- ", lookupUniqueifier);
            // Add new statements to fill up the statement pool.
            for (int i = 0; i < cacheSize; ++i) {
                try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement(query + new Integer(i).toString())) {
                    // sp_executesql
                    pstmt.execute();
                    // sp_prepexec, actual handle created and cached.
                    pstmt.execute();
                }
                // Make sure no handles in discard queue (still only in statement pool).
                assertSame(0, con.getDiscardedServerPreparedStatementCount());
            }
            // No discarded handles yet, all in statement pool.
            assertSame(0, con.getDiscardedServerPreparedStatementCount());
            // action queue).
            for (int i = cacheSize; i < cacheSize + 5; ++i) {
                try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement(query + new Integer(i).toString())) {
                    // sp_executesql
                    pstmt.execute();
                    // sp_prepexec, actual handle created and cached.
                    pstmt.execute();
                }
                // If we use discard queue handles should start going into discard queue.
                if (0 == testNo)
                    assertNotSame(0, con.getDiscardedServerPreparedStatementCount());
                else
                    assertSame(0, con.getDiscardedServerPreparedStatementCount());
            }
            // If we use it, now discard queue should be "full".
            if (0 == testNo)
                assertSame(discardedStatementCount, con.getDiscardedServerPreparedStatementCount());
            else
                assertSame(0, con.getDiscardedServerPreparedStatementCount());
            // action queue.
            try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement(query)) {
                // sp_executesql
                pstmt.execute();
                // sp_prepexec, actual handle created and cached.
                pstmt.execute();
            }
            // Discard queue should now be empty.
            assertSame(0, con.getDiscardedServerPreparedStatementCount());
            // Set statement pool size to 0 and verify statements get discarded.
            int statementsInCache = con.getStatementHandleCacheEntryCount();
            con.setStatementPoolingCacheSize(0);
            assertSame(0, con.getStatementHandleCacheEntryCount());
            if (0 == testNo)
                // Verify statements moved over to discard action queue.
                assertSame(statementsInCache, con.getDiscardedServerPreparedStatementCount());
            // Run discard actions (otherwise run on pstmt.close)
            con.closeUnreferencedPreparedStatementHandles();
            assertSame(0, con.getDiscardedServerPreparedStatementCount());
            // Verify new statement does not go into cache (since cache is now off)
            try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement(query)) {
                // sp_executesql
                pstmt.execute();
                // sp_prepexec, actual handle created and cached.
                pstmt.execute();
                assertSame(0, con.getStatementHandleCacheEntryCount());
            }
        }
    }
}

Example 13 with SQLServerConnection

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

the class PreparedStatementTest method testPrepareRace.

@Test
public void testPrepareRace() throws Exception {
    String[] queries = new String[3];
    queries[0] = String.format("SELECT * FROM sys.tables -- %s", UUID.randomUUID());
    queries[1] = String.format("SELECT * FROM sys.tables -- %s", UUID.randomUUID());
    queries[2] = String.format("SELECT * FROM sys.tables -- %s", UUID.randomUUID());
    ExecutorService threadPool = Executors.newFixedThreadPool(4);
    AtomicReference<Exception> exception = new AtomicReference<>();
    try (SQLServerConnection con = (SQLServerConnection) DriverManager.getConnection(connectionString)) {
        for (int i = 0; i < 4; i++) {
            threadPool.execute(new TestPrepareRace(con, queries, exception));
        }
        threadPool.shutdown();
        threadPool.awaitTermination(10, SECONDS);
        assertNull(exception.get());
        // Force un-prepares.
        con.closeUnreferencedPreparedStatementHandles();
        // Verify that queue is now empty.
        assertSame(0, con.getDiscardedServerPreparedStatementCount());
    }
}

Example 14 with SQLServerConnection

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

the class RegressionTest method testServerCursorPStmt.

/**
 * 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();
}

Example 15 with SQLServerConnection

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

the class RegressionTest method testUpdateQuery.

/**
 * Tests update query
 *
 * @throws SQLException
 */
@Test
public void testUpdateQuery() throws SQLException {
    assumeTrue("JDBC41".equals(Utils.getConfiguredProperty("JDBC_Version")), "Aborting test case as JDBC version is not compatible. ");
    SQLServerConnection con = (SQLServerConnection) DriverManager.getConnection(connectionString);
    String sql;
    SQLServerPreparedStatement pstmt = null;
    JDBCType[] targets = { JDBCType.INTEGER, JDBCType.SMALLINT };
    int rows = 3;
    final String tableName = "[updateQuery]";
    Statement stmt = con.createStatement();
    Utils.dropTableIfExists(tableName, stmt);
    stmt.executeUpdate("CREATE TABLE " + tableName + " (" + "c1 int null," + "PK int NOT NULL PRIMARY KEY" + ")");
    /*
         * populate table
         */
    sql = "insert into " + tableName + " values(" + "?,?" + ")";
    pstmt = (SQLServerPreparedStatement) con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, connection.getHoldability());
    for (int i = 1; i <= rows; i++) {
        pstmt.setObject(1, i, JDBCType.INTEGER);
        pstmt.setObject(2, i, JDBCType.INTEGER);
        pstmt.executeUpdate();
    }
    /*
         * Update table
         */
    sql = "update " + tableName + " SET c1= ? where PK =1";
    for (int i = 1; i <= rows; i++) {
        pstmt = (SQLServerPreparedStatement) con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        for (JDBCType target : targets) {
            pstmt.setObject(1, 5 + i, target);
            pstmt.executeUpdate();
        }
    }
    /*
         * Verify
         */
    ResultSet rs = stmt.executeQuery("select * from " + tableName);
    rs.next();
    assertEquals(rs.getInt(1), 8, "Value mismatch");
    if (null != stmt)
        stmt.close();
    if (null != con)
        con.close();
}