How to append URI as string in SQL query

Question

Looks simple but I unable to make it happen. When browsing domain.com/post/1, it should show data from row id which value 1.

Row id is integer (int4).

Below the the codes, which is not working:

package main

import "fmt"
import "github.com/go-martini/martini"
import "net/http"
import "database/sql"
import _ "github.com/lib/pq"

func SetupDB() *sql.DB {
  db, err := sql.Open("postgres", "user=postgres password=apassword dbname=lesson4 sslmode=disable")
  PanicIf(err)
  return db
}

func PanicIf(err error) {
  if err != nil {
    panic(err)
  }
}

func main() {
  m := martini.Classic()
  m.Map(SetupDB())

  m.Get("/post/:idnumber", func(rw http.ResponseWriter, r *http.Request, db *sql.DB) {

    rows, err := db.Query(`SELECT title, author, description FROM books WHERE id = params["idnumber"]`)
    PanicIf(err)
    defer rows.Close()

    var title, author, description string
    for rows.Next() {
      err:= rows.Scan(&title, &author, &description)
      PanicIf(err)
      fmt.Fprintf(rw, "Title: %s\nAuthor: %s\nDescription: %s\n\n",
        title, author, description)
    }

  })

  m.Run()
}

When you say not working what do you mean by that? Is it not finding the row in the sql? — Leo Correa, Apr 22 '15 at 16:51
I always got it not working and giving error. Have tried various way. I just have no idea — apasajja, Apr 22 '15 at 17:32

Leo Correa · Accepted Answer · 2015-04-22T17:45:15.080

Part of your issue is that you're using the string params["idnumber"] as part of the SQL query

db.Query(`SELECT title, author, description FROM books WHERE id = params["idnumber"]`)

That will look for a book where the id equals params["idnumber"] string.

What you need to do is use placeholders and the arguments according to http://golang.org/pkg/database/sql/#DB.Query

In this case your query should be

db.Query("SELECT title, author, description FROM books WHERE id=$1", params["idnumber"])

That should solve the issue I think you're having. However, until you actually update your question with the actual issue you're having I won't know.

Update

The error you're getting with undefined: params is because you don't have a params object in scope.

I'd suggest reading how martini works in regards of getting the arguments out of the route. https://github.com/go-martini/martini#routing

I forgot to add `params martini.Params`. Your codes works now. Thanks — apasajja, Apr 22 '15 at 17:44

evanmcdonnal · Answer 2 · 2015-04-22T17:50:14.387

1

I think the problem is that you're using the variable name in your string literal query, you want it's value there instead.

Try changing this;

rows, err := db.Query(`SELECT title, author, description FROM books WHERE id = params["idnumber"]`)

to;

rows, err := db.Query("SELECT title, author, description FROM books WHERE id =$1", params["idnumber"])

You could have other issues beyond that but given you're not forming the query correctly I wouldn't expect you to get back the results you want.

edited Apr 22 '15 at 17:50

answered Apr 22 '15 at 17:01

evanmcdonnal

46,131
16
104
115

Also got error like Leo Correa answer. Error on line 27: undefined: params – apasajja Apr 22 '15 at 17:36
@apasajja yeah, well you're just using params out of the blue without declaring it anywhere. What is `m`? You're looking for request url.Values which is a dictionary of key value pairs and you'll need to put in the desired string format prior to passing it into your Query. – evanmcdonnal Apr 22 '15 at 17:41
Yes. I forgot to add `params martini.Params`. Unfortunately your codes not working after params declaration added, while Leo Correa codes works. `m` is martini. Yes, appending the URI as part database query (SQL). – apasajja Apr 22 '15 at 17:48
@apasajja I guess I have the wrong token in my query string ( the ? should be $1 ), I just followed the go docs example. – evanmcdonnal Apr 22 '15 at 17:49

How to append URI as string in SQL query

2 Answers2