prepared statement update doesn't work

[Mary Orcutt]

I'm using Oracle 8 with the thin Oracle driver 8.0.4.0.5 in a Java
application.
I create a prepared statement to insert a row, then create a prepared
statement
to update the same row. The insert works, but the update does not, with
no
exceptions thrown. Is there a problem using prepared statements with
Oracle's
thin driver?

Thanks,
Mary

String query = new String("insert into " + tableName +
" values(?,?,?,?,?,?,?,?,?,?)");
PreparedStatement pstmt = db_conn.prepareStatement(query);
/* set values here */
pstmt.executeUpdate();

String query = new String("update " + tableName +
" set order = ?, status = ?" +
" where login = ? and key = ?");
PreparedStatement pstmt = db_conn.prepareStatement(query);
/* set values here */
pstmt.executeUpdate();

[Thomas Kyte]

A copy of this was sent to Mary Orcutt <ma...@globeset.com>
(if that email address didn't require changing)

Using same JDBC and database as above, I code:

import java.sql.*;

class UpdateTest {
public static void main (String args []) throws SQLException {

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

Connection conn =
DriverManager.getConnection ("jdbc:oracle:thin:@slackdog:1521:oracle8",
"scott", "tiger");

PreparedStatement ps = conn.prepareStatement(
"insert into test ( name, data ) values ( ?, ? )" );

ps.setString( 1, "test" );
ps.setString( 2, "testing" );

int rows1 = ps.executeUpdate();
ps.close();

ps = conn.prepareStatement(
"update test set data = ? where name = ?" );

ps.setString( 1, "TESTING" );
ps.setString( 2, "test" );

int rows2 = ps.executeUpdate();
ps.close();

System.out.println ( rows1 + " rows inserted, " + rows2 + " updated" );
}
}

Then, in sqlplus I:

SQL> create table test ( name varchar2(25), data varchar2(25) );
Table created.

and run:

$ javac UpdateTest.java
$ java UpdateTest
1 rows inserted, 1 updated

showing me it inserted 1 record and updated it. Then, in sqlplus I see:

$ sqlplus scott/tiger

SQL*Plus: Release 8.0.4.0.0 - Production on Wed Mar 10 15:10:7 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.0.0 - Production

SQL> select * from test;

NAME DATA
------------------------------ -------------------------
test TESTING

showing me that I did the update in fact.... (testing is TESTING, only will be
that after the update)

Can you try this example in your system? Also, print out the rows affected from
the insert/update. Perhaps its in your ps.set*() calls? maybe they are out of
order (so you are binding login to key and key to login or something...)

Thomas Kyte
tk...@us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

[Mary Orcutt]

Thanks to Thomas for help with this. Turns out the column I was using in
the where clause was CHAR(30) and I was only giving it the string I was
searching for, which was much less than 30 characters, so it never matched
any of the rows in the table.

I had to change the where clause to be "where login = rpad(?,30)" in order
to pad the string I was using in setString with blanks at the end. Worked
fine.
I won't have to do this when I convert this column to VARCHAR.

Mary

[ghu...@my-dejanews.com]

THANKS!

Until I saw this I was using rtrim() on the left side of the equal sign,
which was causing certain retrievals to be blindingly slow. Rpad()
definitely improved the performance of these calls!

-George Hughen

In article <36E975BB...@globeset.com>,

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own