This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.
Summary: | How to set object parameter in an IN clause with Visual SQL Editor ? | ||
---|---|---|---|
Product: | obsolete | Reporter: | arnaudm <arnaudm> |
Component: | visualweb | Assignee: | John Baker <jbaker> |
Status: | NEW --- | ||
Severity: | blocker | CC: | romanmostyka |
Priority: | P3 | ||
Version: | 6.x | ||
Hardware: | PC | ||
OS: | Linux | ||
Issue Type: | ENHANCEMENT | Exception Reporter: |
Description
arnaudm
2007-10-18 18:58:06 UTC
The Visual Query Editor does support the IN parameter. However, there is a design limitation in that the IN clause is missing the enclosing parenthesis. The workaround is to insert enclosing parenthesis around the expression. There could be multiple causes for the HTTP 500 error 1) The Oracle driver needs to be copied manually to the Tomcat server 2) Error in the query 3) The parameter needs to be set by the visual web application e.g. in your Page bean code (Page1.java) you need to set the parameter: Integer tripId = new Integer(128); getSessionBean1().getTripRowSet().setObject(1, tripId); Here is one valid query for the Travel database using Derby SELECT ALL TRAVEL.TRIP.TRIPID, TRAVEL.TRIP.PERSONID, TRAVEL.TRIP.DEPDATE, TRAVEL.TRIP.DEPCITY, TRAVEL.TRIP.DESTCITY, TRAVEL.TRIP.TRIPTYPEID, TRAVEL.TRIP.LASTUPDATED FROM TRAVEL.TRIP WHERE TRAVEL.TRIP.PERSONID IN ( ? ) To help solve this issue, please attach the server log from Tomcat and a zip file of the project, sample code or use case. There is a bug 94433 when the IN clause contains multiple items ('?','?') update, 94433 is not a bug. Here is a valid query SELECT ALL TRAVEL.TRIP.TRIPID, TRAVEL.TRIP.PERSONID, TRAVEL.TRIP.DEPDATE, TRAVEL.TRIP.DEPCITY, TRAVEL.TRIP.DESTCITY, TRAVEL.TRIP.TRIPTYPEID, TRAVEL.TRIP.LASTUPDATED FROM TRAVEL.TRIP WHERE TRAVEL.TRIP.PERSONID IN ( ?,? ) Hello. Thanks for your answer. I know it's possible to use (?, ?, ... , ?) while setting objects with corresponding index but I don't know how many values or ? I have to put in enumeration list before execution. So, the idea was to put values on the fly as one paramter in setObject() method. But, it seems we can't do it so I will have to make this kind of SQL query by hand. Inconvenient is this these queries are not in the same side of projects like other queries created in hidden sections by NetBeans Visual Query Editor (NVQE). After thinking, I wonder if it could be possible : - to configure only general query NVQE like "select * from table_name where general_criteria" ; - then, getting up query with getCommand() method ; - and finally, to complete it with IN (or other not full supported criteria) on the fly with a loop like we did it in the past when this geature didn't exist. But this reduces interest of this tool which, once time, permit to work queries places in the same side of the project in order not to have them all around in source code. Now, about HTTP 500. Tomcat is well configured standard queries work work very well. Even this : String SQL = "select id, label from table_name where id in (?)"; String IDs = myObject.getIDs(); // get all IDs for IN clause rs.setObject(1, IDs); Works fine too. But not if query created within NVQE. Strange ? When I try to put multi-valued parameter in setObject() method there is no log in Tomcat etc. All println statements can be shown in run log. There is no trouble in writing data in prerender code (no exception raised and all debug code in println statements is displayed correctly), so trouble seems to appear at data exploit time. This explains why I couldn't send any log data. Best regards. The Visual Query Editor supports a minimal subset of SQL. However if Query doesn't work in the Visual Query Editor then the query may still be valid. To test 'IN' with the Visual Query Editor, replace the '?' with an actual value. At least for Oracle, the PreparedStatement is throwing a java.sql.SQLException while executing a parameterized query Instead of using a PreparedStatement, maybe it's better to use a StatementFactory I'm not sure anything can be done, but at this point this is considered an enhancement. Sorry, won't be fixed in 6.5 either Maybe in 7.0 ! In my opinion, this is a lack for developers who want to avoid a lot of query calls in some cases using Visual Editor. Thanks for following this issue. Move visualsqleditor subcomponents below visualweb |