title: java.sql.SQLSyntaxErrorException: ORA-01745: invalid host/bind variable name in managed bean
body: <p>I have this managed bean based on this <a href="http://balusc.blogspot.com/2008/10/effective-datatable-paging-and-sorting.html" rel="nofollow">tutorial</a>.</p>
  
  <pre><code>import java.io.Serializable;
  import javax.enterprise.context.SessionScoped;
  // or import javax.faces.bean.SessionScoped;
  import javax.inject.Named;
  /* include SQL Packages */
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.util.ArrayList;
  import java.util.Date;
  import java.util.HashMap;
  import java.util.List;
  import java.util.Map;
  import javax.annotation.PostConstruct;
  import javax.sql.DataSource;
  import javax.annotation.Resource;
  import javax.faces.component.UICommand;
  import javax.faces.context.FacesContext;
  import javax.inject.Inject;
  import javax.servlet.http.HttpServletRequest;
  import javax.servlet.http.HttpSession;
  // or import javax.faces.bean.ManagedBean; 
  import javax.faces.event.ActionEvent;
  
  import org.glassfish.osgicdi.OSGiService;
  
  // source http://balusc.blogspot.com/2008/10/effective-datatable-paging-and-sorting.html
  
  @Named("SessionsController")
  @SessionScoped
  public class Sessions implements Serializable {
  
      /* Call the Oracle JDBC Connection driver */
      @Resource(name = "jdbc/Oracle")
      private DataSource ds;
      // Data.
      private List&lt;ActiveSessionObj&gt; dataList;
      private int totalRows;
      // Paging.
      private int firstRow;
      private int rowsPerPage;
      private int totalPages;
      private int pageRange;
      private Integer[] pages;
      private int currentPage;
      // Sorting.
      private String sortField;
      private boolean sortAscending;
  
      private static class ActiveSessionObj {
  
          /* Oracle table structure
          CREATE TABLE ACTIVESESSIONSLOG(
          ASESSIONID VARCHAR2(30 ) NOT NULL,
          USERID VARCHAR2(30 ),
          ACTIVITYSTART TIMESTAMP(6),
          ACTIVITYEND TIMESTAMP(6),
          ACTIVITY CLOB
          )
           */
          private String aSessionID;
          private String userID;
          private Date activityStart;
          private Date activityEnd;
          private String activity;
  
          public ActiveSessionObj(String aSessionID, String userID, Date activityStart, Date activityEnd, String activity) {
              this.aSessionID = aSessionID;
              this.userID = userID;
              this.activityStart = activityStart;
              this.activityEnd = activityEnd;
              this.activity = activity;
          }
  
          public String getaSessionID() {
              return aSessionID;
          }
  
          public void setaSessionID(String aSessionID) {
              this.aSessionID = aSessionID;
          }
  
          public String getActivity() {
              return activity;
          }
  
          public void setActivity(String activity) {
              this.activity = activity;
          }
  
          public Date getActivityEnd() {
              return activityEnd;
          }
  
          public void setActivityEnd(Date activityEnd) {
              this.activityEnd = activityEnd;
          }
  
          public Date getActivityStart() {
              return activityStart;
          }
  
          public void setActivityStart(Date activityStart) {
              this.activityStart = activityStart;
          }
  
          public String getUserID() {
              return userID;
          }
  
          public void setUserID(String userID) {
              this.userID = userID;
          }
  
          private ActiveSessionObj() {
              throw new UnsupportedOperationException("Not yet implemented");
          }
      }
  
      // Constructors -------------------------------------------------------------------------------
      public Sessions() {
          // Set default values somehow (properties files?).
          rowsPerPage = 10; // Default rows per page (max amount of rows to be displayed at once).
          pageRange = 10; // Default page range (max amount of page links to be displayed at once).
          sortField = "ASESSIONID"; // Default sort field.
          sortAscending = true; // Default sort direction.
      }
  
      // Paging actions -----------------------------------------------------------------------------
      public void pageFirst() {
          page(0);
      }
  
      public void pageNext() {
          page(firstRow + rowsPerPage);
      }
  
      public void pagePrevious() {
          page(firstRow - rowsPerPage);
      }
  
      public void pageLast() {
          page(totalRows - ((totalRows % rowsPerPage != 0) ? totalRows % rowsPerPage : rowsPerPage));
      }
  
      public void page(ActionEvent event) {
          page(((Integer) ((UICommand) event.getComponent()).getValue() - 1) * rowsPerPage);
      }
  
      private void page(int firstRow) {
          this.firstRow = firstRow;
          loadDataList(); // Load requested page.
      }
  
      // Sorting actions ----------------------------------------------------------------------------
      public void sort(ActionEvent event) {
          String sortFieldAttribute = (String) event.getComponent().getAttributes().get("sortField");
  
          // If the same field is sorted, then reverse order, else sort the new field ascending.
          if (sortField.equals(sortFieldAttribute)) {
              sortAscending = !sortAscending;
          } else {
              sortField = sortFieldAttribute;
              sortAscending = true;
          }
  
          pageFirst(); // Go to first page and load requested page.
      }
  
      // Loaders ------------------------------------------------------------------------------------
      private void loadDataList() {
  
          // Load list and totalCount.
          try {
              dataList = list(firstRow, rowsPerPage, sortField, sortAscending);
              totalRows = count(); //count the tablerows
          } catch (Exception e) {
              throw new RuntimeException(e); // Handle it yourself.
          }
  
          // Set currentPage, totalPages and pages.
          currentPage = (totalRows / rowsPerPage) - ((totalRows - firstRow) / rowsPerPage) + 1;
          totalPages = (totalRows / rowsPerPage) + ((totalRows % rowsPerPage != 0) ? 1 : 0);
          int pagesLength = Math.min(pageRange, totalPages);
          pages = new Integer[pagesLength];
  
          // firstPage must be greater than 0 and lesser than totalPages-pageLength.
          int firstPage = Math.min(Math.max(0, currentPage - (pageRange / 2)), totalPages - pagesLength);
  
          // Create pages (page numbers for page links).
          for (int i = 0; i &lt; pagesLength; i++) {
              pages[i] = ++firstPage;
          }
      }
  
      // Getters ------------------------------------------------------------------------------------
      public List&lt;ActiveSessionObj&gt; getDataList() {
          if (dataList == null) {
              loadDataList(); // Preload page for the 1st view.
          }
          return dataList;
      }
  
      public int getTotalRows() {
          return totalRows;
      }
  
      public int getFirstRow() {
          return firstRow;
      }
  
      public int getRowsPerPage() {
          return rowsPerPage;
      }
  
      public Integer[] getPages() {
          return pages;
      }
  
      public int getCurrentPage() {
          return currentPage;
      }
  
      public int getTotalPages() {
          return totalPages;
      }
  
      // Setters ------------------------------------------------------------------------------------
      public void setRowsPerPage(int rowsPerPage) {
          this.rowsPerPage = rowsPerPage;
      }
  
      // Actions ------------------------------------------------------------------------------------
      /**
       * Returns list of ActiveSessionObj items starting at the given first index with the given row count,
       * sorted by the given sort field and sort order.
       * @param firstRow First index of rows to be returned.
       * @param rowCount Amount of rows to be returned.
       * @param sortField Field to sort the data on.
       * @param sortAscending Whether to sort data ascending or not.
       * @return list of ActiveSessionObj items starting at the given first index with the given row count,
       * sorted by the given sort field and sort order.
       * @throws DAOException If something fails at DAO level.
       */
      public List&lt;ActiveSessionObj&gt; list(int firstRow, int rowCount, String sortField, boolean sortAscending) throws SQLException {
  
          String SQL_LIST_BY_ORDER_AND_LIMIT = "SELECT * FROM ACTIVESESSIONSLOG WHERE ROWNUM &gt;= ? AND ROWNUM &lt;= ? ORDER BY ? ?";     
  
          if (ds == null) {
              throw new SQLException();
          }
  
          String sortDirection = sortAscending ? "ASC" : "DESC";
          String sql = String.format(SQL_LIST_BY_ORDER_AND_LIMIT, sortField, sortDirection);
          Connection conn = ds.getConnection();
          PreparedStatement preparedStatement = null;
          ResultSet resultSet = null;
          List&lt;ActiveSessionObj&gt; dataList = new ArrayList&lt;ActiveSessionObj&gt;();
  
          try {
              conn.setAutoCommit(false);
              boolean committed = false;
              preparedStatement = conn.prepareStatement(sql);
              preparedStatement.setInt(1, firstRow);
              preparedStatement.setInt(2, rowCount);
              preparedStatement.setString(3, sortField);
              preparedStatement.setString(4, sortDirection);                   
  
              resultSet = preparedStatement.executeQuery();
              /* take the result from the SQL query and insert it into Array List collection */
              dataList = ActiveSessionsArrayList(resultSet);
  
          } catch (SQLException e) {
              throw new SQLException(e);
          } finally {
              conn.close();
          }
  
          return dataList;
      }
  
      /**
       * Returns total amount of rows in table.
       * @return Total amount of rows in table.
       * @throws DAOException If something fails at DAO level.
       */
      public int count() throws Exception {
  
          String SQL_COUNT = "SELECT count(*) FROM ACTIVESESSIONSLOG";
  
          PreparedStatement preparedStatement = null;
          ResultSet resultSet = null;
          int count = 0;
  
          try {
              Connection conn = ds.getConnection();
              preparedStatement = conn.prepareStatement(SQL_COUNT);
              resultSet = preparedStatement.executeQuery();
              if (resultSet.next()) {
                  count = resultSet.getInt(1);
              }
          } catch (SQLException e) {
              throw new Exception(e);
          } finally {
              //close the connection
          }
  
          return count;
      }
  
      /**
       * Map the current row of the given ResultSet to ActiveSessionObj.
       * @param resultSet The ResultSet of which the current row is to be mapped to ActiveSessionObj.
       * @return The mapped ActiveSessionObj from the current row of the given ResultSet.
       * @throws SQLException If something fails at database level.
       */
      private static ArrayList&lt;ActiveSessionObj&gt; ActiveSessionsArrayList(ResultSet rs) throws SQLException 
      {
          ArrayList&lt;ActiveSessionObj&gt; al = null;
  
          try
          {
              while(rs.next())
              {
                  al.add(new ActiveSessionObj(
                  rs.getString("ASESSIONID"),
                  rs.getString("USERID"),
                  timestampToDate(rs.getTimestamp("ACTIVITYSTART")),
                  timestampToDate(rs.getTimestamp("ACTIVITYEND")),
                  rs.getString("ACTIVITY")
  
                  ));
              }
          }
          catch(Exception x) {x.printStackTrace();}
  
          return al;
  
  
          /*
          CREATE TABLE ACTIVESESSIONSLOG(
          ASESSIONID VARCHAR2(30 ) NOT NULL,
          USERID VARCHAR2(30 ),
          ACTIVITYSTART TIMESTAMP(6),
          ACTIVITYEND TIMESTAMP(6),
          ACTIVITY CLOB
          )
           */
      }
      // select all button handle
      private Map&lt;Long, Boolean&gt; selectedIds = new HashMap&lt;Long, Boolean&gt;();
  
      // get the list from the JSF page
      public Map&lt;Long, Boolean&gt; getSelectedIds() {
          return selectedIds;
      }
  
      // call this method from the JSF page to delete the selected rows
      public String deleteAction() {
  
          // do something with the selected rows
          return null;
      }
  
      private static Date timestampToDate(java.sql.Timestamp ts)
      {
          Date d = null;
          try
          {
              d = new Date(ts.getTime());
          }
          catch (Exception e) { e.printStackTrace(); }
  
          return d;
      }
  }
  </code></pre>
  
  <p>I get this error stack when I run the code in Glassfish:</p>
  
  <pre><code>java.lang.RuntimeException: java.sql.SQLException: java.sql.SQLSyntaxErrorException: ORA-01745: invalid host/bind variable name
  
      at com.DX_57.HM_57.Sessions.loadDataList(Sessions.java:193)
      at com.DX_57.HM_57.Sessions.getDataList(Sessions.java:214)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:601)
      at javax.el.BeanELResolver.getValue(BeanELResolver.java:363)
      at com.sun.faces.el.DemuxCompositeELResolver._getValue(DemuxCompositeELResolver.java:176)
      at com.sun.faces.el.DemuxCompositeELResolver.getValue(DemuxCompositeELResolver.java:203)
      at com.sun.el.parser.AstValue.getValue(AstValue.java:138)
      at com.sun.el.parser.AstValue.getValue(AstValue.java:183)
      at com.sun.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:224)
      at com.sun.faces.facelets.el.TagValueExpression.getValue(TagValueExpression.java:109)
      at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:194)
      at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:182)
      at javax.faces.component.UIData.getValue(UIData.java:731)
      at javax.faces.component.UIData.getDataModel(UIData.java:1798)
      at javax.faces.component.UIData.setRowIndexWithoutRowStatePreserved(UIData.java:484)
      at javax.faces.component.UIData.setRowIndex(UIData.java:473)
      at com.sun.faces.renderkit.html_basic.TableRenderer.encodeBegin(TableRenderer.java:81)
      at javax.faces.component.UIComponentBase.encodeBegin(UIComponentBase.java:820)
      at javax.faces.component.UIData.encodeBegin(UIData.java:1118)
      at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1755)
      at javax.faces.render.Renderer.encodeChildren(Renderer.java:168)
      at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:845)
      at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1757)
      at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1760)
      at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1760)
      at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(FaceletViewHandlingStrategy.java:402)
      at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:131)
      at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:121)
      at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
      at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:139)
      at javax.faces.webapp.FacesServlet.service(FacesServlet.java:594)
      at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1542)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:281)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
      at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161)
      at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
      at com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317)
      at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195)
      at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:849)
      at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:746)
      at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1045)
      at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:228)
      at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
      at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
      at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
      at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
      at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
      at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
      at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
      at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
      at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
      at java.lang.Thread.run(Thread.java:722)
  Caused by: java.sql.SQLException: java.sql.SQLSyntaxErrorException: ORA-01745: invalid host/bind variable name
  
      at com.DX_57.HM_57.Sessions.list(Sessions.java:289)
      at com.DX_57.HM_57.Sessions.loadDataList(Sessions.java:190)
      ... 57 more
  Caused by: java.sql.SQLSyntaxErrorException: ORA-01745: invalid host/bind variable name
  
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
      at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
      at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
      at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
      at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
      at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)
      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628)
      at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493)
      at com.sun.gjc.spi.jdbc40.PreparedStatementWrapper40.executeQuery(PreparedStatementWrapper40.java:642)
      at com.DX_57.HM_57.Sessions.list(Sessions.java:284)
      ... 58 more
  </code></pre>
  
  <p>I suppose that the SQL query is not correct or there is a problem with the prepared statement. What is the proper way to this query?</p>
  
  <p>Best Wishes</p>
  
  <p><strong>EDIT</strong> This is the Oracle table:</p>
  
  <pre><code>-- TABLE ACTIVESESSIONSLOG
  
  CREATE TABLE ACTIVESESSIONSLOG(
    ASESSIONID VARCHAR2(30 ) NOT NULL,
    USERID VARCHAR2(30 ),
    ACTIVITYSTART TIMESTAMP(6),
    ACTIVITYEND TIMESTAMP(6),
    ACTIVITY CLOB
  )
  /
  
  -- ADD KEYS FOR TABLE ACTIVESESSIONSLOG
  
  ALTER TABLE ACTIVESESSIONSLOG ADD CONSTRAINT ASESSIONID PRIMARY KEY (ASESSIONID)
  /
  </code></pre>