How to execute queryes on Oracle 11g database running on remote Debian Linux PC

[PAUL12]

My problem is the following.

I want to use the Database demo provided in the demoj from the last ICE distribution.
How to adapt the code from the class LibraryI.java so that it is compatible with the new database.


public BookPrx createBook(String isbn, String title, java.util.List<String> authors, Ice.Current current)
throws BookExistsException
{
SQLRequestContext context = SQLRequestContext.getCurrentContext();
assert context != null;



// s.execute("Insert into MOBTEST.BOOKS (ID,ISBN,TITLE,RENTER_ID) values (1,null,'marius',null)");
// s.close();
// connection.close();
try
{
connection = getConnection();
stmt = connection.prepareStatement("SELECT * FROM books WHERE isbn = ?");
stmt.setString(1, isbn);
java.sql.ResultSet rs = stmt.executeQuery();
if(rs.next())
{
throw new BookExistsException();
}
connection.close();
//
// First convert the authors string to an id set.
//
connection = getConnection();
java.util.List<Integer> authIds = new java.util.LinkedList<Integer>();
for(String author : authors)
{
Integer id;
stmt = connection.prepareStatement("SELECT * FROM authors WHERE name = ?");
stmt.setString(1, author);
rs = stmt.executeQuery();
if(rs.next())
{
// If there is a result, then the database
// already contains this author.
id = rs.getInt(1);
assert !rs.next();
}
else
{
// Otherwise, create a new author record.
stmt = connection.prepareStatement("INSERT INTO authors (name) VALUES(?)",java.sql.Statement.RETURN_GENERATED_KEY S);
stmt.setString(1, author);
int count = stmt.executeUpdate();
assert count == 1;
rs = stmt.getGeneratedKeys();
boolean next = rs.next();
assert next;
id = rs.getInt(1);
}

// Add the new id to the list of ids.
authIds.add(id);
}
connection.close();
// Create the new book.
connection = getConnection();
stmt = connection.prepareStatement("INSERT INTO books (isbn, title) VALUES(?, ?)",
java.sql.Statement.RETURN_GENERATED_KEYS);
stmt.setString(1, isbn);
stmt.setString(2, title);
int count = stmt.executeUpdate();
assert count == 1;

rs = stmt.getGeneratedKeys();
boolean next = rs.next();
assert next;
Integer bookId = rs.getInt(1);

// Create new authors_books records.
for(Integer i : authIds)
{
stmt = connection.prepareStatement("INSERT INTO authors_books (book_id, author_id) VALUES(?, ?)");
stmt.setInt(1, bookId);
stmt.setInt(2, i);
count = stmt.executeUpdate();
assert count == 1;
}
connection.close();
return BookPrxHelper.uncheckedCast(current.adapter.create Proxy(BookI.createIdentity(bookId)));
}
catch(java.sql.SQLException e)
{
JDBCException ex = new JDBCException();
ex.initCause(e);
throw ex;
}

}

LibraryI()
{
}

synchronized public void destroy()
{
if(_destroyed)
{
return;
}
_destroyed = true;
for(QueryProxyPair p : _queries)
{
try
{
p.proxy.destroy();
}
catch(Ice.ObjectNotExistException e)
{
// Ignore, it could have already been destroyed.
}
}
}

synchronized public void
shutdown()
{
if(_destroyed)
{
return;
}
_destroyed = true;

// Shutdown each of the associated query objects.
for(QueryProxyPair p : _queries)
{
p.impl.shutdown();
}
}

synchronized private void
add(BookQueryResultPrx proxy, BookQueryResultI impl)
{
// If the session has been destroyed, then destroy the book
// result, and raise an ObjectNotExistException.
if(_destroyed)
{
proxy.destroy();
throw new Ice.ObjectNotExistException();
}
_queries.add(new QueryProxyPair(proxy, impl));
}

synchronized private void reapQueries()
{
if(_destroyed)
{
throw new Ice.ObjectNotExistException();
}

java.util.Iterator<QueryProxyPair> p = _queries.iterator();
while(p.hasNext())
{
QueryProxyPair pair = p.next();
try
{
pair.proxy.ice_ping();
}
catch(Ice.ObjectNotExistException e)
{
p.remove();
}
}
}

static class QueryProxyPair
{
QueryProxyPair(BookQueryResultPrx p, BookQueryResultI i)
{
proxy = p;
impl = i;
}

BookQueryResultPrx proxy;
BookQueryResultI impl;
}
public Connection getConnection(){

try {
// Load the JDBC driver

Class.forName("oracle.jdbc.driver.OracleDriver").n ewInstance();
connection = DriverManager.getConnection("jdbcracle:thin:mobtest@//129.187.64.239:1521/orcl", "mobtest", "mobt");
System.out.println("con established");

} catch (ClassNotFoundException e) {
// Could not find the database driver
} catch (SQLException e) {
// Could not connect to the database
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
PreparedStatement stmt = null;
Connection connection = null;
private java.util.List<QueryProxyPair> _queries = new java.util.LinkedList<QueryProxyPair>();
private boolean _destroyed = false;
}


I have tryed to modify the sql queryes so that they are compatible with Oracle SQL.
The code can not execute the query . We use to execute the prepared staments the object context.

In the last method I have used. A connection object but I got after every 3 book insertion an exception. I have to say that the books are added to the data base but with exceptions in the console.

Can somebody sugest my a beter way to execute the prepared statemnets from this methods so that I use context and not create and close the connection after each prepared statement like I have use it in createBook method.
This is the output of the Server console.

con established
con established
con established
!! 11/8/11 20:30:43:701 demo.Database.library.Server: error: Ice.ThreadPool.Server-0: call of `createBook' on id `/fe5d82e0-aa92-43cf-b162-609716aa80f0' failed: error:
Demo.JDBCException
.........
Caused by: java.sql.SQLException: Invalid column type: getInt not implemented for class oracle.jdbc.driver.T4CRowidAccessor
at oracle.jdbc.driver.Accessor.unimpl(Accessor.java:4 12)
at oracle.jdbc.driver.Accessor.getInt(Accessor.java:5 29)
at oracle.jdbc.driver.OracleReturnResultSet.getInt(Or acleReturnResultSet.java:388)
at library.LibraryI.createBook(LibraryI.java:281)
... 14 more
-- 11/8/11 20:30:43:794 demo.Database.library.Server: SQLRequestContext: rollback context: library.SQLRequestContext@45c3987
-! 11/8/11 20:30:43:794 demo.Database.library.Server: warning: Ice.ThreadPool.Server-0: dispatch exception:
identity: fe5d82e0-aa92-43cf-b162-609716aa80f0
facet:
operation: createBook
remote host: 129.187.209.243 remote port: 62945
Ice.UnknownException
unknown = (null)
at library.DispatchInterceptorI.dispatch(DispatchInte rceptorI.java:42)
at Ice.DispatchInterceptor.__dispatch(DispatchInterce ptor.java:43)
..
Caused by: Demo.JDBCException
..
... 9 more
Caused by: java.sql.SQLException: Invalid column type: getInt not implemented for class oracle.jdbc.driver.T
-- 11/8/11 20:30:44:539 demo.Database.library.Server: Protocol: sending reply
message type = 2 (reply)
compression status = 0 (not compressed; do not compress response, if any)
message size = 25
request id = 36
reply status = 0 (ok)
con established
con established
con established
The message connection established appears every time I add a new book but the errors are there. Still the books are added to the oracle database.
How to get read of them?
How to do it more elegantly without opening and clossing the connection?
How can I use further on the context object?

Regards,
Paul

[xdm]

Hi Paul,

As you know this demo is for mysql, porting in to oracle should be easy, first update the sql schema to be compatible with oracle, and then review the queries that give you errors.

We provide some C++ oracle demos that can help you figured out things, see cpp/demo/Database/Oracle/

For concrete SQL oracle problems you are better asking in oracle SQL forums.

The message connection established appears every time I add a new book but the errors are there. Still the books are added to the oracle database.
How to get read of them?
How to do it more elegantly without opening and clossing the connection?

library/ConnectionPool.java that implements a connection pool with keeps connections open, that works well with MYSQL driver.

How can I use further on the context object?

Not sure what you mean, can you be more concrete.

Caused by: java.sql.SQLException: Invalid column type: getInt not implemented for class oracle.jdbc.driver.T
-- 11/8/11 20:30:44:539 demo.Database.library.Server: Protocol: sending reply

That seems a problem with the scheme or the driver, It could be a different type in the Scheme, the mysql scheme has id as numeric type, and the driver provide the getInt method for numeric types.

[PAUL12]

My problem is actualy this.

con established
!! 11/10/11 01:19:27:796 demo.Database.library.Server: error: Ice.ThreadPool.Server-3: call of `queryByIsbn' on id `/8ee0a953-5926-4fac-93e9-b7bbc922f7f0' failed: error:
Demo.JDBCException
at library.LibraryI.queryByIsbn(LibraryI.java:88)
at Demo._LibraryDisp.___queryByIsbn(_LibraryDisp.java :184)
at Demo._LibraryDisp.__dispatch(_LibraryDisp.java:318 )
at Ice.ObjectImpl.ice_dispatch(ObjectImpl.java:292)
at Ice.ObjectImpl.ice_dispatch(ObjectImpl.java:317)
at library.DispatchInterceptorI.dispatch(DispatchInte rceptorI.java:25)
at Ice.DispatchInterceptor.__dispatch(DispatchInterce ptor.java:43)
at IceInternal.Incoming.invoke(Incoming.java:159)
at Ice.ConnectionI.invokeAll(ConnectionI.java:2357)
at Ice.ConnectionI.dispatch(ConnectionI.java:1208)
at Ice.ConnectionI.message(ConnectionI.java:1163)
at IceInternal.ThreadPool.run(ThreadPool.java:302)
at IceInternal.ThreadPool.access$300(ThreadPool.java: 12)
at IceInternal.ThreadPool$EventHandlerThread.run(Thre adPool.java:643)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Exhausted Resultset
at oracle.jdbc.driver.OracleResultSetImpl.getInt(Orac leResultSetImpl.java:915)
at library.LibraryI.queryByIsbn(LibraryI.java:43)
... 14 more

-- 11/10/11 01:19:27:837 demo.Database.library.Server: SQLRequestContext: rollback context: library.SQLRequestContext@1ce3570c
-! 11/10/11 01:19:27:839 demo.Database.library.Server: warning: Ice.ThreadPool.Server-3: dispatch exception:
identity: 8ee0a953-5926-4fac-93e9-b7bbc922f7f0
facet:
operation: queryByIsbn
remote host: 192.168.1.64 remote port: 50333
Ice.UnknownException
unknown = (null)
at library.DispatchInterceptorI.dispatch(DispatchInte rceptorI.java:43)
at Ice.DispatchInterceptor.__dispatch(DispatchInterce ptor.java:43)
at IceInternal.Incoming.invoke(Incoming.java:159)
at Ice.ConnectionI.invokeAll(ConnectionI.java:2357)
at Ice.ConnectionI.dispatch(ConnectionI.java:1208)
at Ice.ConnectionI.message(ConnectionI.java:1163)
at IceInternal.ThreadPool.run(ThreadPool.java:302)
at IceInternal.ThreadPool.access$300(ThreadPool.java: 12)
at IceInternal.ThreadPool$EventHandlerThread.run(Thre adPool.java:643)
at java.lang.Thread.run(Unknown Source)
Caused by: Demo.JDBCException
at library.LibraryI.queryByIsbn(LibraryI.java:88)
at Demo._LibraryDisp.___queryByIsbn(_LibraryDisp.java :184)
at Demo._LibraryDisp.__dispatch(_LibraryDisp.java:318 )
at Ice.ObjectImpl.ice_dispatch(ObjectImpl.java:292)
at Ice.ObjectImpl.ice_dispatch(ObjectImpl.java:317)
at library.DispatchInterceptorI.dispatch(DispatchInte rceptorI.java:25)
... 9 more
Caused by: java.sql.SQLException: Exhausted Resultset
at oracle.jdbc.driver.OracleResultSetImpl.getInt(Orac leResultSetImpl.java:915)
at library.LibraryI.queryByIsbn(LibraryI.java:43)
... 14 more
When I trye to read the first column value from a Table. like this.

connection = getConnection();
stmt = connection.prepareStatement("SELECT * FROM books WHERE isbn = ?");
stmt.setInt(1, Integer.parseInt(isbn));
ResultSet rs = stmt.executeQuery();

connection.close();
if(rs.getInt(1) == 297){
System.out.println("yes");
}else
System.out.println("no");

[xdm]

You must not read from the ResultSet object after you close the connection, but this is actually a Java SQL problem non Ice related.