Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement problem

 
Md Fizal
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"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
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use strSQL = "SELECT count(*) FROM TABLE1 WHERE personnumber = ?";
psmt1 = conn.prepareStatement(strSQL);
psmt1.setString("value");
resultSet = psmt1.executeQuery();
Thanks
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Amol Desai
Ranch Hand
Posts: 82
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
PreparedStatement setString method takes two arguments.

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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic