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

Example 11 with SQLServerDataSource

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

the class FipsTest method fipsDatSourceEncrypt.

/**
 * Test after removing encrypt in FIPS Data Source.
 */
@Test
public void fipsDatSourceEncrypt() {
    try {
        SQLServerDataSource ds = new SQLServerDataSource();
        setDataSourceProperties(ds);
        ds.setEncrypt(false);
        Connection con = ds.getConnection();
        Assertions.fail("It should fail as we are not passing appropriate params");
    } catch (SQLException e) {
        Assertions.assertTrue(e.getMessage().contains("Unable to verify FIPS mode settings."), "Should create exception for invalid encrypt value");
    }
}

Example 12 with SQLServerDataSource

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

the class FipsTest method fipsDataSourceTrustServerCertificateTest.

/**
 * Test after setting TrustServerCertificate as true.
 *
 * @throws Exception
 */
@Test
public void fipsDataSourceTrustServerCertificateTest() throws Exception {
    try {
        SQLServerDataSource ds = new SQLServerDataSource();
        setDataSourceProperties(ds);
        ds.setTrustServerCertificate(true);
        Connection con = ds.getConnection();
        Assertions.fail("It should fail as we are not passing appropriate params");
    } catch (SQLException e) {
        Assertions.assertTrue(e.getMessage().contains("Unable to verify FIPS mode settings."), "Should create exception for invalid TrustServerCertificate value");
    }
}

Example 13 with SQLServerDataSource

use of com.microsoft.sqlserver.jdbc.SQLServerDataSource 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 14 with SQLServerDataSource

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

the class AzureActiveDirectoryAuthentication method main.

public static void main(String[] args) {
    // Declare the JDBC objects.
    Connection con = null;
    Statement stmt = null;
    CallableStatement cstmt = null;
    ResultSet rs = null;
    String serverName = null;
    String portNumber = null;
    String databaseName = null;
    String username = null;
    String password = null;
    String authentication = null;
    String hostNameInCertificate = null;
    try (BufferedReader br = new BufferedReader(new InputStreamReader(System.in))) {
        System.out.println("Remember to put sqljdbc_auth.dll in the same directory as the pom.xml file.");
        System.out.print("Enter server name: ");
        serverName = br.readLine();
        System.out.print("Enter port number: ");
        portNumber = br.readLine();
        System.out.print("Enter database name: ");
        databaseName = br.readLine();
        System.out.print("Enter username: ");
        username = br.readLine();
        System.out.print("Enter password: ");
        password = br.readLine();
        // e.g. ActiveDirectoryPassword
        System.out.print("Enter authentication: ");
        authentication = br.readLine();
        // e.g. *.database.windows.net
        System.out.print("Enter host name in certificate: ");
        hostNameInCertificate = br.readLine();
        // Establish the connection.
        SQLServerDataSource ds = new SQLServerDataSource();
        ds.setServerName(serverName);
        ds.setPortNumber(Integer.parseInt(portNumber));
        ds.setDatabaseName(databaseName);
        ds.setUser(username);
        ds.setPassword(password);
        ds.setAuthentication(authentication);
        ds.setHostNameInCertificate(hostNameInCertificate);
        con = ds.getConnection();
        System.out.println();
        System.out.println("Connection established successfully.");
        // Create and execute an SQL statement that returns user name.
        String SQL = "SELECT SUSER_SNAME()";
        stmt = con.createStatement();
        rs = stmt.executeQuery(SQL);
        // Iterate through the data in the result set and display it.
        while (rs.next()) {
            System.out.println("user name: " + rs.getString(1));
        }
    }// Handle any errors that may have occurred.
     catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (rs != null)
            try {
                rs.close();
            } catch (Exception e) {
            }
        if (cstmt != null)
            try {
                cstmt.close();
            } catch (Exception e) {
            }
        if (con != null)
            try {
                con.close();
            } catch (Exception e) {
            }
    }
}

Example 15 with SQLServerDataSource

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

the class ExceptionTest method testResultSetErrorSearch.

@Test
public void testResultSetErrorSearch() throws Exception {
    SQLServerDataSource ds = new SQLServerDataSource();
    ds.setURL(connectionString);
    String dropTable_sql = "DROP TABLE IF EXISTS TEST659;";
    String dropProc_sql = "DROP PROCEDURE IF EXISTS proc_insert_masse_TEST;";
    String createTable_sql = "CREATE TABLE TEST659 (ID INT IDENTITY NOT NULL," + "FIELD1 VARCHAR (255) NOT NULL," + "FIELD2 VARCHAR (255) NOT NULL);";
    String createProc_sql = "CREATE PROCEDURE proc_insert_masse_TEST @json NVARCHAR(MAX) " + "AS " + "BEGIN TRANSACTION " + "BEGIN TRY " + "SET NOCOUNT ON; " + "MERGE INTO TEST659 AS target " + "USING " + "(SELECT * FROM OPENJSON(@json) " + "WITH (FIELD1 VARCHAR(255) 'strict $.FIELD1')) " + "AS src " + "ON (1 = 0) " + "WHEN NOT MATCHED THEN " + "INSERT (FIELD1) VALUES (src.FIELD1) " + "OUTPUT inserted.ID; " + "COMMIT TRANSACTION; " + "END TRY " + "BEGIN CATCH " + "DECLARE @errorMessage NVARCHAR(4000) = ERROR_MESSAGE(); " + "ROLLBACK TRANSACTION; " + "RAISERROR('Error occured during the insert: %s', 16, 1, @errorMessage); " + "END CATCH;";
    String proc_sql = "EXECUTE [dbo].proc_insert_masse_TEST N'[{\"FIELD1\" : \"TEST\"}]';";
    Connection conn = ds.getConnection();
    if (conn.getMetaData().getDatabaseMajorVersion() >= 13) {
        Statement stmt = conn.createStatement();
        stmt.execute(dropTable_sql);
        stmt.execute(createTable_sql);
        stmt.execute(dropProc_sql);
        stmt.execute(createProc_sql);
        stmt.execute(proc_sql);
        ResultSet rs = stmt.getResultSet();
        try {
            rs.next();
            fail("No exceptions caught.");
        } catch (SQLException e) {
            assertTrue(e.getMessage().contains("Error occured during the insert:"), "Unexpected Error Message: " + e.getMessage());
        }
    }
}