[ORA-00922: missing or invalid option] preparedStatement.executeUpdate err
i am trying to create and populate an "Bookstore" table by concatenating query strings into one long string: myUpdateString. for some reason i keep getting the above runtime err message.
the update string works if i break it down and use statment.execute("...");
but i want to let SQL handle optimization by making my update into one preparedStatement.
my revelevant source code:
/*populate the database with some data*/
try
{
// short-cut way
// stat.execute("CREATE TABLE Bookstore (title CHAR(50), isbn INTEGER, author CHAR(20), price FLOAT)");
// stat.execute("INSERT INTO Bookstore VALUES ('The Zen of JDBC programming',1,'Ting Y.',109.99)");
// stat.execute("INSERT INTO Bookstore VALUES ('The Typing of the Dead: a strategic guide',2,'Micheal T.',49.99)");
// stat.execute("INSERT INTO Bookstore VALUES ('Fundanmentals of Noseblowing',3,'Tony W.',99.99)");
// proper way for longer more complex query or update actions
String myUpdateString ="CREATE TABLE Bookstore (title CHAR(50), isbn INTEGER, author CHAR(20), price INTEGER)"
+"INSERT INTO Bookstore VALUES ('bookA',1,'author1',10)"
+"INSERT INTO Bookstore VALUES ('bookB',2,'author2',20)"
+"INSERT INTO Bookstore VALUES ('bookC',3,'author3',30)";
prepStmt = conn.prepareStatement(myUpdateString);
prepStmt.executeUpdate();// Line 113
}
catch (SQLException sqle)
{
sqle.getMessage();
sqle.printStackTrace();
}
and my StackTrace:
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.T4C90all.receive(T4C80all.java:623)
at oracle.jdbc.driverT4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:543)
at oracle.jdbc.driverOracleStatement.doExecuteWithTimeout(OraclePreparedStatement.java:2888)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:2960)
at QueryDBFrame.<init>(QueryDB.java:113)
at QueryDB.main(QueryDB.java:15)
Sorry for verbatim. i've tried 2 hours debugging this using everything i know with no luck. hopefully one of experts here can give me some suggestion on where to look for oracle-related error and good documentation.
ting
# 1
> the update string works if i break it down and use
> statment.execute("...");
> but i want to let SQL handle optimization by making
> my update into one preparedStatement.
I don't believe you have understood the reason for a PreparedStatement. It is not to concatenate queries as you are suggesting in your code.
> my revelevant source code:
>
> // proper way for longer more complex query or update
> actions
> String myUpdateString = "CREATE TABLE Bookstore
> (title CHAR(50), isbn INTEGER, author CHAR(20), price
> INTEGER)"
> + "INSERT INTO Bookstore VALUES
> ('bookA',1,'author1',10)"
> + "INSERT INTO Bookstore VALUES
> ('bookB',2,'author2',20)"
> + "INSERT INTO Bookstore VALUES
> ('bookC',3,'author3',30)";
> prepStmt = conn.prepareStatement(myUpdateString);
> prepStmt.executeUpdate();// Line 113
> }
> catch (SQLException sqle)
> {
> sqle.getMessage();
> sqle.printStackTrace();
> }[/code]
To be honest, I'm not sure where to begin on what's wrong with this code. This isn't really close to what you need to be successful using a PreparedStatement. You may want to go through the JDBC tutorial on the SUN website (just search for jdbc tutorial).
Still...
You will want to create your table separate from your queries (in a different call to the database, probably using ExecuteUpdate).
You will want to create placeholders as ? in place of your literal and numeric values, then use the PreparedStatement setXXX functions to set the appropriate values.
You will have to execute the PreparedStatement three times to successfully insert 3 rows.
If you want to learn the hard way and not take the tutorial, you can search the forums for examples of using PreparedStatements.
Good luck on this.
# 2
You should try like that:
class MyBookStore
{
java.sql.PreparedStatement InserBookStoreStatement = null;
java.sql.Connection conn=null;
.
.
.
.
Init()
[
conn = java.sql.DriverManager.getConnection(connect_string,
strUser, strPwd);
PrepareStatments();
}
public void PrepareStatements()
{
String InsertBookStoreQuery = "select Bookstore.* where ISBN=?"
try
{
InserBookStoreStatement = conn.prepareStatement(InsertBookStoreQuery
, oracle.jdbc.OracleResultSet.TYPE_SCROLL_SENSITIVE
, oracle.jdbc.OracleResultSet.CONCUR_UPDATABLE);
}
catch (java.lang.Exception f)
{
f.printStackTrace();
}
}
InsertBookStore
{
try
{
String NewIsbn = GetIsbn;
InsertBookStoreStatement.setString(1,NewIsbn);
java.sql.ResultSet rset = InsertBookStoreStatement.executeQuery();
rset.moveToInsertRow();
rset.updateString("TITLE", "BookA");
rset.updateString("ISBN", NewIsbn);
rset.updateString("Author", "AuthorA);
rset.updateString("PRICE", 30);
rset.insertRow();
rset.close();
}
}
catch (java.lang.Exception f)
{
PrintMyError(f);
}
}
