db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Elliotte Harold" <erhar...@gmail.com>
Subject Re: Adding order by clause eliminates results
Date Mon, 18 Jun 2007 18:19:31 GMT
On 6/18/07, Bryan Pendleton <bpendleton@amberpoint.com> wrote:
>
> > Does this make any sense to anyone? I don't see how adding an ORDER BY
> > clause should change the *number* of results I get.
>
> I don't see how it should, either.
>
> It would be wonderful if you could construct a simple standalone
> reproduction of the problem that you could share with the list.



OK. Here's a stripped down test case that fails with the ORDER BY clause and
passes without it. Change the string "SELECT root_URL FROM foo ORDER BY id"
to ""SELECT root_URL FROM foo" to make it pass.


import java.sql.*;
import java.util.Properties;

import junit.framework.TestCase;

public class Derbytest extends TestCase {

    private ConfigManager manager;
    private final static String driver = "
org.apache.derby.jdbc.EmbeddedDriver";
    private final static String url = "jdbc:derby:derbyDB;";
    private Properties properties = new Properties();

    protected void setUp() throws Exception {
        // Load JDBC driver
        Class.forName(driver).newInstance();
        Connection conn = DriverManager.getConnection(url + "create=true",
properties);

        // TODO We need to refactor this out into a separate setup in an Ant
SQL task
        Statement s = conn.createStatement();
        try {
            s.execute("DROP TABLE foo");
        }
        catch (SQLException ex) { // table doesn't already exist
            if (!ex.getSQLState().equals("42Y55")) {
                throw ex;
            }
        }
        s.execute("CREATE TABLE foo " +
                "(id int NOT NULL, " +
                "root_URL VARCHAR(255) NOT NULL)");
        conn.close();
        manager = new ConfigManager(url, properties);

        super.setUp();

    }


    protected void tearDown() throws Exception {
        try {
            DriverManager.getConnection("jdbc:derby:derbyDB;shutdown=true");
        }
        catch (SQLException expected) {
             if (expected.getErrorCode() != 45000) throw expected;
        }
        super.tearDown();
    }


    public void testGetOne() throws SQLException {
        Connection conn = DriverManager.getConnection(url, properties);
        Statement s = conn.createStatement();
        s.execute("INSERT INTO foo (id, root_url) VALUES (1, '
http://example.com/')");
        int numberBoards = manager.getCount();
        assertEquals(1, numberBoards);
    }

    class ConfigManager {

        private Connection conn;
        private ResultSet results;

        /**
         * @param url JDBC connection string
         * @param properties JDBC properties
         * @throws SQLException if the database connection fails
         */
        public ConfigManager(String url, Properties properties) throws
SQLException {
            conn = DriverManager.getConnection(url, properties);
            Statement statement = conn.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            // why does this return no results when we add an order by
clause?
            statement.execute("SELECT root_URL FROM foo ORDER BY id");
            results = statement.getResultSet();
        }

        /**
         * @throws SQLException
         */
        public int getCount() throws SQLException {
            results.last();
            return results.getRow();
        }

    }

}

thanks,
>
> bryan
>
>
>


-- 
Elliotte Rusty Harold
erharold@gmail.com

Mime
View raw message