|
1191
|
1 #
|
|
|
2 #
|
|
|
3 # Nim's Runtime Library
|
|
|
4 # (c) Copyright 2015 Andreas Rumpf
|
|
|
5 #
|
|
|
6 # See the file "copying.txt", included in this
|
|
|
7 # distribution, for details about the copyright.
|
|
|
8 #
|
|
|
9
|
|
|
10 ## .. note:: In order to use this module, run `nimble install db_connector`.
|
|
|
11 ##
|
|
|
12 ## A higher level `SQLite`:idx: database wrapper. This interface
|
|
|
13 ## is implemented for other databases too.
|
|
|
14 ##
|
|
|
15 ## Basic usage
|
|
|
16 ## ===========
|
|
|
17 ##
|
|
|
18 ## The basic flow of using this module is:
|
|
|
19 ##
|
|
|
20 ## 1. Open database connection
|
|
|
21 ## 2. Execute SQL query
|
|
|
22 ## 3. Close database connection
|
|
|
23 ##
|
|
|
24 ## Parameter substitution
|
|
|
25 ## ----------------------
|
|
|
26 ##
|
|
|
27 ## All `db_*` modules support the same form of parameter substitution.
|
|
|
28 ## That is, using the `?` (question mark) to signify the place where a
|
|
|
29 ## value should be placed. For example:
|
|
|
30 ##
|
|
|
31 ## ```Nim
|
|
|
32 ## sql"INSERT INTO my_table (colA, colB, colC) VALUES (?, ?, ?)"
|
|
|
33 ## ```
|
|
|
34 ##
|
|
|
35 ## Opening a connection to a database
|
|
|
36 ## ----------------------------------
|
|
|
37 ##
|
|
|
38 ## ```Nim
|
|
|
39 ## import db_connector/db_sqlite
|
|
|
40 ##
|
|
|
41 ## # user, password, database name can be empty.
|
|
|
42 ## # These params are not used on db_sqlite module.
|
|
|
43 ## let db = open("mytest.db", "", "", "")
|
|
|
44 ## db.close()
|
|
|
45 ## ```
|
|
|
46 ##
|
|
|
47 ## Creating a table
|
|
|
48 ## ----------------
|
|
|
49 ##
|
|
|
50 ## ```Nim
|
|
|
51 ## db.exec(sql"DROP TABLE IF EXISTS my_table")
|
|
|
52 ## db.exec(sql"""CREATE TABLE my_table (
|
|
|
53 ## id INTEGER,
|
|
|
54 ## name VARCHAR(50) NOT NULL
|
|
|
55 ## )""")
|
|
|
56 ## ```
|
|
|
57 ##
|
|
|
58 ## Inserting data
|
|
|
59 ## --------------
|
|
|
60 ##
|
|
|
61 ## ```Nim
|
|
|
62 ## db.exec(sql"INSERT INTO my_table (id, name) VALUES (0, ?)",
|
|
|
63 ## "Jack")
|
|
|
64 ## ```
|
|
|
65 ##
|
|
|
66 ## Larger example
|
|
|
67 ## --------------
|
|
|
68 ##
|
|
|
69 ## ```Nim
|
|
|
70 ## import db_connector/db_sqlite
|
|
|
71 ## import std/math
|
|
|
72 ##
|
|
|
73 ## let db = open("mytest.db", "", "", "")
|
|
|
74 ##
|
|
|
75 ## db.exec(sql"DROP TABLE IF EXISTS my_table")
|
|
|
76 ## db.exec(sql"""CREATE TABLE my_table (
|
|
|
77 ## id INTEGER PRIMARY KEY,
|
|
|
78 ## name VARCHAR(50) NOT NULL,
|
|
|
79 ## i INT(11),
|
|
|
80 ## f DECIMAL(18, 10)
|
|
|
81 ## )""")
|
|
|
82 ##
|
|
|
83 ## db.exec(sql"BEGIN")
|
|
|
84 ## for i in 1..1000:
|
|
|
85 ## db.exec(sql"INSERT INTO my_table (name, i, f) VALUES (?, ?, ?)",
|
|
|
86 ## "Item#" & $i, i, sqrt(i.float))
|
|
|
87 ## db.exec(sql"COMMIT")
|
|
|
88 ##
|
|
|
89 ## for x in db.fastRows(sql"SELECT * FROM my_table"):
|
|
|
90 ## echo x
|
|
|
91 ##
|
|
|
92 ## let id = db.tryInsertId(sql"""INSERT INTO my_table (name, i, f)
|
|
|
93 ## VALUES (?, ?, ?)""",
|
|
|
94 ## "Item#1001", 1001, sqrt(1001.0))
|
|
|
95 ## echo "Inserted item: ", db.getValue(sql"SELECT name FROM my_table WHERE id=?", id)
|
|
|
96 ##
|
|
|
97 ## db.close()
|
|
|
98 ## ```
|
|
|
99 ##
|
|
|
100 ## Storing binary data example
|
|
|
101 ##----------------------------
|
|
|
102 ##
|
|
|
103 ## ```nim
|
|
|
104 ## import std/random
|
|
|
105 ##
|
|
|
106 ## ## Generate random float datas
|
|
|
107 ## var orig = newSeq[float64](150)
|
|
|
108 ## randomize()
|
|
|
109 ## for x in orig.mitems:
|
|
|
110 ## x = rand(1.0)/10.0
|
|
|
111 ##
|
|
|
112 ## let db = open("mysqlite.db", "", "", "")
|
|
|
113 ## block: ## Create database
|
|
|
114 ## ## Binary datas needs to be of type BLOB in SQLite
|
|
|
115 ## let createTableStr = sql"""CREATE TABLE test(
|
|
|
116 ## id INTEGER NOT NULL PRIMARY KEY,
|
|
|
117 ## data BLOB
|
|
|
118 ## )
|
|
|
119 ## """
|
|
|
120 ## db.exec(createTableStr)
|
|
|
121 ##
|
|
|
122 ## block: ## Insert data
|
|
|
123 ## var id = 1
|
|
|
124 ## ## Data needs to be converted to seq[byte] to be interpreted as binary by bindParams
|
|
|
125 ## var dbuf = newSeq[byte](orig.len*sizeof(float64))
|
|
|
126 ## copyMem(unsafeAddr(dbuf[0]), unsafeAddr(orig[0]), dbuf.len)
|
|
|
127 ##
|
|
|
128 ## ## Use prepared statement to insert binary data into database
|
|
|
129 ## var insertStmt = db.prepare("INSERT INTO test (id, data) VALUES (?, ?)")
|
|
|
130 ## insertStmt.bindParams(id, dbuf)
|
|
|
131 ## let bres = db.tryExec(insertStmt)
|
|
|
132 ## ## Check insert
|
|
|
133 ## doAssert(bres)
|
|
|
134 ## # Destroy statement
|
|
|
135 ## finalize(insertStmt)
|
|
|
136 ##
|
|
|
137 ## block: ## Use getValue to select data
|
|
|
138 ## var dataTest = db.getValue(sql"SELECT data FROM test WHERE id = ?", 1)
|
|
|
139 ## ## Calculate sequence size from buffer size
|
|
|
140 ## let seqSize = int(dataTest.len*sizeof(byte)/sizeof(float64))
|
|
|
141 ## ## Copy binary string data in dataTest into a seq
|
|
|
142 ## var res: seq[float64] = newSeq[float64](seqSize)
|
|
|
143 ## copyMem(unsafeAddr(res[0]), addr(dataTest[0]), dataTest.len)
|
|
|
144 ##
|
|
|
145 ## ## Check datas obtained is identical
|
|
|
146 ## doAssert res == orig
|
|
|
147 ##
|
|
|
148 ## db.close()
|
|
|
149 ## ```
|
|
|
150 ##
|
|
|
151 ##
|
|
|
152 ## Note
|
|
|
153 ## ====
|
|
|
154 ## This module does not implement any ORM features such as mapping the types from the schema.
|
|
|
155 ## Instead, a `seq[string]` is returned for each row.
|
|
|
156 ##
|
|
|
157 ## The reasoning is as follows:
|
|
|
158 ## 1. it's close to what many DBs offer natively (`char**`:c:)
|
|
|
159 ## 2. it hides the number of types that the DB supports
|
|
|
160 ## (int? int64? decimal up to 10 places? geo coords?)
|
|
|
161 ## 3. it's convenient when all you do is to forward the data to somewhere else (echo, log, put the data into a new query)
|
|
|
162 ##
|
|
|
163 ## See also
|
|
|
164 ## ========
|
|
|
165 ##
|
|
|
166 ## * `db_odbc module <db_odbc.html>`_ for ODBC database wrapper
|
|
|
167 ## * `db_mysql module <db_mysql.html>`_ for MySQL database wrapper
|
|
|
168 ## * `db_postgres module <db_postgres.html>`_ for PostgreSQL database wrapper
|
|
|
169
|
|
|
170
|
|
|
171 import ./sqlite3, macros
|
|
|
172
|
|
|
173 import ./db_common
|
|
|
174 export db_common
|
|
|
175
|
|
|
176 import private/dbutils
|
|
|
177
|
|
|
178 import std/private/[since]
|
|
|
179 when defined(nimPreviewSlimSystem):
|
|
|
180 import std/assertions
|
|
|
181
|
|
|
182 type
|
|
|
183 DbConn* = PSqlite3 ## Encapsulates a database connection.
|
|
|
184 Row* = seq[string] ## A row of a dataset. `NULL` database values will be
|
|
|
185 ## converted to an empty string.
|
|
|
186 InstantRow* = PStmt ## A handle that can be used to get a row's column
|
|
|
187 ## text on demand.
|
|
|
188 SqlPrepared* = distinct PStmt ## a identifier for the prepared queries
|
|
|
189
|
|
|
190 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: int32) {.since: (1, 3).}
|
|
|
191 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: int64) {.since: (1, 3).}
|
|
|
192 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: int) {.since: (1, 3).}
|
|
|
193 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: float64) {.since: (1, 3).}
|
|
|
194 proc bindNull*(ps: SqlPrepared, paramIdx: int) {.since: (1, 3).}
|
|
|
195 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: string, copy = true) {.since: (1, 3).}
|
|
|
196 proc bindParam*(ps: SqlPrepared, paramIdx: int,val: openArray[byte], copy = true) {.since: (1, 3).}
|
|
|
197
|
|
|
198 proc dbError*(db: DbConn) {.noreturn.} =
|
|
|
199 ## Raises a `DbError` exception.
|
|
|
200 ##
|
|
|
201 ## **Examples:**
|
|
|
202 ## ```Nim
|
|
|
203 ## let db = open("mytest.db", "", "", "")
|
|
|
204 ## if not db.tryExec(sql"SELECT * FROM not_exist_table"):
|
|
|
205 ## dbError(db)
|
|
|
206 ## db.close()
|
|
|
207 ## ```
|
|
|
208 var e: ref DbError
|
|
|
209 new(e)
|
|
|
210 e.msg = $sqlite3.errmsg(db)
|
|
|
211 raise e
|
|
|
212
|
|
|
213 proc dbQuote*(s: string): string =
|
|
|
214 ## Escapes the `'` (single quote) char to `''`.
|
|
|
215 ## Because single quote is used for defining `VARCHAR` in SQL.
|
|
|
216 runnableExamples:
|
|
|
217 doAssert dbQuote("'") == "''''"
|
|
|
218 doAssert dbQuote("A Foobar's pen.") == "'A Foobar''s pen.'"
|
|
|
219
|
|
|
220 result = "'"
|
|
|
221 for c in items(s):
|
|
|
222 if c == '\'': add(result, "''")
|
|
|
223 else: add(result, c)
|
|
|
224 add(result, '\'')
|
|
|
225
|
|
|
226 proc dbFormat(formatstr: SqlQuery, args: varargs[string]): string =
|
|
|
227 dbFormatImpl(formatstr, dbQuote, args)
|
|
|
228
|
|
|
229 proc prepare*(db: DbConn; q: string): SqlPrepared {.since: (1, 3).} =
|
|
|
230 ## Creates a new `SqlPrepared` statement.
|
|
|
231 if prepare_v2(db, q, q.len.cint,result.PStmt, nil) != SQLITE_OK:
|
|
|
232 discard finalize(result.PStmt)
|
|
|
233 dbError(db)
|
|
|
234
|
|
|
235 proc tryExec*(db: DbConn, query: SqlQuery,
|
|
|
236 args: varargs[string, `$`]): bool {.
|
|
|
237 tags: [ReadDbEffect, WriteDbEffect].} =
|
|
|
238 ## Tries to execute the query and returns `true` if successful, `false` otherwise.
|
|
|
239 ##
|
|
|
240 ## **Examples:**
|
|
|
241 ## ```Nim
|
|
|
242 ## let db = open("mytest.db", "", "", "")
|
|
|
243 ## if not db.tryExec(sql"SELECT * FROM my_table"):
|
|
|
244 ## dbError(db)
|
|
|
245 ## db.close()
|
|
|
246 ## ```
|
|
|
247 assert(not db.isNil, "Database not connected.")
|
|
|
248 var q = dbFormat(query, args)
|
|
|
249 var stmt: sqlite3.PStmt
|
|
|
250 if prepare_v2(db, q.cstring, q.len.cint, stmt, nil) == SQLITE_OK:
|
|
|
251 let x = step(stmt)
|
|
|
252 if x in [SQLITE_DONE, SQLITE_ROW]:
|
|
|
253 result = finalize(stmt) == SQLITE_OK
|
|
|
254 else:
|
|
|
255 discard finalize(stmt)
|
|
|
256 result = false
|
|
|
257
|
|
|
258 proc tryExec*(db: DbConn, stmtName: SqlPrepared): bool {.
|
|
|
259 tags: [ReadDbEffect, WriteDbEffect].} =
|
|
|
260 let x = step(stmtName.PStmt)
|
|
|
261 if x in [SQLITE_DONE, SQLITE_ROW]:
|
|
|
262 result = true
|
|
|
263 else:
|
|
|
264 discard finalize(stmtName.PStmt)
|
|
|
265 result = false
|
|
|
266
|
|
|
267 proc exec*(db: DbConn, query: SqlQuery, args: varargs[string, `$`]) {.
|
|
|
268 tags: [ReadDbEffect, WriteDbEffect].} =
|
|
|
269 ## Executes the query and raises a `DbError` exception if not successful.
|
|
|
270 ##
|
|
|
271 ## **Examples:**
|
|
|
272 ## ```Nim
|
|
|
273 ## let db = open("mytest.db", "", "", "")
|
|
|
274 ## try:
|
|
|
275 ## db.exec(sql"INSERT INTO my_table (id, name) VALUES (?, ?)",
|
|
|
276 ## 1, "item#1")
|
|
|
277 ## except:
|
|
|
278 ## stderr.writeLine(getCurrentExceptionMsg())
|
|
|
279 ## finally:
|
|
|
280 ## db.close()
|
|
|
281 ## ```
|
|
|
282 if not tryExec(db, query, args): dbError(db)
|
|
|
283
|
|
|
284 macro untypedLen(args: varargs[untyped]): int =
|
|
|
285 newLit(args.len)
|
|
|
286
|
|
|
287 macro bindParams*(ps: SqlPrepared, params: varargs[untyped]): untyped {.since: (1, 3).} =
|
|
|
288 let bindParam = bindSym("bindParam", brOpen)
|
|
|
289 let bindNull = bindSym("bindNull")
|
|
|
290 let preparedStatement = genSym()
|
|
|
291 result = newStmtList()
|
|
|
292 # Store `ps` in a temporary variable. This prevents `ps` from being evaluated every call.
|
|
|
293 result.add newNimNode(nnkLetSection).add(newIdentDefs(preparedStatement, newEmptyNode(), ps))
|
|
|
294 for idx, param in params:
|
|
|
295 if param.kind != nnkNilLit:
|
|
|
296 result.add newCall(bindParam, preparedStatement, newIntLitNode idx + 1, param)
|
|
|
297 else:
|
|
|
298 result.add newCall(bindNull, preparedStatement, newIntLitNode idx + 1)
|
|
|
299
|
|
|
300
|
|
|
301 template exec*(db: DbConn, stmtName: SqlPrepared,
|
|
|
302 args: varargs[typed]): untyped =
|
|
|
303 when untypedLen(args) > 0:
|
|
|
304 if reset(stmtName.PStmt) != SQLITE_OK:
|
|
|
305 dbError(db)
|
|
|
306 if clear_bindings(stmtName.PStmt) != SQLITE_OK:
|
|
|
307 dbError(db)
|
|
|
308 stmtName.bindParams(args)
|
|
|
309 if not tryExec(db, stmtName): dbError(db)
|
|
|
310
|
|
|
311 proc newRow(L: int): Row =
|
|
|
312 newSeq(result, L)
|
|
|
313 for i in 0..L-1: result[i] = ""
|
|
|
314
|
|
|
315 proc setupQuery(db: DbConn, query: SqlQuery,
|
|
|
316 args: varargs[string]): PStmt =
|
|
|
317 assert(not db.isNil, "Database not connected.")
|
|
|
318 var q = dbFormat(query, args)
|
|
|
319 if prepare_v2(db, q.cstring, q.len.cint, result, nil) != SQLITE_OK: dbError(db)
|
|
|
320
|
|
|
321 proc setupQuery(db: DbConn, stmtName: SqlPrepared): SqlPrepared {.since: (1, 3).} =
|
|
|
322 assert(not db.isNil, "Database not connected.")
|
|
|
323 result = stmtName
|
|
|
324
|
|
|
325 proc setRow(stmt: PStmt, r: var Row, cols: cint) =
|
|
|
326 for col in 0'i32..cols-1:
|
|
|
327 let cb = column_bytes(stmt, col)
|
|
|
328 setLen(r[col], cb) # set capacity
|
|
|
329 if column_type(stmt, col) == SQLITE_BLOB:
|
|
|
330 copyMem(addr(r[col][0]), column_blob(stmt, col), cb)
|
|
|
331 else:
|
|
|
332 setLen(r[col], 0)
|
|
|
333 let x = column_text(stmt, col)
|
|
|
334 if not isNil(x): add(r[col], x)
|
|
|
335
|
|
|
336 iterator fastRows*(db: DbConn, query: SqlQuery,
|
|
|
337 args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} =
|
|
|
338 ## Executes the query and iterates over the result dataset.
|
|
|
339 ##
|
|
|
340 ## This is very fast, but potentially dangerous. Use this iterator only
|
|
|
341 ## if you require **ALL** the rows.
|
|
|
342 ##
|
|
|
343 ## **Note:** Breaking the `fastRows()` iterator during a loop will cause the
|
|
|
344 ## next database query to raise a `DbError` exception `unable to close due
|
|
|
345 ## to ...`.
|
|
|
346 ##
|
|
|
347 ## **Examples:**
|
|
|
348 ##
|
|
|
349 ## ```Nim
|
|
|
350 ## let db = open("mytest.db", "", "", "")
|
|
|
351 ##
|
|
|
352 ## # Records of my_table:
|
|
|
353 ## # | id | name |
|
|
|
354 ## # |----|----------|
|
|
|
355 ## # | 1 | item#1 |
|
|
|
356 ## # | 2 | item#2 |
|
|
|
357 ##
|
|
|
358 ## for row in db.fastRows(sql"SELECT id, name FROM my_table"):
|
|
|
359 ## echo row
|
|
|
360 ##
|
|
|
361 ## # Output:
|
|
|
362 ## # @["1", "item#1"]
|
|
|
363 ## # @["2", "item#2"]
|
|
|
364 ##
|
|
|
365 ## db.close()
|
|
|
366 ## ```
|
|
|
367 var stmt = setupQuery(db, query, args)
|
|
|
368 var L = (column_count(stmt))
|
|
|
369 var result = newRow(L)
|
|
|
370 try:
|
|
|
371 while step(stmt) == SQLITE_ROW:
|
|
|
372 setRow(stmt, result, L)
|
|
|
373 yield result
|
|
|
374 finally:
|
|
|
375 if finalize(stmt) != SQLITE_OK: dbError(db)
|
|
|
376
|
|
|
377 iterator fastRows*(db: DbConn, stmtName: SqlPrepared): Row
|
|
|
378 {.tags: [ReadDbEffect,WriteDbEffect], since: (1, 3).} =
|
|
|
379 discard setupQuery(db, stmtName)
|
|
|
380 var L = (column_count(stmtName.PStmt))
|
|
|
381 var result = newRow(L)
|
|
|
382 try:
|
|
|
383 while step(stmtName.PStmt) == SQLITE_ROW:
|
|
|
384 setRow(stmtName.PStmt, result, L)
|
|
|
385 yield result
|
|
|
386 except:
|
|
|
387 dbError(db)
|
|
|
388
|
|
|
389 iterator instantRows*(db: DbConn, query: SqlQuery,
|
|
|
390 args: varargs[string, `$`]): InstantRow
|
|
|
391 {.tags: [ReadDbEffect].} =
|
|
|
392 ## Similar to `fastRows iterator <#fastRows.i,DbConn,SqlQuery,varargs[string,]>`_
|
|
|
393 ## but returns a handle that can be used to get column text
|
|
|
394 ## on demand using `[]`. Returned handle is valid only within the iterator body.
|
|
|
395 ##
|
|
|
396 ## **Examples:**
|
|
|
397 ##
|
|
|
398 ## ```Nim
|
|
|
399 ## let db = open("mytest.db", "", "", "")
|
|
|
400 ##
|
|
|
401 ## # Records of my_table:
|
|
|
402 ## # | id | name |
|
|
|
403 ## # |----|----------|
|
|
|
404 ## # | 1 | item#1 |
|
|
|
405 ## # | 2 | item#2 |
|
|
|
406 ##
|
|
|
407 ## for row in db.instantRows(sql"SELECT * FROM my_table"):
|
|
|
408 ## echo "id:" & row[0]
|
|
|
409 ## echo "name:" & row[1]
|
|
|
410 ## echo "length:" & $len(row)
|
|
|
411 ##
|
|
|
412 ## # Output:
|
|
|
413 ## # id:1
|
|
|
414 ## # name:item#1
|
|
|
415 ## # length:2
|
|
|
416 ## # id:2
|
|
|
417 ## # name:item#2
|
|
|
418 ## # length:2
|
|
|
419 ##
|
|
|
420 ## db.close()
|
|
|
421 ## ```
|
|
|
422 var stmt = setupQuery(db, query, args)
|
|
|
423 try:
|
|
|
424 while step(stmt) == SQLITE_ROW:
|
|
|
425 yield stmt
|
|
|
426 finally:
|
|
|
427 if finalize(stmt) != SQLITE_OK: dbError(db)
|
|
|
428
|
|
|
429 iterator instantRows*(db: DbConn, stmtName: SqlPrepared): InstantRow
|
|
|
430 {.tags: [ReadDbEffect,WriteDbEffect], since: (1, 3).} =
|
|
|
431 var stmt = setupQuery(db, stmtName).PStmt
|
|
|
432 try:
|
|
|
433 while step(stmt) == SQLITE_ROW:
|
|
|
434 yield stmt
|
|
|
435 except:
|
|
|
436 dbError(db)
|
|
|
437
|
|
|
438 proc toTypeKind(t: var DbType; x: int32) =
|
|
|
439 case x
|
|
|
440 of SQLITE_INTEGER:
|
|
|
441 t.kind = dbInt
|
|
|
442 t.size = 8
|
|
|
443 of SQLITE_FLOAT:
|
|
|
444 t.kind = dbFloat
|
|
|
445 t.size = 8
|
|
|
446 of SQLITE_BLOB: t.kind = dbBlob
|
|
|
447 of SQLITE_NULL: t.kind = dbNull
|
|
|
448 of SQLITE_TEXT: t.kind = dbVarchar
|
|
|
449 else: t.kind = dbUnknown
|
|
|
450
|
|
|
451 proc setColumns(columns: var DbColumns; x: PStmt) =
|
|
|
452 let L = column_count(x)
|
|
|
453 setLen(columns, L)
|
|
|
454 for i in 0'i32 ..< L:
|
|
|
455 columns[i].name = $column_name(x, i)
|
|
|
456 columns[i].typ.name = $column_decltype(x, i)
|
|
|
457 toTypeKind(columns[i].typ, column_type(x, i))
|
|
|
458 columns[i].tableName = $column_table_name(x, i)
|
|
|
459
|
|
|
460 iterator instantRows*(db: DbConn; columns: var DbColumns; query: SqlQuery,
|
|
|
461 args: varargs[string, `$`]): InstantRow
|
|
|
462 {.tags: [ReadDbEffect].} =
|
|
|
463 ## Similar to `instantRows iterator <#instantRows.i,DbConn,SqlQuery,varargs[string,]>`_,
|
|
|
464 ## but sets information about columns to `columns`.
|
|
|
465 ##
|
|
|
466 ## **Examples:**
|
|
|
467 ##
|
|
|
468 ## ```Nim
|
|
|
469 ## let db = open("mytest.db", "", "", "")
|
|
|
470 ##
|
|
|
471 ## # Records of my_table:
|
|
|
472 ## # | id | name |
|
|
|
473 ## # |----|----------|
|
|
|
474 ## # | 1 | item#1 |
|
|
|
475 ## # | 2 | item#2 |
|
|
|
476 ##
|
|
|
477 ## var columns: DbColumns
|
|
|
478 ## for row in db.instantRows(columns, sql"SELECT * FROM my_table"):
|
|
|
479 ## discard
|
|
|
480 ## echo columns[0]
|
|
|
481 ##
|
|
|
482 ## # Output:
|
|
|
483 ## # (name: "id", tableName: "my_table", typ: (kind: dbNull,
|
|
|
484 ## # notNull: false, name: "INTEGER", size: 0, maxReprLen: 0, precision: 0,
|
|
|
485 ## # scale: 0, min: 0, max: 0, validValues: @[]), primaryKey: false,
|
|
|
486 ## # foreignKey: false)
|
|
|
487 ##
|
|
|
488 ## db.close()
|
|
|
489 ## ```
|
|
|
490 var stmt = setupQuery(db, query, args)
|
|
|
491 setColumns(columns, stmt)
|
|
|
492 try:
|
|
|
493 while step(stmt) == SQLITE_ROW:
|
|
|
494 yield stmt
|
|
|
495 finally:
|
|
|
496 if finalize(stmt) != SQLITE_OK: dbError(db)
|
|
|
497
|
|
|
498 proc `[]`*(row: InstantRow, col: int32): string {.inline.} =
|
|
|
499 ## Returns text for given column of the row.
|
|
|
500 ##
|
|
|
501 ## See also:
|
|
|
502 ## * `instantRows iterator <#instantRows.i,DbConn,SqlQuery,varargs[string,]>`_
|
|
|
503 ## example code
|
|
|
504 $column_text(row, col)
|
|
|
505
|
|
|
506 proc unsafeColumnAt*(row: InstantRow, index: int32): cstring {.inline.} =
|
|
|
507 ## Returns cstring for given column of the row.
|
|
|
508 ##
|
|
|
509 ## See also:
|
|
|
510 ## * `instantRows iterator <#instantRows.i,DbConn,SqlQuery,varargs[string,]>`_
|
|
|
511 ## example code
|
|
|
512 column_text(row, index)
|
|
|
513
|
|
|
514 proc len*(row: InstantRow): int32 {.inline.} =
|
|
|
515 ## Returns number of columns in a row.
|
|
|
516 ##
|
|
|
517 ## See also:
|
|
|
518 ## * `instantRows iterator <#instantRows.i,DbConn,SqlQuery,varargs[string,]>`_
|
|
|
519 ## example code
|
|
|
520 column_count(row)
|
|
|
521
|
|
|
522 proc getRow*(db: DbConn, query: SqlQuery,
|
|
|
523 args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} =
|
|
|
524 ## Retrieves a single row. If the query doesn't return any rows, this proc
|
|
|
525 ## will return a `Row` with empty strings for each column.
|
|
|
526 ##
|
|
|
527 ## **Examples:**
|
|
|
528 ##
|
|
|
529 ## ```Nim
|
|
|
530 ## let db = open("mytest.db", "", "", "")
|
|
|
531 ##
|
|
|
532 ## # Records of my_table:
|
|
|
533 ## # | id | name |
|
|
|
534 ## # |----|----------|
|
|
|
535 ## # | 1 | item#1 |
|
|
|
536 ## # | 2 | item#2 |
|
|
|
537 ##
|
|
|
538 ## doAssert db.getRow(sql"SELECT id, name FROM my_table"
|
|
|
539 ## ) == Row(@["1", "item#1"])
|
|
|
540 ## doAssert db.getRow(sql"SELECT id, name FROM my_table WHERE id = ?",
|
|
|
541 ## 2) == Row(@["2", "item#2"])
|
|
|
542 ##
|
|
|
543 ## # Returns empty.
|
|
|
544 ## doAssert db.getRow(sql"INSERT INTO my_table (id, name) VALUES (?, ?)",
|
|
|
545 ## 3, "item#3") == @[]
|
|
|
546 ## doAssert db.getRow(sql"DELETE FROM my_table WHERE id = ?", 3) == @[]
|
|
|
547 ## doAssert db.getRow(sql"UPDATE my_table SET name = 'ITEM#1' WHERE id = ?",
|
|
|
548 ## 1) == @[]
|
|
|
549 ## db.close()
|
|
|
550 ## ```
|
|
|
551 var stmt = setupQuery(db, query, args)
|
|
|
552 var L = (column_count(stmt))
|
|
|
553 result = newRow(L)
|
|
|
554 if step(stmt) == SQLITE_ROW:
|
|
|
555 setRow(stmt, result, L)
|
|
|
556 if finalize(stmt) != SQLITE_OK: dbError(db)
|
|
|
557
|
|
|
558 proc getAllRows*(db: DbConn, query: SqlQuery,
|
|
|
559 args: varargs[string, `$`]): seq[Row] {.tags: [ReadDbEffect].} =
|
|
|
560 ## Executes the query and returns the whole result dataset.
|
|
|
561 ##
|
|
|
562 ## **Examples:**
|
|
|
563 ##
|
|
|
564 ## ```Nim
|
|
|
565 ## let db = open("mytest.db", "", "", "")
|
|
|
566 ##
|
|
|
567 ## # Records of my_table:
|
|
|
568 ## # | id | name |
|
|
|
569 ## # |----|----------|
|
|
|
570 ## # | 1 | item#1 |
|
|
|
571 ## # | 2 | item#2 |
|
|
|
572 ##
|
|
|
573 ## doAssert db.getAllRows(sql"SELECT id, name FROM my_table") == @[Row(@["1", "item#1"]), Row(@["2", "item#2"])]
|
|
|
574 ## db.close()
|
|
|
575 ## ```
|
|
|
576 result = @[]
|
|
|
577 for r in fastRows(db, query, args):
|
|
|
578 result.add(r)
|
|
|
579
|
|
|
580 proc getAllRows*(db: DbConn, stmtName: SqlPrepared): seq[Row]
|
|
|
581 {.tags: [ReadDbEffect,WriteDbEffect], since: (1, 3).} =
|
|
|
582 result = @[]
|
|
|
583 for r in fastRows(db, stmtName):
|
|
|
584 result.add(r)
|
|
|
585
|
|
|
586 iterator rows*(db: DbConn, query: SqlQuery,
|
|
|
587 args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} =
|
|
|
588 ## Similar to `fastRows iterator <#fastRows.i,DbConn,SqlQuery,varargs[string,]>`_,
|
|
|
589 ## but slower and safe.
|
|
|
590 ##
|
|
|
591 ## **Examples:**
|
|
|
592 ##
|
|
|
593 ## ```Nim
|
|
|
594 ## let db = open("mytest.db", "", "", "")
|
|
|
595 ##
|
|
|
596 ## # Records of my_table:
|
|
|
597 ## # | id | name |
|
|
|
598 ## # |----|----------|
|
|
|
599 ## # | 1 | item#1 |
|
|
|
600 ## # | 2 | item#2 |
|
|
|
601 ##
|
|
|
602 ## for row in db.rows(sql"SELECT id, name FROM my_table"):
|
|
|
603 ## echo row
|
|
|
604 ##
|
|
|
605 ## ## Output:
|
|
|
606 ## ## @["1", "item#1"]
|
|
|
607 ## ## @["2", "item#2"]
|
|
|
608 ##
|
|
|
609 ## db.close()
|
|
|
610 ## ```
|
|
|
611 for r in fastRows(db, query, args): yield r
|
|
|
612
|
|
|
613 iterator rows*(db: DbConn, stmtName: SqlPrepared): Row
|
|
|
614 {.tags: [ReadDbEffect,WriteDbEffect], since: (1, 3).} =
|
|
|
615 for r in fastRows(db, stmtName): yield r
|
|
|
616
|
|
|
617 proc getValue*(db: DbConn, query: SqlQuery,
|
|
|
618 args: varargs[string, `$`]): string {.tags: [ReadDbEffect].} =
|
|
|
619 ## Executes the query and returns the first column of the first row of the
|
|
|
620 ## result dataset. Returns `""` if the dataset contains no rows or the database
|
|
|
621 ## value is `NULL`.
|
|
|
622 ##
|
|
|
623 ## **Examples:**
|
|
|
624 ##
|
|
|
625 ## ```Nim
|
|
|
626 ## let db = open("mytest.db", "", "", "")
|
|
|
627 ##
|
|
|
628 ## # Records of my_table:
|
|
|
629 ## # | id | name |
|
|
|
630 ## # |----|----------|
|
|
|
631 ## # | 1 | item#1 |
|
|
|
632 ## # | 2 | item#2 |
|
|
|
633 ##
|
|
|
634 ## doAssert db.getValue(sql"SELECT name FROM my_table WHERE id = ?",
|
|
|
635 ## 2) == "item#2"
|
|
|
636 ## doAssert db.getValue(sql"SELECT id, name FROM my_table") == "1"
|
|
|
637 ## doAssert db.getValue(sql"SELECT name, id FROM my_table") == "item#1"
|
|
|
638 ##
|
|
|
639 ## db.close()
|
|
|
640 ## ```
|
|
|
641 var stmt = setupQuery(db, query, args)
|
|
|
642 if step(stmt) == SQLITE_ROW:
|
|
|
643 let cb = column_bytes(stmt, 0)
|
|
|
644 if cb == 0:
|
|
|
645 result = ""
|
|
|
646 else:
|
|
|
647 if column_type(stmt, 0) == SQLITE_BLOB:
|
|
|
648 result.setLen(cb)
|
|
|
649 copyMem(addr(result[0]), column_blob(stmt, 0), cb)
|
|
|
650 else:
|
|
|
651 result = newStringOfCap(cb)
|
|
|
652 add(result, column_text(stmt, 0))
|
|
|
653 else:
|
|
|
654 result = ""
|
|
|
655 if finalize(stmt) != SQLITE_OK: dbError(db)
|
|
|
656
|
|
|
657 proc getValue*(db: DbConn, stmtName: SqlPrepared): string
|
|
|
658 {.tags: [ReadDbEffect,WriteDbEffect], since: (1, 3).} =
|
|
|
659 var stmt = setupQuery(db, stmtName).PStmt
|
|
|
660 if step(stmt) == SQLITE_ROW:
|
|
|
661 let cb = column_bytes(stmt, 0)
|
|
|
662 if cb == 0:
|
|
|
663 result = ""
|
|
|
664 else:
|
|
|
665 if column_type(stmt, 0) == SQLITE_BLOB:
|
|
|
666 result.setLen(cb)
|
|
|
667 copyMem(addr(result[0]), column_blob(stmt, 0), cb)
|
|
|
668 else:
|
|
|
669 result = newStringOfCap(cb)
|
|
|
670 add(result, column_text(stmt, 0))
|
|
|
671 else:
|
|
|
672 result = ""
|
|
|
673
|
|
|
674 proc tryInsertID*(db: DbConn, query: SqlQuery,
|
|
|
675 args: varargs[string, `$`]): int64
|
|
|
676 {.tags: [WriteDbEffect], raises: [DbError].} =
|
|
|
677 ## Executes the query (typically "INSERT") and returns the
|
|
|
678 ## generated ID for the row or -1 in case of an error.
|
|
|
679 ##
|
|
|
680 ## **Examples:**
|
|
|
681 ##
|
|
|
682 ## ```Nim
|
|
|
683 ## let db = open("mytest.db", "", "", "")
|
|
|
684 ## db.exec(sql"CREATE TABLE my_table (id INTEGER, name VARCHAR(50) NOT NULL)")
|
|
|
685 ##
|
|
|
686 ## doAssert db.tryInsertID(sql"INSERT INTO not_exist_table (id, name) VALUES (?, ?)",
|
|
|
687 ## 1, "item#1") == -1
|
|
|
688 ## db.close()
|
|
|
689 ## ```
|
|
|
690 assert(not db.isNil, "Database not connected.")
|
|
|
691 var q = dbFormat(query, args)
|
|
|
692 var stmt: sqlite3.PStmt
|
|
|
693 result = -1
|
|
|
694 if prepare_v2(db, q.cstring, q.len.cint, stmt, nil) == SQLITE_OK:
|
|
|
695 if step(stmt) == SQLITE_DONE:
|
|
|
696 result = last_insert_rowid(db)
|
|
|
697 if finalize(stmt) != SQLITE_OK:
|
|
|
698 result = -1
|
|
|
699 else:
|
|
|
700 discard finalize(stmt)
|
|
|
701
|
|
|
702 proc insertID*(db: DbConn, query: SqlQuery,
|
|
|
703 args: varargs[string, `$`]): int64 {.tags: [WriteDbEffect].} =
|
|
|
704 ## Executes the query (typically "INSERT") and returns the
|
|
|
705 ## generated ID for the row.
|
|
|
706 ##
|
|
|
707 ## Raises a `DbError` exception when failed to insert row.
|
|
|
708 ## For Postgre this adds `RETURNING id` to the query, so it only works
|
|
|
709 ## if your primary key is named `id`.
|
|
|
710 ##
|
|
|
711 ## **Examples:**
|
|
|
712 ##
|
|
|
713 ## ```Nim
|
|
|
714 ## let db = open("mytest.db", "", "", "")
|
|
|
715 ## db.exec(sql"CREATE TABLE my_table (id INTEGER, name VARCHAR(50) NOT NULL)")
|
|
|
716 ##
|
|
|
717 ## for i in 0..2:
|
|
|
718 ## let id = db.insertID(sql"INSERT INTO my_table (id, name) VALUES (?, ?)", i, "item#" & $i)
|
|
|
719 ## echo "LoopIndex = ", i, ", InsertID = ", id
|
|
|
720 ##
|
|
|
721 ## # Output:
|
|
|
722 ## # LoopIndex = 0, InsertID = 1
|
|
|
723 ## # LoopIndex = 1, InsertID = 2
|
|
|
724 ## # LoopIndex = 2, InsertID = 3
|
|
|
725 ##
|
|
|
726 ## db.close()
|
|
|
727 ## ```
|
|
|
728 result = tryInsertID(db, query, args)
|
|
|
729 if result < 0: dbError(db)
|
|
|
730
|
|
|
731 proc tryInsert*(db: DbConn, query: SqlQuery, pkName: string,
|
|
|
732 args: varargs[string, `$`]): int64
|
|
|
733 {.tags: [WriteDbEffect], raises: [DbError], since: (1, 3).} =
|
|
|
734 ## same as tryInsertID
|
|
|
735 tryInsertID(db, query, args)
|
|
|
736
|
|
|
737 proc insert*(db: DbConn, query: SqlQuery, pkName: string,
|
|
|
738 args: varargs[string, `$`]): int64
|
|
|
739 {.tags: [WriteDbEffect], since: (1, 3).} =
|
|
|
740 ## same as insertId
|
|
|
741 result = tryInsert(db, query, pkName, args)
|
|
|
742 if result < 0: dbError(db)
|
|
|
743
|
|
|
744 proc execAffectedRows*(db: DbConn, query: SqlQuery,
|
|
|
745 args: varargs[string, `$`]): int64 {.
|
|
|
746 tags: [ReadDbEffect, WriteDbEffect].} =
|
|
|
747 ## Executes the query (typically "UPDATE") and returns the
|
|
|
748 ## number of affected rows.
|
|
|
749 ##
|
|
|
750 ## **Examples:**
|
|
|
751 ##
|
|
|
752 ## ```Nim
|
|
|
753 ## let db = open("mytest.db", "", "", "")
|
|
|
754 ##
|
|
|
755 ## # Records of my_table:
|
|
|
756 ## # | id | name |
|
|
|
757 ## # |----|----------|
|
|
|
758 ## # | 1 | item#1 |
|
|
|
759 ## # | 2 | item#2 |
|
|
|
760 ##
|
|
|
761 ## doAssert db.execAffectedRows(sql"UPDATE my_table SET name = 'TEST'") == 2
|
|
|
762 ##
|
|
|
763 ## db.close()
|
|
|
764 ## ```
|
|
|
765 exec(db, query, args)
|
|
|
766 result = changes(db)
|
|
|
767
|
|
|
768 proc execAffectedRows*(db: DbConn, stmtName: SqlPrepared): int64
|
|
|
769 {.tags: [ReadDbEffect, WriteDbEffect],since: (1, 3).} =
|
|
|
770 exec(db, stmtName)
|
|
|
771 result = changes(db)
|
|
|
772
|
|
|
773 proc close*(db: DbConn) {.tags: [DbEffect].} =
|
|
|
774 ## Closes the database connection.
|
|
|
775 ##
|
|
|
776 ## **Examples:**
|
|
|
777 ## ```Nim
|
|
|
778 ## let db = open("mytest.db", "", "", "")
|
|
|
779 ## db.close()
|
|
|
780 ## ```
|
|
|
781 if sqlite3.close(db) != SQLITE_OK: dbError(db)
|
|
|
782
|
|
|
783 proc open*(connection, user, password, database: string): DbConn {.
|
|
|
784 tags: [DbEffect].} =
|
|
|
785 ## Opens a database connection. Raises a `DbError` exception if the connection
|
|
|
786 ## could not be established.
|
|
|
787 ##
|
|
|
788 ## **Note:** Only the `connection` parameter is used for `sqlite`.
|
|
|
789 ##
|
|
|
790 ## **Examples:**
|
|
|
791 ## ```Nim
|
|
|
792 ## try:
|
|
|
793 ## let db = open("mytest.db", "", "", "")
|
|
|
794 ## ## do something...
|
|
|
795 ## ## db.getAllRows(sql"SELECT * FROM my_table")
|
|
|
796 ## db.close()
|
|
|
797 ## except:
|
|
|
798 ## stderr.writeLine(getCurrentExceptionMsg())
|
|
|
799 ## ```
|
|
|
800 var db: DbConn
|
|
|
801 if sqlite3.open(connection, db) == SQLITE_OK:
|
|
|
802 result = db
|
|
|
803 else:
|
|
|
804 dbError(db)
|
|
|
805
|
|
|
806 proc setEncoding*(connection: DbConn, encoding: string): bool {.
|
|
|
807 tags: [DbEffect].} =
|
|
|
808 ## Sets the encoding of a database connection, returns `true` for
|
|
|
809 ## success, `false` for failure.
|
|
|
810 ##
|
|
|
811 ## **Note:** The encoding cannot be changed once it's been set.
|
|
|
812 ## According to SQLite3 documentation, any attempt to change
|
|
|
813 ## the encoding after the database is created will be silently
|
|
|
814 ## ignored.
|
|
|
815 exec(connection, sql"PRAGMA encoding = ?", [encoding])
|
|
|
816 result = connection.getValue(sql"PRAGMA encoding") == encoding
|
|
|
817
|
|
|
818 proc finalize*(sqlPrepared:SqlPrepared) {.discardable, since: (1, 3).} =
|
|
|
819 discard finalize(sqlPrepared.PStmt)
|
|
|
820
|
|
|
821 template dbBindParamError*(paramIdx: int, val: varargs[untyped]) =
|
|
|
822 ## Raises a `DbError` exception.
|
|
|
823 var e: ref DbError
|
|
|
824 new(e)
|
|
|
825 e.msg = "error binding param in position " & $paramIdx
|
|
|
826 raise e
|
|
|
827
|
|
|
828 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: int32) {.since: (1, 3).} =
|
|
|
829 ## Binds a int32 to the specified paramIndex.
|
|
|
830 if bind_int(ps.PStmt, paramIdx.int32, val) != SQLITE_OK:
|
|
|
831 dbBindParamError(paramIdx, val)
|
|
|
832
|
|
|
833 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: int64) {.since: (1, 3).} =
|
|
|
834 ## Binds a int64 to the specified paramIndex.
|
|
|
835 if bind_int64(ps.PStmt, paramIdx.int32, val) != SQLITE_OK:
|
|
|
836 dbBindParamError(paramIdx, val)
|
|
|
837
|
|
|
838 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: int) {.since: (1, 3).} =
|
|
|
839 ## Binds a int to the specified paramIndex.
|
|
|
840 when sizeof(int) == 8:
|
|
|
841 bindParam(ps, paramIdx, val.int64)
|
|
|
842 else:
|
|
|
843 bindParam(ps, paramIdx, val.int32)
|
|
|
844
|
|
|
845 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: float64) {.since: (1, 3).} =
|
|
|
846 ## Binds a 64bit float to the specified paramIndex.
|
|
|
847 if bind_double(ps.PStmt, paramIdx.int32, val) != SQLITE_OK:
|
|
|
848 dbBindParamError(paramIdx, val)
|
|
|
849
|
|
|
850 proc bindNull*(ps: SqlPrepared, paramIdx: int) {.since: (1, 3).} =
|
|
|
851 ## Sets the bindparam at the specified paramIndex to null
|
|
|
852 ## (default behaviour by sqlite).
|
|
|
853 if bind_null(ps.PStmt, paramIdx.int32) != SQLITE_OK:
|
|
|
854 dbBindParamError(paramIdx)
|
|
|
855
|
|
|
856 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: string, copy = true) {.since: (1, 3).} =
|
|
|
857 ## Binds a string to the specified paramIndex.
|
|
|
858 ## if copy is true then SQLite makes its own private copy of the data immediately
|
|
|
859 if bind_text(ps.PStmt, paramIdx.int32, val.cstring, val.len.int32, if copy: SQLITE_TRANSIENT else: SQLITE_STATIC) != SQLITE_OK:
|
|
|
860 dbBindParamError(paramIdx, val)
|
|
|
861
|
|
|
862 proc bindParam*(ps: SqlPrepared, paramIdx: int,val: openArray[byte], copy = true) {.since: (1, 3).} =
|
|
|
863 ## binds a blob to the specified paramIndex.
|
|
|
864 ## if copy is true then SQLite makes its own private copy of the data immediately
|
|
|
865 let len = val.len
|
|
|
866 if bind_blob(ps.PStmt, paramIdx.int32, val[0].unsafeAddr, len.int32, if copy: SQLITE_TRANSIENT else: SQLITE_STATIC) != SQLITE_OK:
|
|
|
867 dbBindParamError(paramIdx, val)
|
|
|
868
|
|
|
869 when not defined(testing) and isMainModule:
|
|
|
870 var db = open(":memory:", "", "", "")
|
|
|
871 exec(db, sql"create table tbl1(one varchar(10), two smallint)", [])
|
|
|
872 exec(db, sql"insert into tbl1 values('hello!',10)", [])
|
|
|
873 exec(db, sql"insert into tbl1 values('goodbye', 20)", [])
|
|
|
874 var p1 = db.prepare "create table tbl2(one varchar(10), two smallint)"
|
|
|
875 exec(db, p1)
|
|
|
876 finalize(p1)
|
|
|
877 var p2 = db.prepare "insert into tbl2 values('hello!',10)"
|
|
|
878 exec(db, p2)
|
|
|
879 finalize(p2)
|
|
|
880 var p3 = db.prepare "insert into tbl2 values('goodbye', 20)"
|
|
|
881 exec(db, p3)
|
|
|
882 finalize(p3)
|
|
|
883 #db.query("create table tbl1(one varchar(10), two smallint)")
|
|
|
884 #db.query("insert into tbl1 values('hello!',10)")
|
|
|
885 #db.query("insert into tbl1 values('goodbye', 20)")
|
|
|
886 for r in db.rows(sql"select * from tbl1", []):
|
|
|
887 echo(r[0], r[1])
|
|
|
888 for r in db.instantRows(sql"select * from tbl1", []):
|
|
|
889 echo(r[0], r[1])
|
|
|
890 var p4 = db.prepare "select * from tbl2"
|
|
|
891 for r in db.rows(p4):
|
|
|
892 echo(r[0], r[1])
|
|
|
893 finalize(p4)
|
|
|
894 var i5 = 0
|
|
|
895 var p5 = db.prepare "select * from tbl2"
|
|
|
896 for r in db.instantRows(p5):
|
|
|
897 inc i5
|
|
|
898 echo(r[0], r[1])
|
|
|
899 assert i5 == 2
|
|
|
900 finalize(p5)
|
|
|
901
|
|
|
902 for r in db.rows(sql"select * from tbl2", []):
|
|
|
903 echo(r[0], r[1])
|
|
|
904 for r in db.instantRows(sql"select * from tbl2", []):
|
|
|
905 echo(r[0], r[1])
|
|
|
906 var p6 = db.prepare "select * from tbl2 where one = ? "
|
|
|
907 p6.bindParams("goodbye")
|
|
|
908 var rowsP3 = 0
|
|
|
909 for r in db.rows(p6):
|
|
|
910 rowsP3 = 1
|
|
|
911 echo(r[0], r[1])
|
|
|
912 assert rowsP3 == 1
|
|
|
913 finalize(p6)
|
|
|
914
|
|
|
915 var p7 = db.prepare "select * from tbl2 where two=?"
|
|
|
916 p7.bindParams(20'i32)
|
|
|
917 when sizeof(int) == 4:
|
|
|
918 p7.bindParams(20)
|
|
|
919 var rowsP = 0
|
|
|
920 for r in db.rows(p7):
|
|
|
921 rowsP = 1
|
|
|
922 echo(r[0], r[1])
|
|
|
923 assert rowsP == 1
|
|
|
924 finalize(p7)
|
|
|
925
|
|
|
926 exec(db, sql"CREATE TABLE photos(ID INTEGER PRIMARY KEY AUTOINCREMENT, photo BLOB)")
|
|
|
927 var p8 = db.prepare "INSERT INTO photos (ID,PHOTO) VALUES (?,?)"
|
|
|
928 var d = "abcdefghijklmnopqrstuvwxyz"
|
|
|
929 p8.bindParams(1'i32, "abcdefghijklmnopqrstuvwxyz")
|
|
|
930 exec(db, p8)
|
|
|
931 finalize(p8)
|
|
|
932 var p10 = db.prepare "INSERT INTO photos (ID,PHOTO) VALUES (?,?)"
|
|
|
933 p10.bindParams(2'i32,nil)
|
|
|
934 exec(db, p10)
|
|
|
935 exec( db, p10, 3, nil)
|
|
|
936 finalize(p10)
|
|
|
937 for r in db.rows(sql"select * from photos where ID = 1", []):
|
|
|
938 assert r[1].len == d.len
|
|
|
939 assert r[1] == d
|
|
|
940 var i6 = 0
|
|
|
941 for r in db.rows(sql"select * from photos where ID = 3", []):
|
|
|
942 i6 = 1
|
|
|
943 assert i6 == 1
|
|
|
944 var p9 = db.prepare("select * from photos where PHOTO is ?")
|
|
|
945 p9.bindParams(nil)
|
|
|
946 var rowsP2 = 0
|
|
|
947 for r in db.rows(p9):
|
|
|
948 rowsP2 = 1
|
|
|
949 echo(r[0], repr r[1])
|
|
|
950 assert rowsP2 == 1
|
|
|
951 finalize(p9)
|
|
|
952
|
|
|
953 db_sqlite.close(db)
|
