Solved

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

Posted on 2004-08-24
13
589 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article explains the steps required to use the default Photos screensaver to display branding/corporate images
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

839 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