Native Queries in Netweaver 7.1

[Former Member]

Hello Forum,

how do i convince the EntityManager that a select statement used for a native query is actually a select statement? I tried to issue an SQL statement starting with the keyword "select", containing a few parameters (i used a bare "?" to denote them, would this be correct?), a subselect, and some grouping operations, but upon calling getResultList(), the entity manager threw an Exception stating that the select statement was not a select statement.

Regards,

- Thomas

[Former Member]

In fact, the tables and columns have German names (e. g. "messwert"), i just translated them for the sake of readability. The data source (we have our own one) is set to "native SQL" (i think, but i will check this out), so the if (...) part should go through to oracle unchanged. (Well, this is what i expect it to do.) I will try and add the hint mentioned by Adrian.

Since the exception complains about "not a select statement", i suspect a more fundamental problem than just syntax details. But which one?

I tried the view approach earlier when using mysql (hiding away SQL dialect details that way), but the performance dropped to an unusable level, as mysql calculated the whole of the result set first (a few million rows) and filtered the result afterwards to a few hundred rows. Oracle would probably do better.

Regards

- Thomas

[rolf_paulsen]

Hi Thomas,

since you are using MySQL, your data source is not the system data source and it has nothing to do with Open SQL.

You were right: It is a basic issue. Doing some additional research with trivial native query strings, I found out that SAP JPA unlike other JPA providers obviously does not support untyped queries (result sets List<Object[]>).

EntityManager.createNativeQuery only works with SELECT statements if the second parameter is set (persistent class or String with SqlResultSetMapping). The error message "...is not a SELECT statement" is of course misleading (should read e.g. "...is not a DML statement").

(Reading the javadoc of EntityManager:

/**
* Create an instance of Query for executing
* a native SQL statement, e.g., for update or delete.
* @param sqlString a native SQL query string
* @return the new query instance
*/
public Query createNativeQuery(String sqlString);

we see that untyped native queries are an extra feature of e.g. EclipeLink.)

If using a persistent class as second parameter, you have to define a matching @Entity class (and matching table in the database), make sure your query returns a unique id.

I did not check with result set mapping but I guess you will have to define the @Entity and database table as well.

Regards,

-Rolf

[Former Member]

Hi Rolf,

thanks a lot for your research. In fact our data source is independent from the system data source and points to a different scheme on the oracle database.

I will check out how portable the createNativeQuery(..., SomeClass.class) method is -- and if there is a way to calculate a sufficiently unique id. Is there a way to keep SAP JPA from complaining about the missing table for the to-be-created entity? (We could of course create a table and leave it empty.)

At least there is a way now to go on.

Thanks and regards

- Thomas

[adrian_goerler]

Hi,

could you please provide a stack trace in order to determine the origine of the "not a select statement" exception.

-Adrian

[adrian_goerler]

>

> I did not check with result set mapping but I guess you will have to define the @Entity and database table as well.
> 
> Regards,
> -Rolf

A result set mapping does not require an entity. You can just specify a list of column results (i.e result columns 😞

@SqlResultSetMapping(name="myResults",columns={

@ColumnResult(name="column1"),

@ColumnResult(name="column2")

})

Using an entity result would create you a managed enity instance, which is likely not what you are intending.

Unfortunately, you can't specify a result set mapping dynamically.

-Adrian

[Former Member]

@Rolf: could you post a stacktrace created during your research? I don't have access to the old stack traces any longer.

@Adrian: thanks a lot for that hint, i wasn't aware of that annotation. Using non-entity results will save us quite some hassle.

[rolf_paulsen]

knob-creek wrote:

@Rolf: could you post a stacktrace created during your research? I don't have access to the old stack traces any longer.

Here you are:

Query q  = em.createNativeQuery("SELECT dummy FROM DUAL d");
q.getResultList();

Exception in thread "main" java.lang.IllegalStateException: The statement >>SELECT dummy FROM DUAL d<< is no SELECT statement and cannot be executed with the executeQuery method.
	at com.sap.engine.services.orpersistence.query.AbstractQuery.getResultList(AbstractQuery.java:194)
	at com.sap.engine.services.orpersistence.query.NativeQuery.getResultList(NativeQuery.java:35)
	at test.Test.main(Test.java:47)

@Adrian, you are the JEE encyclopedia!

[adrian_goerler]

Hi,

the exception text "The statement >>SELECT dummy FROM DUAL d<< is no SELECT statement " is misleading. SAP JPA just assumes that native queries that have been created with the

createNativeQuery(String sql)

method are DML statements and rejects an attempt to execute them as query using getResultList or getSingleResult. If a native query has been created using either

createNativeQuery(String sql, Class clazz) or createNativeQuery(String sql, String resultSetMappingName),

it is assumed that it is a query and no DML statement and you may execute it using getResultList or getSingleResult (but you can't use executeUpdate).

-Adrian

[Former Member]

Hello Forum,

a happy new year to you all!

(Back to my original question This is the native query i wanted to submit (slightly abbreviated), which was rejected:

"select smw.gdate as gdate, smw.quant as quant," +
" max(smw.prog) as prog," +
" sum(smw.value * zs.weight) / 100 as Q," +
" sum(least(smw.value, 16) * zs.weight) / 100 as QS," +
" sum(if(smw.value < 15, 20 - smw.value, 0) * zs.weight) / 100 as QG " +
"from Station zs" +
" join StationData smw on (zs.id_station = smw.id_station) " +
"where zs.id = ? " +
" and smw.best = 1 " +
" and smw.gdate between ? and ? " +
"group by smw.gdate, smw.quant";

I expected it to return a

List<Object[]>

.

I tried to pass the parameters as "?N" and a bare "?", none of which worked.

[rolf_paulsen]

Hi Thomas,

happy new year to you, too!

Your query definitely is not understandable for Open SQL because of the IF-function and probably because of the column named "value". If you are running against system datasource, you will have to bypass Open SQL as Adrian described above.

Anyway, it is not a good idea to name a column "value" (reserved keyword in Open SQL, comparable to "NVL" in Oracle).

Too keep things simple in Java, maybe you may create a view containing your SQL statement and select from this view with plain non-native JPQL.

create or replace view myview as 
select 
zs.id as id,
smw.gdate as gdate, 
smw.quant as quant,
max(smw.prog) as prog,
sum(smw.value * zs.weight) / 100 as Q,
sum(least(smw.value, 16) * zs.weight) / 100 as QS,
sum(if(smw.value < 15, 20 - smw.value, 0) * zs.weight) / 100 as QG
from Station zs
join StationData smw on (zs.id_station = smw.id_station)
where smw.best = 1
group by smw.gdate, smw.quant, zs.id;

or similar (It is important to extend the group by to "id") and change your query to

select ... from myview where zs.id = ? and smw.gdate between ? and ?

Regards,

Rolf

[adrian_goerler]

Hi,

if you need to use native queries, please make sure that you create the query with the createNativeQuery method. Using "?" as parameter marker should be correct (unless the native dialect of your DB requires something different). "?1" and ":foo" are not supported.

If you are running on Open SQL, you must be aware of the fact that the term "native query might misleading" as the query, you are expressing in SQL is still executed against Open SQL and all rules of Open SQL will apply. If you are running on Open SQL but need to bypass Open SQL checks for a particular query you can do so as decribed here:

http://help.sap.com/saphelp_nw73/helpdata/en/4a/0cf02870c540caab611d56220ec0cb/frameset.htm.

-Adrian

[rolf_paulsen]

Hi,

> Adrian Goerler wrote:

if you need to use native queries, please make sure that you create the query with the createNativeQuery method. Using "?" as parameter marker should be correct (unless the native dialect of your DB requires something different). "?1" and ":foo" are not supported.

In native queries, according to the JPA spec, using positional parameters like :"?1", "?2",... is the only way to add parameters. Neither named parameters like ":foo" nor plain "?" are supported according to JPA 1.0 spec.

See JSR 220: Enterprise JavaBeansTM,Version 3.0, Java Persistence API, section 3.6.3 "Named parameters":

Only positional parameter binding may be portably used for native queries.

I only have (really good) experience with EclipsesLink where defining parameters in native queries works fine with ?1, ?2 like in the JPA spec. If in SAP JPA parameters in native queries must be defined in the query string using plain ?, it will not be compliant with the spec.

Let me add two points to EclipseLink

1. > Adrian Goerler wrote:

EclipseLink and Open JPA require byte code weaving. SAP NetWeaver AS Java does not support dynamic byte code weaving, you need to use static weaving instead. 

EclipseLink works great without any byte code weaving at all. (I guess the same applies for Open JPA). The only constraint is that you have no lazy loading for ManyToOne and OneToOne relations, only lazy loading for ...ToMany relations, but exactly the same constraint applies for SAP JPA before NetWeaver 7.30 without any bytecode weaving option to cure it.

And in SAP JPA NetWeaver 7.30, we do not get fully transparent lazy loading like in EclipseLink but a weaker form using cglib with EntitiyManager.getReference(): Whenever you need lazy loading, you have to hand-code it into your code.

2. SAP (e.g. Adrian Goerler) contributed directly to EclipseLink to support it on NetWeaver, see Sabine Heider's other blog

[http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/19824] [original link is broken] [original link is broken] [original link is broken];

and of course [http://wiki.eclipse.org/EclipseLink/Development/ServerPlatform/NetweaverPlatform]. If you have an S-User, get and read "CD104 SAP and Open Source:Combining Both for the Benefit of our Customers" (2009) to see that SAP evaluated EclipseLink as the candidate to support JPA 2.0 in SAP NetWeaver Java, not Hibernate, not Open JPA, and not SAP JPA.

Cheers,

- Rolf

[adrian_goerler]

In native queries, according to the JPA spec, using positional parameters like :"?1", "?2",... is the only way to add parameters. Neither named parameters like ":foo" nor plain "?" are supported according to JPA 1.0 spec.
See JSR 220: Enterprise JavaBeansTM,Version 3.0, Java Persistence API, section 3.6.3 "Named parameters":
Only positional parameter binding may be portably used for native queries.
I only have (really good) experience with EclipsesLink where defining parameters in native queries works fine with ?1, ?2 like in the JPA spec. If in  SAP JPA parameters in native queries must be defined in the query string using plain ?, it will not be compliant with the spec. 

SAP JPA passes the SQL string specified in a native query raw (unparsed) to the database. This means that SAP JPA does make any assumption with respect to the syntax required by the underlying database with respect to paramter markers. In standard SQL, a plain ? character is used as a parameter marker. Standard SQL does not understand ?1 nor :foo.

The only binding mechanism for parameter values in native queries supported by SAP JPA is positional (as in alignment with the spec). Example

Query query = em.createNativeQuery("SELECT LAST_NAME FROM EMPLOYEE WHERE FIRST_NAME = ?");
query.setParameter(1, "Tim");

> And in SAP JPA NetWeaver 7.30, we do not get fully transparent lazy loading like in EclipseLink but a weaker form using cglib with EntitiyManager.getReference(): Whenever you need lazy loading, you have to hand-code it into your code.

This is a misundersanding. In 7.30, lazy loading of single-valued relationships is supported as an automatic mechanism. You do not need to programatically use getReference. However, as lazy loading of single-valued relationships has been introduced onyl with 7.30, you need enabled it explicitly in order to preserve a downward compatible behavior:

http://help.sap.com/saphelp_nw73/helpdata/en/68/f676ef36094f4381467a308a98fd2a/frameset.htm

-Adrian

[rolf_paulsen]

Hi Adrian,

you wrote:

SAP JPA passes the SQL string specified in a native query raw (unparsed) to the database

This is not what is defined in the JPA spec. A positional parameter is not a plain ? (JDBC-standard, has nothing to do with native dialect of the database) but a ? followed by an integer (?1, ?2, ...):

4.6.4.1 Positional Parameters
The following rules apply to positional parameters.
u2022 Input parameters are designated by the question mark (?) prefix followed by an integer. For
example: ?1.

According to the spec, a native query is not parsed except the parameters. If as you write SAP JPA does not support positional JPQL-like ?n parameters in native queries, it deviates from JPA spec in this point. EclipseLink supports both ways - the ?n (JPQL) and the plain ? (JDBC) (and a proprietary named style with hash prefix - "#param").

A JPA compliant example really looks like this:

Query query = em.createNativeQuery(
"SELECT LAST_NAME FROM EMPLOYEE WHERE FIRST_NAME = ?1");
query.setParameter(1, "Tim");

One big advantage of this ?n convention is that you may easily reuse the same parameter inside your native query - the JPA implementation takes care of duplicate parameter binding. E.g. select all employees with "M" as the first letter of first name and last name:

Query query = em.createNativeQuery(
"SELECT LAST_NAME FROM EMPLOYEE WHERE FIRST_NAME LIKE ?1 AND LAST_NAME LIKE ?1");
query.setParameter(1, "M%");

Adrian Goerler wrote:

This is a misundersanding. In 7.30, lazy loading of single-valued relationships is supported as an automatic mechanism. You do not need to programatically use getReference.

Great to read this. The misunderstanding is due to the help entry your link refers to. It ends with:

Result
The EntityManager.getReference method now returns lazily loadable instances.

Maybe the help information could be improved in this point since EntityManager.getReference is not the most popular way to fetch an entity instance.

Thanks an regards,

- Rolf

[rolf_paulsen]

Hi Thomas,

a bare ? is not correct for parameters. Positional parameters must be followed by an integer: ?1, ?2, ... .

EntityManager.createNativeQuery() should of course work. If it is nor the parameter issue, you may post your query.

Regards,

- Rolf

[Former Member]

Hello Rolf,

thank you for your quick response.

I first tried a native query with named parameters :foo, :bar, which are unsupported. I tried numbered parameters ?1, ?2 as used in JPQL queries, too, next, what had the persistence manager throw an exception about the query not being a select statement. The last thing i tried were the bare ? as used in prepared statements, with the same result, however. I'll post my query here soon.

There is a thread (unanswered) mentioning JPA 2 and another one mentioning 3rd party persistence frameworks. Is JPA 2 already available? Would it be possible to use Hibernate instead?

Regards

- Thomas

[adrian_goerler]

Hi,

you should be able to use any 3rd party persistence provider with SAP NetWeaver AS Java. However, there are two issues known:

- Hibernate requires the usage of "heavy class loaders" as decribed here: /people/community.user/blog/2007/05/21/using-hibernate-in-sap-netweaver-composition-environment (actually, you need to put only the antlr jar in the lib directory, I think). Also, please have a look at this article: http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/60642a88-95fe-2b10-d387-a245d48fc257.

- EclipseLink and Open JPA require byte code weaving. SAP NetWeaver AS Java does not support dynamic byte code weaving, you need to use static weaving instead. Please see this blog for using static weaving in NWDI: https://weblogs.sdn.sap.com/pub/wlg/20537. [original link is broken] [original link is broken] [original link is broken]

However, you need to be aware of the fact that using a 3rd party persistence provider that supports JPA 2 in Java SE does not automatically give you JPA 2.0 functionality in a Java EE 5 application server (such as SAP NetWeaver AS Java) as the javax persistence classes and interfaces come with the application server and hence ar JPA 1.0 only.

-Adrian