Java

SqlException

•

Join Date: Dec 2009

Posts: 59

Reputation: JBeginer7891 is an unknown quantity at this point

Solved Threads: 0

JBeginer7891 JBeginer7891 is offline

Offline

Junior Poster in Training

SqlException

Dec 17th, 2009

Someone please help!
I'm writing a program that enable a user to search for a patient Info when patient number is given.
here is the search code snippet

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
 
	public ArrayList searchPatient(String patientNum)
 
	{
		try	{
String sql = "SELECT fname, sname, location, dob, dor, race, gender, status, initials, idnum FROM PatientTable WHERE patientNo = " + patientNum;
 
			// Create a prepared statement
 			Statement s = con.createStatement();
 
            String pno = "";
	    String firstname="";
	    String lastname="";
	    String locate = "";
	    String dOB="";
	    String dOR="";
	    String Race="";
	    String Gen="";
	    String Stat="";
	    String Initial="";
            double id;
 
                        ResultSet rs = s.executeQuery(sql);
 
		while(rs.next())
	{
                    pno = rs.getString(1);
		    firstname = rs.getString(2);
		    lastname = rs.getString(3);
		    locate=rs.getString(4);
		    dOB = rs.getString(5);
		    dOR = rs.getString(6);
		    Race=rs.getString(7);
		    Gen=rs.getString(8);
		    Stat=rs.getString(9);
		    Initial=rs.getString(10);
		    id = rs.getDouble(11);
 
				//Create a PatientInfo object
PatientInfo patient = new PatientInfo(id, pno, firstname, lastname, locate,dOB, dOR, Race, Gen, Stat, Initial);
 
				//Add the patient object to array list
				patientList.add(patient);
			}
		}
	public ArrayList searchPatient(String patientNum)

	{
		try	{
String sql = "SELECT fname, sname, location, dob, dor, race, gender, status, initials, idnum FROM PatientTable WHERE patientNo = " + patientNum;

			// Create a prepared statement
 			Statement s = con.createStatement();
			
            String pno = "";
	    String firstname="";
	    String lastname="";
	    String locate = "";
	    String dOB="";
	    String dOR="";
	    String Race="";
	    String Gen="";
	    String Stat="";
	    String Initial="";
            double id;

                        ResultSet rs = s.executeQuery(sql);

		while(rs.next())
	{
                    pno = rs.getString(1);
		    firstname = rs.getString(2);
		    lastname = rs.getString(3);
		    locate=rs.getString(4);
		    dOB = rs.getString(5);
		    dOR = rs.getString(6);
		    Race=rs.getString(7);
		    Gen=rs.getString(8);
		    Stat=rs.getString(9);
		    Initial=rs.getString(10);
		    id = rs.getDouble(11);
				
				//Create a PatientInfo object
PatientInfo patient = new PatientInfo(id, pno, firstname, lastname, locate,dOB, dOR, Race, Gen, Stat, Initial);

				//Add the patient object to array list
				patientList.add(patient);
			}
		}

don't get it the Sql statement looks fine but when I run it gives me this exception:

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Thamks for your support!

Last edited by masijade; Dec 17th, 2009 at 7:35 am. Reason: fixed code tags

•

Join Date: Jun 2006

Posts: 7,761

Reputation: ~s.o.s~ has much to be proud of

Solved Threads: 491

~s.o.s~

Offline

Failure as a human

Dec 17th, 2009

Seems to be a problem with the way your query is created; what's the JDBC type of `patientNo'? Is it a VARCHAR? If yes, then you need to wrap the passed in patient number in single quotes when constructing the query. If you don't, your database engine considers the passed in patient number as some kind of identifier or parameter and hence the given error.

BTW, your code is vulnerable to SQL Injection. Try passing in "xxx' or 1=1--" as patient number and watch all the rows being fetched instead of the one you requested. Use PreparedStatement instead of normal statements to save yourself from the trouble of escaping and quoting your input as well as SQL Injection.

I don't accept change; I don't deserve to live.

Sacrifice is a painful, pure and beautiful thing.

Dammit, Jones, What the Hell Are Knoll Pointers?!