duanfen7676
2018-05-05 07:52
浏览 242

Sqlx使用准备好的语句获取

I am trying to fetch some data from postgress table using prepared statements

If I try with database.Get() everything is returned.

Table:

create table accounts
(
  id            bigserial not null
    constraint accounts_pkey
    primary key,
  identificator text      not null,
  password      text      not null,
  salt          text      not null,
  type          smallint  not null,
  level         smallint  not null,
  created_at    timestamp not null,
  updated       timestamp not null,
  expiry_date   timestamp,
  qr_key        text
);

Account struct:

type Account struct {
    ID            string `db:"id"`
    Identificator string `db:"identificator"`

    Password   string         `db:"password"`
    Salt       string         `db:"salt"`
    Type       int            `db:"type"`
    Level      int            `db:"level"`
    ExpiryDate time.Time      `db:"expiry_date"`
    CreatedAt  time.Time      `db:"created_at"`
    UpdateAt   time.Time      `db:"updated_at"`
    QrKey      sql.NullString `db:"qr_key"`
}

BTW i tried using ? instead of $1 & $2

stmt, err := database.Preparex(`SELECT * FROM accounts where identificator = $1 and type = $2`)

if err != nil {
    panic(err)
}
accounts := []account.Account{}
err = stmt.Get(&accounts, "asd", 123)
if err != nil {
    panic(err)
}

The error I get is

"errorMessage": "scannable dest type slice with \u003e1 columns (10) in result",

In the table there are no records I tried to remove all fields except the ID from Account (struct), however it does not work.

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douzhi2760 2018-05-05 08:05
    已采纳

    Documentation for sqlx described Get and Select as:

    Get and Select use rows.Scan on scannable types and rows.StructScan on non-scannable types. They are roughly analagous to QueryRow and Query, where Get is useful for fetching a single result and scanning it, and Select is useful for fetching a slice of results:

    For fetching a single record use Get.

    stmt, err := database.Preparex(`SELECT * FROM accounts where identificator = $1 and type = $2`)
    var account Account
    err = stmt.Get(&account, "asd", 123)
    

    If your query returns more than a single record use Select with statement as:

    stmt, err := database.Preparex(`SELECT * FROM accounts where identificator = $1 and type = $2`)
    var accounts []Account
    err = stmt.Select(&accounts, "asd", 123)
    

    In your case if you use stmt.Select instead if stmt.Get. It will work.

    已采纳该答案
    打赏 评论

相关推荐 更多相似问题