?
Solved

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

Posted on 2004-08-24
13
Medium Priority
?
602 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

771 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