6 Replies Latest reply on Dec 4, 2003 9:32 AM by gavinandrews

very long SQL-Statement

th.krause Nov 17, 2003 6:41 AM

I log on level=DEBUG an see the following statement:
[Constant] Executing SQL: SELECT CST_ID,CST_GRPID, CST_TEXT FROM TBL_CONSTANT WHERE (CST_ID=?) OR (CST_ID=?) OR (CST_ID=?) OR OR (CST_ID=?) OR (CST_ID=?) OR (CST_ID=?) OR (CST_ID=?) OR (CST_ID=?) OR (CST_ID=?) OR (CST_ID=?) OR (CST_ID=?) OR (CST_ID=?) OR
D=?) OR (CST_ID=?) ... OR (CST_ID=?)
The application works correct but I think this statement is a very long version findAll(). The counts of (CST_ID=?) is equal the row of this DB-table. What`s wrong?

1. Re: very long SQL-Statement

joerk Nov 23, 2003 5:26 PM (in response to th.krause)

Hello,

have you found a solution for the problem? I have the same problem and can reproduce it with jboss 3.2.1 and 3.2.2.

I think it is part of a 1-many CMP. The statement is generated when I want to get all child entities of the relationship with something like

Collection schecks = getSchecks();
Iterator iterator = schecks.iterator();
ScheckLocal scheckLocal = null;
while (iterator.hasNext()) {
scheckLocal = (ScheckLocal)iterator.next();
// Status gesperrt wird nicht geaendert
if (!scheckLocal.isDisabled()) {
scheckLocal.setStatus(status);
}
}

The size of the Collection is the count of "OR" is the SQL-Statement. I get an excetion wenn the count of "OR" reach 500 .

com.sap.dbtech.jdbc.exceptions.DatabaseException: [-1104] (at 2048): Too complicated SQL statement (KB-stack overflow)

Any ideas?
Actions
2. Re: very long SQL-Statement

lybo Nov 24, 2003 2:27 AM (in response to th.krause)

Have you seen topics http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=thread.jsp?forum=46&thread=37484 and http://www.jboss.org/ ?
Actions
3. Re: very long SQL-Statement

sysuser1 Dec 2, 2003 8:37 PM (in response to th.krause)

yes, sth same. but no solution for it ?

I just want to know how to do in jboss ( my ver is 3.2.2) to avoid that? or there's no solution in jboss for it?

in fact, with finder, it works, but when getXXXData() exception throws.

I'm wondering another thing: what does Jboss do within "finder", and what's the result?

can getXXXData() get sth else besides PK directlly from the "finder result"?

in my idea, "finder" should have retrieved data from DB, and new the object in the system. getXXXData() only get the data from "finder result". need not retrieve data from DB again.

wondering..........
Actions
4. Re: very long SQL-Statement

gavinandrews Dec 3, 2003 2:55 PM (in response to th.krause)

You can restrict the number of rows returned in one go by the finder by declaring <page-size> in jbosscmp; this restricts the size of the generated SQL.

See the CMP documentation.
Actions
5. Re: very long SQL-Statement

sysuser1 Dec 3, 2003 8:19 PM (in response to th.krause)

where can I get the CMP document? thx
Actions
6. Re: very long SQL-Statement

gavinandrews Dec 4, 2003 9:32 AM (in response to th.krause)

http://jboss.org/index.html?module=html&op=userdisplay&id=docs/index#Base-30xx

It's in Chapter 11 of the JBossBook

my fix was in...
jbosscmp-jdbc.xml

... as follows...

<ejb-name>InstrumentPrice</ejb-name>

<load-groups>
<load-group>
<load-group-name>allInstrumentPrice</load-group-name>
<field-name>ISIN</field-name>
........................
<field-name>Deleted</field-name>
</load-group>
</load-groups>

<query-method>
<method-name>findAll</method-name>
<method-params/>
</query-method>
<read-ahead>
on-find
<page-size>128</page-size>
<eager-load-group>allInstrumentPrice</eager-load-group>
</read-ahead>
Actions

Go to original post