Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC Prepared statment setting space with setString

 
aparna chi
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have query like

select * from abc where xyz=' '

When i execute this query using a tool like TOAD it works fine and returns results that it is supposed to return.

But when i try the same thing with jdbc

PrepareStatment pstmt= con.prepareStatement("select * from abc where xyz = ?");
pstmt.setString(1," ");

ResultSet rs = pstmt.executeQuery();

The query does not return any results. Why this happens? Is there any way to set the " " in PreparedStatement?

Thanks
 
horizon star
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes,
There is a setNull method in PrepareStatement.
You can use pstmt.setNull() instead of setString.

Regards
 
aparna chi
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for reply. But i don't want to set null value. I want to set exactly one space. Actually i tried with that to but it did not work.
The difference here is i don't want to set null but a space.

Thanks and regards
Aparna
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
try pStmt.clearParameters() before your pStmt.setString(). this might help.

if it doesn't help then use Statement instead. it will definitely help. Anyhow, I couldn't think of a scenerio when we need to check this.
[ September 22, 2004: Message edited by: adeel ansari ]
 
Santosh Jagtap
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi aparna,
I tried prepared statement it worked absolutely fine.there might be the problem with ure code or there may not be any record in the db matching ure condition.
[ September 22, 2004: Message edited by: Santosh Jagtap ]
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No there must be record because she is gettin the record while querying with toad. and her code is looking fine either. but again check your code because Santosh tried it and got the expected result.
 
aparna chi
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

This is the code i am running. The method getJDBCConnection returns a connection object.

Connection con = DataBaseUtil.getJDBCConnection();
String sqlQ = "SELECT item_code FROM item WHERE description = ?";
PreparedStatement pstmt = con.prepareStatement(sqlQ) ;
pstmt.setObject(1," ");
ResultSet rs = pstmt.executeQuery();
int count = 0;
while(rs.next())
{
System.out.println("in while loop "+rs.getString(1));
++count;
}

And there are records which match the criteria if I execute the query as

SELECT item_code FROM item WHERE description = ' '

in TOAD it works and gives me desired result.

But with the prepared statement code it does not work.

Thanks
 
Santosh Jagtap
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Aparna,
Ure code seems ok.Just for my sake try this.

Connection con = DataBaseUtil.getJDBCConnection();
String sqlQ = "SELECT item_code FROM item WHERE description = ' '";
PreparedStatement pstmt = con.prepareStatement(sqlQ) ;
//pstmt.setObject(1," ");
ResultSet rs = pstmt.executeQuery();
int count = 0;
while(rs.next())
{
System.out.println("in while loop "+rs.getString(1));
++count;
}

just tell me if it works..
 
aparna chi
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have already tried this and this works but with pstmt.setString it does not work. And the problem is that i can not hardcode the string value as it may contain space or some value depending on what the calling client sets.

So i want to know why it does not work with setString? And is there any workaround this.

Thanks
Aparna
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
try to execute your code pointing on differnt database
on different machine.It may narrow your problem.

beacause pstmt.setString(1,"") works file with me .
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
try

pStmt.clearParameters();

before

pStmt.setString(1, " ");


if doesn't work then as we know prepared satement are precompiled. so may be it would get changed by the compiler.

I couldn't figure out why one want to set a space there. isn't it rubbish.
 
Santosh Jagtap
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Aparna,
See setString also works fine .I dnt kno what is wrong with your code or data.But see if you are taking where condition from client then store that value in some variable and build the query dynamically like this
"select * from <table name> where <column name> = '"+<variable name>+"'";

I hope this will work...
 
Laird Chris
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just to bring this one up again...

I've encountered the same problem now as well.


doesn't produce any results, although there should be one.
Changing the SQL to

works fine.

We are migrating our applications from an Informix system to Oracle. And this code was working before, but it doesn't work with Oracle any more.

If I am using the Oracle provided JDBC-drivers, I have this problem, but if I change the driver to a commercially available JDBC driver for Oracle, it works as it's been with the Informix DB, so it seems it's a problem of the Oracle driver.

Is there any solution without manually changing the statements?
These are JBuilder created EntityBeans, and I don't like the thought of changing this code, as there are many EJBs we've created :-)
[ November 23, 2004: Message edited by: Christian Mattes ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic