diff options
| -rw-r--r-- | lib/impure/db_postgres.nim | 13 | ||||
| -rw-r--r-- | tests/untestable/tpostgres.nim | 69 |
2 files changed, 80 insertions, 2 deletions
diff --git a/lib/impure/db_postgres.nim b/lib/impure/db_postgres.nim index 9bdbae4c2..ef26214b7 100644 --- a/lib/impure/db_postgres.nim +++ b/lib/impure/db_postgres.nim @@ -162,8 +162,10 @@ proc setupQuery(db: DbConn, stmtName: SqlPrepared, proc prepare*(db: DbConn; stmtName: string, query: SqlQuery; nParams: int): SqlPrepared = + ## Creates a new ``SqlPrepared`` statement. Parameter substitution is done + ## via ``$1``, ``$2``, ``$3``, etc. if nParams > 0 and not string(query).contains("$1"): - dbError("""parameter substitution expects "$1" """) + dbError("parameter substitution expects \"$1\"") var res = pqprepare(db, stmtName, query.string, int32(nParams), nil) if pqResultStatus(res) != PGRES_COMMAND_OK: dbError(db) return SqlPrepared(stmtName) @@ -282,6 +284,15 @@ proc getValue*(db: DbConn, query: SqlQuery, var x = pqgetvalue(setupQuery(db, query, args), 0, 0) result = if isNil(x): "" else: $x +proc getValue*(db: DbConn, stmtName: SqlPrepared, + args: varargs[string, `$`]): string {. + tags: [ReadDbEffect].} = + ## executes the query and returns the first column of the first row of the + ## result dataset. Returns "" if the dataset contains no rows or the database + ## value is NULL. + var x = pqgetvalue(setupQuery(db, stmtName, args), 0, 0) + result = if isNil(x): "" else: $x + proc tryInsertID*(db: DbConn, query: SqlQuery, args: varargs[string, `$`]): int64 {. tags: [WriteDbEffect].}= diff --git a/tests/untestable/tpostgres.nim b/tests/untestable/tpostgres.nim index 81fe8bf51..dcbdaad39 100644 --- a/tests/untestable/tpostgres.nim +++ b/tests/untestable/tpostgres.nim @@ -1,4 +1,5 @@ -import db_postgres +import db_postgres, strutils + let db = open("localhost", "dom", "", "test") db.exec(sql"DROP TABLE IF EXISTS myTable") @@ -12,4 +13,70 @@ doAssert db.getValue(sql"SELECT name FROM myTable") == name # Check issue #3513 doAssert db.getValue(sql"SELECT name FROM myTable") == name + +# issue #3560 +proc addToDb(conn: DbConn, fileId: int, fileName: string): int64 = + result = conn.insertId(sql("INSERT into files (id, filename) VALUES (?, ?)"), fileId, fileName) + +db.exec(sql"DROP TABLE IF EXISTS files") +db.exec(sql"DROP TABLE IF EXISTS fileobjects") +db.exec(sql("""CREATE TABLE FILEOBJECTS( + ID SERIAL PRIMARY KEY, + FILE_SIZE INT, + MD5 CHAR(32) NOT NULL UNIQUE + );""")) + +db.exec(sql("""CREATE TABLE FILES( + ID SERIAL PRIMARY KEY, + OBJECT_ID INT, + FILENAME TEXT NOT NULL, + URI TEXT, + SCHEME CHAR(10), + PUBLIC BOOLEAN DEFAULT FALSE, + CONSTRAINT fk1_fileobjs FOREIGN KEY (object_id) + REFERENCES fileobjects (id) MATCH SIMPLE + ON DELETE CASCADE + );""")) + +let f1 = db.addToDb(1, "hello.tmp") +doAssert f1 == 1 +let f2 = db.addToDb(2, "hello2.tmp") +doAssert f2 == 2 + +# PreparedStmt vs. normal query +try: + echo db.getValue(sql("select * from files where id = $1"), 1) + doAssert false, "Exception expected" +except DbError: + let msg = getCurrentExceptionMsg().normalize + doAssert "expects" in msg + doAssert "?" in msg + doAssert "parameter substitution" in msg + +doAssert db.getValue(sql("select filename from files where id = ?"), 1) == "hello.tmp" + +var first = prepare(db, "one", sql"select filename from files where id = $1", 1) +doAssert db.getValue(first, 1) == "hello.tmp" + +try: + var second = prepare(db, "two", sql"select filename from files where id = ?", 1) + doAssert false, "Exception expected" +except: + let msg = getCurrentExceptionMsg().normalize + doAssert "expects" in msg + doAssert "$1" in msg + doAssert "parameter substitution" in msg + +# issue #3569 +db.exec(SqlQuery("DROP TABLE IF EXISTS tags")) +db.exec(SqlQuery("CREATE TABLE tags(id serial UNIQUE, name varchar(255))")) + +for i in 1..10: + var name = "t" & $i + echo(name) + discard db.getRow( + SqlQuery("INSERT INTO tags(name) VALUES(\'$1\') RETURNING id" % [name])) + +echo("All tests succeeded!") + db.close() |