Problems a query/update need help

[ddmeightball]

I am running into an error when I try to execute my sql code when I try to find a Section by the CourseNumber and the SectionNumber. It says I have a datatype mismatch in my SQL criteria.

Problems with query/update : [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

Here is my SQL code that I am trying to use to get information from an access database.

String sql = "SELECT * FROM SECTIONS WHERE course_number= '" + s.getCourseNum()+"' AND section_number=" + s.getSectionNum();

Here is the method that I wrote.

	public Section findSectionByCourNumSectNum(Section s) throws SectionException
	{
		String sql = "SELECT * FROM SECTIONS WHERE course_number= '" + s.getCourseNum()+"' AND section_number=" + s.getSectionNum();
		System.out.println(sql);
		Section foundSection = new Section();
		try
		{
			System.out.println("Before creating statement object.");
		    Statement stmt = conn.createStatement();
		    
		    System.out.println("Before creating ResultSet using the stmt to execute the SQL code.");
		    ResultSet rs = stmt.executeQuery(sql); 
		    System.out.println("After creating ResultSet using the stmt to execute the SQL code.");
		    
		    if(rs.next())
		    {
		    	
		    	foundSection.setDatabaseID(rs.getInt(1));
		    	System.out.println("Found Section DatabaseID: "+ foundSection.getDatabaseID() );
		    	
		    	foundSection.setCourseNum(rs.getString(2));
		    	System.out.println("Found Section CourseNum: " + foundSection.getCourseNum());
		    	
		    	foundSection.setSectionNum(rs.getInt(3));
		    	System.out.println("Found Section SectionNum: " + foundSection.getSectionNum());
		    	
		    	foundSection.setInstructorID(rs.getString(4));
		    	System.out.println("Found Section InstructorID: " + foundSection.getInstrutorID() );
		    	
		    	foundSection.setDays(rs.getString(5));
		    	System.out.println("Found Section Days: " + foundSection.getDays());

				foundSection.setBeginTime(rs.getInt(6));
		    	System.out.println("Found Section BeginTime: " + foundSection.getBeginTime() );
		    	
		    	foundSection.setEndTime(rs.getInt(7));
		    	System.out.println("Found Section EndTime: " + foundSection.getEndTime() );

				Calendar cal = new GregorianCalendar();
				cal.setTimeInMillis(rs.getLong(8));
				foundSection.setStartDate(cal);
		    	System.out.println("Found Section StartDate: " + foundSection.getStartDate().getTime());
		    	
		    	cal.clear();
		    	cal.setTimeInMillis(rs.getLong(9));
				foundSection.setEndDate(cal);
		    	System.out.println("Found Section EndDate: " + foundSection.getEndDate().getTime());

		    	foundSection.setRoomNum(rs.getString(10));
		    	System.out.println("Found Section Room Number: " + foundSection.getRoomNum());
		    	
   			    return foundSection;
   			}
		    else
		    {
		    	return null;
		    }
		    
		}//end try
		catch(SQLException sqe)
		{
		  throw new SectionException(sqe.getMessage());
		}//end catch
	}

I have been looking at this for about an hour and just cant see what I am doing wrong. My code is quitting when I try to execute my SQL Code which leads me to believe that I have a problem with my code. Any suggestions... If you need to see the full program or where I am calling the method that I wrote, post and I will zip it or post it and let you see it.

[si_the_geek]

The error is in your SQL statement - you are trying to compare two different data types (eg: a String against a numeric field).

What data types are your course_number and section_number fields?

For String/Text fields, you need single quotes around the value (as you have for course_number), and for numeric data types you dont (as you have for section_number).

[ddmeightball]

Course_Num is a text field. Section_Num I believe is an int. I dont have access to check though. I will post the database in a zip file here so if anyone has it they can check.

The way that it should work is that I send a section object to the method than use the coursenumber and the sectionnumber of that object to find the rest of the information for that section.

A course number looks like this

INFO1311

And a section number is just how many sections of that course are currently there.

01, 02, 03 etc

[ddmeightball]

I got it to work, but I just wish I could figure out what was wrong with my first SQL statement. I went ahead and used a Prepared statement and just set each of the variables that way.

I am getting another error though in my update method, but I will try to troubleshoot. I will try to use another prepared statement. I dont have alot of experience with them, but I do like their uses though. Do you have any websites that show in depth how to use them. I have googled it and found a few but most werent the indepth that I like. I have read about them in the docs though.


Here was the code that worked.

String sql = "SELECT * FROM SECTIONS WHERE course_number=? AND section_number=?";
ResultSet rs = null; 

PreparedStatement statement = conn.prepareStatement(sql);
			
statement.setString(1,s.getCourseNum());
statement.setInt(2,s.getSectionNum());
rs = statement.executeQuery();

[si_the_geek]

I just looked at the table in Design view - and both of those fields are Text.

This means that for a "normal" SQL statement you should be using single quotes for both fields, and for your prepared statement you should be using setString for both.

Prepared statements have a few advantages (such as no problems with quotes inside data), and are a good way to go. I'm afraid I dont have any articles I can link to that explain them in detail tho.. hopefully one of the Java guys will know something useful and post it!

[CornedBee]

There's nothing in-detail to know about prepared queries. They contain simple unnamed positional parameters that you replace with your own values, without caring for escaping.
Also, some database systems can optimize the query when you prepare it, so you can execute it multiple times with different parameters, which will be faster than repeatedly executing a new query.

[ddmeightball]

There's nothing in-detail to know about prepared queries. They contain simple unnamed positional parameters that you replace with your own values, without caring for escaping.
Also, some database systems can optimize the query when you prepare it, so you can execute it multiple times with different parameters, which will be faster than repeatedly executing a new query.

The book that I am reading doesnt mention anything of great detail about prepared statements. It just uses Statement objects so thats why I wanted something a little more indepth. Can you guys recommend any books that may be of help?

[CornedBee]

Perhaps a book specializing on JDBC? As I said, there's nothing special about prepared statements. You couldn't fill more than 15 pages with it, and that's if you supply a lot of code examples.
Here's the Sun online tutorial about it:
http://java.sun.com/docs/books/tutor.../prepared.html
In my opinion, though, they focus too much on the repeated execution feature and too little on the parameter feature. If people would always use parameters for everything, the problem known as SQL injection wouldn't exist!

Here's some Amazon links that you might want to check out. I don't know any of these books, I just searched for java and database.
http://www.amazon.com/gp/product/059...095936?ie=UTF8
http://www.amazon.com/gp/product/156...095936?ie=UTF8
http://www.amazon.com/gp/product/076...095936?ie=UTF8
http://www.amazon.com/gp/product/076...095936?ie=UTF8

[ddmeightball]

I just looked at the table in Design view - and both of those fields are Text.

This means that for a "normal" SQL statement you should be using single quotes for both fields, and for your prepared statement you should be using setString for both.

I see now that they are both text, but my question is why did it work then, since I am setting an int and not a string for section_num. Does it do some sort of auto cast in the background?

[si_the_geek]

That's right.. unfortunately!

It's a bad thing, as it means your code will appear to work until you use a value that isn't valid (with non numerics in, or just a blank), at which point you will get an error in your code where you didnt before.