CODEWITHSUNDEEP
javadatabasenetbeanssyntax-errorderby
Sandeep Sharma
Sandeep Sharma

Reputation: 11

java.sql.sqlsyntaxerrorexception:comparison between numeric and char(ucs_basic) are not supported

i have faced a problem related to database.Actually when i update the record it show exception "java.sql.sqlsyntaxerrorexception:comparison between numeric and char(ucs_basic) are not supported" here is my coded :

    conn = JavaConnectD.Connerdb();

    int p = JOptionPane.showConfirmDialog(null, "Do you really want to update this record", "UPDATE", JOptionPane.YES_NO_OPTION);
    if(p==0){
    try{

        String sql="UPDATE SANDEEP.STUDENTRECORD SET REGTYPE='"+reg_type.getSelectedItem().toString()+"',SESSION='"+combo_session.getSelectedItem().toString()+"',STUDENTID='"+tf_sid.getText()+"',NAME='"+tf_name.getText()+"',"
                + "CLASS='"+combo_class.getSelectedItem().toString()+"',FNAME='"+tf_fname.getText()+"',MNAME='"+tf_mname.getText()+"',DOB='"+(((JTextField)txt_Date.getDateEditor().getUiComponent()).getText())+"',ADDRESS='"+tf_adrss.getText()+"',MOBILE='"+tf_mobile.getText()+"',PHONE='"+tf_phone.getText()+"',REGFEE='"+tf_reg.getText()+"',ADDMISSION='"+tf_addmision.getText()+"' where STUDENTID='"+tf_sid.getText()+"' ";
        pst=conn.prepareStatement(sql);
        pst.executeUpdate();

        JOptionPane.showMessageDialog(null, "Record Updated");
        tf_sid.setText("");
        tf_name.setText("");
        tf_fname.setText("");
        tf_mname.setText("");
        tf_adrss.setText("");
        tf_mobile.setText("");
        tf_phone.setText("");
        tf_reg.setText("");
        tf_addmision.setText("");

    }catch(Exception e){
        JOptionPane.showMessageDialog(null, e);
    }
    }

i am using netbeans 7.1 and derby embedded database

please tell me where is am writing wrong. if you need any other information please feel free to ask.

Thank you

Upvotes: 1

Views: 347

Answers (1)

Bryan Pendleton
Bryan Pendleton

Reputation: 16359

You should print out the value of your sql variable right after you initialize it, and just before you have called the prepareStatement method on it.

You will see it looks something like UPDATE SANDEEP.STUDENTRECORD SET REGTYPE='v1',SESSION='v2',STUDENTID='v3',NAME='v4',CLASS='v5',FNAME='v6',MNAME='v7',DOB='v8',ADDRESS='v9',MOBILE='v10',PHONE='v11',REGFEE='v12',ADDMISSION='v13' where STUDENTID='v14'

This is an awkward way to construct a SQL statement for several reasons.

As other commenters have noted, it is insecure, because an attacker can supply a value which contains a single quotation mark in the value, which throws the entire statement off and makes it do something totally different (this is called "SQL Injection Attack".

Also, and more directly related to your question, you are trying to specify every single value for your UPDATE as a literal string, but it seems that some of the data types of the columns in your table are not string data types.

Probably, some of your columns are of type INTEGER or of type DATE, etc. For these columns, you don't want to supply string values, you want to supply numeric values, or date values, etc.

The proper way to use java.sql.PreparedStatement for this work is to use parameter marker placeholders in your statement, in place of each of those values, and use the appropriate setInt, setString, setDate etc. methods to supply the actual values, after you have prepared the statement and before you call executeUpdate.

See this document for a thorough tutorial: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Upvotes: 1

Related Questions