Re: Bug in Embedded Driver?
It seems to me that the compiler should not think it has enough information at prepareStatement() time to assign the ? a type of VARCHAR. That looks like a bug to me. As a workaround, the following statement gives the compiler enough information to correctly type the ? parameter. This allows you to set the ? parameter to a null VARBINARY value and then execute the query. This works under both the embedded and network clients: select count(*) from Issue where cast (? as varchar(20) for bit data) is null Regards, Rick Xavier Hanin wrote: On 3/31/07, *Oystein Grovlen - Sun Norway* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Xavier Hanin wrote: On 3/31/07, *Øystein Grøvlen* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] I'm using version 10.2.2.0 http://10.2.2.0 http://10.2.2.0. There have been some fixes in this area lately (see DERBY-1610), and I will try your test case on the development trunk later today. Ok, thanks a lot. I tried the development version, and now it fails for both client and embedded. The failure in the client is probably due to stricter type checking in the client driver. What happens seems to be that the compiler determines the type of the parameter to be VARCHAR during compilation, and hence you get a type mismatch later. I do not understand why it does so. If nobody can explain why, this is probably a bug, and I suggest you open a new JIRA issue for this. JIRA issue created: DERBY-2507 Thanks for your help! - Xavier -- Øystein -- Learn Ivy at ApacheCon: http://www.eu.apachecon.com/ Manage your dependencies with Ivy! http://incubator.apache.org/ivy/
Re: Bug in Embedded Driver?
On 4/2/07, Rick Hillegas [EMAIL PROTECTED] wrote: It seems to me that the compiler should not think it has enough information at prepareStatement() time to assign the ? a type of VARCHAR. That looks like a bug to me. As a workaround, the following statement gives the compiler enough information to correctly type the ? parameter. This allows you to set the ? parameter to a null VARBINARY value and then execute the query. This works under both the embedded and network clients: select count(*) from Issue where cast (? as varchar(20) for bit data) is null Ok, thanks a lot for the workaround, even if I don't think I'll be able to use it since Hibernate is generating the query for me. But it won't get me out of using Derby, it eases my developments a lot! - Xavier Regards, Rick Xavier Hanin wrote: On 3/31/07, *Oystein Grovlen - Sun Norway* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Xavier Hanin wrote: On 3/31/07, *Øystein Grøvlen* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] I'm using version 10.2.2.0 http://10.2.2.0 http://10.2.2.0. There have been some fixes in this area lately (see DERBY-1610), and I will try your test case on the development trunk later today. Ok, thanks a lot. I tried the development version, and now it fails for both client and embedded. The failure in the client is probably due to stricter type checking in the client driver. What happens seems to be that the compiler determines the type of the parameter to be VARCHAR during compilation, and hence you get a type mismatch later. I do not understand why it does so. If nobody can explain why, this is probably a bug, and I suggest you open a new JIRA issue for this. JIRA issue created: DERBY-2507 Thanks for your help! - Xavier -- Øystein -- Learn Ivy at ApacheCon: http://www.eu.apachecon.com/ Manage your dependencies with Ivy! http://incubator.apache.org/ivy/ -- Learn Ivy at ApacheCon: http://www.eu.apachecon.com/ Manage your dependencies with Ivy! http://incubator.apache.org/ivy/
Re: Bug in Embedded Driver?
On 3/31/07, Oystein Grovlen - Sun Norway [EMAIL PROTECTED] wrote: Xavier Hanin wrote: On 3/31/07, *Øystein Grøvlen* [EMAIL PROTECTED] I'm using version 10.2.2.0 http://10.2.2.0. There have been some fixes in this area lately (see DERBY-1610), and I will try your test case on the development trunk later today. Ok, thanks a lot. I tried the development version, and now it fails for both client and embedded. The failure in the client is probably due to stricter type checking in the client driver. What happens seems to be that the compiler determines the type of the parameter to be VARCHAR during compilation, and hence you get a type mismatch later. I do not understand why it does so. If nobody can explain why, this is probably a bug, and I suggest you open a new JIRA issue for this. JIRA issue created: DERBY-2507 Thanks for your help! - Xavier -- Øystein -- Learn Ivy at ApacheCon: http://www.eu.apachecon.com/ Manage your dependencies with Ivy! http://incubator.apache.org/ivy/
Bug in Embedded Driver?
Hi Derby community,
I'm currently using Derby on a freshly started project, and I ran into an
issue which seems to be a bug.
Indeed the same code works well with the Client Driver, and not with the
Embedded Driver. The error I get from Derby is:
org.apache.derby.impl.jdbc.EmbedSQLException: An attempt was made to get a
data value of type 'VARCHAR' from a data value of type 'VARBINARY'
Here is a code snippet reproducing the problem:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import org.apache.derby.jdbc.ClientDriver;
import org.apache.derby.jdbc.EmbeddedDriver;
public class DerbyTest {
public static void main(String[] args) throws Exception {
Class.forName(EmbeddedDriver.class.getName());
//Class.forName(ClientDriver.class.getName());
Connection con =
DriverManager.getConnection(jdbc:derby:test;create=true,
app, app);
//Connection con =
DriverManager.getConnection(jdbc:derby://localhost:1527/test;create=true,
app, app);
try {
con.createStatement().executeUpdate(create table Issue ( ID
char(50) ));
PreparedStatement st = con.prepareStatement(select count(*)
from Issue where ? is null);
st.setNull(1, Types.VARBINARY);
ResultSet rs = st.executeQuery();
System.out.println(rs.next());
} finally {
con.close();
}
}
}
If you switch to the ClientDriver, it's working properly.
Is it a bug? Or is it an incorrect use of the JDBC API, working with client
driver by chance?
FYI, here's some details on my environment:
Derby 10.2.2.0
Sun Windows JDK 6.0
Xavier
--
Learn Ivy at ApacheCon: http://www.eu.apachecon.com/
Manage your dependencies with Ivy!
http://incubator.apache.org/ivy/
Re: Bug in Embedded Driver?
Xavier Hanin wrote: con.createStatement().executeUpdate(create table Issue ( ID char(50) )); PreparedStatement st = con.prepareStatement(select count(*) from Issue where ? is null); st.setNull(1, Types.VARBINARY); I do not think that CHAR and VARBINARY are compatible types. Try using Types.VARCHAR instead. In this case I think it is the client driver that is wrong. It is not first example where it allows type conversions that are not according to the spec. (Ref. https://issues.apache.org/jira/browse/DERBY-1501) -- Øystein
Re: Bug in Embedded Driver?
Xavier Hanin wrote: On 3/31/07, *Øystein Grøvlen* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Xavier Hanin wrote: con.createStatement().executeUpdate(create table Issue ( ID char(50) )); PreparedStatement st = con.prepareStatement(select count(*) from Issue where ? is null); st.setNull(1, Types.VARBINARY); I do not think that CHAR and VARBINARY are compatible types. Try using Types.VARCHAR instead. Yes, but why does derby consider the type as CHAR? Is it something from the specification to assume CHAR when no type can be assumed from the query? Note that I do not compare to the only column I've created. The column could have been compatible with VARBINARY. Ah, I see. My mistake. Then I do not see a reason why this should not be allowed. Which version of Derby are you using? There have been some fixes in this area lately (see DERBY-1610), and I will try your test case on the development trunk later today. Indeed in my usecase I can't use VARCHAR because I'm not at the origin of the query. I use hibernate and do something like this: from Issue where :status is null or status = :status Hibernate converts that to something like select * from Issue where ? is null or status_id = ? and bind the two parameters with the same value, i.e. the id of the status I bind in Hibernate, which is a BIGINT, and thus use VARBINARY as sql type for both parameters binding. And I can't really blame Hibernate, can I? Why not? :-) I would blame Hibernate for using VARBINARY when it should use BIGINT, but I guess that is not the issue here. -- Øystein
Re: Bug in Embedded Driver?
On 3/31/07, Øystein Grøvlen [EMAIL PROTECTED] wrote: Xavier Hanin wrote: On 3/31/07, *Øystein Grøvlen* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Xavier Hanin wrote: con.createStatement().executeUpdate(create table Issue ( ID char(50) )); PreparedStatement st = con.prepareStatement(select count(*) from Issue where ? is null); st.setNull(1, Types.VARBINARY); I do not think that CHAR and VARBINARY are compatible types. Try using Types.VARCHAR instead. Yes, but why does derby consider the type as CHAR? Is it something from the specification to assume CHAR when no type can be assumed from the query? Note that I do not compare to the only column I've created. The column could have been compatible with VARBINARY. Ah, I see. My mistake. Then I do not see a reason why this should not be allowed. Which version of Derby are you using? I'm using version 10.2.2.0. There have been some fixes in this area lately (see DERBY-1610), and I will try your test case on the development trunk later today. Ok, thanks a lot. Indeed in my usecase I can't use VARCHAR because I'm not at the origin of the query. I use hibernate and do something like this: from Issue where :status is null or status = :status Hibernate converts that to something like select * from Issue where ? is null or status_id = ? and bind the two parameters with the same value, i.e. the id of the status I bind in Hibernate, which is a BIGINT, and thus use VARBINARY as sql type for both parameters binding. And I can't really blame Hibernate, can I? Why not? :-) I didn't mean that I can never blame hibernate, but in this case I don't see how they could use another sql type. I would blame Hibernate for using VARBINARY when it should use BIGINT, but I guess that is not the issue here. Agreed. Thanks for your time. - Xavier -- Learn Ivy at ApacheCon: http://www.eu.apachecon.com/ Manage your dependencies with Ivy! http://incubator.apache.org/ivy/
Re: Bug in Embedded Driver?
Xavier Hanin wrote: On 3/31/07, *Øystein Grøvlen* [EMAIL PROTECTED] I'm using version 10.2.2.0 http://10.2.2.0. There have been some fixes in this area lately (see DERBY-1610), and I will try your test case on the development trunk later today. Ok, thanks a lot. I tried the development version, and now it fails for both client and embedded. The failure in the client is probably due to stricter type checking in the client driver. What happens seems to be that the compiler determines the type of the parameter to be VARCHAR during compilation, and hence you get a type mismatch later. I do not understand why it does so. If nobody can explain why, this is probably a bug, and I suggest you open a new JIRA issue for this. -- Øystein
