Sign in to follow this  
Davian_

[web] Trouble with SQL. Assistance, please?

Recommended Posts

Davian_    206
Hi. One of my professors has got me doing a little out of class work to help him with a project he's working on. He wants me to write a Java program which will check his database for invalid and duplicate data. Things have been going well, considering that I hadn't used SQL before, and I've pretty much been teaching myself as I go. However, I've recently run into a problem. I'm using the statement below to check for duplicate lines in the Students table, based on the columns called LastName, FirstName, Parent, and DOB. SELECT LastName, FirstName, Parent, DOB, COUNT(*) AS DupCount FROM Students GROUP BY LastName, FirstName, Parent, DOB HAVING COUNT(*) > 1; I tested the statement by plugging it into an Access query, and it works just fine, but when I run it in my program using JDBC, I get the following error: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'LASTNAME' as part of an aggregate function. My research tells me that this error usually comes up if you include a column in the SELECT portion of the statement, then leave it out of the GROUP BY portion, but that's not the case here, as far as I can see. If someone could help me out, I'd greatly appreciate it.

Share this post


Link to post
Share on other sites
markadrake    104
A few people have solved this error by using PreparedStatement. After that I'm really not sure why it's such a problem - it validates in my environment but gives me the same problem passing it through java.

Edit: Does it work via java without "AS DupCount" in the query?

Some pages I've been looking at, but can't seem to find an example that matches your use of COUNT(*) and AS "".

http://msdn2.microsoft.com/en-us/library/c29290ta(VS.80).aspx


[Edited by - markadrake on May 9, 2007 8:14:05 PM]

Share this post


Link to post
Share on other sites
Michalson    1657
COUNT(*) AS ... is perfectly valid, as is HAVING COUNT(*) > 1. The first is the standard way of indicating how many records have been grouped together into 1 record, and the second is a perfectly valid way of only getting records where duplicates (based on GROUP BY conditions) exist.

Could you provide some more information, like the Java source from where you made the query? The SQL you typed in your post is perfectly valid Jet4 (Access) SQL, so the problem might be in how you are composing the string in Java. Just to also be sure, I assume your connection has something like this: Provider=Microsoft.Jet.OLEDB.4.0;

Share this post


Link to post
Share on other sites
Davian_    206
Here are the few lines of code from my file:

sqlStmt = "SELECT LastName, FirstName, Parent, DOB, COUNT(*) AS DupCount FROM Students GROUP BY LastName, FirstName, Parent, DOB HAVING COUNT(*) > 1;";
dbconn.setSqlStmt(sqlStmt);
tableData = dbconn.getResult();


dbconn is an instance of the DatabaseConnection type, which another student wrote to take care of establishing and maintaining the connection to the database. Here is the code for that:

package connectdb;

import java.sql.*;

/**
* <p>Title: DatabaseConnection</p>
* <p>Description: This class sets up the information need to connect either a MySql or
* Access Database. The only information which will change are the drivers. </p>
*/


public class DatabaseConnection {
private Connection connection;
private Statement statement;
private ResultSet result;
private String driverName;
private String dbPath;
private String sqlStmt;
private String userName;
private String password;

/**
* Constructor for the class
*/

public DatabaseConnection() {
}


/**
* Gets a connection object for the specified database
* @return Connection Object
*/

/** @todo Alex's code for matching directly to the database file instead of using an ODBC driver
* Alex's code for matching directly to the database file instead of using an ODBC driver
* DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());
* setDatabaseConnection(DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=DATABASE", "dba", "sql"));*/


public Connection getConnection() {
try{
Class.forName(getDriverName()).newInstance();
//connection = DriverManager.getConnection(this.getDbPath(),this.getUserName(),this.getPassword());
//DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());
//connection = DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=C:/IntelliSimS.mdb","dba","sql");
connection = DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=IntelliSimS.mdb","dba","sql");


}catch(Exception e){
System.out.println("Error in getConnection:Databaseconnection " + e);
return null;
}
return connection;
}

/**
* Manually sets a connection for the database
* @param connection Connection Object
*/

public void setConnection(Connection connection) {
this.connection = connection;
}

/**
* Gets the database path
* @return String value with the database path
*/

public String getDbPath() {
return dbPath;
}

/**
* Sets the database path
* @param dbPath String value with the database path
*/

public void setDbPath(String dbPath) {
this.dbPath = dbPath;
}

/**
* Gets the driver name for the database
* @return String value with the driver name of the database
*/

public String getDriverName() {
return driverName;
}

/**
* Sets the driver name for the specified database
* @param driverName String value with the driver name for the database
*/

public void setDriverName(String driverName) {
this.driverName = driverName;
}

/**
* Gets the ResultSet object for the statement specified
* @return ResultSet Object
*/

public ResultSet getResult() {
try{
result = statement.executeQuery(getSqlStmt());
}catch(Exception e){
System.out.println("Error in getResult:DatabaseConnection " + getSqlStmt()+" "+e);
return null;
}
return result;
}

/**
* Manullat sets the ResultSet Object for the specified statement
* @param result ResultSet Object
*/

public void setResult(ResultSet result) {
this.result = result;
}

/**
* Performs update,insert,delete statements
* @return True if executed correctly, False if executed incorrectly
*/

public boolean update(){
boolean isOk = false;
try{
if(statement.executeUpdate(getSqlStmt()) > 0)
isOk = true;
}catch(Exception e){
System.out.println("Error in DatabaseConnection - add&#47;update() - " + getSqlStmt() + " "+ e);
isOk = false;
}
return isOk;
}

/**
* Gets the SQL Statement for the action
* @return String value with the SQL statement
*/

public String getSqlStmt() {
return sqlStmt;
}

/**
* Sets the SQL statement for the action
* @param sqlStmt String value with the SQL Statement
*/

public void setSqlStmt(String sqlStmt) {
this.sqlStmt = sqlStmt;
}

/**
* Gets the Statement Object for the specified connection
* @return Statement Object
*/

public Statement getStatement() {
try{
statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
}catch(Exception e){
System.out.println("Error in getStatement: DatabaseConnection" + e);
}
return statement;
}

/**
* Manullay sets the statement object for the specified connection
* @param statement Statement Object
*/

public void setStatement(Statement statement) {
this.statement = statement;
}

/**
* Gets the password for the database
* @return String value with the password
*/

public String getPassword() {
return password;
}

/**
* Sets the password for the database
* @param password String value with the password
*/

public void setPassword(String password) {
if(password.equals(""))
this.password = "";
else
this.password = password;
}

/**
* Gets the user name for the database
* @return String value with the username
*/

public String getUserName() {
return userName;
}

/**
* Sets the user name for the database
* @param userName String value with the user name
*/

public void setUserName(String userName) {
if(userName.equals(""))
this.userName = "";
else
this.userName = userName;
}

/**
* Closes all the connections to the database
*/

public void closeConnection(){
try{
if(result != null)
result.close();
if(statement != null)
statement.close();
if(connection != null)
connection.close();
}catch(Exception e){
System.out.println(e);
}
}

}

Share this post


Link to post
Share on other sites
markadrake    104
I know the sql syntax is valid but wondered if it caused a problem with the aggregate function since count was basically being selected and set to "something" and then "something" did not show up in the group by ending clause of the sql.

I'm really not the expert on it all so that was my only guess.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this