Query with parameter with golang and sqlserver driver

Question

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
    }
}

Are you sure you are using the mssql driver and not sqlserver? Both are in that package but the latter expects parameters in a different format. — steve v
– steve v, Commented Sep 1, 2017 at 15:00
I'm using sqlserver driver, I'm not on my computer right now so I'll try in a couple hours. — Antoine Thiry
– Antoine Thiry, Commented Sep 1, 2017 at 15:03

DDiamond · Accepted Answer · 2020-01-03 18:24:03Z

7

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"
)

edited Jan 3, 2020 at 18:24

DDiamond

1,6282 gold badges11 silver badges19 bronze badges

answered Sep 1, 2017 at 15:08

steve v

3,5501 gold badge20 silver badges25 bronze badges

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

1 Comment

Antoine Thiry Over a year ago

Thanks, I never mentioned the mssql driver btw, I mentioned sqlserver driver explicitly in my question actually, but thanks ! I'll try it when I'll have access to my computer.

Kenny Grant · Accepted Answer · 2017-09-01 14:54:14Z

2

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 = ?"

answered Sep 1, 2017 at 14:54

Kenny Grant

9,7132 gold badges35 silver badges47 bronze badges

3 Comments

Antoine Thiry Over a year ago

Still the same error, it's definitly a problem with the placeholder but I can't find any documentation on this since it's a community based package and not actively developed i think...

Kenny Grant Over a year ago

It looks like the driver claims it accepts the style you're trying to use for mssql but not sqlserver, perhaps check that too.

chovy Over a year ago

how do you pass the placeholder value?

LONG · Accepted Answer · 2017-09-01 14:59:13Z

1

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.

answered Sep 1, 2017 at 14:59

LONG

4,6302 gold badges21 silver badges41 bronze badges

Collectives™ on Stack Overflow

Query with parameter with golang and sqlserver driver

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related