Sql-query and []interface & string problem

Getting Help
1

I have a problem with the argument-interface and strings on query.

var stuff = []interface{}{}

....

query = "SELECT intern_NR, Bezeichnung FROM sv_seminare ORDER BY Bezeichnung ? LIMIT ?,?"
stuff = []interface{}{order, offset, limit}

...

rows, err := db.Query(query, stuff...)

order is are string, offset & limit integer.

Error:

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘? LIMIT ?,?’ at line 1

My log:

2016/10/02 18:37:35 SELECT intern_NR, Bezeichnung FROM sv_seminare ORDER BY Bezeichnung ? LIMIT ?,?
2016/10/02 18:37:35 [asc 0 10]

If I remove the order it runs without errors, but whenever i use a string in my interface FAILLLLLL :slight_smile:

2

ORDER BY Bezeichnung, ? LIMIT ?,?"

3

okay?! Why?

4

http://dev.mysql.com/doc/refman/5.7/en/select.html

ORDER BY can be using for any fields.
You want to use Bezeichnung constantly as first order clause and want to bind the variable as the second field.
Then query can be like
ORDER BY Bezeichnung, ? - ORDER BY Bezeichnung THEN ORDER BY binding variable

5

My Select is yet

SELECT intern_NR, Bezeichnung FROM sv_seminare ORDER BY Bezeichnung, ? LIMIT ?,?

that is

SELECT intern_NR, Bezeichnung FROM sv_seminare ORDER BY Bezeichnung, ASC LIMIT 0,10

and it runs.

What about?

SELECT intern_NR, Bezeichnung FROM sv_seminare WHERE Bezeichnung LIKE '%?%' ORDER BY Bezeichnung, ? LIMIT ?,?

Error:

2016/10/02 22:02:14 [Leip asc 0 10]
2016/10/02 22:02:14 Fatal error:  sql: statement expects 3 inputs; got 4 c:/...

here is the ASC | DESC before the comma

http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

6

Try to use raw string literal

`SELECT intern_NR, Bezeichnung FROM sv_seminare WHERE Bezeichnung LIKE ‘%?%’ ORDER BY Bezeichnung, ? LIMIT ?,?`
instead
“SELECT intern_NR, Bezeichnung FROM sv_seminare WHERE Bezeichnung LIKE ‘%?%’ ORDER BY Bezeichnung, ? LIMIT ?,?”

7

Here is my code

if order == “” {
order = “ASC”
}

log.Println(order, offset, limit, search)

if search != "" {
	query = "SELECT intern_NR, Bezeichnung FROM sv_seminare WHERE Bezeichnung LIKE '%?%' ORDER BY Bezeichnung, ? LIMIT ?,?"
	stuff = []interface{}{search, order, offset, limit}
} else {
	query = "SELECT intern_NR, Bezeichnung FROM sv_seminare ORDER BY Bezeichnung ,? LIMIT ?,?"
	stuff = []interface{}{order, offset, limit}
}

log.Println(query)
log.Println(stuff)
rows, err := db.Query(query, stuff...)

The problem is the type string in interface

8

stuff = []interface{}{"%" + search + "%", order, offset, limit}

it’s fine

9

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.