Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » @NamedStoredProcedureQuery
@NamedStoredProcedureQuery [message #377442] Tue, 29 July 2008 11:43 Go to next message
Mohamed is currently offline Mohamed
Messages: 3
Registered: July 2009
Junior Member
Hello everyone,

I have been trying to run stored procedure using
@NamedStoredProcedureQuery annotation

@NamedStoredProcedureQuery(name="FirstCallProcedure",
resultClass=model.Datetable.class,
procedureName="TESTDATE" )

Procedure does not return anything

…..

Query q = em.createNamedQuery("FirstCallProcedure");
q.getResultList();
//q.executeUpdate();

Warning:

[EPS Warning]: 2008.07.29
11:33:01.562--UnitOfWork(27104945)--java.lang.ClassCastExcep tion:
oracle.jdbc.driver.T4CPreparedStatement cannot be cast to
java.sql.CallableStatement

EclipseLink casting T4CPreparedStatement to CallableStatement


Any idea
Thanks
Re: @NamedStoredProcedureQuery [message #377443 is a reply to message #377442] Wed, 30 July 2008 09:39 Go to previous messageGo to next message
James is currently offline James
Messages: 272
Registered: July 2009
Senior Member
Could you please include the full stack trace to the exception.

It seems odd that your procedure does not have an in or out parameters,
check that you have defined it correctly. A procedure without any in or
out on Oracle does not make sense, perhaps include your procedure as well.

-- James
Re: @NamedStoredProcedureQuery [message #377447 is a reply to message #377443] Wed, 30 July 2008 16:43 Go to previous messageGo to next message
Mohamed is currently offline Mohamed
Messages: 3
Registered: July 2009
Junior Member
Hi James,

I was testing a @NamedStroedProcedureQuery

I created procedure without IN or OUT parameters just testing.
It seams when you have a procedure without IN or OUT parameters, you will
get this kind of warning.

[EPS Warning]: 2008.07.29
11:33:01.562--UnitOfWork(27104945)--java.lang.ClassCastExcep tion:
oracle.jdbc.driver.T4CPreparedStatement cannot be cast to
java.sql.CallableStatement

To reproduce the warning, you can call a procedure without IN/OUT
parameters

Now I have changed to a procedure with IN parameter and no OUT parameter.
Here is the def. of the proc
create or replace procedure INSERTCOUNTRY(cityname IN VARCHAR2)
AS
BEGIN
INSERT IGNORE INTO COUNTRY VALUES(cityname);
END;

ANNOTATION:

@Entity
@NamedStoredProcedureQuery(
name="FirstCallProcedure",
procedureName="INSERTCOUNTRY",
resultClass=void.class,
parameters={@StoredProcedureParameter(direction=Direction.IN ,
name="cityname",
queryParameter="NAME",
type=void.class)}
)
public class Country implements Serializable {
………………


EXECUTION:

EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("JPAStart");
EntityManager em = entityManagerFactory.createEntityManager();

Query q = em.createNamedQuery("FirstCallProcedure");
q.setParameter("NAME", "ITALY");
q.getSingleResult();

EXCEPTION:

[EPS Info]: 2008.07.30 16:08:51.686--ServerSession(19318181)--EclipseLink,
version: Eclipse Persistence Services - 1.0 (Build SNAPSHOT - 20080409)
[EPS Info]: 2008.07.30
16:08:52.076--ServerSession(19318181)--JPATest-session login successful
wait minute I am going to call Stored procedure
[EPS Warning]: 2008.07.30 16:08:52.248--UnitOfWork(16667599)--Exception
[EclipseLink-4002] (Eclipse Persistence Services - 1.0 (Build SNAPSHOT -
20080409)): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'INSERTCOUNTRY'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Error Code: 6550
Call: BEGIN INSERTCOUNTRY(); END;
Query: ResultSetMappingQuery()
Exception [EclipseLink-4002] (Eclipse Persistence Services - 1.0 (Build
SNAPSHOT - 20080409)): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'INSERTCOUNTRY'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Error Code: 6550
Call: BEGIN INSERTCOUNTRY(); END;
Query: ResultSetMappingQuery()


Many thanks indeed
Mohamed
Re: @NamedStoredProcedureQuery [message #377449 is a reply to message #377447] Thu, 31 July 2008 09:10 Go to previous messageGo to next message
James is currently offline James
Messages: 272
Registered: July 2009
Senior Member
The error is indicating your type is wrong. For your VARCHAR param you
must set the type to String.class, not void.class.

-- James
Re: @NamedStoredProcedureQuery [message #377450 is a reply to message #377447] Thu, 31 July 2008 09:16 Go to previous messageGo to next message
James is currently offline James
Messages: 272
Registered: July 2009
Senior Member
Also, could you please log a bug for the error you got using the procedure
with no parameters. It appears we have an issue executing procedures with
no parameters.

https://bugs.eclipse.org/bugs/

-- James
Re: @NamedStoredProcedureQuery [message #377453 is a reply to message #377449] Thu, 31 July 2008 15:43 Go to previous message
Mohamed is currently offline Mohamed
Messages: 3
Registered: July 2009
Junior Member
Even though type is not related to IN parameter as EclipseLink stated.

type – Set this attribute to the type of Java Class that you want to
receive back from the procedure.
This depends on the type returned from the procedure.


I change type=”String.class” and it did not work.

@Entity
@NamedStoredProcedureQuery(
name="FirstCallProcedure",
procedureName="INSERTCOUNTRY",
resultClass=void.class,
parameters={@StoredProcedureParameter(
queryParameter="cityname",
direction=Direction.IN,
type=String.class)}
)


Query q = em.createNamedQuery("FirstCallProcedure");
q.setParameter("cityname", "ITALY");
q.getSingleResult();


Is @NamedStoredProcedureQuery really ever worked?
Is there an example?

Here is the Exception:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 1.0 (Build
SNAPSHOT - 20080409)): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'INSERTCOUNTRY'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Error Code: 6550
Call: BEGIN INSERTCOUNTRY(); END;
Query: ResultSetMappingQuery()

please help
Previous Topic:Basic MOXy Question
Next Topic:unidirectional one-to-many mapping with join column
Goto Forum:
  


Current Time: Thu Aug 21 10:18:15 EDT 2014

Powered by FUDForum. Page generated in 0.02276 seconds