Why ‘No database selected’ SQLException?

Question

why this program is not executing when it goes in to the do while loop second time and why it is giving the exception "Exception java.sql.SQLException: [MySQL][ODBC 5.1 Driver][mysqld-5.0.51a-community-nt]No database selected"

//import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import java.util.Vector;

public class DataBase {

    public void LoadDriver() {

        // Load the JDBC-ODBC bridge driver
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } catch (ClassNotFoundException ee) {
            ee.printStackTrace();
        }
    }

    // 2.open a data source name by means of the jdbcodbcdriver.

    static void connect() throws SQLException {

        // Connect to the database
        Connection con = DriverManager.getConnection("jdbc:odbc:MySQL", "root", "admin");
        Statement stmt = con.createStatement();
        // Shut off autocommit
        con.setAutoCommit(false);


        System.out.println("1.Insert 2.Delete 3.Update 4.Select");
        Scanner s = new Scanner(System.in);
        int x;
        x = s.nextInt();

        String query; // SQL select string
        ResultSet rs; // SQL query results
        boolean more; // "more rows found" switch
        String v1, v2; // Temporary storage results

        Vector<Object> results = new Vector<Object>(10);


        if (x == 1) {

            try {
                stmt.executeUpdate("INSERT INTO employee( emp_id,emp_name ) VALUES ( '122','shiva' ) ");
            } catch(Exception e){System.out.println("Exception " +e);e.printStackTrace();}
        }

        if (x == 2) {

            try {
                stmt.executeUpdate("DELETE from employee where emp_id='102' ");
            }catch(Exception e){System.out.println("Exception "+e);e.printStackTrace();} 
        }

        if (x == 3) {

            try {
                stmt
                        .executeUpdate("UPDATE employee SET emp_name = 'madavan' where emp_id='20'; ");
            } catch(Exception e){System.out.println("Exception "+e);e.printStackTrace();} 
        }


        query = "SELECT * FROM employee ";
        try {
            rs = stmt.executeQuery(query);
            // Check to see if any rows were read
            more = rs.next();
            if (!more) {

                System.out.println("No rows found.");
                return;
            }

            // Loop through the rows retrieved from the query
            while (more) {

                v1 = "ID: " + rs.getInt("emp_id");
                v2 = "Name: " + rs.getString("emp_name");

                System.out.println(v1);
                System.out.println(v2);
                System.out.println("");

                results.addElement(v1 + "\n" + v2 + "\n");

                more = rs.next();
            }
            rs.close();

        } catch (SQLException e) {
            System.out.println("" + results.size() + "results where found.");
        } 
        finally{stmt.close();}
    }

    public static void main(String[] args) throws SQLException {
        String str = "y";
        do {
            DataBase s = new DataBase();
            s.LoadDriver();
            DataBase.connect();
        Scanner sc = new Scanner(System.in);
        System.out.println("DO u Want to PROCEED TO QUERY : ");
        str = sc.next();
        } while (str !="n");
    }

}

Answer 1

Just from looking at the exception.. I would guess that you are not specifying the database. How can you do a select on a table without telling it which schema to select from ? This is typically set in the connection string..

Answer 2

Unless you have to use the jdbc/odbc driver I would use the straight mysql jdbc driver. You can download it free from mysql.

then

public void LoadDriver() {

        // Load the JDBC-ODBC bridge driver
        try {
                Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ee) {
                ee.printStackTrace();
        }
}

static void connect() throws SQLException {

        // Connect to the database
        Connection con = DriverManager.getConnection("jdbc:mysql:host/databasename", "root", "admin");
        Statement stmt = con.createStatement();
...

Answer 3

Found a bug listing at MySQL that gives this error but with different technologies. However, in the description it indicates that it is related to reauthorization not sending the database information, so perhaps that is what you are encountering here as well.

Some things that stick out as odd to me (although no clue if they will have any impact on your error)

• You only need to load the Driver Manager once
• You aren't closing your connection, so either close it or refactor to use the same one.

Perhaps move these two lines to just before the do loop

DataBase s = new DataBase();
s.LoadDriver();

Answer 4

Is the ODBC source actually set up to select a database? eg. can you access the database through another ODBC client tool?

If you need to select a database explicitly in the JDBC string you can do that using the ‘database’ parameter.

But having the database chosen in the ODBC setup would be more usual. And indeed, as Clint mentioned, using the normal MySQL JDBC driver instead of ODBC would be more usual still.

while (str !="n")

That's not how you compare strings in Java.