How to specify the name of the database

[Alejandro Barrero]

RESOLVED i HAD TO ENTER THE URL FOR THE CONNECTION POOL IN TWO PLACES IN THE GLASSFISH ADMIN CONSOLE.

I created a connection pool to a MySQL database in GlassFish. I did specify the database, but executing SQL statements I am getting the error "No database selected". I remember reading that you can specify the name of the database in JDBC, but I have not been able to find any setter or method in Connection or Statement.

[Jeanne Boyarsky]

It's part of the Connection string URL. This is specified in the datasource if you are using one or driver manager if you are not.

[Alejandro Barrero]

Thank you Jeanne, but I have the database name in the URL "jdbc:mysql://localhost:3306/MySQ". I remember that there is a method in some JDBC class to specify the name of the database; it is something like "use", but i don't remember the details.

[Jeanne Boyarsky]

"use databaseName" is a SQL command. "set databaseName" is similar but for the first time. I've never seen either used in JDBC before though I suppose you could try passing them via a statement and see what happens. My guess is they won't work because you aren't yet connected to a database and JDBC doesn't have the concept of connecting to a database server without the database.

[Alejandro Barrero]

Thanks again. What I am doing is

			InitialContext initialContext = new InitialContext();
			this.dataSource = (DataSource) initialContext.lookup("jdbc/paperles");
...
		Connection conn;
		try {
			conn = dataSource.getConnection();
		} catch (SQLException e1) {
			out.println("BAD CONNECTION");
			return;
		}
...
		try {
			conn = this.dataSource.getConnection();
			Statement statement = conn.createStatement();
			ResultSet result = statement.executeQuery(selectStatement);
			cachedRowSet = new CachedRowSetImpl();
			cachedRowSet.populate(result);
		} catch (SQLException e) {
			out.println(e.getMessage());
			return null;
		}

I know that there is a connection. The line "ResultSet result = statement.executeQuery(selectStatement);" gets executed and throws an exception with the error "No database selected".
I remember reading that this is a problem with MySQL and the solution was to somehow set the database. Unfortunately, "statement.execute("USE MySQL");" is giving me errors. This is happening in a MySQL connection pool and somebody knows the answer.

[Jan Cumps]

What is the connection string in jdbc/paperles? Does it contain a database name?

Is MySQ your database name?

[Alejandro Barrero]

Thank you Jan. I access the database in DbVisualiser with"jdbc:mysql://localhost:3306/MySQL". DbVisualiser shows "MySQL" as the root and node "mysql" contains the tables. In the GlassFish administrator console I created the connection pool with "DatabaseName" as "mysql" and I used "jdbc:mysql://localhost:3306/MySQL" as "url". "jdbc/paperles" is the name of the JDBC Resource in GlassFish.

[Ravi Majety]

Hope the connection retrieveal is done at a different location. Try to use the connection object which is retrieved , instead of calling this.datasource.getConnection();

Because, hope this datasource object is not getting initialized. Just keep and sop statement before "conn = this.dataSource.getConnection();" to check whether this.datasource is initialized or not.

If possible give us the complete code , if the code is restrictive to the company, provide the sample code how you might have programmed this part.

Alejandro Barrero wrote:Thanks again. What I am doing is

			InitialContext initialContext = new InitialContext();
			this.dataSource = (DataSource) initialContext.lookup("jdbc/paperles");
...
		Connection conn;
		try {
			conn = dataSource.getConnection();
		} catch (SQLException e1) {
			out.println("BAD CONNECTION");
			return;
		}
...
		try {
			conn = this.dataSource.getConnection();
			Statement statement = conn.createStatement();
			ResultSet result = statement.executeQuery(selectStatement);
			cachedRowSet = new CachedRowSetImpl();
			cachedRowSet.populate(result);
		} catch (SQLException e) {
			out.println(e.getMessage());
			return null;
		}

I know that there is a connection. The line "ResultSet result = statement.executeQuery(selectStatement);" gets executed and throws an exception with the error "No database selected".
I remember reading that this is a problem with MySQL and the solution was to somehow set the database. Unfortunately, "statement.execute("USE MySQL");" is giving me errors. This is happening in a MySQL connection pool and somebody knows the answer.

[Alejandro Barrero]

Thanks for your help Smarty.
What I have is a war file with a servlet installed in GlassFish. I cannot debug the code in the servlet; all I can do is print messages to response.getWriter() and get them running the servlet. The application is a personal application and I would be glad to provide the code for the entire servlet but it is somewhat long and I am giving here the code that I believe is relevant. I was creating the DataSource in the init() method, but following your suggestion, for testing, I am using

		PrintWriter out = null;
		try {
			out = response.getWriter();
		} catch (IOException e) {
			return;
		}
		if (this.dataSource == null) {
			try {
				InitialContext initialContext = new InitialContext();
				this.dataSource = (DataSource) initialContext.lookup("jdbc/paperles");
			} catch (NamingException e) {
				out.println("CANNOT GET DATA SOURCE");
				return;
			}
		}
		Connection conn;
		try {
			conn = dataSource.getConnection();
		} catch (SQLException e1) {
			out.println("BAD CONNECTION");
			return;
		}

For testing, I am now using

		String method = request.getParameter("Method");
		out.println("TESTING method=" + method);
		if ("TEST".equals(method)) {
			try {
				conn = this.dataSource.getConnection();
				Statement statement = conn.createStatement();
				ResultSet result = statement.executeQuery("SELECT * FROM user");
			} catch (SQLException e) {
				out.println("SQLException " + e.getMessage());
			}
		}

In a test program i have

		HttpURLConnection connection = null;
		OutputStreamWriter wr = null;
		BufferedReader rd = null;
		String line = null;
		URL serverAddress = null;
		try {
			serverAddress = new URL("http://localhost:8080/RemoteORM/paperless_office_remote");
			connection = null;
			connection = (HttpURLConnection) serverAddress.openConnection();
			connection.setRequestMethod("POST");
			connection.setDoOutput(true);
			connection.setDoInput(true);
			connection.setReadTimeout(10000);
			connection.connect();
			DataOutputStream out = new DataOutputStream( connection.getOutputStream() );
			out.writeBytes( "Method=TEST" );
			out.flush();
			out.close();			
			InputStream inputStream = connection.getInputStream();
			rd = new BufferedReader(
					new InputStreamReader(inputStream));
			while ((line = rd.readLine()) != null) {
				System.out.println(line);
			}
		} catch (MalformedURLException e) {
			e.printStackTrace();
		} catch (ProtocolException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			connection.disconnect();
			rd = null;
			wr = null;
			connection = null;
		}

The test program is printing:
TESTING method=TEST
SQLException No database selected


Since the SQLException happens in "statement.executeQuery" I think it means that "statement.executeQuery" is executed as a method in object "statement" and that the connection and the DataSource with the connection pool exist. As I said before, I did set the database name and url (jdbc:mysql://localhost:3306/MySQL) parameters when creating the connection pool in GlassFish. It appears that for some strange reason in the interaction between MySQL and GlassFish the database is not set. I remember reading some time ago that this could happen and that I had to explicitly set the database name, but I don't remember how.
For further testing I changed the driver form mysql-connector-java-5.1.16-bin.jar to mysql-connector-java-5.1.2.jar but i am still getting the same error.

[Ravi Majety]

	Connection conn;
		try {
			conn = dataSource.getConnection();
		} catch (SQLException e1) {
			out.println("BAD CONNECTION");
			return;
		}

As per your exception , In above code the exception has not occurred. If it is so then it should not occur in the below code also

		String method = request.getParameter("Method");
		out.println("TESTING method=" + method);
		if ("TEST".equals(method)) {
			try {
				conn = this.dataSource.getConnection();
				Statement statement = conn.createStatement();
				ResultSet result = statement.executeQuery("SELECT * FROM user");
			} catch (SQLException e) {
				out.println("SQLException " + e.getMessage());
			}
		}

Because here you are making use of the same datasource to retrieve the connection. If you are able to retrieve the connection in init then you must able to retrieve the connection in the above code.

Alejandro , check whether did you declared another local variable datasource in init() method also along with class member variable datasource.

Also make the following change in your code..

	Connection conn;
		try {
			conn = this.dataSource.getConnection();// datasource is changed to this.datasource. 

		} catch (SQLException e1) {
			out.println("BAD CONNECTION");
			return;
		}

		String method = request.getParameter("Method");
		out.println("TESTING method=" + method);
		if ("TEST".equals(method)) {

			try {
                                                     out.println("Datasource is "+this.datasource);// just check this. 
				conn = this.dataSource.getConnection();

				Statement statement = conn.createStatement();
				ResultSet result = statement.executeQuery("SELECT * FROM user");
			} catch (SQLException e) {
				out.println("SQLException " + e.getMessage());
			}
		}

Also try to provide the full stacktrace. you can get that from console or from server log. I dont know exactly where it would be in your glassfish server.

Mean while i will also look on this point on internet.

[Jan Cumps]

Alejandro Barrero wrote:Thank you Jan. I access the database in DbVisualiser with"jdbc:mysql://localhost:3306/MySQL". DbVisualiser shows "MySQL" as the root and node "mysql" contains the tables. In the GlassFish administrator console I created the connection pool with "DatabaseName" as "mysql" and I used "jdbc:mysql://localhost:3306/MySQL" as "url". "jdbc/paperles" is the name of the JDBC Resource in GlassFish.

But you use MySQ in stead of MySQL:

but I have the database name in the URL "jdbc:mysql://localhost:3306/MySQ"

[Jan Cumps]

Alejandro, please do not alter the first post in the thread after it has been going on for a while. It becomes difficult to follow.

[Alejandro Barrero]

I will keep that in mind.