CODEWITHSUNDEEP
sqlsql-serverrestgo
Antoine Thiry
Antoine Thiry

Reputation: 2442

Query with parameter with golang and sqlserver driver

I'm trying to get a specific item by ID from my sql server database. Here's my code :

var(
    allArticlesQry string = "SELECT * FROM Articles"
    findArticlesQry string = "SELECT * FROM Articles WHERE Id = ?1"
)

func FindArticle(w http.ResponseWriter, r *http.Request){
    vars := mux.Vars(r)
    var id = vars["id"]
    var article Article

    db := connect()
    defer db.Close()

    stmt, err := db.Prepare(findArticlesQry)    
    if err != nil{
        log.Fatal(err)
    }
    defer stmt.Close()

    err = stmt.QueryRow(id).Scan(&article.Title, &article.Description, &article.Body, &article.Id)
    if err != nil{
        log.Fatal(err)
    }

    w.Header().Set("Content-Type", "application/json; charset=UTF-8")
    w.WriteHeader(http.StatusOK)
    json.NewEncoder(w).Encode(u.HttpResp{Status: 200, Body: article})
}

I'm using this package for the sqlserver driver and it has this example who should work fine : db.Query("SELECT * FROM t WHERE a = ?3, b = ?2, c = ?1", "x", "y", "z")

But everytime I try to call this function it fails and returns : 2017/09/01 16:31:01 mssql: Incorrect syntax near '?'.

So I don't really understand why my query doesn't work..

EDIT

I tried another way, I removed the part where I prepare the query before executing and now it doesn't crash my server, I have a response but the problem is still the same : 

var row = db.QueryRow(findArticlesQry, id).Scan(&article.Title, &article.Description, &article.Body, &article.Id)

And the response : 

{
    "status": 200,
    "description": "",
    "body": {
        "Number": 102,
        "State": 1,
        "Class": 15,
        "Message": "Incorrect syntax near '?'.",
        "ServerName": "DESKTOP-DLROBC4\\LOCALHOST",
        "ProcName": "",
        "LineNo": 1
    }
}

Upvotes: 3

Views: 7554

Answers (3)

steve v
steve v

Reputation: 3540

Per your comment, you are using the sqlserver driver, not the mssql driver, so you are using the wrong parameter format. Per the documentation:

The sqlserver driver uses normal MS SQL Server syntax and expects parameters in the sql query to be in the form of either @Name or @p1 to @pN (ordinal position).

db.QueryContext(ctx, "select * from t where ID = @ID;", sql.Named("ID", 6))

You should therefore change your query to be:

var(
    allArticlesQry string = "SELECT * FROM Articles"
    findArticlesQry string = "SELECT * FROM Articles WHERE Id = @p1"
)

Upvotes: 6

LONG
LONG

Reputation: 4610

db.Query("SELECT * FROM t WHERE a = ?3, b = ?2, c = ?1", "x", "y", "z"), the question mark here works as placeholder, which later will be replaced with the value of 'x', 'y' and 'z' according to its order in the code during runtime.

But this:

SELECT * FROM Articles WHERE Id = ?1 is not a validate SQL statement, correct is to remove the question mark, or you could give a specific value to @Id, like:

SELECT * FROM Articles WHERE Id = @Id

In a short word, db.Query() could build your query using placeholder, but your findArticlesQry variable is storing plain SQL statement, that should follow the basic SQL grammar, ? is not allowed.

Upvotes: 1

Kenny Grant
Kenny Grant

Reputation: 9623

It seems to be a problem with the placeholders. Why not try just ? for your placeholders as you're not shifting the order or repeating them. So try this:

"SELECT * FROM Articles WHERE Id = ?"

Upvotes: 2

Related Questions