Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to make MySQL JDBC Driver case-insensitive

843854Jan 31 2002 — edited Feb 1 2002
Hi,

I'm porting a jdbc application from oracle to mysql. One of the problem is that oracle's column names are case insensitive, whereas CODE in mySql is different from code (see example posted below).

I know that I can change the source code to use always the same case. But is there a way to make the mysql jdbc driver case-insensitive ?
import java.sql.*;

/* getString(String) is case sensitive for org.gjt.mm.mysql.Driver driver */

/*
mysql> describe currency;
+-----------------+----------------+------+-----+----------------+-------+
| Field           | Type           | Null | Key | Default        | Extra |
+-----------------+----------------+------+-----+----------------+-------+
| code            | char(3)        |      | PRI |                |       |
| name            | varchar(40)    |      | UNI |                |       |
| shortCode       | char(2)        | YES  | MUL | NULL           |       |
| exchangeRate    | decimal(19,12) |      |     | 0.000000000000 |       |
| changed         | decimal(1,0)   |      |     | 0              |       |
| markedFlag      | decimal(1,0)   |      |     | 0              |       |
| updateTimestamp | timestamp(14)  | YES  |     | NULL           |       |
+-----------------+----------------+------+-----+----------------+-------+
7 rows in set (0.00 sec)
*/


/*
getObject(1)=EUR
Trying code
getObject("code")=EUR
Trying Code
java.sql.SQLException: Column 'Code' not found.
	at org.gjt.mm.mysql.ResultSet.findColumn(ResultSet.java:1213)
	at org.gjt.mm.mysql.ResultSet.getObject(ResultSet.java:1188)
	at mySqlDemo.main(mySqlDemo.java:61)
getObject(1)=USD
Trying code
getObject("code")=USD
Trying Code
java.sql.SQLException: Column 'Code' not found.
	at org.gjt.mm.mysql.ResultSet.findColumn(ResultSet.java:1213)
	at org.gjt.mm.mysql.ResultSet.getObject(ResultSet.java:1188)
	at mySqlDemo.main(mySqlDemo.java:61)
*/

public class mySqlDemo {
	public final static void main(String[] args) {
		try {
			/*== database constants ==*/
			String DBHOST = "localhost";
			String DBNAME = "test";
			String DBUSER = "test";
			String DBPASS = "";

			String DBDRIVER = "org.gjt.mm.mysql.Driver";

			/*== setup database driver and connect ==*/
			Class.forName(DBDRIVER).newInstance();
			String conurl = "jdbc:mysql://"+DBHOST+"/"+DBNAME;
			Connection db = DriverManager.getConnection(conurl,DBUSER,DBPASS);

			/*== create sql query and execute ==*/
			//String sql = "select code from currency";
			String sql = "select Currency.code, Currency.name, Currency.shortCode, Currency.exchangeRate, Currency.changed, Currency.markedFlag, Currency.updateTimestamp from Currency order by code";

			Statement stmnt = db.createStatement();
			ResultSet rs = stmnt.executeQuery(sql);
			/*== display results ==*/
			while(rs.next()) {
				  System.out.println("getObject(1)=" + rs.getObject(1));

				  // works, because "Currency.code" has been selected
				  System.out.println("Trying code");
				  try {
				  	  System.out.println("getObject(\"code\")=" + rs.getObject("code"));
				  } catch(SQLException ex) {
					  ex.printStackTrace();
				  }

 			      // case sensitive !
				  // does not work, because "Currency.code" has been selected
				  // "code" != "Code"
				  System.out.println("Trying Code");
				  try {
				  	  System.out.println("getObject(\"Code\")=" + rs.getObject("Code"));
				  } catch(SQLException ex) {
					  ex.printStackTrace();
				  }
			}
		} catch(Throwable t) {
			t.printStackTrace();
		}
	}
}
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 1 2002
Added on Jan 31 2002
3 comments
1,706 views