September 28, 2007 at 6:31 am
I have a web application that works perfectly fine on SQL 2000. It’s written in JSP and on a Tomcat/Apache web server.
I set up on a laptop the new environment for which we eventually want to run the web application and am now feeding it with the SQL 2005 database.
When I ran the upgrade advisor (not really an upgrade though, I just restored the database from a BAK made on SQL 2000), there aren’t any problems shown.
However, the application only loads some of the contents from the database. We have a log set up to record the errors thrown, and the only thing that is showing up, is an exception of java.lang.Short when it attempts to populate some of the pages.
I first thought it was an issue with the Java upgrade. So I set up another machine with SQL 2000, and the new version of Java, and the application works fine. So thus it’s got to be something with the database itself moving to SQL 2005.
Are there any differences in data types or anything that I am missing that would require us to rewrite parts of our Java code in order to work with SQL 2005?
We build a lot of queries in the Java code that are used against the db. The next thing I’m going to try is completely rebuilding the tables anew in SQL 2005 and populating with csv instead of trying to restore from a backup. Is that a dumb idea? Any other suggestions to try and target the problem?
Thanks, any help is greatly appreciated.
September 28, 2007 at 7:23 am
mkhines (9/28/2007)
I have a web application that works perfectly fine on SQL 2000. It’s written in JSP and on a Tomcat/Apache web server.I set up on a laptop the new environment for which we eventually want to run the web application and am now feeding it with the SQL 2005 database.
When I ran the upgrade advisor (not really an upgrade though, I just restored the database from a BAK made on SQL 2000), there aren’t any problems shown.
However, the application only loads some of the contents from the database. We have a log set up to record the errors thrown, and the only thing that is showing up, is an exception of java.lang.Short when it attempts to populate some of the pages.
I first thought it was an issue with the Java upgrade. So I set up another machine with SQL 2000, and the new version of Java, and the application works fine. So thus it’s got to be something with the database itself moving to SQL 2005.
Are there any differences in data types or anything that I am missing that would require us to rewrite parts of our Java code in order to work with SQL 2005?
We build a lot of queries in the Java code that are used against the db. The next thing I’m going to try is completely rebuilding the tables anew in SQL 2005 and populating with csv instead of trying to restore from a backup. Is that a dumb idea? Any other suggestions to try and target the problem?
Thanks, any help is greatly appreciated.
When you restore a 2000 database backup to 2005, it will run in compatibility mode. You can change this in the database options though. However, this should not cause a problem. The datatypes should be exactly the same. What is the exact exception you are getting? Is it a NumberFormatException? Are you doing any parsing of the data in your Java application? Can you reproduce the problem reliably? If so, can you identify the query that is executed and is likely returning invalid data?
Regards,
Andras
September 28, 2007 at 7:24 am
There are new datatypes in 2k5, and text / ntext / image datatypes are due to be depreciated in the next couple of versions, but I don't think they actually dropped datatypes.
What datatypes are you using?
It could also be (and this is more likely) T-SQL changes that are affecting your queries if you pass in T-SQL to the database via your Java code.
September 28, 2007 at 8:02 am
Here is the exact exception that I am getting -
EXCEPTION_TYPE: Exception CLASS: .DBQuery METHOD: getDiseaseRange MESSAGE: java.lang.Short
EXCEPTION_TYPE: Exception CLASS: .DBQuery METHOD: getDiseaseRange MESSAGE: java.lang.Short
(that exception literally shows up twice here in the log)
And here is the method it refers to in DBQuery -
public DiseaseName[] getDiseaseNames(String rangeStart, String rangeEnd){
return getDiseaseRange(rangeStart, rangeEnd, DISEASE_AFFECTS_EITHER);
}
//This is getting the entire list of diseases with fact sheets (which means we have a web page for them as well) and is used to populate for example the diseaseIndex page
public DiseaseName[] getDiseaseRange(String rangeStart, String rangeEnd, int affects){
Connection conn = this.connect();
String query = null;
try{
query = "SELECT DiseaseNames.diseaseNameID, DiseaseNames.diseaseName, FactSheets.factSheetID FROM DiseaseNames LEFT OUTER JOIN FactSheets ON DiseaseNames.diseaseNameID = FactSheets.diseaseNameID WHERE";
if(affects == DISEASE_AFFECTS_DOMESTIC_ANIMALS){
query += " DiseaseNames.affectsDomestic = 1 AND";
}else if(affects == DISEASE_AFFECTS_HUMANS){
query += " DiseaseNames.affectsHumans = 1 AND";
}
query += " (LEFT(DiseaseNames.diseaseName, 1) BETWEEN ? AND ?) ORDER BY DiseaseNames.diseaseName";
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, rangeStart);
statement.setString(2, rangeEnd);
ResultSet rs = statement.executeQuery();
ArrayList diseases = new ArrayList();
while(rs.next()){
diseases.add(new DiseaseName(rs.getInt("diseaseNameID"), rs.getString("diseaseName"), (Integer) rs.getObject("factSheetID")));
}
statement.close();
return (DiseaseName[]) (diseases.isEmpty() ? null : diseases.toArray(new DiseaseName[diseases.size()]));
}catch(SQLException e){
setError(ERR_SQL, e, query);
return null;
}catch(Exception e){
setError(ERR_GENERAL, e);
return null;
}finally{
this.release(conn);
}
}
Here is the code on the JSP page
<%
int i;
String[][] letters = {
{"A", "C"},
{"D", "F"},
{"G", "I"},
{"J", "L"},
{"M", "O"},
{"P", "R"},
{"S", "U"},
{"V", "X"},
{"Y", "Z"}
};
%>
<%
//letters[0] = First letter
//letters[1] = Last letter
for (i=0; i<letters.length; i++){
%>| <%
}
%>
|
<%
DBQuery dbquery = (DBQuery) application.getAttribute("dbquery");
DiseaseName[] diseases;
for(i = 0; i < letters.length; i++){
diseases = dbquery.getDiseaseNames(letters[0], letters[1]);
if(diseases != null){
%>
<%
for(int j = 0; j < diseases.length; j++){
%> <%
if(diseases[j].getFactSheetID() != null){
%> <%
} else {
%> <%
}
%> <%
}
%>
<%
}
}
%>
Basically, we're grabbing a list of diseases that affect wildlife, and then drawing a list of them on a website. If the disease is one that we have information for, it makes it a hyperlink to the corresponding fact sheet page for that disease.
Also -- what is showing up, is a partial list of the names of diseases, without the hyperlinks where they ought to be -
Let me know if you want to see the table contents or anything.. Thanks for your help!!!
September 28, 2007 at 8:23 am
Unfortunately your exception message got truncated. java.lang.Short is a class, and the full exception message should contain a bit more information 🙂
Have you tried debugging the above application? If you can reproduce this issue in an easy way, can you execute the relevant query and see what it returns if you execute this query directly. If you cannot debug this code for whatever reason, you can fire up SQL Server Profiler, and can find the query that is being executed. See if it returns data that you are expecting. This will help to find out if the problem is on the database side or in the Java application.
Andras
September 28, 2007 at 10:00 am
Hi Andras,
I tried first to take the query and paste it right into Enterprise manager itself, replacing the rangeStart and rangeEnd with relevant numbers instead of ?, then when it runs I get no output. However if I simply create a query and leave out the where clause altogether I get a recordset returned.
So then I tried the SQL profiler method you suggest. I first opened the front page of the site, and then navigated to one of the problem pages (same as noted above) - here is the result of that (see txt file attached).
The front page (successful) load shows up through line # 490, then the second page I load shows up through the rest of the trace until I stopped it.
Does that give you any hints? All I can see from the top part that worked fine on the front page, is that at some point it outputs the exact select (circa 490) clause, unlike the bottom part.. does that tell you that the SQL is bad ?
Thanks,
Megan
September 28, 2007 at 10:39 am
Megan,
In the text file, I don't see anywhere where @PO is being declared or set. that might be part of your problem. Not sure if that's the sum total, though.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply