[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

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)
{    
  try
  {  
    database = GIIDatabaseManager.makeConnection();
                 
    Statement statement = database.createStatement();            

    String statementString = "SELECT " +  columnName +
                             " FROM "+ theTable.trim() +
                             " WHERE HeaderName = '"+
                               headerName + "'";
 
    ResultSet theResults = statement.executeQuery(statementString);
 
    theResults.next();
     
     
    database.close();
     
    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

    database.close();

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);
   
    database.close();
     
    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?


0
prain
Asked:
prain
  • 3
  • 2
1 Solution
 
adrpoCommented:
Hi,

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

Cheers,
za-k/
 
0
 
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()?.
0
 
adrpoCommented:

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) {
                          buildIndexMapping();
                     }
     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:
http://www.java2s.com/Open-Source/Java-Document/Open-Source-JDBC-Driver/mysql/com/mysql/jdbc/ResultSetImpl.java.htm

Cheers,
za-k/
0
 
adrpoCommented:

1.
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().

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

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

4.
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!

Cheers,
za-k/
0
 
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 :-)

prain
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now