aspose file tools*
The moose likes JDBC and the fly likes JDBC Prepared statment setting space with setString Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Prepared statment setting space with setString" Watch "JDBC Prepared statment setting space with setString" New topic
Author

JDBC Prepared statment setting space with setString

aparna chi
Greenhorn

Joined: May 19, 2004
Posts: 16
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

Joined: Sep 21, 2004
Posts: 2
Yes,
There is a setNull method in PrepareStatement.
You can use pstmt.setNull() instead of setString.

Regards
aparna chi
Greenhorn

Joined: May 19, 2004
Posts: 16
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

Joined: Aug 15, 2004
Posts: 2874
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

Joined: Sep 20, 2004
Posts: 23
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 ]

Santy
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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

Joined: May 19, 2004
Posts: 16
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

Joined: Sep 20, 2004
Posts: 23
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

Joined: May 19, 2004
Posts: 16
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

Joined: Aug 13, 2004
Posts: 1081

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 .


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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

Joined: Sep 20, 2004
Posts: 23
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

Joined: Nov 23, 2004
Posts: 1
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 ]
 
 
subject: JDBC Prepared statment setting space with setString