I have the following java code for inserting values to the database
When i try the code below it works
st.executeUpdate("INSERT INTO USERT " + "VALUES ('1', 'Simpson', 'Mr', 'Springfield', '2001')");
But when i try the code below i get an error
java.sql.SQLSyntaxErrorException: ORA-00911: invalid character
st.executeUpdate("INSERT INTO USERT (`USERID`, `FIRSTNAME`,`LASTNAME`,`EMAIL`,`PHONE`) VALUES ('2', 'james', 'john', 'myemail', 'myphone')");
Most of the answers provided here at stack overflow refer to a character misplacement of a semicolon ;. i.e. link 1 , link 2, link 3 which does not seem to help me
Here is the full code of my class
package dbproject;
import java.sql.*;
public class jdbcconnection {
public static void main(String[] args) {
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
java.sql.Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","data1","mypass");
Statement st=con.createStatement();
st.executeUpdate("INSERT INTO USERT (`USERID`, `FIRSTNAME`,`LASTNAME`,`EMAIL`,`PHONE`) VALUES ('2', 'james', 'john', 'myemail', 'myphone')");
//st.executeUpdate("INSERT INTO USERT " + "VALUES ('1', 'Simpson', 'Mr', 'Springfield', '2001')");
con.close();
}
catch(Exception e){
System.out.println(e);
}
}
}
What could be wrong with my execute query and what else could lead to the error?
解决方案
MySQL and Oracle have some minor differences in their definition of an identifier. In MySQL, an unquoted identifier may begin with a digit, and double quotation marks are allowed in a quoted identifier; however, neither of these is allowed in an Oracle identifier. In MySQL, the quote character is the backtick (`). If the SQL mode ANSI_QUOTES is set, double quotes can also be used to quote the identifiers. In Oracle, identifiers are quoted using double quotation marks.
try;
st.executeUpdate("INSERT INTO USERT (USERID, FIRSTNAME,LASTNAME,EMAIL,PHONE) VALUES ('2', 'james', 'john', 'myemail', 'myphone')");