Parameter placeholder not recognized

[tko]

I'm trying to run a query that is essentially select * from t where c like '%?%' where the ? is provided as argument. However the placeholder isn't recognized and I just get a runtime error SQL query contains 0 params, but 1 arguments given instead.

It's unclear what the rules about placeholder really are, but it seems to work better when written as .. like ('%' || ? || '%') but a somewhat more definitive best practice example would be helpful, one that would also address escaping % and _ etc. properly that becomes a problem soon after.

[nurpax]

@tko Sqlite-simple punts placeholder substitution to the underlying sqlite3 native library. There's no substitution happening on the Haskell side. Thus any placeholder docs you find for sqlite3 should apply for sqlite-simple. I'm not sure if the "like %" case is mentioned explicitly in sqlite3 docs though - this is the main page for bound parameters: http://www.sqlite.org/c3ref/bind_blob.html.

I think your use of "||" for concatenation is the right way to go about this. There are some examples of this in SO, e.g. http://stackoverflow.com/questions/1105463/sqlite-binding-within-string-literal.

Do you have any specific suggestions on how to document this better in sqlite-simple? E.g., is there something that should be added to https://hackage.haskell.org/package/sqlite-simple-0.4.8.0/docs/Database-SQLite-Simple.html under "Parameter substitution" section?

[tko]

I think a simple note on how to deal with the peculiarities of a like '%?%'like query would be helpful since from the surrounding examples with naked ? and (?) it's not immediately obvious strings / single quotes effectively disable the substitution.

Further than that I can't help thinking there should be (in haskell side) some helper functions to help manage constructing the queries, in my case turn a list of strings into a list of and (or or) separated like '%?%' queries in escape-safe fashion; the bookkeeping with generating lists of where-clauses and their corresponding arguments is kind of nontrivially annoying and easy to miss something. Unfortunately I don't have concrete suggestions for how to design such an API, other than providing a low-ish level function to escape special characters in (like-)queries - the details are very much sql territory, especially in combination of escape clause, and IMO interfacing library should provide functions to help deal with it.

[nurpax]

I'll add a note on the '%?' escaping into haddocks.

I agree that some types of queries are cumbersome to construct. I've been meaning to add some helpers like this, just not quite sure what specific functions would be needed. Currently sqlite-simple doesn't event provide a function to escape a string (like sqlite3's mprintf).

For some types of query construction, I've noticed that named parameters work much better than positional ? params. Using plain positional params when you have a varying number of things to substitute is pretty painful.

I've you have examples of things that are causing you pain, please post them here. This will help design better helper functions for these cases.

[tko]

My specific example was a combination of like and ands, e.g. turning a user provided query string "a_thing 100%" into (Query "(col like ('%' || ? || '%') escape '\') and (col like ('%' || ? || '%') escape '\')", ["a\_thing", "100\%"])

Not sure about named vs. positional params when it comes to dynamic queries, seems concatenating query strings and arguments in tandem seems simpler than coming up with uniquely named arguments that can not clash.