• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

ResultSet multiple line query exception

 
Shilpa Tendulkar
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Shilpa Tendulkar
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 18212
53
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Where are you setting your query string? Is it actually coming from the JSF page? I hope not (SQL Injection Attacks).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic