Reputation: 45

MySql Exception Syntax error

I am getting MySQL Exception when the servlet gets executed . I cannot find where the syntax has gone wrong in the query.I am getting following Exception -

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's1.employee_id='I123' and s1.creation_date= DATE_FORMAT(sysdate(), '%Y-%m-%d') -' at line 1

Below is the servlet which contains the mysql query -

   import java.io.IOException;
    import java.sql.ResultSet;
    import java.text.SimpleDateFormat;

    import java.util.Date;
    import java.util.Locale;

    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import javax.servlet.http.HttpSession;

    public class HomepageTable extends connection {
        String sql,table;
        ResultSet rs = null;
        private static final long serialVersionUID = 1L;
        Date now = new Date();

        SimpleDateFormat dateFormat = new SimpleDateFormat("EEE", Locale.US); 
        String asWeek = dateFormat.format(now);

        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            try
            {
                System.out.println(asWeek);
                get_connection();
            if(asWeek.equals("Mon"))
            {
                sql="select e1.employee_name,d1.dealer_name , d2.distributor_name ,  s1.invoice_id ,s1.invoice_created_date, r1.region_name , a1.area_name , s1.payment_date, s1.amount " +
                        "from sales_master_table s1 " +
                        "left join dealers_info d1 on s1.dealer_visited = d1.dealer_id " +
                        "left join distributors_info d2 on s1.distributor_visited = d2.distributor_id " +
                        "left join territory_master_table r1 on r1.region_code = s1.region" +
                        "left join area_master_table a1 on a1.area_code = s1.area" +
                        "left join employee_master_table e1 on e1.employee_id=s1.employee_id" +
                        "where " +
                        "s1.employee_id=? and" +
                        "s1.creation_date= DATE_FORMAT(sysdate(), '%Y-%m-%d')- INTERVAL 2 DAY" +
                        "union" +
                        "select e1.employee_name,d1.dealer_name , d2.distributor_name ,  s1.invoice_id ,s1.invoice_created_date, r1.region_name , a1.area_name , s1.payment_date, s1.amount " +
                        "from sales_master_table s1" +
                        "left join dealers_info d1 on s1.dealer_visited = d1.dealer_id" +
                        "left join distributors_info d2 on s1.distributor_visited = d2.distributor_id " +
                        "left join territory_master_table r1 on r1.region_code = s1.region" +
                        "left join area_master_table a1 on a1.area_code = s1.area" +
                        "left join employee_master_table e1 on e1.employee_id=s1.employee_id" +
                        "where" +
                        "s1.creation_date= DATE_FORMAT(sysdate(), '%Y-%m-%d')- INTERVAL 2 DAY" +
                        "and level1_head = (select distinct e2.employee_name from employee_master_table e2 , sales_master_table s2" +
                        "where e2.employee_id=s2.employee_id and s2.employee_id=?)" +
                        "or level2_head = (select distinct e2.employee_name from employee_master_table e2 , sales_master_table s2" +
                        "where e2.employee_id=s2.employee_id and s2.employee_id=?)" +
                        "or level3_head= (select distinct e2.employee_name from employee_master_table e2 , sales_master_table s2" +
                        "where e2.employee_id=s2.employee_id and s2.employee_id=?)";
            }
            else
            {
                sql="(select e1.employee_name,d1.dealer_name , d2.distributor_name ,  s1.invoice_id ,s1.invoice_created_date, r1.region_name , a1.area_name , s1.payment_date, s1.amount " +
                    "from sales_master_table s1 " +
                    "left join dealers_info d1 on s1.dealer_visited = d1.dealer_id " +
                    "left join distributors_info d2 on s1.distributor_visited = d2.distributor_id " +
                    "left join territory_master_table r1 on r1.region_code = s1.region" +
                    "left join area_master_table a1 on a1.area_code = s1.area" +
                    "left join employee_master_table e1 on e1.employee_id=s1.employee_id" +
                    "where " +
                    "s1.employee_id=? and " +
                    "s1.creation_date= DATE_FORMAT(sysdate(), '%Y-%m-%d')-INTERVAL 2 DAY)" +
                    " union" +
                    "(select e1.employee_name,d1.dealer_name , d2.distributor_name ,  s1.invoice_id ,s1.invoice_created_date, r1.region_name , a1.area_name , s1.payment_date, s1.amount " +
                    "from sales_master_table s1" +
                    "left join dealers_info d1 on s1.dealer_visited = d1.dealer_id" +
                    "left join distributors_info d2 on s1.distributor_visited = d2.distributor_id " +
                    "left join territory_master_table r1 on r1.region_code = s1.region" +
                    "left join area_master_table a1 on a1.area_code = s1.area" +
                    "left join employee_master_table e1 on e1.employee_id=s1.employee_id" +
                    "where " +
                    "s1.creation_date= DATE_FORMAT(sysdate(), '%Y-%m-%d')- INTERVAL 2 DAY" +
                    "and level1_head = (select distinct e2.employee_name from employee_master_table e2 , sales_master_table s2" +
                    "where e2.employee_id=s2.employee_id and s2.employee_id=?)" +
                    "or level2_head = (select distinct e2.employee_name from employee_master_table e2 , sales_master_table s2" +
                    "where e2.employee_id=s2.employee_id and s2.employee_id=?)" +
                    "or level3_head= (select distinct e2.employee_name from employee_master_table e2 , sales_master_table s2" +
                    "where e2.employee_id=s2.employee_id and s2.employee_id=?))";

            }

                 st=con.prepareStatement(sql);
                 HttpSession session = request.getSession();

                 String userid = (String)session.getAttribute("userid");

                 st.setString(1, userid);
                 st.setString(2, userid);
                 st.setString(3, userid);
                 st.setString(4, userid);
                rs =  st.executeQuery();
                table="<thead>" +
                        "<tr>" +
                        "<td>Dealer</td>" +
                        "<td>Distributor</td>" +
                        "<td>Region</td>" +
                        "<td>Area</td>" +
                        "<td>Invoice Date</td>" +
                        "<td>Payment Date</td>" +
                        "<td>Amount</td>" +
                        "</tr></thead><tbody>";

                while(rs.next())
                {   table+="<tr>";
                    table+="<td>"+rs.getString("Dealer_name")+"</td>";
                    table+="<td>"+rs.getString("distributor_name")+"</td>";
                    table+="<td>"+rs.getString("region_name")+"</td>";
                    table+="<td>"+rs.getString("area_name")+"</td>";
                    table+="<td>"+rs.getString("invoice_created_date")+"</td>";
                    table+="<td>"+rs.getString("payment_date")+"</td>";
                    table+="<td>"+rs.getString("amount")+"</td>";
                    table+="</tr>";
                }
                table+="</tbody>";
                response.getWriter().print(table);
                st.close();
                rs.close();
                close_connection();
            }

            catch(Exception e)
            {
                e.printStackTrace();
            }
        }

    }

Upvotes: 1

Answers (3)

Lokesh Kumar

Reputation: 418

Your sql query is missing spaces. When two string are concatenated then space is not inserted, so your mysql query is not exactly what you wrote because it is missing spaces.

Print Your Sql Query ..you will understand

Upvotes: 1

Andrew

Reputation: 7768

Check you spaces. SQL is strict about it.

Also, I suggest that you learn how to debug your code. The easiest way is to echo your SQL and see how it looks. Then copy echoed SQL, add values if you are preparing your statements and run the query directly on the server (mysqlworkbench or phpmyadmin).

Upvotes: 1

ta.speot.is

Reputation: 27214

"left join employee_master_table e1 on e1.employee_id=s1.employee_id" +
"where" +
"s1.creation_date= DATE_FORMAT(sysdate(), '%Y-%m-%d')- INTERVAL 2 DAY" +

You're not adding spaces between keywords, in many places. The above fragment becomes:

left join employee_master_table e1 on e1.employee_id=s1.employee_idwheres1.creation_date= DATE_FORMAT(sysdate(), '%Y-%m-%d')- INTERVAL 2 DAY
                                      xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Don't build SQL like this. Try to use stored procedures or at least get the queries out of your code and into a resource file.

Upvotes: 2

MySql Exception Syntax error

Answers (3)

Related Questions