This is the code:
import db_sqlite, times
var conn: TDbConn
proc mark_items(urls: seq[string]) =
## Marks all the passed in atoms as seen *now* in the database.
let query = sql"""UPDATE rss_entries
SET last_seen = ? WHERE url in (?)"""
conn.exec(query, $int(get_time()), urls)
passing_args.nim(9, 7) Error: type mismatch: got (TDbConn, TSqlQuery, string, seq[string])
but expected one of:
db_sqlite.Exec(db: TDbConn, query: TSqlQuery, args: varargs[string])
let query = sql"""SELECT foo, bar FROM rss_entries WHERE url IN (?)"""
for row in conn.fast_rows(query, known_urls):
blah
import db_sqlite, times
var conn: TDbConn
proc mark_items(urls: seq[string]) =
## Marks all the passed in atoms as seen *now* in the database.
let query = sql"""UPDATE rss_entries
SET last_seen = ? WHERE url in (?)"""
conn.exec(query, $int(get_time()) & urls)
& exists for sequences too.
Sorry for not explaining properly, the problem here is that for the in (?) syntax I was expecting the exec to transform the string sequence into a comma separated list of values. So given a list of three words, the expected expansion of the query would be something like:
UPDATE rss_entries
SET last_seen = 13123123 WHERE url in ("one", "two", "three")
AFAICS the only proper way of passing a list of strings now is to generate manually a list of n question marks (like ?, ?, ?) and embed that into the query so that the list gets picked up completely during formatting. Or am I doing something really wrong? Huh, this is something that sort of works automatically for other language/bindings I've tried, help this spoiled brat!
WRT your example, the concatenation of a string to a sequence is amusing. I would either expect that the whole sequence gets stringified (not happening), or the string is inserted first into the sequence as the parameter stand naturally. Instead, the first parameter is appended to the list:
let
a = $int(get_time())
b = @["one", "two"]
c = a & b
echo join(c, ", ")
# --> one, two, 1374868327
Unexpected, though understandable.
Should we enhace the database modules with these lines?:
proc questionify*(num_question_marks: int): string =
## Produces a string like '?,?,?' for n specified entries.
##
## If num_question_marks is less than one the proc will assert on debug
## builds and return a single question mark in release builds. Example:
##
## .. code-block:: nimrod
## assert questionify(3) == "?,?,?"
## assert questionify(-1) == "?" # only works in release builds!
## assert questionify(2) == "?,?"
assert num_question_marks > 0
result = newStringOfCap(num_question_marks * 2 - 1)
for pos in 0..num_question_marks-2: result &= "?,"
result &= "?"
template `?`*(x: seq[string]): string =
## Template wrapper around the questionify proc for string sequences.
##
## You can use this as a shorthand syntax for creating sql `IN` statements.
## Example:
##
## .. code-block:: nimrod
## let
## names = @["Araq", "Mat2", "gradha"]
## query = sql("""SELECT id, age FROM users
## WHERE name IN ($1)""" % ?names)
## for row in conn.fast_rows(query, names):
## process(row)
questionify(x.len)
In python, there is a feature which allows multiplying strings with an integer. If this existed in nimrod, your questionify function could be replaced with (if n is number of question marks)
( "?," * (n-1)) & "?"
Simple and nice feature maybe?
Simple and nice feature maybe?
strutils.repeatStr(n-1, "?,") & "?"
Yeah, maybe I should have defined * instead.
@gradha:
I like it. Please put it in a db_common.nim module. All db_* modules should import db_common and export it for clients.
Reviving this old thread... I'm trying to create a query based on a dynamic number of parameters for an WHERE ID IN (?, ?, ?) statement.
I was expecting that simply passing a string sequence of ID's into the sql statement would automatically parse this, but doesn't seem to.
Is this above-referenced questionify proc or anything similar built into the standard db lib? Could not find anything in the docs or source code in db_common.