Chris,
I have three components ...
1) table setup (sql in top of java file)
2) java test (should insert into the table)
3) java test (should insert null ... but inserts empty geom)
Unfortunately though I don't think my patch solved my problem ... only masked it.
For 9i the patch does allow an empty geometry to be inserted.Unfortunately, an empty geometry is not the same as a null geometry. If you query the table after running the test case with this query you will get a count of 0 rather than 1
select count(*) from stest where geometry is null
Does anyone know how to insert a NULL value into the DB? (you can do it using SQL, so it's a valid construct from MDSYS.SDO_Geometry).
Cheers,
David
On 3/14/06, Chris Holmes <[EMAIL PROTECTED]> wrote:
We may have a 10g instance to test the patch on, if you can provide an
easy test case for us to run.
Chris
David Zwiers wrote:
> Hey guys,
>
> I think I ran into a bug in the GeometryConverter ... though i'd run it past
> cause I could be missing something.
>
> In the toSDO method, the first line is "if( geom == null) return
> asEmptyDataType();". When we go inside this, there is a call to "toSTRUCT(
> null, DATATYPE )" ... which creates a nasty ORA-00600 errorcode (internal
> error). After searching the net with the text (changes based on the actual
> error) it looks like this is an array out of bounds happening inside oracle.
>
>
> I think the culprit was the null in the second statement above. I replaced
> that with
> toSTRUCT( new Datum[5], DATATYPE ); and everything works for me ( 9.0.2).
> Note JDBC does not support null geometries in the setNull methods, so this
> was the only way that worked for me. Also, I got the [5] from the end of the
> toSDO method.
>
> The main reason for the msg, has todo with versions 8.x and 10.x. I'm not
> familiar with there internals, and don't have a test server ... so didn't
> want to mess this up for the other versions. Have you tested inserting null
> geoms into those servers? And if so, do you have an instance to test this
> patch?
>
> Cheers,
>
> David
--
Chris Holmes
The Open Planning Project
thoughts at: http://cholmes.wordpress.com
package org.geotools.data.oracle.sdo; import java.net.URL; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Properties;
import org.geotools.data.oracle.sdo.GeometryConverter;
import com.vividsolutions.jts.geom.Coordinate;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.geom.GeometryFactory;
import oracle.jdbc.OracleConnection;
import oracle.sql.STRUCT;
import junit.framework.TestCase;
public class GTTest extends TestCase {
/*
* SQL to run:
drop table STEST;
create table STEST
(
FID VARCHAR2(10) not null,
GEOMETRY MDSYS.SDO_GEOMETRY
);
*/
public GTTest(String arg0) {
super(arg0);
}
private Connection conn = null;
protected void setUp() throws Exception {
super.setUp();
conn =
getOracleConnection("VRIMS-SERVER","1521","VRIMS","FTAD$ADMIN","password");
}
private static OracleConnection getOracleConnection(String server, String
port, String sid, String userid, String pwd) throws SQLException {
String url = "jdbc:oracle:thin:@"+server+":"+port+":"+sid;
return (OracleConnection)openConnection(
"oracle.jdbc.driver.OracleDriver", url, userid, pwd );
}
private static Connection openConnection( String driver, String url, String
uid, String pwd ) throws SQLException {
Connection conn = null;
try {
Class.forName( driver );
} catch ( java.lang.ClassNotFoundException e ) {
fail( e.getMessage() );
}
conn = DriverManager.getConnection( url, uid, pwd );
return conn;
}
protected void tearDown() throws Exception {
super.tearDown();
if(conn != null && !conn.isClosed())
conn.close();
}
private static String sql = "INSERT INTO STEST (FID, GEOMETRY) values
(?, ?)";
public void testWithData() throws SQLException{
Coordinate[] coords = new Coordinate[3];
coords[0] = new Coordinate(0,0);
coords[1] = new Coordinate(1,1);
coords[2] = new Coordinate(2,2);
Geometry g = (new GeometryFactory()).createLineString(coords);
PreparedStatement ps = conn.prepareStatement(sql);
try{
ps.setString(1,"F_1");
STRUCT strt = (new
GeometryConverter((OracleConnection)conn)).toSDO(g);
ps.setObject(2,strt);
ps.execute();
}finally{
if(ps!=null)
ps.close();
}
}
public void testWithoutData() throws SQLException{
PreparedStatement ps = conn.prepareStatement(sql);
try{
ps.setString(1,"F_2");
STRUCT strt = (new
GeometryConverter((OracleConnection)conn)).toSDO(null);
ps.setObject(2,strt);
ps.execute();
}finally{
if(ps!=null)
ps.close();
}
}
// does not work
// public void testWithoutConverter() throws SQLException{
//
// PreparedStatement ps = conn.prepareStatement(sql);
// try{
// ps.setString(1,"F_3");
//
// ps.setObject(2,null);
//
// ps.execute();
//
// }finally{
// if(ps!=null)
// ps.close();
// }
// }
}
