JDBC: Problem with execution of SQL Statement

[Sameer]

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= "jdbcracle:thinIISSERVER: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]

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= "jdbcracle:thinIISSERVER: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

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

 

[steve]

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= "jdbcracle:thinIISSERVER: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

 

[SNK111]

How to: SQL in Java

Check this article ( shahriarnk.com/Shahriar-N-K-Research-Embedding-SQL-in-C-Sharp-Java.html ) for details on how to connect to SQL Server database from Java database applications. It also describes how to pass embedded SQL queries (SELECT, INSERT, UPDATE, DELETE), calling stored procedures, pass parameters etc.

Shahriar Nour Khondokar: shahriarnk.com/