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.

Bug 119400 - How to set object parameter in an IN clause with Visual SQL Editor ?
Summary: How to set object parameter in an IN clause with Visual SQL Editor ?
Status: NEW
Alias: None
Product: obsolete
Classification: Unclassified
Component: visualweb (show other bugs)
Version: 6.x
Hardware: PC Linux
: P3 blocker (vote)
Assignee: John Baker
Depends on:
Reported: 2007-10-18 18:58 UTC by arnaudm
Modified: 2008-11-18 13:19 UTC (History)
1 user (show)

See Also:
Exception Reporter:


Note You need to log in before you can comment on or make changes to this bug.
Description arnaudm 2007-10-18 18:58:06 UTC

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.

Comment 1 John Baker 2007-10-18 21:56:14 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 ( 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

To help solve this issue, please attach the server log from Tomcat and a zip file of the project, sample code or use case.

Comment 2 John Baker 2007-10-18 22:08:06 UTC
There is a bug 94433 when the IN clause contains multiple items ('?','?')
Comment 3 John Baker 2007-10-18 22:10:40 UTC
update, 94433 is not a bug.

Here is a valid query 

Comment 4 arnaudm 2007-10-20 11:42:49 UTC

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.
Comment 5 John Baker 2007-11-09 18:45:24 UTC
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.
Comment 6 John Baker 2007-11-09 20:32:56 UTC
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
Comment 7 John Baker 2007-11-09 21:43:54 UTC
I'm not sure anything can be done, but at this point this is considered an enhancement.
Comment 8 John Baker 2008-10-08 04:30:20 UTC
Sorry, won't be fixed in 6.5 either
Comment 9 arnaudm 2008-10-09 10:32:03 UTC
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.
Comment 10 Petr Blaha 2008-11-18 13:19:48 UTC
Move visualsqleditor subcomponents below visualweb