ive done something stupid!

[lhtec]

Hi and greetings!

Im trying to learn a bit of SQl through jdbc. at present i have a little problem concerning constructing a SELECT statement including a variable. salary which is declared as an integer.

when i use this statement

String thisQuery = "SELECT* FROM EmployeeTable WHERE EmployeeSalary > 25000";

everything works well and i get the expected output. However when i use this statement: -

String thisQuery = "SELECT* FROM EmployeeTable WHERE EmployeeSalary > '"+salary+"'";

i get a data type mismatch error namely

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

Clearly im doing something totally wrong building my string but i cant see for the life of me what is is.

Could some kind soul please kick me in theright direction

ta
John

[Davey]

You seem to have unnecessary quotes arond your salary variable:

String thisQuery = "SELECT* FROM EmployeeTable WHERE EmployeeSalary > '"+salary+"'";

This should probably read:

String thisQuery = "SELECT* FROM EmployeeTable WHERE EmployeeSalary > "+salary;

[lhtec]

You seem to have unnecessary quotes arond your salary variable:



This should probably read:

String thisQuery = "SELECT* FROM EmployeeTable WHERE EmployeeSalary > "+salary;

Thanks davey, apparently java doesnt like that sort of thing and you have to deliniate the variable with the single quotes, concatenating a string doesnt do it.

i ahve actually solved the problem using a prepared statement but if there is a way of resolving the problem using a simple statement id still like to know.

John

[Davey]

Sorry, you're right you can't add ints onto strings. I should have said you need to do something like:

	String query = "select * from ... where EmployeeSalary>";
	int salary = 100;
	query += new Integer(salary).toString();

Although this will work, you are better off using a prepared statement if you can.

[cjard]

Sorry, you're right you can't add ints onto strings. I should have said you need to do something like:

of course you can:

String sql = "SELECT * FROM Table WHERE field > " + 100;

System.out.println("In loop number " + loopCounter);



the error was caused by the OP putting quotes around the value:

String sql = "SELECT * FROM Table WHERE field > ' " + 100 + " ' ";
--> "SELECT * FROM Table WHERE field > ' 100 ' ";


access cant compare an integer column to be greater than a string value without converting one or the other, and you wouldnt want it to do that itself; you should jsut present a nice sql to it

If you have sql problems in future, you should print out the sql immediately before you send it to the database:
System.out.println(sql);

you can then look at it ans sense check it, or run it in Access itself to see if it works..

note though that some syntax differences exist; access uses * for wildcards, but JDBC/ODBC uses %.

ACCESS FORMAT: SELECT * FROM table WHERE field LIKE 'abc*'
ODBC/JAVA FORM: SELECT * FROM table WHERE field LIKE 'abc%'

Although this will work, you are better off using a prepared statement if you can.

agree. they are faster in repeated use and immune to sql injection hacking