Golang的mssql驱动返回了”mssql: Invalid object name”错误。

huangapple go评论94阅读模式
英文:

Golang mssql driver returns "mssql: Invalid object name"

问题

在我的应用程序中,我有一个全局作用域的变量:

var db *sql.DB

稍后会使用以下代码调用它:

slcstrSource, slcint64Timestamp, slcstrContent, err := DB_functions.GetContent(db)
            if err != nil {
                 fmt.Println("Error: " + err.Error())
            }

GetContent函数如下:

func GetContent(db *sql.DB) ([]string, []int64, []string, error) {

    var slcstrContent []string
    var slcint64Timestamp []int64
    var slcstrSource []string

    // 执行查询
    rows, err := db.Query("SELECT source, timestamp, content FROM MyDatabase.MyTable")
    if err != nil {
            return slcstrSource, slcint64Timestamp, slcstrContent, err
    }
    defer rows.Close()

    for rows.Next() {

            // 用于存储列中内容的变量
            var source, content string
            var timestamp int64

            // 获取查询结果
            err := rows.Scan(&source, &timestamp, &content)
            if err != nil {
                    return slcstrSource, slcint64Timestamp, slcstrContent, err
            }

            // 将结果添加到最终返回给调用者的切片中
            slcstrSource = append(slcstrSource, source)
            slcstrContent = append(slcstrContent, content)
            slcint64Timestamp = append(slcint64Timestamp, timestamp)
    }

    return slcstrSource, slcint64Timestamp, slcstrContent, nil
}

当我运行应用程序并执行这些代码段时,我得到一个错误:

Error: mssql: Invalid object name 'MyDatabase.MyTable'.

当我使用db.Ping()测试数据库连接时,似乎是正常的。根据我的初步分析,错误发生在查询语句处,但我找不到问题所在。我检查了数据库,确实有一个名为MyDatabase的数据库,其中包含一个名为MyTable的表,并且该表中有这三列的信息...

在执行查询之前或者在执行查询时,我是否漏掉了什么?

英文:

In an application I have a globally scoped

var db *sql.DB

that is later called with

slcstrSource, slcint64Timestamp, slcstrContent, err := DB_functions.GetContent(db)
            if err != nil {
                 fmt.Println("Error: " + err.Error())
            }

GetContent is this:

func GetContent(db *sql.DB) ([]string, []int64, []string, error) {

    var slcstrContent []string
    var slcint64Timestamp []int64
    var slcstrSource []string

    // Run the query
    rows, err := db.Query("SELECT source, timestamp, content FROM MyDatabase.MyTable")
    if err != nil {
            return slcstrSource, slcint64Timestamp, slcstrContent, err
    }
    defer rows.Close()

    for rows.Next() {

            // Holding variables for the content in the columns
            var source, content string
            var timestamp int64

            // Get the results of the query
            err := rows.Scan(&source, &timestamp, &content)
            if err != nil {
                    return slcstrSource, slcint64Timestamp, slcstrContent, err
            }

            // Append them into the slices that will eventually be returned to the caller
            slcstrSource = append(slcstrSource, source)
            slcstrContent = append(slcstrContent, content)
            slcint64Timestamp = append(slcint64Timestamp, timestamp)
    }

    return slcstrSource, slcint64Timestamp, slcstrContent, nil
}

When I run the application and these sections of code are hit, I get an:

Error: mssql: Invalid object name 'MyDatabase.MyTable'.

When I db.Ping() the database, it seems to work. From what I've narrowed down the error is happening right at the query, but I can't find what's wrong. I checked the database and there is a database called MyDatabase with a table called MyTable and the table has information in those three columns...

Is there something I'm missing before making the query, or in making the query?

答案1

得分: 4

我检查了数据库,发现有一个名为MyDatabase的数据库,其中有一个名为MyTable的表,该表在这三个列中有信息...

看起来驱动程序的工作正常。要在SQL Server中查询表,您应该使用[Database].[Schema].[TableName]的格式。如果您没有为表定义特定的模式名称,则默认情况下将在dbo模式下创建它。
话虽如此,在查询中您实际上不需要指定数据库名称。您可以在连接字符串中定义它。我不确定您如何定义连接详细信息,但请参考以下内容,并根据您的需求进行相应调整。

var (
    debug = flag.Bool("debug", false, "启用调试")
    password = flag.String("password", "mypwd", "数据库密码")
    port *int = flag.Int("port", 1433, "数据库端口")
    server = flag.String("server", "MyServer", "数据库服务器")
    user = flag.String("user", "MyUser", "数据库用户")
    connStr = fmt.Sprintf("server=%s;Initial Catalog=MySchema;userid=%s;password=%s;port=%d", *server, *user, *password, *port)
)

func main() {
    db, err := sql.Open("mssql", connStr)
}

然后,您可以像这样查询您的表:

rows, err := db.Query("SELECT source, timestamp, content FROM MySchema.MyTable")
英文:

> I checked the database and there is a database called MyDatabase with
> a table called MyTable and the table has information in those three
> columns...

It seems like the driver is working just like it should. In order to query a table in SQL Server you should use [Database].[Schema].[TableName]. If you have not defined a particular schema name for your table then it will be created under the dbo schema by default.
In saying that you don't really need to specify the database name in your query. You rather define that on the connection string. I'm not sure how you have defined your connection details but have a look at the below and adapt accordingly to your needs.

var (
    debug = flag.Bool("debug", false, "enable debugging")
    password = flag.String("password", "mypwd", "the database password")
    port *int = flag.Int("port", 1433, "the database port")
    server = flag.String("server", "MyServer", "the database server")
    user = flag.String("user", "MyUser", "the database user")
    connStr = fmt.Sprintf("server=%s;Initial Catalog=MySchema;userid=%s;password=%s;port=%d", *server, *user, *password, *port)
        )
    
    func main() {
        db, err := sql.Open("mssql", connStr)
    }

Then you can query your table like this:

rows, err := db.Query("SELECT source, timestamp, content FROM MySchema.MyTable")

huangapple
  • 本文由 发表于 2015年8月21日 01:50:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/32125030.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定