?
Solved

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

Posted on 2004-08-24
13
Medium Priority
?
628 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 1000 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 1000 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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 

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 1000 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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

You have missed a phone call. The number looks like it belongs to the bunch of numbers which your company uses. How to find out who has just called you?
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
Suggested Courses
Course of the Month6 days, 5 hours left to enroll

589 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