Solved

JDBC: JSP; Failed to execute a conditional database query! Kindly instruct.

Posted on 2004-08-24
13
588 Views
Last Modified: 2008-02-01
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.


0
Comment
Question by:Rrugemalira
  • 7
  • 4
  • 2
13 Comments
 
LVL 35

Accepted Solution

by:
TimYates earned 250 total points
ID: 11886935
Try:

        String sql = "SELECT middlename, patient_number, " +
                         "num_dependants, marital_status, gender, occupation " +
                          "FROM patient WHERE firstname = ? AND surname = ? AND " +
                          "date_birth = ?";
         
0
 
LVL 35

Assisted Solution

by:TimYates
TimYates earned 250 total points
ID: 11886965
you might want to change

        ps.setString(3,bdate);

to

        SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" ) ;
        ps.setDate( 3, sdf.parse( bdate ) ) ;

(assuming bdate is in dd/MM/yyyy format)

As this will save you any problems you have with having to convert the date to the same format as the db :-)

Tim
0
 

Author Comment

by:Rrugemalira
ID: 11888063
With:

       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.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 11

Expert Comment

by:sompol_kiatkamolchai
ID: 11888534
0
 

Author Comment

by:Rrugemalira
ID: 11889175
Thank you for pointing me to the sites:

With this code:
          System.out.println("firstname = " + fname);
        System.out.println("surname = " + lname);
        System.out.println("date_birth = " + bdate);
         
          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!!

0
 
LVL 11

Expert Comment

by:sompol_kiatkamolchai
ID: 11889205
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 + "'";
0
 
LVL 11

Expert Comment

by:sompol_kiatkamolchai
ID: 11889209
You need to surround variable with ' to let the sql engine know that it is a constant value not a field in table.
0
 
LVL 11

Expert Comment

by:sompol_kiatkamolchai
ID: 11889224
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();

0
 
LVL 11

Assisted Solution

by:sompol_kiatkamolchai
sompol_kiatkamolchai earned 250 total points
ID: 11889236
STOP..... I found your cause of problem.

I see in your question that you use

          result = ps.executeQuery(sql);

So that mean you pass query to PreparedStatement and it will execute by Statement Interface instead, which is not correct
You just change to


         result = ps.executeQuery();

Everything should be fine.
0
 
LVL 11

Expert Comment

by:sompol_kiatkamolchai
ID: 11889289
Any question, please feel free to ask me.
0
 
LVL 35

Expert Comment

by:TimYates
ID: 11889856
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
0
 
LVL 11

Expert Comment

by:sompol_kiatkamolchai
ID: 11889878
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.
0
 
LVL 35

Expert Comment

by:TimYates
ID: 11889883
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...
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Examines three attack vectors, specifically, the different types of malware used in malicious attacks, web application attacks, and finally, network based attacks.  Concludes by examining the means of securing and protecting critical systems and inf…
An article on effective troubleshooting
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now