ive done something stupid!

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

You seem to have unnecessary quotes arond your salary variable:

Quote:

---

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

---

This should probably read:

Code:

---

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

---

Quote:

---

Originally Posted by Davey

You seem to have unnecessary quotes arond your salary variable:



This should probably read:

Code:

---

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

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

Code:

---

        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.

Quote:

---

Originally Posted by Davey

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%'

Quote:

---

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