SDO_GEOMETRY scanning appears broken

[andygarfield]

I'm trying to get data from an SDO_GEOMETRY column, but the scanning seemingly doesn't work. This program just prints 5 blank rows:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-goracle/goracle"
)

func main() {
    db, err := sql.Open("goracle", "user/pass@db")
    if err != nil {
        panic(err)
    }

    rows, err := db.Query(`
        SELECT SDO_GEOMETRY('POINT(1 0)', 4326) SHAPE FROM DUAL UNION ALL
        SELECT SDO_GEOMETRY('POINT(0 1)', 4326) SHAPE FROM DUAL UNION ALL
        SELECT SDO_GEOMETRY('POINT(-1 4)', 4326) SHAPE FROM DUAL UNION ALL
        SELECT SDO_GEOMETRY('POINT(2 -6)', 4326) SHAPE FROM DUAL UNION ALL
        SELECT SDO_GEOMETRY('POINT(-2 7)', 4326) SHAPE FROM DUAL`,
    )
    if err != nil {
        fmt.Println(err)
    }

    for i := 0; i < 5; i++ {
        rows.Next()
        b := sql.RawBytes{}

        rows.Scan(&b)

        fmt.Printf("% x\n", b)
    }
}

[tgulacsi]

What is the error? What is a CREATE for tbl?

[andygarfield]

I modified the example above, hopefully that is more helpful. The output is five blank rows, but there is no explicit error.

Thanks for the help!

[tgulacsi]

Please try the SDO branch - still does not work, it seems that the objects I got back from ODPI-C are empty.

[anthony-tuininga]

It looks to me like the object type associated with the object is being created without having the init() called which populates the various properties of that object type! You'll probably need another constructor type function in obj.go.

[tgulacsi]

Thanks, @anthony-tuininga , that init() was the missing potion!

@andygarfield please try the SDO branch (03e789f) ! Scan into interface{} and cast it to *goracle.Object, than inspect the Attributes.

[andygarfield]

I was able to navigate the object, but I could not get at the scalar data. Am I missing something?

package main

import (
    "database/sql"
    "fmt"
    "github.com/go-goracle/goracle"
    _ "github.com/go-goracle/goracle"
)

func main() {
    db, err := sql.Open("goracle", "user/pass@db")
    if err != nil {
        panic(err)
    }

    rows, err := db.Query(`
        SELECT SDO_GEOMETRY('POINT(-2 7)', 4326) SHAPE FROM DUAL`,
    )
    if err != nil {
        fmt.Printf("error %v", err)
    }

    rows.Next()

    var data interface{}
    rows.Scan(&data)

    obj := data.(*goracle.Object)

    for _, a := range obj.ObjectType.Attributes {
        if a.Name == "SDO_POINT" {
            for _, val := range a.ObjectType.Attributes {
                fmt.Printf("%v\n", val.Name)
            }
        }
    }
}

Output:

X
Y
Z

[tgulacsi]

e4ef005 is better (the API is weird: you have to use ObjectType.Attributes, but GetAttribute on the object itself...), but now I get 0. "X": {3009 0x7f748805ea40} (ORA--12153: driver: bad connection) errors.

I'm attaching the log with DPI_DEBUG_LEVEL=3 (sdo.log), @anthony-tuininga can you help? (This DB I've tested against does not have SDO_POINT, so I've created a test version of it according to SDO_POINT's documentation).

[tgulacsi]

Here is the log with DPI_DEBUG_LEVEL=999: sdo.log

[tgulacsi]

Forget it. It's on my side. d6e3238 is better, but not perfect yet.

[anthony-tuininga]

Taking a quick peek at the code I am wondering why you don't store a map by name of the various attributes internally and then have this method instead?

func (O *Object) GetAttribute(name string, dest interface{}) error

Then, internally you can look up the attribute by name (which is probably more useful to your end users than an integer index!) and you do the same thing as Scan itself does for queries. This would allow you to use it as follows:

var gtype int
err := obj.GetAttribute("SDO_GTYPE", &gtype)

I'm not a Go expert, though, so maybe I'm missing something. :-)

[tgulacsi]

Thanks, good idea! I've copied the ODPI-C almost as-is, to ease testing, and mostly because I don't use Objects and don't know (yet) what kind of API would be the best.

Please help again! 1f405b1 results in

=== RUN   TestSDO
getAttributeValue(0x27d4d80, "SDO_GTYPE"=0x2807f20, 3003, &{isNull:1 _:[0 0 0 0] value:[0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0]})
getAttributeValue(0x27d4d80, "SDO_POINT"=0x283c030, 3009, &{isNull:1 _:[0 0 0 0] value:[0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0]})
--- PASS: TestSDO (0.06s)
    z_test.go:1711: Typ=121 Len=11: 192,41,39,10,0,0,0,0,56,127,145 N
    z_test.go:1718: SDO_GTYPE: {NativeTypeNum:3003 dpiData:0xc0000c2320} [true]
    z_test.go:1723: SDO_POINT: {NativeTypeNum:3009 dpiData:0xc0000c2320} [true]
    z_test.go:1726: SDO_POINT. sub=<nil>

as if dpiObject_getAttributeValue would return NULL, though the object is not null and DUMP confirms that. What am I doing wrong?

[tgulacsi]

Ok, 592f801 works, as long as I tested! @andygarfield can you test it?

[andygarfield]

Here is an example of a real feature.

SELECT MDSYS.SDO_GEOMETRY(
    3001,
    NULL,
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(
        1,1,1,4,1,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ),
    MDSYS.SDO_ORDINATE_ARRAY(
        480736.567,10853969.692,0,0.998807402795312,-0.0488238888381834,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
    ) SHAPE
FROM DUAL

I got my previous simple case to work, but shouldn't there be a method on *goracle.Data that can access the array inside the object? I get <null> back when I try to access SDO_ORDINATE_ARRAY with Data.GetObject().

[tgulacsi]

feb7ecd prints those numbers of SDO_ORDINATES, but the API is horrible. Shall we automate the Get of collection as an array somehow?

Or making an Object.Get(name) (interface{}, error) that automates the use of goracle.Data be enough?

[andygarfield]

Object.Get(name) (interface{}, error) seems like it would be a good option, in my opinion.