英文:
Error when add a query parameter to postgres query
问题
当我写下这段代码时：
err := database.QueryRow("SELECT page_title,page_content,page_date FROM pages WHERE id=1").
Scan(&thisPage.Title, &thisPage.Content, &thisPage.Date)
一切都正常工作。但是我希望它不仅仅获取id=1的页面，而是可以动态地获取。
所以我写下了：
err := database.QueryRow("SELECT page_title,page_content,page_date FROM pages WHERE id=?", pageID).
Scan(&thisPage.Title, &thisPage.Content, &thisPage.Date)
但是我得到了一个错误：
GolangdProjects go run test.go
1
2017/04/13 11:29:57 Couldn't get page: 1
exit status 1
完整的代码如下：
package main
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
"github.com/gorilla/mux"
"log"
"net/http"
)
const (
DBHost = "localhost"
DBPort = ":5432"
DBUser = "nirgalon"
DBPass = ""
DBDbase = "cms"
PORT = ":8080"
)
var database *sql.DB
type Page struct {
Title string
Content string
Date string
}
func ServePage(w http.ResponseWriter, r *http.Request) {
vars := mux.Vars(r)
pageID := vars["id"]
thisPage := Page{}
fmt.Println(pageID)
err := database.QueryRow("SELECT page_title,page_content,page_date FROM pages WHERE id=1").Scan(&thisPage.Title, &thisPage.Content, &thisPage.Date)
if err != nil {
log.Fatal("Couldn't get page: " + pageID)
log.Fatal(err.Error)
}
html := `<html><head><title>` + thisPage.Title + `</title></head><body><h1>` + thisPage.Title + `</h1><div>` + thisPage.Content + `</div></body></html>`
fmt.Fprintln(w, html)
}
func main() {
db, err := sql.Open("postgres", "user=nirgalon dbname=cms sslmode=disable")
if err != nil {
log.Println("Couldn't connnect to db")
log.Fatal(err)
}
database = db
routes := mux.NewRouter()
routes.HandleFunc("/page/{id:[0-9]+}", ServePage)
http.Handle("/", routes)
http.ListenAndServe(PORT, nil)
}
英文:
When I write the code:
err := database.QueryRow("SELECT page_title,page_content,page_date FROM pages WHERE id=1").
Scan(&thisPage.Title, &thisPage.Content, &thisPage.Date)
Everything works fine. But I want it to not just get the page with id=1, but to be dynamic.
So I write:
err := database.QueryRow("SELECT page_title,page_content,page_date FROM pages WHERE id=?", pageID).
Scan(&thisPage.Title, &thisPage.Content, &thisPage.Date)
But I get an error:
GolangdProjects go run test.go
1
2017/04/13 11:29:57 Couldn't get page: 1
exit status 1
The full code:
package main
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
"github.com/gorilla/mux"
"log"
"net/http"
)
const (
DBHost = "localhost"
DBPort = ":5432"
DBUser = "nirgalon"
DBPass = ""
DBDbase = "cms"
PORT = ":8080"
)
var database *sql.DB
type Page struct {
Title string
Content string
Date string
}
func ServePage(w http.ResponseWriter, r *http.Request) {
vars := mux.Vars(r)
pageID := vars["id"]
thisPage := Page{}
fmt.Println(pageID)
err := database.QueryRow("SELECT page_title,page_content,page_date FROM pages WHERE id=1").Scan(&thisPage.Title, &thisPage.Content, &thisPage.Date)
if err != nil {
log.Fatal("Couldn't get page: " + pageID)
log.Fatal(err.Error)
}
html := `<html><head><title>` + thisPage.Title + `</title></head><body><h1>` + thisPage.Title + `</h1><div>` + thisPage.Content + `</div></body></html>`
fmt.Fprintln(w, html)
}
func main() {
db, err := sql.Open("postgres", "user=nirgalon dbname=cms sslmode=disable")
if err != nil {
log.Println("Couldn't connnect to db")
log.Fatal(err)
}
database = db
routes := mux.NewRouter()
routes.HandleFunc("/page/{id:[0-9]+}", ServePage)
http.Handle("/", routes)
http.ListenAndServe(PORT, nil)
}
答案1
得分: 1
psql驱动程序在参数中使用$1、$2等：
database.QueryRow("SELECT page_title,page_content,page_date FROM pages WHERE id = $1", pageID)
英文:
psql driver uses $1, $2, etc for params:
database.QueryRow("SELECT page_title,page_content,page_date FROM pages WHERE id = $1", pageID)
答案2
得分: 0
QueryRow接受类型为interface{}的可变参数。可能是pageID被插入为字符串（由mux.Vars返回），导致查询格式不正确，如下所示：
"SELECT page_title,page_content,page_date FROM pages WHERE id='1'"
尝试将pageID转换为整数：
id := vars["id"]
pageID := strconv.Atoi(id)
英文:
QueryRow takes var args of type interface{}. It could be that pageID is being interpolated as a string (as returned by mux.Vars), resulting in a query that's incorrectly formatted as:
"SELECT page_title,page_content,page_date FROM pages WHERE id='1'"
Try converting pageID to int:
id := vars["id"]
pageID := strconv.Atoi(id)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论