Woshiadai Dev Notebook

1. MySQL(http://www.mysql.com)mm.mysql-2.0.2-bin.jar
Class.forName( “org.gjt.mm.mysql.Driver” );
cn = DriverManager.getConnection( “jdbc:mysql://MyDbComputerNameOrIP:3306/myDatabaseName”, sUsr, sPwd );

2. PostgreSQL(http://www.de.postgresql.org)pgjdbc2.jar
Class.forName( “org.postgresql.Driver” );
cn = DriverManager.getConnection( “jdbc:postgresql://MyDbComputerNameOrIP/myDatabaseName”, sUsr, sPwd );

3. Oracle(http://www.oracle.com/ip/deploy/database/oracle9i/)classes12.zip
Class.forName( “oracle.jdbc.driver.OracleDriver” );
cn = DriverManager.getConnection( “jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL”, sUsr, sPwd );

4. Sybase(http://jtds.sourceforge.net)jconn2.jar
Class.forName( “com.sybase.jdbc2.jdbc.SybDriver” );
cn = DriverManager.getConnection( “jdbc:sybase:Tds:MyDbComputerNameOrIP:2638″, sUsr, sPwd );
// (Default-Username/Password: “dba”/”sql”)

5. Microsoft SQLServer(http://jtds.sourceforge.net)
Class.forName( “net.sourceforge.jtds.jdbc.Driver” );
cn = DriverManager.getConnection( “jdbc:jtds:sqlserver://MyDbComputerNameOrIP:1433/master”, sUsr, sPwd );

6. Microsoft SQLServer(http://www.microsoft.com)
Class.forName( “com.microsoft.jdbc.sqlserver.SQLServerDriver” );
cn = DriverManager.getConnection( “jdbc:microsoft:sqlserver://MyDbComputerNameOrIP:1433;databaseName=master”, sUsr, sPwd );

7. ODBC
Class.forName( “sun.jdbc.odbc.JdbcOdbcDriver” );
Connection cn = DriverManager.getConnection( “jdbc:odbc:” + sDsn, sUsr, sPwd );

8.DB2
Class.forName(”Com.ibm.db2.jdbc.net.DB2Driver”);
String url=”jdbc:db2://192.9.200.108:6789/SAMPLE”
cn = DriverManager.getConnection( url, sUsr, sPwd );

Problem: want to create a new database in MySQL using JDBC

Usually when people work with JDBC, they need a Connection object to the destination database, but since we need to create a new database, where to find an existing connection?

Setup: MySQL v4.1.9 + mysql-connector-java v3.1.6 + J2SE v1.4.2_06

Solution 1: Create a Connection to “mysql” admin database and use it to create the new database.

There are two preloaded databases when you install MySQL: mysql and test. mysql is the admin database that keeps metadata like access control information. We can just create a Connection object to mysql admin database and use it to create another new database.

Solution 2: Use Runtime.exec(command) to call mysql command line client.

Solution 3: Create a directory with the same name as the new database name in MySQL data directory, e.g., C:\Program Files\MySQL\MySQL Server 4.1\data. And MySQL server will “think” a new database is created. This method might have risk of corrupted metadata although I have tried this method before and no abnormal behavior was observed.

Problem: I want to see if a database named “foo” exist in MySQL server

This should not be a tough problem at first sight. However, I googled the topic and did not find an official answer except several quick&dirty solutions. Please let me know if there are better and clean ways to do it right.

Setup: MySQL v4.1.9 + mysql-connector-java v3.1.6 + J2SE v1.4.2_06

Solution 1: Catch the exception for “unknown database %s” (I used this one finally).

If you create a new Connection to the URL that points to a non-existent database in MySQL, an SQLException is thrown with the message “unknown database %s” where %s is the database name. The exception has error code of 1049 (int) and SQL state of 42000 (String). Check MySQL error code list for the full list of possible errors for MySQL.

So, I just check the SQLException error code and/or SQL state to make sure this exception happens so that I know a database with the given name exist or not.

        boolean isExistRepos = true;
       
        try{
            connection = DriverManager.getConnection(reposURL, “root”, “rootpassword”);
        }catch (SQLException se){
            while(se != null && isExistRepos){
                String logMessage = “\n\n An SQL Error Occured: “
                                  + se.getMessage() + “\n\t”
                                  + “Error Code: ” + se.getErrorCode()
                                  + “\n\t” + “SQLState: “
                                  + se.getSQLState() + “\n”;
                System.err.println(logMessage);
               
                //repos does not exist and the connection cannot set up
                //MySQL error list (http://dev.mysql.com/doc/mysql/en/error-handling.html)
                //#Error: 1049 SQLSTATE: 42000 (ER_BAD_DB_ERROR)
                //Message: Unknown database ‘%s’
                if((se.getErrorCode() == 1049) && (se.getSQLState().equalsIgnoreCase(”42000″)))
                    isExistRepos = false;
                se = se.getNextException();
            }
        }

Solution 2: Call mysql command line client using Runtime class.

I did not try it, but here is the general idea. MySQL comes with a command line client program called “mysql” where users can interact with the database server. You can type in “show databases” and a list of existing databases will be presented.

In Java, we can use Runtime.getRuntime().exec(“mysql -u root -p rootpassword”) to get a handle on a Process object, then we can use OutputStream and InputStream to input “show databases” and parse the output to see if the database foo exist in the list of databases.

Solution 3: Check if the directory corresponding to the database exists in MySQL data directory.

I just found that for each database, there exist a directory with the same name in MySQL data directory, e.g. C:\Program Files\MySQL\MySQL Server 4.1\data for my case. So, you can juse check if the directory foo exist in that directory to tell if the corresponding database exist or not. I am not sure about MySQL internals, so this solution is not stable and portable.

Firefox is celebrating 50 million downloads now

I have been using it for a long time now, it is quite neat and fast. You don’t have to worry about IE security holes and non-standard M$ tags anymore.

BTW, this is a test post using BlogJet. The only thing I found annoying with this desktop blog publishing tool is that it has poor support for encoding, e.g. I cannot post Simplified Chinese contents