Gianni
|
| Posted: 08/28/2002, 8:27 AM |
|
Look at this custom query:
SELECT pltdaclassi.int_anno,
pltdaclassi.int_codscuola,
pltdaclassi.int_classe,
pltdaclassi.chr_tipo,
pltdaclassi.int_postitot,
pltdaclassi.int_postilib,
pltdaclassi.dat_datavar,
pltdaclassi.chr_stato,
pltdascuole.chr_descrizione,
pltdascuole.int_circolo
FROM pltdaclassi, pltdascuole
WHERE pltdascuole.int_codscuola = pltdaclassi.int_codscuola
AND pltdascuole.int_codscuola = ANY
( SELECT pltdacompetenze.int_codscuola
FROM pltdacompetenze
WHERE pltdacompetenze.int_userid = " + (String)session.getAttribute("UserID") + "
OR " + (String)session.getAttribute("UserRights") + " > 1 )
When I click OK on form properties, CC says "Invalid SQL query".
But if I upload the generated page and request it, it is correctly interpreted.
Infact the generated code is
sSQL = "SELECT pltdaclassi.int_anno, pltdaclassi.int_codscuola, pltdaclassi.int_classe, pltdaclassi.chr_tipo, pltdaclassi.int_postitot, pltdaclassi.int_postilib, pltdaclassi.dat_datavar, pltdaclassi.chr_stato, pltdascuole.chr_descrizione, pltdascuole.int_circolo FROM pltdaclassi, pltdascuole WHERE pltdascuole.int_codscuola = pltdaclassi.int_codscuola AND pltdascuole.int_codscuola = ANY ( SELECT pltdacompetenze.int_codscuola FROM pltdacompetenze WHERE pltdacompetenze.int_userid = " + (String)session.getAttribute("UserID") + " OR " + (String)session.getAttribute("UserRights") + " > 1 )";
which is sintactically exact.
Is this a CC+JSP bug? (2.0.5)
Thanks
Gianni
|
|
|
 |
WilliamB
|
| Posted: 08/28/2002, 9:17 AM |
|
CC can't understand your query because you are using JSP code within the query. If you enter a custom query for a form, CC will sent it to the database so as to determine the fields to make available in the design window of the form. AN of course, when the query is sent to the database, it cannot execute JSP code so the query is invalid.
If you need to specify input parameters, you can use the 'Input' tab of the form properties window to do so instead of trying to place when in the where clause of the query.
|
|
|
|
Gianni
|
| Posted: 08/28/2002, 11:58 PM |
|
Thank you for your answer, William.
Sorry but I don't agree with you because if I write the correspondent query using PHP code instead of JSP, CC doesn't return any error. Shouldn't CC act in the same way?
And I can't specify the input parameters I need in the 'input' tab because they are parameters to the subquery of the where predicate, as you can see reading through the SQL sentence.
I think that a good solution is the one described bu Nicole in thread "Custom SQL string problem": put the where predicate in the 'where' field of the SQL tab, even though it raises another problem. The 'where' field is too small to host my where clause.
Can someone at YesSoftware tell me if there are plans to widen the 'where' field?
Thank you
Gianni
|
|
|
|
Nicole
|
| Posted: 08/29/2002, 5:55 AM |
|
Gannie,
the another solution is to edit/replace sWhere variable (that stores "Where" clause) in the form Open event.
|
|
|
|
|
