Internal Exception: java.sql.SQLException: No database selected

Question

I know this question is very common among persistence users. I've visited so many the threads and questions on so many forums and still I'm not getting solution.

I've created RESTful web services from database (an option in Netbeans 8.0.2). I've also created connection pool and JNDI for this project. Names are properly given as shown here.

Using EclipseLink (JPA 2.1) i.e by default I'm getting this kind of response:

GET RequestFailed RequestFailed --> Status: (500) Response: { HTTP Status 500 - Internal Server Error

type Exception report

messageInternal Server Error

descriptionThe server encountered an internal error that prevented it from fulfilling this request.

exception

javax.servlet.ServletException: javax.ejb.EJBException

root cause

javax.ejb.EJBException

root cause

javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLException: No database selected Error Code: 1046 Call: SELECT id, access_expires, access_token, address, bank_account_type, created, device_id, device_type, email, facebook_id, grazie_code, latitude, longitude, password, phone_number, profile_pic, refresh_expires, refresh_token, reg_id, token_created, total_balance, type_work, user_name, user_type FROM users Query: ReadAllQuery(referenceClass=Users sql="SELECT id, access_expires, access_token, address, bank_account_type, created, device_id, device_type, email, facebook_id, grazie_code, latitude, longitude, password, phone_number, profile_pic, refresh_expires, refresh_token, reg_id, token_created, total_balance, type_work, user_name, user_type FROM users")

root cause

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLException: No database selected Error Code: 1046 Call: SELECT id, access_expires, access_token, address, bank_account_type, created, device_id, device_type, email, facebook_id, grazie_code, latitude, longitude, password, phone_number, profile_pic, refresh_expires, refresh_token, reg_id, token_created, total_balance, type_work, user_name, user_type FROM users Query: ReadAllQuery(referenceClass=Users sql="SELECT id, access_expires, access_token, address, bank_account_type, created, device_id, device_type, email, facebook_id, grazie_code, latitude, longitude, password, phone_number, profile_pic, refresh_expires, refresh_token, reg_id, token_created, total_balance, type_work, user_name, user_type FROM users")

root cause

java.sql.SQLException: No database selected

note The full stack traces of the exception and its root causes are available in the GlassFish Server Open Source Edition 4.1 logs. GlassFish Server Open Source Edition 4.1

my persistence.xml file is like this:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
  <persistence-unit name="dwolla_apiPU" transaction-type="JTA">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <jta-data-source>jdbc/dwolla</jta-data-source>
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <shared-cache-mode>ALL</shared-cache-mode>
    <properties>
        <property name="eclipselink.target-database" value="MySQL"/>
        <property name="eclipselink.logging.logger" value="DefaultLogger"/>
        <property name="eclipselink.ddl-generation" value="create-tables"/>
        <property name="eclipelink.logging.level" value="FINEST"/>
        <!-- JDBC connection properties -->
        <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/dwolla"/>
        <property name="javax.persistence.jdbc.user" value="root"/>
        <property name="javax.persistence.jdbc.password" value=""/>
        <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
        <property name="eclipselink.jdbc.batch-writing" value="JDBC"/>
        <property name="javax.persistence.schema-generation.database.action" value="create-tables"/>
    </properties>
  </persistence-unit>
</persistence>

Domain.xml:

<resources>
    <jdbc-resource pool-name="__TimerPool" jndi-name="jdbc/__TimerPool" object-type="system-admin" />
    <jdbc-resource pool-name="DerbyPool" jndi-name="jdbc/__default" object-type="system-all" />
    <jdbc-resource pool-name="APIConnectionPool" jndi-name="jdbc/dwolla" object-type="user" />
    <jdbc-connection-pool name="__TimerPool" datasource-classname="org.apache.derby.jdbc.EmbeddedXADataSource" res-type="javax.sql.XADataSource">
        <property value="${com.sun.aas.instanceRoot}/lib/databases/ejbtimer" name="databaseName" />
        <property value=";create=true" name="connectionAttributes" />
    </jdbc-connection-pool>
    <jdbc-connection-pool is-isolation-level-guaranteed="false" name="DerbyPool" datasource-classname="org.apache.derby.jdbc.ClientDataSource" res-type="javax.sql.DataSource">
        <property value="1527" name="PortNumber" />
        <property value="APP" name="Password" />
        <property value="APP" name="User" />
        <property value="localhost" name="serverName" />
        <property value="sun-appserv-samples" name="DatabaseName" />
        <property value=";create=true" name="connectionAttributes" />
    </jdbc-connection-pool>
    <jdbc-connection-pool is-isolation-level-guaranteed="false" name="APIConnectionPool" datasource-classname="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" res-type="javax.sql.DataSource">
        <property name="URL" value="jdbc:mysql://localhost:3306/dwolla"/>
        <property value="3306" name="PortNumber" />
        <property value="" name="Password" />
        <property value="root" name="User" />
        <property value="localhost" name="serverName" />
        <property value="dwolla" name="DatabaseName" />
        <property value=";create=true" name="connectionAttributes" />
    </jdbc-connection-pool>
</resources>
<servers>
    <server name="server" config-ref="server-config">
        <resource-ref ref="jdbc/__TimerPool" />
        <resource-ref ref="jdbc/__default" />
        <resource-ref ref="jdbc/dwolla" />
    </server>
</servers>

Answer 1

To correct this error, go to glassfish admin console, proceed to JNDI>Additional properties tab.

Under the Name column, confirm if the user,password,URL are correct if not edit the values accordingly.

Edit URL to jdbc:mysql://localhost/{database}

Note that {database} is your database name. Save the changes and test.

Answer 2

I got the solution.As you can see I've edited domain.xml of glassfish server and set the connection pool properties and JNDI. I went to glassfish admin console and edited the connection pool, I get that the server is not recognizing the changes I did. It was showing the URL property like "jdbc:mysql://:3306/", don't know why it didn't get the properties from domain.xml but finally now it is working.