PreparedStatement.setObject() and String arrays

[Penta]

Hi, i must use an IN clause in a PreparedStatement, and as stated in http://www.h2database.com/html/performance.html (Prepared Statements and IN(...)) I used the setObject() method.

This is my test case but something is wrong and no records are returned from my PreparedStatement. What's wrong?

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import org.h2.util.JdbcUtils;

public class TestSelect {

public static void main(String[] args) throws Exception {

Connection conn = null;

Statement stmt = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try {

Class.forName("org.h2.Driver");

conn = DriverManager.getConnection("jdbc:h2:mem:test", "sa", "sa");

stmt = conn.createStatement();

stmt.execute("create temp table mytest(pk identity, col varchar(20))");

stmt.execute("insert into mytest (col) values('1')");

stmt.execute("insert into mytest (col) values('2')");

stmt.execute("insert into mytest (col) values('3')");

//this doesn't work, no records returned

pstmt = conn.prepareStatement("select pk from mytest where col in (?)");

pstmt.setObject(1, new Object[] { "1", "2" });

rs = pstmt.executeQuery();

while (rs.next()) {

System.out.println("pk " + rs.getLong(1));

}

JdbcUtils.closeSilently(rs);

JdbcUtils.closeSilently(pstmt);

//this works

pstmt = conn.prepareStatement("select pk from mytest where col in ('1','2')");

rs = pstmt.executeQuery();

while (rs.next()) {

System.out.println("pk " + rs.getLong(1));

}

} finally {

JdbcUtils.closeSilently(rs);

JdbcUtils.closeSilently(pstmt);

JdbcUtils.closeSilently(conn);

}

}

}

[Noel Grandin]

Try reading the documentation again.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/KNhefTqWTKEJ.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Penta]

OK, my fault, I didn't understand the syntax, so the correct statement is "select pk from table(x int=?) t inner join mytest on t.x=mytest.col" and it works, but what about delete statements? What kind of syntax must I use?

[Noel Grandin]

Talk about laziness.

How about you try some stuff and see what works or doesn't.
And if you can't figure anything out, then can you come back here.

Hint - the "select pk from table(x int-=?)" part is re-usable.

[Penta]

I already tried "delete from table(x int=?) t inner join mytest on t.x=mytest.col" and I'm sure this is wrong, not only because I have an error, but even because I'm not so expert in SQL and I can't even understand what i wrote, my previous select statement was made with a stupid cut & paste and I'm not proud of this but that's it.

I posted on this forum because I tried many times and I wasn't able to solve this problem, it's 2 years I use H2 in my project and since this I have 2 posts in this group, so I don't think I'm so lazy passing my time asking people doing my job.

Maybe you should use more kindness in your answers, but don't worry I made a batch of single delete statements and I obtained what I want, so I will not bother you anymore.

[Noel Grandin]

On 2012-12-12 11:56, Penta wrote:
> I already tried "delete from table(x int=?) t inner join mytest on
> t.x=mytest.col" and I'm sure this is wrong, not only because I have an
> error, but even because I'm not so expert in SQL and I can't even
> understand what i wrote, my previous select statement was made with a
> stupid cut & paste and I'm not proud of this but that's it.

Well there is your problem in a nutshell.
Instead of cookbook coding, trying to understand SQL will let you get
much further.

Try this:
http://www.w3schools.com/sql/default.asp

If you're having trouble understanding it because it's not in your
native language, install Google Chrome and the Google Chrome Translator Bar
https://chrome.google.com/webstore/detail/google-translate/aapbdbdomjkkjkaonfhkkikfgjllcleb

> I posted on this forum because I tried many times and I wasn't able to
> solve this problem, it's 2 years I use H2 in my project and since this
> I have 2 posts in this group, so I don't think I'm so lazy passing my
> time asking people doing my job.
> Maybe you should use more kindness in your answers, but don't worry I
> made a batch of single delete statements and I obtained what I want,
> so I will not bother you anymore.
>

Would you rather have kindness, or have someone actually trying to help you?
I can always just keep quiet and let you stew in your own ignorance.

[Thomas Mueller]

Hi,

There's also StackOverflow you could go to for help.

But, basically I think the solution is to use "IN(select pk from table(x int-=?))". Usually the learning effect is better if people find the solution themselves...

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To post to this group, send email to h2-da...@googlegroups.com.

To unsubscribe from this group, send email to h2-database+unsubscribe@googlegroups.com.

[Penta]

Thanks to all, 

effectively the solution "IN(select pk from table(x int-=?))" was ridiculously simple, but I was stuck using inner join trying to replicate the select syntax without an inner query.... I'm a bit ashamed :-(

Not all days are good days...

Thanks again.

To unsubscribe from this group, send email to h2-database...@googlegroups.com.