14 Replies Latest reply on Apr 18, 2006 10:36 AM by 452043

    JGeometry doesn't work with multiple connections...

    271872
      If I try to use JGeometry.store() to create a STRUCT, it works as long as I always use the same connection. If I use JGeometry.store, close the connection, open a new connection and call JGeometry.store I get the following:

      java.sql.SQLException: Cannot construct ARRAY instance, invalid connection
      at oracle.sql.ARRAY.<init>(ARRAY.java:123)
      at oracle.spatial.geometry.JGeometry.store(JGeometry.java:1289)
      at ...

      It appears that JGeometry is keeping around some static descriptors that were initialized with my first connection and that are incompatible with my follow-on connections. Am I interpreting this correctly?

      Is this a known bug?

      Any help that you could throw this way would be much appreciated.

      Thanks,
      -Ray
        • 1. Re: JGeometry doesn't work with multiple connections...
          Siva Ravada-Oracle
          Ray,
          This is the current expected behavior.
          JGeometry keeps static descriptors around so that
          it doesn't have to get them for every call to store.

          If you are changing connections, you should get a new
          JGeometry object with that connection.

          • 2. Re: JGeometry doesn't work with multiple connections...
            117288
            Hi,

            I experience the same problem as above.
            I don't understand what you mean in your answer.
            How do you flush the descriptors of JGeometry for a new connection?

            Regards
            Harald Hjelmseth
            • 3. Re: JGeometry doesn't work with multiple connections...
              264534
              Hi,
              Actually there is no method in JGeometry that will reset/clear the static database descriptors.

              A simple workaround is to create a new class that extends JGeometry, and add a method that sets all descriptors to null (so that JGeometry will recreate them when a new incompatible connection is to be used).

              for instance:
              public class MyJGeometry extends JGeometry
              {
                public static void clearDBDescriptors()
                {
                      geomDesc = null;
                     pointDesc = null;
                  elemInfoDesc = null;
                  ordinateDesc = null;
                }
              }
              then use MyJGeometry anywhere you are currently using JGeometry.

              hope this helps.
              • 4. Re: JGeometry doesn't work with multiple connections...
                447453
                LJ,
                Could you provide a full code sample for this workaround? Also where do you place the this MyJGeometry class? add to the sdoapi.jar alongside the JGeometry class (not sure if this is legal?) or in a separate package or where?

                In particular Im having trouble with the constructor for the MyJGeometry class as JGeometry does not have a default constuctor...any help here would be great...

                My app is converting shapefiles to oracle using different db connections but it falls down when using different connections as outlined above.

                I have included my code snippet using JGeometry below for clarity...
                for(int i = 0; i < shapefileReader.numRecords(); i++)
                {
                byte abyte0[] = shapefileReader.getGeometryBytes(i, m_dim);
                AdapterShapefileJGeom shapeAdapter = new AdapterShapefileJGeom();
                JGeometry jGeometry = shapeAdapter.importGeometry(abyte0, m_dim, m_srid);
                oracle.sql.STRUCT struct = JGeometry.store(jGeometry, conn);
                int j = i + 1;
                ps.setInt(1, j);
                ps.setObject(2, struct);
                                    
                ps.executeUpdate();
                }

                Many thanks for any help on this, its been driving me nuts!
                J
                • 5. Re: JGeometry doesn't work with multiple connections...
                  fjlee-Oracle
                  A sample program is provided below. It assumes that table states (state varchar2(26), state_abrv varchar2(2), geom sdo_geometry) exists in the database instance ORCL on system fjlee and table states2 (state varchar2(26), state_abrv varchar2(2), geom sdo_geometry) exists in the database instance dev2 on system stadb38. The main program reads the records in the states table and writes them to the states2 table using two connections.

                  ==========================================================

                  import java.sql.*;
                  import oracle.jdbc.OracleConnection;
                  import oracle.jdbc.pool.OracleDataSource;
                  import oracle.sql.*;
                  import oracle.jdbc.driver.*;
                  import oracle.jdbc.*;
                  import oracle.spatial.geometry.*;

                  public class TwoConnTest
                  {
                  // default parameter values:
                  static String url2 = "jdbc:oracle:thin:@stadb38:15222:dev2";
                  static String url = "jdbc:oracle:thin:@fjlee:1521:orcl";
                  static String userName = "SCOTT";
                  static String password = "TIGER";

                  public static void main(String [] args)
                  {
                  Connection conn = null;
                  Connection conn2 = null;
                  JGeometry geom = null;
                  MyJGeometry geom2 = null;
                  PreparedStatement pstmt = null;
                  PreparedStatement pstmt2 = null;
                  ResultSet rs = null;
                  STRUCT st = null;
                  STRUCT st2 = null;
                  String state;
                  String stateAbrv;
                  try
                  {
                  conn = getConnection(url, userName, password);
                  conn2 = getConnection(url2, userName, password);
                  pstmt = conn.prepareStatement("select state, state_abrv, geom from states");
                  rs = pstmt.executeQuery();

                  pstmt2 = conn2.prepareStatement(
                  "insert into states2 (state, state_abrv, geom) values (?, ?, ?)");
                  int cnt = 0;
                  while (rs.next())
                  {
                  state = rs.getString(1);
                  stateAbrv = rs.getString(2);
                  st = (STRUCT) rs.getObject("GEOM");
                  geom = JGeometry.load(st);
                  pstmt2.setString(1, state);
                  pstmt2.setString(2, stateAbrv);
                  st2 = MyJGeometry.store(geom, conn2);
                  pstmt2.setObject(3, st2);
                  pstmt2.executeUpdate();
                  cnt++;
                  }
                  System.out.println(" inserted rows = "+cnt);
                  conn2.commit();
                  }
                  catch (Exception e)
                  {
                  e.printStackTrace();
                  System.exit(1);
                  }
                  finally
                  {
                  if (rs != null)
                  {
                  try {rs.close();} catch (SQLException ignore) {};
                  }
                  if (pstmt != null)
                  {
                  try {pstmt.close();} catch (SQLException ignore) {};
                  }
                  if (pstmt2 != null)
                  {
                  try {pstmt2.close();} catch (SQLException ignore) {};
                  }
                  if (conn2 != null)
                  {
                  try {conn2.close();} catch (SQLException ignore) {};
                  }
                  if (conn != null)
                  {
                  try {conn.close();} catch (SQLException ignore) {};
                  }
                  }
                  }

                  public static synchronized Connection getConnection(
                  String connectURL, String user, String password) throws SQLException,
                  ClassNotFoundException
                  {
                  try
                  {
                  // use data source to get a JDBC connection
                  OracleConnection oconn;
                  OracleDataSource ods = new OracleDataSource();
                  ods.setUser(user);
                  ods.setPassword(password);
                  ods.setURL(connectURL);
                  oconn = (OracleConnection)ods.getConnection();
                  oconn.setAutoCommit(false);
                  return oconn;
                  }
                  catch (SQLException e)
                  {
                  e.printStackTrace();
                  System.out.println("getConnection error");
                  throw e;
                  }
                  }
                  class MyJGeometry extends oracle.spatial.geometry.JGeometry
                  {
                  public MyJGeometry()
                  {
                  // a dummy rectangle
                  super(0,0,0,0,0);
                  }
                  }

                  }
                  • 6. Re: JGeometry doesn't work with multiple connections...
                    447531
                    the code above does not seem to solve the problem of repeatedly calling JGeometry.store(geom, connection) passing in a different connection each time.
                    It does not clear out the static database references stored within JGeometry as it uses the same connection each time to write to.

                    How can I alter this code snippet to successfully use a different connection object to store geom data?

                    byte abyte0[] = shapeReader.getGeometryBytes(i, m_dim);
                    AdapterShapefileJGeom shapeAdapter = new AdapterShapefileJGeom();
                    JGeometry geom = shapeAdapter.importGeometry(abyte0, m_dim, m_srid);
                    struct = JGeometry.store(geom, connection);
                    int j = i + 1;
                    ps.setInt(1, j);
                    ps.setObject(2, struct);
                    ps.executeUpdate();

                    Thank you
                    • 7. Re: JGeometry doesn't work with multiple connections...
                      fjlee-Oracle
                      John,

                      I like to know how you create your connections and pass one to the JGeometry.store(struct, conn) calls. Could you post a sample program for it.

                      Thanks.
                      • 8. Re: JGeometry doesn't work with multiple connections...
                        447531
                        i use connection pooling in an EJB architecture for all db connections, using a factory method as follows:
                        public Connection getConnection(String poolName)
                        throws Exception
                        {
                        Connection connection = null;
                        Context initial = null;
                        initial = new InitialContext();
                        Object o = initial.lookup( poolName );

                        try{
                        connection = ( (XADataSource) o ).getXAConnection().getConnection();
                        }
                        catch ( ClassCastException cce ){
                        connection = ( (DataSource) o ).getConnection();
                        }
                        finally{
                        try {
                        if ( initial != null ){
                        initial.close();
                        }
                        }
                        catch (NamingException ne) {
                             log.error( classname + "Error closing context: " + ne.getMessage(), ne );
                        }
                        }
                        return connection;
                        }     


                        the from my loadShapeFile method:
                        try
                        {
                        conn = getConnection( connPool );
                        }
                        catch (Exception e)
                        {
                        e.printStackTrace();
                        }

                        try
                        {
                        String s1 = "INSERT INTO " + dbUser + "." + tableName + " VALUES(?, ?)";
                        ps = conn.prepareStatement(s1);
                             
                        ShapefileReaderJGeom reader = new ShapefileReaderJGeom(shapefileName, m_dim);
                                       
                        for(int i = 0; i < reader.numRecords(); i++){     
                        byte abyte0[] = reader.getGeometryBytes(i, m_dim);
                        AdapterShapefileJGeom shapeAdapter = new AdapterShapefileJGeom();
                        JGeometry geom = shapeAdapter.importGeometry(abyte0, m_dim, m_srid);
                        struct = JGeometry.store(geom, conn);
                        int j = i + 1;
                        ps.setInt(1, j);
                        ps.setObject(2, struct);
                        ps.executeUpdate();
                        }
                        reader.closeShapefile();
                        }
                        catch(Exception ex)
                        {
                        ex.printStackTrace();
                        }

                        when i call JGeometry.store(geom, conn) a second time with a different conn object (to another db) the store() method fails as it still has some static refs to the first connection and thus thinks this new connection is invalid.

                        Any help on this would be appreciated.
                        John
                        • 9. Re: JGeometry doesn't work with multiple connections...
                          fjlee-Oracle
                          Hi John:

                          Please try the following:

                          1. Create and compile a subclass MyJGeometry to be included in the oracle.spatial.geometry package as follows:

                          package oracle.spatial.geometry;

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

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


                          2. Create a sample program as TwoConnDemo.java as follows (change the connection url and other related parameters to use your Oracle instance):

                          import java.sql.*;
                          import java.util.Properties;
                          import oracle.jdbc.OracleConnection;
                          import oracle.jdbc.pool.OracleDataSource;
                          import oracle.jdbc.pool.OracleOCIConnectionPool;
                          import oracle.sql.*;
                          import oracle.jdbc.driver.*;
                          import oracle.jdbc.*;
                          import oracle.spatial.geometry.*;

                          public class TwoConnDemo
                          {
                          public static void main(String [] args)
                          {
                          String url = "jdbc:oracle:oci:@";
                          String userName = "SCOTT";
                          String password = "TIGER";
                          //String tnsAlias = "STADB38-OCI-DEV2";
                          String tnsAlias = "ORCL-OCI";
                          Connection conn = null;
                          OracleOCIConnectionPool cpool = null;

                          try
                          {
                          // initial oci connection pool
                          cpool = new OracleOCIConnectionPool();
                          cpool.setURL ( url + tnsAlias);
                          cpool.setUser(userName); // user name
                          cpool.setPassword(password); // password
                          Properties poolConfigProps = new Properties( ) ;
                          poolConfigProps.put(OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, "2") ;
                          poolConfigProps.put(OracleOCIConnectionPool.CONNPOOL_INCREMENT, "1") ;
                          poolConfigProps.put(OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, "20") ;

                          cpool.setPoolConfig(poolConfigProps);

                          for (int i=0; i<4; i++)
                          {
                          // get connection from oci jdbc pool
                          conn = cpool.getConnection(userName, password);
                          TwoConnDemo.loadFromPoolConn(i+1, conn);
                          if (conn!=null)
                          {
                          try
                          {
                          conn.close();
                          }
                          catch (Exception e)
                          {
                          e.printStackTrace();
                          System.exit(1);
                          }
                          finally
                          {
                          conn=null;
                          }
                          }
                          }
                          } catch (Exception e)
                          {
                          e.printStackTrace();
                          System.exit(1);
                          }
                          }

                          public static void loadFromPoolConn(int id, Connection conn)
                          {
                          JGeometry geom = null;
                          PreparedStatement pstmt = null;
                          PreparedStatement pstmt2 = null;
                          ResultSet rs = null;
                          STRUCT st = null;
                          STRUCT st2 = null;
                          String state;
                          String stateAbrv;
                          try
                          {
                          pstmt = conn.prepareStatement("select state, state_abrv, geom from states");
                          rs = pstmt.executeQuery();

                          pstmt2 = conn.prepareStatement(
                          "insert into states2 (state, state_abrv, geom) values (?, ?, ?)");
                          int cnt = 0;
                          // use a subclass to clear the descriptors
                          MyJGeometry.clearDBDescriptors();
                          while (rs.next())
                          {
                          state = rs.getString(1);
                          stateAbrv = rs.getString(2);
                          st = (STRUCT) rs.getObject("GEOM");
                          geom = JGeometry.load(st);
                          pstmt2.setString(1,state);
                          pstmt2.setString(2, stateAbrv);
                          st2 = JGeometry.store(geom, conn);
                          pstmt2.setObject(3, st2);
                          pstmt2.executeUpdate();
                          cnt++;
                          }
                          // clear the descriptors (function will be available in a 10gR2 patch or 11gR1)
                          //if (geom!=null) geom.clearDBDescriptors();
                          System.out.println(" Using connection "+id+": inserted rows = "+cnt);
                          conn.rollback();
                          }
                          catch (Exception e)
                          {
                          e.printStackTrace();
                          System.exit(1);
                          }
                          finally
                          {
                          if (rs != null)
                          {
                          try {rs.close();} catch (SQLException ignore) {};
                          }
                          if (pstmt != null)
                          {
                          try {pstmt.close();} catch (SQLException ignore) {};
                          }
                          if (pstmt2 != null)
                          {
                          try {pstmt2.close();} catch (SQLException ignore) {};
                          }
                          if (conn != null)
                          {
                          try {conn.close();} catch (SQLException ignore) {};
                          }
                          }
                          }

                          }

                          3. You can add the MyJGeometry.class to sdoapi.jar or set the classpath to include it when you run the TwoConnDemo.


                          Frank Lee
                          • 10. Re: JGeometry doesn't work with multiple connections...
                            447531
                            Frank,
                            Are u sure the variables (i.e. geomDesc, pointDesc etc) outlined in the method:
                            public static void clearDBDescriptors()
                            {
                            geomDesc = null;
                            pointDesc = null;
                            elemInfoDesc = null;
                            ordinatesDesc = null;
                            }

                            are actually accesible from this location?

                            Regards,
                            John
                            • 11. Re: JGeometry doesn't work with multiple connections...
                              fjlee-Oracle
                              John,

                              Yes, from this new class in the same package. These descriptors a package static variables.

                              Frank
                              • 12. Re: JGeometry doesn't work with multiple connections...
                                447453
                                Thanks fjlee, just got back around to this now...got it working with above solution.
                                Am I free to add the MyJGeometry class to the standard sdoapi.jar as part of this workaround? Am I legally able to do this I guess is what I'm asking.....

                                Regards,
                                J
                                • 13. Re: JGeometry doesn't work with multiple connections...
                                  467820
                                  Be warned, this function, clearDBDescriptors must be synchronised with JGeometry.store(), otherfalls the problems arises with multithreaded access to these static descriptors. See Re: SQLException doing JGeometry.store in multithreaded environment .
                                  • 14. Re: JGeometry doesn't work with multiple connections...
                                    452043
                                    Hi,
                                    After creating a new MyJGeometry class as given in the above solution, we are getting 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.

                                    I have started a new thread for the above problem here Re: Problem with JGeometry.store method while using Thick driver.

                                    Please help with any information you have on this problem.

                                    Thanks in advance,
                                    Subrahmanyam.