adodb方式连接sqlServer2k

require (
    github.com/go-ole/go-ole v1.2.4 // indirect
    github.com/mattn/go-adodb v0.0.1
    github.com/robfig/cron/v3 v3.0.1
    github.com/wonderivan/logger v1.0.0
    golang.org/x/net v0.0.0-20200904194848-62affa334b73 // indirect
)

package main

import (
    "database/sql"
    "flag"
    "fmt"
    "log"
    "strconv"

    _ "github.com/mattn/go-adodb"
    "github.com/robfig/cron/v3"
    "github.com/wonderivan/logger"
)

var (
    local    bool
    remoteIP string
    remoteDS string
    database string
)

func init() {
    flag.BoolVar(&local, "local", false, "set window connect.")
    flag.StringVar(&remoteIP, "remoteIP", "192.168.0.10", "set up remote mssql of ip.")
    flag.StringVar(&remoteDS, "remoteDS", "MSSQLSERVER", "set up remote mssql of datasource.")
    flag.StringVar(&database, "database", "drivertest", "set up remote mssql of database.")
}

type Mssql struct {
    *sql.DB
    dataSource string
    database   string
    windows    bool
    sa         *SA
}

type SA struct {
    user   string
    passwd string
    port   int
}

func NewMssql() *Mssql {
    mssql := new(Mssql)
    dataS := "localhost"
    if !local {
        dataS = fmt.Sprintf("%s\\%s", remoteIP, remoteDS)
    }

    mssql = &Mssql{
        // 如果数据库是默认实例（MSSQLSERVER）则直接使用IP，命名实例需要指明。
        // dataSource: "192.168.1.104\\MSSQLSERVER",
        dataSource: dataS,
        database:   database,
        // windows: true 为windows身份验证，false 必须设置sa账号和密码
        windows: local,
        sa: &SA{
            user:   "elink",
            passwd: "elink888",
            port:   1433,
        },
    }

    return mssql

}

func (m *Mssql) Open() error {
    config := fmt.Sprintf("Provider=SQLOLEDB;Initial Catalog=%s;Data Source=%s",
        m.database, m.dataSource)

    if m.windows {
        config = fmt.Sprintf("%s;Integrated Security=SSPI", config)
    } else {
        // sql 2000的端口写法和sql 2005以上的有所不同，在Data Source 后以逗号隔开。
        config = fmt.Sprintf("%s,%d;user id=%s;password=%s",
            config, m.sa.port, m.sa.user, m.sa.passwd)
    }

    var err error
    m.DB, err = sql.Open("adodb", config)
    fmt.Println(config)

    return err
}

func (m *Mssql) Select() {
    rows, err := m.Query("select uid, name from sysusers")
    if err != nil {
        fmt.Printf("select query err: %s\n", err)
    }
    i := 0
    for rows.Next() {
        var id, name string
        rows.Scan(&id, &name)
        fmt.Printf("id = %s, name = %s\n", id, name)
        i++
    }
    fmt.Println(i)
}

func main() {
    // flag.Parse()

    // m := NewMssql()
    // config1 := fmt.Sprintf("Provider=SQLOLEDB;Initial Catalog=%s;Data Source=%s",
    //     m.database, m.dataSource)

    // if m.windows {
    //     config1 = fmt.Sprintf("%s;Integrated Security=SSPI", config1)
    // } else {
    //     // sql 2000的端口写法和sql 2005以上的有所不同，在Data Source 后以逗号隔开。
    //     config1 = fmt.Sprintf("%s,%d;user id=%s;password=%s",
    //         config1, m.sa.port, m.sa.user, m.sa.passwd)
    // }
    // fmt.Println(config1)
    // err := mssql.Open()

    // checkError(err)

    // mssql.Select()

    // config := fmt.Sprintf("Provider=SQLOLEDB;Initial Catalog=%s;Data Source=%s\\MSSQLSERVER,%s;user id=%s;password=%s",
    //     "drivertest", "192.168.0.10", "1433", "elink", "elink888")
    config := fmt.Sprintf("Provider=SQLOLEDB;Initial Catalog=%s;Data Source=%s\\MSSQLSERVER,%s;user id=%s;password=%s",
        "znykt", "37.64.227.132", "1433", "elink", "elink888")
    DB, err := sql.Open("adodb", config)
    if err != nil {
        log.Fatal(err)
    }

    crontab := cron.New(cron.WithSeconds()) //精确到秒
    // 定时任务
    deviceStatusOnline_spec := "*/5 * * * * ?" //秒 分 时 日 月 周
    // 定义定时器调用的任务函数
    _, err = crontab.AddFunc(deviceStatusOnline_spec, func() {
        rows, err := DB.Query("select top 200 id from MYCARGOOUTRECORD order by id desc")
        if err != nil {
            fmt.Printf("select query err: %s\n", err)
        }
        i := 0
        for rows.Next() {
            // var id, name string
            // rows.Scan(&id, &name)
            // fmt.Printf("id = %s, name = %s\n", id, name)
            i++
        }
        logger.Info("结果数量：" + strconv.Itoa(i))
    })
    if err != nil {
        log.Fatal(err)
    }
    // 启动定时器
    crontab.Start()
    select {}
}

// func checkError(err error) {
//     if err != nil {
//         log.Fatal(err)
//     }
// }