Hi all!

I am currently evaluating Firebird in Combination with BEA WebLogic
8.1, and I found that some finder methods of my CMP-EJBs don´t work.
They show an sqlexception in a statement that is supposed to work
(though not an elegant one). I prepared a reproducer (see below).
The PreparedStatement works fine without the parameter
(method "thisWorksFine") but once the parameter is introduced, it
breaks (method "thisDoesntWork"). The exception is:

org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569.
Dynamic SQL Error
SQL error code = -804
Data type unknown
at org.firebirdsql.jdbc.AbstractPreparedStatement.<init>
(AbstractPreparedStatement.java:91)
at org.firebirdsql.jdbc.FBPreparedStatement.<init>
(FBPreparedStatement.java:34)
at org.firebirdsql.jdbc.AbstractConnection.prepareStatement
(AbstractConnection.java:682)
at org.firebirdsql.jdbc.AbstractConnection.prepareStatement
(AbstractConnection.java:232)
at dissclv.test.PreparedStatementTest.thisDoesntWork
<snip>

Do I miss something? I read something about different dialects but
found no further information. Is that a possible cause? Would
someone be so kind an try to reproduce this problem?

Thanks for your replies,

Daniel




package dissclv.test;

import java.sql.*;

/**
* create database 'c:\daten\dissertation\firebird\clv.fdb'
page_size 8192 user 'sysdba' password 'xxx';
*
* create table customers(
* id Integer not null,
* primary key(id))
*
* create table orders(
* id Integer not null,
* customerId Integer not null,
* creationDate TimeStamp not null,
* primary key(id))
*
*
*
*/
public class PreparedStatementTest {

void thisWorksFine() throws Exception
{
DriverManager.registerDriver(new
org.firebirdsql.jdbc.FBDriver());
Connection con = DriverManager.getConnection
("jdbc:firebirdsql:localhost/3050:clv", "sysdba", "xxx");
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = con.prepareStatement("SELECT WL0.id FROM
CUSTOMERS WL0 WHERE\n" +
"( 1 < ( SELECT COUNT(DISTINCT WL2.id ) FROM
CUSTOMERS WL1 , ORDERS WL2 WHERE ( WL0.id = WL1.id ) AND WL1.id =
WL2.customerid) )");
rs = pstmt.executeQuery();
if (rs.next())
{
System.out.println("Query returned results");
}
else
{
System.out.println("Empty resultset");
}
} finally {
try {
if (rs != null) rs.close();
} finally {
}
try {
if (pstmt != null) pstmt.close();
} finally {
}
try {
if (con != null) con.close();
} finally {
}
}
}


void thisDoesntWork() throws Exception
{
DriverManager.registerDriver(new
org.firebirdsql.jdbc.FBDriver());
Connection con = DriverManager.getConnection
("jdbc:firebirdsql:localhost/3050:clv", "sysdba", "clv");
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = con.prepareStatement("SELECT WL0.id FROM
CUSTOMERS WL0 WHERE\n" +
"( ? < ( SELECT COUNT(DISTINCT WL2.id ) FROM
CUSTOMERS WL1 , ORDERS WL2 WHERE ( WL0.id = WL1.id ) AND WL1.id =
WL2.customerid) )");
pstmt.setInt(1,1);
rs = pstmt.executeQuery();
if (rs.next())
{
System.out.println("Query returned results");
}
else
{
System.out.println("Empty resultset");
}
} finally {
try {
if (rs != null) rs.close();
} finally {
}
try {
if (pstmt != null) pstmt.close();
} finally {
}
try {
if (con != null) con.close();
} finally {
}
}
}

public static void main(String[] args) throws Exception{
PreparedStatementTest test = new PreparedStatementTest();
test.thisDoesntWork();
}

}