Home Dashboard Directory Help
Search

SQL Server 2005 JDBC Driver returns incomplete set of rows by jmarold


Status: 

Closed
 as Fixed Help for as Fixed


0
0
Sign in
to vote
Type: Bug
ID: 273735
Opened: 4/26/2007 9:49:27 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

Attempt a select with an error when specifying a maximum number of rows. Then on
the same connection do a new (valid) select without specifying a max number of
rows. The max number of rows is specified on the statement -- a new statement was
used.
SQL Server 2005 JDBC driver incorectly limits the number of rows returned.
SQL Server 2000 JDBC driver correctly returns the full set of rows.

// Here is a Java class which demonstrates the problem.
// This uses the Northwind database.


public class SelectTest
{

    public static void main(String[] args) throws Exception
    {
        if(4 != args.length)
        {
            System.err.println("Usage: java SelectTest <jdbc class> <db url> <db user> <db password>");
            System.exit(1);
        }
        System.err.println("Loading class");
        Class.forName(args[0]);
        System.err.println("Getting connection");
        java.sql.Connection con1 = java.sql.DriverManager.getConnection(args[1], args[2], args[3]);
        System.err.println("Retrieving data from good table");
        int initialCount = doSelectTransaction("Order Details", "", con1);
        System.err.println("Number of rows retrieved: "+ Integer.toString(initialCount));
        System.err.println("Retrieving limited data from good table");
        int intermediateCount = doSelectTransaction("Order Details", "WHERE ProductID='59'", con1);
        System.err.println("Number of rows retrieved: "+ Integer.toString(intermediateCount));
        // now do error retrieval
        try
        {
            System.err.println("Retrieving 1 row from invalid table");
            int badCount = doSelectSingle("salesZZ", "", con1);
            System.err.println("Number of rows retrieved: "+ Integer.toString(badCount));
        }
        catch (Exception e)
        {
            System.err.println(e);
        }
        System.err.println("Retrieving data from good table again");
        int finalCount = doSelectTransaction("Order Details", "", con1);
        System.err.println("Number of rows retrieved: "+ Integer.toString(finalCount));
        if ( ! (finalCount==initialCount))
        {
            System.err.println("ERROR: Wrong number of rows retrieved.");
        }


    }

    public static int
    doSelectTransaction(String table, String where, java.sql.Connection connection) throws Exception
    {
        connection.setAutoCommit(false);
        String sql = "select * from [" + table + "] " + where + ";";
        java.sql.Statement statement = null;
        try
        {
            statement = connection.createStatement();
            java.sql.ResultSet resultSet = statement.executeQuery(sql);
            int count = 0;
            while (resultSet.next())
            {
                count++;
            }
            statement.close();
            connection.commit();
            connection.setAutoCommit(true);

            return (count);
        }
        catch(java.sql.SQLException e)
        {
            throw new Exception(e);
        }
        catch(java.lang.Exception e)
        {
            throw new Exception(e);
        }
        finally
        {
            if (statement != null)
            {
                try
                {
                    statement.close();
                }
                catch (java.sql.SQLException e)
                {
                }
            }
        }
    }

    public static int
    doSelectSingle(String table, String where, java.sql.Connection connection) throws Exception
    {

        String sql = "select * from [" + table + "] " + where + ";";
        java.sql.Statement statement = null;
        try
        {
            statement = connection.createStatement();
            statement.setMaxRows(1);
            java.sql.ResultSet resultSet = statement.executeQuery(sql);
            int count = 0;
            while (resultSet.next())
            {
                count++;
            }
            statement.close();

            return (count);
        }
        catch(java.sql.SQLException e)
        {
            throw new Exception(e);
        }
        catch(java.lang.Exception e)
        {
            throw new Exception(e);
        }
        finally
        {
            if (statement != null)
            {
                try
                {
                    statement.close();
                }
                catch (java.sql.SQLException e)
                {
                }
            }
        }
    }
}



Details
Sign in to post a comment.
Posted by Microsoft on 6/11/2007 at 10:21 AM
Thank you for your feedback. We have identified the problem and the fix will be available in future releases.
Sign in to post a workaround.