Search in sources :

Example 6 with SQLServerStatement

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

the class readLargeData method main.

public static void main(String[] args) {
    // Declare the JDBC objects.
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    String serverName = null;
    String portNumber = null;
    String databaseName = null;
    String username = null;
    String password = null;
    try (BufferedReader br = new BufferedReader(new InputStreamReader(System.in))) {
        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();
        // Create a variable for the connection string.
        String connectionUrl = "jdbc:sqlserver://" + serverName + ":" + portNumber + ";" + "databaseName=" + databaseName + ";username=" + username + ";password=" + password + ";";
        // Establish the connection.
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        con = DriverManager.getConnection(connectionUrl);
        createTable(con);
        // Create test data as an example.
        StringBuffer buffer = new StringBuffer(4000);
        for (int i = 0; i < 4000; i++) buffer.append((char) ('A'));
        PreparedStatement pstmt = con.prepareStatement("UPDATE Document_JDBC_Sample " + "SET DocumentSummary = ? WHERE (DocumentID = 1)");
        pstmt.setString(1, buffer.toString());
        pstmt.executeUpdate();
        pstmt.close();
        // In adaptive mode, the application does not have to use a server cursor
        // to avoid OutOfMemoryError when the SELECT statement produces very large
        // results.
        // Create and execute an SQL statement that returns some data.
        String SQL = "SELECT Title, DocumentSummary " + "FROM Document_JDBC_Sample";
        stmt = con.createStatement();
        // Display the response buffering mode.
        SQLServerStatement SQLstmt = (SQLServerStatement) stmt;
        System.out.println("Response buffering mode is: " + SQLstmt.getResponseBuffering());
        // Get the updated data from the database and display it.
        rs = stmt.executeQuery(SQL);
        while (rs.next()) {
            Reader reader = rs.getCharacterStream(2);
            if (reader != null) {
                char[] output = new char[40];
                while (reader.read(output) != -1) {
                    // Print the chunk of the data that was read.
                    String stringOutput = new String(output);
                    System.out.println("Document_Summary Data Chunk: " + stringOutput);
                }
                System.out.println(rs.getString(1) + " has been accessed for the summary column.");
                // Close the stream.
                reader.close();
            }
        }
    }// Handle any errors that may have occurred.
     catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (rs != null)
            try {
                rs.close();
            } catch (Exception e) {
            }
        if (stmt != null)
            try {
                stmt.close();
            } catch (Exception e) {
            }
        if (con != null)
            try {
                con.close();
            } catch (Exception e) {
            }
    }
}
Also used : InputStreamReader(java.io.InputStreamReader) SQLServerStatement(com.microsoft.sqlserver.jdbc.SQLServerStatement) Statement(java.sql.Statement) PreparedStatement(java.sql.PreparedStatement) Connection(java.sql.Connection) BufferedReader(java.io.BufferedReader) Reader(java.io.Reader) InputStreamReader(java.io.InputStreamReader) PreparedStatement(java.sql.PreparedStatement) SQLServerStatement(com.microsoft.sqlserver.jdbc.SQLServerStatement) SQLException(java.sql.SQLException) ResultSet(java.sql.ResultSet) BufferedReader(java.io.BufferedReader)

Example 7 with SQLServerStatement

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

the class updateLargeData method main.

public static void main(String[] args) {
    // Declare the JDBC objects.
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    String serverName = null;
    String portNumber = null;
    String databaseName = null;
    String username = null;
    String password = null;
    Reader reader = null;
    try (BufferedReader br = new BufferedReader(new InputStreamReader(System.in))) {
        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();
        // Create a variable for the connection string.
        String connectionUrl = "jdbc:sqlserver://" + serverName + ":" + portNumber + ";" + "databaseName=" + databaseName + ";username=" + username + ";password=" + password + ";";
        // Establish the connection.
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        con = DriverManager.getConnection(connectionUrl);
        createTable(con);
        stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
        if (stmt.isWrapperFor(com.microsoft.sqlserver.jdbc.SQLServerStatement.class)) {
            SQLServerStatement SQLstmt = stmt.unwrap(com.microsoft.sqlserver.jdbc.SQLServerStatement.class);
            SQLstmt.setResponseBuffering("adaptive");
            System.out.println("Response buffering mode has been set to " + SQLstmt.getResponseBuffering());
        }
        // Select all of the document summaries.
        rs = stmt.executeQuery("SELECT Title, DocumentSummary FROM Document_JDBC_Sample");
        // Update each document summary.
        while (rs.next()) {
            // Retrieve the original document summary.
            reader = rs.getCharacterStream("DocumentSummary");
            if (reader == null) {
                // Update the document summary.
                System.out.println("Updating " + rs.getString("Title"));
                rs.updateString("DocumentSummary", "Work in progress");
                rs.updateRow();
            } else {
                System.out.println("reading " + rs.getString("Title"));
                reader.close();
                reader = null;
            }
        }
    }// Handle any errors that may have occurred.
     catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (reader != null)
            try {
                reader.close();
            } catch (Exception e) {
            }
        if (rs != null)
            try {
                rs.close();
            } catch (Exception e) {
            }
        if (stmt != null)
            try {
                stmt.close();
            } catch (Exception e) {
            }
        if (con != null)
            try {
                con.close();
            } catch (Exception e) {
            }
    }
}
Also used : InputStreamReader(java.io.InputStreamReader) SQLServerStatement(com.microsoft.sqlserver.jdbc.SQLServerStatement) Statement(java.sql.Statement) Connection(java.sql.Connection) ResultSet(java.sql.ResultSet) BufferedReader(java.io.BufferedReader) BufferedReader(java.io.BufferedReader) Reader(java.io.Reader) InputStreamReader(java.io.InputStreamReader) SQLServerStatement(com.microsoft.sqlserver.jdbc.SQLServerStatement) SQLException(java.sql.SQLException)

Example 8 with SQLServerStatement

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

the class AESetup method setUpConnection.

/**
 * Create connection, statement and generate path of resource file
 *
 * @throws Exception
 * @throws TestAbortedException
 */
@BeforeAll
static void setUpConnection() throws TestAbortedException, Exception {
    assumeTrue(13 <= new DBConnection(connectionString).getServerVersion(), "Aborting test case as SQL Server version is not compatible with Always encrypted ");
    String AETestConenctionString = connectionString + ";sendTimeAsDateTime=false";
    readFromFile(javaKeyStoreInputFile, "Alias name");
    try (SQLServerConnection con = (SQLServerConnection) DriverManager.getConnection(AETestConenctionString);
        SQLServerStatement stmt = (SQLServerStatement) con.createStatement()) {
        dropCEK(stmt);
        dropCMK(stmt);
    }
    keyPath = Utils.getCurrentClassPath() + jksName;
    storeProvider = new SQLServerColumnEncryptionJavaKeyStoreProvider(keyPath, secretstrJks.toCharArray());
    stmtColEncSetting = SQLServerStatementColumnEncryptionSetting.Enabled;
    Properties info = new Properties();
    info.setProperty("ColumnEncryptionSetting", "Enabled");
    info.setProperty("keyStoreAuthentication", "JavaKeyStorePassword");
    info.setProperty("keyStoreLocation", keyPath);
    info.setProperty("keyStoreSecret", secretstrJks);
    con = (SQLServerConnection) DriverManager.getConnection(AETestConenctionString, info);
    stmt = (SQLServerStatement) con.createStatement();
    createCMK(keyStoreName, javaKeyAliases);
    createCEK(storeProvider);
}
Also used : DBConnection(com.microsoft.sqlserver.testframework.DBConnection) SQLServerConnection(com.microsoft.sqlserver.jdbc.SQLServerConnection) Properties(java.util.Properties) SQLServerStatement(com.microsoft.sqlserver.jdbc.SQLServerStatement) SQLServerColumnEncryptionJavaKeyStoreProvider(com.microsoft.sqlserver.jdbc.SQLServerColumnEncryptionJavaKeyStoreProvider) BeforeAll(org.junit.jupiter.api.BeforeAll)

Example 9 with SQLServerStatement

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

the class WrapperTest method wrapTest.

/**
 * Wrapper tests
 * @throws Exception
 */
@Test
public void wrapTest() throws Exception {
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    Connection con = DriverManager.getConnection(connectionString);
    Statement stmt = con.createStatement();
    try {
        // First make sure that a statement can be unwrapped
        boolean isWrapper = ((SQLServerStatement) stmt).isWrapperFor(Class.forName("com.microsoft.sqlserver.jdbc.SQLServerStatement"));
        assertEquals(isWrapper, true, "SQLServerStatement should be a wrapper for self");
        isWrapper = ((SQLServerStatement) stmt).isWrapperFor(Class.forName("com.microsoft.sqlserver.jdbc.ISQLServerStatement"));
        assertEquals(isWrapper, true, "SQLServerStatement should be a wrapper for ISQLServerStatement");
        isWrapper = ((SQLServerStatement) stmt).isWrapperFor(Class.forName("com.microsoft.sqlserver.jdbc.SQLServerConnection"));
        assertEquals(isWrapper, false, "SQLServerStatement should not be a wrapper for SQLServerConnection");
        // Now make sure that we can unwrap a SQLServerCallableStatement to a SQLServerStatement
        CallableStatement cs = con.prepareCall("{  ? = CALL " + "ProcName" + " (?, ?, ?, ?) }");
        // Test the class first
        isWrapper = ((SQLServerCallableStatement) cs).isWrapperFor(Class.forName("com.microsoft.sqlserver.jdbc.SQLServerStatement"));
        assertEquals(isWrapper, true, "SQLServerCallableStatement should be a wrapper for SQLServerStatement");
        // Now unwrap the Callable to a statement and call a SQLServerStatement specific function and make sure it succeeds.
        SQLServerStatement stmt2 = (SQLServerStatement) ((SQLServerCallableStatement) cs).unwrap(Class.forName("com.microsoft.sqlserver.jdbc.SQLServerStatement"));
        stmt2.setResponseBuffering("adaptive");
        // now test the interface
        isWrapper = ((SQLServerCallableStatement) cs).isWrapperFor(Class.forName("com.microsoft.sqlserver.jdbc.ISQLServerCallableStatement"));
        assertEquals(isWrapper, true, "SQLServerCallableStatement should be a wrapper for ISQLServerCallableStatement");
        // Now unwrap the Callable to a statement and call a SQLServerStatement specific function and make sure it succeeds.
        ISQLServerPreparedStatement stmt4 = (ISQLServerPreparedStatement) ((SQLServerCallableStatement) cs).unwrap(Class.forName("com.microsoft.sqlserver.jdbc.ISQLServerPreparedStatement"));
        stmt4.setResponseBuffering("adaptive");
        if (isKatmaiServer())
            stmt4.setDateTimeOffset(1, null);
        // Try Unwrapping CallableStatement to a callableStatement
        isWrapper = ((SQLServerCallableStatement) cs).isWrapperFor(Class.forName("com.microsoft.sqlserver.jdbc.SQLServerCallableStatement"));
        assertEquals(isWrapper, true, "SQLServerCallableStatement should be a wrapper for SQLServerCallableStatement");
        // Now unwrap the Callable to a SQLServerCallableStatement and call a SQLServerStatement specific function and make sure it succeeds.
        SQLServerCallableStatement stmt3 = (SQLServerCallableStatement) ((SQLServerCallableStatement) cs).unwrap(Class.forName("com.microsoft.sqlserver.jdbc.SQLServerCallableStatement"));
        stmt3.setResponseBuffering("adaptive");
        if (isKatmaiServer()) {
            stmt3.setDateTimeOffset(1, null);
        }
        if (null != stmt4) {
            stmt4.close();
        }
        if (null != cs) {
            cs.close();
        }
    } catch (UnsupportedOperationException e) {
        assertEquals(System.getProperty("java.specification.version"), "1.5", "isWrapperFor should be supported in anything other than 1.5");
        assertTrue(e.getMessage().equalsIgnoreCase("This operation is not supported."), "Wrong exception message");
    } finally {
        if (null != stmt) {
            stmt.close();
        }
        if (null != con) {
            con.close();
        }
    }
}
Also used : SQLServerCallableStatement(com.microsoft.sqlserver.jdbc.SQLServerCallableStatement) SQLServerStatement(com.microsoft.sqlserver.jdbc.SQLServerStatement) SQLServerCallableStatement(com.microsoft.sqlserver.jdbc.SQLServerCallableStatement) Statement(java.sql.Statement) CallableStatement(java.sql.CallableStatement) ISQLServerPreparedStatement(com.microsoft.sqlserver.jdbc.ISQLServerPreparedStatement) SQLServerCallableStatement(com.microsoft.sqlserver.jdbc.SQLServerCallableStatement) CallableStatement(java.sql.CallableStatement) Connection(java.sql.Connection) DBConnection(com.microsoft.sqlserver.testframework.DBConnection) ISQLServerPreparedStatement(com.microsoft.sqlserver.jdbc.ISQLServerPreparedStatement) SQLServerStatement(com.microsoft.sqlserver.jdbc.SQLServerStatement) Test(org.junit.jupiter.api.Test) AbstractTest(com.microsoft.sqlserver.testframework.AbstractTest)

Example 10 with SQLServerStatement

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

the class BatchExecutionWithNullTest method testSetup.

@BeforeEach
public void testSetup() throws TestAbortedException, Exception {
    assumeTrue(13 <= new DBConnection(connectionString).getServerVersion(), "Aborting test case as SQL Server version is not compatible with Always encrypted ");
    connection = DriverManager.getConnection(connectionString);
    SQLServerStatement stmt = (SQLServerStatement) connection.createStatement();
    Utils.dropTableIfExists("esimple", stmt);
    String sql1 = "create table esimple (id integer not null, name varchar(255), constraint pk_esimple primary key (id))";
    stmt.execute(sql1);
    stmt.close();
}
Also used : DBConnection(com.microsoft.sqlserver.testframework.DBConnection) SQLServerStatement(com.microsoft.sqlserver.jdbc.SQLServerStatement) BeforeEach(org.junit.jupiter.api.BeforeEach)

Aggregations

SQLServerStatement (com.microsoft.sqlserver.jdbc.SQLServerStatement)11 AbstractTest (com.microsoft.sqlserver.testframework.AbstractTest)5 Connection (java.sql.Connection)5 Test (org.junit.jupiter.api.Test)5 DBConnection (com.microsoft.sqlserver.testframework.DBConnection)4 ResultSet (java.sql.ResultSet)4 SQLException (java.sql.SQLException)4 Statement (java.sql.Statement)4 SQLServerCallableStatement (com.microsoft.sqlserver.jdbc.SQLServerCallableStatement)3 SQLServerPreparedStatement (com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement)2 BufferedReader (java.io.BufferedReader)2 InputStreamReader (java.io.InputStreamReader)2 Reader (java.io.Reader)2 CallableStatement (java.sql.CallableStatement)2 PreparedStatement (java.sql.PreparedStatement)2 BeforeAll (org.junit.jupiter.api.BeforeAll)2 ISQLServerPreparedStatement (com.microsoft.sqlserver.jdbc.ISQLServerPreparedStatement)1 SQLServerColumnEncryptionJavaKeyStoreProvider (com.microsoft.sqlserver.jdbc.SQLServerColumnEncryptionJavaKeyStoreProvider)1 SQLServerConnection (com.microsoft.sqlserver.jdbc.SQLServerConnection)1 IOException (java.io.IOException)1