reworking Access to mySQL

[Ken Kesselman]

Hi, All.

I have a working project - a simulator of Web auction. Currently it pulls all data from a small 4-table Access DB. My question is: is it possible to change the format of the database from Access to mySQL, and what steps should I take to do that?

I'm a very, very lightweight in JDBC\Java.. So any help is GREATELY appreciated.

I'd be glad to provide you the source code of thisproject if you decide to help.

Thank you in advance,

KK

[Bear Bibeault]

What aspect are you confused about? Converting the data, or converting the application?

If the latter, unless you used Access-specific SQL statements (and the table structure and names are identical) your app may not need to change at all.

[Ken Kesselman]

Hi, There!

Thank you for replying.

I guess I'm confused in the way the application will connect to DB. As about the query statements - they are standard: SELECT, INSERT, UPDATE

Here's my DBConnect.java:

package package;
import java.sql.*;
 
public class DBConnect {
  private Connection connection;
  private String url = "jdbc  :-o	  dbc  :-o	  asp";
  private String username = "anonymous";
  private String password = "guest";
 
  public DBConnect() {
  }
 
  public Connection getConnection() {
    try {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      connection = DriverManager.getConnection(
          url, username, password);
    }
    catch (ClassNotFoundException cnfex) {
      System.err.println("Failed to load JDBC/ODBC driver.");
      cnfex.printStackTrace();
      System.exit(1); // terminate program
    }
    catch (SQLException sqlex) {
      System.err.println("Unable to connect");
      sqlex.printStackTrace();
    }
    return connection;
       }
    }

My qyuestion is how do I change that so i do not have to set up ODBC connection on the system? Basically, so it uses JDBC driver directly - smth like this:

package data;
 
import java.sql.*;
 
public class CommonDataBean
{
    public static String CONN_STR = 
    "jdbc:mysql://localhost/sequencedb?user=root&password=";
    public static String DRIVER_URL = "com.mysql.jdbc.Driver";
    protected Connection connection = null;
 
    public CommonDataBean() throws BeanException
    {
if (this.connection == null)
    try {
Class.forName( DRIVER_URL );
this.connection = DriverManager.getConnection( CONN_STR );
    } catch ( ClassNotFoundException cnfe ) {
    throw new BeanException( this.getClass().getName(),
 "CommonDataBean", cnfe.getMessage() );
    } catch ( SQLException sqle ) {
    throw new BeanException( this.getClass().getName(),
 "CommonDataBean", sqle.getMessage() );
    }
    }

Am I on the right track here? Can I dierctly modify my code to just change from using ODBC to just JDBC driver?

Thank you again!

Ken
[ May 19, 2005: Message edited by: Bear Bibeault ]

[Bear Bibeault]

Yes, that's the right tactic. Your JDBC driver obviously needs to tbe in the classpath, but no other system-specific setup is needed (no control panel crap or stuff like that). The JDBC URL will need to match what is expected by MySQL as well. The MySQL docs should provide that info, or those more familiar with MySQL than myself -- I usually use PostgreSQL -- can chime in.

[Ken Kesselman]

Hey, Bear!

I changed my code to look like this:

 package package;
import java.sql.*;
 
public class DBConnect {
  
  public static String CONN_STR = 
  "jdbc:mysql://localhost/sequencedb?user=root&password=";
  public static String DRIVER_URL = "com.mysql.jdbc.Driver";
  protected Connection connection = null;
  
  public DBConnect() {
  }
 
  public Connection getConnection() {
    try {
      Class.forName(DRIVER_URL);
      connection = DriverManager.getConnection(CONN_STR);
    }
    catch (ClassNotFoundException cnfex) {
      System.err.println("Failed to load JDBC driver.");
      cnfex.printStackTrace();
      System.exit(1); // terminate program
    }
    catch (SQLException sqlex) {
      System.err.println("Unable to connect");
      sqlex.printStackTrace();
    }
    return connection;
       }
    }

Does this look right? (assuming all the URLs are correct)?

KK