-
1. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
mayerw01 Jul 3, 2017 5:30 AM (in response to ss_2000)Did you link your datasource to the MySQL driver like:
<datasource jta="true" jndi-name="java:jboss/datasources/test" pool-name="test" enabled="true" use-ccm="true">
<connection-url>jdbc:mysql://{hostname}:3306/test</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<driver>mysql</driver>
...
</datasource>
-
2. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
ss_2000 Jul 9, 2017 4:29 PM (in response to mayerw01)Hi Thanks for the reply. Apologies I could not respond earlier> i have been without any Internet.
Yes I have tried your prescribed solution but unfortunately, I keep getting the same result.
-
3. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
emag Jul 9, 2017 6:46 PM (in response to ss_2000)Please share your standalone*.xml you use and how to get DataSource. I suspect your app lookups predefined ExampleDS.
-
4. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
ss_2000 Jul 10, 2017 5:31 AM (in response to emag)I have the following setup in standalone.xml:
<datasources>
<datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">
<connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
<driver>h2</driver>
<security>
<user-name>sa</user-name>
<password>sa</password>
</security>
</datasource>
<datasource datasource jta="true" jndi-name="java:/MySqlDS" pool-name="MySqlDS" enabled="true" use-ccm="true">
<connection-url>jdbc:mysql://localhost:3306/test?useSSL=false</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<driver>mysql</driver>
<pool>
<min-pool-size>2</min-pool-size>
<max-pool-size>5</max-pool-size>
</pool>
<security>
<user-name>root</user-name>
<password>root</password>
</security>
</datasource>
As per the Wildfly documentstion, I have placed the Driver (mysql-connector-java-5.1.42-bin.jar) at the following location along with module.xml:
/Users/satindersingh/servers/wildfly1010/modules/system/layers/base/com/mysql/main
The contents of module.xml are:
<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.1" name="com.mysql">
<resources>
<resource-root path="mysql-connector-java-5.1.42-bin.jar"/>
</resources>
<dependencies>
<module name="javax.api"/>
<module name="javax.transaction.api"/>
</dependencies>
</module>
The DataSource calling in the Servlet is:
@WebServlet(name = "NewServlet", urlPatterns = {"/NewServlet"})
public class NewServlet extends HttpServlet {
private static final Logger LOGGER=Logger.getLogger("NewServlet");
@Resource(name = "java:/MySQLDS")
DataSource ds;
And the calling code within the same Servlet is:
out.println("<h1>Servlet NewServlet at " + request.getContextPath() + "</h1>"); works well and displays
Servlet NewServlet at /TestDS
on the Browser.
out.println("<h2>DataSource found: " + ds.toString() + "</h2>");works well and displays
DataSource found: org.jboss.as.connector.subsystems.datasources.WildFlyDataSource@1e910448
on the Browser.try{
con=ds.getConnection(); works well and displays
if (con!=null){
out.println("<h2>Connection found: " + con.toString() + "</h2>"); works well and displays
Connection found: org.jboss.jca.adapters.jdbc.jdk7.WrappedConnectionJDK7@4f4dd345
out.println("<h2>Auto Commit is : " + con.getAutoCommit() + "</h2>");
Display: Auto Commit is : true
String catalog=con.getCatalog();
if (catalog!=null){
out.println("<h2>Catalog name is : " + catalog + "</h2>");
Catalog name is : TEST
}
String query="select * from TEST.USER";
Statement stm=con.createStatement();
ERROR IS HERE
09:24:21,696 ERROR [stderr] (default task-1) org.h2.jdbc.JdbcSQLException: Schema "TEST" not found; SQL statement:
09:24:21,705 ERROR [stderr] (default task-1) select * from TEST.USER [90079-173]
-
5. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
mayerw01 Jul 10, 2017 7:40 AM (in response to ss_2000)Try to use @Resource(mappedName = "java:/MySQLDS") instead.
-
6. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
ss_2000 Jul 10, 2017 7:50 AM (in response to mayerw01)@Wolfgang
Thanks for your help, but it has not worked. I have just tried.
Regards
-
7. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
mayerw01 Jul 10, 2017 8:07 AM (in response to ss_2000)Is the message the same?
It looks like WildFly is using the default data source (ExampleDS) if mappedName is not specified.
-
8. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
ss_2000 Jul 10, 2017 9:32 AM (in response to mayerw01)Yes message is indeed the same. Unfortunately, I can't check what DataSource is being used. I was hoping that DataSource.toString() method will reveal the name of the database being used. But that method does not return any 'plain english' value. It is just a gobbledygook
org.jboss.as.connector.subsystems.datasources.WildFlyDataSource@7fb14721
Unfortunately, even Connection.toString() method is not meaningful:
org.jboss.jca.adapters.jdbc.jdk7.WrappedConnectionJDK7@73e13d23
But a method Connection.getCatalog() gives the expected value of the Schema name i.e "TEST"
The following statements behave nicely as well:
Connection conn = ds.getConnection();
Statement stm=conn.createStatement();
And finally the error is thrown when I perform -
ResultSet rs=stm.executeQuery("select * from TEST.USER")
The exception is as per my original posting:
14:18:00,503 ERROR [stderr] (default task-1) org.h2.jdbc.JdbcSQLException: Schema "TEST" not found; SQL statement:
14:18:00,512 ERROR [stderr] (default task-1) select * from TEST.USER [90079-173]
I shall keep trying but anu clue or help is greatly appreciated.
Thanks
-
9. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
mayerw01 Jul 10, 2017 10:35 AM (in response to ss_2000)That is really strange.
But you could try to figure out details regarding your connection via
con.getMetaData();
-
10. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
jaikiran Jul 11, 2017 12:40 AM (in response to ss_2000)I think it all comes down to using wrong datasource. In your standalone.xml your JNDI name for the datasource is java:/MySqlDS
<datasource datasource jta="true" jndi-name="java:/MySqlDS"
So you should be using:
@Resource(lookup="java:/MySqlDS")
to inject this datasource. The case of the string matters. In one of the replies, Wolfgang Mayer suggested this change, but there was an oversight with the case of the string which is why I think it didn't work (it's a different issue that it didn't throw an exception in that case).
Do this specific change and get the latest logs (if it still fails).
-
11. Re: org.h2.jdbc.JdbcSQLException: Table "USER" not found; SQL statement
ss_2000 Jul 11, 2017 5:33 AM (in response to jaikiran)Yes it works indeed. It was just a matter of getting the name of the resource in correct case.
Thanks a lot everyone. Appreciate all your help.