8 Replies Latest reply on Apr 10, 2007 7:30 PM by 33450

    Problem with JGeometry.store method while using Thick driver.

    452043
      Hi,
      I had following problem with the JGeometry.store static method. I am using multiple connections and using thick driver for database connection.

      java.sql.SQLException: Cannot construct ARRAY instance, invalid connection
      at oracle.sql.ARRAY.<init>(ARRAY.java:141)
      at oracle.spatial.geometry.JGeometry.store(JGeometry.java:1289)
      at org.deegree.io.datastore.sql.oracle.OracleDatastore.prepareStatement(OracleDatastore.java:246)


      After going through the oracle spatial forum, I found the following two links which discussed the same problem.
      Re: JGeometry doesn't work with multiple connections...
      Re: SQLException doing JGeometry.store in multithreaded environment

      I used the solution given in these threads and created MyJGeometry class as shown below.

      package oracle.spatial.geometry;

      import java.sql.Connection;
      import java.sql.SQLException;

      public class MyJGeometry extends JGeometry {

           public static Object mutex = new Object();

           public MyJGeometry() {
                // create a dummy rectangle JGeometry instance
                super(0., 0., 0., 0., 0);
           }

           public static oracle.sql.STRUCT store2(JGeometry geom,
                     Connection connection)
                     throws java.sql.SQLException {
                synchronized (mutex) {
                     clearDBDescriptors();
                     return JGeometry.store(geom, connection);
                }
           }

           public static JGeometry load2(oracle.sql.STRUCT struct) throws SQLException {
                synchronized (mutex) {
                     clearDBDescriptors();
                     return JGeometry.load(struct);
                }
           }

           public static void clearDBDescriptors() {
                synchronized (mutex) {
                     geomDesc = null;
                     pointDesc = null;
                     elemInfoDesc = null;
                     ordinatesDesc = null;
                }
           }
      }


      Now I am using MyJGeometry.store2() & MyJGeometry.load2() methods to store and load the data.


      However this fix did not help and I am getting the following error randomly.

      java.sql.SQLException: ORA-00904: "X1"."ST_PLACE": invalid identifier

      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
      at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:672)
      at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:598)
      at oracle.jdbc.driver.T2CPreparedStatement.executeForDescribe(T2CPreparedStatement.java:571)
      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1031)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1124)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)


      I am running exactly same set of queries and this works sometimes without any errors giving all the required results, and fails sometimes with exception as given above. The invalid identifier changes for each occurance of error, sometimes it is ST_PLACE, sometimes FIPS, sometimes NAME etc. All these are valid identifiers in the underlying view, and if run separately from an SQLPLUS all the queries work.

      Also the same set of queries work fine if we just change to a thin driver. So the problem seems to be only with the thick driver.

      Please help !!

      Thanks in advance,
      Subrahmanyam.
        • 1. Re: Problem with JGeometry.store method while using Thick driver.
          452043
          Additionally sometimes I get the following error also randomly.


          java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
          ORA-06550: line 6, column 13:
          PLS-00306: wrong number or types of arguments in call to 'ODCISTATSINDEXCOST'
          ORA-06550: line 6, column 7:
          PL/SQL: Statement ignored

          at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
          at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:2321)
          at oracle.jdbc.oci8.OCIDBAccess.parseExecuteDescribe(OCIDBAccess.java:1255)
          at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)
          at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)
          at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
          at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)


          This seems to be completely Oracle problem.. We dont have control on any of the internal code..
          • 2. Re: Problem with JGeometry.store method while using Thick driver.
            452043
            Just to bring this back to first page in hope that some techie from Oracle will see and help.
            • 3. Re: Problem with JGeometry.store method while using Thick driver.
              Lqian-Oracle
              Hi,
              Just some rather general tips when using jgeometry:

              1. try stick with thin driver if OCI is not absolutely necessary.
              2. make sure only Oracle JDBC driver classes are used; for instance your error stacktrace shows a non-oracle JDBC class is invoking the JGeometry.store() method. This will not work as JGeometry.store requires ORacle db speicifc data types that is only available in an Oracle JDBC driver.
              3. Some of the other errors seem like different database schemas are involved. please check your application and make sure the right connection to the right database is used in querying the right table.
              • 4. Re: Problem with JGeometry.store method while using Thick driver.
                452043
                Hi,
                Thanks for the reply.

                We are using the OCI driver because of performance reasons and we want to continue using it. If you have any information on why only a thin driver is recommended with the oracle SDO API please provide that information.
                I have heard from many people saying that SDO API doesn't work with type 2 drivers, so far we had good success using it, but this JGeometry.store method is proving to be a real problem.

                The JGeometry.store is a public static method, so I think its not wrong to call this from non oracle class. However we are using Oracle JDBC thick(type 2) driver only.

                For the third point you mentioned, we have verified that we are connected to proper database before querying the tables. If the connection is to a different database, it should rather say that the view name is not found. We have very unique view names and no two schemas will have the same view name.

                So our conclusion is that Oracle is able to identify the views but some how while validating the field names it is failing.
                • 5. Re: Problem with JGeometry.store method while using Thick driver.
                  Lqian-Oracle
                  I dont have specific numbers, but my general experience is OCI driver is not much (if at all) faster than thin when loading geomtries. Unless you absolutly require certain features that are only available in OCI (which is very few), thin is always a better option for various reasons (ease of admin, runs on any platform, no dll/.so required, generally faster).
                  • 6. Re: Problem with JGeometry.store method while using Thick driver.
                    483672
                    Hello All
                    I too get a similiar error when I switch between datasources (managed) by oc4j. Subrimaniam did you work out the problem?


                    06/06/19 13:25:46 java.sql.SQLException: Cannot construct STRUCT instance, invalid connection
                    06/06/19 13:25:46 at oracle.sql.STRUCT.<init>(STRUCT.java:126)
                    06/06/19 13:25:46 at oracle.spatial.geometry.JGeometry.store(JGeometry.java:1616)
                    • 7. Re: Problem with JGeometry.store method while using Thick driver.
                      467820
                      Hi
                      06/06/19 13:25:46 java.sql.SQLException: Cannot construct STRUCT instance, invalid connection
                      06/06/19 13:25:46 at oracle.sql.STRUCT.<init>(STRUCT.java:126)
                      06/06/19 13:25:46 at oracle.spatial.geometry.JGeometry.store(JGeometry.java:1616)
                      See the links in the first post to solve the problem
                      1. try stick with thin driver if OCI is not absolutely necessary.
                      I am using the thin driver and I'm getting the same problem quite randomly as well when database is used in multiple threads simultaneously:

                      Caused by: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
                      ORA-06550: line 6, column 13:
                      PLS-00306: wrong number or types of arguments in call to 'ODCISTATSINDEXCOST'
                      ORA-06550: line 6, column 7:
                      PL/SQL: Statement ignored

                      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
                      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
                      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
                      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
                      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
                      at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
                      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
                      at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
                      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
                      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
                      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
                      at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:211)
                      at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:137)
                      at org.hibernate.loader.Loader.getResultSet(Loader.java:1676)
                      at org.hibernate.loader.Loader.doQuery(Loader.java:662)
                      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:223)
                      at org.hibernate.loader.Loader.doList(Loader.java:2147)


                      So the change of driver doesn't bringt anything. Any other ideas how to workaround this?
                      • 8. Re: Problem with JGeometry.store method while using Thick driver.
                        33450
                        In case anyone is searching and finds this, we did run into this same exact problem. It is caused by having mis-matched libraries for JGeometry. This was using OC4J, not OAS.

                        Look at your path (system environmental variable). If OAS is in front of your JDK/JRE and/or Oracle client, move it to the back of the path. This will allow the application to again use the correct binding library to create the JGeometry.