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();
}
}
}