I've already posted this once today and so far nobody seems to have an answer, so I'll try once more.
I have a problem that nobody seems to know about or perhaps I'm just not seeing it. Anyway, I put together a simple SQL manager javabean that works properly except for this. Here is my problem:
SELECT * FROM site_mimetypes WHERE extension = 'PROPERTIES'
SELECT * FROM site_mimetypes WHERE extension = 'DOC'
SELECT * FROM site_mimetypes WHERE extension = 'FILES'
These queries run as they should at the db command-line:
- first query returns empty
- second query returns a record that matchs
- third query returns empty
Now, if I run this through my sql javabean, the third query returns the results of the second query. I've also tried using the sql "LIKE" command to no avail. This happens every time. Whats funny though is if for example the extension I'm looking for doesn't exist at all in any form, meaning say I'm searching for the string "EXE", it will return properly as empty because there aren't any records that start with the letter "E". Now as I understood it, isn't the "=" operator supposed to find an exact match. It certainly works a 100 percent of the time during the first query and always when done from the db command-line. I'm using MySQL, Tomcat 5.0, JDK 1.5, Connector/J 3.0.... I'll post my sql javabean and below that, I'll post my simple JSP file that calls the javabean:
package sql;
import java.sql.*;
import java.util.*;
import java.io.*;
public class SQLManager {
private String SQL_URL = new String();
private String SQL_USER = new String();
private String SQL_PASS = new String();
private static String SQL_DRIVER = new String();
private String SQL_RESULT = new String();
private String PROP_PATH = "C:\\Program Files\\Apache Software Foundation\\Tomcat 5.0\\webapps\\dam\\WEB-INF\\classes\\sql\\";
private String PROP_FILE = "database.properties";
public SQLManager() { }
public String getResult() { return this.SQL_RESULT; }
public void executeQuery(String statement, int column) throws SQLException {
try {
retrieveProperties(PROP_PATH + PROP_FILE);
Class.forName(SQL_DRIVER);
Connection conn = DriverManager.getConnection(SQL_URL, SQL_USER, SQL_PASS);
Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stat.executeQuery(statement);
while(rs.next()) {
this.SQL_RESULT = rs.getString(column);
}
rs.close();
stat.close();
conn.close();
} catch(ClassNotFoundException ce) { }
catch(SQLException se) { }
catch(IOException ie) { }
}
public void retrieveProperties(String filename) throws IOException {
Properties props = new Properties();
FileInputStream in = new FileInputStream(filename);
props.load(in);
in.close();
this.SQL_DRIVER = props.getProperty("jdbc.drivers");
this.SQL_URL = props.getProperty("jdbc.url");
this.SQL_USER = props.getProperty("jdbc.username");
this.SQL_PASS = props.getProperty("jdbc.password");
}
}
--------------------------------------------------------------------------------------------------------------
<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" >
<head>
<title>SQL Test</title>
<meta http-equiv="content-type" content="application/xhtml+xml; charset=UTF-8" />
</head>
<jsp:useBean class="sql.SQLManager" id="sql" scope="page">
</jsp:useBean>
<body>
<%
sql.executeQuery("SELECT * FROM site_mimetypes WHERE extension = 'VOODOO';", 2);
sql.executeQuery("SELECT * FROM site_mimetypes WHERE extension = 'DOC';", 2);
sql.executeQuery("SELECT * FROM site_mimetypes WHERE extension = 'FILE';", 2);
%>
<%= sql.getResult() %>
</body>
</html>