Golang SQL查询变量替换

Question

I have sql query that needs variable substitution for better consumption of my go-kit service.

I have dep & org as user inputs which are part of my rest service, for instance: dep = 'abc' and org = 'def'.

I've tried few things like:

rows, err := db.Query(
    "select name from table where department='&dep' and organisation='&org'",
)

And:

rows, err := db.Query(
    "select name from table where department=? and organisation=?", dep , org,
)

That led to error: sql: statement expects 0 inputs; got 2

Only hard-coded values work and substitution fails .

I haven't found much help from oracle blogs regarding this and wondering if there is any way to approach this.

Answer 1

Parameter Placeholder Syntax (reference: http://go-database-sql.org/prepared.html )

The syntax for placeholder parameters in prepared statements is database-specific. For example, comparing MySQL, PostgreSQL, and Oracle:

MySQL               PostgreSQL            Oracle
=====               ==========            ====== 
WHERE col = ?       WHERE col = $1        WHERE col = :col 
VALUES(?, ?, ?)     VALUES($1, $2, $3)    VALUES(:val1, :val2, :val3)

For oracle you need to use :dep, :org as placeholders.

Answer 2

As @dakait stated, on your prepare statement you should use : placeholders.

So, for completeness, you would get it working with something like:

package main

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

// Output is an example struct
type Output struct {
    Name string
}

const (
    dep = "abc"
    org = "def"
)

func main() {

    query := "SELECT name from table WHERE department= :1 and organisation = :2"

    q, err := db.Prepare(query)
    if err != nil {
        log.Fatal(err)
    }

    defer q.Close()

    var out Output

    if err := q.QueryRow(dep, org).Scan(&out.Name); err != nil {
        log.Fatal(err)
    }

    fmt.Println(out.Name)

}