I have the following code in my program, which does not fetch any records from the database. ----------------------------------- strSQL = "SELECT count(*) FROM TABLE1 WHERE personnumber = ?"; psmt1 = conn.prepareStatement(strSQL); psmt1.setString(1); resultSet = psmt1.executeQuery(); ----------------------------------- whereas the following method works fine and it gives the expected result. ----------------------------------- strSQL = "SELECT count(*) FROM TABLE1 WHERE personnumber = '" + strLoginID + "'"; ; psmt1 = conn.prepareStatement(strSQL); resultSet = psmt1.executeQuery(); ----------------------------------- Same is the case with UPDATE statement query also, but INSERT statement works fine with "?". Could someone help me to figure out the problem?
Md Fizal
Ranch Hand
Joined: Dec 23, 2002
Posts: 61
posted
0
I got a clue. It's cos the data type in the database is CHAR. setString() doesnt seem to work with CHAR types, but with VARCHAR only. Any idea why so?
setString() should work for char types as well. How are you setting strLoginID in the first example? It looks like you are assuming that the personnumber to search for is always one.
"Fiz", The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp. We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please edit your profile and select a new name which meets the requirements. Please change your display name soon. You already have 19 posts and accounts with invalid diaplay names get deleted. Thanks. Dave
sravan reddy
Greenhorn
Joined: Jul 30, 2003
Posts: 6
posted
0
Fiz, try like this.. ----------------------------------- strSQL = "SELECT count(*) FROM TABLE1 WHERE personnumber = ?"; psmt1 = conn.prepareStatement(strSQL); psmt1.setString(1,"'"+strLoginID+"'"); //strLoginID value u will be getting from somw ehre resultSet = psmt1.executeQuery(); i'm sure it will work.. sravan
No it won't, the PreparedStatement adds the single quotes for you (actually it doesn't have to, but for all intent this is taken care of) What you have added is equivalent to
ie the quotes will be escaped and included in the SQL. Dave
R Laksh
Greenhorn
Joined: Aug 27, 2003
Posts: 25
posted
0
Use strSQL = "SELECT count(*) FROM TABLE1 WHERE personnumber = ?"; psmt1 = conn.prepareStatement(strSQL); psmt1.setString("value"); resultSet = psmt1.executeQuery(); Thanks
laks77 Welcome to Javaranch!! Please change your display name to conform to our Naming Policy. You can change your display name here. Thanks and we hope to see you around more often.
parameterindex - The first parameter is 1, second 2 and so on value - String value Hence the following should work : String strLoginId = "Emp1";// strSQL = "SELECT count(*) FROM TABLE1 WHERE personnumber = ?"; psmt1 = conn.prepareStatement(strSQL); psmt1.setString(1,strLoginId); resultSet = psmt1.executeQuery(); -Amol
I agree. Here's the link: http://ej-technologies/jprofiler - if it wasn't for jprofiler, we would need to
run our stuff on 16 servers instead of 3.
0
