safetest.java

第三方的SQL Server and Sybase的jdbc dirver,速度更快

/*
 * SAfeTest.java
 *
 * Created on 08/23/2002
 */

package net.sourceforge.jtds.test;

import java.sql.*;
import java.math.BigDecimal;

import junit.framework.TestSuite;
import net.sourceforge.jtds.util.Logger;
import net.sourceforge.jtds.jdbc.Driver;
import net.sourceforge.jtds.jdbc.Messages;

import java.text.SimpleDateFormat;
import java.util.Vector;

/**
 * @author  Alin Sinpalean
 * @version $Id: SAfeTest.java,v 1.52 2005/06/02 16:10:42 alin_sinpalean Exp $
 * @since   0.4
 */
public class SAfeTest extends DatabaseTestCase {
    public SAfeTest(String name) {
        super(name);
    }

    public static void main(String args[]) {
        Logger.setActive(true);

        if (args.length > 0) {
            junit.framework.TestSuite s = new TestSuite();

            for (int i=0; i<args.length; i++) {
                s.addTest(new SAfeTest(args[i]));
            }

            junit.textui.TestRunner.run(s);
        } else {
            junit.textui.TestRunner.run(SAfeTest.class);
        }
    }

    /**
     * Test whether NULL values, 0-length strings and single space strings
     * are treated right.
     */
    public void testNullLengthStrings0001() throws Exception {
        String types[] = {
            "VARCHAR(50)",
            "TEXT",
            "VARCHAR(350)",
            "NVARCHAR(50)",
            "NTEXT",
        };

        String values[] = {
            null,
            "",
            " ",
            "x"
        };

        Statement stmt = con.createStatement();
        boolean tds70orLater = props.getProperty(Messages.get(Driver.TDS)) == null
            || props.getProperty(Messages.get(Driver.TDS)).charAt(0) >= '7';
        int typeCnt = tds70orLater ? types.length : 2;

        for (int i = 0; i < typeCnt; i++) {
            assertEquals(0, stmt.executeUpdate("CREATE TABLE #SAfe0001 (val " + types[i] + " NULL)"));

            for (int j = 0; j < values.length; j++) {
                String insQuery = values[j]==null ?
                    "INSERT INTO #SAfe0001 VALUES (NULL)" :
                    "INSERT INTO #SAfe0001 VALUES ('"+values[j]+"')";
                assertEquals(1, stmt.executeUpdate(insQuery));
                ResultSet rs = stmt.executeQuery("SELECT val FROM #SAfe0001");

                assertTrue(rs.next());

                if (tds70orLater || !" ".equals(values[j])) {
                    assertEquals(values[j], rs.getString(1));
                } else {
                    if (values[j] == null) {
                        assertEquals(null, rs.getObject(1));
                    } else {
                        assertEquals("", rs.getString(1));
                    }
                }

                assertTrue(!rs.next());
                assertEquals(0, stmt.executeUpdate("TRUNCATE TABLE #SAfe0001"));
            }

            assertEquals(0, stmt.executeUpdate("DROP TABLE #SAfe0001"));
        }
        stmt.close();
    }

    /**
     * Test cancelling. Create 2 connections, lock some records on one of them
     * and try to read them using the other one. Cancel the statement from the
     * second connection, then try executing a simple query on it to make sure
     * it's in a correct state.
     */
    public void testCancel0001() throws Exception {
        // Create another connection to make sure the statements will deadlock
        Connection con2 = getConnection();

        Statement stmt = con.createStatement();
        assertFalse(stmt.execute(
                "create table ##SAfe0001 (id int primary key, val varchar(20) null)"));
        assertFalse(stmt.execute(
                "insert into ##SAfe0001 values (1, 'Line 1') "+
                "insert into ##SAfe0001 values (2, 'Line 2')"));
        assertEquals(1, stmt.getUpdateCount());
        assertTrue(!stmt.getMoreResults());
        assertEquals(1, stmt.getUpdateCount());
        assertTrue(!stmt.getMoreResults());
        assertEquals(-1, stmt.getUpdateCount());

        con.setAutoCommit(false);
        // This is where we lock the first line in the table
        stmt.executeUpdate("update ##SAfe0001 set val='Updated Line' where id=1");

        final Statement stmt2 = con2.createStatement();

        new Thread() {
            public void run() {
                try {
                    sleep(1000);
                    stmt2.cancel();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }.start();

        try {
            stmt2.executeQuery("if 1 = 1 select * from ##SAfe0001");
            // Make sure we get to the
            stmt2.getMoreResults();
            fail("Expecting cancel exception");
        } catch( SQLException ex ) {
            assertEquals(
                    "Expecting cancel exception. Got " + ex.getMessage(),
                    "S1008", ex.getSQLState());
        }

        con.setAutoCommit(true);

        stmt.execute("drop table ##SAfe0001");
        stmt.close();

        // Just run a tiny query to make sure the stream is still in working
        // condition.
        ResultSet rs = stmt2.executeQuery("select 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertTrue(!rs.next());
        stmt2.close();
        con2.close();
    }

    /**
     * Test cancelling. Create 2 connections, lock some records on one of them
     * and try to read them using the other one with a timeout set. When the
     * second connection times out try executing a simple query on it to make
     * sure it's in a correct state.
     */
    public void testCancel0002() throws Exception {
        // Create another connection to make sure the statements will deadlock
        Connection con2 = getConnection();

        Statement stmt = con.createStatement();
        assertFalse(stmt.execute(
                "create table ##SAfe0002 (id int primary key, val varchar(20) null)"));
        assertFalse(stmt.execute(
                "insert into ##SAfe0002 values (1, 'Line 1') "+
                "insert into ##SAfe0002 values (2, 'Line 2')"));
        assertEquals(1, stmt.getUpdateCount());
        assertTrue(!stmt.getMoreResults());
        assertEquals(1, stmt.getUpdateCount());
        assertTrue(!stmt.getMoreResults());
        assertEquals(-1, stmt.getUpdateCount());

        con.setAutoCommit(false);
        // This is where we lock the first line in the table
        stmt.executeUpdate("update ##SAfe0002 set val='Updated Line' where id=1");

        Statement stmt2 = con2.createStatement();
        stmt2.setQueryTimeout(1);

        try {
            stmt2.executeQuery("if 1 = 1 select * from ##SAfe0002");
            fail("Expecting timeout exception");
        } catch( SQLException ex ) {
            assertEquals(
                    "Expecting timeout exception. Got " + ex.getMessage(),
                    "HYT00", ex.getSQLState());
        }

        // SAfe What should we do with the results if the execution timed out?!

        con.setAutoCommit(true);

        stmt.execute("drop table ##SAfe0002");
        stmt.close();

        // Just run a tiny query to make sure the stream is still in working
        // condition.
        ResultSet rs = stmt2.executeQuery("select 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertTrue(!rs.next());
        stmt2.close();
        con2.close();
    }

    /**
     * Test for bug [1120442] Statement hangs in socket read after
     * Statement.cancel().
     * <p/>
     * In 1.0.1 and earlier versions network packets consisting of a single
     * TDS_DONE packet with the CANCEL flag set were ignored and a new read()
     * was attempted, essentially causing a deadlock.
     * <p/>
     * Because it relies on a particular succession of events this test will
     * not always work as expected, i.e. the cancel might be executed too early
     * or too late, but it won't fail in this situation.
     */
    public void testCancel0003() throws Exception {
        final Statement stmt = con.createStatement();

        for (int i = 0; i < 100; i++) {
            Thread t = new Thread(new Runnable() {
                public void run() {
                    try {
                        // Cancel the statement and hope this happens
                        // immediately after the executeQuery() below and
                        // before any results arrive
                        stmt.cancel();
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                }
            });
            t.start();

            // Create a thread that executes a query
            try {
                stmt.executeQuery("select max(id) from sysobjects");
                // Can't fail here, the cancel() request might be out of order
            } catch (SQLException ex) {
                // Request was canceled
                if (!"S1008".equals(ex.getSQLState())) {
                    ex.printStackTrace();
                }
                assertEquals("S1008", ex.getSQLState());
            }

            // Wait for the cancel to finish executing
            try {
                t.join();
            } catch (InterruptedException ex) {
                // Ignore
            }
        }

        // Make sure the connection is still alive
        stmt.executeQuery("select 1");
        stmt.close();
    }

    // MT-unsafe!!!
    volatile int started, done;
    volatile boolean failed;

    /**
     * Test <code>CursorResultSet</code> concurrency. Create a number of threads that execute concurrent queries using
     * scrollable result sets. All requests should be run on the same connection (<code>Tds</code> instance).
     */
    public void testCursorResultSetConcurrency0003() throws Exception {
        Statement stmt0 = con.createStatement();
        stmt0.execute(
                "create table #SAfe0003(id int primary key, val varchar(20) null)");
        stmt0.execute(
                "insert into #SAfe0003 values (1, 'Line 1') "+
                "insert into #SAfe0003 values (2, 'Line 2')");
        while (stmt0.getMoreResults() || stmt0.getUpdateCount() != -1);

        final Object o1=new Object(), o2=new Object();

        int threadCount = 25;
        Thread threads[] = new Thread[threadCount];

        started = done = 0;
        failed = false;

        for (int i=0; i<threadCount; i++) {

            threads[i] = new Thread() {
                public void run() {
                    ResultSet rs;
                    Statement stmt = null;

                    try {
                        stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                        rs = stmt.executeQuery("SELECT * FROM #SAfe0003");

                        assertEquals(null, rs.getWarnings());
                        assertEquals(null, stmt.getWarnings());

                        // Synchronize all threads
                        synchronized (o2) {
                            synchronized(o1) {
                                started++;
                                o1.notify();
                            }

                            try {
                                o2.wait();
                            } catch (InterruptedException e) {
                            }
                        }

                        assertNotNull("executeQuery should not return null", rs);
                        assertTrue(rs.next());
                        assertTrue(rs.next());
                        assertTrue(!rs.next());
                        assertTrue(rs.previous());
                        assertTrue(rs.previous());
                        assertTrue(!rs.previous());
                    } catch (SQLException e) {
                        e.printStackTrace();

                        synchronized (o1) {
                            failed = true;
                        }

                        fail("An SQL Exception occured: "+e);
                    } finally {
                        if (stmt != null) {
                            try {
                                stmt.close();