Golang SQL query variable substituion

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.

The right way is db.Query() or db.Prepare() with stmt.Exec(). But db.Query() does not return only error, whats is your driver? Please, complete with a sample of code with db variable creation. Reference: golang.org/pkg/database/sql/#DB.Query — Rodrigo Brito
– Rodrigo Brito, Commented Sep 15, 2018 at 0:00
func NewOracleConnection(cfg config.Config, log log.Logger) (Connection, error) { var err error o := &Oracle{ Driver: cfg.DB.Driver, ConnectString: cfg.DB.ConnectString, logger: log, } o.Db, err = sql.Open(o.Driver, o.ConnectString) if err != nil { log.Log("event", "connecting to database", "err", err.Error()) } return o, err } — Chakrapani Velugubantla
– Chakrapani Velugubantla, Commented Sep 15, 2018 at 4:20
i have tried db.query & Exec. what's interesting is sqlplus client works fine for the same query with variable substitution but fails with this driver — Chakrapani Velugubantla
– Chakrapani Velugubantla, Commented Sep 15, 2018 at 4:21
For that driver it seems to be :N (github.com/mattn/go-oci8/blob/master/oci8Sql_test.go#L1149) where N is the position of the parameter. So in your case something like this should work: db.Query("select name from table where department = :1 and organisation = :2", dep, org) — mkopriva
– mkopriva, Commented Sep 15, 2018 at 8:18

dakait · Accepted Answer · 2018-09-15 05:15:25Z

26

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.

answered Sep 15, 2018 at 5:15

dakait

4205 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AndreaM16 · Accepted Answer · 2018-09-15 09:32:41Z

3

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)

}

answered Sep 15, 2018 at 9:32

AndreaM16

4,0055 gold badges37 silver badges78 bronze badges

Collectives™ on Stack Overflow

Golang SQL query variable substituion

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related