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.
Hello. First, I want to thank you for all the work accomplished for the better tool never done. I use it since release 3 and before when name was "Forte 4 Java", you know, isn't it ? I never had any fatal trouble !!! Today, I just want to know if it's possible to manage SQL queries with Visual SQL Editor integrated in NetBeans 5.5.1. Explaination : I want to use SQL query like that "select id, label from table_name where id in (1, 12, 5)" for example on an Oracle Database. This query is very simple and works very well, even in NetBeans programs ... But, when I configure it in Visual SQL Editor, it fails when I deploy to Tomcat 5.5 (integrated or not). When I add criteria : (see summary code bellow) - if I type values : result is OK ; - if I choose '?', result is "HTTP 500" in my browser. sammple : (code placed in prerender method) rowSet.setCommand("select id, label from table_name where id in (1, 2, 3)"); --> ok (Basic SQL) rowSet.setObject(1, "(1)"); --> ok (with SQL either coded in hard or SQL Editor) rowSet.setObject(1, "(1, 2, 3)"); --> ok (with SQL coded in hard or values typed in SQL Editor (by Value)) --> NOT OK if parameted in SQL Editor !!! (in realty, I want data to be generated by code, so it's only a simple sample for test) => tested with String, String [], Vector, List, ... for setObject()'s second parameter // Refreh code ... There is no error in logs and all my "println" works, even aftre refresh() So, I'm sure I'm doing something bad. How to resolve my issue via this tool (great tool which prevents everybody from having queries all over the source code in order to have better code management) ? Many thanks for all. Best regards. ----------------------------------- Arnaud MILLEREUX
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