Re: JDBC: Problem with execution of SQL Statement

From:

steve <steve@aol.com>

Newsgroups:

comp.lang.java.programmer,comp.lang.java.help

Date:

Tue, 20 Jun 2006 05:42:08 +0800

Message-ID:

<e775ng01st9@news4.newsguy.com>

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

Hello,
A simple JDBC program is given below:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestClass {

    public static void main(String args[]){
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url= "jdbc:oracle:thin:@IISSERVER:1521:ORACLE";
            Connection connection =
DriverManager.getConnection(url,"scott","tiger");
            Statement statement;
            ResultSet resultSet;
            statement = connection.createStatement();
            String query = "INSERT INTO RESULT "+
                    "VALUES("+
                    "4,'IT-04-01','','SHINDE
SAMEER',54,55,40,58,52,34,40,43,30,26,'SECOND CLASS'"+
                    ");";
            System.out.println(query);
            resultSet = statement.executeQuery(query);

        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
   }
}

The variable query holds the value:
INSERT INTO RESULT VALUES(4,'IT-04-01','','SHINDE
SAMEER',54,55,40,58,52,34,40,43,30,26,'SECOND CLASS');

I can run this statement in SQl Plus and get the record inserted into
the table.
But this is not the case with program. The Oracle driver loads and I
get a SQLException

java.sql.SQLException: ORA-00911: invalid character

        at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
        at
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
        at
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
        at
oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
        at
oracle.jdbc.driver.T4CStatement.execute_for_rows(T4CStatement.java:474)
        at

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

028)
at
oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1125)
at TestClass.main(TestClass.java:24)

What may be the problem?
Please help.

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