CODEWITHSUNDEEP
sqlsqlitego
struggling_learner
struggling_learner

Reputation: 1258

parameterizing SQL subquery in golang

Please consider this question.

I need to parameterize a SQL (sub) query in golang. Please consider the pseudo-code below or at https://go.dev/play/p/F-jZGEiDnsd

The hayStack details come to me in an string slice lookIn and can vary. I need to search for %eedl% (needle) in all these haystacks.

The code in the comment is how I currently handle it - I only parameterize the needle I am looking for.

How do I parameterize the hayStacks as well?

package main

import "fmt"

func main() {
    fmt.Println("Hello, 世界")
    lookIn := []string{"hayStack.1", "hayStack.2", "hayStack.3", "hayStack.4", "hayStack.5"}
    needle := "eedl"
    needle = "%" + needle + "%"

    for i := range lookIn {
        fmt.Println("lookIn", lookIn[i])
    }
    fmt.Println("needle", needle)

    /* this is how I currently do 
        txt := `select needle,tagTitle,Epoch from ( select needle,tagTitle,Epoch where hayStackName = "hayStack.1" or hayStackName = "hayStack.2" or hayStackName = "hayStack.3" or hayStackName = "hayStack.3" or hayStackName = "hayStack.4" or hayStackName = "hayStack.5" )  where tagTitle like ? order by tagTitle COLLATE NOCASE ASC ;`
        rows, err := sqliteDbPtr.Query(txt, needle)

        if err != nil {
            if err != sql.ErrNoRows {
                checkErr(err)
                panic(err)
            }
            return
        }
        defer rows.Close()
    */

}

Any input will be much appreciated.

Upvotes: 1

Views: 837

Answers (1)

mkopriva
mkopriva

Reputation: 38203

Using just the standard library you can collect the haystack arguments and the needle argument into a single slice and then pass that to the Query method.

For the SQL itself you can use the IN operator and generate its right hand side operand as a list of ? based on the number of haystacks.

args := make([]interface{}, len(lookIn)+1) // slice of all args
inRHS := "" // string of ?,... for the IN operator's right hand side
for i := range lookIn {
    args[i] = lookIn[i]
    inRHS += "?,"
}
args[len(args)-1] = needle   // add the last arg
inRHS = inRHS[:len(inRHS)-1] // remove last ","

query := `SELECT needle,tagTitle,Epoch
FROM some_table
WHERE hayStackName IN (` + inRHS + `)
AND tagTitle LIKE ?
ORDER BY tagTitle COLLATE NOCASE ASC`

rows, err := sqliteDbPtr.Query(query, args...)
if err != nil {
    // handle error
}
defer rows.Close()

for rows.Next() {
    //
}
if err := rows.Err(); err != nil {
    //
}

NOTE that I've removed the subquery and added the FROM some_table clause which your question was missing.

Upvotes: 1

Related Questions