JDBC table does not exist but it does

Question

I am creating a Java Web with JSF and Facelet that allows an user to register to a database. The issue is that JDBC always tells me that table is not existent. I already lost 5 hours on this and don't know what can be wrong.

It keep saying that the table does not exist, but it does. Would you please be able to help?

Thanks!

Databases:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| landing_page       |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Database tables:

mysql> show tables;
+------------------------+
| Tables_in_landing_page |
+------------------------+
| visitors               |
+------------------------+
1 row in set (0.00 sec)

Describe table visitors;

mysql> describe visitors;
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment              |
| first_name | varchar(255) | NO   |     | NULL              |                             |
| last_name  | varchar(255) | NO   |     | NULL              |                             |
| email      | varchar(255) | NO   |     | NULL              |                             |
| datetime   | datetime     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)

Datasource in the connection method:

private static final String DATASOURCE_NAME = "java:comp/env/jdbc/landing_page";

Insert statement:

prepStatement = connection.prepareStatement("INSERT INTO visitors (first_name, last_name, email) VALUES (?, ?, ?)");

context.xml

<Context>
    <Resource name="jdbc/landing_page" auth="Container" type="javax.sql.DataSource"
               maxActive="100" maxIdle="30" maxWait="10000"
               username="root" password="password" driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/landing_page"/>
</Context>

web.xml

<resource-ref>
        <description>DB Connection</description>
        <res-ref-name>jdbc/landing_page</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
</resource-ref>

Error:

Severe:   java.sql.SQLSyntaxErrorException: Table/View 'VISITORS' 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:586)
    at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareCachedStatement(ConnectionWrapper40.java:255)
    at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareCachedStatement(ConnectionWrapper40.java:52)
    at com.sun.gjc.spi.ManagedConnectionImpl.prepareCachedStatement(ManagedConnectionImpl.java:992)
    at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareStatement(ConnectionWrapper40.java:173)
    at DAO.VisitorDAO.registerVisitor(VisitorDAO.java:43)
    at Bean.VisitorBean.registerVisitor(VisitorBean.java:32)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at javax.el.ELUtil.invokeMethod(ELUtil.java:332)
    at javax.el.BeanELResolver.invoke(BeanELResolver.java:537)
    at javax.el.CompositeELResolver.invoke(CompositeELResolver.java:256)
    at com.sun.el.parser.AstValue.invoke(AstValue.java:283)
    at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:304)
    at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
    at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:87)
    at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
    at javax.faces.component.UICommand.broadcast(UICommand.java:315)
    at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790)
    at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282)
    at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:658)
    at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:318)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
    at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
    at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:416)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:283)
    at com.sun.enterprise.v3.services.impl.ContainerMapper$HttpHandlerCallable.call(ContainerMapper.java:459)
    at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:167)
    at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:206)
    at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:180)
    at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:235)
    at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:283)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:200)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:132)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:111)
    at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
    at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:536)
    at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:112)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:117)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:56)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:137)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:591)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:571)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.derby.client.am.SqlException: Table/View 'VISITORS' 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)
    ... 55 more

[this](http://stackoverflow.com/questions/29184866/java-sql-sqlsyntaxerrorexception-table-view-x-does-not-exist) may be it will help. have a look — jack jay, Feb 04 '17 at 16:49
You may check https://dev.mysql.com/doc/refman/5.7/en/cannot-find-table.html — nikli, Feb 04 '17 at 16:53
The stack trace looks like you are using a Derby Database not MySQL. Are you sure you're actually using the MySQL connection? — vanje, Feb 04 '17 at 17:04
I did not move or change the database. I am on Mac OS. I am sure I am using MySQL. — user1060551, Feb 04 '17 at 17:10
I deleted my comment, the stack trace shows no mysql classes. Do you have derby installed? even if you were using mysql it would fall because outside windows table names are case sensitive — bichito, Feb 04 '17 at 17:15
I checked the Apache Derby Client driver JAR and the MySQL JDBC driver JAR. The MySQL driver JAR doesn't contain a package `org.apache.derby`. You are actually using the Derby driver here not MySQL. Maybe you should do a text search for `jdbc:derby` to find the corresponding configuration. — vanje, Feb 04 '17 at 17:25
Thanks Vanje what you say makes sense, but how do I force Netbeans to use the MySQL JDBC driver? It is for sure included in the project libraries. — user1060551, Feb 04 '17 at 18:43
You 'force' Java (not Netbeans) by using the right configuration. You seem to have the right config, but by the looks of it you are not using that to get a connection. You might want to include the code used to open the connection. — Mark Rotteveel, Feb 05 '17 at 09:23

score 2 · Answer 1 · answered Feb 05 '17 at 19:44

Thanks to vanje's comment I could solve the issue. I installed tomcat instead of glassfish and imported the MySQL JDBC that I downloaded from MySQL instead of the one that came with NetBeans. After that it asked for me to actually test the DB connected from the interface, and it worked.

JDBC table does not exist but it does

1 Answers1