I have been using nil to insert NULL values into my Postgres DB. Now when 0.19.1 has phased out nil - what to do?
I have tried with empty strings, "NULL", dbNull, etc., but I haven't found a solution.
import db_postgres
var db = connecToDB()
db.exec(sql("""CREATE TABLE myTable (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
age INTEGER"""))
discard insertID(db, sql"INSERT INTO myTable (name, age) VALUES (?, ?)", "John", 55)
# How to insert a NULL value?
discard insertID(db, sql"INSERT INTO myTable (name, age) VALUES (?, ?)", nil, 55)
discard insertID(db, sql"INSERT INTO myTable (name, age) VALUES (?, ?)", "John", nil)
Thanks for the reply @Araq, then I'll stop my searching for now.
Would you care to elaborate on why I shouldn't use NULL? Except for getting rid of --nilseqs:on ;-)
as a workaround you could try:
discard insertID(db, sql"INSERT INTO myTable (age) VALUES (?)",55)
discard insertID(db, sql"INSERT INTO myTable (name) VALUES (?)", "John")
@mikra: Good idea, I could fix the query builder to support this [1]. But it does only work for INSERT - so I'm still missing for UPDATE.
@mashingan: I don't think I get it. How do I append the NULL value?
[1]
# discard insertID(db, sqlInsert(db, "myTable", ["name", "age"]), "John", 50)
proc sqlInsert*(table: string, data: varargs[string]): SqlQuery =
var fields = "INSERT INTO " & table & " ("
var vals = ""
for i, d in data:
if i > 0:
fields.add(", ")
vals.add(", ")
fields.add(d)
vals.add('?')
result = sql(fields & ") VALUES (" & vals & ")")
[2]
# exec(db, sqlUpdate(db, "myTable", ["age"], ["name"]), nil, "John")
proc sqlUpdate*(table: string, data: varargs[string], where: varargs[string]): SqlQuery =
var fields = "UPDATE " & table & " SET "
for i, d in data:
if i > 0:
fields.add(", ")
fields.add(d & " = ?")
var wes = " WHERE "
for i, d in where:
if i > 0:
wes.add(" AND ")
wes.add(d & " = ?")
result = sql(fields & wes)
@mikra: Now I get it. That could be a solution, but I would prefer that it would handled by the lib db_postgres.
@timothee: I can't seem to figure out, how to let proc insertID* accept it as Option[string] and still use nil.. :(
Current solution
# lib/impure/db_postgres.nim
proc dbFormat(formatstr: SqlQuery, args: varargs[string], emptyIsNull = true): string =
# emptyIsNull = true => Empty strings, "", will be set to NULL
result = ""
var a = 0
if args.len > 0 and not string(formatstr).contains("?"):
dbError("""parameter substitution expects "?" """)
if args.len == 0:
return string(formatstr)
else:
for c in items(string(formatstr)):
if c == '?':
# Check for emptyIsNull and empty string
if emptyIsNull and args[a].len() == 0:
add(result, "NULL")
else:
add(result, dbQuote(args[a]))
inc(a)
else:
add(result, c)
Second attempt (working)
# Custom object with predefined global null: dbNullVal
# exec(db, sql"UPDATE myTable SET age = ? WHERE name = ?", dbNullVal, "John")
# lib/impure/db_postgres.nim
# __ New
type
ArgObj = object
val: string
isNull: bool
var arg: ArgObj
var dbNullVal*: ArgObj
dbNullVal.isNull = true
proc argType*(v: ArgObj): ArgObj =
return dbNullVal
proc argType*(v: string | int): ArgObj =
arg.val = $v
arg.isNull = false
return arg
# __ Changes to existing
proc dbFormat[T](formatstr: SqlQuery, args: varargs[T, argType]): string =
# varargs[string, `$`] => varargs[T, argType]
result = ""
var a = 0
if args.len > 0 and not string(formatstr).contains("?"):
dbError("""parameter substitution expects "?" """)
if args.len == 0:
return string(formatstr)
else:
for c in items(string(formatstr)):
if c == '?':
# Check bool on arg
if args[a].isNull:
add(result, "NULL")
else:
# Use args.val instead
add(result, dbQuote(args[a].val))
inc(a)
else:
add(result, c)
# varargs[string, `$`] for all is just replaced with varargs[T, argType]
proc exec*[T](db: DbConn, query: SqlQuery, args: varargs[T, argType]) {.
tags: [ReadDbEffect, WriteDbEffect].} =
## executes the query and raises EDB if not successful.
var res = pqexecParams(db, dbFormat(query, args), 0, nil, nil,
nil, nil, 0)
if pqresultStatus(res) != PGRES_COMMAND_OK: dbError(db)
pqclear(res)
there is also an open github issue about that : https://github.com/nim-lang/Nim/issues/5768
here is mentioned that sqlite3_bind_* calls should be used. I agree.
I prepared a small (academic) example to demonstrate the bug:
import db_sqlite
const create_table1_sql = sql"""
create table if not exists ttab(
id integer primary key,
question text,
answer text
);"""
const insert_sql = sql"""
insert into ttab (id,question,answer) values (?,'how do you do ?',?)
"""
let db = open(":memory:","","","")
db.exec(create_table1_sql)
db.exec(insert_sql,1,"fine!") # index out of bounds is raised here
db.close
#[
db_sqlite.nim(139) exec
db_sqlite.nim(129) tryExec
db_sqlite.nim(119) dbFormat
system.nim(2939) sysFatal
Error: unhandled exception: index out of bounds [IndexError]
]#
but to provide a proper binding the API could be modified a little bit. Instead of just providing the values there should be a possibility for the caller to provide the dbTypes. Type-guessing at a lower level is error prone...
welcome. I checked the source a little bit and I think the problem affects the entire db-layer. I filed an issue about that: https://github.com/nim-lang/Nim/issues/9453 The problem has nothing to do with the NULL values - it's just how the sql is parsed and the values are inserted into the sql before invoking execute on the db-side (I always test with latest devel)
I found also other issues how the results are retrieved and iterated (for huge tables or resultsets I think you blow up your memory because internally the entire result set is consumed)
I wish I could contribute to that problem but at the moment I am a little bit lack of spare time. I think the first thing would be doing it proper for sqlite3; an then doing a rework on the (generic) db-layer without affecting the actual API
Something like this:
import options, strutils
from sequtils import map
proc queryStr(query: string, args: varargs[Option[string]]):
(string, seq[string]) =
## Transform '?' in query string according to optioned arguments given
if '?' notin query:
return (query, args.map get)
var
qpos = -1
prevq = -1
builder = ""
outargs = newseq[string]()
for arg in args:
prevq = qpos
# still need to make sure it's skipped when ? within ''
qpos = query.find('?', start = qpos+1)
if qpos == -1:
builder &= query[prevq+1 .. ^1]
break
if arg.isNone:
builder &= query[prevq+1 .. qpos].replace("?", "NULL")
continue
outargs.add get(arg)
builder &= query[prevq+1 .. qpos]
# the left over string slice
if qpos != -1 or qpos+1 < query.high:
builder &= query[qpos+1 .. ^1]
(builder, outargs)
let (q, args) = queryStr(
"INSERT INTO table_name(name, age, info) VALUES(?, ?, ?);",
none(string), some $19, some "info")
echo q
echo args
Have we resolved this issue by now?
How do I insert NULL values in Postgres using db_postgres?
You don't :) . You need to include the NULL within the query or use a nimble package which does that for you. So:
sql("INSERT INTO table_name(name, age, question, info) VALUES(NULL, NULL,'any question?', NULL);")