This discussion is archived
5 Replies Latest reply: Oct 16, 2007 12:05 AM by 807605 RSS

Connecting to SQL server and create database

807605 Newbie
Currently Being Moderated
I'm looking to simplify the database configuration process for the user of my application. With a fresh installation of MySQL, there are no usable databases so I have to create my own for my application. Am I able to execute statements to create a database and table, or can I only make a database connection if the database already exists?

Here is my method for connecting:
    private void setupDatabase ()
    {
        UserProperties props = new UserProperties ();
        String [] statements = {"CREATE DATABASE IF NOT EXISTS music",
        "DROP TABLE IF EXISTS library",
        "CREATE TABLE library ( artist varchar (30) DEFAULT '', title varchar (30) DEFAULT '', path varchar (255), length int DEFAULT 0, id INT UNIQUE AUTO_INCREMENT, PRIMARY KEY (id) ) TYPE=INNODB"};
        
        try
        {
            progressLabel.setText ("Connecting to database...");
            downloadProgress.setIndeterminate (true);
            Connection connection = Database.setupConnection ();
            
            progressLabel.setText ("Configuring database...");
            Statement stmt = connection.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            for (int i = 0; i < statements.length; i++)
            {
                String sql = statements;
PreparedStatement pstmt = connection.prepareStatement (sql);
pstmt.executeQuery ();
Database.closePreparedStatement (pstmt);
}
Database.closeStatement (stmt);
Database.closeConnection (connection);
}
catch (Exception ex)
{
JOptionPane.showMessageDialog (null,"There was an error in configuring your MySQL databse.\n\nPlease check your network connection. "
+ " Contact the vendor if the problem persists.", "Connection Error",
JOptionPane.ERROR_MESSAGE);
}

}
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 1. Re: Connecting to SQL server and create database
    807605 Newbie
    Currently Being Moderated
    So what is the output after u execute your code?
  • 2. Re: Connecting to SQL server and create database
    807605 Newbie
    Currently Being Moderated
    java.sql.SQLException: Can not issue data manipulation statements with executeQuery().
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
            at com.mysql.jdbc.Statement.checkForDml(Statement.java:409)
            at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1344)
            at MusicList.setupDatabase(MusicList.java:361)
            at MusicList.access$400(MusicList.java:18)
            at MusicList$Task.doInBackground(MusicList.java:180)
            at MusicList$Task.doInBackground(MusicList.java:160)
            at javax.swing.SwingWorker$1.call(SwingWorker.java:279)
            at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
            at java.util.concurrent.FutureTask.run(FutureTask.java:138)
            at javax.swing.SwingWorker.run(SwingWorker.java:319)
            at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
            at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
            at java.lang.Thread.run(Thread.java:619)
  • 3. Re: Connecting to SQL server and create database
    807605 Newbie
    Currently Being Moderated
    Also executing:
                for (int i = 0; i < statements.length; i++)
                {
                    //String sql = statements;
    stmt.execute (statements[i]);
    }
     gives me 
    java.sql.SQLException: No database selected
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2934)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3178)
    at com.mysql.jdbc.Statement.execute(Statement.java:711)
    at MusicList.setupDatabase(MusicList.java:360)
    at MusicList.access$400(MusicList.java:18)
    at MusicList$Task.doInBackground(MusicList.java:180)
    at MusicList$Task.doInBackground(MusicList.java:160)
    at javax.swing.SwingWorker$1.call(SwingWorker.java:279)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
    at java.util.concurrent.FutureTask.run(FutureTask.java:138)
    at javax.swing.SwingWorker.run(SwingWorker.java:319)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
    at java.lang.Thread.run(Thread.java:619)
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 4. Re: Connecting to SQL server and create database
    807605 Newbie
    Currently Being Moderated
    use execute(), or executeUpdate() to issue data manipulation statements.
    executeQuery() is only meant for SELECT queries (i.e. queries that return a
    result set).
  • 5. Re: Connecting to SQL server and create database
    807605 Newbie
    Currently Being Moderated
    So I guess it is creating the database, but because my Connection isn't created with a database name (since one doesn't exist at the time), when I try to execute the statements such as creating the table, it can't because a database isn't selected. I'm assuming after creating the database, I have to then create a new connection with the database name in the URL variable and then do the table creation?