I execute insert statement using sql pod

cnn := SqlConn.open(“jdbc:oracle:oci:@mysvr”, “user”, “password”) 
stm := cnn.sql(“INSERT INTO ….”).prepare
stm.execute()

then I got exception below.

java.sql.SQLException: Invalid column type: getLong not implemented for class ora
cle.jdbc.driver.RowidAccessor
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
        at oracle.jdbc.driver.Accessor.unimpl(Accessor.java:358)
        at oracle.jdbc.driver.Accessor.getLong(Accessor.java:496)
        at oracle.jdbc.driver.OracleReturnResultSet.getLong(OracleReturnResultSet.java:290)
        at fan.sql.StatementPeer.executeResult(StatementPeer.java:245)
        at fan.sql.StatementPeer.execute(StatementPeer.java:208)
        at fan.sql.Statement.execute(Statement.fan)
        at fan.sql.Statement.execute(Statement.fan)

The jdbc driver is Oracle version - 10.2.0.1.0 . How can I avoid this problem ?

That looks like one of two problems:

• trying to get update count and its not supported by JDBC driver
• trying to get specific column with number type as long

Some of the update stuff seems to be most inconsistent b/w JDBC drivers. Do you know how to pull from hg and rebuild the latest sql pod from source? If you can do that we can try and debug and figure out what exactly the problem is. I've made quite a few changes since our last build.

Do you know how to pull from hg and rebuild the latest sql pod from source?

Yes, I do. I tryed to build from source and run on it.

java.sql.SQLException: Invalid column type: getLong not implemented for class oracle.jdbc.driver.RowidAccessor
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java :112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java :146)
        at oracle.jdbc.driver.Accessor.unimpl(Accessor.java:358)
        at oracle.jdbc.driver.Accessor.getLong(Accessor.java:496)
        at oracle.jdbc.driver.OracleReturnResultSet.getLong(OracleReturnResultSe t.java:290)
        at fan.sql.StatementPeer.executeResult(StatementPeer.java:257)
        at fan.sql.StatementPeer.execute(StatementPeer.java:220)
        at fan.sql.Statement.execute(Statement.fan)
        at fan.sql.Statement.execute(Statement.fan)
        at fan.aeontest_0.InsAeon$main$0.doCall(/D:/test.fan:45)
         :

SQL insert statement itself is succeeded, and the record is added , then the above exception occurs.

Are you trying to get the autogenerated id of the new inserted row ?

I remember several years back using SAP/Oracle with Fantom and having an issues like that.

Basically Oracle did/does not support that the "standard" way of returning generated keys and I had to use a workaround (I think using a sequence and then select mysequence.nextval after the insert IIRC).

I don't miss using Oracle at all, while SQL "standrad" is not eally respected by anybody, Oracle was particularly a pain to deal with. .

If that sounds like your issue, i can try to dig for the workaround I had (might have had to use java sql connection directly from Fantom, but can't quite remember)

See this for relevant info: http://stackoverflow.com/questions/1004128/obtain-id-of-an-insert-in-the-same-statement

My explaination is confusing , sorry.

I'm not trying to get the autogenerated id of the new inserted row. I just try to insert a record, and occur exception on the Statement.execute() but the record is inserted.

static Void main()
{
  cnn := SqlConn.open(“jdbc:oracle:oci:@mysvr”, “user”, “password”) 
  stm := cnn.sql(“INSERT INTO ….”).prepare
  stm.execute()  // exception occurs
  // finish
}

Okay, so Oracle says they support auto-generated keys, but you can't access them as a long.

1. Do you have an auto-generated key on that table? If so what is its type?
2. Can you add the following debugging lines to StatementPeer.java line 257 and let me know what you see?

   List keys = new List(Sys.IntType);
   while (rs.next()) 
   {
     System.out.println("--> " + rs.getObject(1));         
     System.out.println("  > " + rs.getObject(1).getClass());         
     keys.add(rs.getLong(1));
   } 
   if (!keys.isEmpty()) return keys;

Thanks for helping me debug

1. Do you have an auto-generated key on that table? If so what is its type?

No, I don't. It has two-column primary key, and they are both varchar, need to specify value explicitly when I add a record.

2. Can you add the following debugging lines to StatementPeer.java line 257 and let me know what you see?

The result is :

--> oracle.sql.ROWID@1fc6e42
  > class oracle.sql.ROWID

I try to access it as str, and it is successful.

But I don't know if it is useful to get list of new records' rowids.

Is there anything else that I try ?

Can you try out this changeset and let me know if that fixes your problem. Thanks

That works fine for INSERT .... VALUES statements. Thank you very much.

But I tried INSERT ... SELECT statement to see multiple list return value then occured another exception . It seems like this problem.

https://forums.oracle.com/forums/thread.jspa?messageID=10263361

That statement works correctly on SQLPlus. I will post after examining a little more. Do I post it on this topic ?

What is exception?

When I try to execute INSERT .... SELECT satement, SqlErr is raised.

sql::SqlErr: ORA-00933: SQL command not properly ended.  
  sql::SqlConnPeer.err (SqlConnPeer.java:179)
  sql::StatementPeer.execute (StatementPeer.java:238)
  sql::Statement.execute (Statement.fan)
  sql::Statement.execute (Statement.fan)
  test3_0::InsAeon.main (/D:/test/test3.fan:18)
  java.lang.reflect.Method.invoke (Unknown)
  fan.sys.Method.invoke (Method.java:559)
  fan.sys.Method$MethodFunc.callList (Method.java:198)
  fan.sys.Method.callList (Method.java:138)
  fanx.tools.Fan.callMain (Fan.java:173)
  fanx.tools.Fan.executeFile (Fan.java:98)
  fanx.tools.Fan.execute (Fan.java:37)
  fanx.tools.Fan.run (Fan.java:298)
  fanx.tools.Fan.main (Fan.java:336)
Cause:
  sys::Err: java.sql.SQLException: ORA-00933: SQL command not properly ended.  
    oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:112)
    oracle.jdbc.driver.T4CTTIoer.processError (T4CTTIoer.java:331)
    oracle.jdbc.driver.T4CTTIoer.processError (T4CTTIoer.java:288)
    oracle.jdbc.driver.T4C8Oall.receive (T4C8Oall.java:745)
    oracle.jdbc.driver.T4CPreparedStatement.doOall8 (T4CPreparedStatement.java:2
19)
    oracle.jdbc.driver.T4CPreparedStatement.executeForRows (T4CPreparedStatement
.java:970)
    oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout (OracleStatement.jav
a:1190)
    oracle.jdbc.driver.OraclePreparedStatement.executeInternal (OraclePreparedSt
atement.java:3370)
    oracle.jdbc.driver.OraclePreparedStatement.execute (OraclePreparedStatement.
java:3476)
    sql::StatementPeer.execute (StatementPeer.java:219)
    sql::Statement.execute (Statement.fan)
    sql::Statement.execute (Statement.fan)
    test3_0::InsAeon.main (/D:/test/test3.fan:18)

I try to execute same sql statement on java-jdbc code. Then when I set Statement.RETURN_GENERATED_KEYS get same oracle error , and I set Statement.NO_GENERATED_KEYS , it is OK.

What happens if you use query instead of execute?

When I use execute then smilar error is raised.

sql::SqlErr: ORA-00933: SQL command not properly ended. 

  sql::SqlConnPeer.err (SqlConnPeer.java:179)
  sql::StatementPeer.query (StatementPeer.java:75)
  sql::Statement.query (Statement.fan)
  sql::Statement.query (Statement.fan)
  test5_0::InsAeon.main (/D:/test/test5.fan:21)
  java.lang.reflect.Method.invoke (Unknown)
  fan.sys.Method.invoke (Method.java:559)
  fan.sys.Method$MethodFunc.callList (Method.java:198)
  fan.sys.Method.callList (Method.java:138)
  fanx.tools.Fan.callMain (Fan.java:173)
  fanx.tools.Fan.executeFile (Fan.java:98)
  fanx.tools.Fan.execute (Fan.java:37)
  fanx.tools.Fan.run (Fan.java:298)
  fanx.tools.Fan.main (Fan.java:336)
Cause:
  sys::Err: java.sql.SQLException: ORA-00933: SQL command not properly ended. 
  :
  :