Four Stars

How I can insert SDO_GEOMETRY using tOracleOutput

Hi, 
I'm trying to insert a  SDO_GEOMETRY object/element in one column using tOracleOutput.
My first option was to insert the rows using the tOracleRow component using the following query string: INSERT INTO SAMPLE_POINTS values(1,SDO_GEOMETRY(?)). This option worked properly but the performance is really bad. 
My second option was to use the component tOracleOutput to perform a batchupdate  but  it wasn't work:
I have tried to use different  kind of objects,byte[], string for passing the data to SDO_GEOMETRY column but always get an error.
Reading Oracle Documentation Oracle JGeometry Api I see that is necessary create an STRUCT object, but I don't know how I can include this code in Talend.
 /// writing a geometry back to database
PreparedStatement ps = connection.prepareStatement(
"UPDATE states set geometry=? where name='Florida'");
//convert JGeometry instance to DB STRUCT using the SDO pickler
STRUCT obj = JGeometry.store(connection, j_geom);
ps.setObject(1, obj);
ps.execute();



Have someone any clue how to work with Oracle spatial types and Talend (tOracleOutput)?
Have someone any clue how to use custom code in the PreparedStatement for the tOracleOutput?

Thank you in advance.
David
1 REPLY
Moderator

Re: How I can insert SDO_GEOMETRY using tOracleOutput

Hi,
The "Use PreparedStatement" option has been added to all db row components (Advanced settings section).
For your performance issue, have you already checked "Commit every" option to ensure transaction quality and above all better performance on executions?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.