File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JSF and the fly likes ResultSet multiple line query exception Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » JSF
Bookmark "ResultSet multiple line query exception" Watch "ResultSet multiple line query exception" New topic
Author

ResultSet multiple line query exception

Shilpa Tendulkar
Ranch Hand

Joined: Jul 29, 2001
Posts: 75
Hi All,

I get this weard exception when I try to set a query in mulitple line in setCommand() mehod of resulset. However if I set that query in one line, it works well.
My code is:
alternatePropertiesLRS.setCommand("SELECT property.property_name,dates.property_id,dates.property_date,SUM(dates.bookable) AS bookable," +
"SUM(dates.booked) AS booked,DATEDIFF('2007-08-25','2007-08-18') AS nights,dates.property_code " +
"FROM property_dates dates " +
"LEFT OUTER JOIN property ON property.id = dates.property_id " +
"WHERE property.supplier_id=1 AND dates.property_date <='2007-08-24' AND " +
"dates.property_date >='2007-08-18' AND (dates.property_date = '2007-08-18' AND dates.bookable <> 0) " +
"GROUP BY dates.property_id " +
"HAVING SUM(dates.bookable) <= DATEDIFF('2007-08-25','2007-08-18') AND SUM(dates.booked) = 0 " +
"ORDER BY dates.property_id");
------------------------------------------------------
MY STACK TRACE:

javax.faces.FacesException: javax.el.ELException: javax.faces.el.EvaluationException: javax.el.ELException: javax.faces.el.PropertyNotFoundException: java.lang.RuntimeException: java.sql.SQLException: SQL String can not be NULL
at javax.faces.component.UIOutput.getValue(UIOutput.java:176)
at com.sun.rave.web.ui.component.RbCbSelectorBase.getSelected(RbCbSelectorBase.java:154)
at com.sun.rave.web.ui.renderer.RbCbRendererBase.renderInput(RbCbRendererBase.java:406)
at com.sun.rave.web.ui.renderer.RbCbRendererBase.renderSelection(RbCbRendererBase.java:349)
at com.sun.rave.web.ui.renderer.CheckboxRenderer.renderEnd(CheckboxRenderer.java:329)
at com.sun.rave.web.ui.renderer.AbstractRenderer.encodeEnd(AbstractRenderer.java:235)
at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:848)
at com.sun.rave.web.ui.util.RenderingUtilities.renderComponent(RenderingUtilities.java:88)
at com.sun.rave.web.ui.renderer.TableColumnRenderer.encodeChildren(TableColumnRenderer.java:173)
at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:828)
at com.sun.rave.web.ui.util.RenderingUtilities.renderComponent(RenderingUtilities.java:80)
at com.sun.rave.web.ui.renderer.TableRowGroupRenderer.encodeChildren(TableRowGroupRenderer.java:185)
at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:828)
at com.sun.rave.web.ui.util.RenderingUtilities.renderComponent(RenderingUtilities.java:80)
at com.sun.rave.web.ui.renderer.TableRenderer.encodeChildren(TableRenderer.java:157)
at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:828)
at com.sun.rave.insync.faces.FacesPageUnit.renderBean(FacesPageUnit.java:1266)
at com.sun.rave.insync.faces.FacesPageUnit.renderNode(FacesPageUnit.java:1485)
at com.sun.rave.insync.faces.FacesPageUnit.renderBean(FacesPageUnit.java:1299)
at com.sun.rave.insync.faces.FacesPageUnit.renderNode(FacesPageUnit.java:1485)
at com.sun.rave.insync.faces.FacesPageUnit.renderBean(FacesPageUnit.java:1299)
at com.sun.rave.insync.faces.FacesPageUnit.renderNode(FacesPageUnit.java:1485)
at com.sun.rave.insync.faces.FacesPageUnit.renderBean(FacesPageUnit.java:1299)
at com.sun.rave.insync.faces.FacesPageUnit.renderNode(FacesPageUnit.java:1485)
at com.sun.rave.insync.faces.FacesPageUnit.renderBean(FacesPageUnit.java:1299)
at com.sun.rave.insync.faces.FacesPageUnit.getFacesRenderTree(FacesPageUnit.java:1069)
at com.sun.rave.insync.faces.FacesPageUnit.renderHtml(FacesPageUnit.java:1607)
at com.sun.rave.insync.models.FacesModel.getHtmlDomFragment(FacesModel.java:1554)
at com.sun.rave.insync.models.FacesModel.findHtmlBody(FacesModel.java:1578)
at com.sun.rave.insync.models.FacesModel.getHtmlBody(FacesModel.java:1566)
at com.sun.rave.designer.jsf.JsfForm.getHtmlBody(JsfForm.java:434)
at com.sun.rave.designer.WebForm.getHtmlBody(WebForm.java:863)
at com.sun.rave.designer.WebForm.getHtmlBody(WebForm.java:849)
at com.sun.rave.designer.DesignerPaneUI.resetPageBox(DesignerPaneUI.java:582)
at com.sun.rave.designer.WebForm.modelRefreshed(WebForm.java:1484)
at com.sun.rave.designer.WebForm.access$200(WebForm.java:85)
at com.sun.rave.designer.WebForm$HtmlDomProviderListener.modelRefreshed(WebForm.java:1804)
at com.sun.rave.designer.jsf.JsfForm.fireModelRefreshed(JsfForm.java:697)
at com.sun.rave.designer.jsf.JsfForm.refreshModel(JsfForm.java:555)
at com.sun.rave.designer.WebForm.refreshModel(WebForm.java:1444)
at com.sun.rave.designer.action.RefreshAction.refresh(RefreshAction.java:71)
at com.sun.rave.designer.action.RefreshAction.performAction(RefreshAction.java:65)
at com.sun.rave.spi.designtime.idebridge.action.AbstractDesignBeanAction$DelegateAction.actionPerformed(AbstractDesignBeanAction.java:158)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
at javax.swing.AbstractButton.doClick(AbstractButton.java:302)
at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:1000)
at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:1041)
at java.awt.Component.processMouseEvent(Component.java:5501)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3135)
at java.awt.Component.processEvent(Component.java:5266)
at java.awt.Container.processEvent(Container.java:1966)
at java.awt.Component.dispatchEventImpl(Component.java:3968)
at java.awt.Container.dispatchEventImpl(Container.java:2024)
at java.awt.Component.dispatchEvent(Component.java:3803)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
at java.awt.Container.dispatchEventImpl(Container.java:2010)
at java.awt.Window.dispatchEventImpl(Window.java:1778)
at java.awt.Component.dispatchEvent(Component.java:3803)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)
Caused by: javax.el.ELException: javax.faces.el.EvaluationException: javax.el.ELException: javax.faces.el.PropertyNotFoundException: java.lang.RuntimeException: java.sql.SQLException: SQL String can not be NULL
at javax.faces.component.ValueExpressionValueBindingAdapter.getValue(ValueExpressionValueBindingAdapter.java:76)
at javax.faces.component.UIOutput.getValue(UIOutput.java:173)
... 68 more
Caused by: javax.faces.el.EvaluationException: javax.el.ELException: javax.faces.el.PropertyNotFoundException: java.lang.RuntimeException: java.sql.SQLException: SQL String can not be NULL
at com.sun.faces.application.ValueBindingValueExpressionAdapter.getValue(ValueBindingValueExpressionAdapter.java:106)
at javax.faces.component.ValueExpressionValueBindingAdapter.getValue(ValueExpressionValueBindingAdapter.java:73)
... 69 more
Caused by: javax.el.ELException: javax.faces.el.PropertyNotFoundException: java.lang.RuntimeException: java.sql.SQLException: SQL String can not be NULL
at com.sun.faces.el.PropertyResolverChainWrapper.getValue(PropertyResolverChainWrapper.java:78)
at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:143)
at com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:62)
at com.sun.el.parser.AstValue.getValue(AstValue.java:117)
at com.sun.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:192)
at com.sun.faces.application.ValueBindingValueExpressionAdapter.getValue(ValueBindingValueExpressionAdapter.java:102)
... 70 more
Caused by: javax.faces.el.PropertyNotFoundException: java.lang.RuntimeException: java.sql.SQLException: SQL String can not be NULL
at com.sun.rave.web.ui.faces.DataProviderPropertyResolver$ValueData.getValue(DataProviderPropertyResolver.java:341)
at com.sun.rave.web.ui.faces.DataProviderPropertyResolver.getValue(DataProviderPropertyResolver.java:143)
at com.sun.faces.el.PropertyResolverChainWrapper.getValue(PropertyResolverChainWrapper.java:75)
... 75 more
Caused by: java.lang.RuntimeException: java.sql.SQLException: SQL String can not be NULL
at com.sun.data.provider.impl.CachedRowSetDataProvider.getMetaData(CachedRowSetDataProvider.java:1374)
at com.sun.data.provider.impl.CachedRowSetDataProvider.getFieldKeys(CachedRowSetDataProvider.java:494)
at com.sun.data.provider.impl.CachedRowSetDataProvider.getFieldKeyInternal(CachedRowSetDataProvider.java:472)
at com.sun.data.provider.impl.CachedRowSetDataProvider.getFieldKey(CachedRowSetDataProvider.java:444)
at com.sun.data.provider.impl.TableRowDataProvider.getFieldKey(TableRowDataProvider.java:113)
at com.sun.rave.web.ui.faces.DataProviderPropertyResolver$ValueData.getValue(DataProviderPropertyResolver.java:327)
... 77 more
Caused by: java.sql.SQLException: SQL String can not be NULL
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.PreparedStatement.<init>(PreparedStatement.java:438)
at com.mysql.jdbc.Connection.clientPrepareStatement(Connection.java:2151)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4661)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4573)
at com.sun.rave.sql.DesignTimeConnection.prepareStatement(DesignTimeConnection.java:187)
at com.sun.sql.rowset.CachedRowSetXImpl.getMetaData(CachedRowSetXImpl.java:2359)
at com.sun.data.provider.impl.CachedRowSetDataProvider.getMetaData(CachedRowSetDataProvider.java:1372)
... 82 more


Can anyone help on this please?
Thank you.


SCJP5
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

Looks like this
"Caused by: javax.faces.el.EvaluationException: javax.el.ELException: javax.faces.el.PropertyNotFoundException:"


So I think in your backing bean you do not have a getter/setter for the attribute that will hold the SQL String value. Since it is not there JSF cannot set that value of that attribute, so it is getting set to null, and you cannot pass null to the setCommand method.

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Shilpa Tendulkar
Ranch Hand

Joined: Jul 29, 2001
Posts: 75
Thanks Mark for your reply.
Problem here is , when I set a query string in setCommand() like
rs.setCommand("select * from table"), it doesnt give me any exception,
however because my query is long I set it like

rs.setCommand("select field1,field2,field3,field4, "+
field5,field6,field7,field8 from table"); and this time I get the exception i posted before.

If I set this long query on one line, it works fine...
rs.setCommand("select field1,field2,field3,field4,field5,field6,field7,field8 from table");

Please note the way long query has written.

Please help me out here...
jgood man
Greenhorn

Joined: Jul 25, 2007
Posts: 1
Did you ever get a response to this. The same thing is happening to me. Very annoying. And I didnt quite understand what he was saying about needing a getter/setter for the sql attribute.
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16012
    
  19

Where are you setting your query string? Is it actually coming from the JSF page? I hope not (SQL Injection Attacks).


Customer surveys are for companies who didn't pay proper attention to begin with.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ResultSet multiple line query exception