JDBC: Problem with execution of SQL Statement

Sameer

unread,

Jun 19, 2006, 1:16:44 PM6/19/06

to

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:1028)
at
oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1125)
at TestClass.main(TestClass.java:24)

What may be the problem?
Please help.

Frank Langelage

unread,

Jun 19, 2006, 2:47:49 PM6/19/06

to

Try to use executeUpdate for this Insert and erase the ';' at the end of
the command.

steve

unread,

Jun 19, 2006, 5:42:08 PM6/19/06

to

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

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