current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite

I'm trying to figure out what the pattern is for using named parameters in go's built-in database/sql package. I looked at the oracle driver, but it seems like just a wrapper for the C library. Have people solved this in an elegant way? So far I've just worked around the problem by putting {0}, {1} as the parameters in the unit tests, but it sure would be nice to be able to use them normally as a map[string]interface{} or something. Does anyone have an idea or an implementation that seems idiomatic?

For reference, here is a test:

db := testConn()
stmt, err := db.Prepare("return {0} as int1, {1} as int2")
if err != nil {
   t.Fatal(err)
}
rows, err := stmt.Query(123, 456)
if err != nil {
   t.Fatal(err)
}
rows.Next()

var test int
var test2 int
err = rows.Scan(&test, &test2)
if err != nil {
   t.Fatal(err)
}
if test != 123 {
   t.Fatal("test != 123;", test)
}
if test2 != 456 {
   t.Fatal("test2 != 456;", test2)
}

And what I'm doing in Query is:

func (stmt *cypherStmt) Query(args []driver.Value) (driver.Rows, error) {
   cyphReq := cypherRequest{
      Query: stmt.query,
   }
   if len(args) > 0 {
      cyphReq.Params = make(map[string]interface{})
   }
   for idx, e := range args {
      cyphReq.Params[strconv.Itoa(idx)] = e
   }
...
share|improve this question
    
The existing answers seem to assume that you're writing a client, but it looks to me like you're writing a driver. Is that right? –  andybalholm Jan 4 at 18:18
    
Yep: github.com/wfreeman/cq –  Wes Freeman Jan 4 at 19:31
    
The database/sql package is built around positional parameters, not named parameters. So any workaround to make it use named parameters would be unidiomatic almost by definition. –  andybalholm Jan 4 at 19:51
    
I'm coming to that realization. It's been a month since I started the project. Still, the query language implemented uses named parameters (and that's the idiomatic way). The main problem with implementation is that they need to be pushed through the driver.Value type, as opposed to interface{}. –  Wes Freeman Jan 4 at 20:10
    
I don't know what's exactly behind that db.Prepare method, but as far as I undestand, you are trying to create an SQL query object and add paramenters in OO manner, so you can pass values to be used in WHERE clause (as an example). There is a stored procedure in MS SQL called sp_executesql that can take SQL statement with parameters' values marked by placeholders, and then populate defined parameters' values from variables. Oracle should have the same thing. If this is at all close to what your are looking for, I can have a look further into this. –  Stoleg Jan 8 at 12:38

3 Answers 3

up vote 1 down vote accepted
+150

It would be possible to create a map[string]interface{} type that implements driver.Valuer{} to serialize it as a []byte, and then convert it back in the driver.

But that would be inefficient and unidiomatic. Since your driver would then be used in a nonstandard way anyway, it would probably be better to just forget about database/sql and write a package with a totally custom interface.

share|improve this answer
up vote 7 down vote

I'm using wrapper on top of database/sql called sqlx https://github.com/jmoiron/sqlx You can check here how he did it.

Example on how to select into a tuple

type Person struct {
    FirstName string `db:"first_name"`
    LastName  string `db:"last_name"`
    Email     string
}
jason = Person{}
err = db.Get(&jason, "SELECT * FROM person WHERE first_name=$1", "Jason")
fmt.Printf("%#v\n", jason)
// Person{FirstName:"Jason", LastName:"Moiron", Email:"[email protected]"}

Example on how to insert a tuple

dude := Person{
    FirstName:"Jason", 
    LastName:"Moiron", 
    Email:"[email protected]"
}
 _, err = db.NamedExec(`INSERT INTO person (first_name,last_name,email) VALUES (:first,:last,:email)`, dude)
share|improve this answer
    
My question is kind of reverse--how to put parameters in as a map. –  Wes Freeman Dec 8 '13 at 18:57
    
I've edited a question with a details on how to insert a tuple –  Goranek Dec 8 '13 at 19:03
    
Cool--I'm still hoping for someone to show a way with the native database/sql package. –  Wes Freeman Dec 8 '13 at 19:50
up vote 4 down vote

As far as I know, no driver natively provides for named parameters. I personally use gorp which allows you to bind queries from structs or maps: 

_, err = dbm.Select(&users,
    "select * from PersistentUser where mykey = :Key",
    map[string]interface{}{
        "Key": 43,
    }
)

or

_, err = dbm.Select(&users,
    "select * from PersistentUser where mykey = :Key",
    User{Key: 43},
)
share|improve this answer
    
Thanks for mentioning--that's pretty slick. I'm hoping for a way with the native database/sql package. –  Wes Freeman Dec 8 '13 at 19:51
3  
You cannot do it with native database/sql –  Goranek Dec 9 '13 at 11:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.