mattn / go-adodb

Microsoft ActiveX Object DataBase driver for go that using exp/sql
http://mattn.kaoriya.net/
MIT License
142 stars 36 forks source link

WinCC OLE DB Provider error executing query #58

Closed badsector998 closed 2 years ago

badsector998 commented 2 years ago

So i have been trying to implement this module to access a database which using WinCC OLE DB Provider(Page 22 to 25). I can connect to the database using open method and i also successfully ping the database, but when i execute the query it returns error.

here's the query.

TAG:R,'SystemArchive\\1511_AT_1379A/MS.PV_Out#Value','2022-02-08 07:35:00.000', '2022-02-08 07:40:00.000'

and here's the error log from the program i made.

Test Connection String : Provider=WinCCOLEDBProvider.1;Persist Security Info=False;User ID="";Data Source=10.1.1.1\WINCC;Catalog=CC_OS_1__21_12_14_16_25_11R;Mode=Read;Location="";Mode=Read;Extended Properties=""

Sql Driver : [adodb]

Ping Successful!

Test Query : TAG:R,'SystemArchive\1511_AT_1379A/MS.PV_Out#Value','2022-02-08 07:35:00.000', '2022-02-08 07:40:00.000' Error Preparing Statement Exception occurred. (Provider cannot derive parameter information and SetParameterInfo has not been called.)

panic: runtime error: invalid memory address or nil pointer dereference [signal 0xc0000005 code=0x1 addr=0x38 pc=0x864d82]

goroutine 1 [running]: database/sql.(Stmt).QueryContext(0x0, {0x8de310, 0xc00009e010}, {0x0, 0x0, 0x0}) C:/Program Files/Go/src/database/sql/sql.go:2726 +0x82 database/sql.(Stmt).Query(...) C:/Program Files/Go/src/database/sql/sql.go:2784 main.main() D:/tugas/Pagawean/MusaGreen/Repo/adodb-wincc-oledb/main.go:63 +0x45f

The point i take here is the provider cannot derive parameter information and the set parameter info has not been called. I have been trying to search across webs but i still havent managed to figure what the error exactly means and what the provider actually wants. Most cases i found is about the provider version x86 vs x64, which people prefer to use x86 and there is another one saying the error occured because of the provider bug so it needs to set the cursor within "ADODB.Connection" object. I have tried the second one by forking this repo and modify the Open() method by adding this line

_, err = oleutil.PutProperty(db, "CursorLocation", 3)
if err != nil {
    return nil, err
}
val, err := oleutil.GetProperty(db, "Parameters")
if err != nil {
    return nil, err
}
dbrc := val.ToIDispatch()
val.Clear()

then i return the dbrc on return &AdodbConn{db: dbrc}, nil.

Still no luck this far, is there anyone can point something out? is there anything i miss here? kind regard thank you :)

mattn commented 2 years ago

Please try to git pull latest changes and do

import (
    ...

    "github.com/mattn/go-adodb"
)

func main() {
    sql.Register("adodb_with_cursorlocation", &adodb.AdodbDriver{
        CursorLocation: 3,
    })
    db, err := sql.Open("v", "Provider="+provider+";Data Source="+f+";")
    if err != nil {
        fmt.Println("open", err)
        return
    }
    ...
}
badsector998 commented 2 years ago

Wonderful. The set cursor location method you added made the query execution executed successfully. Would you be so kind to elaborate more about your library? I mean executing the query would be the second step, next thing is i have to prepare an "ADODB.RecordSet" object to contain the query result. Does the db.Query() method can handle the query result or support as "ADODB.RecordSet" ?

for row.Next() {
    var (
        valueId   int
        timeStamp string
        realValue string
        quality   string
        flags     string
    )
    // var valueName string
    err = row.Scan(&valueId, &timeStamp, &realValue, &quality, &flags)
    if err != nil {
        fmt.Println("Row Error : ", err)
    }
    fmt.Println(valueId, timeStamp, realValue, quality, flags)
}

I already provided above line in my code to contain the values but the fmt.Println doesnt return any result though.

badsector998 commented 2 years ago

I tried to declare my own ADODB.Recordset but from go-ole oleutils.CreateObject("ADODB.RecordSet"), it makes the variable type to become ole.IUknown type. Of course i cannot store sql.Rows() result into ole.IUnknown, since the compiler itself complains about it. I also tried to study from your functions, then again i still have no idea of how did you make these ole based type into standard sql.

Edit : So I also have been playing around a little bit using "go-ole" package. So i was wondering how can i convert a variable from *ole.IUnknown to *ole.VARIANT as for i want to create an ADODB.Recordset using oleutils.CreateObject("ADODB.RecordSet") then i want to use the variable to store return value from "Execute" command(or Query() method?

badsector998 commented 2 years ago

Sorry for late reply but the sql.query() doing just fine. Thank you :)