sql: 使用`preparex`和`context.context`预期8个参数,但实际得到了0个参数。

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

sql: expected 8 arguments, got 0 using `preparex` and `context.context`

问题

我的问题与那个问题无关。我的问题主要是关于preparexctx。我已经使用db.NamedExec完成了实现,我的代码对这些都有效。我在这里尝试做的是理解context.contextpreparex,并使用这两个进行实现。

代码片段:

model.go

type User struct {
    ID        int       `db:"id" json:"id"`
    CreatedAt time.Time `db:"created_at" json:"created_at"`
    UpdatedAt time.Time `db:"updated_at" json:"updated_at"`
    DeletedAt time.Time `db:"deleted_at" json:"deleted_at"`
    Username  string    `db:"username" json:"username"`
    Password  string    `db:"password" json:"password"`
    FirstName string    `db:"first_name" json:"first_name"`
    LastName  string    `db:"last_name" json:"last_name"`
    Phone     string    `db:"phone" json:"phone"`
    Status    bool      `db:"status" json:"status"`
    Addrs     []UserAddress
}

查询:

queryInsertUserData = `
    INSERT INTO users (id, created_at, updated_at, deleted_at, username, password, first_name, last_name, phone, status) VALUES($1, now(), now(), $2, $3, $4, $5, $6, $7, $8)
`

type queries struct {
    insertUserData,
    insertAddrData *sqlx.Stmt
}

// prepareStatement是一个用于准备sqlx语句的方法
func (queries *queries) prepareStatement(db *sqlx.DB, query string) (*sqlx.Stmt, error) {
    stmt, err := db.Preparex(query) //https://go.dev/doc/database/prepared-statements
    if err != nil {
        return nil, err
    }

    return stmt, err
}

constructUserData, err := queries.prepareStatement(db, queryInsertUserData)
queries.insertUserData = constructUserData

实现:

// 插入用户数据
func (postgres *Postgres) InsertUserData(ctx context.Context) (*entity.User, error) {
    c := entity.User{}

    err := postgres.queries.insertUserData.SelectContext(ctx, &c) //<---这里
    if err != nil {
        return nil, err
    }
    return &c, nil
}

我的ctx是:

ctx = context.WithValue(ctx, "ID", 1)
ctx = context.WithValue(ctx, "Username", "John")
ctx = context.WithValue(ctx, "Password", "pass")

当我传递给postgres.queries.insertUserData.SelectContext(ctx, &c)

我得到:sql: expected 8 arguments, got 0

为什么它说得到了0个参数?有人可以帮我解决这个问题吗?如何传递ctx并提供插入查询值?

英文:

My question is not related to that one. My question was mostly on preparex and ctx. I have already done with the implementation using the db.NamedExec and my code is working for those. What I am trying to do here is to understand context.context and preparex. Implement using these two.

CODE SNIPPET:

model.go

type User struct {
	ID        int	    `db:&quot;id&quot; json:&quot;id&quot;`
	CreatedAt time.Time `db:&quot;created_at&quot; json:&quot;created_at&quot;`
	UpdatedAt time.Time `db:&quot;updated_at&quot; json:&quot;updated_at&quot;`
	DeletedAt time.Time `db:&quot;deleted_at&quot; json:&quot;deleted_at&quot;`
	Username  string	`db:&quot;username&quot; json:&quot;username&quot;`
	Password  string	`db:&quot;password&quot; json:&quot;password&quot;`
	FirstName string	`db:&quot;first_name&quot; json:&quot;first_name&quot;`
	LastName  string	`db:&quot;last_name&quot; json:&quot;last_name&quot;`
	Phone     string	`db:&quot;phone&quot; json:&quot;phone&quot;`
	Status bool			`db:&quot;status&quot; json:&quot;status&quot;`
	Addrs  []UserAddress
}

Query:

queryInsertUserData = `
    INSERT INTO users (id, created_at, updated_at, deleted_at, username, password, first_name, last_name, phone, status) VALUES($1, now(), now(), $2, $3, $4, $5, $6, $7, $8)
`

type queries struct {
	insertUserData, 
	insertAddrData *sqlx.Stmt
}

//prepareStatement is a method for preparing sqlx statement
func (queries *queries) prepareStatement(db *sqlx.DB, query string) (*sqlx.Stmt, error) {
	stmt, err := db.Preparex(query) //https://go.dev/doc/database/prepared-statements
	if err != nil {
		return nil, err
	}

	return stmt, err
}

constructUserData, err := queries.prepareStatement(db, queryInsertUserData)
queries.insertUserData = constructUserData

Implementation:

// Insert User data
func (postgres *Postgres) InsertUserData(ctx context.Context) (*entity.User, error) {
	c := entity.User{}

	err := postgres.queries.insertUserData.SelectContext(ctx, &amp;c) //&lt;---here
	if err != nil {
		return nil, err
	}
	return &amp;c, nil
}

my ctx is :

ctx = context.WithValue(ctx, &quot;ID&quot;, 1)
ctx = context.WithValue(ctx, &quot;Username&quot;, &quot;John&quot;)
ctx = context.WithValue(ctx, &quot;Password&quot;, &quot;pass&quot;)

when I am passing to postgres.queries.insertUserData.SelectContext(ctx, &amp;c)

I am getting: sql: expected 8 arguments, got 0

why it is saying got 0? Can anyone help me with this? How to pass ctx and provide the insert query values?

答案1

得分: 1

我没有理解你的代码结构,但从主要的上下文(插入查询)来看,你应该像这样做:

package main

import (
	"context"
	"database/sql"
	"log"
)

var db *sql.DB

type User struct {
    ID        int       `db:"id" json:"id"`
    CreatedAt time.Time `db:"created_at" json:"created_at"`
    UpdatedAt time.Time `db:"updated_at" json:"updated_at"`
    DeletedAt time.Time `db:"deleted_at" json:"deleted_at"`
    Username  string    `db:"username" json:"username"`
    Password  string    `db:"password" json:"password"`
    FirstName string    `db:"first_name" json:"first_name"`
    LastName  string    `db:"last_name" json:"last_name"`
    Phone     string    `db:"phone" json:"phone"`
    Status    bool      `db:"status" json:"status"`
    Addrs     []UserAddress
}

func main() {
	users := []User {
		{...User 1 data...},
		{...User 2 data...},
	}

	stmt, err := db.Prepare("INSERT INTO users (id, created_at, updated_at, deleted_at, username, password, first_name, last_name, phone, status) VALUES($1, now(), now(), $2, $3, $4, $5, $6, $7, $8)")
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close() // Prepared statements take up server resources and should be closed after use.

	for id, user := range users {
		if _, err := stmt.Exec(id+1, user.Username, user.Password,... other data); err != nil {
			log.Fatal(err)
		}
	}
}

此外,你可以使用Gorm这个强大的包来使用所有关系型数据库。

英文:

I didn't get the structure of your code, but from the main context (inserting query) you should do something like this:

package main

import (
	&quot;context&quot;
	&quot;database/sql&quot;
	&quot;log&quot;
)

var db *sql.DB
type User struct {
    ID        int       `db:&quot;id&quot; json:&quot;id&quot;`
    CreatedAt time.Time `db:&quot;created_at&quot; json:&quot;created_at&quot;`
    UpdatedAt time.Time `db:&quot;updated_at&quot; json:&quot;updated_at&quot;`
    DeletedAt time.Time `db:&quot;deleted_at&quot; json:&quot;deleted_at&quot;`
    Username  string    `db:&quot;username&quot; json:&quot;username&quot;`
    Password  string    `db:&quot;password&quot; json:&quot;password&quot;`
    FirstName string    `db:&quot;first_name&quot; json:&quot;first_name&quot;`
    LastName  string    `db:&quot;last_name&quot; json:&quot;last_name&quot;`
    Phone     string    `db:&quot;phone&quot; json:&quot;phone&quot;`
    Status bool         `db:&quot;status&quot; json:&quot;status&quot;`
    Addrs  []UserAddress
}
func main() {
	users := []User {
		{...User 1 data...},
		{...User 2 data...},
	}

	stmt, err := db.Prepare(&quot;INSERT INTO users (id, created_at, updated_at, deleted_at, username, password, first_name, last_name, phone, status) VALUES($1, now(), now(), $2, $3, $4, $5, $6, $7, $8)&quot;)
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close() // Prepared statements take up server resources and should be closed after use.

	for id, user := range users {
		if _, err := stmt.Exec(id+1, user.Username, user.Password,... other data); err != nil {
			log.Fatal(err)
		}
	}
}

also, you can use the Gorm powerful package for using all relational databases.

答案2

得分: 1

我使用这个助手进行插入操作。

使用以下查询语法:

INSERT INTO checks (
	status) VALUES (:status)
	returning id;

示例结构体

	type Row struct {
		Status string `db:"status"`
	}
// Insert函数使用查询SQL命令将行插入表中
// 表仅用于日志记录,实际表名在查询中定义
// 函数期望具有命名参数的查询
func Insert(ctx context.Context, row interface{}, query string, table string, tx *sqlx.Tx) (int64, error) {
	// 将命名查询转换为本机参数格式
	query, args, err := tx.BindNamed(query, row)
	if err != nil {
		return 0, fmt.Errorf("无法绑定参数插入到表 %q 中:%w", table, err)
	}

	var id struct {
		Val int64 `db:"id"`
	}

	err = sqlx.GetContext(ctx, tx, &id, query, args...)
	if err != nil {
		return 0, fmt.Errorf("无法插入到表 %q 中:%w", table, err)
	}

	return id.Val, nil
}
英文:

I use this helper for inserts.

Works with query syntax as follows:

INSERT INTO checks (
	status) VALUES (:status)
	returning id;

Sample struct

	type Row struct {
		Status string `db:&quot;status&quot;`
	}
// Insert inserts row into table using query SQL command
// table used only for loging, actual table name defined in query
// function expects Query with named parameters
func Insert(ctx context.Context, row interface{}, query string, table string, tx *sqlx.Tx) (int64, error) {
	// convert named query to native parameters format
	query, args, err := tx.BindNamed(query, row)
	if err != nil {
		return 0, fmt.Errorf(&quot;cannot bind parameters for insert into %q: %w&quot;, table, err)
	}

	var id struct {
		Val int64 `db:&quot;id&quot;`
	}

	err = sqlx.GetContext(ctx, tx, &amp;id, query, args...)
	if err != nil {
		return 0, fmt.Errorf(&quot;cannot insert into %q: %w&quot;, table, err)
	}

	return id.Val, nil
}

huangapple
  • 本文由 发表于 2022年6月6日 12:30:36
  • 转载请务必保留本文链接:https://go.coder-hub.com/72513080.html
匿名

发表评论

匿名网友

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

确定