PreparedStatement with IN (array) doesn't work

[rdicroce]

Per the answer to this StackOverflow question, it should be possible to pass an Object[] to PreparedStatement#setObject() to parameterize an IN clause.

However, using the latest 1.4.187, it doesn't work. As an example, running the below code prints nothing. It appears that H2 is interpreting the query as

select * from test where name in (('foo', 'bar'))

which returns zero rows (note the extra set of parentheses), rather than the expected

select * from test where name in ('foo', 'bar')

H2 throws a parse exception if you attempt to "fix" this by removing the parentheses in the prepared statement, like this:

select * from test where name in ?

/*
 * Copyright 2004-2014 H2 Group. Multiple-Licensed under the MPL 2.0,
 * and the EPL 1.0 (http://h2database.com/html/license.html).
 * Initial Developer: H2 Group
 */
package h2stats;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.h2.tools.DeleteDbFiles;

/**
 * A very simple class that shows how to load the driver, create a database,
 * create a table, and insert some data.
 */
public class InArrayBugTest {

    /**
     * Called when ran from command line.
     *
     * @param args ignored
     */
    public static void main(String... args) throws Exception {
        // delete the database named 'test' in the user home directory
        //DeleteDbFiles.execute("~", "test", true);

        Class.forName("org.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:h2:D:/temp/inArrayBugTest");
        Statement stat = conn.createStatement();

        // this line would initialize the database
        // from the SQL script file 'init.sql'
        // stat.execute("runscript from 'init.sql'");

        stat.execute("create table test(id int primary key, name varchar(255))");
        stat.execute("insert into test values(1, 'Hello')");
        stat.execute("insert into test values(2, 'foo')");
        stat.execute("insert into test values(3, 'bar')");

        PreparedStatement ps = conn.prepareStatement("select * from test where name in (?)");
        ps.setObject(1, new Object[] {"foo", "bar"});

        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }

        ps.close();
        stat.close();
        conn.close();
    }

}

[thomasmueller]

Hi,

I'm sorry, my answer to this StackOverflow question was wrong. I have fixed
it now:

PreparedStatement prep = conn.prepareStatement(
"select * from users where login in (select * from table(x int = ?))");
prep.setObject(1, new Object[] { "1", "2" });ResultSet rs = prep.executeQuery();

Does that answer your question?

Regards,
Thomas

On Thursday, July 2, 2015, Rich DiCroce notifications@github.com wrote:

Per the answer to this StackOverflow question
http://stackoverflow.com/questions/3723854/jdbc-in-set-condition-can-i-pass-a-set-as-single-param,
it should be possible to pass an Object[] to PreparedStatement#setObject()
to parameterize an IN clause.

However, using the latest 1.4.187, it doesn't work. As an example, running
the below code prints nothing. It appears that H2 is interpreting the query
as

select * from test where name in (('foo', 'bar'))

which returns zero rows (note the extra set of parentheses), rather than
the expected

select * from test where name in ('foo', 'bar')

H2 throws a parse exception if you attempt to "fix" this by removing the
parentheses in the prepared statement, like this:

select * from test where name in ?

/* * Copyright 2004-2014 H2 Group. Multiple-Licensed under the MPL 2.0, * and the EPL 1.0 (http://h2database.com/html/license.html). * Initial Developer: H2 Group /package h2stats;
import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.Statement;
import org.h2.tools.DeleteDbFiles;
/* * A very simple class that shows how to load the driver, create a database, * create a table, and insert some data. */public class InArrayBugTest {

/**     * Called when ran from command line.     *     * @param args ignored     */
public static void main(String... args) throws Exception {
    // delete the database named 'test' in the user home directory
    //DeleteDbFiles.execute("~", "test", true);

    Class.forName("org.h2.Driver");
    Connection conn = DriverManager.getConnection("jdbc:h2:D:/temp/inArrayBugTest");
    Statement stat = conn.createStatement();

    // this line would initialize the database
    // from the SQL script file 'init.sql'
    // stat.execute("runscript from 'init.sql'");

    stat.execute("create table test(id int primary key, name varchar(255))");
    stat.execute("insert into test values(1, 'Hello')");
    stat.execute("insert into test values(2, 'foo')");
    stat.execute("insert into test values(3, 'bar')");

    PreparedStatement ps = conn.prepareStatement("select * from test where name in (?)");
    ps.setObject(1, new Object[] {"foo", "bar"});

    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        System.out.println(rs.getString("name"));
    }

    ps.close();
    stat.close();
    conn.close();
}

}

—
Reply to this email directly or view it on GitHub
#149.

[rdicroce]

Yes, that works much better. Thanks Thomas!