java.sql.SQLException: No database selected - why?

Question

the last days I was trying to learn how to access mySQL databases via Java. I am able to load the driver and get a connection to the database ( at least I think so, since I don't get an exception there..)

the code is:

    import java.sql.*;
    public class test
    {
        public static void main(String[] args)
        {
            try
            {
              Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
              System.out.println("driver loaded...");
            }
            catch(ClassNotFoundException e){
              System.out.println("Error in loading the driver..."+e);
              System.exit(0);
            }
            try
            {
                Connection dbConnection= DriverManager.getConnection("jdbc:odbc:test","root","password");
                System.out.println("Connection successful...");
                Statement stmt = dbConntection.createStatement();
                stmt.executeUpdate("create table Accounts ( name char(20) )");
             }
             catch(SQLException e)
             {
                  System.out.println("database-ConnectionError: "+e);
                  System.exit(0);
             }   
        }
    }

When I execute it, it says:

driver loaded...
Connection successful...
database-ConnectionError: java.sql.SQLException: [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.31]No database selected

I really don't know the problem, because I thought the database is selected during the "getConnection" process....
I tried to select a database by adding this line:

    stmt.executeUpdate("use test;");

after creating the Statement.

unfortunately it didn't work because I got another exception which said I should check on the syntax. I don't understand that either because in my commandline it works just fine... I don't know if it is possible to use these type of commands via Java so if it isn't, please forgive my mistake.

I hope you can help me and I didn't miss the solution during my own search!

Already Thanks to all who reply and use their time on my problems!

ps. If I forgot to point out some important infos ( I don't think i did) please ask:)

edit: I also tried to create a new database during runtime

     stmt.executeUpdate("CREATE DATABASE test;");

this actually works, but the database won't be selected either...

Elyssa

As an aside: with a JDBC driver, you should not use commands like use <database> to switch between databases. Instead you should use setCatalog and/or setSchema (or specify the database in the connection string). Using use <database> may lead to incorrect behavior of the driver. – Rather

Answer 1

Firstly, I am considering my answer to show you another better way for connection with MySQL Database, it's much easier and less nu-expected Exception(s).
You need to do some steps:

1. Download Connector/J and add it to your class path(if you are using an IDE there is add the .jar to the library, or there is many tuts on YouTube).
2. Create your database in your MySQL program.

3. See this example below example below I made for you demonstrates how to connect and execute queries on MySQL :

   import java.sql.*;
   
   public class MySqlConnection {
     private String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
     private String MYSQL_URL = "jdbc:mysql://localhost:3306/test";
   
     private Connection con;
     private Statement st;
     private ResultSet rs;
   
     public MySqlConnection() {
   
       try {
         Class.forName(MYSQL_DRIVER);
         System.out.println("Class Loaded....");
         con = DriverManager.getConnection(MYSQL_URL,"","");
         System.out.println("Connected to the database....");
         st = con.createStatement();
         int c =st.executeUpdate("CREATE TABLE Accounts (Name VARCHAR(30))");
         System.out.println("Table have been created.");
         System.out.println(c+" Row(s) have been affected");
         con.close();
   
       } catch(ClassNotFoundException ex) {
          System.out.println("ClassNotFoundException:\n"+ex.toString());
          ex.printStackTrace();
   
       } catch(SQLException ex) {
           System.out.println("SQLException:\n"+ex.toString());
           ex.printStackTrace();
       }
     }
   
     public static void main(String...args) {
       new MySqlConnection();
     }
   }
   

Natatorium

@sepphuber3: your'r welcome, maybe and maybe ODBC(Not sure) :). – Natatorium

Answer 2

Before you can add a table, you first have to select a database. you can create a new database with:

  CREATE DATABASE database_name

you can connect to a specific database with:

String url = "jdbc:mysql://localhost/databasename";
String username = "test";
String password = "test";
Connection connection = DriverManager.getConnection(url, username, password);

Cressler

I've got the same problem there. Sorry I forgot to say that... A database is created but still not selected.. how do I do that? – Elyssa

I connect to my database by appending the url where you connect with the database name if you are running localhost, localhost/databasename – Cressler

updated answer with how i connect to my database. This method works fine in my program. – Cressler

I believe it should be possible.. But why do you want to use odbc instead of mysql? dev.mysql.com/downloads/connector/j – Cressler

Answer 3

Here is your updated example, which works for me.

public static void main(String[] args) throws InstantiationException,
        IllegalAccessException {
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        System.out.println("driver loaded...");
    } catch (ClassNotFoundException e) {
        System.out.println("Error in loading the driver..." + e);
        System.exit(0);
    }
    try {
        Connection dbConnection = DriverManager
                .getConnection("jdbc:mysql://localhost/test?user=root&password=password");
        System.out.println("Connection successful...");
        Statement stmt = dbConnection.createStatement();
        stmt.executeUpdate("create table Accounts ( name char(20) )");
    } catch (SQLException e) {
        System.out.println("database-ConnectionError: " + e);
        System.exit(0);
    }
}

Make sure you have added a proper mysql-connector to your build path. I used the: mysql-connector-java-5.1.24-bin.jar

Valiancy

thank you! I'll try the connector you used tomorrow! I really hope I can bring make it run^^ – Elyssa

Answer 4

static final String DB_URL = "jdbc:mysql://localhost:3306/sys";

Use database name in the URL. It worked for me

Educe