CODEWITHSUNDEEP
javasqlormjpaglassfish
javing
javing

Reputation: 12423

SQLSyntaxErrorException: Table/View 'BUYER' does not exist. What is missing?

A few weeks ago I went on holidays and I paused one of my projects. When I came back I was just checking the registration page and was surprised when I got an SQLException saying that a table does not exist. I don't understand it because that table exists, I created it from an entity. I pasted the code here so you can see that everything seems to be ok. I think it probably has something to do with the database (I use glassfish 3 app server).

Here is an image from the user interface that says that the problem has to do with some validation methods (Checking if a user exists already and checking if an email already exists):

enter image description here

Just in case I will also print the stackTrace:

WARNING: Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Table/View 'BUYER' does not exist.
Error Code: -1
Call: SELECT COUNT(NICKNAME) FROM BUYER WHERE (NICKNAME = ?)
    bind => [test]
Query: ReportQuery(referenceClass=Buyer sql="SELECT COUNT(NICKNAME) FROM BUYER WHERE (NICKNAME = ?)")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:687)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:530)
    at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:529)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:205)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:191)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:262)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:618)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2537)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2480)
    at org.eclipse.persistence.queries.ReportQuery.executeDatabaseQuery(ReportQuery.java:838)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:675)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:958)
    at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:432)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1021)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2857)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1225)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1207)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1181)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:453)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:714)
    at ejbs.BuyersRegistratorEJB.nickNameAlreadyExists(BuyersRegistratorEJB.java:82)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.glassfish.ejb.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1056)
    at org.glassfish.ejb.security.application.EJBSecurityManager.invoke(EJBSecurityManager.java:1128)
    at com.sun.ejb.containers.BaseContainer.invokeBeanMethod(BaseContainer.java:5292)
    at com.sun.ejb.EjbInvocation.invokeBeanMethod(EjbInvocation.java:615)
    at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:797)
    at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:567)
    at org.jboss.weld.ejb.SessionBeanInterceptor.aroundInvoke(SessionBeanInterceptor.java:47)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:858)
    at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:797)
    at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:567)
    at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.doAround(SystemInterceptorProxy.java:157)
    at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.aroundInvoke(SystemInterceptorProxy.java:139)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:858)
    at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:797)
    at com.sun.ejb.containers.interceptors.InterceptorManager.intercept(InterceptorManager.java:367)
    at com.sun.ejb.containers.BaseContainer.__intercept(BaseContainer.java:5264)
    at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:5252)
    at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:190)
    at com.sun.ejb.containers.EJBLocalObjectInvocationHandlerDelegate.invoke(EJBLocalObjectInvocationHandlerDelegate.java:84)
    at $Proxy174.nickNameAlreadyExists(Unknown Source)
    at managedbeans.RegistrationController.validateNickName(RegistrationController.java:170)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.sun.el.parser.AstValue.invoke(AstValue.java:234)
    at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:297)
    at org.jboss.weld.util.el.ForwardingMethodExpression.invoke(ForwardingMethodExpression.java:43)
    at org.jboss.weld.el.WeldMethodExpression.invoke(WeldMethodExpression.java:72)
    at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:98)
    at javax.faces.validator.MethodExpressionValidator.validate(MethodExpressionValidator.java:95)
    at javax.faces.component.UIInput.validateValue(UIInput.java:1127)
    at javax.faces.component.UIInput.validate(UIInput.java:941)
    at javax.faces.component.UIInput.executeValidate(UIInput.java:1189)
    at javax.faces.component.UIInput.processValidators(UIInput.java:691)
    at javax.faces.component.UIForm.processValidators(UIForm.java:243)
    at javax.faces.component.UIComponentBase.processValidators(UIComponentBase.java:1080)
    at javax.faces.component.UIComponentBase.processValidators(UIComponentBase.java:1080)
    at javax.faces.component.UIViewRoot.processValidators(UIViewRoot.java:1180)
    at com.sun.faces.lifecycle.ProcessValidationsPhase.execute(ProcessValidationsPhase.java:76)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:312)
    at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1523)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:279)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:188)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:641)
    at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:97)
    at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:85)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:185)
    at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:325)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:226)
    at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:165)
    at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:791)
    at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:693)
    at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:954)
    at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:170)
    at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:135)
    at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:102)
    at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:88)
    at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:76)
    at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:53)
    at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:57)
    at com.sun.grizzly.ContextTask.run(ContextTask.java:69)
    at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:330)
    at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:309)
    at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLSyntaxErrorException: Table/View 'BUYER' does not exist.
    at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
    at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source)
    at com.sun.gjc.spi.base.ConnectionHolder.prepareStatement(ConnectionHolder.java:535)
    at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareCachedStatement(ConnectionWrapper40.java:251)
    at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareCachedStatement(ConnectionWrapper40.java:48)
    at com.sun.gjc.spi.ManagedConnection.prepareCachedStatement(ManagedConnection.java:880)
    at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareStatement(ConnectionWrapper40.java:169)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1404)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1353)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:645)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:580)
    ... 99 more
Caused by: org.apache.derby.client.am.SqlException: Table/View 'BUYER' does not exist.
    at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
    at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
    at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
    at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source)
    at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source)
    ... 109 more

Here is the EJB that the error says is making the problem:

@Stateless(name = "ejbs/BuyersRegistratorEJB")
public class BuyersRegistratorEJB implements IBuyersRegistratorEJB {
  @PersistenceContext
  private EntityManager em;

  @Override
  public Buyer createBuyer(Buyer buyer) {
    Date date = new Date();
    DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
    buyer.setRegistrationDate(dateFormat.format(date));

    boolean textPatterCorrect = false;
    // TODO: Prepare regular expresion also for serbian latin characters
    String simpleTextPatternText = "^[a-zA-Z0-9]+$";
    Pattern textPattern = null;
    Matcher nameMatcher = null;
    Matcher secondNameMatcher = null;
    Matcher nickNameMatcher = null;
    Matcher passwordMatcher = null;

    textPattern = Pattern.compile(simpleTextPatternText);
    nameMatcher = textPattern.matcher(buyer.getName());
    secondNameMatcher = textPattern.matcher(buyer.getSecondName());
    nickNameMatcher = textPattern.matcher(buyer.getNickName());
    passwordMatcher = textPattern.matcher(buyer.getPassword());

    if (nameMatcher.matches() && secondNameMatcher.matches()
        && nickNameMatcher.matches() && passwordMatcher.matches()) {
      textPatterCorrect = true;
    } else {
      System.out
          .println("SOME OF THE INPUT DO NOT MATCH THE REGULAR EXPRESION FOR TEXT!");
    }

    if (textPatterCorrect) {
      em.persist(buyer);// EJB validation passed. Handle the input to the
                // next layer.
      return buyer;
    } else {
      throw new RuntimeException(
          "[BuyersRegistrationEJB] Text format validation FAILED!");
    }
  }

  // This will check if the email already exists!
  @Override
  public boolean emailAlreadyExists(String value) {
    Query checkEmailExists = em
        .createQuery("SELECT COUNT(b.email) FROM Buyer b WHERE b.email=:emailparam");
    checkEmailExists.setParameter("emailparam", value);
    long matchCounter = 0;
    matchCounter = (Long) checkEmailExists.getSingleResult();
    if (matchCounter > 0) {
      return true;
    }
    return false;
  }

  // This will check if the nickName already exists!
  @Override
  public boolean nickNameAlreadyExists(String value) {
    Query nickNameExists = em.createQuery("SELECT COUNT(n.nickName) FROM Buyer n WHERE n.nickName=:nicknameparam");
    nickNameExists.setParameter("nicknameparam", value);
    long matchCounter = 0;
    matchCounter = (Long) nickNameExists.getSingleResult();
    if (matchCounter > 0) {
      return true;
    }
    return false;
  }
}

I will post my configuration files also:

sun-resources.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE resources PUBLIC "-//Sun Microsystems, Inc.//DTD Application Server 9.0   Resource Definitions //EN" "http://www.sun.com/software/appserver/dtds/sun-resources_1_3.dtd">
<resources>
  <jdbc-resource enabled="true" jndi-name="jdbc/myDatasource" object-type="user" pool-name="Derby_groupbuydb_userPool"/>

  <jdbc-connection-pool allow-non-component-callers="false" associate-with-thread="false" connection-creation-retry-attempts="0" connection-creation-retry-interval-in-seconds="10" connection-leak-reclaim="false" connection-leak-timeout-in-seconds="0" connection-validation-method="auto-commit" datasource-classname="org.apache.derby.jdbc.ClientDataSource" fail-all-connections="false" idle-timeout-in-seconds="300" is-connection-validation-required="false" is-isolation-level-guaranteed="true" lazy-connection-association="false" lazy-connection-enlistment="false" match-connections="false" max-connection-usage-count="0" max-pool-size="32" max-wait-time-in-millis="60000" name="Derby_groupbuydb_userPool" non-transactional-connections="false" pool-resize-quantity="2" res-type="javax.sql.DataSource" statement-timeout-in-seconds="-1" steady-pool-size="8" validate-atmost-once-period-in-seconds="0" wrap-jdbc-objects="false">
    <property name="serverName" value="localhost"/>
    <property name="PortNumber" value="1527"/>
    <property name="DatabaseName" value="groupbuydb"/>
    <property name="User" value="user"/>
    <property name="Password" value="pwd"/>
    <property name="URL" value="jdbc:derby://localhost:1527/groupbuydb;create=true"/>
    <property name="driverClass" value="org.apache.derby.jdbc.ClientDriver"/>
  </jdbc-connection-pool>
</resources>

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
  <persistence-unit name="GroupBuySystem">
    <jta-data-source>jdbc/myDatasource</jta-data-source>
    <class>entities.Administ</class>
    <class>entities.Buyer</class>
    <class>entities.Comment</class>
    <class>entities.Log</class>
    <class>entities.Offer</class>
    <class>entities.Seller</class>
  </persistence-unit>
</persistence>

This all worked correctly 3 weeks ago, but now it doesn't. I dont know what is wrong.

Upvotes: 2

Views: 14346

Answers (2)

Hendra Rismana
Hendra Rismana

Reputation: 21

I've just finished with this problem. I used Ecommerce Affablebean in NetBeans. And I found out that this code:

<resource-ref>
<description>Connects to database for AffableBean application</description>
<res-ref-name>jdbc/affablebean</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>

Should be like this

<resource-ref>
<description>Connects to database for AffableBean application</description>
<res-ref-name>jdbc/affablebean</res-ref-name>
<res-type>javax.sql.ConnectionPoolDataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>

Upvotes: 2

Kieren Johnstone
Kieren Johnstone

Reputation: 42003

There are several possible reasons:

  1. Your table has been deleted
  2. Your credentials can no longer see or access it
  3. Your credentials have changed
  4. Your configuration has become corrupted, or changed
  5. You are looking at the wrong database, or something similar.

You need to rule each one of these out specifically in order, and you will be left with your answer. It's very likely one of these :)

Upvotes: 5

Related Questions