5 Replies Latest reply on Oct 16, 2007 7:05 AM by 807605

    Connecting to SQL server and create database

    807605
      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
          So what is the output after u execute your code?
          • 2. Re: Connecting to SQL server and create database
            807605
            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
              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
                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
                  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?