Re: JDBC: Problem with execution of SQL Statement

On Tue, 20 Jun 2006 01:16:44 +0800, Sameer wrote
(in article <1150737404.228258.124070@p79g2000cwp.googlegroups.com>):


oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1


don't even dream of doing it that way!!
use bind variables, otherwise your code will fall apart for at least the
following reasons:

1. miss nested " or '
2. sql injection attacks.
3. confusion on insert position

code it this way: ( and I only use this for debugging my sql code, then later
convert to stored procedures)

 "begin insert into photo_store(id,section_id, name, description)
values(graphics_child_seq.nextval,?,?,?) return id into ?; end;"

   st = (OracleCallableStatement) c.prepareCall(sql);
            st.setLong(1, sectionId); // Bind the photo section id
            st.setString(2, photo.getName()); // Bind the photo name
            st.setString(3, photo.getDescription()); // Bind the photo
description
            st.registerOutParameter(4, java.sql.Types.INTEGER);
            st.executeUpdate();
            id = st.getInt(4);

 1.never do it just by position , as you have, because if you rebuild your
table or delete a column, all your sql will fall apart. ALWAYS use a named
column

2. for a 100% correct way use the following:
 String The_qry = "{ ? = call
external_user.PACKAGE_02.user_security(?,?,?)}";
            cstmt = (OracleCallableStatement)dbconn.prepareCall(The_qry);
            cstmt.registerOutParameter(1, OracleTypes.CURSOR);
            cstmt.setString(2, "DUMMY");
            cstmt.setString(3, currentuser);
            cstmt.setString(4, identification);

            cstmt.execute();

here i have a called procedure in the database, I pass the values to the
procedure & the procedure does the database access. why?:

1. i can control the security, the user that has access to the package has
very low access rights, they cannot read/write the database directly
2. this is almost totally immune to sql injection attacks
3. always use bind variables, for both speed , & security
4. I can make any code fixes in the database, without having to re-issue a
new app
5. I clearly separate the business rules from the user application.
6. even if someone did hack the account, they would spend a very long time
trying to figure out, what the procedures are & what values that take.

for speed and development use example 1, then when it works convert to
example 2.
but please , never write code the way you have, because once you get to more
than 10 values to insert , it will be almost impossible to track down
errors.and you cannot write a test case , to test such code.

Steve