Friday, July 22, 2011

Encoding a local connection to MySQL

The code, which I intend to modify, is described in detail on this page. My first task is to modify the batch file which sets the path to the binaries directories to incorporate the current jdk version:

cd My Documents\DDrive\CodeLocal\Fin
set path=C:\Program Files\Java\jdk1.6.0_26\bin

This batch file also makes current the directory where my code files are kept. It leads me to the second task, which is to modify the code files. The first modification is to replace:

// Load database driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

with

// Load database driver
Class.forName("com.mysql.jdbc.Driver");

The second modification is to replace:

// Make connection
String connn = "jdbc:derby:dbFin;create=true";
connect = DriverManager.getConnection( connn );

with

// Make connection
String url = "jdbc:mysql://localhost:3306/mysql";
connect = DriverManager.getConnection(url,"root", "my password");

The third modification is to create the dbFin database as follows:

stat.executeUpdate("CREATE DATABASE dbFin");

Finally, for simplicity, I shall remove the make table query and leave the disconnection sequence unchanged.

Before modifying the batch file to run the code, I must compile it as follows:

javac FindbCreate.java

This having been done, the one line batch file to run the code becomes:

Java -classpath "C:\Documents and Settings\Jonathan\My Documents\DDrive\ CodeLocal\Fin\mysql-connector-java-5.1.17-bin.jar"; "C:\Documents and Settings\Jonathan\My Documents\DDrive\ CodeLocal\Fin" FindbCreate

Running it was quite hard work, not least because it was late evening and I'd hit the grog again, so the batch files were full of typos. I had also forgotten that I had set my own password on the root user. Anyway, in the clear light of the next morning it ran correctly. For completeness the full text of the code file was:

import java.sql.*;

public class FindbCreate {
public static void main(String args[])
throws ClassNotFoundException, SQLException {
/**
* These are shared variable declarations
*/
Connection connect = null;
Statement stat = null;
/**
* This is the start of the main code.
*/
System.out.println("try connection");
try {
// Load database driver
Class.forName("com.mysql.jdbc.Driver");
// Make connection
String url = "jdbc:mysql://localhost:3306/mysql";
connect = DriverManager.getConnection(url,"root", "my password");
stat = connect.createStatement();
System.out.println("connection succeeds");
} catch(Exception ex) {
System.out.println("connection fails");
System.err.println("SQLException: " + ex.getMessage());
}
/**
* These are the makedatabase lines
*/
System.out.println("try makedatabase");
try {
stat.executeUpdate("CREATE DATABASE dbFin");
System.out.println("makedatabase succeeds");
} catch(Exception ex) {
System.out.println("makedatabase fails");
System.out.println("CREATE DATABASE dbFin");
System.err.println("SQLException: " + ex.getMessage());
} finally {
// Close connection
if (connect!= null) {
try {
connect.close();
System.out.println("disconnection succeeds");
} catch (SQLException sqlEx) {
System.out.println("disconnection fails");
}
}
}
}
}

And the return messages were:

try connection
connection succeeds
try makedatabase
makedatabase succeeds
disconnection succeeds

No comments: