| Problems executing a Stored Procedure [message #639978] |
Thu, 18 November 2010 15:33  |
 Guillermo Aldunate
Messages: 15
Registered: February 2010 |
Junior Member |
|
|
Hello, I've been trying unsuccessfully to get data out of a stored procedure. For the time being the platform is a unit test one, using Eclipse+JUnit, ORM EclipseLink, DBM MS SQL Server. The stored procedure uses no IN or OUT parameters, executes a query that joins a bunch of tables, and returns a recordset consisting of 5K records, each having six columns.
The problem is: I can execute the stored procedure (using both JPA createNativeQuery("NN") and @NamedStoredProcedureQuery + createNamedQuery(name)). In both cases the procedure is executed and the recordset returns the right number of rows (say objects), compared to the ISQL output.
The problem is that when I try to insert the rows into an object list whose objects have their fields defined according to the data types of the columns returned from the stored procedure, I get a ClassCastException, no matter how I define these rows. Now the questions:
1.- Is there a way to display what's being returned?
2.- Can you guide me on what am I doing wrong?
Thanks a lot, Guillermo
|
|
|
| Re: Problems executing a Stored Procedure [message #639984 is a reply to message #639978] |
Thu, 18 November 2010 15:38  |
|
Sounds like your object is using different types than the database is returning.
You could set the class on the native SQL query, then EclipseLink will build the objects for you, and handle any type conversion.
Otherwise you will either need to use the same types as the database is returning, or convert the data to the type you desire. The class cast exception should tell you what the type was.
James : Wiki : Book : Blog : Twitter
|
|
|
| Re: Problems executing a Stored Procedure [message #640045 is a reply to message #639984] |
Thu, 18 November 2010 19:48 |
Guillermo Aldunate
Messages: 15
Registered: February 2010 |
Junior Member |
|
|
Hello James. I'm really messed up with the information appearing in EclipseLink documentation, on one hand, and in ejb-3_0-fr-spec-persistence.pdf (the document detailing persistence in JSR220 specification). For one thing, the spec is written in a very cryptic form, and one can infer that, in order to execute the native query (or stored procedure, for this case) you have to have a predefined entity where to store the results of the query. However, when it comes to a stored procedure, if one defines such an entity in an Eclipse JPA project, it will issue an error saying that there's no table with the entity name in the database (which shouldn't be, since the columns in the stored procedure's query come from several different tables in the database).
On the other hand, the EclipseLink documentation is clearly insufficient (please see the ELUG) and doesn't give details on how to specify each of the annotation parameters (in fact, the only way to guess what does each parameter in the @NamedStoredProcedureQuery mean is to compare them to what appears in the CreateNativeQuery method in the JPA spec), and it doesn't detail how to specify an non-existent object which EclipseLink is to build. 
Please lend me a hand on this, for I'm completely lost. Thanks, Guillermo 
|
|
|
| Re: Problems executing a Stored Procedure [message #640581 is a reply to message #639978] |
Mon, 22 November 2010 14:36 |
|
Please log a bug for anything you find missing in the documentation.
Native SQL queries in JPA can either return the raw data (as returned by the databse),
em.createNativeQuery("Select * from foo");
or, they can return objects if you pass a class to the query and define mappings for that class.
em.createNativeQuery("Select * from foo", Foo.class);
Normally stored procedures return data from somewhere, so there is a table resembling the output that you can map to. Otherwise you can just map the Entity to a dummy table that matches the result set of the procedure. JPA should not care if this table exists or not, as long as you don't try to persist or update the objects. If you have schema validation on, then you will need to turn it off.
You could also select the raw data and map it to your class yourself.
James : Wiki : Book : Blog : Twitter
|
|
|
Powered by
FUDForum. Page generated in 0.01971 seconds
] 
Search
Help
Register
Login
Home
