PreparedStatement - ExecuteUpdate - Delete - Oracle 10g
I'm using ojdbc14.jar from Oracle 10g, JDK 1.4.2_13.
When I try and run the following code I do not get any exceptions, but no data is deleted:
Bad Code
PreparedStatement deleteStatement = null;
try
{
String sql = "DELETE FROM myTable WHERE StrVal1 = ? "
+ " AND StrVal2 = ? AND StrVal3 = ? AND StrVal4 = ? ";
deleteStatement = connection1.prepareStatement(sql);
deleteStatement.setString(1, value1Str);
deleteStatement.setString(2, value2Str);
deleteStatement.setString(3, value3Str);
deleteStatement.setString(4, value4Str);
int numRowsDeleted = deleteStatement.executeUpdate();
}
....
When this is run, numRowsDeleted is 0.
Good Code 1
If the code is changed to a simple java.sql.Statement so it looks like:
String sql = "DELETE FROM myTable WHERE StrVal1 = '" + value1Str + "'" +
" AND StrVal2 = '" + value2Str + "'" +
" AND StrVal3 = '" + value3Str + "'" +
" AND StrVal4 = '" + value4Str + "'";
int numRowsDeleted = deleteStatement.executeUpdate(sql);
Then I get 1 row deleted.
Good Code 2
I found a partial solution -- eliminate the 4th value (very static) and the code looks like:
PreparedStatement deleteStatement = null;
try
{
String sql = "DELETE FROM myTable WHERE StrVal1 = ? "
+ " AND StrVal2 = ? AND StrVal3 = ? ";
if (StrVal4.equals("FOOBAR"))
{
sql+= "AND StrVal4 = 'FOOBAR'";
}
deleteStatement = connection1.prepareStatement(sql);
deleteStatement.setString(1, value1Str);
deleteStatement.setString(2, value2Str);
deleteStatement.setString(3, value3Str);
int numRowsDeleted = deleteStatement.executeUpdate();
}
This also runs successfully and deletes one row.
Help :-)
I'm a little worried since I have 5 other tables where I have similar PreparedStatements where > 3 values are "set" -- they seem to run fine in my current tests. So why does this one example not work right?
Thanks in advance for any/all help.
Brian
# 1
There is nothing wrong with the first example, and it is, in fact, the way it should be done. It would probably help to see the real code, but if no error is being produced, then the statement is probably running correctly, but the values are not what you think they are. Try printing out the four values before plugging them into the PreparedStatement, and then see if they really are what you expected them to be.
# 2
try this:
PreparedStatement deleteStatement = null;
try
{
String sql = "DELETE FROM myTable WHERE StrVal1 = ? "
+ " AND StrVal2 = ? AND StrVal3 = ? AND StrVal4 = ? ";
connection1.setAutoCommit(false);
deleteStatement = connection1.prepareStatement(sql);
deleteStatement.setString(1, value1Str);
deleteStatement.setString(2, value2Str);
deleteStatement.setString(3, value3Str);
deleteStatement.setString(4, value4Str);
int numRowsDeleted = deleteStatement.executeUpdate();
connection1.commit();
connection1.setAutoCommit(true);
}
# 3
java_2006: can you explain your solution in detail? I don't see why this should solve the problem.Anyway, if I compare bad code with good code #2, I agree with masijade. Most probably value4 doesn't contain that value what you expected. Print them all to console and compare it.
# 4
By any chance are any of your string values null ?
There is a difference between "= null" and "is null".
Only last night I had to create a messy workaound for this problem.
In your case, that would be something like
PreparedStatement deleteStatement = null;
StringBuffer sql = new StringBuffer("DELETE FROM myTable WHERE ");
if (value1Str == null )
sql.append("StrVal1 is null");
else
sql.append("StrVal1 = ?");
if (value2Str == null )
sql.append(" AND StrVal2 is null");
else
sql.append(" AND StrVal2 =?");
if (value3Str == null )
sql.append(" AND StrVal3 is null");
else
sql.append(" AND StrVal3 =?");
if (value4Str == null )
sql.append(" AND StrVal4 is null");
else
sql.append(" AND StrVal4 =?");
deleteStatement = connection1.prepareStatement(sql.toString());
int questionMarkIndex = 1;
if (value1Str != null )
{
deleteStatement.setString(questionMarkIndex, value1Str);
questionMarkIndex++;
}
if (value2Str != null )
{
deleteStatement.setString(questionMarkIndex, value2Str);
questionMarkIndex++;
}
if (value3Str != null )
{
deleteStatement.setString(questionMarkIndex, value3Str);
questionMarkIndex++;
}
if (value4Str != null )
{
deleteStatement.setString(questionMarkIndex, value4Str);
questionMarkIndex++;
}
int numRowsDeleted = deleteStatement.executeUpdate();
}
regards,
Owen
# 5
>> There is a difference between "= null" and "is null".
I think you mean there's a difference between = 'null' and = null
= null and is null should be synonymous.
I agree this might be the problem but your code seems a little over complicated. An easier way would be to test for null and substitute with 'null' ie "where nvl(x,'null') = ?
The cleaner approach would be not to insert the 'null' value in the first place.
# 6
> I think you mean there's a difference between = 'null' and = null
Nope. Here's a real example against my DB. ( a test Hypersonic db )
The following example returns nothing, but executes without warnings.
select * from cases where updated_by = null
This example returns the proper resultset.
select * from cases where updated_by is null
Yes, my example is overly complicated, but for me I needed maximum database independance. ie. runnable on Oracle, DB2, Hypersonic, MySQL, Teradata & SQL Server.
regards,
Owen
# 7
Of course. Sorry.where a = null will fail when a is null as null = null is unknown.
# 8
The example I gave is the real code, I just changed the names of the variables to be generic.
In a test, before I made this post, I used an extension to the PreparedStatement class that allowed me to have a new method I could call at the end, like a "toSQLString" that showed what the whole string looked like after the substitutions.
The string was perfect, I could copy/paste it into PL/SQL Developer tool and run the SQL code and it deleted the row. But as a standard PreparedStatement it doesn't work right.
# 9
java_2006: I tried the changes for AutoCommit you suggested no luck.
# 10
All,
Thanks for the suggestions. So far nothing has worked.
I did make it even simpler by shrinking the code down to the following:
try
{
String sql = "DELETE FROM myTable WHERE StrVal1 = 123 "
+ " AND StrVal2 = 'XY' AND StrVal3 = '12345ABCDEF' AND StrVal4 = ? ";
deleteStatement = connection1.prepareStatement(sql);
deleteStatement.setString(1, value4Str);
int numRowsDeleted = deleteStatement.executeUpdate();
}
....
When this is run, numRowsDeleted is 0.
It still fails. I even tried:
String sql = "DELETE FROM myTable WHERE StrVal4 = ? "
+ " AND StrVal1 = '123' AND StrVal2 = 'XY' AND StrVal3 = '12345ABCDEF' ";
With no luck. In the database the fields are defined as:
field1: CHAR 3
field2: CHAR 5
field3: CHAR 32
field4: CHAR 30
Of course making StrVal4 = 'XYZ' works. I can change the other 3 back to "?" and it works. For some reason, field 4 doesn't work. The last field in the table is a DATE field -- but that shouldn't matter. It is only used for a timestamp when a new record is inserted.
Thanks again - Brian.
# 11
SOLUTION:
I had posted the same question on the Oracle forum too. Here is the solution:
=======================
Hi. I'll guess that the column whose parameter you set by hand to make
it work is defined as a CHAR(XX), rather than a VARCHAR2(XX). If you
send hard-coded strings in the SQL, the DBMS will parse them and do
the blank-padding implicitly to compare the hard-coded values, but if
you use a parameter marker, the query plan is generated, and then
when the driver sends the actual parameter value, it will only be compared
directly, so if it's not the same length as the column, the match will
fail. So the idea solution is to not use CHAR(XX). If you can't change the
DLL, change your JDBC code to blank-pad the parameter value to the
column length.
Joe Weinstein at BEA Systems
# 12
> Of course making StrVal4 = 'XYZ' works. I can change
> the other 3 back to "?" and it works. For some
> reason, field 4 doesn't work. The last field in the
> table is a DATE field -- but that shouldn't matter.
> It is only used for a timestamp when a new record is
> inserted.
>
> Thanks again - Brian.
If the last field is a Date field, you should have been using setDate instead of setString and passing it a Date object.
Edit: Nevermind. You said an additional field was a Date field.
