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: | SQL does not work on the visual table component, why? is there a solution? | ||
---|---|---|---|
Product: | obsolete | Reporter: | vitorpavanelli <vitorpavanelli> |
Component: | visualweb | Assignee: | John Baker <jbaker> |
Status: | NEW --- | ||
Severity: | blocker | ||
Priority: | P3 | ||
Version: | 6.x | ||
Hardware: | PC | ||
OS: | Windows XP | ||
Issue Type: | ENHANCEMENT | Exception Reporter: |
Description
vitorpavanelli
2008-09-22 12:57:28 UTC
John, can you evaluate this please ? thanks Hi, thank you for the help!! I will be more clear and specific... I am using Netbeans 6.1 and I am developing a Web Application with Visual JSF. This visual JSF works with the woodstock componentes and it has been working nice so far. I had to make the QUERY above and CONCATENATE fields like here MAX(CONTACT_PERSON.F_NAME || ' ' || CONTACT_PERSON.L_NAME) AS CONTACT_NAME, but somehow, when I execute the query on the DataSet, it says there is a Lexical problem with the "||" and it will not allow me to display the fields and data from on the visual table. Even SUBQUERIES don't work because of the "(".... like here SELECT AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, MAX(CONTACT_PERSON.F_NAME || ' ' || CONTACT_PERSON.L_NAME) AS CONTACT_NAME, AGENCY.EMAIL, (SELECT COUNT(LDS.ID) FROM LEADS LDS WHERE LDS.AGENCY_ID = AGENCY.ID) AS LDSPROVIDED, (SELECT COUNT(LDS2.ID) FROM LEADS LDS2 WHERE LDS2.AGENCY_ID = AGENCY.ID AND LDS2.CUSTOMER = 1) AS LDSCONVERTED, (SELECT COUNT(AG.ID) FROM AGENCY AG) AS TOTALAGENCY I have no idea how to solve this. Thank you very much for the help, Vitor Which database and driver versions are you using? To make sure the SQL is correct, you can test the query from the Services window by selecting a connection, right-click then choose Execute Query. In the SQL Editor, paste the SQL then execute. The sql is working perfectly fine. I only get the problem when I go to the visual query on Netbeans. I am using Oracle XE and the driver version is the latest one. The Visual Query Editor only interprets a subset of SQL. It's parser is very limited, unfortunately. We hope to improve this in a future release. A better editor to use to verify your SQL is the NetBeans SQL Editor as explained in my previous comment. Even though the Visual Query Editor fails to execute the query, the application should still work. Here's how you can make sure your application works: 1) Test your query using the SQL Editor. If you have a parameterized query, though, you'll need to insert some test data for the parameter 2) In your Visual Web project, open the Design view of your page, then use the Navigator window. Expand the SessionBean node where your rowset is created (by default) then select the Rowset. View the Properties window and for the Statement property, you can edit the SQL. 3) Also, in the rowset Properties window, enable the printStatements property. By enabling this property, the SQL statement executed by the application at runtime will be printed in the Application Server log file. That way you can be sure the SQL you expect is executed Hi Jbaker, Everything you said works perfectly, but the problem is... I can not bind this rowSet on the table.. it does not allow me to do it. It is all because this Lexical problem. is there anything that I can for this binding? I can send print screens in jpg format so you can see. But the problem is, I can not bind the rowSet on my table. I always click with the right button on the table, then table layout.... and there I choose the dataSource.. but it does not allow me to do it. And it only happend now. All my pages I have done this way, they work fine. the problem is only with this kind of select that uses concatenation and sub queries. thank you very much for your attention, Vitor Check the SQL written to SessionBean1.java 's _init() method. You may need to escape each character of [ || ' ' || ] in the SQL. you mean doing like this.......... "SELECT AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, MAX(CONTACT_PERSON.F_NAME [|| \' \' ||] CONTACT_PERSON.L_NAME)..." ? I think like this: SELECT AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, MAX(CONTACT_PERSON.F_NAME \|\| \'\ ' \|\| CONTACT_PERSON.L_NAME) ... If this doesn't work then I'll try to create a similar query. Any chance you can send me the SQL to create the tables ? yeah! it doesn't allow me to do what you said.. so here you have the sql and the tables, JUST RUN THE SCRIPT SELECT AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, MAX(CONTACT_PERSON.F_NAME || ' ' || CONTACT_PERSON.L_NAME) AS CONTACT_NAME, AGENCY.EMAIL, (SELECT COUNT(LDS.ID) FROM LEADS LDS WHERE LDS.AGENCY_ID = AGENCY.ID) AS LDSPROVIDED, (SELECT COUNT(LDS2.ID) FROM LEADS LDS2 WHERE LDS2.AGENCY_ID = AGENCY.ID AND LDS2.CUSTOMER = 1) AS LDSCONVERTED, (SELECT COUNT(AG.ID) FROM AGENCY AG) AS TOTALAGENCY, '2' AS SELECTEDAGENCY FROM AGENCY, CONTACT_PERSON WHERE AGENCY.ID = CONTACT_PERSON.AGENCY_ID AND CONTACT_PERSON.ID = (SELECT MIN(CP2.ID) FROM CONTACT_PERSON CP2 WHERE CP2.AGENCY_ID = AGENCY.ID) GROUP BY AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, AGENCY.EMAIL, AGENCY.CREATED_DATE, AGENCY.ID ORDER BY AGENCY.CREATED_DATE COPY/PASTE EVERYTHING AND RUN CREATE TABLE AGENCY ( ID NUMBER NOT NULL, AGENCY VARCHAR2(40), C_NAME VARCHAR2(40), EMAIL VARCHAR2(50), ADDRESS VARCHAR2(50), CITY VARCHAR2(30), COUNTRY VARCHAR2(20), POSTAL_CODE VARCHAR2(12), PHONE VARCHAR2(12), FAX VARCHAR2(12), COMMENTS VARCHAR2(500), ACTIVE NUMBER, COMISSION_AGREED NUMBER, BANK VARCHAR2(40), BANK_ACCOUNT NUMBER, BANK_CODE NUMBER, IBAN VARCHAR2(30), BIC VARCHAR2(30), WEBSITE VARCHAR2(30), CREATED_DATE DATE ) ; CREATE TABLE AGENCYCONTACTLOG ( ID NUMBER NOT NULL, LOG_DATE DATE, CONTACTED VARCHAR2(15), DETAILS VARCHAR2(500), AGENCY_ID NUMBER NOT NULL ) ; CREATE TABLE APARTMENT ( ID NUMBER NOT NULL, NAME VARCHAR2(20), HTYPE VARCHAR2(20), ZONE NUMBER, FLOOR VARCHAR2(20), SOLD NUMBER, APTPRICE_ID NUMBER, SEAVIEW VARCHAR2(20), COMMENTS VARCHAR2(400) ) ; CREATE TABLE APPSETTINGS ( ID NUMBER NOT NULL, PRICE_AP_NFA NUMBER, PRICE_AP_TER NUMBER, PRICE_VI_NFA NUMBER, PRICE_VI_LAND NUMBER, PRICE_VI_TER NUMBER, PRICE_VI_POOL NUMBER, COMMISSION NUMBER, DISC_OSV NUMBER, DISC_LSV NUMBER, DISC_NRG NUMBER, TAX_LAND NUMBER, TAX_PROP NUMBER, DISC_NOU NUMBER, PAY_RES NUMBER, PAY_PURCHAG_PERC NUMBER, PAY_PRELPURCHAG_PERC NUMBER, PAY_RESAG_PERC NUMBER ) ; CREATE TABLE APTPRICE ( ID NUMBER NOT NULL, ATYPE VARCHAR2(20), SQM_NFA NUMBER(5, 2), SQM_TERRANCE NUMBER(5, 2) ) ; CREATE TABLE CONTACT_PERSON ( ID NUMBER NOT NULL, F_NAME VARCHAR2(40), L_NAME VARCHAR2(40), TEL VARCHAR2(20), FAX VARCHAR2(20), EMAIL VARCHAR2(40), AGENCY_ID NUMBER ) ; CREATE TABLE LEADS ( ID NUMBER NOT NULL, FIRST_NAME VARCHAR2(40), LAST_NAME VARCHAR2(40), ADDRESS VARCHAR2(50), CITY VARCHAR2(30), POSTAL_CODE VARCHAR2(10), EMAIL VARCHAR2(40), PHONE VARCHAR2(15), FAX VARCHAR2(15), COUNTRY VARCHAR2(15), QUALITY VARCHAR2(10), STATUS VARCHAR2(15), DATE_CREATED DATE, LEADSOURCE VARCHAR2(20), ACTIVE NUMBER, AGENCY_ID NUMBER, CUSTOMER NUMBER, LONG_STOP_DATE DATE ) ; CREATE TABLE LEADSCONTACTLOG ( ID NUMBER NOT NULL, LOG_DATE DATE, DETAILS VARCHAR2(500), CONTACTED VARCHAR2(15), LEADS_ID NUMBER ) ; CREATE TABLE PAYMENT ( UNITSOLD_ID NUMBER NOT NULL, RESERVATION NUMBER, RESERV_DATE DATE, RESERV_AGREED NUMBER, RESERV_AGREED_DATE DATE, PRELIM_PURCHASE NUMBER, PRELIM_PURCHASE_DATE DATE, PURCHASE_AGREED NUMBER, PURCHASE_AGREED_DATE DATE ) ; CREATE TABLE ROLE ( ID NUMBER(*, 0) NOT NULL, TYPE VARCHAR2(20), DESCRIPTION VARCHAR2(300) NOT NULL ) ; CREATE TABLE UNITSOLD ( ID NUMBER NOT NULL, PAY_PURCHAG NUMBER, PAY_PRELPURCHAG NUMBER, PAY_RESAG NUMBER, PAY_RES NUMBER, COMMISSION NUMBER, TAX_PROP NUMBER, TAX_LAND NUMBER, PRICE_GROSS NUMBER, PRICE_NET NUMBER, AGENCY_ID NUMBER, UNITY_TYPE VARCHAR2(2), VILLA_ID NUMBER, APARTMENT_ID NUMBER, LEADS_ID NUMBER, PAY_PURCHAG_PERC NUMBER, PAY_PRELPURCHAG_PERC NUMBER, PAY_RESAG_PERC NUMBER, CONTRACT VARCHAR2(20), PURCHASE_DATE DATE, DISCOUNT NUMBER ) ; CREATE TABLE USERPROJECT ( ID NUMBER(*, 0) NOT NULL, NAME VARCHAR2(20) NOT NULL, PASSWORD VARCHAR2(20), EMAIL VARCHAR2(30) NOT NULL, ROLE_ID NUMBER(*, 0) ) ; CREATE TABLE VILLA ( ID NUMBER NOT NULL, NAME VARCHAR2(10), ZONE NUMBER(*, 0), SQM_LAND NUMBER(8, 2), SOLD NUMBER(*, 0), VTPRICE_ID NUMBER, SEAVIEW VARCHAR2(10), COMMENTS VARCHAR2(400) ) ; CREATE TABLE VTPRICE ( ID NUMBER NOT NULL, TYPE VARCHAR2(20), SQM_ANGF NUMBER(6, 2), SQM_VTER NUMBER(6, 2), SQM_POOL NUMBER(5, 2) ) ; ALTER TABLE AGENCY ADD CONSTRAINT AGENCY_PK PRIMARY KEY ( ID ) ENABLE ; ALTER TABLE AGENCYCONTACTLOG ADD CONSTRAINT AGENCYCONTACTLOG_PK PRIMARY KEY ( ID ) ENABLE ; ALTER TABLE APARTMENT ADD CONSTRAINT APARTMENT_PK PRIMARY KEY ( ID ) ENABLE ; ALTER TABLE APPSETTINGS ADD CONSTRAINT APPSETTINGS_PK PRIMARY KEY ( ID ) ENABLE ; ALTER TABLE APTPRICE ADD CONSTRAINT APTPRICE_PK PRIMARY KEY ( ID ) ENABLE ; ALTER TABLE CONTACT_PERSON ADD CONSTRAINT TABLE1_PK PRIMARY KEY ( ID ) ENABLE ; ALTER TABLE LEADS ADD CONSTRAINT LEADS_PK PRIMARY KEY ( ID ) ENABLE ; ALTER TABLE LEADSCONTACTLOG ADD CONSTRAINT LEADSCONTACTLOG_PK PRIMARY KEY ( ID ) ENABLE ; ALTER TABLE PAYMENT ADD CONSTRAINT PAYMENT_PK PRIMARY KEY ( UNITSOLD_ID ) ENABLE ; ALTER TABLE ROLE ADD CONSTRAINT ROLE_PK PRIMARY KEY ( ID ) ENABLE ; ALTER TABLE UNITSOLD ADD CONSTRAINT UNITSOLD_PK PRIMARY KEY ( ID ) ENABLE ; ALTER TABLE USERPROJECT ADD CONSTRAINT USERPROJECT_PK PRIMARY KEY ( ID ) ENABLE ; ALTER TABLE VILLA ADD CONSTRAINT VILLA_PK PRIMARY KEY ( ID ) ENABLE ; ALTER TABLE VTPRICE ADD CONSTRAINT VTPRICE_PK PRIMARY KEY ( ID ) ENABLE ; ALTER TABLE AGENCYCONTACTLOG ADD CONSTRAINT AGENCYCONTACTLOG_AGENCY_FK FOREIGN KEY ( AGENCY_ID ) REFERENCES AGENCY ( ID ) ENABLE ; ALTER TABLE APARTMENT ADD CONSTRAINT APARTMENT_APTPRICE_FK FOREIGN KEY ( APTPRICE_ID ) REFERENCES APTPRICE ( ID ) ENABLE ; ALTER TABLE CONTACT_PERSON ADD CONSTRAINT CONTACT_PERSON_AGENCY_FK FOREIGN KEY ( AGENCY_ID ) REFERENCES AGENCY ( ID ) ENABLE ; ALTER TABLE LEADS ADD CONSTRAINT LEADS_AGENCY_FK FOREIGN KEY ( AGENCY_ID ) REFERENCES AGENCY ( ID ) ENABLE ; ALTER TABLE LEADSCONTACTLOG ADD CONSTRAINT LEADSCONTACTLOG_LEADS_FK FOREIGN KEY ( LEADS_ID ) REFERENCES LEADS ( ID ) ENABLE ; ALTER TABLE PAYMENT ADD CONSTRAINT PAYMENT_UNITSOLD_FK FOREIGN KEY ( UNITSOLD_ID ) REFERENCES UNITSOLD ( ID ) ENABLE ; ALTER TABLE UNITSOLD ADD CONSTRAINT UNITSOLD_APARTMENT_FK FOREIGN KEY ( APARTMENT_ID ) REFERENCES APARTMENT ( ID ) ENABLE ; ALTER TABLE UNITSOLD ADD CONSTRAINT UNITSOLD_LEADS_FK FOREIGN KEY ( LEADS_ID ) REFERENCES LEADS ( ID ) ENABLE ; ALTER TABLE UNITSOLD ADD CONSTRAINT UNITSOLD_VILLA_FK FOREIGN KEY ( VILLA_ID ) REFERENCES VILLA ( ID ) ENABLE ; ALTER TABLE UNITSOLD ADD CONSTRAINT UNITSOLD_AGENCY_FK FOREIGN KEY ( AGENCY_ID ) REFERENCES AGENCY ( ID ) ENABLE ; ALTER TABLE USERPROJECT ADD CONSTRAINT USERPROJECT_ROLE_FK FOREIGN KEY ( ROLE_ID ) REFERENCES ROLE ( ID ) ENABLE ; ALTER TABLE VILLA ADD CONSTRAINT VILLA_VTPRICE_FK FOREIGN KEY ( VTPRICE_ID ) REFERENCES VTPRICE ( ID ) ENABLE ; CREATE INDEX AGENCY_INDEX_AG ON AGENCY (AGENCY ASC); CREATE INDEX AGENCY_INDEX_CFN ON AGENCY (C_NAME ASC); CREATE INDEX LEADS_INDEX_FNAME ON LEADS (FIRST_NAME ASC); CREATE INDEX LEADS_INDEX_LNAME ON LEADS (LAST_NAME ASC); Looks like a bug in CachedRowSetXImpl5 oracleFixup : oracleFixup modifies the query so metadata can be retrieved. Error is ORA-00921: unexpected end of SQL command The statement with the problem gets truncated to SELECT AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, MAX(CONTACT_PERSON.F_NAME || ' ' || CONTACT_PERSON.L_NAME) AS CONTACT_NAME, AGENCY.EMAIL, (SELECT COUNT(LDS.ID) FROM LEADS LDS WHERE 1=0 GROUP BY AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, AGENCY.EMAIL, AGENCY.CREATED_DATE, AGENCY.ID ORDER BY AGENCY.CREATED_DATE which is an incomplete SQL statement. oracleFixup is a hack to get applications that use the Oracle 9 driver to work. However, there is no check for the Oracle version, only if the database is Oracle The underlying problem is with the Oracle driver. Oracle cannot retrieve the metadata for this SQL java.lang.RuntimeException: java.sql.SQLException: statement handle not executed: getMetaData Oracle error code is 17144 The standard SQL Editor seems to be able to obtain the metadata, so I'll need to find out how it retrieves it I see that DBMetaDataFactory in db.dataview has some workarounds for Oracle. This looks like a risky fix. I think the best bet is for the user to simplify the query. Could you use views to simplify this ? Hi John, I will make the view, but if I change the database to MySQl, will it work fine? the same sql? thank you, Vitor you mean that Oracle modifies this query that I made SELECT AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, MAX(CONTACT_PERSON.F_NAME || ' ' || CONTACT_PERSON.L_NAME) AS CONTACT_NAME, AGENCY.EMAIL, (SELECT COUNT(LDS.ID) FROM LEADS LDS WHERE LDS.AGENCY_ID = AGENCY.ID) AS LDSPROVIDED to this..... /**The statement with the problem gets truncated to **/ SELECT AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, MAX(CONTACT_PERSON.F_NAME || ' ' || CONTACT_PERSON.L_NAME) AS CONTACT_NAME, AGENCY.EMAIL,/** ( SELECT COUNT(LDS.ID ) **/FROM LEADS LDS WHERE 1=0 GROUP BY AGENCY.AGENCY, AGENCY.COUNTRY, AGENCY.CITY, AGENCY.EMAIL, AGENCY.CREATED_DATE, AGENCY.ID ORDER BY AGENCY.CREATED_DATE which is an incomplete SQL statement. or you created this sql? MySQL should work - SQL may be slightly different though. As a result of the Oracle JDBC limitation, NetBeans - Visual Web modifies the query. It's too risky to fix Visual Web to compensate for the Oracle bug. Any change could affect other users' applications that use Oracle. Reporter can use MySQL as a workaround. Well! thank you very much for your attention John! you were quite helpful!!! I made a view to compensate this lack. do you know if there is any plans to change the visual faces-config.xml to make the arrows free to be moved?? have a nice day, Vitor your welcome. No, I don't think arrows can be moved or changed and I don't know of any plans to enhance the faces-config editor This is really an Oracle bug that is worked-around by Visual Web. The workaround just doesn't work for very complicated queries that involve subqueries with group-by I see. But, if I try to use MySQl, will it work with concatenation, sub queries and so on? Maybe these will help: http://www.mysqltutorial.org/mysql-join-subqueries.aspx http://www.dovka.org/blog/category/mysql http://help.godaddy.com/article/2384 http://placementpapers.net/helpingroot/Paper/MySQL-Query-Interview-Question-Part-7 Thank you for the links, but I meant if the queries will work with Netbeans, visual JSF and MySQL, like I am doing with Oracle. But it is okay. I will find out later. thank you very much for you help John, Vitor |