Rrugemalira
asked on
JDBC: JSP; Failed to execute a conditional database query! Kindly instruct.
JDBC: JSP; Failed to execute a conditional database query!
This is the error message:
SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax near '?, surname = ?, date_birth = ?' at line 1"
SQLState: 42000
VendorError: 1064
Here is the culprit code segment:
String sql = "SELECT middlename, patient_number, " +
"num_dependants, marital_status, gender, occupation " +
"FROM patient WHERE firstname = ?, surname = ?, " +
"date_birth = ?";
PreparedStatement ps = conn.prepareStatement(sql)
// Set parameters in the prepared statement
ps.setString(1,fname);
ps.setString(2,lname);
ps.setString(3,bdate);
// Execute the query to get the ResultSet object from the database
ResultSet result = null;
result = ps.executeQuery(sql);
And here is the database table:
mysql> DESCRIBE patient;
+----------------+--------
| Field | Type | Null | Key | Default | Extra |
+----------------+--------
| patient_id | bigint(20) unsigned | | PRI | NULL | auto_increment |
| address_id | bigint(20) unsigned | | PRI | 0 | |
| diagnosis_id | bigint(20) unsigned | | PRI | 0 | |
| sdp_id | bigint(20) unsigned | | PRI | 0 | |
| firstname | varchar(20) | YES | | NULL | |
| middlename | char(1) | YES | | NULL | |
| surname | varchar(20) | YES | | NULL | |
| date_birth | date | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
| occupation | varchar(15) | YES | | NULL | |
| marital_status | varchar(10) | YES | | NULL | |
| num_dependants | int(11) | YES | | NULL | |
| patient_number | int(11) | YES | | NULL | |
+----------------+--------
13 rows in set (0.00 sec)
I do appreciate your help.
This is the error message:
SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax near '?, surname = ?, date_birth = ?' at line 1"
SQLState: 42000
VendorError: 1064
Here is the culprit code segment:
String sql = "SELECT middlename, patient_number, " +
"num_dependants, marital_status, gender, occupation " +
"FROM patient WHERE firstname = ?, surname = ?, " +
"date_birth = ?";
PreparedStatement ps = conn.prepareStatement(sql)
// Set parameters in the prepared statement
ps.setString(1,fname);
ps.setString(2,lname);
ps.setString(3,bdate);
// Execute the query to get the ResultSet object from the database
ResultSet result = null;
result = ps.executeQuery(sql);
And here is the database table:
mysql> DESCRIBE patient;
+----------------+--------
| Field | Type | Null | Key | Default | Extra |
+----------------+--------
| patient_id | bigint(20) unsigned | | PRI | NULL | auto_increment |
| address_id | bigint(20) unsigned | | PRI | 0 | |
| diagnosis_id | bigint(20) unsigned | | PRI | 0 | |
| sdp_id | bigint(20) unsigned | | PRI | 0 | |
| firstname | varchar(20) | YES | | NULL | |
| middlename | char(1) | YES | | NULL | |
| surname | varchar(20) | YES | | NULL | |
| date_birth | date | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
| occupation | varchar(15) | YES | | NULL | |
| marital_status | varchar(10) | YES | | NULL | |
| num_dependants | int(11) | YES | | NULL | |
| patient_number | int(11) | YES | | NULL | |
+----------------+--------
13 rows in set (0.00 sec)
I do appreciate your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For mySQL,
http://dev.mysql.com/doc/mysql/en/
and here for select statment
http://dev.mysql.com/doc/mysql/en/SELECT.html
For JDBC,
http://192.18.97.151/ECom/EComTicketServlet/BEGINsdlcweb2c.sun.com-178f9%3A412bee45%3A486bff2ee5cff746/-2147483648/492775407/1/392066/392042/492775407/2ts+/westCoastFSEND/7076-jdbc-3.0-fr-spec-oth-JSpec/7076-jdbc-3.0-fr-spec-oth-JSpec:1/jdbc-3_0-fr-spec.pdf
http://dev.mysql.com/doc/mysql/en/
and here for select statment
http://dev.mysql.com/doc/mysql/en/SELECT.html
For JDBC,
http://192.18.97.151/ECom/EComTicketServlet/BEGINsdlcweb2c.sun.com-178f9%3A412bee45%3A486bff2ee5cff746/-2147483648/492775407/1/392066/392042/492775407/2ts+/westCoastFSEND/7076-jdbc-3.0-fr-spec-oth-JSpec/7076-jdbc-3.0-fr-spec-oth-JSpec:1/jdbc-3_0-fr-spec.pdf
ASKER
Thank you for pointing me to the sites:
With this code:
System.out.println("firstn
System.out.println("surnam
System.out.println("date_b
String sql = "SELECT middlename, patient_number, " +
"num_dependants, marital_status, gender, occupation " +
"FROM patient WHERE firstname =" + fname +
" AND surname = " + lname +
" AND date_birth = " + bdate;
I get the following output and error message:
firstname = Godwin
surname = Rwakabamba
date_birth = 1966-02-20
SQLException: Column not found, message from server: "Unknown column 'Godwin' in 'where clause'"
SQLState: S0022
VendorError: 1054
Now fname is taken as a column!!
With this code:
System.out.println("firstn
System.out.println("surnam
System.out.println("date_b
String sql = "SELECT middlename, patient_number, " +
"num_dependants, marital_status, gender, occupation " +
"FROM patient WHERE firstname =" + fname +
" AND surname = " + lname +
" AND date_birth = " + bdate;
I get the following output and error message:
firstname = Godwin
surname = Rwakabamba
date_birth = 1966-02-20
SQLException: Column not found, message from server: "Unknown column 'Godwin' in 'where clause'"
SQLState: S0022
VendorError: 1054
Now fname is taken as a column!!
String sql = "SELECT middlename, patient_number, " +
"num_dependants, marital_status, gender, occupation " +
"FROM patient WHERE firstname =" + fname +
" AND surname = " + lname +
" AND date_birth = " + bdate;
to
String sql = "SELECT middlename, patient_number, " +
"num_dependants, marital_status, gender, occupation " +
"FROM patient WHERE firstname =" + "'"+ fname + "'" +
" AND surname = " + "'"+ lname + "'"+
" AND date_birth = " + "'"+ bdate + "'";
"num_dependants, marital_status, gender, occupation " +
"FROM patient WHERE firstname =" + fname +
" AND surname = " + lname +
" AND date_birth = " + bdate;
to
String sql = "SELECT middlename, patient_number, " +
"num_dependants, marital_status, gender, occupation " +
"FROM patient WHERE firstname =" + "'"+ fname + "'" +
" AND surname = " + "'"+ lname + "'"+
" AND date_birth = " + "'"+ bdate + "'";
You need to surround variable with ' to let the sql engine know that it is a constant value not a field in table.
But if it's possible, it's better to use prepare statment
String sql = "SELECT middlename, patient_number, " +
"num_dependants, marital_status, gender, occupation " +
"FROM patient WHERE firstname = ? AND surname = ? AND " +
"date_birth = ?";
PreparedStatement ps = conn.prepareStatement(sql)
// Set parameters in the prepared statement
ps.setString(1,fname);
ps.setString(2,lname);
ps.setString(3,bdate);
// Execute the query to get the ResultSet object from the database
ResultSet result = null;
result = ps.executeQuery();
String sql = "SELECT middlename, patient_number, " +
"num_dependants, marital_status, gender, occupation " +
"FROM patient WHERE firstname = ? AND surname = ? AND " +
"date_birth = ?";
PreparedStatement ps = conn.prepareStatement(sql)
// Set parameters in the prepared statement
ps.setString(1,fname);
ps.setString(2,lname);
ps.setString(3,bdate);
// Execute the query to get the ResultSet object from the database
ResultSet result = null;
result = ps.executeQuery();
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Any question, please feel free to ask me.
Yup...the error was what I pointed out in my first comment, and what sompol has just said about the ps.executeQuery()
Between us we got it :-)
Tim
Between us we got it :-)
Tim
Yes. We both answer the question. You pointed that comma should change to "AND" and I said that no parameter need for executeQuery in PreparedStatement.
Rrugemalira, also like sompol said, MAKE sure you are using
String sql = "SELECT middlename, patient_number, " +
"num_dependants, marital_status, gender, occupation " +
"FROM patient WHERE firstname = ? AND surname = ? AND " +
"date_birth = ?";
and NOT
String sql = "SELECT middlename, patient_number, " +
"num_dependants, marital_status, gender, occupation " +
"FROM patient WHERE firstname =" + fname +
" AND surname = " + lname +
" AND date_birth = " + bdate;
As was suggested for debug...
The second one can be a security risk, and may open your database for external attack (via SQL injection)
Depends on your db and drivers...
String sql = "SELECT middlename, patient_number, " +
"num_dependants, marital_status, gender, occupation " +
"FROM patient WHERE firstname = ? AND surname = ? AND " +
"date_birth = ?";
and NOT
String sql = "SELECT middlename, patient_number, " +
"num_dependants, marital_status, gender, occupation " +
"FROM patient WHERE firstname =" + fname +
" AND surname = " + lname +
" AND date_birth = " + bdate;
As was suggested for debug...
The second one can be a security risk, and may open your database for external attack (via SQL injection)
Depends on your db and drivers...
ASKER
String sql = "SELECT middlename, patient_number, " +
"num_dependants, marital_status, gender, occupation " +
"FROM patient WHERE firstname = ? AND surname = ? AND " +
"date_birth = ?";
The error message becomes:
SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax near '? AND surname = ? AND date_birth = ?' at line 1"
SQLState: 42000
VendorError: 1064
P'se recommend a good book on JDBC and mySQL syntax! I'd appreciate very much.