I can select, insert, update but not delete.. please help

[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:

 ===== 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:

.
.
...     }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:

 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 someone please hlep me???

[Srinivasa Raghavan]

See the exception itself tells you where is the error is.
Whats the SQL you are using to delete from a table ?

[Shailesh Chandra]

Romaysa,

There are many system.out.println in your program.
can you tell us which one is last system.out printed to ccnsole.

and what is the delete query formed by your program.

thanks

[Azz Romaysa]

This is my Sql Query:
String deleteQuery= "DELETE "+
"FROM irn_gebruiker "+
"WHERE gebruikersnaam ='" +deletedGeb+"';";

This is the printed query in my consoll:
===== De gebruikers is succesvol verwijderd: Test 22 DELETE FROM irn_gebruiker WHERE gebruikersnaam ='Test 22';

Test 22 is the selected User which I want to delete.
[ January 07, 2005: Message edited by: Azz Romaysa ]

[Srinivasa Raghavan]

Originally posted by Azz Romaysa:
This is my Sql Query:
String deleteQuery= "DELETE "+
"FROM irn_gebruiker "+
"WHERE gebruikersnaam ='" +deletedGeb+"';";

Is that ";" really needed.

[pascal betz]

i think the ; at the end of the statement is only needed in the mysql console but not in JDBC. try without.

pascal

[Azz Romaysa]

It stills give the same Exception:

 ===== 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:
163)
        at com.caucho.server.TcpConnection.run(TcpConnection.java:139)
        at java.lang.Thread.run(Thread.java:534)
===== De gebruikers is succesvol verwijderd: AzzeddineDELETE FROM irn_gebruiker
WHERE gebruikersnaam ='Azzeddine' 

[Srinivasa Raghavan]

Hi Friend,

Do this , write the Delete SQl statement and execute it manually , in a tool like TOAD or SQL console.
If it works format the same delete statement in java code , but without ";"

[Shailesh Chandra]

I think I found the errror

Check the line

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


this line should be written as

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

instead of passing delete query variable you are passing a string as "deleteQuery" in getInformation method.

please verify same and revert

thanks

Shailesh
[ January 07, 2005: Message edited by: Shailesh Chandra ]

[Azz Romaysa]

Shailesh,

I don't know how should I thank you....
Yes that was the fout... I was blind that I couldn't see it.
It works now great.

Thank you very very much.

[Srinivasa Raghavan]

Shailesh Gud work well done .
Where r u from ?

[Shailesh Chandra]

We all learn by mistakes

cheers Shailesh

[Shailesh Chandra]

thanks srini....

I had sent you a personal messsage about me, by the way I am from Delhi

Shailesh
[ January 07, 2005: Message edited by: Shailesh Chandra ]

[Srinivasa Raghavan]

Originally posted by Shailesh Chandra:
thanks srini....

I had sent you a personal messsage about me, by the way I am from Delhi

Shailesh

[ January 07, 2005: Message edited by: Shailesh Chandra ]

Shailesh Do you remember couple of days back i also made a silly, bloody ( sorry for using this word ) mistake , you found that out !!
This happens ... We have to correct it.

[Shailesh Chandra]

yes I remember.