Can any one help me java.sql.SQLException: ORA-00936: missing expression

Question

package controller;

import pojo.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;




public class JdbcConn {
    int found=0;
    public boolean register(Member c)
    {
        Connection conn=null;
        Statement stmt =null;
        ResultSet rs = null;
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn= DriverManager.getConnection("jdbc:oracle:thin:@localhost","system","system");
            stmt=conn.createStatement();
            System.out.println("Data base is connected");
            rs= stmt.executeQuery("select USERID from member");
            System.out.println("Hi");
            while(rs.next())
            {
                System.out.println("bye");
                if((c.getUserId()).equals(rs.getString(1)) )
                {
                    System.out.println("bye");
                    found=1;
                    break;
                }
            }

            if(found==1)
            {
                return false;
            }
            else
                stmt.executeUpdate("insert into member values ('"+ c.getName() +"','"+c.getEmail()+ "','"+c.getMobileNo()+ "','"+c.getAddress1()+"',,'"+c.getAddress2()+"','"+c.getUserId()+"','"+c.getPassword()+"','"+c.getSecretQuestion()+"','"+c.getSecretAnswer()+"')");

                return true;
        }
        catch(ClassNotFoundException e)
        {
            System.out.print(e);
            e.printStackTrace();
        }
        catch(SQLException e)
        {
            System.out.print(e);
        }
        return false;
    }




    public boolean login(String userid, String password)
    {
        Connection conn=null;
        Statement stmt =null;
        ResultSet rs = null;    
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost","system","system");
            stmt=conn.createStatement();

            rs= stmt.executeQuery("select userid, password from member where userid = '"+userid+"'");
            if(rs==null)
            {
                return false;
            }   

            else
            {
                while(rs.next())
                {
                    String uid,pass;
                    uid=rs.getString(1);
                    pass=rs.getString(2);
                    if(password.equals(pass))
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }

        }
        catch(ClassNotFoundException e)
        {
            System.out.print(e);
        }
        catch(SQLException e)
        {
            System.out.print(e);
        }
        return false;
    }

}

I am invoking the code from one servlet. It is showing me the following output.Though the same code is working correctly in somewhere else:

Output:

Data base is connected
Hi
java.sql.SQLException: ORA-00936: missing expression

Can anyone help me with the problem.

Don't concatenate values into a SQL statement, use a `PreparedStatement` instead. It will most probably fix your current problem, plus it will protect you from SQL injection. — , Jul 21 '14 at 09:28
Your SQL statement is wrong: `... c.getAddress1()+"',,'"+c.getAddress2() ...`. There is a repeated comma. — sampathsris, Jul 21 '14 at 09:30

score 2 · Accepted Answer · answered Jul 21 '14 at 09:29

2

In your insert you have the following

+c.getAddress1()+"',,'"+c.getAddress2()+

There should be only one comma instead of two

answered Jul 21 '14 at 09:29

Mansuro

4,558
4
36
76

or `+c.getAddress1()+"',\"\",'"+c.getAddress2()+` – Subhrajyoti Majumder Jul 21 '14 at 09:30
Thanks.....you are right..problem is solved ....but i am not getting why first time bye is not printed .....as the while is executed – Prasenjit Jul 21 '14 at 09:41
@user3773682 select USERID from member doesn't return any rows probably – Mansuro Jul 21 '14 at 10:21

score 1 · Answer 2 · edited May 23 '17 at 12:27

I suggest to use PreparedStatement to avoid such error.

Advantage of using PreparedStatement over Statement

A SQL statement is precompiled and stored in a PreparedStatement object.
This object can then be used to efficiently execute this statement multiple times.
Reduces execution time.
Automatic prevention of SQL injection attacks by builtin escaping of quotes and other special characters

Read more PreparedStatements and performance

See Java Tutorial on Using Prepared Statements

sample code:

String insertTableSQL = "INSERT INTO DBUSER"
        + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
        + "(?,?,?,?)";
PreparedStatement preparedStatement = dbConnection.prepareStatement(insertTableSQL);
preparedStatement.setInt(1, 11);
preparedStatement.setString(2, "mkyong");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
// execute insert SQL stetement
preparedStatement .executeUpdate();

Find complete Sample example here to learn more about it.

score 0 · Answer 3 · answered Jul 21 '14 at 09:33

Use prepared statements to avoid such issues. This will also avoid issues which you may face because of certain special characters like single quotes etc.
If not, then try one of the following:
1. If there is no column between address1 and address2, then use below (extra comma removed):
  
  String query = " stmt.executeUpdate("insert into member values ('"+ c.getName() +"','"+c.getEmail()+ "','"+c.getMobileNo()+ "','"+c.getAddress1()+"','"+c.getAddress2()+"','"+c.getUserId()+"','"+c.getPassword()+"','"+c.getSecretQuestion()+"','"+c.getSecretAnswer()+"')"); "; stmt.executeUpdate (query);
2. If there is supposed to be an empty value that has to be inserted, then use below (an empty field is inserted using ,'', )
  
  String query = " stmt.executeUpdate("insert into member values ('"+ c.getName() +"','"+c.getEmail()+ "','"+c.getMobileNo()+ "','"+c.getAddress1()+"', '' ,'"+c.getAddress2()+"','"+c.getUserId()+"','"+c.getPassword()+"','"+c.getSecretQuestion()+"','"+c.getSecretAnswer()+"')"); "; stmt.executeUpdate (query);

score 0 · Answer 4 · answered Jul 21 '14 at 09:37

In your insert statement you can specify which columns you insert/update that would correspond to the SQL syntax having insert into member (col1, col2) values (val1, val2). The number of columns should be exactly the same before and after values in parenthesis. Incorrect number of columns or values are caused the error.

Can any one help me java.sql.SQLException: ORA-00936: missing expression

4 Answers4