Error when deleting a row in the Database ...

[Azz Romaysa]

Hi everybody,

My problem is that I can Isnert, Update, and Select from my databse but canot delete. I get always this Exception when I try to delete:

code:

===== DE ACTION NU IS: DELETE
java.sql.SQLException: Syntax error or access violation message from server: "Yo
u have an error in your SQL syntax; check the manual that corresponds to your My
SQL server version for the right syntax to use near 'deleteQuery' at line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1224)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2248)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2196)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1259)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1205)
at db.DatabaseConnection.getInformation(DatabaseConnection.java:65)
at User.doPost(User.java:151)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:165)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:103)
at com.caucho.server.http.FilterChainServlet.doFilter(FilterChainServlet
.java:96)
at com.caucho.server.http.Invocation.service(Invocation.java:315)
at com.caucho.server.http.CacheInvocation.service(CacheInvocation.java:1
35)
at com.caucho.server.http.HttpRequest.handleRequest(HttpRequest.java:246
)
at com.caucho.server.http.HttpRequest.handleConnection(HttpRequest.java:
164)
at com.caucho.server.TcpConnection.run(TcpConnection.java:139)
at java.lang.Thread.run(Thread.java:534)



This is the code when the query is submitted:

code:


.
.
... }else if (actie.equalsIgnoreCase("DELETE")){

String deletedGeb = req.getParameter("geb");
conn.openConnection();
String deleteQuery= "DELETE "+
"FROM irn_gebruiker "+
"WHERE gebruikersnaam ='" +deletedGeb+"';";
try{
ResultSet rs = conn.getInformation("deleteQuery", 0);
System.out.println("===== De gebruikers is succesvol verwijderd: "+deletedGeb +deleteQuery);
}
catch( Exception e ){
System.out.println(e);
System.out.println("Er kan geen gebruiker verwijderd worden!!!");
}
gotoPage("/result.jsp",req, res);
...
...



And this is my DatabaseConnection class:

code:

package db;

import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.awt.*;
import java.awt.event.*;
import java.text.*;

public class DatabaseConnection {
String query = "";
ResultSet rs = null;
Connection connection = null;
Statement stmt = null;
public static String error;

// Methode: open connectie met database
public void openConnection() {
try {
// Initialiseer de MySql-driver
Class.forName("com.mysql.jdbc.Driver").newInstance();
String url = "jdbc:mysql://localhost/irnDB?user=root&password=0305allahoakbar81";

//String user = "root";

// Open connectie met database
connection = DriverManager.getConnection(url);
// Vang JDBC-errors af

} catch( ClassNotFoundException e) {
System.err.println("JDBC Error:");
e.printStackTrace();
// Vang SQL-errors af
} catch (java.sql.SQLException e) {
System.err.println("SQL Exception:");
e.printStackTrace();

}
catch( Exception e) {
System.err.println("Exception Error:");
e.printStackTrace();
}

}


public ResultSet getInformation( String query, int getValue )
{
this.query = query;

try
{
stmt = connection.createStatement();

// Kijk of de statement een resultset moet teruggeven of niet
if( getValue == 1 )
{
rs = stmt.executeQuery( query );
System.err.println( "===== query : "+query );
return rs;
}
else
{
stmt.executeUpdate( query );
System.err.println( "===== query : "+query );
}
}
catch ( java.sql.SQLException e )
{
e.printStackTrace();
}
return rs;
}



// Methode: Sluit connectie met database af
public void disconnect()
{
try
{
stmt.close();
connection.close();
}

catch( Exception e )
{
System.out.println( e.getMessage() );
}
}

}

Could please someone help me to fix this problem?

Thanks,
Abu Romaysa

[Vijayendra V Rao]

Should'nt it be

DELETE *

?

[Azz Romaysa]

Thanks,

The problem has been solved. But that problem wasn't in SELECT *

Abu Romaysa

[Ben Souther]

You might want to move this to the JDBC forum.

To debug, I usually print the SQL statement to standard out so I can see exactly what I'm sending to the database.

[Mark Spritzler]

Exactly Ben.

This forum is for Servlets question. Your question is more specific to SQL even than JDBC. But I am going to move this to the JDBC Forum.

Also, there is a "CODE" button below the "Add Reply" button that will keep your code formatted with indentation, which makes your code readable. Just posting the code directly makes it impossible to read.

Thanks

Mark

[David Harkness]

You said you solved it, but didn't say how. For anyone curious, the following line was probably it.

ResultSet rs = conn.getInformation("deleteQuery", 0);

Note that "deleteQuery" was the name of the String holding the built query. Removing the quotes should do it.