Executing stored procedures in Firebird using JPA NamedStoredProcedureQuery

Question

    EntityManager em = getEntityManager();

    EntityTransaction etx = em.getTransaction();
    etx.begin();

    Query query = em.createNamedQuery("login_procedure").setParameter("param1","user").setParameter("param2", "pw");


    Integer result = 23;
    try {
        System.out.println("query = " + query.getSingleResult());
    } catch (Exception e) {
        result = null;
        e.printStackTrace();
    }

    etx.commit();
    em.close();

...executing this code I get

[EL Warning]: 2011-02-10 17:32:16.846--UnitOfWork(1267140342)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 1.2.0.v20091016-r5565): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 36 = Error Code: 335544569 Call: EXECUTE PROCEDURE LOGIN_PROCEDURE(USER_NAME = ?, USER_PASSWORD = ?) bind => [user, pw] Query: DataReadQuery(name="login_procedure" )

The -104 SQL error usually indicates a SQL syntax error.

Everything is processed without any error until query.getSingleResult() is called. Calling query.getResultList() doesn't change anything. I've tried several 1.x and 2.x EclipseLink versions. The Firebird DB version is 2.1.

The JPA2 declaration is:

    @Entity
@NamedStoredProcedureQuery(
        name = "login_procedure",
        resultClass = void.class,
        procedureName = "LOGIN_PROCEDURE",
        returnsResultSet = false,
        parameters = {
                @StoredProcedureParameter(queryParameter = "param1", name = "USER_NAME", direction = Direction.IN, type = String.class),
                @StoredProcedureParameter(queryParameter = "param2", name = "USER_PASSWORD", direction = Direction.IN, type = String.class)
        }
)
@Table(name = "USERS")
public class Login implements Serializable {
    @Id
    private Long id;
}

UPDATE: After tinkering a little bit more, I believe there might be an error in the EclipseLink implementation as EXECUTE PROCEDURE LOGIN_PROCEDURE(USER_NAME = ?, USER_PASSWORD = ?) isn't valid Firebird 2.1 syntax for calling procedures.

Answer 1

Inspired by this post, I've found a solution/workaround:

public class JPATest {
final Session session;

JPATest() {
    final String DATABASE_USERNAME = "SYSDBA";
    final String DATABASE_PASSWORD = "masterkey";
    final String DATABASE_URL = "jdbc:firebirdsql:dbServer/3050:e:/my/db.fdb";
    final String DATABASE_DRIVER = "org.firebirdsql.jdbc.FBDriver";

    final DatabaseLogin login = new DatabaseLogin();
    login.setUserName(DATABASE_USERNAME);
    login.setPassword(DATABASE_PASSWORD);
    login.setConnectionString(DATABASE_URL);
    login.setDriverClassName(DATABASE_DRIVER);
    login.setDatasourcePlatform(new FirebirdPlatform());
    login.bindAllParameters();

    final Project project = new Project(login);
    session = project.createDatabaseSession();
    session.setLogLevel(SessionLog.FINE);
    ((DatabaseSession) session).login();

}

public static void main(String[] args) {
    final JPATest jpaTest = new JPATest();
    jpaTest.run();
}

protected void run() {
    testProcCursor();
}

/*
* Run Proc with scalar input and cursor output
*/
@SuppressWarnings("unchecked")
private void testProcCursor() {
    final StoredProcedureCall call = new StoredProcedureCall();
    call.setProcedureName("LOGIN");
    call.addUnamedArgument("USER_NAME"); // .addNamedArgument doesn't work
    call.addUnamedArgument("USER_PASSWORD");

    final DataReadQuery query = new DataReadQuery();
    query.setCall(call);
    query.addArgument("USER_NAME");
    query.addArgument("USER_PASSWORD");

    final List<String> queryArgs = new ArrayList<String>();
    queryArgs.add("onlinetester");
    queryArgs.add("test");

    final List outList = (List) session.executeQuery(query, queryArgs);
    final ListIterator<DatabaseRecord> listIterator = ((List<DatabaseRecord>) outList).listIterator();
    while (listIterator.hasNext()) {
        final DatabaseRecord databaseRecord = listIterator.next();
        System.out.println("Value -->" + databaseRecord.getValues());
    }
}

}

Apparently named parameters aren't supported in my specific configuration but using unnamed parameters in annotations, hasn't solved the problem either. However using unnamed parameters, as specified above, solved the problem for me.

Answer 2

By specifying the name="USER_NAME" you are making Eclipselink use the 'USER_NAME=?' syntax instead of just passing in the unnamed parameter. Try removing the name definition.