Solved

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

Posted on 2004-08-24
13
592 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

The conference as a whole was very interesting, although if one has to make a choice between this one and some others, you may want to check out the others.  This conference is aimed mainly at government agencies.  So it addresses the various compli…
After seeing many questions for JRNL_WRAP_ERROR for replication failure, I thought it would be useful to write this article.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

691 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