Migration from MYSQL 3.22 to 5.0.46 Creates a problem when using JDBC

Here is the problem. Please read it carefully. I want a solid answer and not guessed answers.

We had installed MySQL 3.22 for years and recently we did a project using that version reading the DB via JAVA/JDBC.
We had written a function like this....

public static String getItem(String columnName, String headerName)
    database = GIIDatabaseManager.makeConnection();
    Statement statement = database.createStatement();            

    String statementString = "SELECT " +  columnName +
                             " FROM "+ theTable.trim() +
                             " WHERE HeaderName = '"+
                               headerName + "'";
    ResultSet theResults = statement.executeQuery(statementString);
    return theResults.getString(columnName);
  catch(SQLException sqlExp)
   System.out.println("Error in Here");
  return null;    

This worked well with MySQL 3.22, because even if the database is closed, the
theResult (ResultSet) object is now populated. so the return result is extracted from
the theResult object in the memory. So it wasn't matter if the database connection was
closed or not.

AFTERWARDS We Installed MySQL 5.0.43 and moved the tables into that database.
Now WE canot use the above function.

Reason we have found out was that because, in the function, we close the database


before the values is read from the resultset ; throwing the following exception.

at com.mysql.jdbc.ResultSetImpl.buildIndexMapping(ResultSetImpl.java:711)
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1056)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5474)
at xxxxx.xxxx.jdbc.myClassName.getItem(myClassName.java:443)

Obviosly MYSQL 5.0.43 works differently.

We have a soultion. Shown below is the solution we are think to have. But we have many many
functions like this.  But we are concerned about the enormous about of code changes in all
functions like this.

Here is the solution, that works well with MySQL 5.0.43

    String returnValue = theResults.getString(columnName);
    return returnValue;

This works in 5.0.43 but the problem is it's an enormous amount of code change in the whole system.

So my question is....

WHY WOULD THE ResultSet Object (in MySQL 5.0.43), after populated, would want the connection to
the DB to read the data?.

Is there a simple solution to this?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Why don't you make a connection pooling in GIIDatabaseManager?
Then re-write or subclass and override the database.close method to
do nothing.

prainAuthor Commented:
I am sorry I am not clear what you are saying here.
What do you mean by "Connection Pooling".

And the Why do I Override the close()?.

Q: "WHY WOULD THE ResultSet Object (in MySQL 5.0.43),
      after populated,  would want the connection to
      the DB to read the data?."

A: Because it need to build an columnName <-> index mapping
    which it doesn't have yet:
    theResults.getString(columnName); calls findColumn(name):
    public String getString(String columnName) throws SQLException {
              return getString(findColumn(columnName));
    findColumn looks like:
    public synchronized int findColumn(String columnName)
              throws SQLException {
                     Integer index;
                     if (!this .hasBuiltIndexMapping) {
     If there is no index maping, it will build it.
     method buildIndexMapping() will call at some time:
     String columnName = this .fields[i].getName();
     Fields also have a connection reference and if is null
     it will return null when getName() is called.
     Then later in the buildIndexMapping() it will do:
     TreeMap.put(columnName, index) which will cause an java.NullPointerException

Have a look at the code here to understand more:


Actually you don't need to do any connection pooling if you don't want to.
This one should return your db implementation with close() method overriden to NOT
CLOSE THE connection:
MyDatabase database = GIIDatabaseManager.makeConnection();
Then your ReturnSet.getString(columnName); will still have the connection avaliable to
do the buildIndexMapping().

Another way to fix this is to subclass and override the:
to call buildIndexMapping() on the result set BEFORE is returned.
Then i think is safe to close the database.

Yet another way to fix this is to use the old driver with the new MySQL server.

The "weird" way to fix this is to edit the jdbc driver sources and call
buildIndexMapping() at the end in the ResultSet constructors!

Of course if you choose to subclass and override something, you will have to
change the imports everywhere. Instead of:
java.sql.Database or java.sql.Statement you will have to search and replace it with:
myapp.sql.MyDatabase or myapp.sql.MyStatement, but string replace is far better
than editing the source everywhere!


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
prainAuthor Commented:
So basically what you are saying is that the issue is the Driver. In fact the old driver does not work on the new (5.0) MySQL Server. We had that issue. So we replaced the old driver with the appropriate one for mySQL 5.0.43.

Well let me understand well what you say here. Seems like good infor. Will get back.
Thanks :-)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.