Get reference to connection in Janino

[Shane StClair]

Hello,

Is it possible to get a reference to a defined connection in Janino?

I'm inserting spatial objects into Oracle 11g, and this requires using Janino to build Oracle JDBC structs. I have a connection defined like this:

    <connection id="dest_db"

url="jdbc:oracle:thin:@the-host:1521:the-db"

user="user"

password="password"

>

        statement.batchSize=200    

    </connection>

But this doesn't work:

Struct oracleStruct = new OracleJDBCTypeFactory().createStruct( sdoGeom, (Connection) get("dest_db") );

Instead, I have to create a duplicate connection in an outer Janino query like this:

<query connection-id="janino">

    import java.sql.DriverManager;

    import java.sql.Connection;

    import java.lang.System;

    

    import com.vividsolutions.jts.io.WKBReader;

    import com.vividsolutions.jts.geom.GeometryFactory;

    import com.vividsolutions.jts.geom.PrecisionModel;

    Connection conn = null;

    Class.forName("oracle.jdbc.driver.OracleDriver");

    conn = DriverManager.getConnection(

         "jdbc:oracle:thin:@the-host:1521:the-db"

        ,"user"

        ,"password"

    );

    set("dest_conn", conn);

            

    next();

    <query connection-id="source_db">

        SELECT name

            ,geom.STAsBinary() as geom_wkb

        FROM table

        <query connection-id="janino">

            import com.vividsolutions.jts.geom.Geometry;

            import com.vividsolutions.jts.io.WKBReader;

            import com.vividsolutions.jts.geom.GeometryFactory;

            import com.vividsolutions.jts.geom.PrecisionModel;

            

            import java.sql.Blob;

            import org.hibernatespatial.oracle.SDOGeometryType;

            import org.hibernatespatial.oracle.SDOGeometry;

            import org.hibernatespatial.oracle.OracleJDBCTypeFactory;

            import java.sql.DriverManager;

            import java.sql.Connection;

            import java.sql.Struct;

            Blob blob = (Blob) get("geom_wkb");

            int blobLen = (int) blob.length();

            WKBReader wkbReader = new WKBReader( new GeometryFactory( new PrecisionModel( PrecisionModel.FLOATING ), 4326 ) );

            Geometry geom = wkbReader.read( blob.getBytes(1, blobLen ) );

            SDOGeometryType sdoGeomType = new SDOGeometryType();

            SDOGeometry sdoGeom = sdoGeomType.convertJTSGeometry( (Geometry) geom );

            Struct oracleStruct = new OracleJDBCTypeFactory().createStruct( sdoGeom, (Connection) get("dest_conn") );

            set("sdo_geom_struct", oracleStruct); 

            next();

            <script connection-id="dest_db">

                INSERT INTO TABLE(

                    NAME

                   ,GEOM

                ) VALUES (

                     ?name

                    ,?sdo_geom_struct

               )

            </script>

        </query>

    </query>

    conn.close();

</query>

[Ivan Vasiliev]

If you do access blob/clob you just only need to get connection from it (by casting to concrete impl).
http://docs.oracle.com/cd/E16338_01/appdev.112/e13995/oracle/sql/DatumWithConnection.html#getJavaSqlConnection__

AFAIK the is no way to get connection from the script.

С уважением,
Васильев Иван

2012/1/18 Shane StClair <srst...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "Scriptella ETL" group.
To view this discussion on the web visit https://groups.google.com/d/msg/scriptella/-/HeyKKC4mKQAJ.
To post to this group, send email to scrip...@googlegroups.com.
To unsubscribe from this group, send email to scriptella+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/scriptella?hl=en.

[Ivan Vasiliev]

To get clob/blob you also can run:

select empty_clob() as Alloha from dual

С уважением,
Васильев Иван

19 января 2012 г. 2:17 пользователь Ivan Vasiliev <vasil...@gmail.com> написал:

[Fyodor Kupolov]

Hi guys,

Unfortunately obtaining current connection is not possible now. I plan to implement it as follows:

1) Make current scriptella.Connection available as etl.connection variable.

2) For JdbcConnection, add public method getNativeConnection to return JDBC connection. This is applicable to several other drivers.

[TSK-26971] Add getConnection() accessor in EtlVariable (Unfortunately issue tracker is broken again and not visible to unregistered users, even though I've explicitly added anonymous to readonly group).

Please let me know if you see a better solution.

Regards,

Fyodor

[Fyodor Kupolov]

Now tracker issues are visible to unregistered users. It turned out,
there is an individual permissions tab for each issue type.

On Jan 19, 9:43 am, Fyodor Kupolov <scripte...@gmail.com> wrote:
> Hi guys,
>
> Unfortunately obtaining current connection is not possible now. I plan to
> implement it as follows:
> 1) Make current scriptella.Connection available as etl.connection variable.
> 2) For JdbcConnection, add public method getNativeConnection to return JDBC
> connection. This is applicable to several other drivers.
>
> [TSK-26971] Add getConnection() accessor in

> EtlVariable<http://javaforge.com/issue/26971> (Unfortunately

> issue tracker is broken again and not visible to unregistered users, even
> though I've explicitly added anonymous to readonly group).
>
> Please let me know if you see a better solution.
>
> Regards,
> Fyodor
>
>
>
>
>
>
>
>
>
> On Thu, Jan 19, 2012 at 1:22 AM, Ivan Vasiliev <vasilie...@gmail.com> wrote:
> > To get clob/blob you also can run:
>
> > select empty_clob() as Alloha from dual
>
> > С уважением,
> > Васильев Иван
>

> > 19 января 2012 г. 2:17 пользователь Ivan Vasiliev <vasilie...@gmail.com>

> > написал:
>
> >> If you do access blob/clob you just only need to get connection from it
> >> (by casting to concrete impl).
>

> http://docs.oracle.com/cd/E16338_01/appdev.112/e13995/oracle/sql/Datu...

>
>
>
>
>
>
>
>
>
> >> AFAIK the is no way to get connection from the script.
>
> >> С уважением,
> >> Васильев Иван
>

> >> 2012/1/18 Shane StClair <srstcl...@gmail.com>

[Shane StClair]

With this enhancement, will you be able to get any defined connection by id? If so, then sounds great! Thanks.

[Fyodor Kupolov]

Thanks for a suggestion. The method should accept connection id,
otherwise there is no point in exposing connection to its script.

[Fyodor Kupolov]

Hi guys,

I just wanted to let you know that, the feature has been implemented and will be available in 1.2. It is now possible to obtain an underlying connection from the "etl" variable by calling etl.getConnection(id). Example Java code:

        scriptella.core.EtlVariable etl = (scriptella.core.EtlVariable)get("etl");

        java.sql.Connection c = (java.sql.Connection)((scriptella.spi.NativeConnectionProvider)etl.getConnection("db")).getNativeConnection();

Best Regards,

Fyodor